diff options
author | Marko Mäkelä <marko.makela@mariadb.com> | 2021-11-29 11:16:20 +0200 |
---|---|---|
committer | Marko Mäkelä <marko.makela@mariadb.com> | 2021-11-29 11:16:20 +0200 |
commit | d4cb1776030b643895da0532b75c051f55e84356 (patch) | |
tree | 67ca086a5e0e0f0b959c8dd2080009bd51dc6a9b /mysql-test/main/cte_nonrecursive.test | |
parent | ca2695392411adc7f1d19f85d464213128e4f285 (diff) | |
parent | 4da22738765ecc31fe9e253d349ca6e9d0f5d985 (diff) | |
download | mariadb-git-d4cb1776030b643895da0532b75c051f55e84356.tar.gz |
Merge 10.4 into 10.5
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 # |