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(