Fixed bugs: mdev-13780 CTE not found, mdev-14184 recursive CTE not found

The support of embedded CTEs was not correct in the cases when
embedded CTEs were used multiple times. The problems occurred with
both non-recursive (bug mdev-13780) and recursive (bug mdev-14184)
embedded CTEs.

1 files changed, 28 insertions, 0 deletions

diff --git a/mysql-test/t/cte_nonrecursive.test b/mysql-test/t/cte_nonrecursive.test
index 57b7ae1658f..c9639f50a19 100644
--- a/mysql-test/t/cte_nonrecursive.test
+++ b/mysql-test/t/cte_nonrecursive.test
@@ -743,3 +743,31 @@ eval $q;
 eval explain $q;
 
 DROP TABLE t1,t2;
+
+--echo #
+--echo # MDEV-13780: tower of embedding CTEs with multiple usage of them
+--echo #
+
+create table t1 (a int);
+insert into t1 values (3), (2), (4), (7), (1), (2), (5);
+
+let $q=
+with cte_e as
+(
+  with cte_o as
+  (
+    with cte_i as (select * from t1 where a < 7)
+    select * from cte_i where a > 1
+  )
+  select * from cte_o as cto_o1 where a < 3
+  union
+  select * from cte_o as cto_o2 where a > 4
+)
+select * from cte_e as cte_e1 where a > 1
+union
+select * from cte_e as cte_e2;
+
+eval $q;
+eval explain extended $q;
+
+drop table t1;