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

Sergei Petrunia psergey at askmonty.org
Thu Jun 24 22:02:09 EEST 2021


revision-id: 521c546a71c9449a87ebabbf13725b91fec45671 (mariadb-10.5.10-226-g521c546a71c)
parent(s): 7abf8b5c4d856a766dfdf4b2e1212694d66cbffb
author: Sergei Petrunia
committer: Sergei Petrunia
timestamp: 2021-06-24 22:02:09 +0300
message:

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

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.

This patch makes pushdown_cond_for_derived() to first extract the
portion of a condition which can be pushed, and then push only that.

---
 mysql-test/main/derived_cond_pushdown.result | 45 +++++++++++++++++++++++++
 mysql-test/main/derived_cond_pushdown.test   | 50 ++++++++++++++++++++++++++++
 sql/item.cc                                  | 24 +++++++++++++
 sql/item.h                                   | 11 ++++++
 sql/item_cmpfunc.h                           |  4 +++
 sql/item_func.h                              |  1 +
 sql/sql_derived.cc                           | 23 +++++++++++++
 7 files changed, 158 insertions(+)

diff --git a/mysql-test/main/derived_cond_pushdown.result b/mysql-test/main/derived_cond_pushdown.result
index 016ca22af1b..82e34a71964 100644
--- a/mysql-test/main/derived_cond_pushdown.result
+++ b/mysql-test/main/derived_cond_pushdown.result
@@ -17343,3 +17343,48 @@ 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 select * from v3 where col1=123 and col2=321;
+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	
+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..1e924747b8f 100644
--- a/mysql-test/main/derived_cond_pushdown.test
+++ b/mysql-test/main/derived_cond_pushdown.test
@@ -3540,3 +3540,53 @@ 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 select * from v3 where col1=123 and col2=321;
+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/item.cc b/sql/item.cc
index 8cad7111e07..13505690c9b 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -7605,6 +7605,30 @@ Item *find_producing_item(Item *item, st_select_lex *sel)
   return NULL;
 }
 
+
+/*
+  @brief Check if this item cannot be pushed down into derived table
+
+  @detail
+     This function checks if derived_field_transformer_for_where() will
+     fail. It will fail if the "producing item" (column in the derived table)
+     cannot be cloned.
+
+  @return
+    false - Ok, can be pushed
+    true  - Cannot be pushed
+*/
+
+bool Item_field::check_non_pushable_processor(void *arg)
+{
+  st_select_lex *sel= (st_select_lex *)arg;
+  Item *producing_item= find_producing_item(this, sel);
+  if (producing_item)
+    return producing_item->walk(&Item::check_non_cloneable_processor, 0, 0);
+  return false; // Ok
+}
+
+
 Item *Item_field::derived_field_transformer_for_where(THD *thd, uchar *arg)
 {
   st_select_lex *sel= (st_select_lex *)arg;
diff --git a/sql/item.h b/sql/item.h
index e340483466b..a8526bd1016 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -2118,6 +2118,12 @@ class Item: public Value_source,
     If there is some, sets a bit for this key in the proper key map.
   */
   virtual bool check_index_dependence(void *arg) { return 0; }
+
+  /* Return true if the item can NOT be pushed down into a derived table */
+  virtual bool check_non_pushable_processor(void *arg) { return 0; }
+
+  /* Return true if the item cannot be cloned (get_copy() will return NULL) */
+  virtual bool check_non_cloneable_processor(void *arg) { return 0; }
   /*============== End of Item processor list ======================*/
 
   /*
@@ -2471,10 +2477,14 @@ class Item: public Value_source,
     marker &= ~EXTRACTION_MASK;
   }
   void check_pushable_cond(Pushdown_checker excl_dep_func, uchar *arg);
+  /*
+    @seealso pushable_cond_checker_for_derived_inner
+  */
   bool pushable_cond_checker_for_derived(uchar *arg)
   {
     return excl_dep_on_table(*((table_map *)arg));
   }
+  bool pushable_cond_checker_for_derived_inner(uchar *arg);
   bool pushable_cond_checker_for_subquery(uchar *arg)
   {
     DBUG_ASSERT(((Item*) arg)->get_IN_subquery());
@@ -3610,6 +3620,7 @@ class Item_field :public Item_ident,
     return field->table->pos_in_table_list->outer_join;
   }
   bool check_index_dependence(void *arg) override;
+  bool check_non_pushable_processor(void *arg) override;
   friend class Item_default_value;
   friend class Item_insert_value;
   friend class st_select_lex_unit;
diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h
index aa7269ab95a..d02a92bbfa9 100644
--- a/sql/item_cmpfunc.h
+++ b/sql/item_cmpfunc.h
@@ -2908,6 +2908,8 @@ class Item_func_regex :public Item_bool_func
   const char *func_name() const { return "regexp"; }
   enum precedence precedence() const { return IN_PRECEDENCE; }
   Item *get_copy(THD *) { return 0; }
+  bool check_non_cloneable_processor(void *arg) { return true; }
+
   void print(String *str, enum_query_type query_type)
   {
     print_op(str, query_type);
@@ -2947,6 +2949,7 @@ class Item_func_regexp_instr :public Item_long_func
   bool fix_length_and_dec();
   const char *func_name() const { return "regexp_instr"; }
   Item *get_copy(THD *thd) { return 0; }
+  bool check_non_cloneable_processor(void *arg) { return true; }
 };
 
 
@@ -3206,6 +3209,7 @@ class Item_equal: public Item_bool_func
   void set_context_field(Item_field *ctx_field) { context_field= ctx_field; }
   void set_link_equal_fields(bool flag) { link_equal_fields= flag; }
   Item* get_copy(THD *thd) { return 0; }
+  bool check_non_cloneable_processor(void *arg) override { return true; }
   /*
     This does not comply with the specification of the virtual method,
     but Item_equal items are processed distinguishly anyway
diff --git a/sql/item_func.h b/sql/item_func.h
index e774d9c53bd..506b2da335c 100644
--- a/sql/item_func.h
+++ b/sql/item_func.h
@@ -3563,6 +3563,7 @@ class Item_func_sp :public Item_func,
     return TRUE;
   }
   Item *get_copy(THD *) { return 0; }
+  bool check_non_cloneable_processor(void *arg) override { return true; }
   bool eval_not_null_tables(void *opt_arg)
   {
     not_null_tables_cache= 0;
diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc
index ed3743b029b..1017dc1a0d8 100644
--- a/sql/sql_derived.cc
+++ b/sql/sql_derived.cc
@@ -1358,6 +1358,25 @@ bool mysql_derived_reinit(THD *thd, LEX *lex, TABLE_LIST *derived)
 }
 
 
+/*
+  @brief
+  Check if this item can be pushed into given SELECT.
+
+  @param arg  Pointer to SELECT_LEX object of the child select
+
+  @seealso  Item::pushable_cond_checker_for_derived
+
+  @detail
+     This function checks if derived_field_transformer_for_where() will
+     fail. It will fail if the "producing_item" (column in the derived table)
+     cannot be cloned.
+*/
+bool Item::pushable_cond_checker_for_derived_inner(uchar *arg)
+{
+  return !walk(&Item::check_non_pushable_processor, false, arg);
+}
+
+
 /**
   @brief
     Extract condition that can be pushed into a derived table/view
@@ -1486,6 +1505,10 @@ bool pushdown_cond_for_derived(THD *thd, Item *cond, TABLE_LIST *derived)
     if (!extracted_cond_copy)
       continue;
 
+    extracted_cond_copy->check_pushable_cond(
+      &Item::pushable_cond_checker_for_derived_inner, (uchar*)sl);
+    extracted_cond_copy= extracted_cond_copy->build_pushable_cond(thd, NULL, 0);
+
     /*
       Rename the columns of all non-first selects of a union to be compatible
       by names with the columns of the first select. It will allow to use copies


More information about the commits mailing list