Fixed mdev-15119 CTE, referencing another CTE, that is declared after,

                 does not return error

Corrected the code of st_select_lex::find_table_def_in_with_clauses() for
a proper identification of CTE references used in embedded CTEs.

2 files changed, 39 insertions, 0 deletions

diff --git a/mysql-test/r/cte_nonrecursive.result b/mysql-test/r/cte_nonrecursive.result
index dc48c1b58dd..81e77ca60bd 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;