diff options
Diffstat (limited to 'mysql-test/r/cte_nonrecursive.result')
| -rw-r--r-- | mysql-test/r/cte_nonrecursive.result | 30 |
1 files changed, 30 insertions, 0 deletions
diff --git a/mysql-test/r/cte_nonrecursive.result b/mysql-test/r/cte_nonrecursive.result index a473f96bb57..d944843f2d0 100644 --- a/mysql-test/r/cte_nonrecursive.result +++ b/mysql-test/r/cte_nonrecursive.result @@ -1265,3 +1265,33 @@ a 4 deallocate prepare stmt; drop table t1; +# +# MDEV-14852: CTE using temporary table in query +# with two references to the CTE +# +create temporary table t1 (i int); +insert into t1 values (5),(4),(1),(2),(3); +with +c1 as (select i from t1), +c2 as (select i from c1 where c1.i=2) +select i from c1 where i > 3 union select i from c2; +i +5 +4 +2 +drop table t1; +create table t1 (term char(10)); +create temporary table t2 (term char(10)); +insert into t1 values ('TERM01'),('TERM02'),('TERM03'); +insert into t2 values ('TERM02'),('TERM03'),('TERM04'); +with c1 as (select * from t1), c2 as (select * from t2) +(select * from c1 left outer join c2 on c1.term = c2.term) +union all +(select * from c1 right outer join c2 on c1.term = c2.term +where c1.term is null); +term term +TERM02 TERM02 +TERM03 TERM03 +TERM01 NULL +NULL TERM04 +drop table t1,t2; |