- commits - lists.mariadb.org

[Commits] ebc5a2b9178: MDEV-25969: Condition pushdown into derived table doesn't work if select list uses SP
by psergey 30 Jun '21

30 Jun '21

revision-id: ebc5a2b917829363dcc9499523f249c87f990123 (mariadb-10.2.39-33-gebc5a2b9178) parent(s): 8147d2e6183a1a4a4f3db2884966f5da2d17678c author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-06-30 13:47:01 +0300 message: MDEV-25969: Condition pushdown into derived table doesn't work if select list uses SP Variant 3, the "handle failures at conversion" approach. 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 transform_condition_or_part() 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/r/derived_cond_pushdown.result | 146 ++++++++++++++++++++++++++++++ mysql-test/t/derived_cond_pushdown.test | 70 ++++++++++++++ sql/sql_derived.cc | 78 ++++++++++++++-- 3 files changed, 288 insertions(+), 6 deletions(-) diff --git a/mysql-test/r/derived_cond_pushdown.result b/mysql-test/r/derived_cond_pushdown.result index 28532ae88a4..f5ef3c3a84d 100644 --- a/mysql-test/r/derived_cond_pushdown.result +++ b/mysql-test/r/derived_cond_pushdown.result @@ -10673,4 +10673,150 @@ Warnings: Note 1003 select `v2`.`a` AS `a`,`v2`.`f` AS `f`,`v2`.`g` AS `g` from `test`.`v2` where `v2`.`f` = `v2`.`a` and `v2`.`g` = `v2`.`a` drop view v1,v2; drop table t1; +# +# 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, + "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; +# +# Another testcase, with pushdown through GROUP BY +# +create table t1 (a int, b int); +insert into t1 values (1,1),(2,2),(3,3); +create function f1(a int) returns int DETERMINISTIC return (a+1); +create view v2(a, a2, s) as +select a, f1(a), sum(b) from t1 group by a, f1(a); +# Here, +# "(s+1) > 10" will be pushed into HAVING +# "a > 1" will be pushed all the way to the table scan on t1 +# "a2>123" will be pushed into HAVING (as it refers to an SP call which +# prevents pushing it to the WHERE) +explain format=json +select * from v2 where (s+1) > 10 AND a > 1 and a2>123; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "<derived2>", + "access_type": "ALL", + "rows": 3, + "filtered": 100, + "attached_condition": "v2.s + 1 > 10 and v2.a > 1 and v2.a2 > 123", + "materialized": { + "query_block": { + "select_id": 2, + "having_condition": "s + 1 > 10 and a2 > 123", + "filesort": { + "sort_key": "t1.a, f1(t1.a)", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 3, + "filtered": 100, + "attached_condition": "t1.a > 1" + } + } + } + } + } + } + } +} +drop view v2; +drop function f1; +drop table t1; # End of 10.2 tests diff --git a/mysql-test/t/derived_cond_pushdown.test b/mysql-test/t/derived_cond_pushdown.test index 58f38ac1e5a..9b7658a725e 100644 --- a/mysql-test/t/derived_cond_pushdown.test +++ b/mysql-test/t/derived_cond_pushdown.test @@ -2237,4 +2237,74 @@ eval explain extended $q2; drop view v1,v2; drop table t1; +--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; + +--echo # +--echo # Another testcase, with pushdown through GROUP BY +--echo # +create table t1 (a int, b int); +insert into t1 values (1,1),(2,2),(3,3); + +create function f1(a int) returns int DETERMINISTIC return (a+1); + +create view v2(a, a2, s) as +select a, f1(a), sum(b) from t1 group by a, f1(a); + +--echo # Here, +--echo # "(s+1) > 10" will be pushed into HAVING +--echo # "a > 1" will be pushed all the way to the table scan on t1 +--echo # "a2>123" will be pushed into HAVING (as it refers to an SP call which +--echo # prevents pushing it to the WHERE) +explain format=json +select * from v2 where (s+1) > 10 AND a > 1 and a2>123; + +drop view v2; +drop function f1; +drop table t1; --echo # End of 10.2 tests diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc index 632baf4bc5b..4ee95eb0516 100644 --- a/sql/sql_derived.cc +++ b/sql/sql_derived.cc @@ -1192,6 +1192,67 @@ bool mysql_derived_reinit(THD *thd, LEX *lex, TABLE_LIST *derived) } +/* + @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 for some sub-condition, in this case we want to + convert the most restrictive part of the condition that's 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 *transform_condition_or_part(THD *thd, + Item *cond, + Item_transformer transformer, + uchar *arg) +{ + if (cond->type() != Item::COND_ITEM || + ((Item_cond*) cond)->functype() != Item_func::COND_AND_FUNC) + { + Item *new_item= cond->transform(thd, transformer, arg); + // Indicate that the condition is not pushable + if (!new_item) + cond->clear_extraction_flag(); + return new_item; + } + + 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) + { + // Indicate that the condition is not pushable + item->clear_extraction_flag(); + li.remove(); + } + else + li.replace(new_item); + } + + switch (((Item_cond*) cond)->argument_list()->elements) + { + case 0: + return NULL; + case 1: + return ((Item_cond*) cond)->argument_list()->head(); + default: + return cond; + } +} + + /** @brief Extract the condition depended on derived table/view and pushed it there @@ -1287,9 +1348,11 @@ bool pushdown_cond_for_derived(THD *thd, Item *cond, TABLE_LIST *derived) if (!sl->join->group_list && !sl->with_sum_func) { /* extracted_cond_copy is pushed into where of sl */ - extracted_cond_copy= extracted_cond_copy->transform(thd, - &Item::derived_field_transformer_for_where, - (uchar*) sl); + extracted_cond_copy= + transform_condition_or_part(thd, + extracted_cond_copy, + &Item::derived_field_transformer_for_where, + (uchar*)sl); if (extracted_cond_copy) { extracted_cond_copy->walk( @@ -1316,9 +1379,12 @@ bool pushdown_cond_for_derived(THD *thd, Item *cond, TABLE_LIST *derived) pushed into the where clause of sl to make them usable in the new context */ if (cond_over_grouping_fields) - cond_over_grouping_fields= cond_over_grouping_fields->transform(thd, - &Item::derived_grouping_field_transformer_for_where, - (uchar*) sl); + { + cond_over_grouping_fields= + transform_condition_or_part(thd, cond_over_grouping_fields, + &Item::derived_grouping_field_transformer_for_where, + (uchar*) sl); + } if (cond_over_grouping_fields) {

1 0

[Commits] 658cdcd3f2b: MDEV-25969: Condition pushdown into derived table doesn't work if select list uses SP
by psergey 29 Jun '21

29 Jun '21

revision-id: 658cdcd3f2bc0e2eb3e0cfc5efcb96b964da95eb (mariadb-10.2.39-33-g658cdcd3f2b) parent(s): 8147d2e6183a1a4a4f3db2884966f5da2d17678c author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-06-29 16:31:28 +0300 message: MDEV-25969: Condition pushdown into derived table doesn't work if select list uses SP Variant 3, the "handle failures at conversion" approach. 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 transform_condition_or_part() 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/r/derived_cond_pushdown.result | 146 ++++++++++++++++++++++++++++++ mysql-test/t/derived_cond_pushdown.test | 70 ++++++++++++++ sql/sql_derived.cc | 78 ++++++++++++++-- 3 files changed, 288 insertions(+), 6 deletions(-) diff --git a/mysql-test/r/derived_cond_pushdown.result b/mysql-test/r/derived_cond_pushdown.result index 28532ae88a4..f5ef3c3a84d 100644 --- a/mysql-test/r/derived_cond_pushdown.result +++ b/mysql-test/r/derived_cond_pushdown.result @@ -10673,4 +10673,150 @@ Warnings: Note 1003 select `v2`.`a` AS `a`,`v2`.`f` AS `f`,`v2`.`g` AS `g` from `test`.`v2` where `v2`.`f` = `v2`.`a` and `v2`.`g` = `v2`.`a` drop view v1,v2; drop table t1; +# +# 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, + "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; +# +# Another testcase, with pushdown through GROUP BY +# +create table t1 (a int, b int); +insert into t1 values (1,1),(2,2),(3,3); +create function f1(a int) returns int DETERMINISTIC return (a+1); +create view v2(a, a2, s) as +select a, f1(a), sum(b) from t1 group by a, f1(a); +# Here, +# "(s+1) > 10" will be pushed into HAVING +# "a > 1" will be pushed all the way to the table scan on t1 +# "a2>123" will be pushed into HAVING (as it refers to an SP call which +# prevents pushing it to the WHERE) +explain format=json +select * from v2 where (s+1) > 10 AND a > 1 and a2>123; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "<derived2>", + "access_type": "ALL", + "rows": 3, + "filtered": 100, + "attached_condition": "v2.s + 1 > 10 and v2.a > 1 and v2.a2 > 123", + "materialized": { + "query_block": { + "select_id": 2, + "having_condition": "s + 1 > 10 and a2 > 123", + "filesort": { + "sort_key": "t1.a, f1(t1.a)", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 3, + "filtered": 100, + "attached_condition": "t1.a > 1" + } + } + } + } + } + } + } +} +drop view v2; +drop function f1; +drop table t1; # End of 10.2 tests diff --git a/mysql-test/t/derived_cond_pushdown.test b/mysql-test/t/derived_cond_pushdown.test index 58f38ac1e5a..9b7658a725e 100644 --- a/mysql-test/t/derived_cond_pushdown.test +++ b/mysql-test/t/derived_cond_pushdown.test @@ -2237,4 +2237,74 @@ eval explain extended $q2; drop view v1,v2; drop table t1; +--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; + +--echo # +--echo # Another testcase, with pushdown through GROUP BY +--echo # +create table t1 (a int, b int); +insert into t1 values (1,1),(2,2),(3,3); + +create function f1(a int) returns int DETERMINISTIC return (a+1); + +create view v2(a, a2, s) as +select a, f1(a), sum(b) from t1 group by a, f1(a); + +--echo # Here, +--echo # "(s+1) > 10" will be pushed into HAVING +--echo # "a > 1" will be pushed all the way to the table scan on t1 +--echo # "a2>123" will be pushed into HAVING (as it refers to an SP call which +--echo # prevents pushing it to the WHERE) +explain format=json +select * from v2 where (s+1) > 10 AND a > 1 and a2>123; + +drop view v2; +drop function f1; +drop table t1; --echo # End of 10.2 tests diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc index 632baf4bc5b..4ee95eb0516 100644 --- a/sql/sql_derived.cc +++ b/sql/sql_derived.cc @@ -1192,6 +1192,67 @@ bool mysql_derived_reinit(THD *thd, LEX *lex, TABLE_LIST *derived) } +/* + @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 for some sub-condition, in this case we want to + convert the most restrictive part of the condition that's 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 *transform_condition_or_part(THD *thd, + Item *cond, + Item_transformer transformer, + uchar *arg) +{ + if (cond->type() != Item::COND_ITEM || + ((Item_cond*) cond)->functype() != Item_func::COND_AND_FUNC) + { + Item *new_item= cond->transform(thd, transformer, arg); + // Indicate that the condition is not pushable + if (!new_item) + cond->clear_extraction_flag(); + return new_item; + } + + 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) + { + // Indicate that the condition is not pushable + item->clear_extraction_flag(); + li.remove(); + } + else + li.replace(new_item); + } + + switch (((Item_cond*) cond)->argument_list()->elements) + { + case 0: + return NULL; + case 1: + return ((Item_cond*) cond)->argument_list()->head(); + default: + return cond; + } +} + + /** @brief Extract the condition depended on derived table/view and pushed it there @@ -1287,9 +1348,11 @@ bool pushdown_cond_for_derived(THD *thd, Item *cond, TABLE_LIST *derived) if (!sl->join->group_list && !sl->with_sum_func) { /* extracted_cond_copy is pushed into where of sl */ - extracted_cond_copy= extracted_cond_copy->transform(thd, - &Item::derived_field_transformer_for_where, - (uchar*) sl); + extracted_cond_copy= + transform_condition_or_part(thd, + extracted_cond_copy, + &Item::derived_field_transformer_for_where, + (uchar*)sl); if (extracted_cond_copy) { extracted_cond_copy->walk( @@ -1316,9 +1379,12 @@ bool pushdown_cond_for_derived(THD *thd, Item *cond, TABLE_LIST *derived) pushed into the where clause of sl to make them usable in the new context */ if (cond_over_grouping_fields) - cond_over_grouping_fields= cond_over_grouping_fields->transform(thd, - &Item::derived_grouping_field_transformer_for_where, - (uchar*) sl); + { + cond_over_grouping_fields= + transform_condition_or_part(thd, cond_over_grouping_fields, + &Item::derived_grouping_field_transformer_for_where, + (uchar*) sl); + } if (cond_over_grouping_fields) {

1 0

[Commits] 4e4f742: Adjusted test results after the fix for MDEV-20411 (2)
by IgorBabaev 27 Jun '21

27 Jun '21

revision-id: 4e4f742ed7987ee52a34618d2ea3731e5e198ed8 (mariadb-10.2.31-1027-g4e4f742) parent(s): 8b3f816cab30eadd574b975d11c9e525a0b1691e author: Igor Babaev committer: Igor Babaev timestamp: 2021-06-26 23:11:10 -0700 message: Adjusted test results after the fix for MDEV-20411 (2) --- mysql-test/suite/funcs_1/r/myisam_views-big.result | 8 ++++---- 1 file changed, 4 insertions(+), 4 deletions(-) diff --git a/mysql-test/suite/funcs_1/r/myisam_views-big.result b/mysql-test/suite/funcs_1/r/myisam_views-big.result index a1d59e1..d3227d8 100644 --- a/mysql-test/suite/funcs_1/r/myisam_views-big.result +++ b/mysql-test/suite/funcs_1/r/myisam_views-big.result @@ -4054,11 +4054,11 @@ CREATE VIEW v1 or REPLACE AS Select * from tb2 my_table; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'or REPLACE AS Select * from tb2 my_table' at line 1 CREATE VIEW v1 WITH CASCADED CHECK OPTION AS Select * from tb2 my_table limit 50; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'CASCADED CHECK OPTION AS Select * +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WITH CASCADED CHECK OPTION AS Select * from tb2 my_table limit 50' at line 1 CREATE VIEW v1 WITH LOCAL CHECK OPTION AS Select * from tb2 my_table limit 50; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'LOCAL CHECK OPTION AS Select * +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WITH LOCAL CHECK OPTION AS Select * from tb2 my_table limit 50' at line 1 SELECT * FROM tb2 my_table CREATE VIEW As v1; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'CREATE VIEW As v1' at line 1 @@ -4088,7 +4088,7 @@ FROM test.tb2 my_table CHECK OPTION WITH CASCADED; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'CHECK OPTION WITH CASCADED' at line 2 CREATE OR REPLACE VIEW v1 WITH CASCADED CHECK OPTION AS SELECT F59, F60 FROM test.tb2 my_table; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'CASCADED CHECK OPTION +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WITH CASCADED CHECK OPTION AS SELECT F59, F60 FROM test.tb2 my_table' at line 1 CREATE OR REPLACE AS SELECT F59, F60 FROM test.tb2 my_table VIEW v1 WITH CASCADED CHECK OPTION; @@ -4117,7 +4117,7 @@ FROM test.tb2 my_table CHECK OPTION WITH LOCAL; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'CHECK OPTION WITH LOCAL' at line 2 CREATE OR REPLACE VIEW v1 WITH CASCADED CHECK OPTION AS SELECT F59, F60 FROM test.tb2 my_table; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'CASCADED CHECK OPTION +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WITH CASCADED CHECK OPTION AS SELECT F59, F60 FROM test.tb2 my_table' at line 1 CREATE OR REPLACE AS SELECT F59, F60 FROM test.tb2 my_table VIEW v1 WITH LOCAL CHECK OPTION;

1 0

[Commits] 8b3f816: Adjusted test results after the fix for MDEV-20411
by IgorBabaev 26 Jun '21

26 Jun '21

revision-id: 8b3f816cab30eadd574b975d11c9e525a0b1691e (mariadb-10.2.31-1026-g8b3f816) parent(s): 12c80df4825955253a1a442098dbceb2a3e59971 author: Igor Babaev committer: Igor Babaev timestamp: 2021-06-26 08:51:17 -0700 message: Adjusted test results after the fix for MDEV-20411 --- mysql-test/suite/funcs_1/r/innodb_views.result | 8 ++++---- mysql-test/suite/funcs_1/r/memory_views.result | 8 ++++---- mysql-test/suite/funcs_1/r/storedproc.result | 11 +++++------ mysql-test/suite/innodb_fts/r/fulltext.result | 2 +- 4 files changed, 14 insertions(+), 15 deletions(-) diff --git a/mysql-test/suite/funcs_1/r/innodb_views.result b/mysql-test/suite/funcs_1/r/innodb_views.result index 1316988..5e4b008 100644 --- a/mysql-test/suite/funcs_1/r/innodb_views.result +++ b/mysql-test/suite/funcs_1/r/innodb_views.result @@ -3551,11 +3551,11 @@ CREATE VIEW v1 or REPLACE AS Select * from tb2 my_table; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'or REPLACE AS Select * from tb2 my_table' at line 1 CREATE VIEW v1 WITH CASCADED CHECK OPTION AS Select * from tb2 my_table limit 50; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'CASCADED CHECK OPTION AS Select * +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WITH CASCADED CHECK OPTION AS Select * from tb2 my_table limit 50' at line 1 CREATE VIEW v1 WITH LOCAL CHECK OPTION AS Select * from tb2 my_table limit 50; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'LOCAL CHECK OPTION AS Select * +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WITH LOCAL CHECK OPTION AS Select * from tb2 my_table limit 50' at line 1 SELECT * FROM tb2 my_table CREATE VIEW As v1; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'CREATE VIEW As v1' at line 1 @@ -3585,7 +3585,7 @@ FROM test.tb2 my_table CHECK OPTION WITH CASCADED; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'CHECK OPTION WITH CASCADED' at line 2 CREATE OR REPLACE VIEW v1 WITH CASCADED CHECK OPTION AS SELECT F59, F60 FROM test.tb2 my_table; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'CASCADED CHECK OPTION +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WITH CASCADED CHECK OPTION AS SELECT F59, F60 FROM test.tb2 my_table' at line 1 CREATE OR REPLACE AS SELECT F59, F60 FROM test.tb2 my_table VIEW v1 WITH CASCADED CHECK OPTION; @@ -3614,7 +3614,7 @@ FROM test.tb2 my_table CHECK OPTION WITH LOCAL; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'CHECK OPTION WITH LOCAL' at line 2 CREATE OR REPLACE VIEW v1 WITH CASCADED CHECK OPTION AS SELECT F59, F60 FROM test.tb2 my_table; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'CASCADED CHECK OPTION +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WITH CASCADED CHECK OPTION AS SELECT F59, F60 FROM test.tb2 my_table' at line 1 CREATE OR REPLACE AS SELECT F59, F60 FROM test.tb2 my_table VIEW v1 WITH LOCAL CHECK OPTION; diff --git a/mysql-test/suite/funcs_1/r/memory_views.result b/mysql-test/suite/funcs_1/r/memory_views.result index 9f3fbd7..acd0425 100644 --- a/mysql-test/suite/funcs_1/r/memory_views.result +++ b/mysql-test/suite/funcs_1/r/memory_views.result @@ -3552,11 +3552,11 @@ CREATE VIEW v1 or REPLACE AS Select * from tb2 my_table; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'or REPLACE AS Select * from tb2 my_table' at line 1 CREATE VIEW v1 WITH CASCADED CHECK OPTION AS Select * from tb2 my_table limit 50; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'CASCADED CHECK OPTION AS Select * +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WITH CASCADED CHECK OPTION AS Select * from tb2 my_table limit 50' at line 1 CREATE VIEW v1 WITH LOCAL CHECK OPTION AS Select * from tb2 my_table limit 50; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'LOCAL CHECK OPTION AS Select * +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WITH LOCAL CHECK OPTION AS Select * from tb2 my_table limit 50' at line 1 SELECT * FROM tb2 my_table CREATE VIEW As v1; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'CREATE VIEW As v1' at line 1 @@ -3586,7 +3586,7 @@ FROM test.tb2 my_table CHECK OPTION WITH CASCADED; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'CHECK OPTION WITH CASCADED' at line 2 CREATE OR REPLACE VIEW v1 WITH CASCADED CHECK OPTION AS SELECT F59, F60 FROM test.tb2 my_table; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'CASCADED CHECK OPTION +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WITH CASCADED CHECK OPTION AS SELECT F59, F60 FROM test.tb2 my_table' at line 1 CREATE OR REPLACE AS SELECT F59, F60 FROM test.tb2 my_table VIEW v1 WITH CASCADED CHECK OPTION; @@ -3615,7 +3615,7 @@ FROM test.tb2 my_table CHECK OPTION WITH LOCAL; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'CHECK OPTION WITH LOCAL' at line 2 CREATE OR REPLACE VIEW v1 WITH CASCADED CHECK OPTION AS SELECT F59, F60 FROM test.tb2 my_table; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'CASCADED CHECK OPTION +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WITH CASCADED CHECK OPTION AS SELECT F59, F60 FROM test.tb2 my_table' at line 1 CREATE OR REPLACE AS SELECT F59, F60 FROM test.tb2 my_table VIEW v1 WITH LOCAL CHECK OPTION; diff --git a/mysql-test/suite/funcs_1/r/storedproc.result b/mysql-test/suite/funcs_1/r/storedproc.result index 516ea98..64573ed 100644 --- a/mysql-test/suite/funcs_1/r/storedproc.result +++ b/mysql-test/suite/funcs_1/r/storedproc.result @@ -2807,7 +2807,7 @@ ERROR 42000: You have an error in your SQL syntax; check the manual that corresp SELECT * from t1 where f2=f1' at line 1 CREATE PROCEDURE with() SELECT * from t1 where f2=f1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '() +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'with() SELECT * from t1 where f2=f1' at line 1 CREATE PROCEDURE write() SELECT * from t1 where f2=f1; @@ -9219,7 +9219,7 @@ CREATE PROCEDURE sp1() BEGIN declare with char; END// -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'char; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'with char; END' at line 3 DROP PROCEDURE IF EXISTS sp1; Warnings: @@ -11547,9 +11547,8 @@ BEGIN declare with condition for sqlstate '02000'; declare exit handler for with set @var2 = 1; END// -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'condition for sqlstate '02000'; -declare exit handler for with set @var2 = 1; -END' at line 3 +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'with condition for sqlstate '02000'; +declare exit handler for with set @var2 ...' at line 3 DROP PROCEDURE IF EXISTS sp1; Warnings: Note 1305 PROCEDURE db_storedproc.sp1 does not exist @@ -13649,7 +13648,7 @@ CREATE PROCEDURE sp1( ) BEGIN declare with handler for sqlstate '02000' set @var2 = 1; END// -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'handler for sqlstate '02000' set @var2 = 1; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'with handler for sqlstate '02000' set @var2 = 1; END' at line 3 DROP PROCEDURE IF EXISTS sp1; Warnings: diff --git a/mysql-test/suite/innodb_fts/r/fulltext.result b/mysql-test/suite/innodb_fts/r/fulltext.result index 90deb48..1e5a83f 100644 --- a/mysql-test/suite/innodb_fts/r/fulltext.result +++ b/mysql-test/suite/innodb_fts/r/fulltext.result @@ -56,7 +56,7 @@ Only MyISAM tables support collections MySQL has now support for full-text search Full-text search in MySQL implements vector space model select * from t1 where MATCH(a,b) AGAINST ("indexes" IN BOOLEAN MODE WITH QUERY EXPANSION); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'QUERY EXPANSION)' at line 1 +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WITH QUERY EXPANSION)' at line 1 explain select * from t1 where MATCH(a,b) AGAINST ("collections"); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 fulltext a a 0 1 Using where

1 0

[Commits] 2a2b17c: MDEV-20411 Procedure containing CTE incorrectly stored in mysql.proc
by IgorBabaev 26 Jun '21

26 Jun '21

revision-id: 2a2b17ce6dd1a61cd6d26af8211dc93c339956f1 (mariadb-10.2.31-1024-g2a2b17c) parent(s): 9258cfa4b469ab0d841e32ced7e501e04043637f author: Igor Babaev committer: Igor Babaev timestamp: 2021-06-25 18:03:29 -0700 message: MDEV-20411 Procedure containing CTE incorrectly stored in mysql.proc If the first token of the body of a stored procedure was 'WITH' then the beginning of the body was determined incorrectly and that token was missing in the string representing the body of the SP in mysql.proc. As a resultnany call of such procedure failed as the string representing the body could not be parsed. The patch corrects the code of the functions get_tok_start() and get_cpp_tok_start() of the class Lex_input_stream to make them take into account look ahead tokens. The patch is needed only for 10.2 as this problem has neen resolved in 10.3+. --- mysql-test/r/cte_nonrecursive.result | 55 ++++++++++++++++++++++++++++++++++++ mysql-test/r/fulltext.result | 2 +- mysql-test/t/cte_nonrecursive.test | 29 +++++++++++++++++++ sql/sql_lex.h | 4 +-- 4 files changed, 87 insertions(+), 3 deletions(-) diff --git a/mysql-test/r/cte_nonrecursive.result b/mysql-test/r/cte_nonrecursive.result index 7c6c6e8..c1d7fd0 100644 --- a/mysql-test/r/cte_nonrecursive.result +++ b/mysql-test/r/cte_nonrecursive.result @@ -1964,4 +1964,59 @@ call p1(); ERROR 42S22: Unknown column 'a' in 'field list' drop procedure p1; drop table t1,t2; +# +# MDEV-20411: SP containing only one SELECT with WITH clause +# +create procedure sp1 () +with cte as (select 1 as a) select * from cte; +call sp1(); +a +1 +call sp1(); +a +1 +create table t1 (a int); +insert into t1 values (3), (7), (1), (7), (1), (1), (3), (1), (5); +create procedure sp2 () +with cte as (select * from t1) select * from cte; +call sp2(); +a +3 +7 +1 +7 +1 +1 +3 +1 +5 +call sp2(); +a +3 +7 +1 +7 +1 +1 +3 +1 +5 +create procedure sp3 () +with cte as (select * from t1 group by a) select * from cte; +call sp3(); +a +1 +3 +5 +7 +call sp3(); +a +1 +3 +5 +7 +drop procedure sp1; +drop procedure sp2; +drop procedure sp3; +drop table t1; # End of 10.2 tests diff --git a/mysql-test/r/fulltext.result b/mysql-test/r/fulltext.result index 5118cc3..709e91c 100644 --- a/mysql-test/r/fulltext.result +++ b/mysql-test/r/fulltext.result @@ -49,7 +49,7 @@ a b Full-text indexes are called collections Only MyISAM tables support collections select * from t1 where MATCH(a,b) AGAINST ("indexes" IN BOOLEAN MODE WITH QUERY EXPANSION); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'QUERY EXPANSION)' at line 1 +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WITH QUERY EXPANSION)' at line 1 explain select * from t1 where MATCH(a,b) AGAINST ("collections"); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 fulltext a a 0 1 Using where diff --git a/mysql-test/t/cte_nonrecursive.test b/mysql-test/t/cte_nonrecursive.test index f994781..cbe4f8b 100644 --- a/mysql-test/t/cte_nonrecursive.test +++ b/mysql-test/t/cte_nonrecursive.test @@ -1463,4 +1463,33 @@ drop procedure p1; drop table t1,t2; + +--echo # +--echo # MDEV-20411: SP containing only one SELECT with WITH clause +--echo # + +create procedure sp1 () +with cte as (select 1 as a) select * from cte; +call sp1(); +call sp1(); + +create table t1 (a int); +insert into t1 values (3), (7), (1), (7), (1), (1), (3), (1), (5); + +create procedure sp2 () +with cte as (select * from t1) select * from cte; +call sp2(); +call sp2(); + +create procedure sp3 () +with cte as (select * from t1 group by a) select * from cte; +call sp3(); +call sp3(); + +drop procedure sp1; +drop procedure sp2; +drop procedure sp3; + +drop table t1; + --echo # End of 10.2 tests diff --git a/sql/sql_lex.h b/sql/sql_lex.h index 03c06b9..bdf52e8 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -2176,7 +2176,7 @@ class Lex_input_stream /** Get the token start position, in the raw buffer. */ const char *get_tok_start() { - return m_tok_start; + return lookahead_token >= 0 ? m_tok_start_prev : m_tok_start; } void set_cpp_tok_start(const char *pos) @@ -2222,7 +2222,7 @@ class Lex_input_stream /** Get the token start position, in the pre-processed buffer. */ const char *get_cpp_tok_start() { - return m_cpp_tok_start; + return lookahead_token >= 0 ? m_cpp_tok_start_prev : m_cpp_tok_start; } /** Get the token end position, in the pre-processed buffer. */

1 0

[Commits] 4f6ac2d3bf0: MDEV-25969: Condition pushdown into derived table doesn't work if select list uses SP
by Sergei Petrunia 25 Jun '21

25 Jun '21

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(

1 0

[Commits] 521c546a71c: MDEV-25969: Condition pushdown into derived table doesn't work if select list uses SP
by Sergei Petrunia 24 Jun '21

24 Jun '21

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

1 0

[Commits] 7f24e37: MDEV-25679 Wrong result selecting from simple view with LIMIT and ORDER BY
by IgorBabaev 22 Jun '21

22 Jun '21

revision-id: 7f24e37fbecd5af08ec473a7a1449f305977c6d7 (mariadb-10.4.11-659-g7f24e37) parent(s): ce868cd89e352f7ce04d8db260f96893df2bf26c author: Igor Babaev committer: Igor Babaev timestamp: 2021-06-22 12:23:13 -0700 message: MDEV-25679 Wrong result selecting from simple view with LIMIT and ORDER BY Cherry-picking only test case. --- mysql-test/main/derived_view.result | 21 +++++++++++++++++++++ mysql-test/main/derived_view.test | 17 +++++++++++++++++ 2 files changed, 38 insertions(+) diff --git a/mysql-test/main/derived_view.result b/mysql-test/main/derived_view.result index 00940d8..f761a9f 100644 --- a/mysql-test/main/derived_view.result +++ b/mysql-test/main/derived_view.result @@ -3480,3 +3480,24 @@ Warnings: Note 1003 select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`a` AS `a`,3 AS `d`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`c` AS `c` from `test`.`t1` join `test`.`t2` join `test`.`t3` where `test`.`t1`.`a` = 3 and `test`.`t1`.`pk` <= 2 drop view v1; drop table t1,t2,t3; +# +# MDEV-25679: view / derived table defined as ordered select with LIMIT +# +create table t1 (a int); +insert into t1 values (3), (7), (1); +create view v1 as (select a from t1 limit 2) order by a desc; +(select a from t1 limit 2) order by a desc; +a +7 +3 +select * from v1; +a +7 +3 +select * from ((select a from t1 limit 2) order by a desc) dt; +a +3 +7 +drop view v1; +drop table t1; +# End of 10.2 tests diff --git a/mysql-test/main/derived_view.test b/mysql-test/main/derived_view.test index 76b15fa..9ab1ddd 100644 --- a/mysql-test/main/derived_view.test +++ b/mysql-test/main/derived_view.test @@ -2273,3 +2273,20 @@ eval explain extended $q; drop view v1; drop table t1,t2,t3; + +--echo # +--echo # MDEV-25679: view / derived table defined as ordered select with LIMIT +--echo # + +create table t1 (a int); +insert into t1 values (3), (7), (1); + +create view v1 as (select a from t1 limit 2) order by a desc; +(select a from t1 limit 2) order by a desc; +select * from v1; +select * from ((select a from t1 limit 2) order by a desc) dt; + +drop view v1; +drop table t1; + +--echo # End of 10.2 tests

1 0

[Commits] 883d9a6: MDEV-25679 Wrong result selecting from simple view with LIMIT and ORDER BY
by IgorBabaev 22 Jun '21

22 Jun '21

revision-id: 883d9a66ccdef5b32876d24377d327b61e1a12c0 (mariadb-10.4.11-659-g883d9a6) parent(s): ce868cd89e352f7ce04d8db260f96893df2bf26c author: Igor Babaev committer: Igor Babaev timestamp: 2021-06-22 10:58:06 -0700 message: MDEV-25679 Wrong result selecting from simple view with LIMIT and ORDER BY This bug affected queries with views / derived_tables / CTEs whose specifications were of the form (SELECT ... LIMIT <n>) ORDER BY ... Units representing such specifications contains one SELECT_LEX structure for (SELECT ... LIMIT <n>) and additionally SELECT_LEX structure for fake_select_lex. This fact should have been taken into account in the function mysql_derived_fill(). This patch has to be applied to 10.2 and 10.3 only. --- mysql-test/main/derived_view.result | 21 +++++++++++++++++++++ mysql-test/main/derived_view.test | 17 +++++++++++++++++ sql/sql_derived.cc | 2 +- 3 files changed, 39 insertions(+), 1 deletion(-) diff --git a/mysql-test/main/derived_view.result b/mysql-test/main/derived_view.result index 00940d8..040f291 100644 --- a/mysql-test/main/derived_view.result +++ b/mysql-test/main/derived_view.result @@ -3480,3 +3480,24 @@ Warnings: Note 1003 select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`a` AS `a`,3 AS `d`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`c` AS `c` from `test`.`t1` join `test`.`t2` join `test`.`t3` where `test`.`t1`.`a` = 3 and `test`.`t1`.`pk` <= 2 drop view v1; drop table t1,t2,t3; +# +# MDEV-25679: view / derived table defined as ordered select with LIMIT +# +create table t1 (a int); +insert into t1 values (3), (7), (1); +create view v1 as (select a from t1 limit 2) order by a desc; +(select a from t1 limit 2) order by a desc; +a +7 +3 +select * from v1; +a +7 +3 +select * from ((select a from t1 limit 2) order by a desc) dt; +a +7 +3 +drop view v1; +drop table t1; +# End of 10.2 tests diff --git a/mysql-test/main/derived_view.test b/mysql-test/main/derived_view.test index 76b15fa..9ab1ddd 100644 --- a/mysql-test/main/derived_view.test +++ b/mysql-test/main/derived_view.test @@ -2273,3 +2273,20 @@ eval explain extended $q; drop view v1; drop table t1,t2,t3; + +--echo # +--echo # MDEV-25679: view / derived table defined as ordered select with LIMIT +--echo # + +create table t1 (a int); +insert into t1 values (3), (7), (1); + +create view v1 as (select a from t1 limit 2) order by a desc; +(select a from t1 limit 2) order by a desc; +select * from v1; +select * from ((select a from t1 limit 2) order by a desc) dt; + +drop view v1; +drop table t1; + +--echo # End of 10.2 tests diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc index fc01dcd..8e6c8db 100644 --- a/sql/sql_derived.cc +++ b/sql/sql_derived.cc @@ -1262,7 +1262,7 @@ bool mysql_derived_fill(THD *thd, LEX *lex, TABLE_LIST *derived) res= derived->fill_recursive(thd); } } - else if (unit->is_unit_op()) + else if (unit->is_unit_op() || unit->fake_select_lex) { // execute union without clean up res= unit->exec();

1 0

[Commits] 6e94ef4: MDEV-25679 Wrong result selecting from simple view with LIMIT and ORDER BY
by IgorBabaev 22 Jun '21

22 Jun '21

revision-id: 6e94ef41859cc628f92224e33dd661d9a4be215b (mariadb-10.3.26-183-g6e94ef4) parent(s): 9dc50ea2293640f8153bc806612fd72634498010 author: Igor Babaev committer: Igor Babaev timestamp: 2021-06-21 22:25:37 -0700 message: MDEV-25679 Wrong result selecting from simple view with LIMIT and ORDER BY This bug affected queries with views / derived_tables / CTEs whose specifications were of the form (SELECT ... LIMIT <n>) ORDER BY ... Units representing such specifications contains one SELECT_LEX structure for (SELECT ... LIMIT <n>) and additionally SELECT_LEX structure for fake_select_lex. This fact should have been taken into account in the function mysql_derived_fill(). This patch has to be applied to 10.2 and 10.3 only. --- mysql-test/main/derived_view.result | 21 +++++++++++++++++++++ mysql-test/main/derived_view.test | 17 +++++++++++++++++ sql/sql_derived.cc | 2 +- 3 files changed, 39 insertions(+), 1 deletion(-) diff --git a/mysql-test/main/derived_view.result b/mysql-test/main/derived_view.result index dd0cac7..65a1adc 100644 --- a/mysql-test/main/derived_view.result +++ b/mysql-test/main/derived_view.result @@ -3478,3 +3478,24 @@ Warnings: Note 1003 select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`a` AS `a`,3 AS `d`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`c` AS `c` from `test`.`t1` join `test`.`t2` join `test`.`t3` where `test`.`t1`.`a` = 3 and `test`.`t1`.`pk` <= 2 drop view v1; drop table t1,t2,t3; +# +# MDEV-25679: view / derived table defined as ordered select with LIMIT +# +create table t1 (a int); +insert into t1 values (3), (7), (1); +create view v1 as (select a from t1 limit 2) order by a desc; +(select a from t1 limit 2) order by a desc; +a +7 +3 +select * from v1; +a +7 +3 +select * from ((select a from t1 limit 2) order by a desc) dt; +a +7 +3 +drop view v1; +drop table t1; +# End of 10.2 tests diff --git a/mysql-test/main/derived_view.test b/mysql-test/main/derived_view.test index 76b15fa..9ab1ddd 100644 --- a/mysql-test/main/derived_view.test +++ b/mysql-test/main/derived_view.test @@ -2273,3 +2273,20 @@ eval explain extended $q; drop view v1; drop table t1,t2,t3; + +--echo # +--echo # MDEV-25679: view / derived table defined as ordered select with LIMIT +--echo # + +create table t1 (a int); +insert into t1 values (3), (7), (1); + +create view v1 as (select a from t1 limit 2) order by a desc; +(select a from t1 limit 2) order by a desc; +select * from v1; +select * from ((select a from t1 limit 2) order by a desc) dt; + +drop view v1; +drop table t1; + +--echo # End of 10.2 tests diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc index e2e30f3..8f10013 100644 --- a/sql/sql_derived.cc +++ b/sql/sql_derived.cc @@ -1163,7 +1163,7 @@ bool mysql_derived_fill(THD *thd, LEX *lex, TABLE_LIST *derived) res= derived->fill_recursive(thd); } } - else if (unit->is_unit_op()) + else if (unit->is_unit_op() || unit->fake_select_lex) { // execute union without clean up res= unit->exec();

1 0