- commits - lists.mariadb.org

[Commits] f001477: MDEV-25714 Join using derived with aggregation returns incorrect results
by IgorBabaev 04 Jun '21

04 Jun '21

revision-id: f001477f6cfe9ba5e3c3a97b59ce15e485eb9a16 (mariadb-10.3.12-228-gf001477) parent(s): 490dcfd5d74ce45cbdd6105e4295116f6a944e2b author: Igor Babaev committer: Igor Babaev timestamp: 2021-06-04 14:27:11 -0700 message: MDEV-25714 Join using derived with aggregation returns incorrect results If a join query uses a derived table (view / CTE) with GROUP BY clause then the execution plan for such join may employ split optimization. When this optimization is employed the derived table is not materialized. Rather only some partitions of the derived table are subject to grouping. Split optimization can be applied only if: - there are some indexes over the tables used in the join specifying the derived table whose prefixes partially cover the field items used in the GROUP BY list (such indexes are called splitting indexes) - the WHERE condition of the join query contains conjunctive equalities between columns of the derived table that comprise major parts of splitting indexes and columns of the other join tables. When the optimizer evaluates extending of a partial join by the rows of the derived table it always considers a possibility of using split optimization. Different splitting indexes can be used depending on the extended partial join. At some rare conditions, for example, when there is a non-splitting covering index for a table joined in the join specifying the derived table usage of a splitting index to produce rows needed for grouping may be still less beneficial than usage of such covering index without any splitting technique. The function JOIN_TAB::choose_best_splitting() must take this into account. Approved by Oleksandr Byelkin <sanja(a)mariadb.com> --- mysql-test/main/derived_cond_pushdown.result | 2 +- mysql-test/main/derived_split_innodb.result | 61 ++++++++++++++++++++++++++++ mysql-test/main/derived_split_innodb.test | 37 +++++++++++++++++ sql/opt_split.cc | 27 +++++++++--- 4 files changed, 121 insertions(+), 6 deletions(-) diff --git a/mysql-test/main/derived_cond_pushdown.result b/mysql-test/main/derived_cond_pushdown.result index 4474cb8..2b7aa8a 100644 --- a/mysql-test/main/derived_cond_pushdown.result +++ b/mysql-test/main/derived_cond_pushdown.result @@ -16620,7 +16620,7 @@ EXPLAIN EXTENDED SELECT * FROM v1 JOIN v2 ON v1.f = v2.f; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -3 LATERAL DERIVED NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table +3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table Warnings: Note 1003 /* select#1 */ select NULL AS `f`,`v2`.`f` AS `f` from `test`.`t1` `a` straight_join `test`.`t1` `b` join `test`.`v2` where 0 DROP VIEW v1,v2; diff --git a/mysql-test/main/derived_split_innodb.result b/mysql-test/main/derived_split_innodb.result index b9ed016..84d84a8 100644 --- a/mysql-test/main/derived_split_innodb.result +++ b/mysql-test/main/derived_split_innodb.result @@ -140,3 +140,64 @@ id select_type table type possible_keys key key_len ref rows Extra 2 DERIVED t2 index NULL PRIMARY 4 NULL 3 drop view v1; drop table t1,t2; +# +# Bug mdev-25714: usage non-splitting covering index is cheaper than +# usage of the best splitting index for one group +# +create table t1 ( +id int not null, itemid int not null, index idx (itemid) +) engine=innodb; +insert into t1 values (1, 2), (2,2), (4,2), (4,2), (0,3), (3,3); +create table t2 (id int not null) engine=innodb; +insert into t2 values (2); +create table t3 ( +id int not null, itemid int not null, userid int not null, primary key (id), +index idx1 (userid, itemid), index idx2 (itemid) +) engine innodb; +insert into t3 values (1,1,1), (2,1,1), (3,2,1), (4,2,1), (5,3,1); +analyze table t1,t2,t3; +Table Op Msg_type Msg_text +test.t1 analyze status OK +test.t2 analyze status OK +test.t3 analyze status OK +set optimizer_switch='split_materialized=on'; +explain select t1.id, t1.itemid, dt.id, t2.id +from t1, +(select itemid, max(id) as id from t3 where userid = 1 group by itemid) dt, +t2 +where t1.id = dt.id and t1.itemid = dt.itemid and t2.id=t1.itemid; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 1 +1 PRIMARY t1 ref idx idx 4 test.t2.id 1 +1 PRIMARY <derived2> ref key0 key0 9 test.t2.id,test.t1.id 2 +2 DERIVED t3 ref idx1,idx2 idx1 4 const 5 Using where; Using index +select t1.id, t1.itemid, dt.id, t2.id +from t1, +(select itemid, max(id) as id from t3 where userid = 1 group by itemid) dt, +t2 +where t1.id = dt.id and t1.itemid = dt.itemid and t2.id=t1.itemid; +id itemid id id +4 2 4 2 +4 2 4 2 +set optimizer_switch='split_materialized=off'; +explain select t1.id, t1.itemid, dt.id, t2.id +from t1, +(select itemid, max(id) as id from t3 where userid = 1 group by itemid) dt, +t2 +where t1.id = dt.id and t1.itemid = dt.itemid and t2.id=t1.itemid; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 1 +1 PRIMARY t1 ref idx idx 4 test.t2.id 1 +1 PRIMARY <derived2> ref key0 key0 9 test.t2.id,test.t1.id 2 +2 DERIVED t3 ref idx1 idx1 4 const 5 Using where; Using index +select t1.id, t1.itemid, dt.id, t2.id +from t1, +(select itemid, max(id) as id from t3 where userid = 1 group by itemid) dt, +t2 +where t1.id = dt.id and t1.itemid = dt.itemid and t2.id=t1.itemid; +id itemid id id +4 2 4 2 +4 2 4 2 +drop table t1,t2,t3; +set optimizer_switch='split_materialized=default'; +# End of 10.3 tests diff --git a/mysql-test/main/derived_split_innodb.test b/mysql-test/main/derived_split_innodb.test index 1bf70cd..4f2c241 100644 --- a/mysql-test/main/derived_split_innodb.test +++ b/mysql-test/main/derived_split_innodb.test @@ -123,3 +123,40 @@ eval set statement optimizer_switch='split_materialized=off' for explain $q; drop view v1; drop table t1,t2; +--echo # +--echo # Bug mdev-25714: usage non-splitting covering index is cheaper than +--echo # usage of the best splitting index for one group +--echo # + +create table t1 ( + id int not null, itemid int not null, index idx (itemid) +) engine=innodb; +insert into t1 values (1, 2), (2,2), (4,2), (4,2), (0,3), (3,3); +create table t2 (id int not null) engine=innodb; +insert into t2 values (2); +create table t3 ( + id int not null, itemid int not null, userid int not null, primary key (id), + index idx1 (userid, itemid), index idx2 (itemid) +) engine innodb; +insert into t3 values (1,1,1), (2,1,1), (3,2,1), (4,2,1), (5,3,1); +analyze table t1,t2,t3; + +let $q= +select t1.id, t1.itemid, dt.id, t2.id + from t1, + (select itemid, max(id) as id from t3 where userid = 1 group by itemid) dt, + t2 + where t1.id = dt.id and t1.itemid = dt.itemid and t2.id=t1.itemid; + +set optimizer_switch='split_materialized=on'; +eval explain $q; +eval $q; + +set optimizer_switch='split_materialized=off'; +eval explain $q; +eval $q; + +drop table t1,t2,t3; +set optimizer_switch='split_materialized=default'; + +--echo # End of 10.3 tests diff --git a/sql/opt_split.cc b/sql/opt_split.cc index cfac0c9..e53bab9 100644 --- a/sql/opt_split.cc +++ b/sql/opt_split.cc @@ -949,11 +949,7 @@ SplM_plan_info * JOIN_TAB::choose_best_splitting(double record_count, in the cache */ spl_plan= spl_opt_info->find_plan(best_table, best_key, best_key_parts); - if (!spl_plan && - (spl_plan= (SplM_plan_info *) thd->alloc(sizeof(SplM_plan_info))) && - (spl_plan->best_positions= - (POSITION *) thd->alloc(sizeof(POSITION) * join->table_count)) && - !spl_opt_info->plan_cache.push_back(spl_plan)) + if (!spl_plan) { /* The plan for the chosen key has not been found in the cache. @@ -963,6 +959,27 @@ SplM_plan_info * JOIN_TAB::choose_best_splitting(double record_count, reset_validity_vars_for_keyuses(best_key_keyuse_ext_start, best_table, best_key, remaining_tables, true); choose_plan(join, all_table_map & ~join->const_table_map); + + /* + Check that the chosen plan is really a splitting plan. + If not or if there is not enough memory to save the plan in the cache + then just return with no splitting plan. + */ + POSITION *first_non_const_pos= join->best_positions + join->const_tables; + TABLE *table= first_non_const_pos->table->table; + key_map spl_keys= table->keys_usable_for_splitting; + if (!(first_non_const_pos->key && + spl_keys.is_set(first_non_const_pos->key->key)) || + !(spl_plan= (SplM_plan_info *) thd->alloc(sizeof(SplM_plan_info))) || + !(spl_plan->best_positions= + (POSITION *) thd->alloc(sizeof(POSITION) * join->table_count)) || + spl_opt_info->plan_cache.push_back(spl_plan)) + { + reset_validity_vars_for_keyuses(best_key_keyuse_ext_start, best_table, + best_key, remaining_tables, false); + return 0; + } + spl_plan->keyuse_ext_start= best_key_keyuse_ext_start; spl_plan->table= best_table; spl_plan->key= best_key;

1 0

[Commits] 385f431: Corrected the test case of MDEV-25714 in order to have the same EXPLAIN output as in 10.3
by IgorBabaev 04 Jun '21

04 Jun '21

revision-id: 385f4316c3b8250943383145c115000ee153e3de (mariadb-10.4.11-639-g385f431) parent(s): 0b797130c674a4dd8b8dcf35d3ade38353e19284 author: Igor Babaev committer: Igor Babaev timestamp: 2021-06-03 20:43:04 -0700 message: Corrected the test case of MDEV-25714 in order to have the same EXPLAIN output as in 10.3 --- mysql-test/main/derived_split_innodb.result | 4 ++++ mysql-test/main/derived_split_innodb.test | 4 ++++ 2 files changed, 8 insertions(+) diff --git a/mysql-test/main/derived_split_innodb.result b/mysql-test/main/derived_split_innodb.result index 6119145..63db8c9 100644 --- a/mysql-test/main/derived_split_innodb.result +++ b/mysql-test/main/derived_split_innodb.result @@ -190,6 +190,8 @@ id int not null, itemid int not null, userid int not null, primary key (id), index idx1 (userid, itemid), index idx2 (itemid) ) engine innodb; insert into t3 values (1,1,1), (2,1,1), (3,2,1), (4,2,1), (5,3,1); +set use_stat_tables='never'; +set optimizer_use_condition_selectivity=1; analyze table t1,t2,t3; Table Op Msg_type Msg_text test.t1 analyze status OK @@ -235,4 +237,6 @@ id itemid id id 4 2 4 2 drop table t1,t2,t3; set optimizer_switch='split_materialized=default'; +set use_stat_tables=default; +set optimizer_use_condition_selectivity=default; # End of 10.3 tests diff --git a/mysql-test/main/derived_split_innodb.test b/mysql-test/main/derived_split_innodb.test index 6f33c71..22793b4 100644 --- a/mysql-test/main/derived_split_innodb.test +++ b/mysql-test/main/derived_split_innodb.test @@ -166,6 +166,8 @@ create table t3 ( index idx1 (userid, itemid), index idx2 (itemid) ) engine innodb; insert into t3 values (1,1,1), (2,1,1), (3,2,1), (4,2,1), (5,3,1); +set use_stat_tables='never'; +set optimizer_use_condition_selectivity=1; analyze table t1,t2,t3; let $q= @@ -185,5 +187,7 @@ eval $q; drop table t1,t2,t3; set optimizer_switch='split_materialized=default'; +set use_stat_tables=default; +set optimizer_use_condition_selectivity=default; --echo # End of 10.3 tests

1 0

[Commits] 0b79713: MDEV-25714 Join using derived with aggregation returns incorrect results
by IgorBabaev 03 Jun '21

03 Jun '21

revision-id: 0b797130c674a4dd8b8dcf35d3ade38353e19284 (mariadb-10.4.11-638-g0b79713) parent(s): fa0bbff032bc5717715fdf32ce4c8ebdfcf73944 author: Igor Babaev committer: Igor Babaev timestamp: 2021-06-03 14:44:03 -0700 message: MDEV-25714 Join using derived with aggregation returns incorrect results If a join query uses a derived table (view / CTE) with GROUP BY clause then the execution plan for such join may employ split optimization. When this optimization is employed the derived table is not materialized. Rather only some partitions of the derived table are subject to grouping. Split optimization can be applied only if: - there are some indexes over the tables used in the join specifying the derived table whose prefixes partially cover the field items used in the GROUP BY list (such indexes are called splitting indexes) - the WHERE condition of the join query contains conjunctive equalities between columns of the derived table that comprise major parts of splitting indexes and columns of the other join tables. When the optimizer evaluates extending of a partial join by the rows of the derived table it always considers a possibility of using split optimization. Different splitting indexes can be used depending on the extended partial join. At some rare conditions, for example, when there is a non-splitting covering index for a table joined in the join specifying the derived table usage of a splitting index to produce rows needed for grouping may be still less beneficial than usage of such covering index without any splitting technique. The function JOIN_TAB::choose_best_splitting() must take this into account. Approved by Oleksandr Byelkin <sanja(a)mariadb.com> --- mysql-test/main/derived_cond_pushdown.result | 2 +- mysql-test/main/derived_split_innodb.result | 61 ++++++++++++++++++++++++++++ mysql-test/main/derived_split_innodb.test | 37 +++++++++++++++++ sql/opt_split.cc | 27 +++++++++--- 4 files changed, 121 insertions(+), 6 deletions(-) diff --git a/mysql-test/main/derived_cond_pushdown.result b/mysql-test/main/derived_cond_pushdown.result index 7644e65..52d8ccd 100644 --- a/mysql-test/main/derived_cond_pushdown.result +++ b/mysql-test/main/derived_cond_pushdown.result @@ -16703,7 +16703,7 @@ EXPLAIN EXTENDED SELECT * FROM v1 JOIN v2 ON v1.f = v2.f; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -3 LATERAL DERIVED NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table +3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table Warnings: Note 1003 /* select#1 */ select NULL AS `f`,`v2`.`f` AS `f` from `test`.`t1` `a` straight_join `test`.`t1` `b` join `test`.`v2` where 0 DROP VIEW v1,v2; diff --git a/mysql-test/main/derived_split_innodb.result b/mysql-test/main/derived_split_innodb.result index ff71b7d..6119145 100644 --- a/mysql-test/main/derived_split_innodb.result +++ b/mysql-test/main/derived_split_innodb.result @@ -175,3 +175,64 @@ id select_type table type possible_keys key key_len ref rows Extra 3 LATERAL DERIVED t1 ref a,a_2 a 5 test.t1.a 1 Using where; Using temporary; Using filesort 3 LATERAL DERIVED t2 ref c c 5 test.t1.b 1 Using index DROP TABLE t1, t2; +# +# Bug mdev-25714: usage non-splitting covering index is cheaper than +# usage of the best splitting index for one group +# +create table t1 ( +id int not null, itemid int not null, index idx (itemid) +) engine=innodb; +insert into t1 values (1, 2), (2,2), (4,2), (4,2), (0,3), (3,3); +create table t2 (id int not null) engine=innodb; +insert into t2 values (2); +create table t3 ( +id int not null, itemid int not null, userid int not null, primary key (id), +index idx1 (userid, itemid), index idx2 (itemid) +) engine innodb; +insert into t3 values (1,1,1), (2,1,1), (3,2,1), (4,2,1), (5,3,1); +analyze table t1,t2,t3; +Table Op Msg_type Msg_text +test.t1 analyze status OK +test.t2 analyze status OK +test.t3 analyze status OK +set optimizer_switch='split_materialized=on'; +explain select t1.id, t1.itemid, dt.id, t2.id +from t1, +(select itemid, max(id) as id from t3 where userid = 1 group by itemid) dt, +t2 +where t1.id = dt.id and t1.itemid = dt.itemid and t2.id=t1.itemid; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 1 +1 PRIMARY t1 ref idx idx 4 test.t2.id 1 +1 PRIMARY <derived2> ref key0 key0 9 test.t2.id,test.t1.id 2 +2 DERIVED t3 ref idx1,idx2 idx1 4 const 5 Using where; Using index +select t1.id, t1.itemid, dt.id, t2.id +from t1, +(select itemid, max(id) as id from t3 where userid = 1 group by itemid) dt, +t2 +where t1.id = dt.id and t1.itemid = dt.itemid and t2.id=t1.itemid; +id itemid id id +4 2 4 2 +4 2 4 2 +set optimizer_switch='split_materialized=off'; +explain select t1.id, t1.itemid, dt.id, t2.id +from t1, +(select itemid, max(id) as id from t3 where userid = 1 group by itemid) dt, +t2 +where t1.id = dt.id and t1.itemid = dt.itemid and t2.id=t1.itemid; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 1 +1 PRIMARY t1 ref idx idx 4 test.t2.id 1 +1 PRIMARY <derived2> ref key0 key0 9 test.t2.id,test.t1.id 2 +2 DERIVED t3 ref idx1 idx1 4 const 5 Using where; Using index +select t1.id, t1.itemid, dt.id, t2.id +from t1, +(select itemid, max(id) as id from t3 where userid = 1 group by itemid) dt, +t2 +where t1.id = dt.id and t1.itemid = dt.itemid and t2.id=t1.itemid; +id itemid id id +4 2 4 2 +4 2 4 2 +drop table t1,t2,t3; +set optimizer_switch='split_materialized=default'; +# End of 10.3 tests diff --git a/mysql-test/main/derived_split_innodb.test b/mysql-test/main/derived_split_innodb.test index 19a6ecf..6f33c71 100644 --- a/mysql-test/main/derived_split_innodb.test +++ b/mysql-test/main/derived_split_innodb.test @@ -150,3 +150,40 @@ eval set statement optimizer_switch='split_materialized=on' for $query; DROP TABLE t1, t2; +--echo # +--echo # Bug mdev-25714: usage non-splitting covering index is cheaper than +--echo # usage of the best splitting index for one group +--echo # + +create table t1 ( + id int not null, itemid int not null, index idx (itemid) +) engine=innodb; +insert into t1 values (1, 2), (2,2), (4,2), (4,2), (0,3), (3,3); +create table t2 (id int not null) engine=innodb; +insert into t2 values (2); +create table t3 ( + id int not null, itemid int not null, userid int not null, primary key (id), + index idx1 (userid, itemid), index idx2 (itemid) +) engine innodb; +insert into t3 values (1,1,1), (2,1,1), (3,2,1), (4,2,1), (5,3,1); +analyze table t1,t2,t3; + +let $q= +select t1.id, t1.itemid, dt.id, t2.id + from t1, + (select itemid, max(id) as id from t3 where userid = 1 group by itemid) dt, + t2 + where t1.id = dt.id and t1.itemid = dt.itemid and t2.id=t1.itemid; + +set optimizer_switch='split_materialized=on'; +eval explain $q; +eval $q; + +set optimizer_switch='split_materialized=off'; +eval explain $q; +eval $q; + +drop table t1,t2,t3; +set optimizer_switch='split_materialized=default'; + +--echo # End of 10.3 tests diff --git a/sql/opt_split.cc b/sql/opt_split.cc index 395422d..d272638 100644 --- a/sql/opt_split.cc +++ b/sql/opt_split.cc @@ -960,11 +960,7 @@ SplM_plan_info * JOIN_TAB::choose_best_splitting(double record_count, in the cache */ spl_plan= spl_opt_info->find_plan(best_table, best_key, best_key_parts); - if (!spl_plan && - (spl_plan= (SplM_plan_info *) thd->alloc(sizeof(SplM_plan_info))) && - (spl_plan->best_positions= - (POSITION *) thd->alloc(sizeof(POSITION) * join->table_count)) && - !spl_opt_info->plan_cache.push_back(spl_plan)) + if (!spl_plan) { /* The plan for the chosen key has not been found in the cache. @@ -974,6 +970,27 @@ SplM_plan_info * JOIN_TAB::choose_best_splitting(double record_count, reset_validity_vars_for_keyuses(best_key_keyuse_ext_start, best_table, best_key, remaining_tables, true); choose_plan(join, all_table_map & ~join->const_table_map); + + /* + Check that the chosen plan is really a splitting plan. + If not or if there is not enough memory to save the plan in the cache + then just return with no splitting plan. + */ + POSITION *first_non_const_pos= join->best_positions + join->const_tables; + TABLE *table= first_non_const_pos->table->table; + key_map spl_keys= table->keys_usable_for_splitting; + if (!(first_non_const_pos->key && + spl_keys.is_set(first_non_const_pos->key->key)) || + !(spl_plan= (SplM_plan_info *) thd->alloc(sizeof(SplM_plan_info))) || + !(spl_plan->best_positions= + (POSITION *) thd->alloc(sizeof(POSITION) * join->table_count)) || + spl_opt_info->plan_cache.push_back(spl_plan)) + { + reset_validity_vars_for_keyuses(best_key_keyuse_ext_start, best_table, + best_key, remaining_tables, false); + return 0; + } + spl_plan->keyuse_ext_start= best_key_keyuse_ext_start; spl_plan->table= best_table; spl_plan->key= best_key;

1 0

[Commits] 5801b45: MDEV-25635 Assertion failure when pushing from HAVING into WHERE of view
by IgorBabaev 02 Jun '21

02 Jun '21

revision-id: 5801b4573853796329a4b63501df7b303011f04c (mariadb-10.2.31-986-g5801b45) parent(s): d06205ba3713da6c5875f124d5e431d3704aad1d author: Igor Babaev committer: Igor Babaev timestamp: 2021-06-02 08:40:30 -0700 message: MDEV-25635 Assertion failure when pushing from HAVING into WHERE of view This bug could manifest itself after pushing a where condition over a mergeable derived table / view / CTE DT into a grouping view / derived table / CTE V whose item list contained set functions with constant arguments such as MIN(2), SUM(1) etc. In such cases the field references used in the condition pushed into the view V that correspond set functions are wrapped into Item_direct_view_ref wrappers. Due to a wrong implementation of the virtual method const_item() for the class Item_direct_view_ref the wrapped set functions with constant arguments could be erroneously taken for constant items. This could lead to a wrong result set returned by the main select query in 10.2. In 10.4 where a possibility of pushing condition from HAVING into WHERE had been added this could cause a crash. Approved by Sergey Petrunya <sergey.petrunya(a)mariadb.com> --- mysql-test/r/derived_cond_pushdown.result | 39 +++++++++++++++++++++++++++++++ mysql-test/t/derived_cond_pushdown.test | 25 ++++++++++++++++++++ sql/item.h | 5 +++- 3 files changed, 68 insertions(+), 1 deletion(-) diff --git a/mysql-test/r/derived_cond_pushdown.result b/mysql-test/r/derived_cond_pushdown.result index 25237aa..28532ae 100644 --- a/mysql-test/r/derived_cond_pushdown.result +++ b/mysql-test/r/derived_cond_pushdown.result @@ -10634,4 +10634,43 @@ m 7 drop view v1; drop table t1; +# +# MDEV-25635: pushdown into grouping view using aggregate functions +# with constant arguments via a mergeable derived table +# +create table t1 (a int); +insert into t1 values (3), (7), (1), (3), (7), (7), (3); +create view v1 as select a, sum(1) as f, sum(1) as g from t1 group by a; +select * from v1; +a f g +1 1 1 +3 3 3 +7 3 3 +select * from (select * from v1) as dt where a=f and a=g; +a f g +1 1 1 +3 3 3 +explain extended select * from (select * from v1) as dt where a=f and a=g; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <derived3> ALL NULL NULL NULL NULL 7 100.00 Using where +3 DERIVED t1 ALL NULL NULL NULL NULL 7 100.00 Using temporary; Using filesort +Warnings: +Note 1003 select `v1`.`a` AS `a`,`v1`.`f` AS `f`,`v1`.`g` AS `g` from `test`.`v1` where `v1`.`a` = `v1`.`f` and `v1`.`a` = `v1`.`g` +create view v2 as select a, min(1) as f, min(1) as g from t1 group by a; +select * from v2; +a f g +1 1 1 +3 1 1 +7 1 1 +select * from (select * from v2) as dt where a=f and a=g; +a f g +1 1 1 +explain extended select * from (select * from v2) as dt where a=f and a=g; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <derived3> ALL NULL NULL NULL NULL 7 100.00 Using where +3 DERIVED t1 ALL NULL NULL NULL NULL 7 100.00 Using temporary; Using filesort +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; # End of 10.2 tests diff --git a/mysql-test/t/derived_cond_pushdown.test b/mysql-test/t/derived_cond_pushdown.test index 31b4904..58f38ac 100644 --- a/mysql-test/t/derived_cond_pushdown.test +++ b/mysql-test/t/derived_cond_pushdown.test @@ -2212,4 +2212,29 @@ select * from v1 where m > 0; drop view v1; drop table t1; +--echo # +--echo # MDEV-25635: pushdown into grouping view using aggregate functions +--echo # with constant arguments via a mergeable derived table +--echo # + +create table t1 (a int); +insert into t1 values (3), (7), (1), (3), (7), (7), (3); + +create view v1 as select a, sum(1) as f, sum(1) as g from t1 group by a; +select * from v1; +let $q1= +select * from (select * from v1) as dt where a=f and a=g; +eval $q1; +eval explain extended $q1; + +create view v2 as select a, min(1) as f, min(1) as g from t1 group by a; +select * from v2; +let $q2= +select * from (select * from v2) as dt where a=f and a=g; +eval $q2; +eval explain extended $q2; + +drop view v1,v2; +drop table t1; + --echo # End of 10.2 tests diff --git a/sql/item.h b/sql/item.h index c94709c..76be66d 100644 --- a/sql/item.h +++ b/sql/item.h @@ -4952,7 +4952,10 @@ class Item_direct_view_ref :public Item_direct_ref table_map used_tables() const; void update_used_tables(); table_map not_null_tables() const; - bool const_item() const { return used_tables() == 0; } + bool const_item() const + { + return (*ref)->const_item() && (null_ref_table == NO_NULL_TABLE); + } TABLE *get_null_ref_table() const { return null_ref_table; } bool walk(Item_processor processor, bool walk_subquery, void *arg) {

1 0

[Commits] 3eef28a9f5c: Change version to 10.7.0
by psergey 02 Jun '21

02 Jun '21

revision-id: 3eef28a9f5c41ac064c1c3b998cf584fbf295cdd (mariadb-10.6.1-31-g3eef28a9f5c) parent(s): 06dd151bb86ad5b87d4d46011f36da1289c01074 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-06-02 14:51:52 +0300 message: Change version to 10.7.0 --- VERSION | 6 +++--- 1 file changed, 3 insertions(+), 3 deletions(-) diff --git a/VERSION b/VERSION index b9abaef7e76..aa8af315e21 100644 --- a/VERSION +++ b/VERSION @@ -1,4 +1,4 @@ MYSQL_VERSION_MAJOR=10 -MYSQL_VERSION_MINOR=6 -MYSQL_VERSION_PATCH=2 -SERVER_MATURITY=beta +MYSQL_VERSION_MINOR=7 +MYSQL_VERSION_PATCH=0 +SERVER_MATURITY=alpha

1 0

[Commits] 62d269e: MDEV-25635 Assertion failure when pushing from HAVING into WHERE of view
by IgorBabaev 01 Jun '21

01 Jun '21

revision-id: 62d269ea914de5b9225e0584cca52444e09c1c56 (mariadb-10.2.31-986-g62d269e) parent(s): d06205ba3713da6c5875f124d5e431d3704aad1d author: Igor Babaev committer: Igor Babaev timestamp: 2021-05-31 22:37:15 -0700 message: MDEV-25635 Assertion failure when pushing from HAVING into WHERE of view This bug could manifest itself after pushing a where condition over a mergeable derived table / view / CTE DT into a grouping view / derived table / CTE V whose item list contained set functions with constant arguments such as MIN(2), SUM(1) etc. In such cases the field references used in the condition pushed into the view V that correspond set functions are wrapped into Item_direct_view_ref wrappers. Due to a wrong implementation of the virtual method const_item() for the class Item_direct_view_ref the wrapped set functions with constant arguments could be erroneously taken for constant items. This could lead to a wrong result set returned by the main select query in 10.2. In 10.4 where a possibility of pushing condition from HAVING into WHERE had been added this could cause a crash. --- mysql-test/r/derived_cond_pushdown.result | 39 +++++++++++++++++++++++++++++++ mysql-test/t/derived_cond_pushdown.test | 25 ++++++++++++++++++++ sql/item.h | 5 +++- 3 files changed, 68 insertions(+), 1 deletion(-) diff --git a/mysql-test/r/derived_cond_pushdown.result b/mysql-test/r/derived_cond_pushdown.result index 25237aa..28532ae 100644 --- a/mysql-test/r/derived_cond_pushdown.result +++ b/mysql-test/r/derived_cond_pushdown.result @@ -10634,4 +10634,43 @@ m 7 drop view v1; drop table t1; +# +# MDEV-25635: pushdown into grouping view using aggregate functions +# with constant arguments via a mergeable derived table +# +create table t1 (a int); +insert into t1 values (3), (7), (1), (3), (7), (7), (3); +create view v1 as select a, sum(1) as f, sum(1) as g from t1 group by a; +select * from v1; +a f g +1 1 1 +3 3 3 +7 3 3 +select * from (select * from v1) as dt where a=f and a=g; +a f g +1 1 1 +3 3 3 +explain extended select * from (select * from v1) as dt where a=f and a=g; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <derived3> ALL NULL NULL NULL NULL 7 100.00 Using where +3 DERIVED t1 ALL NULL NULL NULL NULL 7 100.00 Using temporary; Using filesort +Warnings: +Note 1003 select `v1`.`a` AS `a`,`v1`.`f` AS `f`,`v1`.`g` AS `g` from `test`.`v1` where `v1`.`a` = `v1`.`f` and `v1`.`a` = `v1`.`g` +create view v2 as select a, min(1) as f, min(1) as g from t1 group by a; +select * from v2; +a f g +1 1 1 +3 1 1 +7 1 1 +select * from (select * from v2) as dt where a=f and a=g; +a f g +1 1 1 +explain extended select * from (select * from v2) as dt where a=f and a=g; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <derived3> ALL NULL NULL NULL NULL 7 100.00 Using where +3 DERIVED t1 ALL NULL NULL NULL NULL 7 100.00 Using temporary; Using filesort +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; # End of 10.2 tests diff --git a/mysql-test/t/derived_cond_pushdown.test b/mysql-test/t/derived_cond_pushdown.test index 31b4904..58f38ac 100644 --- a/mysql-test/t/derived_cond_pushdown.test +++ b/mysql-test/t/derived_cond_pushdown.test @@ -2212,4 +2212,29 @@ select * from v1 where m > 0; drop view v1; drop table t1; +--echo # +--echo # MDEV-25635: pushdown into grouping view using aggregate functions +--echo # with constant arguments via a mergeable derived table +--echo # + +create table t1 (a int); +insert into t1 values (3), (7), (1), (3), (7), (7), (3); + +create view v1 as select a, sum(1) as f, sum(1) as g from t1 group by a; +select * from v1; +let $q1= +select * from (select * from v1) as dt where a=f and a=g; +eval $q1; +eval explain extended $q1; + +create view v2 as select a, min(1) as f, min(1) as g from t1 group by a; +select * from v2; +let $q2= +select * from (select * from v2) as dt where a=f and a=g; +eval $q2; +eval explain extended $q2; + +drop view v1,v2; +drop table t1; + --echo # End of 10.2 tests diff --git a/sql/item.h b/sql/item.h index c94709c..76be66d 100644 --- a/sql/item.h +++ b/sql/item.h @@ -4952,7 +4952,10 @@ class Item_direct_view_ref :public Item_direct_ref table_map used_tables() const; void update_used_tables(); table_map not_null_tables() const; - bool const_item() const { return used_tables() == 0; } + bool const_item() const + { + return (*ref)->const_item() && (null_ref_table == NO_NULL_TABLE); + } TABLE *get_null_ref_table() const { return null_ref_table; } bool walk(Item_processor processor, bool walk_subquery, void *arg) {

1 0

[Commits] 5ff1765: MDEV-25714 Join using derived with aggregation returns incorrect results
by IgorBabaev 27 May '21

27 May '21

revision-id: 5ff176547f0ef9e8aa76604319919912a2a8c23b (mariadb-10.3.26-168-g5ff1765) parent(s): 1e5ebf3762abdb8108620b46e76d4ebdde8472f7 author: Igor Babaev committer: Igor Babaev timestamp: 2021-05-26 23:41:59 -0700 message: MDEV-25714 Join using derived with aggregation returns incorrect results If a join query uses a derived table (view / CTE) with GROUP BY clause then the execution plan for such join may employ split optimization. When this optimization is employed the derived table is not materialized. Rather only some partitions of the derived table are subject to grouping. Split optimization can be applied only if: - there are some indexes over the tables used in the join specifying the derived table whose prefixes partially cover the field items used in the GROUP BY list (such indexes are called splitting indexes) - the WHERE condition of the join query contains conjunctive equalities between columns of the derived table that comprise major parts of splitting indexes and columns of the other join tables. When the optimizer evaluates extending of a partial join by the rows of the derived table it always considers a possibility of using split optimization. Different splitting indexes can be used depending on the extended partial join. At some rare conditions, for example, when there is a non-splitting covering index for a table joined in the join specifying the derived table usage of a splitting index to produce rows needed for grouping may be still less beneficial than usage of such covering index without any splitting technique. The function JOIN_TAB::choose_best_splitting() must take this into account. Approved by Oleksandr Byelkin <sanja(a)mariadb.com> --- mysql-test/main/derived_cond_pushdown.result | 2 +- mysql-test/main/derived_split_innodb.result | 61 ++++++++++++++++++++++++++++ mysql-test/main/derived_split_innodb.test | 37 +++++++++++++++++ sql/opt_split.cc | 27 +++++++++--- 4 files changed, 121 insertions(+), 6 deletions(-) diff --git a/mysql-test/main/derived_cond_pushdown.result b/mysql-test/main/derived_cond_pushdown.result index f3d63b5..5fc0111 100644 --- a/mysql-test/main/derived_cond_pushdown.result +++ b/mysql-test/main/derived_cond_pushdown.result @@ -16712,7 +16712,7 @@ EXPLAIN EXTENDED SELECT * FROM v1 JOIN v2 ON v1.f = v2.f; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -3 LATERAL DERIVED NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table +3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table Warnings: Note 1003 /* select#1 */ select NULL AS `f`,`v2`.`f` AS `f` from `test`.`t1` `a` straight_join `test`.`t1` `b` join `test`.`v2` where 0 DROP VIEW v1,v2; diff --git a/mysql-test/main/derived_split_innodb.result b/mysql-test/main/derived_split_innodb.result index 0b57e72..7ea3b68 100644 --- a/mysql-test/main/derived_split_innodb.result +++ b/mysql-test/main/derived_split_innodb.result @@ -174,3 +174,64 @@ id select_type table type possible_keys key key_len ref rows Extra 2 LATERAL DERIVED t1 ref a,a_2 a 5 test.t1.a 1 Using where; Using temporary; Using filesort 2 LATERAL DERIVED t2 ref c c 5 test.t1.b 1 Using index DROP TABLE t1, t2; +# +# Bug mdev-25714: usage non-splitting covering index is cheaper than +# usage of the best splitting index for one group +# +create table t1 ( +id int not null, itemid int not null, index idx (itemid) +) engine=innodb; +insert into t1 values (1, 2), (2,2), (4,2), (4,2), (0,3), (3,3); +create table t2 (id int not null) engine=innodb; +insert into t2 values (2); +create table t3 ( +id int not null, itemid int not null, userid int not null, primary key (id), +index idx1 (userid, itemid), index idx2 (itemid) +) engine innodb; +insert into t3 values (1,1,1), (2,1,1), (3,2,1), (4,2,1), (5,3,1); +analyze table t1,t2,t3; +Table Op Msg_type Msg_text +test.t1 analyze status OK +test.t2 analyze status OK +test.t3 analyze status OK +set optimizer_switch='split_materialized=on'; +explain select t1.id, t1.itemid, dt.id, t2.id +from t1, +(select itemid, max(id) as id from t3 where userid = 1 group by itemid) dt, +t2 +where t1.id = dt.id and t1.itemid = dt.itemid and t2.id=t1.itemid; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 1 +1 PRIMARY t1 ref idx idx 4 test.t2.id 1 +1 PRIMARY <derived2> ref key0 key0 9 test.t2.id,test.t1.id 2 +2 DERIVED t3 ref idx1,idx2 idx1 4 const 5 Using where; Using index +select t1.id, t1.itemid, dt.id, t2.id +from t1, +(select itemid, max(id) as id from t3 where userid = 1 group by itemid) dt, +t2 +where t1.id = dt.id and t1.itemid = dt.itemid and t2.id=t1.itemid; +id itemid id id +4 2 4 2 +4 2 4 2 +set optimizer_switch='split_materialized=off'; +explain select t1.id, t1.itemid, dt.id, t2.id +from t1, +(select itemid, max(id) as id from t3 where userid = 1 group by itemid) dt, +t2 +where t1.id = dt.id and t1.itemid = dt.itemid and t2.id=t1.itemid; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 1 +1 PRIMARY t1 ref idx idx 4 test.t2.id 1 +1 PRIMARY <derived2> ref key0 key0 9 test.t2.id,test.t1.id 2 +2 DERIVED t3 ref idx1 idx1 4 const 5 Using where; Using index +select t1.id, t1.itemid, dt.id, t2.id +from t1, +(select itemid, max(id) as id from t3 where userid = 1 group by itemid) dt, +t2 +where t1.id = dt.id and t1.itemid = dt.itemid and t2.id=t1.itemid; +id itemid id id +4 2 4 2 +4 2 4 2 +drop table t1,t2,t3; +set optimizer_switch='split_materialized=default'; +# End of 10.3 tests diff --git a/mysql-test/main/derived_split_innodb.test b/mysql-test/main/derived_split_innodb.test index 19a6ecf..6f33c71 100644 --- a/mysql-test/main/derived_split_innodb.test +++ b/mysql-test/main/derived_split_innodb.test @@ -150,3 +150,40 @@ eval set statement optimizer_switch='split_materialized=on' for $query; DROP TABLE t1, t2; +--echo # +--echo # Bug mdev-25714: usage non-splitting covering index is cheaper than +--echo # usage of the best splitting index for one group +--echo # + +create table t1 ( + id int not null, itemid int not null, index idx (itemid) +) engine=innodb; +insert into t1 values (1, 2), (2,2), (4,2), (4,2), (0,3), (3,3); +create table t2 (id int not null) engine=innodb; +insert into t2 values (2); +create table t3 ( + id int not null, itemid int not null, userid int not null, primary key (id), + index idx1 (userid, itemid), index idx2 (itemid) +) engine innodb; +insert into t3 values (1,1,1), (2,1,1), (3,2,1), (4,2,1), (5,3,1); +analyze table t1,t2,t3; + +let $q= +select t1.id, t1.itemid, dt.id, t2.id + from t1, + (select itemid, max(id) as id from t3 where userid = 1 group by itemid) dt, + t2 + where t1.id = dt.id and t1.itemid = dt.itemid and t2.id=t1.itemid; + +set optimizer_switch='split_materialized=on'; +eval explain $q; +eval $q; + +set optimizer_switch='split_materialized=off'; +eval explain $q; +eval $q; + +drop table t1,t2,t3; +set optimizer_switch='split_materialized=default'; + +--echo # End of 10.3 tests diff --git a/sql/opt_split.cc b/sql/opt_split.cc index c3a2d03..edf9ae3 100644 --- a/sql/opt_split.cc +++ b/sql/opt_split.cc @@ -960,11 +960,7 @@ SplM_plan_info * JOIN_TAB::choose_best_splitting(double record_count, in the cache */ spl_plan= spl_opt_info->find_plan(best_table, best_key, best_key_parts); - if (!spl_plan && - (spl_plan= (SplM_plan_info *) thd->alloc(sizeof(SplM_plan_info))) && - (spl_plan->best_positions= - (POSITION *) thd->alloc(sizeof(POSITION) * join->table_count)) && - !spl_opt_info->plan_cache.push_back(spl_plan)) + if (!spl_plan) { /* The plan for the chosen key has not been found in the cache. @@ -974,6 +970,27 @@ SplM_plan_info * JOIN_TAB::choose_best_splitting(double record_count, reset_validity_vars_for_keyuses(best_key_keyuse_ext_start, best_table, best_key, remaining_tables, true); choose_plan(join, all_table_map & ~join->const_table_map); + + /* + Check that the chosen plan is really a splitting plan. + If not or if there is not enough memory to save the plan in the cache + then just return with no splitting plan. + */ + POSITION *first_non_const_pos= join->best_positions + join->const_tables; + TABLE *table= first_non_const_pos->table->table; + key_map spl_keys= table->keys_usable_for_splitting; + if (!(first_non_const_pos->key && + spl_keys.is_set(first_non_const_pos->key->key)) || + !(spl_plan= (SplM_plan_info *) thd->alloc(sizeof(SplM_plan_info))) || + !(spl_plan->best_positions= + (POSITION *) thd->alloc(sizeof(POSITION) * join->table_count)) || + spl_opt_info->plan_cache.push_back(spl_plan)) + { + reset_validity_vars_for_keyuses(best_key_keyuse_ext_start, best_table, + best_key, remaining_tables, false); + return 0; + } + spl_plan->keyuse_ext_start= best_key_keyuse_ext_start; spl_plan->table= best_table; spl_plan->key= best_key;

1 0

[Commits] 46a17ea: MDEV-23886 Reusing CTE inside a function fails with table doesn't exist
by IgorBabaev 26 May '21

26 May '21

revision-id: 46a17ea4875966159d3f8369488b813b1ac4c4b6 (mariadb-10.5.4-637-g46a17ea) parent(s): c80cecb5e3e509d37929b4f446edf9b6c636b98f author: Igor Babaev committer: Igor Babaev timestamp: 2021-05-25 17:13:17 -0700 message: MDEV-23886 Reusing CTE inside a function fails with table doesn't exist In the code existed just before this patch binding of a table reference to the specification of the corresponding CTE happens in the function open_and_process_table(). If the table reference is not the first in the query the specification is cloned in the same way as the specification of a view is cloned for any reference of the view. This works fine for standalone queries, but does not work for stored procedures / functions for the following reason. When the first call of a stored procedure/ function SP is processed the body of SP is parsed. When a query of SP is parsed the info on each encountered table reference is put into a TABLE_LIST object linked into a global chain associated with the query. When parsing of the query is finished the basic info on the table references from this chain except table references to derived tables and information schema tables is put in one hash table associated with SP. When parsing of the body of SP is finished this hash table is used to construct TABLE_LIST objects for all table references mentioned in SP and link them into the list of such objects passed to a pre-locking process that calls open_and_process_table() for each table from the list. When a TABLE_LIST for a view is encountered the view is opened and its specification is parsed. For any table reference occurred in the specification a new TABLE_LIST object is created to be included into the list for pre-locking. After all objects in the pre-locking have been looked through the tables mentioned in the list are locked. Note that the objects referenced CTEs are just skipped here as it is impossible to resolve these references without any info on the context where they occur. Now the statements from the body of SP are executed one by one that. At the very beginning of the execution of a query the tables used in the query are opened and open_and_process_table() now is called for each table reference mentioned in the list of TABLE_LIST objects associated with the query that was built when the query was parsed. For each table reference first the reference is checked against CTEs definitions in whose scope it occurred. If such definition is found the reference is considered resolved and if this is not the first reference to the found CTE the the specification of the CTE is re-parsed and the result of the parsing is added to the parsing tree of the query as a sub-tree. If this sub-tree contains table references to other tables they are added to the list of TABLE_LIST objects associated with the query in order the referenced tables to be opened. When the procedure that opens the tables comes to the TABLE_LIST object created for a non-first reference to a CTE it discovers that the referenced table instance is not locked and reports an error. Thus processing non-first table references to a CTE similar to how references to view are processed does not work for queries used in stored procedures / functions. And the main problem is that the current pre-locking mechanism employed for stored procedures / functions does not allow to save the context in which a CTE reference occur. It's not trivial to save the info about the context where a CTE reference occurs while the resolution of the table reference cannot be done without this context and consequentially the specification for the table reference cannot be determined. This patch solves the above problem by moving resolution of all CTE references at the parsing stage. More exactly references to CTEs occurred in a query are resolved right after parsing of the query has finished. After resolution any CTE reference it is marked as a reference to to derived table. So it is excluded from the hash table created for pre-locking used base tables and view when the first call of a stored procedure / function is processed. This solution required recursive calls of the parser. The function THD::sql_parser() has been added specifically for recursive invocations of the parser. # Conflicts: # sql/sql_cte.cc # sql/sql_cte.h # sql/sql_lex.cc # sql/sql_lex.h # sql/sql_view.cc # sql/sql_yacc.yy # sql/sql_yacc_ora.yy --- mysql-test/main/cte_nonrecursive.result | 201 +++++++++++++++ mysql-test/main/cte_nonrecursive.test | 202 +++++++++++++++ sql/item_subselect.cc | 1 - sql/sp_head.cc | 3 +- sql/sql_base.cc | 33 +-- sql/sql_class.cc | 56 +++++ sql/sql_class.h | 8 +- sql/sql_cte.cc | 422 ++++++++++++++++++++++---------- sql/sql_cte.h | 85 ++++++- sql/sql_lex.cc | 9 +- sql/sql_lex.h | 26 +- sql/sql_parse.cc | 15 +- sql/sql_prepare.cc | 3 - sql/sql_view.cc | 9 - sql/sql_yacc.yy | 26 +- sql/table.h | 37 +++ 16 files changed, 929 insertions(+), 207 deletions(-) diff --git a/mysql-test/main/cte_nonrecursive.result b/mysql-test/main/cte_nonrecursive.result index 54283f1..763e51f 100644 --- a/mysql-test/main/cte_nonrecursive.result +++ b/mysql-test/main/cte_nonrecursive.result @@ -1763,6 +1763,207 @@ a c 2 1 7 3 drop table t1; +# +# MDEV-23886: Stored Function returning the result of a query +# that uses CTE over a table twice +# +create table t1 (c1 int); +insert into t1 values (1),(2),(6); +create function f1() returns int return +( with cte1 as (select c1 from t1) +select sum(c1) from +(select * from cte1 union all select * from cte1) dt +); +select f1(); +f1() +18 +create function f2() returns int return +( with cte1 as (select c1 from t1) +select sum(s.c1) from cte1 as s, cte1 as t where s.c1=t.c1 +); +select f2(); +f2() +9 +create function f3() returns int return +( with cte1 as (select c1 from t1) +select +case +when exists(select 1 from cte1 where c1 between 1 and 2) then 1 +when exists(select 1 from cte1 where c1 between 5 and 6) then 2 +else 0 +end +); +select f3(); +f3() +1 +create view v1 as (select c1 from t1); +create function f4() returns int return +( select sum(c1) from +(select * from v1 union all select * from v1) dt +); +select f4(); +f4() +18 +create function f5() returns int return +( select sum(s.c1) from v1 as s, v1 as t where s.c1=t.c1 +); +select f5(); +f5() +9 +create view v2(s) as +with cte1 as (select c1 from t1) +select sum(c1) from (select * from cte1 union all select * from cte1) dt; +create function f6() returns int return +(select s from v2); +select f6(); +f6() +18 +create function f7() returns int return +( select r.s from v2 as r, v2 as t where r.s=t.s +); +select f7(); +f7() +18 +select f5() + f6(); +f5() + f6() +27 +prepare stmt from "select f5() + f6();"; +execute stmt; +f5() + f6() +27 +execute stmt; +f5() + f6() +27 +deallocate prepare stmt; +drop function f1; +drop function f2; +drop function f3; +drop function f4; +drop function f5; +drop function f6; +drop function f7; +drop view v1; +drop view v2; +create table t2 (a int, b int); +insert into t2 +with cte1 as (select c1 from t1) +select * from cte1 as s, cte1 as t where s.c1=t.c1 and s.c1 > 5; +select * from t2; +a b +6 6 +create procedure p1() +begin +insert into t2 +with cte1 as (select c1 from t1) +select * from cte1 as s, cte1 as t where s.c1=t.c1 and s.c1 <= 2 and t.c1 >= 2; +end | +call p1(); +select * from t2; +a b +6 6 +2 2 +drop procedure p1; +# checking CTE resolution for queries with hanging CTEs +with +cte1(a) as (select * from t1 where c1 <= 2), +cte2(b) as (select * from cte1 where a >= 2), +cte3 as (select * from cte1,cte2 where cte1.a < cte2.b) +select * from cte3; +a b +1 2 +select * from t2; +a b +6 6 +2 2 +with +cte1(a) as (select * from t1 where c1 <= 2), +cte2(b) as (select * from cte1 where a >= 2), +cte3 as (select * from cte1,cte2 where cte1.a < cte2.b) +select * from t2; +a b +6 6 +2 2 +with +cte1(a) as (select * from t1 where c1 <= 2), +cte2(b) as (select * from cte1 where c1 >= 2), +cte3 as (select * from cte1,cte2 where cte1.a < cte2.b) +select * from t2; +ERROR 42S22: Unknown column 'c1' in 'where clause' +with +cte1(a) as (select * from t1 where c1 <= 2), +cte2(b) as (select * from cte1 where a >= 2), +cte3 as (select * from cte1,cte2 where cte1.a < cte2.c1) +select * from t2; +ERROR 42S22: Unknown column 'cte2.c1' in 'where clause' +with +cte1 as (select * from t1 where c1 <= 2), +cte2(a,b) as (select * from cte1 as s1, cte1 as s2 where s1.c1=s2.c1) +select * from cte2; +a b +1 1 +2 2 +with +cte1 as (select * from t1 where c1 <= 2), +cte2(a,b) as (select * from cte1 as s1, cte1 as s2 where s1.c1=s2.c1) +select * from t2; +a b +6 6 +2 2 +with +cte1 as (select * from t1 where c1 <= 2), +cte2(a,b) as (select * from cte1 as s1, cte1 as s2 where s1.c1=c1) +select * from t2; +ERROR 23000: Column 'c1' in where clause is ambiguous +with cte3 as +( with cte2(a,b) as +( with cte1 as (select * from t1 where c1 <= 2) +select * from cte1 as s1, cte1 as s2 where s1.c1=s2.c1) +select r1.a,r2.b from cte2 as r1, cte2 as r2) +select * from cte3; +a b +1 1 +2 1 +1 2 +2 2 +with cte3 as +( with cte2(a,b) as +( with cte1 as (select * from t1 where c1 <= 2) +select * from cte1 as s1, cte1 as s2 where s1.c1=s2.c1) +select r1.a,r2.b from cte2 as r1, cte2 as r2) +select * from t2; +a b +6 6 +2 2 +with cte3 as +( with cte2(a,b) as +( with cte1 as (select * from t1 where c1 <= 2) +select * from cte1 as s1, cte1 as s2 where s1.c1=s2.c1) +select r1.c1,r2.c1 from cte2 as r1, cte2 as r2) +select * from t2; +ERROR 42S22: Unknown column 'r1.c1' in 'field list' +create procedure p1() +begin +insert into t2 +with cte1 as (select c1 from t1) +select * from t1 as s, t1 as t where s.c1=t.c1 and s.c1 <= 2 and t.c1 >= 2; +end | +call p1(); +select * from t2; +a b +6 6 +2 2 +2 2 +drop procedure p1; +create procedure p1() +begin +insert into t2 +with cte1 as (select a from t1) +select * from t1 as s, t1 as t where s.c1=t.c1 and s.c1 <= 2 and t.c1 >= 2; +end | +call p1(); +ERROR 42S22: Unknown column 'a' in 'field list' +drop procedure p1; +drop table t1,t2; # End of 10.2 tests # # MDEV-21673: several references to CTE that uses diff --git a/mysql-test/main/cte_nonrecursive.test b/mysql-test/main/cte_nonrecursive.test index a4a1a17..59dae44 100644 --- a/mysql-test/main/cte_nonrecursive.test +++ b/mysql-test/main/cte_nonrecursive.test @@ -1273,6 +1273,208 @@ select a, c from cte as r2 where a > 4; drop table t1; +--echo # +--echo # MDEV-23886: Stored Function returning the result of a query +--echo # that uses CTE over a table twice +--echo # + +create table t1 (c1 int); +insert into t1 values (1),(2),(6); + +create function f1() returns int return +( with cte1 as (select c1 from t1) + select sum(c1) from + (select * from cte1 union all select * from cte1) dt +); +select f1(); + +create function f2() returns int return +( with cte1 as (select c1 from t1) + select sum(s.c1) from cte1 as s, cte1 as t where s.c1=t.c1 +); +select f2(); + +create function f3() returns int return +( with cte1 as (select c1 from t1) + select + case + when exists(select 1 from cte1 where c1 between 1 and 2) then 1 + when exists(select 1 from cte1 where c1 between 5 and 6) then 2 + else 0 + end +); +select f3(); + +create view v1 as (select c1 from t1); + +create function f4() returns int return +( select sum(c1) from + (select * from v1 union all select * from v1) dt +); +select f4(); + +create function f5() returns int return +( select sum(s.c1) from v1 as s, v1 as t where s.c1=t.c1 +); +select f5(); + +create view v2(s) as +with cte1 as (select c1 from t1) +select sum(c1) from (select * from cte1 union all select * from cte1) dt; + +create function f6() returns int return +(select s from v2); +select f6(); + +create function f7() returns int return +( select r.s from v2 as r, v2 as t where r.s=t.s +); +select f7(); + +select f5() + f6(); + +prepare stmt from "select f5() + f6();"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +drop function f1; +drop function f2; +drop function f3; +drop function f4; +drop function f5; +drop function f6; +drop function f7; + +drop view v1; +drop view v2; + +create table t2 (a int, b int); + +insert into t2 +with cte1 as (select c1 from t1) +select * from cte1 as s, cte1 as t where s.c1=t.c1 and s.c1 > 5; + +select * from t2; + +delimiter |; + +create procedure p1() +begin +insert into t2 +with cte1 as (select c1 from t1) +select * from cte1 as s, cte1 as t where s.c1=t.c1 and s.c1 <= 2 and t.c1 >= 2; +end | + +delimiter ;| + +call p1(); +select * from t2; + +drop procedure p1; + +--echo # checking CTE resolution for queries with hanging CTEs + +with +cte1(a) as (select * from t1 where c1 <= 2), +cte2(b) as (select * from cte1 where a >= 2), +cte3 as (select * from cte1,cte2 where cte1.a < cte2.b) +select * from cte3; + +select * from t2; + +with +cte1(a) as (select * from t1 where c1 <= 2), +cte2(b) as (select * from cte1 where a >= 2), +cte3 as (select * from cte1,cte2 where cte1.a < cte2.b) +select * from t2; + +--error ER_BAD_FIELD_ERROR +with +cte1(a) as (select * from t1 where c1 <= 2), +cte2(b) as (select * from cte1 where c1 >= 2), +cte3 as (select * from cte1,cte2 where cte1.a < cte2.b) +select * from t2; + +--error ER_BAD_FIELD_ERROR +with +cte1(a) as (select * from t1 where c1 <= 2), +cte2(b) as (select * from cte1 where a >= 2), +cte3 as (select * from cte1,cte2 where cte1.a < cte2.c1) +select * from t2; + +with +cte1 as (select * from t1 where c1 <= 2), +cte2(a,b) as (select * from cte1 as s1, cte1 as s2 where s1.c1=s2.c1) +select * from cte2; + +with +cte1 as (select * from t1 where c1 <= 2), +cte2(a,b) as (select * from cte1 as s1, cte1 as s2 where s1.c1=s2.c1) +select * from t2; + +--error ER_NON_UNIQ_ERROR +with +cte1 as (select * from t1 where c1 <= 2), +cte2(a,b) as (select * from cte1 as s1, cte1 as s2 where s1.c1=c1) +select * from t2; + +with cte3 as +( with cte2(a,b) as + ( with cte1 as (select * from t1 where c1 <= 2) + select * from cte1 as s1, cte1 as s2 where s1.c1=s2.c1) + select r1.a,r2.b from cte2 as r1, cte2 as r2) +select * from cte3; + +with cte3 as +( with cte2(a,b) as + ( with cte1 as (select * from t1 where c1 <= 2) + select * from cte1 as s1, cte1 as s2 where s1.c1=s2.c1) + select r1.a,r2.b from cte2 as r1, cte2 as r2) +select * from t2; + +--error ER_BAD_FIELD_ERROR +with cte3 as +( with cte2(a,b) as + ( with cte1 as (select * from t1 where c1 <= 2) + select * from cte1 as s1, cte1 as s2 where s1.c1=s2.c1) + select r1.c1,r2.c1 from cte2 as r1, cte2 as r2) +select * from t2; + +delimiter |; + +create procedure p1() +begin +insert into t2 +with cte1 as (select c1 from t1) +select * from t1 as s, t1 as t where s.c1=t.c1 and s.c1 <= 2 and t.c1 >= 2; +end | + +delimiter ;| + +call p1(); +select * from t2; + +drop procedure p1; + +delimiter |; + +create procedure p1() +begin +insert into t2 +with cte1 as (select a from t1) +select * from t1 as s, t1 as t where s.c1=t.c1 and s.c1 <= 2 and t.c1 >= 2; +end | + +delimiter ;| + +--error ER_BAD_FIELD_ERROR +call p1(); + +drop procedure p1; + +drop table t1,t2; + --echo # End of 10.2 tests --echo # diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index 7fc7f41..ffebf7d 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -1846,7 +1846,6 @@ double Item_in_subselect::val_real() As far as Item_in_subselect called only from Item_in_optimizer this method should not be used */ - DBUG_ASSERT(0); DBUG_ASSERT(fixed == 1); if (forced_const) return value; diff --git a/sql/sp_head.cc b/sql/sp_head.cc index 513e720..b8e8def 100644 --- a/sql/sp_head.cc +++ b/sql/sp_head.cc @@ -3487,8 +3487,7 @@ sp_lex_keeper::reset_lex_and_exec_core(THD *thd, uint *nextp, Json_writer_object trace_command(thd); Json_writer_array trace_command_steps(thd, "steps"); if (open_tables) - res= check_dependencies_in_with_clauses(m_lex->with_clauses_list) || - instr->exec_open_and_lock_tables(thd, m_lex->query_tables); + res= instr->exec_open_and_lock_tables(thd, m_lex->query_tables); if (likely(!res)) { diff --git a/sql/sql_base.cc b/sql/sql_base.cc index e926ebc..f9f9856 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -3589,7 +3589,11 @@ open_and_process_table(THD *thd, TABLE_LIST *tables, uint *counter, uint flags, if (tables->derived) { if (!tables->view) + { + if (!tables->is_derived()) + tables->set_derived(); goto end; + } /* We restore view's name and database wiped out by derived tables processing and fall back to standard open process in order to @@ -3599,35 +3603,6 @@ open_and_process_table(THD *thd, TABLE_LIST *tables, uint *counter, uint flags, tables->db= tables->view_db; tables->table_name= tables->view_name; } - else if (tables->select_lex) - { - /* - Check whether 'tables' refers to a table defined in a with clause. - If so set the reference to the definition in tables->with. - */ - if (!tables->with) - tables->with= tables->select_lex->find_table_def_in_with_clauses(tables); - /* - If 'tables' is defined in a with clause set the pointer to the - specification from its definition in tables->derived. - */ - if (tables->with) - { - if (tables->is_recursive_with_table() && - !tables->is_with_table_recursive_reference()) - { - tables->with->rec_outer_references++; - With_element *with_elem= tables->with; - while ((with_elem= with_elem->get_next_mutually_recursive()) != - tables->with) - with_elem->rec_outer_references++; - } - if (tables->set_as_with_table(thd, tables->with)) - DBUG_RETURN(1); - else - goto end; - } - } if (!tables->derived && is_infoschema_db(&tables->db)) { diff --git a/sql/sql_class.cc b/sql/sql_class.cc index ac42b50..8b61750 100644 --- a/sql/sql_class.cc +++ b/sql/sql_class.cc @@ -2810,6 +2810,62 @@ void THD::close_active_vio() #endif +/* + @brief MySQL parser used for recursive invocations + + @param old_lex The LEX structure in the state when this parser + is called recursively + @param lex The LEX structure used to parse a new SQL fragment + @param str The SQL fragment to parse + @param str_len The length of the SQL fragment to parse + @param stmt_prepare_mode true <=> when parsing a prepare statement + + @details + This function is to be used when parsing of an SQL fragment is + needed within one of the grammar rules. + + @notes + Currently the function is used only when the specification of a CTE + is parsed for the not first and not recursive references of the CTE. + + @retval false On a successful parsing of the fragment + @retval true Otherwise +*/ + +bool THD::sql_parser(LEX *old_lex, LEX *lex, + char *str, uint str_len, bool stmt_prepare_mode) +{ + extern int MYSQLparse(THD * thd); + extern int ORAparse(THD * thd); + + bool parse_status= false; + Parser_state parser_state; + Parser_state *old_parser_state= m_parser_state; + + if (parser_state.init(this, str, str_len)) + return true; + + m_parser_state= &parser_state; + parser_state.m_lip.stmt_prepare_mode= stmt_prepare_mode; + parser_state.m_lip.multi_statements= false; + parser_state.m_lip.m_digest= NULL; + + lex->param_list= old_lex->param_list; + lex->sphead= old_lex->sphead; + lex->spname= old_lex->spname; + lex->spcont= old_lex->spcont; + lex->sp_chistics= old_lex->sp_chistics; + lex->trg_chistics= old_lex->trg_chistics; + + parse_status= (variables.sql_mode & MODE_ORACLE) ? + ORAparse(this) : MYSQLparse(this) != 0; + + m_parser_state= old_parser_state; + + return parse_status; +} + + struct Item_change_record: public ilink { Item **place; diff --git a/sql/sql_class.h b/sql/sql_class.h index de05b0d..ac8d26c 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -4401,14 +4401,11 @@ class THD: public THD_count, /* this must be first */ to resolve all CTE names as we don't need this message to be thrown for any CTE references. */ - if (!lex->with_clauses_list) + if (!lex->with_cte_resolution) { my_message(ER_NO_DB_ERROR, ER(ER_NO_DB_ERROR), MYF(0)); return TRUE; } - /* This will allow to throw an error later for non-CTE references */ - to->str= NULL; - to->length= 0; return FALSE; } @@ -5210,6 +5207,9 @@ class THD: public THD_count, /* this must be first */ Item *sp_prepare_func_item(Item **it_addr, uint cols= 1); bool sp_eval_expr(Field *result_field, Item **expr_item_ptr); + bool sql_parser(LEX *old_lex, LEX *lex, + char *str, uint str_len, bool stmt_prepare_mode); + }; diff --git a/sql/sql_cte.cc b/sql/sql_cte.cc index 815a0f4..1dbdfbd 100644 --- a/sql/sql_cte.cc +++ b/sql/sql_cte.cc @@ -84,7 +84,7 @@ void st_select_lex_unit::set_with_clause(With_clause *with_cl) true on failure */ -bool check_dependencies_in_with_clauses(With_clause *with_clauses_list) +bool LEX::check_dependencies_in_with_clauses() { for (With_clause *with_clause= with_clauses_list; with_clause; @@ -102,6 +102,201 @@ bool check_dependencies_in_with_clauses(With_clause *with_clauses_list) /** @brief + Resolve references to CTE in specification of hanging CTE + + @details + A CTE to which there are no references in the query is called hanging CTE. + Although such CTE is not used for execution its specification must be + subject to context analysis. All errors concerning references to + non-existing tables or fields occurred in the specification must be + reported as well as all other errors caught at the prepare stage. + The specification of a hanging CTE might contain references to other + CTE outside of the specification and within it if the specification + contains a with clause. This function resolves all such references for + all hanging CTEs encountered in the processed query. + + @retval + false on success + true on failure +*/ + +bool +LEX::resolve_references_to_cte_in_hanging_cte() +{ + for (With_clause *with_clause= with_clauses_list; + with_clause; with_clause= with_clause->next_with_clause) + { + for (With_element *with_elem= with_clause->with_list.first; + with_elem; with_elem= with_elem->next) + { + if (!with_elem->is_referenced()) + { + TABLE_LIST *first_tbl= + with_elem->spec->first_select()->table_list.first; + TABLE_LIST **with_elem_end_pos= with_elem->head->tables_pos.end_pos; + if (first_tbl && resolve_references_to_cte(first_tbl, with_elem_end_pos)) + return true; + } + } + } + return false; +} + + +/** + @brief + Resolve table references to CTE from a sub-chain of table references + + @param tables Points to the beginning of the sub-chain + @param tables_last Points to the address with the sub-chain barrier + + @details + The method resolves tables references to CTE from the chain of + table references specified by the parameters 'tables' and 'tables_last'. + It resolves the references against the CTE definition occurred in a query + or the specification of a CTE whose parsing tree is represented by + this LEX structure. The method is always called right after the process + of parsing the query or of the specification of a CTE has been finished, + thus the chain of table references used in the parsed fragment has been + already built. It is assumed that parameters of the method specify a + a sub-chain of this chain. + If a table reference can be potentially a table reference to a CTE and it + has not been resolved yet then the method tries to find the definition + of the CTE against which the reference can be resolved. If it succeeds + it sets the field TABLE_LIST::with to point to the found definition. + It also sets the field TABLE_LIST::derived to point to the specification + of the found CTE and sets TABLE::db.str to empty_c_string. This will + allow to handle this table reference like a reference to a derived handle. + If another table reference has been already resolved against this CTE + and this CTE is not recursive then a clone of the CTE specification is + constructed using the function With_element::clone_parsed_spec() and + TABLE_LIST::derived is set to point to this clone rather than to the + original specification. + If the method does not find a matched CTE definition in the parsed fragment + then in the case when the flag this->only_cte_resolution is set to true + it just moves to the resolution of the next table reference from the + specified sub-chain while in the case when this->only_cte_resolution is set + to false the method additionally sets an mdl request for this table + reference. + + @notes + The flag this->only_cte_resolution is set to true in the cases when + the failure to resolve a table reference as a CTE reference within + the fragment associated with this LEX structure does not imply that + this table reference cannot be resolved as such at all. + + @retval false On success: no errors reported, no memory allocations failed + @retval true Otherwise +*/ + +bool LEX::resolve_references_to_cte(TABLE_LIST *tables, + TABLE_LIST **tables_last) +{ + With_element *with_elem= 0; + + for (TABLE_LIST *tbl= tables; tbl != *tables_last; tbl= tbl->next_global) + { + if (tbl->derived) + continue; + if (!tbl->db.str && !tbl->with) + tbl->with= tbl->select_lex->find_table_def_in_with_clauses(tbl); + if (!tbl->with) // no CTE matches table reference tbl + { + if (only_cte_resolution) + continue; + if (!tbl->db.str) // no database specified in table reference tbl + { + if (!thd->db.str) // no default database is set + { + my_message(ER_NO_DB_ERROR, ER(ER_NO_DB_ERROR), MYF(0)); + return true; + } + if (copy_db_to(&tbl->db)) + return true; + if (!(tbl->table_options & TL_OPTION_ALIAS)) + MDL_REQUEST_INIT(&tbl->mdl_request, MDL_key::TABLE, + tbl->db.str, tbl->table_name.str, + tbl->mdl_type, MDL_TRANSACTION); + tbl->mdl_request.set_type((tbl->lock_type >= TL_WRITE_ALLOW_WRITE) ? + MDL_SHARED_WRITE : MDL_SHARED_READ); + } + continue; + } + with_elem= tbl->with; + if (tbl->is_recursive_with_table() && + !tbl->is_with_table_recursive_reference()) + { + tbl->with->rec_outer_references++; + while ((with_elem= with_elem->get_next_mutually_recursive()) != + tbl->with) + with_elem->rec_outer_references++; + } + if (!with_elem->is_used_in_query || with_elem->is_recursive) + { + tbl->derived= with_elem->spec; + if (tbl->derived != tbl->select_lex->master_unit() && + !with_elem->is_recursive && + !tbl->is_with_table_recursive_reference()) + { + tbl->derived->move_as_slave(tbl->select_lex); + } + with_elem->is_used_in_query= true; + } + else + { + if (!(tbl->derived= tbl->with->clone_parsed_spec(thd->lex, tbl))) + return true; + } + tbl->db.str= empty_c_string; + tbl->db.length= 0; + tbl->schema_table= 0; + if (tbl->derived) + { + tbl->derived->first_select()->set_linkage(DERIVED_TABLE_TYPE); + tbl->select_lex->add_statistics(tbl->derived); + } + if (tbl->with->is_recursive && tbl->is_with_table_recursive_reference()) + continue; + with_elem->inc_references(); + } + return false; +} + + +/** + @brief + Find out dependencies between CTEs, resolve references to them + + @details + The function can be called in two modes. With this->with_cte_resolution + set to false the function only finds out all dependencies between CTEs + used in a query expression with a WITH clause whose parsing has been + just finished. Based on these dependencies recursive CTEs are detected. + If this->with_cte_resolution is set to true the function additionally + resolves all references to CTE occurred in this query expression. + + @retval + true on failure + false on success +*/ + +bool +LEX::check_cte_dependencies_and_resolve_references() +{ + if (check_dependencies_in_with_clauses()) + return true; + if (!with_cte_resolution) + return false; + if (resolve_references_to_cte(query_tables, query_tables_last)) + return true; + if (resolve_references_to_cte_in_hanging_cte()) + return true; + return false; +} + + +/** + @brief Check dependencies between tables defined in this with clause @details @@ -138,10 +333,11 @@ bool With_clause::check_dependencies() elem != with_elem; elem= elem->next) { - if (lex_string_cmp(system_charset_info, with_elem->query_name, - elem->query_name) == 0) + if (lex_string_cmp(system_charset_info, with_elem->get_name(), + elem->get_name()) == 0) { - my_error(ER_DUP_QUERY_NAME, MYF(0), with_elem->query_name->str); + my_error(ER_DUP_QUERY_NAME, MYF(0), + with_elem->get_name_str()); return true; } } @@ -248,13 +444,12 @@ With_element *With_clause::find_table_def(TABLE_LIST *table, with_elem != barrier; with_elem= with_elem->next) { - if (my_strcasecmp(system_charset_info, with_elem->query_name->str, - table->table_name.str) == 0 && + if (my_strcasecmp(system_charset_info, with_elem->get_name_str(), + table->table_name.str) == 0 && !table->is_fqtn) { table->set_derived(); - table->db.str= empty_c_string; - table->db.length= 0; + with_elem->referenced= true; return with_elem; } } @@ -611,7 +806,7 @@ bool With_clause::check_anchors() if (elem == with_elem) { my_error(ER_RECURSIVE_WITHOUT_ANCHORS, MYF(0), - with_elem->query_name->str); + with_elem->get_name_str()); return true; } } @@ -644,7 +839,7 @@ bool With_clause::check_anchors() if (elem->work_dep_map & elem->get_elem_map()) { my_error(ER_UNACCEPTABLE_MUTUAL_RECURSION, MYF(0), - with_elem->query_name->str); + with_elem->get_name_str()); return true; } } @@ -798,7 +993,8 @@ bool With_element::set_unparsed_spec(THD *thd, @brief Create a clone of the specification for the given with table - @param thd The context of the statement containing this with element + @param old_lex The LEX structure created for the query or CTE specification + where this With_element is defined @param with_table The reference to the table defined in this element for which the clone is created. @@ -808,12 +1004,13 @@ bool With_element::set_unparsed_spec(THD *thd, this element. The clone is created when the string with the specification saved in unparsed_spec is fed into the parser as an input string. The parsing - this string a unit object representing the specification is build. + this string a unit object representing the specification is built. A chain of all table references occurred in the specification is also formed. The method includes the new unit and its sub-unit into hierarchy of the units of the main query. I also insert the constructed chain of the table references into the chain of all table references of the main query. + The method resolves all references to CTE in the clone. @note Clones is created only for not first references to tables defined in @@ -829,116 +1026,128 @@ bool With_element::set_unparsed_spec(THD *thd, NULL - otherwise */ -st_select_lex_unit *With_element::clone_parsed_spec(THD *thd, +st_select_lex_unit *With_element::clone_parsed_spec(LEX *old_lex, TABLE_LIST *with_table) { + THD *thd= old_lex->thd; LEX *lex; - st_select_lex_unit *res= NULL; - Query_arena backup; - Query_arena *arena= thd->activate_stmt_arena_if_needed(&backup); - bool has_tmp_tables; + st_select_lex_unit *res= NULL; if (!(lex= (LEX*) new(thd->mem_root) st_lex_local)) - { - if (arena) - thd->restore_active_arena(arena, &backup); return res; - } - LEX *old_lex= thd->lex; thd->lex= lex; bool parse_status= false; - Parser_state parser_state; - TABLE_LIST *spec_tables; - TABLE_LIST *spec_tables_tail; st_select_lex *with_select; char save_end= unparsed_spec.str[unparsed_spec.length]; ((char*) &unparsed_spec.str[unparsed_spec.length])[0]= '\0'; - if (parser_state.init(thd, (char*) unparsed_spec.str, (unsigned int)unparsed_spec.length)) - goto err; - parser_state.m_lip.stmt_prepare_mode= stmt_prepare_mode; - parser_state.m_lip.multi_statements= false; - parser_state.m_lip.m_digest= NULL; lex_start(thd); lex->clone_spec_offset= unparsed_spec_offset; - lex->param_list= old_lex->param_list; - lex->sphead= old_lex->sphead; - lex->spname= old_lex->spname; - lex->spcont= old_lex->spcont; - lex->sp_chistics= old_lex->sp_chistics; - - lex->stmt_lex= old_lex; - parse_status= parse_sql(thd, &parser_state, 0); + lex->with_cte_resolution= true; + + /* + The specification of a CTE is to be parsed as a regular query. + At the very end of the parsing query the function + check_cte_dependencies_and_resolve_references() will be called. + It will check the dependencies between CTEs that are defined + within the query and will resolve CTE references in this query. + If a table reference is not resolved as a CTE reference within + this query it still can be resolved as a reference to a CTE defined + in the same clause as the CTE whose specification is to be parsed + or defined in an embedding CTE definition. + + Example: + with + cte1 as ( ... ), + cte2 as ([WITH ...] select ... from cte1 ...) + select ... from cte2 as r, ..., cte2 as s ... + + Here the specification of cte2 has be cloned for table reference + with alias s1. The specification contains a reference to cte1 + that is defined outside this specification. If the reference to + cte1 cannot be resolved within the specification of cte2 it's + not necessarily has to be a reference to a non-CTE table. That's + why the flag lex->only_cte_resolution has to be set to true + before parsing of the specification of cte2 invoked by this + function starts. Otherwise an mdl_lock would be requested for s + and this would not be correct. + */ + + lex->only_cte_resolution= true; + + lex->stmt_lex= old_lex->stmt_lex ? old_lex->stmt_lex : old_lex; + + parse_status= thd->sql_parser(old_lex, lex, + (char*) unparsed_spec.str, + (unsigned int)unparsed_spec.length, + stmt_prepare_mode); + ((char*) &unparsed_spec.str[unparsed_spec.length])[0]= save_end; - with_select= lex->first_select_lex(); + with_select= lex->unit.first_select(); if (parse_status) goto err; - if (check_dependencies_in_with_clauses(lex->with_clauses_list)) - goto err; - - spec_tables= lex->query_tables; - spec_tables_tail= 0; - has_tmp_tables= thd->has_temporary_tables(); - for (TABLE_LIST *tbl= spec_tables; - tbl; - tbl= tbl->next_global) - { - if (has_tmp_tables && !tbl->derived && !tbl->schema_table && - thd->open_temporary_table(tbl)) - goto err; - spec_tables_tail= tbl; - } - if (spec_tables) + /* + The global chain of TABLE_LIST objects created for the specification that + just has been parsed is added to such chain that contains the reference + to the CTE whose specification is parsed right after the TABLE_LIST object + created for the reference. + */ + if (lex->query_tables) { - if (with_table->next_global) + head->tables_pos.set_start_pos(&with_table->next_global); + head->tables_pos.set_end_pos(lex->query_tables_last); + TABLE_LIST *next_tbl= with_table->next_global; + if (next_tbl) { - spec_tables_tail->next_global= with_table->next_global; - with_table->next_global->prev_global= &spec_tables_tail->next_global; + *(lex->query_tables->prev_global= next_tbl->prev_global)= + lex->query_tables; + *(next_tbl->prev_global= lex->query_tables_last)= next_tbl; } else { - old_lex->query_tables_last= &spec_tables_tail->next_global; + *(lex->query_tables->prev_global= old_lex->query_tables_last)= + lex->query_tables; + old_lex->query_tables_last= lex->query_tables_last; } - spec_tables->prev_global= &with_table->next_global; - with_table->next_global= spec_tables; } res= &lex->unit; res->with_element= this; + /* + The unit of the specification that just has been parsed is included + as a slave of the select that contained in its from list the table + reference for which the unit has been created. + */ lex->unit.include_down(with_table->select_lex); - lex->unit.set_slave(with_select); + lex->unit.set_slave(with_select); + lex->unit.cloned_from= spec; old_lex->all_selects_list= (st_select_lex*) (lex->all_selects_list-> insert_chain_before( (st_select_lex_node **) &(old_lex->all_selects_list), with_select)); - if (check_dependencies_in_with_clauses(lex->with_clauses_list)) - res= NULL; + /* - Resolve references to CTE from the spec_tables list that has not - been resolved yet. + Now all references to the CTE defined outside of the cloned specification + has to be resolved. Additionally if old_lex->only_cte_resolution == false + for the table references that has not been resolved requests for mdl_locks + has to be set. */ - for (TABLE_LIST *tbl= spec_tables; - tbl; - tbl= tbl->next_global) + lex->only_cte_resolution= old_lex->only_cte_resolution; + if (lex->resolve_references_to_cte(lex->query_tables, + lex->query_tables_last)) { - if (!tbl->with) - tbl->with= with_select->find_table_def_in_with_clauses(tbl); - if (tbl == spec_tables_tail) - break; - } - if (check_table_access(thd, SELECT_ACL, spec_tables, FALSE, UINT_MAX, FALSE)) + res= NULL; goto err; + } - lex->sphead= NULL; // in order not to delete lex->sphead + lex->sphead= NULL; // in order not to delete lex->sphead lex_end(lex); err: - if (arena) - thd->restore_active_arena(arena, &backup); thd->lex= old_lex; return res; } @@ -1145,59 +1354,6 @@ With_element *st_select_lex::find_table_def_in_with_clauses(TABLE_LIST *table) } -/** - @brief - Set the specifying unit in this reference to a with table - - @details - The method assumes that the given element with_elem defines the table T - this table reference refers to. - If this is the first reference to T the method just sets its specification - in the field 'derived' as the unit that yields T. Otherwise the method - first creates a clone specification and sets rather this clone in this field. - - @retval - false on success - true on failure -*/ - -bool TABLE_LIST::set_as_with_table(THD *thd, With_element *with_elem) -{ - if (table) - { - /* - This table was prematurely identified as a temporary table. - We correct it here, but it's not a nice solution in the case - when the temporary table with this name is not used anywhere - else in the query. - */ - thd->mark_tmp_table_as_free_for_reuse(table); - table= 0; - } - with= with_elem; - schema_table= NULL; - if (!with_elem->is_referenced() || with_elem->is_recursive) - { - derived= with_elem->spec; - if (derived != select_lex->master_unit() && - !with_elem->is_recursive && - !is_with_table_recursive_reference()) - { - derived->move_as_slave(select_lex); - } - } - else - { - if(!(derived= with_elem->clone_parsed_spec(thd, this))) - return true; - } - derived->first_select()->set_linkage(DERIVED_TABLE_TYPE); - select_lex->add_statistics(derived); - with_elem->inc_references(); - return false; -} - - bool TABLE_LIST::is_recursive_with_table() { return with && with->is_recursive; @@ -1297,7 +1453,7 @@ bool st_select_lex::check_unrestricted_recursive(bool only_standard_compliant) if (only_standard_compliant && with_elem->is_unrestricted()) { my_error(ER_NOT_STANDARD_COMPLIANT_RECURSIVE, - MYF(0), with_elem->query_name->str); + MYF(0), with_elem->get_name_str()); return true; } @@ -1514,7 +1670,7 @@ static void list_strlex_print(THD *thd, String *str, List<Lex_ident_sys> *list) void With_element::print(THD *thd, String *str, enum_query_type query_type) { - str->append(query_name); + str->append(get_name()); if (column_list.elements) { List_iterator_fast<Lex_ident_sys> li(column_list); diff --git a/sql/sql_cte.h b/sql/sql_cte.h index 4c42dd2..44628df 100644 --- a/sql/sql_cte.h +++ b/sql/sql_cte.h @@ -25,6 +25,39 @@ struct st_unit_ctxt_elem; /** + @class With_element_head + @brief Head of the definition of a CTE table + + It contains the name of the CTE and it contains the position of the subchain + of table references used in the definition in the global chain of table + references used in the query where this definition is encountered. +*/ + +class With_element_head : public Sql_alloc +{ + /* The name of the defined CTE */ + LEX_CSTRING *query_name; + +public: + /* + The structure describing the subchain of the table references used in + the specification of the defined CTE in the global chain of table + references used in the query. The structure is fully defined only + after the CTE definition has been parsed. + */ + TABLE_CHAIN tables_pos; + + With_element_head(LEX_CSTRING *name) + : query_name(name) + { + tables_pos.set_start_pos(0); + tables_pos.set_end_pos(0); + } + friend class With_element; +}; + + +/** @class With_element @brief Definition of a CTE table @@ -85,9 +118,22 @@ class With_element : public Sql_alloc subqueries and specifications of other with elements). */ uint references; + + /* + true <=> this With_element is referred in the query in which the + element is defined + */ + bool referenced; + + /* + true <=> this With_element is needed for the execution of the query + in which the element is defined + */ + bool is_used_in_query; + /* Unparsed specification of the query that specifies this element. - It used to build clones of the specification if they are needed. + It's used to build clones of the specification if they are needed. */ LEX_CSTRING unparsed_spec; /* Offset of the specification in the input string */ @@ -101,10 +147,12 @@ class With_element : public Sql_alloc public: /* - The name of the table introduced by this with elememt. The name - can be used in FROM lists of the queries in the scope of the element. + Contains the name of the defined With element and the position of + the subchain of the tables references used by its definition in the + global chain of TABLE_LIST objects created for the whole query. */ - LEX_CSTRING *query_name; + With_element_head *head; + /* Optional list of column names to name the columns of the table introduced by this with element. It is used in the case when the names are not @@ -163,18 +211,27 @@ class With_element : public Sql_alloc /* List of derived tables containing recursive references to this CTE */ SQL_I_List<TABLE_LIST> derived_with_rec_ref; - With_element(LEX_CSTRING *name, + With_element(With_element_head *h, List <Lex_ident_sys> list, st_select_lex_unit *unit) : next(NULL), base_dep_map(0), derived_dep_map(0), sq_dep_map(0), work_dep_map(0), mutually_recursive(0), top_level_dep_map(0), sq_rec_ref(NULL), next_mutually_recursive(NULL), references(0), - query_name(name), column_list(list), cycle_list(0), spec(unit), + referenced(false), is_used_in_query(false), + head(h), column_list(list), cycle_list(0), spec(unit), is_recursive(false), rec_outer_references(0), with_anchor(false), level(0), rec_result(NULL) { unit->with_element= this; } + LEX_CSTRING *get_name() { return head->query_name; } + const char *get_name_str() { return get_name()->str; } + + void set_tables_start_pos(TABLE_LIST **pos) + { head->tables_pos.set_start_pos(pos); } + void set_tables_end_pos(TABLE_LIST **pos) + { head->tables_pos.set_end_pos(pos); } + bool check_dependencies_in_spec(); void check_dependencies_in_select(st_select_lex *sl, st_unit_ctxt_elem *ctxt, @@ -201,9 +258,9 @@ class With_element : public Sql_alloc bool set_unparsed_spec(THD *thd, const char *spec_start, const char *spec_end, my_ptrdiff_t spec_offset); - st_select_lex_unit *clone_parsed_spec(THD *thd, TABLE_LIST *with_table); + st_select_lex_unit *clone_parsed_spec(LEX *old_lex, TABLE_LIST *with_table); - bool is_referenced() { return references != 0; } + bool is_referenced() { return referenced; } void inc_references() { references++; } @@ -263,6 +320,12 @@ class With_element : public Sql_alloc void set_cycle_list(List<Lex_ident_sys> *cycle_list_arg); friend class With_clause; + + friend + bool LEX::resolve_references_to_cte(TABLE_LIST *tables, + TABLE_LIST **tables_last); + friend + bool LEX::resolve_references_to_cte_in_hanging_cte(); }; const uint max_number_of_elements_in_with_clause= sizeof(table_map)*8; @@ -361,8 +424,10 @@ class With_clause : public Sql_alloc friend class With_element; friend - bool - check_dependencies_in_with_clauses(With_clause *with_clauses_list); + bool LEX::check_dependencies_in_with_clauses(); + + friend + bool LEX::resolve_references_to_cte_in_hanging_cte(); }; inline diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index f16102d..de323ae 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -1255,6 +1255,8 @@ void LEX::start(THD *thd_arg) explain_json= false; context_analysis_only= 0; derived_tables= 0; + with_cte_resolution= false; + only_cte_resolution= false; safe_to_cache_query= 1; parsing_options.reset(); empty_field_list_on_rset= 0; @@ -2911,6 +2913,7 @@ void st_select_lex_unit::init_query() is_view= false; with_clause= 0; with_element= 0; + cloned_from= 0; columns_are_renamed= false; with_wrapped_tvc= false; have_except_all_or_intersect_all= false; @@ -8997,6 +9000,8 @@ bool LEX::check_main_unit_semantics() if (unit.set_nest_level(0) || unit.check_parameters(first_select_lex())) return TRUE; + if (check_cte_dependencies_and_resolve_references()) + return TRUE; return FALSE; } @@ -9703,8 +9708,8 @@ bool LEX::main_select_push(bool service) { DBUG_ENTER("LEX::main_select_push"); DBUG_PRINT("info", ("service: %u", service)); - current_select_number= 1; - builtin_select.select_number= 1; + current_select_number= ++thd->lex->stmt_lex->current_select_number; + builtin_select.select_number= current_select_number; builtin_select.is_service_select= service; if (push_select(&builtin_select)) DBUG_RETURN(TRUE); diff --git a/sql/sql_lex.h b/sql/sql_lex.h index 45a3bf7..4c15c75 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -944,6 +944,8 @@ class st_select_lex_unit: public st_select_lex_node { With_clause *with_clause; /* With element where this unit is used as the specification (if any) */ With_element *with_element; + /* The unit used as a CTE specification from which this unit is cloned */ + st_select_lex_unit *cloned_from; /* thread handler */ THD *thd; /* @@ -1543,7 +1545,9 @@ class st_select_lex: public st_select_lex_node } With_element *get_with_element() { - return master_unit()->with_element; + return master_unit()->cloned_from ? + master_unit()->cloned_from->with_element : + master_unit()->with_element; } With_element *find_table_def_in_with_clauses(TABLE_LIST *table); bool check_unrestricted_recursive(bool only_standard_compliant); @@ -3389,6 +3393,20 @@ struct LEX: public Query_tables_list */ uint8 derived_tables; uint8 context_analysis_only; + /* + true <=> The parsed fragment requires resolution of references to CTE + at the end of parsing. This name resolution process involves searching + for possible dependencies between CTE defined in the parsed fragment and + detecting possible recursive references. + The flag is set to true if the fragment contains CTE definitions. + */ + bool with_cte_resolution; + /* + true <=> only resolution of references to CTE are required in the parsed + fragment, no checking of dependencies between CTE is required. + This flag is used only when parsing clones of CTE specifications. + */ + bool only_cte_resolution; bool local_file; bool check_exists; bool autocommit; @@ -4725,6 +4743,12 @@ struct LEX: public Query_tables_list const LEX_CSTRING *constraint_name, Table_ident *ref_table_name, DDL_options ddl_options); + bool check_dependencies_in_with_clauses(); + bool resolve_references_to_cte_in_hanging_cte(); + bool check_cte_dependencies_and_resolve_references(); + bool resolve_references_to_cte(TABLE_LIST *tables, + TABLE_LIST **tables_last); + }; diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index 0e2b80b..e9252f0 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -3594,9 +3594,6 @@ mysql_execute_command(THD *thd) thd->get_stmt_da()->opt_clear_warning_info(thd->query_id); } - if (check_dependencies_in_with_clauses(thd->lex->with_clauses_list)) - DBUG_RETURN(1); - #ifdef HAVE_REPLICATION if (unlikely(thd->slave_thread)) { @@ -8291,7 +8288,7 @@ TABLE_LIST *st_select_lex::add_table_to_list(THD *thd, ptr->is_fqtn= TRUE; ptr->db= table->db; } - else if (lex->copy_db_to(&ptr->db)) + else if (!lex->with_cte_resolution && lex->copy_db_to(&ptr->db)) DBUG_RETURN(0); else ptr->is_fqtn= FALSE; @@ -8308,7 +8305,9 @@ TABLE_LIST *st_select_lex::add_table_to_list(THD *thd, } ptr->table_name= table->table; - ptr->lock_type= lock_type; + ptr->lock_type= lock_type; + ptr->mdl_type= mdl_type; + ptr->table_options= table_options; ptr->updating= MY_TEST(table_options & TL_OPTION_UPDATING); /* TODO: remove TL_OPTION_FORCE_INDEX as it looks like it's not used */ ptr->force_index= MY_TEST(table_options & TL_OPTION_FORCE_INDEX); @@ -8989,8 +8988,10 @@ void st_select_lex::set_lock_for_tables(thr_lock_type lock_type, bool for_update { tables->lock_type= lock_type; tables->updating= for_update; - tables->mdl_request.set_type((lock_type >= TL_WRITE_ALLOW_WRITE) ? - MDL_SHARED_WRITE : MDL_SHARED_READ); + + if (tables->db.str && tables->db.str[0]) + tables->mdl_request.set_type((lock_type >= TL_WRITE_ALLOW_WRITE) ? + MDL_SHARED_WRITE : MDL_SHARED_READ); } DBUG_VOID_RETURN; } diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc index 21958e2..5fac907 100644 --- a/sql/sql_prepare.cc +++ b/sql/sql_prepare.cc @@ -2321,9 +2321,6 @@ static bool check_prepared_statement(Prepared_statement *stmt) if (tables) thd->get_stmt_da()->opt_clear_warning_info(thd->query_id); - if (check_dependencies_in_with_clauses(thd->lex->with_clauses_list)) - goto error; - if (sql_command_flags[sql_command] & CF_HA_CLOSE) mysql_ha_rm_tables(thd, tables); diff --git a/sql/sql_view.cc b/sql/sql_view.cc index cfd43bd..01fd424 100644 --- a/sql/sql_view.cc +++ b/sql/sql_view.cc @@ -431,12 +431,6 @@ bool mysql_create_view(THD *thd, TABLE_LIST *views, lex->link_first_table_back(view, link_to_local); view->open_type= OT_BASE_ONLY; - if (check_dependencies_in_with_clauses(lex->with_clauses_list)) - { - res= TRUE; - goto err_no_relink; - } - WSREP_TO_ISOLATION_BEGIN(WSREP_MYSQL_DB, NULL, NULL); /* @@ -1419,9 +1413,6 @@ bool mysql_make_view(THD *thd, TABLE_SHARE *share, TABLE_LIST *table, TABLE_LIST *tbl; Security_context *security_ctx= 0; - if (check_dependencies_in_with_clauses(thd->lex->with_clauses_list)) - goto err; - /* Check rights to run commands which show underlying tables. In the optimizer trace we would not like to show trace for diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index 1a045c5..ec72e39 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -280,6 +280,7 @@ void _CONCAT_UNDERSCORED(turn_parser_debug_on,yyparse)() class sp_head *sphead; class sp_name *spname; class sp_variable *spvar; + class With_element_head *with_element_head; class With_clause *with_clause; class Virtual_column_info *virtual_column; @@ -1753,7 +1754,7 @@ End SQL_MODE_ORACLE_SPECIFIC */ %type <with_clause> with_clause -%type <lex_str_ptr> query_name +%type <with_element_head> with_element_head %type <ident_sys_list> comma_separated_ident_list @@ -2946,7 +2947,11 @@ call: if (unlikely(Lex->call_statement_start(thd, $2))) MYSQL_YYABORT; } - opt_sp_cparam_list {} + opt_sp_cparam_list + { + if (Lex->check_cte_dependencies_and_resolve_references()) + MYSQL_YYABORT; + } ; /* CALL parameters */ @@ -3753,6 +3758,8 @@ expr_lex: $$->sp_lex_in_use= true; $$->set_item($2); Lex->pop_select(); //min select + if (Lex->check_cte_dependencies_and_resolve_references()) + MYSQL_YYABORT; if ($$->sphead->restore_lex(thd)) MYSQL_YYABORT; } @@ -12649,6 +12656,8 @@ do: { Lex->insert_list= $3; Lex->pop_select(); //main select + if (Lex->check_cte_dependencies_and_resolve_references()) + MYSQL_YYABORT; } ; @@ -14831,6 +14840,7 @@ with_clause: if (unlikely(with_clause == NULL)) MYSQL_YYABORT; lex->derived_tables|= DERIVED_WITH; + lex->with_cte_resolution= true; lex->curr_with_clause= with_clause; with_clause->add_to_list(Lex->with_clauses_list_last_next); if (lex->current_select && @@ -14858,7 +14868,7 @@ with_list: with_list_element: - query_name + with_element_head opt_with_column_list AS '(' query_expression ')' opt_cycle { @@ -14876,6 +14886,7 @@ with_list_element: { elem->set_cycle_list($7); } + elem->set_tables_end_pos(lex->query_tables_last); } ; @@ -14936,12 +14947,15 @@ comma_separated_ident_list: ; -query_name: +with_element_head: ident { - $$= (LEX_CSTRING *) thd->memdup(&$1, sizeof(LEX_CSTRING)); - if (unlikely($$ == NULL)) + LEX_CSTRING *name= + (LEX_CSTRING *) thd->memdup(&$1, sizeof(LEX_CSTRING)); + $$= new (thd->mem_root) With_element_head(name); + if (unlikely(name == NULL || $$ == NULL)) MYSQL_YYABORT; + $$->tables_pos.set_start_pos(Lex->query_tables_last); } ; diff --git a/sql/table.h b/sql/table.h index 58789dc..8efab4c 100644 --- a/sql/table.h +++ b/sql/table.h @@ -2109,6 +2109,29 @@ struct vers_select_conds_t struct LEX; class Index_hint; + +/* + @struct TABLE_CHAIN + @brief Subchain of global chain of table references + + The structure contains a pointer to the address of the next_global + pointer to the first TABLE_LIST objectof the subchain and the address + of the next_global pointer to the element right after the last + TABLE_LIST object of the subchain. For an empty subchain both pointers + have the same value. +*/ + +struct TABLE_CHAIN +{ + TABLE_CHAIN() {} + + TABLE_LIST **start_pos; + TABLE_LIST ** end_pos; + + void set_start_pos(TABLE_LIST **pos) { start_pos= pos; } + void set_end_pos(TABLE_LIST **pos) { end_pos= pos; } +}; + struct TABLE_LIST { TABLE_LIST() {} /* Remove gcc warning */ @@ -2443,6 +2466,20 @@ struct TABLE_LIST /* call back function for asking handler about caching in query cache */ qc_engine_callback callback_func; thr_lock_type lock_type; + + /* + Two fields below are set during parsing this table reference in the cases + when the table reference can be potentially a reference to a CTE table. + In this cases the fact that the reference is a reference to a CTE or not + will be ascertained at the very end of parsing of the query when referencies + to CTE are resolved. For references to CTE and to derived tables no mdl + requests are needed while for other table references they are. If a request + is possibly postponed the info that allows to issue this request must be + saved in 'mdl_type' and 'table_options'. + */ + enum_mdl_type mdl_type; + ulong table_options; + uint outer_join; /* Which join type */ uint shared; /* Used in multi-upd */ bool updatable; /* VIEW/TABLE can be updated now */

1 0

[Commits] 04de651: MDEV-23886 Reusing CTE inside a function fails with table doesn't exist
by IgorBabaev 25 May '21

25 May '21

revision-id: 04de651725c3eeee8f216c55e2f8133e4547fadb (mariadb-10.4.11-630-g04de651) parent(s): 67083ca4f3dd11f44810e22d370e6c3b01e3bc54 author: Igor Babaev committer: Igor Babaev timestamp: 2021-05-25 00:43:03 -0700 message: MDEV-23886 Reusing CTE inside a function fails with table doesn't exist In the code existed just before this patch binding of a table reference to the specification of the corresponding CTE happens in the function open_and_process_table(). If the table reference is not the first in the query the specification is cloned in the same way as the specification of a view is cloned for any reference of the view. This works fine for standalone queries, but does not work for stored procedures / functions for the following reason. When the first call of a stored procedure/ function SP is processed the body of SP is parsed. When a query of SP is parsed the info on each encountered table reference is put into a TABLE_LIST object linked into a global chain associated with the query. When parsing of the query is finished the basic info on the table references from this chain except table references to derived tables and information schema tables is put in one hash table associated with SP. When parsing of the body of SP is finished this hash table is used to construct TABLE_LIST objects for all table references mentioned in SP and link them into the list of such objects passed to a pre-locking process that calls open_and_process_table() for each table from the list. When a TABLE_LIST for a view is encountered the view is opened and its specification is parsed. For any table reference occurred in the specification a new TABLE_LIST object is created to be included into the list for pre-locking. After all objects in the pre-locking have been looked through the tables mentioned in the list are locked. Note that the objects referenced CTEs are just skipped here as it is impossible to resolve these references without any info on the context where they occur. Now the statements from the body of SP are executed one by one that. At the very beginning of the execution of a query the tables used in the query are opened and open_and_process_table() now is called for each table reference mentioned in the list of TABLE_LIST objects associated with the query that was built when the query was parsed. For each table reference first the reference is checked against CTEs definitions in whose scope it occurred. If such definition is found the reference is considered resolved and if this is not the first reference to the found CTE the the specification of the CTE is re-parsed and the result of the parsing is added to the parsing tree of the query as a sub-tree. If this sub-tree contains table references to other tables they are added to the list of TABLE_LIST objects associated with the query in order the referenced tables to be opened. When the procedure that opens the tables comes to the TABLE_LIST object created for a non-first reference to a CTE it discovers that the referenced table instance is not locked and reports an error. Thus processing non-first table references to a CTE similar to how references to view are processed does not work for queries used in stored procedures / functions. And the main problem is that the current pre-locking mechanism employed for stored procedures / functions does not allow to save the context in which a CTE reference occur. It's not trivial to save the info about the context where a CTE reference occurs while the resolution of the table reference cannot be done without this context and consequentially the specification for the table reference cannot be determined. This patch solves the above problem by moving resolution of all CTE references at the parsing stage. More exactly references to CTEs occurred in a query are resolved right after parsing of the query has finished. After resolution any CTE reference it is marked as a reference to to derived table. So it is excluded from the hash table created for pre-locking used base tables and view when the first call of a stored procedure / function is processed. This solution required recursive calls of the parser. The function THD::sql_parser() has been added specifically for recursive invocations of the parser. --- mysql-test/main/cte_nonrecursive.result | 201 +++++++++++++++ mysql-test/main/cte_nonrecursive.test | 202 +++++++++++++++ sql/item_subselect.cc | 1 - sql/sp_head.cc | 3 +- sql/sql_base.cc | 33 +-- sql/sql_class.cc | 56 +++++ sql/sql_class.h | 8 +- sql/sql_cte.cc | 420 ++++++++++++++++++++++---------- sql/sql_cte.h | 85 ++++++- sql/sql_lex.cc | 9 +- sql/sql_lex.h | 25 +- sql/sql_parse.cc | 15 +- sql/sql_prepare.cc | 3 - sql/sql_view.cc | 9 - sql/sql_yacc.yy | 26 +- sql/sql_yacc_ora.yy | 26 +- sql/table.h | 37 +++ 17 files changed, 948 insertions(+), 211 deletions(-) diff --git a/mysql-test/main/cte_nonrecursive.result b/mysql-test/main/cte_nonrecursive.result index f50ac50..d647057 100644 --- a/mysql-test/main/cte_nonrecursive.result +++ b/mysql-test/main/cte_nonrecursive.result @@ -1763,6 +1763,207 @@ a c 2 1 7 3 drop table t1; +# +# MDEV-23886: Stored Function returning the result of a query +# that uses CTE over a table twice +# +create table t1 (c1 int); +insert into t1 values (1),(2),(6); +create function f1() returns int return +( with cte1 as (select c1 from t1) +select sum(c1) from +(select * from cte1 union all select * from cte1) dt +); +select f1(); +f1() +18 +create function f2() returns int return +( with cte1 as (select c1 from t1) +select sum(s.c1) from cte1 as s, cte1 as t where s.c1=t.c1 +); +select f2(); +f2() +9 +create function f3() returns int return +( with cte1 as (select c1 from t1) +select +case +when exists(select 1 from cte1 where c1 between 1 and 2) then 1 +when exists(select 1 from cte1 where c1 between 5 and 6) then 2 +else 0 +end +); +select f3(); +f3() +1 +create view v1 as (select c1 from t1); +create function f4() returns int return +( select sum(c1) from +(select * from v1 union all select * from v1) dt +); +select f4(); +f4() +18 +create function f5() returns int return +( select sum(s.c1) from v1 as s, v1 as t where s.c1=t.c1 +); +select f5(); +f5() +9 +create view v2(s) as +with cte1 as (select c1 from t1) +select sum(c1) from (select * from cte1 union all select * from cte1) dt; +create function f6() returns int return +(select s from v2); +select f6(); +f6() +18 +create function f7() returns int return +( select r.s from v2 as r, v2 as t where r.s=t.s +); +select f7(); +f7() +18 +select f5() + f6(); +f5() + f6() +27 +prepare stmt from "select f5() + f6();"; +execute stmt; +f5() + f6() +27 +execute stmt; +f5() + f6() +27 +deallocate prepare stmt; +drop function f1; +drop function f2; +drop function f3; +drop function f4; +drop function f5; +drop function f6; +drop function f7; +drop view v1; +drop view v2; +create table t2 (a int, b int); +insert into t2 +with cte1 as (select c1 from t1) +select * from cte1 as s, cte1 as t where s.c1=t.c1 and s.c1 > 5; +select * from t2; +a b +6 6 +create procedure p1() +begin +insert into t2 +with cte1 as (select c1 from t1) +select * from cte1 as s, cte1 as t where s.c1=t.c1 and s.c1 <= 2 and t.c1 >= 2; +end | +call p1(); +select * from t2; +a b +6 6 +2 2 +drop procedure p1; +# checking CTE resolution for queries with hanging CTEs +with +cte1(a) as (select * from t1 where c1 <= 2), +cte2(b) as (select * from cte1 where a >= 2), +cte3 as (select * from cte1,cte2 where cte1.a < cte2.b) +select * from cte3; +a b +1 2 +select * from t2; +a b +6 6 +2 2 +with +cte1(a) as (select * from t1 where c1 <= 2), +cte2(b) as (select * from cte1 where a >= 2), +cte3 as (select * from cte1,cte2 where cte1.a < cte2.b) +select * from t2; +a b +6 6 +2 2 +with +cte1(a) as (select * from t1 where c1 <= 2), +cte2(b) as (select * from cte1 where c1 >= 2), +cte3 as (select * from cte1,cte2 where cte1.a < cte2.b) +select * from t2; +ERROR 42S22: Unknown column 'c1' in 'where clause' +with +cte1(a) as (select * from t1 where c1 <= 2), +cte2(b) as (select * from cte1 where a >= 2), +cte3 as (select * from cte1,cte2 where cte1.a < cte2.c1) +select * from t2; +ERROR 42S22: Unknown column 'cte2.c1' in 'where clause' +with +cte1 as (select * from t1 where c1 <= 2), +cte2(a,b) as (select * from cte1 as s1, cte1 as s2 where s1.c1=s2.c1) +select * from cte2; +a b +1 1 +2 2 +with +cte1 as (select * from t1 where c1 <= 2), +cte2(a,b) as (select * from cte1 as s1, cte1 as s2 where s1.c1=s2.c1) +select * from t2; +a b +6 6 +2 2 +with +cte1 as (select * from t1 where c1 <= 2), +cte2(a,b) as (select * from cte1 as s1, cte1 as s2 where s1.c1=c1) +select * from t2; +ERROR 23000: Column 'c1' in where clause is ambiguous +with cte3 as +( with cte2(a,b) as +( with cte1 as (select * from t1 where c1 <= 2) +select * from cte1 as s1, cte1 as s2 where s1.c1=s2.c1) +select r1.a,r2.b from cte2 as r1, cte2 as r2) +select * from cte3; +a b +1 1 +2 1 +1 2 +2 2 +with cte3 as +( with cte2(a,b) as +( with cte1 as (select * from t1 where c1 <= 2) +select * from cte1 as s1, cte1 as s2 where s1.c1=s2.c1) +select r1.a,r2.b from cte2 as r1, cte2 as r2) +select * from t2; +a b +6 6 +2 2 +with cte3 as +( with cte2(a,b) as +( with cte1 as (select * from t1 where c1 <= 2) +select * from cte1 as s1, cte1 as s2 where s1.c1=s2.c1) +select r1.c1,r2.c1 from cte2 as r1, cte2 as r2) +select * from t2; +ERROR 42S22: Unknown column 'r1.c1' in 'field list' +create procedure p1() +begin +insert into t2 +with cte1 as (select c1 from t1) +select * from t1 as s, t1 as t where s.c1=t.c1 and s.c1 <= 2 and t.c1 >= 2; +end | +call p1(); +select * from t2; +a b +6 6 +2 2 +2 2 +drop procedure p1; +create procedure p1() +begin +insert into t2 +with cte1 as (select a from t1) +select * from t1 as s, t1 as t where s.c1=t.c1 and s.c1 <= 2 and t.c1 >= 2; +end | +call p1(); +ERROR 42S22: Unknown column 'a' in 'field list' +drop procedure p1; +drop table t1,t2; # End of 10.2 tests # # MDEV-21673: several references to CTE that uses diff --git a/mysql-test/main/cte_nonrecursive.test b/mysql-test/main/cte_nonrecursive.test index 49df2bb..c2a0a39 100644 --- a/mysql-test/main/cte_nonrecursive.test +++ b/mysql-test/main/cte_nonrecursive.test @@ -1261,6 +1261,208 @@ select a, c from cte as r2 where a > 4; drop table t1; +--echo # +--echo # MDEV-23886: Stored Function returning the result of a query +--echo # that uses CTE over a table twice +--echo # + +create table t1 (c1 int); +insert into t1 values (1),(2),(6); + +create function f1() returns int return +( with cte1 as (select c1 from t1) + select sum(c1) from + (select * from cte1 union all select * from cte1) dt +); +select f1(); + +create function f2() returns int return +( with cte1 as (select c1 from t1) + select sum(s.c1) from cte1 as s, cte1 as t where s.c1=t.c1 +); +select f2(); + +create function f3() returns int return +( with cte1 as (select c1 from t1) + select + case + when exists(select 1 from cte1 where c1 between 1 and 2) then 1 + when exists(select 1 from cte1 where c1 between 5 and 6) then 2 + else 0 + end +); +select f3(); + +create view v1 as (select c1 from t1); + +create function f4() returns int return +( select sum(c1) from + (select * from v1 union all select * from v1) dt +); +select f4(); + +create function f5() returns int return +( select sum(s.c1) from v1 as s, v1 as t where s.c1=t.c1 +); +select f5(); + +create view v2(s) as +with cte1 as (select c1 from t1) +select sum(c1) from (select * from cte1 union all select * from cte1) dt; + +create function f6() returns int return +(select s from v2); +select f6(); + +create function f7() returns int return +( select r.s from v2 as r, v2 as t where r.s=t.s +); +select f7(); + +select f5() + f6(); + +prepare stmt from "select f5() + f6();"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +drop function f1; +drop function f2; +drop function f3; +drop function f4; +drop function f5; +drop function f6; +drop function f7; + +drop view v1; +drop view v2; + +create table t2 (a int, b int); + +insert into t2 +with cte1 as (select c1 from t1) +select * from cte1 as s, cte1 as t where s.c1=t.c1 and s.c1 > 5; + +select * from t2; + +delimiter |; + +create procedure p1() +begin +insert into t2 +with cte1 as (select c1 from t1) +select * from cte1 as s, cte1 as t where s.c1=t.c1 and s.c1 <= 2 and t.c1 >= 2; +end | + +delimiter ;| + +call p1(); +select * from t2; + +drop procedure p1; + +--echo # checking CTE resolution for queries with hanging CTEs + +with +cte1(a) as (select * from t1 where c1 <= 2), +cte2(b) as (select * from cte1 where a >= 2), +cte3 as (select * from cte1,cte2 where cte1.a < cte2.b) +select * from cte3; + +select * from t2; + +with +cte1(a) as (select * from t1 where c1 <= 2), +cte2(b) as (select * from cte1 where a >= 2), +cte3 as (select * from cte1,cte2 where cte1.a < cte2.b) +select * from t2; + +--error ER_BAD_FIELD_ERROR +with +cte1(a) as (select * from t1 where c1 <= 2), +cte2(b) as (select * from cte1 where c1 >= 2), +cte3 as (select * from cte1,cte2 where cte1.a < cte2.b) +select * from t2; + +--error ER_BAD_FIELD_ERROR +with +cte1(a) as (select * from t1 where c1 <= 2), +cte2(b) as (select * from cte1 where a >= 2), +cte3 as (select * from cte1,cte2 where cte1.a < cte2.c1) +select * from t2; + +with +cte1 as (select * from t1 where c1 <= 2), +cte2(a,b) as (select * from cte1 as s1, cte1 as s2 where s1.c1=s2.c1) +select * from cte2; + +with +cte1 as (select * from t1 where c1 <= 2), +cte2(a,b) as (select * from cte1 as s1, cte1 as s2 where s1.c1=s2.c1) +select * from t2; + +--error ER_NON_UNIQ_ERROR +with +cte1 as (select * from t1 where c1 <= 2), +cte2(a,b) as (select * from cte1 as s1, cte1 as s2 where s1.c1=c1) +select * from t2; + +with cte3 as +( with cte2(a,b) as + ( with cte1 as (select * from t1 where c1 <= 2) + select * from cte1 as s1, cte1 as s2 where s1.c1=s2.c1) + select r1.a,r2.b from cte2 as r1, cte2 as r2) +select * from cte3; + +with cte3 as +( with cte2(a,b) as + ( with cte1 as (select * from t1 where c1 <= 2) + select * from cte1 as s1, cte1 as s2 where s1.c1=s2.c1) + select r1.a,r2.b from cte2 as r1, cte2 as r2) +select * from t2; + +--error ER_BAD_FIELD_ERROR +with cte3 as +( with cte2(a,b) as + ( with cte1 as (select * from t1 where c1 <= 2) + select * from cte1 as s1, cte1 as s2 where s1.c1=s2.c1) + select r1.c1,r2.c1 from cte2 as r1, cte2 as r2) +select * from t2; + +delimiter |; + +create procedure p1() +begin +insert into t2 +with cte1 as (select c1 from t1) +select * from t1 as s, t1 as t where s.c1=t.c1 and s.c1 <= 2 and t.c1 >= 2; +end | + +delimiter ;| + +call p1(); +select * from t2; + +drop procedure p1; + +delimiter |; + +create procedure p1() +begin +insert into t2 +with cte1 as (select a from t1) +select * from t1 as s, t1 as t where s.c1=t.c1 and s.c1 <= 2 and t.c1 >= 2; +end | + +delimiter ;| + +--error ER_BAD_FIELD_ERROR +call p1(); + +drop procedure p1; + +drop table t1,t2; + --echo # End of 10.2 tests --echo # diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index 53a6847..0fd6454 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -1810,7 +1810,6 @@ double Item_in_subselect::val_real() As far as Item_in_subselect called only from Item_in_optimizer this method should not be used */ - DBUG_ASSERT(0); DBUG_ASSERT(fixed == 1); if (forced_const) return value; diff --git a/sql/sp_head.cc b/sql/sp_head.cc index aa4f809..3ea4938 100644 --- a/sql/sp_head.cc +++ b/sql/sp_head.cc @@ -3411,8 +3411,7 @@ sp_lex_keeper::reset_lex_and_exec_core(THD *thd, uint *nextp, Json_writer_object trace_command(thd); Json_writer_array trace_command_steps(thd, "steps"); if (open_tables) - res= check_dependencies_in_with_clauses(m_lex->with_clauses_list) || - instr->exec_open_and_lock_tables(thd, m_lex->query_tables); + res= instr->exec_open_and_lock_tables(thd, m_lex->query_tables); if (likely(!res)) { diff --git a/sql/sql_base.cc b/sql/sql_base.cc index 41f02fb..d8baa11 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -3695,7 +3695,11 @@ open_and_process_table(THD *thd, TABLE_LIST *tables, uint *counter, uint flags, if (tables->derived) { if (!tables->view) + { + if (!tables->is_derived()) + tables->set_derived(); goto end; + } /* We restore view's name and database wiped out by derived tables processing and fall back to standard open process in order to @@ -3705,35 +3709,6 @@ open_and_process_table(THD *thd, TABLE_LIST *tables, uint *counter, uint flags, tables->db= tables->view_db; tables->table_name= tables->view_name; } - else if (tables->select_lex) - { - /* - Check whether 'tables' refers to a table defined in a with clause. - If so set the reference to the definition in tables->with. - */ - if (!tables->with) - tables->with= tables->select_lex->find_table_def_in_with_clauses(tables); - /* - If 'tables' is defined in a with clause set the pointer to the - specification from its definition in tables->derived. - */ - if (tables->with) - { - if (tables->is_recursive_with_table() && - !tables->is_with_table_recursive_reference()) - { - tables->with->rec_outer_references++; - With_element *with_elem= tables->with; - while ((with_elem= with_elem->get_next_mutually_recursive()) != - tables->with) - with_elem->rec_outer_references++; - } - if (tables->set_as_with_table(thd, tables->with)) - DBUG_RETURN(1); - else - goto end; - } - } if (!tables->derived && is_infoschema_db(&tables->db)) { diff --git a/sql/sql_class.cc b/sql/sql_class.cc index 7864623..7e43605 100644 --- a/sql/sql_class.cc +++ b/sql/sql_class.cc @@ -2790,6 +2790,62 @@ void THD::close_active_vio() #endif +/* + @brief MySQL parser used for recursive invocations + + @param old_lex The LEX structure in the state when this parser + is called recursively + @param lex The LEX structure used to parse a new SQL fragment + @param str The SQL fragment to parse + @param str_len The length of the SQL fragment to parse + @param stmt_prepare_mode true <=> when parsing a prepare statement + + @details + This function is to be used when parsing of an SQL fragment is + needed within one of the grammar rules. + + @notes + Currently the function is used only when the specification of a CTE + is parsed for the not first and not recursive references of the CTE. + + @retval false On a successful parsing of the fragment + @retval true Otherwise +*/ + +bool THD::sql_parser(LEX *old_lex, LEX *lex, + char *str, uint str_len, bool stmt_prepare_mode) +{ + extern int MYSQLparse(THD * thd); + extern int ORAparse(THD * thd); + + bool parse_status= false; + Parser_state parser_state; + Parser_state *old_parser_state= m_parser_state; + + if (parser_state.init(this, str, str_len)) + return true; + + m_parser_state= &parser_state; + parser_state.m_lip.stmt_prepare_mode= stmt_prepare_mode; + parser_state.m_lip.multi_statements= false; + parser_state.m_lip.m_digest= NULL; + + lex->param_list= old_lex->param_list; + lex->sphead= old_lex->sphead; + lex->spname= old_lex->spname; + lex->spcont= old_lex->spcont; + lex->sp_chistics= old_lex->sp_chistics; + lex->trg_chistics= old_lex->trg_chistics; + + parse_status= (variables.sql_mode & MODE_ORACLE) ? + ORAparse(this) : MYSQLparse(this) != 0; + + m_parser_state= old_parser_state; + + return parse_status; +} + + struct Item_change_record: public ilink { Item **place; diff --git a/sql/sql_class.h b/sql/sql_class.h index f754524..cb34067 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -4244,14 +4244,11 @@ class THD: public THD_count, /* this must be first */ to resolve all CTE names as we don't need this message to be thrown for any CTE references. */ - if (!lex->with_clauses_list) + if (!lex->with_cte_resolution) { my_message(ER_NO_DB_ERROR, ER(ER_NO_DB_ERROR), MYF(0)); return TRUE; } - /* This will allow to throw an error later for non-CTE references */ - to->str= NULL; - to->length= 0; return FALSE; } @@ -5047,6 +5044,9 @@ class THD: public THD_count, /* this must be first */ Item *sp_prepare_func_item(Item **it_addr, uint cols= 1); bool sp_eval_expr(Field *result_field, Item **expr_item_ptr); + bool sql_parser(LEX *old_lex, LEX *lex, + char *str, uint str_len, bool stmt_prepare_mode); + }; /** A short cut for thd->get_stmt_da()->set_ok_status(). */ diff --git a/sql/sql_cte.cc b/sql/sql_cte.cc index f3861eb..dfcb4e1 100644 --- a/sql/sql_cte.cc +++ b/sql/sql_cte.cc @@ -83,7 +83,7 @@ void st_select_lex_unit::set_with_clause(With_clause *with_cl) true on failure */ -bool check_dependencies_in_with_clauses(With_clause *with_clauses_list) +bool LEX::check_dependencies_in_with_clauses() { for (With_clause *with_clause= with_clauses_list; with_clause; @@ -101,6 +101,201 @@ bool check_dependencies_in_with_clauses(With_clause *with_clauses_list) /** @brief + Resolve references to CTE in specification of hanging CTE + + @details + A CTE to which there are no references in the query is called hanging CTE. + Although such CTE is not used for execution its specification must be + subject to context analysis. All errors concerning references to + non-existing tables or fields occurred in the specification must be + reported as well as all other errors caught at the prepare stage. + The specification of a hanging CTE might contain references to other + CTE outside of the specification and within it if the specification + contains a with clause. This function resolves all such references for + all hanging CTEs encountered in the processed query. + + @retval + false on success + true on failure +*/ + +bool +LEX::resolve_references_to_cte_in_hanging_cte() +{ + for (With_clause *with_clause= with_clauses_list; + with_clause; with_clause= with_clause->next_with_clause) + { + for (With_element *with_elem= with_clause->with_list.first; + with_elem; with_elem= with_elem->next) + { + if (!with_elem->is_referenced()) + { + TABLE_LIST *first_tbl= + with_elem->spec->first_select()->table_list.first; + TABLE_LIST **with_elem_end_pos= with_elem->head->tables_pos.end_pos; + if (first_tbl && resolve_references_to_cte(first_tbl, with_elem_end_pos)) + return true; + } + } + } + return false; +} + + +/** + @brief + Resolve table references to CTE from a sub-chain of table references + + @param tables Points to the beginning of the sub-chain + @param tables_last Points to the address with the sub-chain barrier + + @details + The method resolves tables references to CTE from the chain of + table references specified by the parameters 'tables' and 'tables_last'. + It resolves the references against the CTE definition occurred in a query + or the specification of a CTE whose parsing tree is represented by + this LEX structure. The method is always called right after the process + of parsing the query or of the specification of a CTE has been finished, + thus the chain of table references used in the parsed fragment has been + already built. It is assumed that parameters of the method specify a + a sub-chain of this chain. + If a table reference can be potentially a table reference to a CTE and it + has not been resolved yet then the method tries to find the definition + of the CTE against which the reference can be resolved. If it succeeds + it sets the field TABLE_LIST::with to point to the found definition. + It also sets the field TABLE_LIST::derived to point to the specification + of the found CTE and sets TABLE::db.str to empty_c_string. This will + allow to handle this table reference like a reference to a derived handle. + If another table reference has been already resolved against this CTE + and this CTE is not recursive then a clone of the CTE specification is + constructed using the function With_element::clone_parsed_spec() and + TABLE_LIST::derived is set to point to this clone rather than to the + original specification. + If the method does not find a matched CTE definition in the parsed fragment + then in the case when the flag this->only_cte_resolution is set to true + it just moves to the resolution of the next table reference from the + specified sub-chain while in the case when this->only_cte_resolution is set + to false the method additionally sets an mdl request for this table + reference. + + @notes + The flag this->only_cte_resolution is set to true in the cases when + the failure to resolve a table reference as a CTE reference within + the fragment associated with this LEX structure does not imply that + this table reference cannot be resolved as such at all. + + @retval false On success: no errors reported, no memory allocations failed + @retval true Otherwise +*/ + +bool LEX::resolve_references_to_cte(TABLE_LIST *tables, + TABLE_LIST **tables_last) +{ + With_element *with_elem= 0; + + for (TABLE_LIST *tbl= tables; tbl != *tables_last; tbl= tbl->next_global) + { + if (tbl->derived) + continue; + if (!tbl->db.str && !tbl->with) + tbl->with= tbl->select_lex->find_table_def_in_with_clauses(tbl); + if (!tbl->with) // no CTE matches table reference tbl + { + if (only_cte_resolution) + continue; + if (!tbl->db.str) // no database specified in table reference tbl + { + if (!thd->db.str) // no default database is set + { + my_message(ER_NO_DB_ERROR, ER(ER_NO_DB_ERROR), MYF(0)); + return true; + } + if (copy_db_to(&tbl->db)) + return true; + if (!(tbl->table_options & TL_OPTION_ALIAS)) + tbl->mdl_request.init(MDL_key::TABLE, tbl->db.str, + tbl->table_name.str, + tbl->mdl_type, MDL_TRANSACTION); + tbl->mdl_request.set_type((tbl->lock_type >= TL_WRITE_ALLOW_WRITE) ? + MDL_SHARED_WRITE : MDL_SHARED_READ); + } + continue; + } + with_elem= tbl->with; + if (tbl->is_recursive_with_table() && + !tbl->is_with_table_recursive_reference()) + { + tbl->with->rec_outer_references++; + while ((with_elem= with_elem->get_next_mutually_recursive()) != + tbl->with) + with_elem->rec_outer_references++; + } + if (!with_elem->is_used_in_query || with_elem->is_recursive) + { + tbl->derived= with_elem->spec; + if (tbl->derived != tbl->select_lex->master_unit() && + !with_elem->is_recursive && + !tbl->is_with_table_recursive_reference()) + { + tbl->derived->move_as_slave(tbl->select_lex); + } + with_elem->is_used_in_query= true; + } + else + { + if (!(tbl->derived= tbl->with->clone_parsed_spec(thd->lex, tbl))) + return true; + } + tbl->db.str= empty_c_string; + tbl->db.length= 0; + tbl->schema_table= 0; + if (tbl->derived) + { + tbl->derived->first_select()->set_linkage(DERIVED_TABLE_TYPE); + tbl->select_lex->add_statistics(tbl->derived); + } + if (tbl->with->is_recursive && tbl->is_with_table_recursive_reference()) + continue; + with_elem->inc_references(); + } + return false; +} + + +/** + @brief + Find out dependencies between CTEs, resolve references to them + + @details + The function can be called in two modes. With this->with_cte_resolution + set to false the function only finds out all dependencies between CTEs + used in a query expression with a WITH clause whose parsing has been + just finished. Based on these dependencies recursive CTEs are detected. + If this->with_cte_resolution is set to true the function additionally + resolves all references to CTE occurred in this query expression. + + @retval + true on failure + false on success +*/ + +bool +LEX::check_cte_dependencies_and_resolve_references() +{ + if (check_dependencies_in_with_clauses()) + return true; + if (!with_cte_resolution) + return false; + if (resolve_references_to_cte(query_tables, query_tables_last)) + return true; + if (resolve_references_to_cte_in_hanging_cte()) + return true; + return false; +} + + +/** + @brief Check dependencies between tables defined in this with clause @details @@ -137,10 +332,11 @@ bool With_clause::check_dependencies() elem != with_elem; elem= elem->next) { - if (lex_string_cmp(system_charset_info, with_elem->query_name, - elem->query_name) == 0) + if (lex_string_cmp(system_charset_info, with_elem->get_name(), + elem->get_name()) == 0) { - my_error(ER_DUP_QUERY_NAME, MYF(0), with_elem->query_name->str); + my_error(ER_DUP_QUERY_NAME, MYF(0), + with_elem->get_name_str()); return true; } } @@ -247,13 +443,12 @@ With_element *With_clause::find_table_def(TABLE_LIST *table, with_elem != barrier; with_elem= with_elem->next) { - if (my_strcasecmp(system_charset_info, with_elem->query_name->str, - table->table_name.str) == 0 && + if (my_strcasecmp(system_charset_info, with_elem->get_name_str(), + table->table_name.str) == 0 && !table->is_fqtn) { table->set_derived(); - table->db.str= empty_c_string; - table->db.length= 0; + with_elem->referenced= true; return with_elem; } } @@ -610,7 +805,7 @@ bool With_clause::check_anchors() if (elem == with_elem) { my_error(ER_RECURSIVE_WITHOUT_ANCHORS, MYF(0), - with_elem->query_name->str); + with_elem->get_name_str()); return true; } } @@ -643,7 +838,7 @@ bool With_clause::check_anchors() if (elem->work_dep_map & elem->get_elem_map()) { my_error(ER_UNACCEPTABLE_MUTUAL_RECURSION, MYF(0), - with_elem->query_name->str); + with_elem->get_name_str()); return true; } } @@ -797,7 +992,8 @@ bool With_element::set_unparsed_spec(THD *thd, @brief Create a clone of the specification for the given with table - @param thd The context of the statement containing this with element + @param old_lex The LEX structure created for the query or CTE specification + where this With_element is defined @param with_table The reference to the table defined in this element for which the clone is created. @@ -807,12 +1003,13 @@ bool With_element::set_unparsed_spec(THD *thd, this element. The clone is created when the string with the specification saved in unparsed_spec is fed into the parser as an input string. The parsing - this string a unit object representing the specification is build. + this string a unit object representing the specification is built. A chain of all table references occurred in the specification is also formed. The method includes the new unit and its sub-unit into hierarchy of the units of the main query. I also insert the constructed chain of the table references into the chain of all table references of the main query. + The method resolves all references to CTE in the clone. @note Clones is created only for not first references to tables defined in @@ -828,114 +1025,128 @@ bool With_element::set_unparsed_spec(THD *thd, NULL - otherwise */ -st_select_lex_unit *With_element::clone_parsed_spec(THD *thd, +st_select_lex_unit *With_element::clone_parsed_spec(LEX *old_lex, TABLE_LIST *with_table) { + THD *thd= old_lex->thd; LEX *lex; - st_select_lex_unit *res= NULL; - Query_arena backup; - Query_arena *arena= thd->activate_stmt_arena_if_needed(&backup); + st_select_lex_unit *res= NULL; if (!(lex= (LEX*) new(thd->mem_root) st_lex_local)) - { - if (arena) - thd->restore_active_arena(arena, &backup); return res; - } - LEX *old_lex= thd->lex; thd->lex= lex; bool parse_status= false; - Parser_state parser_state; - TABLE_LIST *spec_tables; - TABLE_LIST *spec_tables_tail; st_select_lex *with_select; char save_end= unparsed_spec.str[unparsed_spec.length]; ((char*) &unparsed_spec.str[unparsed_spec.length])[0]= '\0'; - if (parser_state.init(thd, (char*) unparsed_spec.str, (unsigned int)unparsed_spec.length)) - goto err; - parser_state.m_lip.stmt_prepare_mode= stmt_prepare_mode; - parser_state.m_lip.multi_statements= false; - parser_state.m_lip.m_digest= NULL; lex_start(thd); lex->clone_spec_offset= unparsed_spec_offset; - lex->param_list= old_lex->param_list; - lex->sphead= old_lex->sphead; - lex->spname= old_lex->spname; - lex->spcont= old_lex->spcont; - lex->sp_chistics= old_lex->sp_chistics; - - lex->stmt_lex= old_lex; - parse_status= parse_sql(thd, &parser_state, 0); + lex->with_cte_resolution= true; + + /* + The specification of a CTE is to be parsed as a regular query. + At the very end of the parsing query the function + check_cte_dependencies_and_resolve_references() will be called. + It will check the dependencies between CTEs that are defined + within the query and will resolve CTE references in this query. + If a table reference is not resolved as a CTE reference within + this query it still can be resolved as a reference to a CTE defined + in the same clause as the CTE whose specification is to be parsed + or defined in an embedding CTE definition. + + Example: + with + cte1 as ( ... ), + cte2 as ([WITH ...] select ... from cte1 ...) + select ... from cte2 as r, ..., cte2 as s ... + + Here the specification of cte2 has be cloned for table reference + with alias s1. The specification contains a reference to cte1 + that is defined outside this specification. If the reference to + cte1 cannot be resolved within the specification of cte2 it's + not necessarily has to be a reference to a non-CTE table. That's + why the flag lex->only_cte_resolution has to be set to true + before parsing of the specification of cte2 invoked by this + function starts. Otherwise an mdl_lock would be requested for s + and this would not be correct. + */ + + lex->only_cte_resolution= true; + + lex->stmt_lex= old_lex->stmt_lex ? old_lex->stmt_lex : old_lex; + + parse_status= thd->sql_parser(old_lex, lex, + (char*) unparsed_spec.str, + (unsigned int)unparsed_spec.length, + stmt_prepare_mode); + ((char*) &unparsed_spec.str[unparsed_spec.length])[0]= save_end; - with_select= lex->first_select_lex(); + with_select= lex->unit.first_select(); if (parse_status) goto err; - if (check_dependencies_in_with_clauses(lex->with_clauses_list)) - goto err; - - spec_tables= lex->query_tables; - spec_tables_tail= 0; - for (TABLE_LIST *tbl= spec_tables; - tbl; - tbl= tbl->next_global) - { - if (!tbl->derived && !tbl->schema_table && - thd->open_temporary_table(tbl)) - goto err; - spec_tables_tail= tbl; - } - if (spec_tables) + /* + The global chain of TABLE_LIST objects created for the specification that + just has been parsed is added to such chain that contains the reference + to the CTE whose specification is parsed right after the TABLE_LIST object + created for the reference. + */ + if (lex->query_tables) { - if (with_table->next_global) + head->tables_pos.set_start_pos(&with_table->next_global); + head->tables_pos.set_end_pos(lex->query_tables_last); + TABLE_LIST *next_tbl= with_table->next_global; + if (next_tbl) { - spec_tables_tail->next_global= with_table->next_global; - with_table->next_global->prev_global= &spec_tables_tail->next_global; + *(lex->query_tables->prev_global= next_tbl->prev_global)= + lex->query_tables; + *(next_tbl->prev_global= lex->query_tables_last)= next_tbl; } else { - old_lex->query_tables_last= &spec_tables_tail->next_global; + *(lex->query_tables->prev_global= old_lex->query_tables_last)= + lex->query_tables; + old_lex->query_tables_last= lex->query_tables_last; } - spec_tables->prev_global= &with_table->next_global; - with_table->next_global= spec_tables; } res= &lex->unit; res->with_element= this; + /* + The unit of the specification that just has been parsed is included + as a slave of the select that contained in its from list the table + reference for which the unit has been created. + */ lex->unit.include_down(with_table->select_lex); - lex->unit.set_slave(with_select); + lex->unit.set_slave(with_select); + lex->unit.cloned_from= spec; old_lex->all_selects_list= (st_select_lex*) (lex->all_selects_list-> insert_chain_before( (st_select_lex_node **) &(old_lex->all_selects_list), with_select)); - if (check_dependencies_in_with_clauses(lex->with_clauses_list)) - res= NULL; + /* - Resolve references to CTE from the spec_tables list that has not - been resolved yet. + Now all references to the CTE defined outside of the cloned specification + has to be resolved. Additionally if old_lex->only_cte_resolution == false + for the table references that has not been resolved requests for mdl_locks + has to be set. */ - for (TABLE_LIST *tbl= spec_tables; - tbl; - tbl= tbl->next_global) + lex->only_cte_resolution= old_lex->only_cte_resolution; + if (lex->resolve_references_to_cte(lex->query_tables, + lex->query_tables_last)) { - if (!tbl->with) - tbl->with= with_select->find_table_def_in_with_clauses(tbl); - if (tbl == spec_tables_tail) - break; - } - if (check_table_access(thd, SELECT_ACL, spec_tables, FALSE, UINT_MAX, FALSE)) + res= NULL; goto err; + } - lex->sphead= NULL; // in order not to delete lex->sphead + lex->sphead= NULL; // in order not to delete lex->sphead lex_end(lex); err: - if (arena) - thd->restore_active_arena(arena, &backup); thd->lex= old_lex; return res; } @@ -1105,59 +1316,6 @@ With_element *st_select_lex::find_table_def_in_with_clauses(TABLE_LIST *table) } -/** - @brief - Set the specifying unit in this reference to a with table - - @details - The method assumes that the given element with_elem defines the table T - this table reference refers to. - If this is the first reference to T the method just sets its specification - in the field 'derived' as the unit that yields T. Otherwise the method - first creates a clone specification and sets rather this clone in this field. - - @retval - false on success - true on failure -*/ - -bool TABLE_LIST::set_as_with_table(THD *thd, With_element *with_elem) -{ - if (table) - { - /* - This table was prematurely identified as a temporary table. - We correct it here, but it's not a nice solution in the case - when the temporary table with this name is not used anywhere - else in the query. - */ - thd->mark_tmp_table_as_free_for_reuse(table); - table= 0; - } - with= with_elem; - schema_table= NULL; - if (!with_elem->is_referenced() || with_elem->is_recursive) - { - derived= with_elem->spec; - if (derived != select_lex->master_unit() && - !with_elem->is_recursive && - !is_with_table_recursive_reference()) - { - derived->move_as_slave(select_lex); - } - } - else - { - if(!(derived= with_elem->clone_parsed_spec(thd, this))) - return true; - } - derived->first_select()->set_linkage(DERIVED_TABLE_TYPE); - select_lex->add_statistics(derived); - with_elem->inc_references(); - return false; -} - - bool TABLE_LIST::is_recursive_with_table() { return with && with->is_recursive; @@ -1257,7 +1415,7 @@ bool st_select_lex::check_unrestricted_recursive(bool only_standard_compliant) if (only_standard_compliant && with_elem->is_unrestricted()) { my_error(ER_NOT_STANDARD_COMPLIANT_RECURSIVE, - MYF(0), with_elem->query_name->str); + MYF(0), with_elem->get_name_str()); return true; } @@ -1457,7 +1615,7 @@ void With_clause::print(String *str, enum_query_type query_type) void With_element::print(String *str, enum_query_type query_type) { - str->append(query_name); + str->append(get_name()); if (column_list.elements) { List_iterator_fast<LEX_CSTRING> li(column_list); diff --git a/sql/sql_cte.h b/sql/sql_cte.h index 80d5664..4693599 100644 --- a/sql/sql_cte.h +++ b/sql/sql_cte.h @@ -25,6 +25,39 @@ struct st_unit_ctxt_elem; /** + @class With_element_head + @brief Head of the definition of a CTE table + + It contains the name of the CTE and it contains the position of the subchain + of table references used in the definition in the global chain of table + references used in the query where this definition is encountered. +*/ + +class With_element_head : public Sql_alloc +{ + /* The name of the defined CTE */ + LEX_CSTRING *query_name; + +public: + /* + The structure describing the subchain of the table references used in + the specification of the defined CTE in the global chain of table + references used in the query. The structure is fully defined only + after the CTE definition has been parsed. + */ + TABLE_CHAIN tables_pos; + + With_element_head(LEX_CSTRING *name) + : query_name(name) + { + tables_pos.set_start_pos(0); + tables_pos.set_end_pos(0); + } + friend class With_element; +}; + + +/** @class With_element @brief Definition of a CTE table @@ -85,9 +118,22 @@ class With_element : public Sql_alloc subqueries and specifications of other with elements). */ uint references; + + /* + true <=> this With_element is referred in the query in which the + element is defined + */ + bool referenced; + + /* + true <=> this With_element is needed for the execution of the query + in which the element is defined + */ + bool is_used_in_query; + /* Unparsed specification of the query that specifies this element. - It used to build clones of the specification if they are needed. + It's used to build clones of the specification if they are needed. */ LEX_CSTRING unparsed_spec; /* Offset of the specification in the input string */ @@ -101,10 +147,12 @@ class With_element : public Sql_alloc public: /* - The name of the table introduced by this with elememt. The name - can be used in FROM lists of the queries in the scope of the element. + Contains the name of the defined With element and the position of + the subchain of the tables references used by its definition in the + global chain of TABLE_LIST objects created for the whole query. */ - LEX_CSTRING *query_name; + With_element_head *head; + /* Optional list of column names to name the columns of the table introduced by this with element. It is used in the case when the names are not @@ -162,18 +210,27 @@ class With_element : public Sql_alloc /* List of derived tables containing recursive references to this CTE */ SQL_I_List<TABLE_LIST> derived_with_rec_ref; - With_element(LEX_CSTRING *name, + With_element(With_element_head *h, List <LEX_CSTRING> list, st_select_lex_unit *unit) : next(NULL), base_dep_map(0), derived_dep_map(0), sq_dep_map(0), work_dep_map(0), mutually_recursive(0), top_level_dep_map(0), sq_rec_ref(NULL), next_mutually_recursive(NULL), references(0), - query_name(name), column_list(list), spec(unit), + referenced(false), is_used_in_query(false), + head(h), column_list(list), spec(unit), is_recursive(false), rec_outer_references(0), with_anchor(false), level(0), rec_result(NULL) { unit->with_element= this; } + LEX_CSTRING *get_name() { return head->query_name; } + const char *get_name_str() { return get_name()->str; } + + void set_tables_start_pos(TABLE_LIST **pos) + { head->tables_pos.set_start_pos(pos); } + void set_tables_end_pos(TABLE_LIST **pos) + { head->tables_pos.set_end_pos(pos); } + bool check_dependencies_in_spec(); void check_dependencies_in_select(st_select_lex *sl, st_unit_ctxt_elem *ctxt, @@ -200,9 +257,9 @@ class With_element : public Sql_alloc bool set_unparsed_spec(THD *thd, const char *spec_start, const char *spec_end, my_ptrdiff_t spec_offset); - st_select_lex_unit *clone_parsed_spec(THD *thd, TABLE_LIST *with_table); + st_select_lex_unit *clone_parsed_spec(LEX *old_lex, TABLE_LIST *with_table); - bool is_referenced() { return references != 0; } + bool is_referenced() { return referenced; } void inc_references() { references++; } @@ -260,6 +317,12 @@ class With_element : public Sql_alloc void prepare_for_next_iteration(); friend class With_clause; + + friend + bool LEX::resolve_references_to_cte(TABLE_LIST *tables, + TABLE_LIST **tables_last); + friend + bool LEX::resolve_references_to_cte_in_hanging_cte(); }; const uint max_number_of_elements_in_with_clause= sizeof(table_map)*8; @@ -358,8 +421,10 @@ class With_clause : public Sql_alloc friend class With_element; friend - bool - check_dependencies_in_with_clauses(With_clause *with_clauses_list); + bool LEX::check_dependencies_in_with_clauses(); + + friend + bool LEX::resolve_references_to_cte_in_hanging_cte(); }; inline diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index 386d179..c63a3b5 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -721,6 +721,8 @@ void LEX::start(THD *thd_arg) explain_json= false; context_analysis_only= 0; derived_tables= 0; + with_cte_resolution= false; + only_cte_resolution= false; safe_to_cache_query= 1; parsing_options.reset(); empty_field_list_on_rset= 0; @@ -2374,6 +2376,7 @@ void st_select_lex_unit::init_query() is_view= false; with_clause= 0; with_element= 0; + cloned_from= 0; columns_are_renamed= false; intersect_mark= NULL; with_wrapped_tvc= false; @@ -8326,6 +8329,8 @@ bool LEX::check_main_unit_semantics() if (unit.set_nest_level(0) || unit.check_parameters(first_select_lex())) return TRUE; + if (check_cte_dependencies_and_resolve_references()) + return TRUE; return FALSE; } @@ -9021,8 +9026,8 @@ void st_select_lex::add_statistics(SELECT_LEX_UNIT *unit) bool LEX::main_select_push(bool service) { DBUG_ENTER("LEX::main_select_push"); - current_select_number= 1; - builtin_select.select_number= 1; + current_select_number= ++thd->lex->stmt_lex->current_select_number; + builtin_select.select_number= current_select_number; builtin_select.is_service_select= service; if (push_select(&builtin_select)) DBUG_RETURN(TRUE); diff --git a/sql/sql_lex.h b/sql/sql_lex.h index 2389b23..eb21419 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -917,6 +917,8 @@ class st_select_lex_unit: public st_select_lex_node { With_clause *with_clause; /* With element where this unit is used as the specification (if any) */ With_element *with_element; + /* The unit used as a CTE specification from which this unit is cloned */ + st_select_lex_unit *cloned_from; /* thread handler */ THD *thd; /* @@ -1501,7 +1503,9 @@ class st_select_lex: public st_select_lex_node } With_element *get_with_element() { - return master_unit()->with_element; + return master_unit()->cloned_from ? + master_unit()->cloned_from->with_element : + master_unit()->with_element; } With_element *find_table_def_in_with_clauses(TABLE_LIST *table); bool check_unrestricted_recursive(bool only_standard_compliant); @@ -3324,6 +3328,20 @@ struct LEX: public Query_tables_list */ uint8 derived_tables; uint8 context_analysis_only; + /* + true <=> The parsed fragment requires resolution of references to CTE + at the end of parsing. This name resolution process involves searching + for possible dependencies between CTE defined in the parsed fragment and + detecting possible recursive references. + The flag is set to true if the fragment contains CTE definitions. + */ + bool with_cte_resolution; + /* + true <=> only resolution of references to CTE are required in the parsed + fragment, no checking of dependencies between CTE is required. + This flag is used only when parsing clones of CTE specifications. + */ + bool only_cte_resolution; bool local_file; bool check_exists; bool autocommit; @@ -4571,6 +4589,11 @@ struct LEX: public Query_tables_list select_stack[0]->is_service_select); } + bool check_dependencies_in_with_clauses(); + bool resolve_references_to_cte_in_hanging_cte(); + bool check_cte_dependencies_and_resolve_references(); + bool resolve_references_to_cte(TABLE_LIST *tables, + TABLE_LIST **tables_last); }; diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index 3ae7c7c..82b81a2b 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -3554,9 +3554,6 @@ mysql_execute_command(THD *thd) thd->get_stmt_da()->opt_clear_warning_info(thd->query_id); } - if (check_dependencies_in_with_clauses(thd->lex->with_clauses_list)) - DBUG_RETURN(1); - #ifdef HAVE_REPLICATION if (unlikely(thd->slave_thread)) { @@ -8187,7 +8184,7 @@ TABLE_LIST *st_select_lex::add_table_to_list(THD *thd, ptr->is_fqtn= TRUE; ptr->db= table->db; } - else if (lex->copy_db_to(&ptr->db)) + else if (!lex->with_cte_resolution && lex->copy_db_to(&ptr->db)) DBUG_RETURN(0); else ptr->is_fqtn= FALSE; @@ -8204,7 +8201,9 @@ TABLE_LIST *st_select_lex::add_table_to_list(THD *thd, } ptr->table_name= table->table; - ptr->lock_type= lock_type; + ptr->lock_type= lock_type; + ptr->mdl_type= mdl_type; + ptr->table_options= table_options; ptr->updating= MY_TEST(table_options & TL_OPTION_UPDATING); /* TODO: remove TL_OPTION_FORCE_INDEX as it looks like it's not used */ ptr->force_index= MY_TEST(table_options & TL_OPTION_FORCE_INDEX); @@ -8886,8 +8885,10 @@ void st_select_lex::set_lock_for_tables(thr_lock_type lock_type, bool for_update { tables->lock_type= lock_type; tables->updating= for_update; - tables->mdl_request.set_type((lock_type >= TL_WRITE_ALLOW_WRITE) ? - MDL_SHARED_WRITE : MDL_SHARED_READ); + + if (tables->db.str && tables->db.str[0]) + tables->mdl_request.set_type((lock_type >= TL_WRITE_ALLOW_WRITE) ? + MDL_SHARED_WRITE : MDL_SHARED_READ); } DBUG_VOID_RETURN; } diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc index 8d094a0..816990f 100644 --- a/sql/sql_prepare.cc +++ b/sql/sql_prepare.cc @@ -2376,9 +2376,6 @@ static bool check_prepared_statement(Prepared_statement *stmt) if (tables) thd->get_stmt_da()->opt_clear_warning_info(thd->query_id); - if (check_dependencies_in_with_clauses(thd->lex->with_clauses_list)) - goto error; - if (sql_command_flags[sql_command] & CF_HA_CLOSE) mysql_ha_rm_tables(thd, tables); diff --git a/sql/sql_view.cc b/sql/sql_view.cc index 126db90..1e3c4ce 100644 --- a/sql/sql_view.cc +++ b/sql/sql_view.cc @@ -431,12 +431,6 @@ bool mysql_create_view(THD *thd, TABLE_LIST *views, lex->link_first_table_back(view, link_to_local); view->open_type= OT_BASE_ONLY; - if (check_dependencies_in_with_clauses(lex->with_clauses_list)) - { - res= TRUE; - goto err; - } - WSREP_TO_ISOLATION_BEGIN(WSREP_MYSQL_DB, NULL, NULL); /* @@ -1413,9 +1407,6 @@ bool mysql_make_view(THD *thd, TABLE_SHARE *share, TABLE_LIST *table, TABLE_LIST *tbl; Security_context *security_ctx= 0; - if (check_dependencies_in_with_clauses(thd->lex->with_clauses_list)) - goto err; - /* Check rights to run commands which show underlying tables. In the optimizer trace we would not like to show trace for diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index 9b2355e..dfe239c 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -770,6 +770,7 @@ Virtual_column_info *add_virtual_expression(THD *thd, Item *expr) class sp_head *sphead; class sp_name *spname; class sp_variable *spvar; + class With_element_head *with_element_head; class With_clause *with_clause; class Virtual_column_info *virtual_column; @@ -2188,7 +2189,7 @@ END_OF_INPUT %type <with_clause> with_clause -%type <lex_str_ptr> query_name +%type <with_element_head> with_element_head %type <lex_str_list> opt_with_column_list @@ -3336,7 +3337,11 @@ call: if (unlikely(Lex->call_statement_start(thd, $2))) MYSQL_YYABORT; } - opt_sp_cparam_list {} + opt_sp_cparam_list + { + if (Lex->check_cte_dependencies_and_resolve_references()) + MYSQL_YYABORT; + } ; /* CALL parameters */ @@ -4185,6 +4190,8 @@ sp_proc_stmt_return: LEX *lex= Lex; sp_head *sp= lex->sphead; Lex->pop_select(); //main select + if (Lex->check_cte_dependencies_and_resolve_references()) + MYSQL_YYABORT; if (unlikely(sp->m_handler->add_instr_freturn(thd, sp, lex->spcont, $3, lex)) || unlikely(sp->restore_lex(thd))) @@ -13337,6 +13344,8 @@ do: { Lex->insert_list= $3; Lex->pop_select(); //main select + if (Lex->check_cte_dependencies_and_resolve_references()) + MYSQL_YYABORT; } ; @@ -15507,6 +15516,7 @@ with_clause: if (unlikely(with_clause == NULL)) MYSQL_YYABORT; lex->derived_tables|= DERIVED_WITH; + lex->with_cte_resolution= true; lex->curr_with_clause= with_clause; with_clause->add_to_list(Lex->with_clauses_list_last_next); if (lex->current_select && @@ -15534,7 +15544,7 @@ with_list: with_list_element: - query_name + with_element_head opt_with_column_list { $2= new List<LEX_CSTRING> (Lex->with_column_list); @@ -15554,6 +15564,7 @@ with_list_element: if (elem->set_unparsed_spec(thd, spec_start, $7.pos(), spec_start - query_start)) MYSQL_YYABORT; + elem->set_tables_end_pos(lex->query_tables_last); } ; @@ -15580,12 +15591,15 @@ with_column_list: ; -query_name: +with_element_head: ident { - $$= (LEX_CSTRING *) thd->memdup(&$1, sizeof(LEX_CSTRING)); - if (unlikely($$ == NULL)) + LEX_CSTRING *name= + (LEX_CSTRING *) thd->memdup(&$1, sizeof(LEX_CSTRING)); + $$= new (thd->mem_root) With_element_head(name); + if (unlikely(name == NULL || $$ == NULL)) MYSQL_YYABORT; + $$->tables_pos.set_start_pos(Lex->query_tables_last); } ; diff --git a/sql/sql_yacc_ora.yy b/sql/sql_yacc_ora.yy index cb58c4a..76eb21a 100644 --- a/sql/sql_yacc_ora.yy +++ b/sql/sql_yacc_ora.yy @@ -248,6 +248,7 @@ void ORAerror(THD *thd, const char *s) class sp_head *sphead; class sp_name *spname; class sp_variable *spvar; + class With_element_head *with_element_head; class With_clause *with_clause; class Virtual_column_info *virtual_column; @@ -1689,7 +1690,7 @@ END_OF_INPUT %type <with_clause> with_clause -%type <lex_str_ptr> query_name +%type <with_element_head> with_element_head %type <lex_str_list> opt_with_column_list @@ -3138,7 +3139,11 @@ call: if (unlikely(Lex->call_statement_start(thd, $2))) MYSQL_YYABORT; } - opt_sp_cparam_list {} + opt_sp_cparam_list + { + if (Lex->check_cte_dependencies_and_resolve_references()) + MYSQL_YYABORT; + } ; /* CALL parameters */ @@ -4092,6 +4097,8 @@ sp_proc_stmt_return: LEX *lex= Lex; sp_head *sp= lex->sphead; Lex->pop_select(); //main select + if (Lex->check_cte_dependencies_and_resolve_references()) + MYSQL_YYABORT; if (unlikely(sp->m_handler->add_instr_freturn(thd, sp, lex->spcont, $3, lex)) || unlikely(sp->restore_lex(thd))) @@ -13436,6 +13443,8 @@ do: { Lex->insert_list= $3; Lex->pop_select(); //main select + if (Lex->check_cte_dependencies_and_resolve_references()) + MYSQL_YYABORT; } ; @@ -15624,6 +15633,7 @@ with_clause: if (unlikely(with_clause == NULL)) MYSQL_YYABORT; lex->derived_tables|= DERIVED_WITH; + lex->with_cte_resolution= true; lex->curr_with_clause= with_clause; with_clause->add_to_list(Lex->with_clauses_list_last_next); if (lex->current_select && @@ -15651,7 +15661,7 @@ with_list: with_list_element: - query_name + with_element_head opt_with_column_list { $2= new List<LEX_CSTRING> (Lex->with_column_list); @@ -15671,6 +15681,7 @@ with_list_element: if (elem->set_unparsed_spec(thd, spec_start, $7.pos(), spec_start - query_start)) MYSQL_YYABORT; + elem->set_tables_end_pos(lex->query_tables_last); } ; @@ -15697,12 +15708,15 @@ with_column_list: ; -query_name: +with_element_head: ident { - $$= (LEX_CSTRING *) thd->memdup(&$1, sizeof(LEX_CSTRING)); - if (unlikely($$ == NULL)) + LEX_CSTRING *name= + (LEX_CSTRING *) thd->memdup(&$1, sizeof(LEX_CSTRING)); + $$= new (thd->mem_root) With_element_head(name); + if (unlikely(name == NULL || $$ == NULL)) MYSQL_YYABORT; + $$->tables_pos.set_start_pos(Lex->query_tables_last); } ; diff --git a/sql/table.h b/sql/table.h index 4a739ed..f510aaa 100644 --- a/sql/table.h +++ b/sql/table.h @@ -2139,6 +2139,29 @@ struct vers_select_conds_t struct LEX; class Index_hint; + +/* + @struct TABLE_CHAIN + @brief Subchain of global chain of table references + + The structure contains a pointer to the address of the next_global + pointer to the first TABLE_LIST objectof the subchain and the address + of the next_global pointer to the element right after the last + TABLE_LIST object of the subchain. For an empty subchain both pointers + have the same value. +*/ + +struct TABLE_CHAIN +{ + TABLE_CHAIN() {} + + TABLE_LIST **start_pos; + TABLE_LIST ** end_pos; + + void set_start_pos(TABLE_LIST **pos) { start_pos= pos; } + void set_end_pos(TABLE_LIST **pos) { end_pos= pos; } +}; + struct TABLE_LIST { TABLE_LIST() {} /* Remove gcc warning */ @@ -2473,6 +2496,20 @@ struct TABLE_LIST /* call back function for asking handler about caching in query cache */ qc_engine_callback callback_func; thr_lock_type lock_type; + + /* + Two fields below are set during parsing this table reference in the cases + when the table reference can be potentially a reference to a CTE table. + In this cases the fact that the reference is a reference to a CTE or not + will be ascertained at the very end of parsing of the query when referencies + to CTE are resolved. For references to CTE and to derived tables no mdl + requests are needed while for other table references they are. If a request + is possibly postponed the info that allows to issue this request must be + saved in 'mdl_type' and 'table_options'. + */ + enum_mdl_type mdl_type; + ulong table_options; + uint outer_join; /* Which join type */ uint shared; /* Used in multi-upd */ bool updatable; /* VIEW/TABLE can be updated now */

1 0

[Commits] 8c5f4cde660: MDEV-25630: Crash with window function in left expr of IN subquery
by psergey 22 May '21

22 May '21

revision-id: 8c5f4cde660e2c93f57a6204aa2077768094baab (mariadb-10.2.31-965-g8c5f4cde660) parent(s): 2087d47aaeadc06dd007ce9bd28984ecc8e2101e author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-05-22 15:53:33 +0300 message: MDEV-25630: Crash with window function in left expr of IN subquery * Make Item_in_optimizer::fix_fields inherit the with_window_func attribute of the subquery's left expression (the subquery itself cannot have window functions that are aggregated in this select) * Make Item_cache_wrapper::Item_cache_wrapper() inherit with_window_func attribute of the item it is caching. --- mysql-test/r/win.result | 19 +++++++++++++++++++ mysql-test/t/win.test | 14 ++++++++++++++ sql/item.cc | 1 + sql/item_cmpfunc.cc | 3 +++ 4 files changed, 37 insertions(+) diff --git a/mysql-test/r/win.result b/mysql-test/r/win.result index dd74c5c77fd..8a31dcc0634 100644 --- a/mysql-test/r/win.result +++ b/mysql-test/r/win.result @@ -3892,5 +3892,24 @@ id rn 1 1 drop table t1; # +# MDEV-25630: Crash with window function in left expr of IN subquery +# +CREATE TABLE t1 (i int); +INSERT INTO t1 VALUES (1),(2),(3); +SELECT lag(i) over (ORDER BY i) IN ( SELECT 1 FROM t1 a) FROM t1; +lag(i) over (ORDER BY i) IN ( SELECT 1 FROM t1 a) +NULL +1 +0 +DROP TABLE t1; +CREATE TABLE t1 (i int); +INSERT INTO t1 VALUES (1),(2),(3); +SELECT sum(i) over () IN ( SELECT 1 FROM t1 a) FROM t1; +sum(i) over () IN ( SELECT 1 FROM t1 a) +0 +0 +0 +DROP TABLE t1; +# # End of 10.2 tests # diff --git a/mysql-test/t/win.test b/mysql-test/t/win.test index 57214ab0165..c07a81f17da 100644 --- a/mysql-test/t/win.test +++ b/mysql-test/t/win.test @@ -2542,6 +2542,20 @@ order by rn desc; drop table t1; +--echo # +--echo # MDEV-25630: Crash with window function in left expr of IN subquery +--echo # + +CREATE TABLE t1 (i int); +INSERT INTO t1 VALUES (1),(2),(3); +SELECT lag(i) over (ORDER BY i) IN ( SELECT 1 FROM t1 a) FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (i int); +INSERT INTO t1 VALUES (1),(2),(3); +SELECT sum(i) over () IN ( SELECT 1 FROM t1 a) FROM t1; +DROP TABLE t1; + --echo # --echo # End of 10.2 tests --echo # diff --git a/sql/item.cc b/sql/item.cc index be64edca9a1..d7a3659a2ce 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -8203,6 +8203,7 @@ Item_cache_wrapper::Item_cache_wrapper(THD *thd, Item *item_arg): name= item_arg->name; name_length= item_arg->name_length; with_subselect= orig_item->with_subselect; + with_window_func= orig_item->with_window_func; if ((expr_value= Item_cache::get_cache(thd, orig_item))) expr_value->setup(thd, orig_item); diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index 7b7604053e3..8a2c532f621 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -1416,6 +1416,9 @@ bool Item_in_optimizer::fix_fields(THD *thd, Item **ref) maybe_null=1; with_subselect= 1; with_sum_func= with_sum_func || args[1]->with_sum_func; + with_window_func= args[0]->with_window_func; + // The subquery cannot have window functions aggregated in this select + DBUG_ASSERT(!args[1]->with_window_func); with_field= with_field || args[1]->with_field; with_param= args[0]->with_param || args[1]->with_param; used_tables_and_const_cache_join(args[1]);

1 0