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