diff options
author | Oleksandr Byelkin <sanja@mariadb.com> | 2023-01-27 13:54:14 +0100 |
---|---|---|
committer | Oleksandr Byelkin <sanja@mariadb.com> | 2023-01-27 13:54:14 +0100 |
commit | 7fa02f5c0ba4c3060cbff8cf9d1e472d268f3dad (patch) | |
tree | b16c43d92abcb99316b2de31770c567e1c70bfa6 /mysql-test/main/cte_recursive.test | |
parent | 672cdcbb93a7355c715f3e232d4c5852209f30b5 (diff) | |
parent | c8f9bb2718c4ed7b464504c54df961bfeb2cccca (diff) | |
download | mariadb-git-7fa02f5c0ba4c3060cbff8cf9d1e472d268f3dad.tar.gz |
Merge branch '10.4' into 10.5
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 8efbdbf9677..ebea3b96754 100644 --- a/mysql-test/main/cte_recursive.test +++ b/mysql-test/main/cte_recursive.test @@ -3875,6 +3875,129 @@ 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 # |