diff options
Diffstat (limited to 'mysql-test/main/cte_nonrecursive.test')
-rw-r--r-- | mysql-test/main/cte_nonrecursive.test | 51 |
1 files changed, 49 insertions, 2 deletions
diff --git a/mysql-test/main/cte_nonrecursive.test b/mysql-test/main/cte_nonrecursive.test index 78a901ba0f2..0e58c4aa3e1 100644 --- a/mysql-test/main/cte_nonrecursive.test +++ b/mysql-test/main/cte_nonrecursive.test @@ -1217,7 +1217,9 @@ DROP TABLE test.t; --echo # MDEV-22781: create view with CTE without default database --echo # -drop database test; +create database db; +use db; +drop database db; create database db1; create table db1.t1 (a int); insert into db1.t1 values (3),(7),(1); @@ -1239,7 +1241,6 @@ drop view db1.v1; drop table db1.t1; drop database db1; -create database test; use test; --echo # @@ -1590,6 +1591,52 @@ drop function g; drop function f; drop table t1; +--echo # +--echo # MDEV-27086: union using CTEs in CREATE TABLE +--echo # + +create or replace temporary table tmp as +with cte1 as (select 1 as a), cte2 as (select 2 as a) +select * from cte1 union select * from cte2; +select * from tmp; + +create table t1 as +with cte1 as (select 1 as a), cte2 as (select 2 as a) +select * from cte1 union select * from cte2; +select * from t1; + +insert into t1 values (3); + +create table t2 as +with cte1 as (select * from t1 where a <2), cte2 as (select * from t1 where a > 2) +select * from cte1 union select * from cte2; +select * from t2; + +drop table t1,t2; + +--echo # +--echo # MDEV-26470: CTE in WITH clause of subquery used in DELETE +--echo # + +create table t1 (a int); +insert into t1 values (3), (7), (1), (5); + +create table t2 (b int); +insert into t2 values (4), (1), (3), (2); + +delete from t1 + where a in (with cte(a) as (select * from t2 where b <=2) select a from cte); +select * from t1; + +insert into t1 values (1), (3); + +delete t1 from t1, t2 + where t1.a=t2.b or + t1.a in (with cte(a) as (select b+1 from t2) select * from cte); +select * from t1; + +drop table t1,t2; + --echo # End of 10.2 tests --echo # |