diff options
| -rw-r--r-- | mysql-test/r/cte_nonrecursive.result | 19 | ||||
| -rw-r--r-- | mysql-test/t/cte_nonrecursive.test | 20 | ||||
| -rw-r--r-- | sql/sql_cte.cc | 26 |
3 files changed, 54 insertions, 11 deletions
diff --git a/mysql-test/r/cte_nonrecursive.result b/mysql-test/r/cte_nonrecursive.result index 7bd17d272bd..738135215c2 100644 --- a/mysql-test/r/cte_nonrecursive.result +++ b/mysql-test/r/cte_nonrecursive.result @@ -1379,3 +1379,22 @@ USE db1; WITH cte AS (SELECT 1 AS a) SELECT db1.cte.a FROM db1.cte; ERROR 42S02: Table 'db1.cte' doesn't exist DROP DATABASE db1; +USE test; +# +# MDEV-15119: CTE c2 specified after CTE c1 and is used in +# CTE c3 that is embedded into the spec of c1 +# +CREATE TABLE t1 (i int); +INSERT INTO t1 VALUES (1),(2),(3); +WITH c1 AS (WITH c3 AS (SELECT * FROM c2) SELECT * FROM c3), +c2 AS (SELECT * FROM t1) +SELECT * FROM c1; +ERROR 42S02: Table 'test.c2' doesn't exist +WITH RECURSIVE c1 AS (WITH c3 AS (SELECT * FROM c2) SELECT * FROM c3), +c2 AS (SELECT * FROM t1) +SELECT * FROM c1; +i +1 +2 +3 +DROP TABLE t1; diff --git a/mysql-test/t/cte_nonrecursive.test b/mysql-test/t/cte_nonrecursive.test index 1af91dd38c0..05de03fc7af 100644 --- a/mysql-test/t/cte_nonrecursive.test +++ b/mysql-test/t/cte_nonrecursive.test @@ -944,3 +944,23 @@ USE db1; WITH cte AS (SELECT 1 AS a) SELECT db1.cte.a FROM db1.cte; DROP DATABASE db1; +USE test; + +--echo # +--echo # MDEV-15119: CTE c2 specified after CTE c1 and is used in +--echo # CTE c3 that is embedded into the spec of c1 +--echo # + +CREATE TABLE t1 (i int); +INSERT INTO t1 VALUES (1),(2),(3); + +--error ER_NO_SUCH_TABLE +WITH c1 AS (WITH c3 AS (SELECT * FROM c2) SELECT * FROM c3), + c2 AS (SELECT * FROM t1) +SELECT * FROM c1; + +WITH RECURSIVE c1 AS (WITH c3 AS (SELECT * FROM c2) SELECT * FROM c3), + c2 AS (SELECT * FROM t1) +SELECT * FROM c1; + +DROP TABLE t1; diff --git a/sql/sql_cte.cc b/sql/sql_cte.cc index a67337433fa..a53e6b07813 100644 --- a/sql/sql_cte.cc +++ b/sql/sql_cte.cc @@ -1006,23 +1006,20 @@ bool With_element::is_anchor(st_select_lex *sel) With_element *st_select_lex::find_table_def_in_with_clauses(TABLE_LIST *table) { - st_select_lex_unit *master_unit= NULL; With_element *found= NULL; - for (st_select_lex *sl= this; - sl; - sl= master_unit->outer_select()) + st_select_lex_unit *master_unit; + st_select_lex *outer_sl; + for (st_select_lex *sl= this; sl; sl= outer_sl) { - With_element *with_elem= sl->get_with_element(); /* If sl->master_unit() is the spec of a with element then the search for a definition was already done by With_element::check_dependencies_in_spec and it was unsuccesful. Yet for units cloned from the spec it has not been done yet. */ - With_clause *attached_with_clause=sl->get_with_clause(); - if (attached_with_clause && - (found= attached_with_clause->find_table_def(table, NULL))) - break; + master_unit= sl->master_unit(); + outer_sl= master_unit->outer_select(); + With_element *with_elem= sl->get_with_element(); if (with_elem) { With_clause *containing_with_clause= with_elem->get_owner(); @@ -1030,9 +1027,16 @@ With_element *st_select_lex::find_table_def_in_with_clauses(TABLE_LIST *table) NULL : with_elem; if ((found= containing_with_clause->find_table_def(table, barrier))) break; - sl= sl->master_unit()->outer_select(); + if (outer_sl && !outer_sl->get_with_element()) + break; + } + else + { + With_clause *attached_with_clause= sl->get_with_clause(); + if (attached_with_clause && + (found= attached_with_clause->find_table_def(table, NULL))) + break; } - master_unit= sl->master_unit(); /* Do not look for the table's definition beyond the scope of the view */ if (master_unit->is_view) break; |