revision-id: b09d941daed35136c95c590f8ac37257179e5e99 (mariadb-10.2.31-1237-gb09d941) parent(s): 42ae765960869a7ce381341d7b98c1e8aa157b29 author: Igor Babaev committer: Igor Babaev timestamp: 2021-11-15 22:21:05 -0800 message: MDEV-26825 Bogus error for query with two usage of CTE referring another CTE This bug affected queries with two or more references to a CTE referring another CTE if the definition of the latter contained an invocation of a stored function that used a base table. The bug could lead to a bogus error message or to an assertion failure. For any non-first reference to CTE cte1 With_element::clone_parsed_spec() is called that parses the specification of cte1 to construct the unit structure for this usage of cte1. If cte1 refers to another CTE cte2 outside of the specification of cte1 then With_element::clone_parsed_spec() has to be called for cte2 as well. This call is made by the function LEX::resolve_references_to_cte() within the invocation of the function With_element::clone_parsed_spec() for cte1. When the specification of a CTE is parsed all table references encountered in it must be added to the global list of table references for the query. As the specification for the non-first usage of a CTE is parsed at a recursive call of the parser the function With_element::clone_parsed_spec() invoked at this recursive call should takes care of appending the list of table references encountered in the specification of this CTE cte1 to the list of table references created for the query. And it should do it after the call of LEX::resolve_references_to_cte() that resolves references to CTEs defined outside of the specification of cte1 because this call may invoke the parser again for specifications of other CTEs and the table references from their specifications must ultimately appear in the global list of table references of the query. The code of With_element::clone_parsed_spec() misplaced the call of LEX::resolve_references_to_cte(). As a result LEX::query_tables_last used for the query that was supposed to point to the field 'next_global' of the last element in the global list of table references actually pointed to 'next_global' of the previous element. The above inconsistency certainly caused serious problems when table references used in the stored functions invoked in cloned specifications of CTEs were added to the global list of table references. --- mysql-test/r/cte_nonrecursive.result | 50 +++++++++++++++++++++++++++++++ mysql-test/t/cte_nonrecursive.test | 36 +++++++++++++++++++++++ sql/sql_cte.cc | 57 +++++++++++++++++++++--------------- 3 files changed, 120 insertions(+), 23 deletions(-) diff --git a/mysql-test/r/cte_nonrecursive.result b/mysql-test/r/cte_nonrecursive.result index 2504e55..db747a7 100644 --- a/mysql-test/r/cte_nonrecursive.result +++ b/mysql-test/r/cte_nonrecursive.result @@ -2086,4 +2086,54 @@ a b a b 1 3 1 3 drop procedure sp; drop table t1; +# +# MDEV-26825: query with two usage of CTE that refers to another CTE +# with stored function using a base table. +# +create table t1 (id int primary key); +insert into t1 values +(1), (2), (3), (4), (5), (6), (7), (8), (9), (10); +create function f(in_id int) returns integer +return (select id from t1 where t1.id = in_id); +with c1 as (select id from t1 where f(id)=id group by id), +c2 as (select id from c1 as pt group by id) +select id from c2 as s1 union select id from c2 as s2; +id +1 +2 +3 +4 +5 +6 +7 +8 +9 +10 +with c1 as (select id from t1 as r where f(id)=id group by id), +c2 as (select id from c1 as pt group by id) +select id from c2 as s1 union select id from c2 as s2; +id +1 +2 +3 +4 +5 +6 +7 +8 +9 +10 +create function g() returns int return (select count(*) from t1); +create procedure sp1() +with c1 as (select id from t1 a where g() > 10), +c2 as (select id from c1) +select id from c2 as s1 union select id from c2 as s2; +call sp1(); +id +call sp1(); +id +drop procedure sp1; +drop function g; +drop function f; +drop table t1; # End of 10.2 tests diff --git a/mysql-test/t/cte_nonrecursive.test b/mysql-test/t/cte_nonrecursive.test index c20a0dc..2b90bab 100644 --- a/mysql-test/t/cte_nonrecursive.test +++ b/mysql-test/t/cte_nonrecursive.test @@ -1542,4 +1542,40 @@ call sp(); drop procedure sp; drop table t1; +--echo # +--echo # MDEV-26825: query with two usage of CTE that refers to another CTE +--echo # with stored function using a base table. +--echo # + +create table t1 (id int primary key); +insert into t1 values +(1), (2), (3), (4), (5), (6), (7), (8), (9), (10); + +create function f(in_id int) returns integer +return (select id from t1 where t1.id = in_id); + +with c1 as (select id from t1 where f(id)=id group by id), + c2 as (select id from c1 as pt group by id) +select id from c2 as s1 union select id from c2 as s2; + +with c1 as (select id from t1 as r where f(id)=id group by id), + c2 as (select id from c1 as pt group by id) +select id from c2 as s1 union select id from c2 as s2; + +create function g() returns int return (select count(*) from t1); +create procedure sp1() + +with c1 as (select id from t1 a where g() > 10), + c2 as (select id from c1) +select id from c2 as s1 union select id from c2 as s2; + +call sp1(); +call sp1(); + +drop procedure sp1; +drop function g; + +drop function f; +drop table t1; + --echo # End of 10.2 tests diff --git a/sql/sql_cte.cc b/sql/sql_cte.cc index 22a9984..e6b868d 100644 --- a/sql/sql_cte.cc +++ b/sql/sql_cte.cc @@ -1021,6 +1021,15 @@ st_select_lex_unit *With_element::clone_parsed_spec(LEX *old_lex, lex_start(thd); lex->clone_spec_offset= unparsed_spec_offset; lex->with_cte_resolution= true; + /* + There's no need to add SPs/SFs referenced in the clone to the global + list of the SPs/SFs used in the query as they were added when the first + reference to the cloned CTE was parsed. Yet the recursive call of the + parser must to know that they were already included into the list. + */ + lex->sroutines= old_lex->sroutines; + lex->sroutines_list_own_last= old_lex->sroutines_list_own_last; + lex->sroutines_list_own_elements= old_lex->sroutines_list_own_elements; /* The specification of a CTE is to be parsed as a regular query. @@ -1067,6 +1076,29 @@ st_select_lex_unit *With_element::clone_parsed_spec(LEX *old_lex, goto err; /* + 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.cloned_from= spec; + + /* + 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. + */ + lex->only_cte_resolution= old_lex->only_cte_resolution; + if (lex->resolve_references_to_cte(lex->query_tables, + lex->query_tables_last)) + { + res= NULL; + goto err; + } + + /* 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 @@ -1090,32 +1122,11 @@ st_select_lex_unit *With_element::clone_parsed_spec(LEX *old_lex, old_lex->query_tables_last= lex->query_tables_last; } } + old_lex->sroutines_list_own_last= lex->sroutines_list_own_last; + old_lex->sroutines_list_own_elements= lex->sroutines_list_own_elements; 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.cloned_from= spec; - - /* - 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. - */ - lex->only_cte_resolution= old_lex->only_cte_resolution; - if (lex->resolve_references_to_cte(lex->query_tables, - lex->query_tables_last)) - { - res= NULL; - goto err; - } - last_clone_select= lex->all_selects_list; while (last_clone_select->next_select_in_list()) last_clone_select= last_clone_select->next_select_in_list();