diff options
Diffstat (limited to 'mysql-test/main/cte_recursive.test')
-rw-r--r-- | mysql-test/main/cte_recursive.test | 123 |
1 files changed, 123 insertions, 0 deletions
diff --git a/mysql-test/main/cte_recursive.test b/mysql-test/main/cte_recursive.test index 270a5023d31..f4dafbf9294 100644 --- a/mysql-test/main/cte_recursive.test +++ b/mysql-test/main/cte_recursive.test @@ -3872,5 +3872,128 @@ select * from cte; drop table t1,t2,t3,x; --echo # +--echo # MDEV-30248: Embedded non-recursive CTE referring to base table 'x' +--echo # within a CTE with name 'x' used in a subquery from +--echo # select list of another CTE +--echo # + +CREATE TABLE x (a int) ENGINE=MyISAM; +INSERT INTO x VALUES (3),(7),(1); +CREATE TABLE t1 (b int) ENGINE=MYISAM; +INSERT INTO t1 VALUES (1); + +let $q1= +WITH cte AS +( + SELECT + ( + WITH x AS + (WITH x AS (SELECT a FROM x AS t) SELECT 1 AS b) + SELECT b FROM x AS r + ) AS c +) +SELECT cte.c FROM cte; +eval $q1; + +let $q2= +WITH cte AS +( + SELECT + ( + WITH x AS + (WITH x AS (SELECT a FROM x AS t) SELECT b FROM t1) + SELECT b FROM x AS r + ) AS c +) +SELECT cte.c FROM cte; +eval $q2; + +let $q3= +WITH cte AS +( + SELECT + ( + WITH x AS + (WITH y AS (SELECT a FROM x AS t) SELECT b FROM t1) + SELECT b FROM x AS r + ) AS c +) +SELECT cte.c FROM cte; +eval $q3; + + +let $q4= +WITH cte AS +( + SELECT + ( + WITH x AS + (WITH y(b) AS (SELECT a FROM x AS t LIMIT 1) SELECT b FROM y) + SELECT b FROM x AS r + ) AS c +) +SELECT cte.c FROM cte; +eval $q4; + +let $q5= +WITH cte AS +( + SELECT + ( + WITH x AS + (WITH x(b) AS (SELECT a FROM x AS t LIMIT 1) SELECT b FROM x) + SELECT b FROM x AS r + ) AS c +) +SELECT cte.c FROM cte; +eval $q5; + +let $q6= +WITH x AS +( + SELECT + ( + WITH x AS + (WITH x AS (SELECT a FROM x AS t) SELECT 1 AS b) + SELECT b FROM x AS r + ) AS c +) +SELECT x.c from x; +eval $q6; + +let $q7= +WITH cte AS +( + SELECT + ( + WITH x AS + (WITH x AS (SELECT a FROM x AS t) SELECT 2 AS b) + SELECT r1.b FROM x AS r1, x AS r2 WHERE r1.b=r2.b + ) AS c +) +SELECT cte.c from cte; +eval $q7; + + +DROP TABLE x; + +--ERROR ER_NO_SUCH_TABLE +eval $q1; +--ERROR ER_NO_SUCH_TABLE +eval $q2; +--ERROR ER_NO_SUCH_TABLE +eval $q3; +--ERROR ER_NO_SUCH_TABLE +eval $q4; +--ERROR ER_NO_SUCH_TABLE +eval $q5; +--ERROR ER_NO_SUCH_TABLE +eval $q6; +--ERROR ER_NO_SUCH_TABLE +eval $q7; + +DROP TABLE t1; + +--echo # --echo # End of 10.3 tests --echo # |