[Commits] 4f6ac2d3bf0: MDEV-25969: Condition pushdown into derived table doesn't work if select list uses SP

Sergei Petrunia psergey at askmonty.org
Fri Jun 25 19:11:19 EEST 2021


revision-id: 4f6ac2d3bf0dd68e640a6d728fd2b98788bc7959 (mariadb-10.5.10-226-g4f6ac2d3bf0)
parent(s): 7abf8b5c4d856a766dfdf4b2e1212694d66cbffb
author: Sergei Petrunia
committer: Sergei Petrunia
timestamp: 2021-06-25 19:11:19 +0300
message:

MDEV-25969: Condition pushdown into derived table doesn't work if select list uses SP

Variant 2, the "handle failures at conversion" approach.
The patch is against the 10.5 series.

Consider a query in form:

  select ... from (select item2 as COL1) as T where COL1=123

Condition pushdown into derived table will try to push "COL1=123" condition
down into table T.
The process of pushdown involves "substituting" the item, that is,
replacing Item_field("T.COL1") with its "producing item" item2.
In order to use item2, one needs to clone it (call Item::build_clone).

If the item is not cloneable (e.g. Item_func_sp is not), the pushdown
process will fail and nothing at all will be pushed.

Fixed by introducing get_clonable_extracted_cond_for_where() which
will try to apply the transformation for as many parts of condition
as possible.  The parts of condition that couldn't be transformed are
dropped.

---
 mysql-test/main/derived_cond_pushdown.result | 98 ++++++++++++++++++++++++++++
 mysql-test/main/derived_cond_pushdown.test   | 48 ++++++++++++++
 sql/sql_lex.cc                               | 54 ++++++++++++++-
 3 files changed, 198 insertions(+), 2 deletions(-)

diff --git a/mysql-test/main/derived_cond_pushdown.result b/mysql-test/main/derived_cond_pushdown.result
index 016ca22af1b..6a053b0f551 100644
--- a/mysql-test/main/derived_cond_pushdown.result
+++ b/mysql-test/main/derived_cond_pushdown.result
@@ -17343,3 +17343,101 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 drop view v1;
 drop table t1;
 # End of 10.4 tests
+#
+# MDEV-25969: Condition pushdown into derived table doesn't work if select list uses SP
+#
+create function f1(a int) returns int DETERMINISTIC return (a+1);
+create table t1 (
+pk int primary key,
+a int,
+b int,
+key(a)
+);
+create table t2(a int);
+insert into t2 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t3(a int);
+insert into t3 select A.a + B.a* 10 + C.a * 100 from t2 A, t2 B, t2 C;
+insert into t1 select a,a,a from t3;
+create view v1 as
+select
+t1.a as col1,
+f1(t1.b) as col2
+from
+t1;
+create view v2 as
+select
+t1.a as col1,
+f1(t1.b) as col2
+from
+t1;
+create view v3 as
+select col2, col1 from v1
+union all
+select col2, col1 from v2;
+explain select * from v3 where col1=123;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DERIVED	t1	ref	a	a	5	const	1	
+3	UNION	t1	ref	a	a	5	const	1	
+# This must use ref accesses for reading table t1, not full scans:
+explain format=json
+select * from v3 where col1=123 and col2=321;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "<derived2>",
+      "access_type": "ALL",
+      "rows": 2,
+      "filtered": 100,
+      "attached_condition": "v3.col1 = 123 and v3.col2 = 321",
+      "materialized": {
+        "query_block": {
+          "union_result": {
+            "table_name": "<union2,3>",
+            "access_type": "ALL",
+            "query_specifications": [
+              {
+                "query_block": {
+                  "select_id": 2,
+                  "table": {
+                    "table_name": "t1",
+                    "access_type": "ref",
+                    "possible_keys": ["a"],
+                    "key": "a",
+                    "key_length": "5",
+                    "used_key_parts": ["a"],
+                    "ref": ["const"],
+                    "rows": 1,
+                    "filtered": 100
+                  }
+                }
+              },
+              {
+                "query_block": {
+                  "select_id": 3,
+                  "operation": "UNION",
+                  "table": {
+                    "table_name": "t1",
+                    "access_type": "ref",
+                    "possible_keys": ["a"],
+                    "key": "a",
+                    "key_length": "5",
+                    "used_key_parts": ["a"],
+                    "ref": ["const"],
+                    "rows": 1,
+                    "filtered": 100
+                  }
+                }
+              }
+            ]
+          }
+        }
+      }
+    }
+  }
+}
+drop function f1;
+drop view v1,v2,v3;
+drop table t1, t2,t3;
diff --git a/mysql-test/main/derived_cond_pushdown.test b/mysql-test/main/derived_cond_pushdown.test
index a880712c8bd..8661bcdf388 100644
--- a/mysql-test/main/derived_cond_pushdown.test
+++ b/mysql-test/main/derived_cond_pushdown.test
@@ -3540,3 +3540,51 @@ drop view v1;
 drop table t1;
 
 --echo # End of 10.4 tests
+
+--echo #
+--echo # MDEV-25969: Condition pushdown into derived table doesn't work if select list uses SP
+--echo #
+create function f1(a int) returns int DETERMINISTIC return (a+1);
+
+create table t1 (
+  pk int primary key,
+  a int,
+  b int,
+  key(a)
+);
+
+create table t2(a int);
+insert into t2 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+
+create table t3(a int);
+insert into t3 select A.a + B.a* 10 + C.a * 100 from t2 A, t2 B, t2 C;
+
+insert into t1 select a,a,a from t3;
+
+create view v1 as
+select
+  t1.a as col1,
+  f1(t1.b) as col2
+from
+  t1;
+
+create view v2 as
+select
+  t1.a as col1,
+  f1(t1.b) as col2
+from
+  t1;
+create view v3 as
+select col2, col1 from v1
+union all
+select col2, col1 from v2;
+
+explain select * from v3 where col1=123;
+
+--echo # This must use ref accesses for reading table t1, not full scans:
+explain format=json
+select * from v3 where col1=123 and col2=321;
+
+drop function f1;
+drop view v1,v2,v3;
+drop table t1, t2,t3;
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index f16102d918b..ac615483aee 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -10360,6 +10360,55 @@ bool LEX::sp_proc_stmt_statement_finalize(THD *thd, bool no_lookahead)
 }
 
 
+/*
+  @brief
+    Given condition cond and transformer+argument, try transforming as many
+    disjuncts as possible.
+
+  @detail
+    The motivation of this function is to convert the condition that's being
+    pushed into a WHERE clause with derived_field_transformer_for_where.
+    The transformer may fail, in this case we want to convert as much of the
+    condition as possible.
+    This function only does it for top-level AND: disjuncts that could not be
+    converted are dropped.
+
+  @return
+    Converted condition, or NULL if nothing could be converted
+*/
+
+static
+Item *get_clonable_extracted_cond_for_where(THD *thd,
+                                            Item *cond,
+                                            Item_transformer transformer,
+                                            uchar *arg)
+{
+  if (cond->type() != Item::COND_ITEM ||
+      ((Item_cond*) cond)->functype() != Item_func::COND_AND_FUNC)
+    return cond->transform(thd, transformer, arg);
+
+  List_iterator<Item> li(*((Item_cond*) cond)->argument_list());
+  Item *item;
+  while ((item=li++))
+  {
+    Item *new_item= item->transform(thd, transformer, arg);
+    if (!new_item)
+      li.remove();
+    else
+      li.replace(new_item);
+  }
+  switch (((Item_cond*) cond)->argument_list()->elements)
+  {
+  case 0:
+    return 0;
+  case 1:
+    return ((Item_cond*) cond)->argument_list()->head();
+  default:
+    return cond;
+  }
+}
+
+
 /**
   @brief
     Extract the condition that can be pushed into WHERE clause
@@ -10368,6 +10417,8 @@ bool LEX::sp_proc_stmt_statement_finalize(THD *thd, bool no_lookahead)
   @param cond            the condition from which to extract a pushed condition
   @param remaining_cond  IN/OUT the condition that will remain of cond after
                          the extraction
+                         Note: returning NULL means "re-check the entire
+                         condition at the upper level".
   @param transformer     the transformer callback function to be
                          applied to the fields of the condition so it
                          can be pushed`
@@ -10432,8 +10483,7 @@ void st_select_lex::pushdown_cond_into_where_clause(THD *thd, Item *cond,
 
   if (!join->group_list && !with_sum_func)
   {
-    cond=
-      cond->transform(thd, transformer, arg);
+    cond= get_clonable_extracted_cond_for_where(thd, cond, transformer, arg);
     if (cond)
     {
       cond->walk(


More information about the commits mailing list