revision-id: 2f2bf3df7c4318a1e94a331b304c3c18fcafd7bb (mariadb-10.2.31-1040-g2f2bf3d) parent(s): 99f700a820ef90b5b36ef765fb1532145ab3e907 author: Igor Babaev committer: Igor Babaev timestamp: 2021-07-06 14:38:32 -0700 message: MDEV-26095 Infinite recursion when processing embedded recursive CTE with missing RECURSIVE If a table reference r used inthe specification of a CTE whose definition is contained in the WITH clause where RECURSIVE is omitted then this table reference cannot be considered as a recursive table reference even if it is used in the query that specifies CTE whose name is r. It can be considered only as a reference to an embedding CTE or to a temporary table or to a base table/view. If there is no such object with name r then an error message must be reported. This patch fixes the code that actually in some cases resolved r as a reference to the CTE whose specification contained r if its name was r in spite of the fact that r was not considered as a recursive CTE. This happened in the cases when the definition of r was used in the specification of another CTE. Such wrong name resolution for r led to an infinite recursive invocations of the parser that ultimately crashed the server. This bug is a result of the fix for mdev-13780 that was not quite correct. Approved by Oleksandr Byelkin <sanja@mariadb.com> --- mysql-test/r/cte_nonrecursive.result | 25 +++++++++++++++++++++++++ mysql-test/t/cte_nonrecursive.test | 23 +++++++++++++++++++++++ sql/sql_cte.cc | 4 +++- 3 files changed, 51 insertions(+), 1 deletion(-) diff --git a/mysql-test/r/cte_nonrecursive.result b/mysql-test/r/cte_nonrecursive.result index c1d7fd0..5cc5a25 100644 --- a/mysql-test/r/cte_nonrecursive.result +++ b/mysql-test/r/cte_nonrecursive.result @@ -2019,4 +2019,29 @@ drop procedure sp1; drop procedure sp2; drop procedure sp3; drop table t1; +# +# MDEV-26095: missing RECURSIVE for the recursive definition of CTE +# embedded into another CTE definition +# +create table t1 (a int); +insert into t1 values (5), (7); +with cte_e as ( +with recursive cte_r as ( +select a from t1 union select a+1 as a from cte_r r where a < 10 +) select * from cte_r +) select * from cte_e; +a +5 +7 +6 +8 +9 +10 +with cte_e as ( +with cte_r as ( +select a from t1 union select a+1 as a from cte_r r where a < 10 +) select * from cte_r +) select * from cte_e; +ERROR 42S02: Table 'test.cte_r' doesn't exist +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 cbe4f8b..68dbc0c 100644 --- a/mysql-test/t/cte_nonrecursive.test +++ b/mysql-test/t/cte_nonrecursive.test @@ -1492,4 +1492,27 @@ drop procedure sp3; drop table t1; +--echo # +--echo # MDEV-26095: missing RECURSIVE for the recursive definition of CTE +--echo # embedded into another CTE definition +--echo # + +create table t1 (a int); +insert into t1 values (5), (7); + +with cte_e as ( + with recursive cte_r as ( + select a from t1 union select a+1 as a from cte_r r where a < 10 + ) select * from cte_r +) select * from cte_e; + +--ERROR ER_NO_SUCH_TABLE +with cte_e as ( + with cte_r as ( + select a from t1 union select a+1 as a from cte_r r where a < 10 + ) select * from cte_r +) select * from cte_e; + +drop table t1; + --echo # End of 10.2 tests diff --git a/sql/sql_cte.cc b/sql/sql_cte.cc index 9dad33d..702db8f 100644 --- a/sql/sql_cte.cc +++ b/sql/sql_cte.cc @@ -1256,6 +1256,7 @@ bool With_element::is_anchor(st_select_lex *sel) With_element *st_select_lex::find_table_def_in_with_clauses(TABLE_LIST *table) { With_element *found= NULL; + With_clause *containing_with_clause= NULL; st_select_lex_unit *master_unit; st_select_lex *outer_sl; for (st_select_lex *sl= this; sl; sl= outer_sl) @@ -1268,6 +1269,7 @@ With_element *st_select_lex::find_table_def_in_with_clauses(TABLE_LIST *table) */ With_clause *attached_with_clause= sl->get_with_clause(); if (attached_with_clause && + attached_with_clause != containing_with_clause && (found= attached_with_clause->find_table_def(table, NULL))) break; master_unit= sl->master_unit(); @@ -1275,7 +1277,7 @@ With_element *st_select_lex::find_table_def_in_with_clauses(TABLE_LIST *table) With_element *with_elem= sl->get_with_element(); if (with_elem) { - With_clause *containing_with_clause= with_elem->get_owner(); + containing_with_clause= with_elem->get_owner(); With_element *barrier= containing_with_clause->with_recursive ? NULL : with_elem; if ((found= containing_with_clause->find_table_def(table, barrier)))