cte_nonrecursive.result

create table t1 (a int, b  varchar(32));
insert into t1 values
(4,'aaaa' ), (7,'bb'), (1,'ccc'), (4,'dd');
insert into t1 values
(3,'eee'), (7,'bb'), (1,'fff'), (4,'ggg');
create table t2 (c int);
insert into t2 values
(2), (4), (5), (3);
# select certain field in the specification of t
with t as (select a from t1 where b >= 'c') 
select * from t2,t where t2.c=t.a;
c	a
4	4
3	3
4	4
select * from t2, (select a from t1 where b >= 'c') as t
where t2.c=t.a;
c	a
4	4
3	3
4	4
explain 
with t as (select a from t1 where b >= 'c') 
select * from t2,t where t2.c=t.a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	4	
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	8	Using where; Using join buffer (flat, BNL join)
explain
select * from t2, (select a from t1 where b >= 'c') as t
where t2.c=t.a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	4	
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	8	Using where; Using join buffer (flat, BNL join)
# select '*' in the specification of t
with t as (select * from t1 where b >= 'c') 
select * from t2,t where t2.c=t.a;
c	a	b
4	4	dd
3	3	eee
4	4	ggg
select * from t2, (select * from t1 where b >= 'c') as t
where t2.c=t.a;
c	a	b
4	4	dd
3	3	eee
4	4	ggg
explain
with t as (select * from t1 where b >= 'c') 
select * from t2,t where t2.c=t.a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	4	
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	8	Using where; Using join buffer (flat, BNL join)
explain
select * from t2, (select * from t1 where b >= 'c') as t
where t2.c=t.a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	4	
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	8	Using where; Using join buffer (flat, BNL join)
# rename fields returned by the specication when defining t
with t(f1,f2) as (select * from t1 where b >= 'c') 
select * from t2,t where t2.c=t.f1;
c	f1	f2
4	4	dd
3	3	eee
4	4	ggg
explain
with t(f1,f2) as (select * from t1 where b >= 'c') 
select * from t2,t where t2.c=t.f1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	4	
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	8	Using where; Using join buffer (flat, BNL join)
# materialized query specifying t
with t as (select a, count(*) from t1 where b >= 'c' group by a) 
select * from t2,t where t2.c=t.a;
c	a	count(*)
4	4	2
3	3	1
select * from t2, (select a, count(*) from t1 where b >= 'c' group by a) as t 
where t2.c=t.a;
c	a	count(*)
4	4	2
3	3	1
explain
with t as (select a, count(*) from t1 where b >= 'c' group by a) 
select * from t2,t where t2.c=t.a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	4	Using where
1	PRIMARY	<derived2>	ref	key0	key0	5	test.t2.c	2	
2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	8	Using where; Using temporary; Using filesort
explain
select * from t2, (select a, count(*) from t1 where b >= 'c' group by a) as t 
where t2.c=t.a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	4	Using where
1	PRIMARY	<derived2>	ref	key0	key0	5	test.t2.c	2	
2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	8	Using where; Using temporary; Using filesort
# specivication of t contains having
with t as (select a, count(*) from t1 where b >= 'c'
             group by a having count(*)=1 ) 
select * from t2,t where t2.c=t.a;
c	a	count(*)
3	3	1
select * from t2, (select a, count(*) from t1 where b >= 'c'
                      group by a having count(*)=1) t
where t2.c=t.a;
c	a	count(*)
3	3	1
# main query contains having
with t as (select * from t2 where c <= 4) 
select a, count(*) from t1,t where t1.a=t.c group by a having count(*)=1;
a	count(*)
3	1
select a, count(*) from t1, (select * from t2 where c <= 4) t 
where t1.a=t.c group by a having count(*)=1;
a	count(*)
3	1
# main query contains group by + order by
with t as (select * from t2 where c <= 4 ) 
select a, count(*) from t1,t where t1.a=t.c group by a order by count(*);
a	count(*)
3	1
4	3
select a, count(*) from t1, (select * from t2 where c <= 4 ) t
where t1.a=t.c group by a order by count(*);
a	count(*)
3	1
4	3
# main query contains group by + order by + limit
with t as (select * from t2 where c <= 4 ) 
select a, count(*) from t1,t
where t1.a=t.c group by a order by count(*) desc limit 1;
a	count(*)
4	3
select a, count(*) from t1, (select * from t2 where c <= 4 ) t
where t1.a=t.c group by a order by count(*) desc limit 1;
a	count(*)
4	3
# t is used in a subquery
with t as (select a from t1 where a<5)
select * from t2 where c in (select a from t);
c
4
3
select * from t2 
where c in (select a from (select a from t1 where a<5) as t);
c
4
3
explain
with t as (select a from t1 where a<5)
select * from t2 where c in (select a from t);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	4	
1	PRIMARY	<subquery3>	eq_ref	distinct_key	distinct_key	4	func	1	
3	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
explain
select * from t2 
where c in (select a from (select a from t1 where a<5) as t);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	4	
1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
2	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
# materialized t is used in a subquery
with t as (select count(*) as c from t1 where b >= 'c' group by a)
select * from t2 where c in (select c from t);
c
2
select * from t2
where c in (select c from (select count(*) as c from t1
where b >= 'c' group by a) as t);
c
2
explain
with t as (select count(*) as c from t1 where b >= 'c' group by a)
select * from t2 where c in (select c from t);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	4	Using where
1	PRIMARY	<derived2>	ref	key0	key0	8	test.t2.c	2	Using where; FirstMatch(t2)
2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	8	Using where; Using temporary; Using filesort
explain
select * from t2
where c in (select c from (select count(*) as c from t1
where b >= 'c' group by a) as t);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	4	Using where
1	PRIMARY	<derived3>	ref	key0	key0	8	test.t2.c	2	Using where; FirstMatch(t2)
3	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	8	Using where; Using temporary; Using filesort
# two references to t specified by a query
# selecting a field:  both in main query
with t as (select a from t1 where b >= 'c')
select * from t as r1, t as r2 where r1.a=r2.a;
a	a
1	1
1	1
4	4
4	4
3	3
1	1
1	1
4	4
4	4
select * from (select a from t1 where b >= 'c') as r1,
(select a from t1 where b >= 'c') as r2 
where r1.a=r2.a;
a	a
1	1
1	1
4	4
4	4
3	3
1	1
1	1
4	4
4	4
explain
with t as (select a from t1 where b >= 'c')
select * from t as r1, t as r2 where r1.a=r2.a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	8	Using where; Using join buffer (flat, BNL join)
explain
select * from (select a from t1 where b >= 'c') as r1,
(select a from t1 where b >= 'c') as r2 
where r1.a=r2.a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	8	Using where; Using join buffer (flat, BNL join)
# two references to materialized t: both in main query
with t as (select distinct a from t1 where b >= 'c')
select * from t as r1, t as r2 where r1.a=r2.a;
a	a
1	1
4	4
3	3
select * from (select distinct a from t1 where b >= 'c') as r1,
(select distinct a from t1 where b >= 'c') as r2 
where r1.a=r2.a;
a	a
1	1
4	4
3	3
explain
with t as (select distinct a from t1 where b >= 'c')
select * from t as r1, t as r2 where r1.a=r2.a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	8	Using where
1	PRIMARY	<derived3>	ref	key0	key0	5	r1.a	2	
3	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	8	Using where; Using temporary
2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	8	Using where; Using temporary
explain
select * from (select distinct a from t1 where b >= 'c') as r1,
(select distinct a from t1 where b >= 'c') as r2 
where r1.a=r2.a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	8	Using where
1	PRIMARY	<derived3>	ref	key0	key0	5	r1.a	2	
3	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	8	Using where; Using temporary
2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	8	Using where; Using temporary
# two references to t specified by a query
# selecting all fields:  both in main query
with t as (select * from t1 where b >= 'c')
select * from t as r1, t as r2 where r1.a=r2.a;
a	b	a	b
1	ccc	1	ccc
1	fff	1	ccc
4	dd	4	dd
4	ggg	4	dd
3	eee	3	eee
1	ccc	1	fff
1	fff	1	fff
4	dd	4	ggg
4	ggg	4	ggg
select * from (select * from t1 where b >= 'c') as r1,
(select * from t1 where b >= 'c') as r2
where r1.a=r2.a;
a	b	a	b
1	ccc	1	ccc
1	fff	1	ccc
4	dd	4	dd
4	ggg	4	dd
3	eee	3	eee
1	ccc	1	fff
1	fff	1	fff
4	dd	4	ggg
4	ggg	4	ggg
explain
with t as (select * from t1 where b >= 'c')
select * from t as r1, t as r2 where r1.a=r2.a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	8	Using where; Using join buffer (flat, BNL join)
explain
select * from (select * from t1 where b >= 'c') as r1,
(select * from t1 where b >= 'c') as r2
where r1.a=r2.a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	8	Using where; Using join buffer (flat, BNL join)
# two references to t specifying explicitly column names
with t(c) as (select a from t1 where b >= 'c')
select * from t r1, t r2 where r1.c=r2.c;
c	c
1	1
1	1
4	4
4	4
3	3
1	1
1	1
4	4
4	4
# t two references of t used in different parts of a union
with t as (select a from t1 where b >= 'c')
select * from t where a < 2
union
select * from t where a >= 4;
a
1
4
select * from (select a from t1 where b >= 'c') as t
where t.a < 2
union
select * from (select a from t1 where b >= 'c') as t
where t.a >= 4;
a
1
4
explain
with t as (select a from t1 where b >= 'c')
select * from t where a < 2
union
select * from t where a >= 4;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
3	UNION	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
NULL	UNION RESULT	<union1,3>	ALL	NULL	NULL	NULL	NULL	NULL	
explain
select * from (select a from t1 where b >= 'c') as t
where t.a < 2
union
select * from (select a from t1 where b >= 'c') as t
where t.a >= 4;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
3	UNION	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
NULL	UNION RESULT	<union1,3>	ALL	NULL	NULL	NULL	NULL	NULL	
# specification of t contains union
with t as (select a from t1 where b >= 'f' 
union
select c as a from t2 where c < 4) 
select * from t2,t where t2.c=t.a;
c	a
2	2
4	4
3	3
select * from t2, 
(select a from t1 where b >= 'f' 
union
select c as a from t2 where c < 4) as t 
where t2.c=t.a;
c	a
2	2
4	4
3	3
explain
with t as (select a from t1 where b >= 'f' 
union
select c as a from t2 where c < 4) 
select * from t2,t where t2.c=t.a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	4	Using where
1	PRIMARY	<derived2>	ref	key0	key0	5	test.t2.c	2	
2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
3	UNION	t2	ALL	NULL	NULL	NULL	NULL	4	Using where
NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
explain
select * from t2, 
(select a from t1 where b >= 'f' 
union
select c as a from t2 where c < 4) as t 
where t2.c=t.a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	4	Using where
1	PRIMARY	<derived2>	ref	key0	key0	5	test.t2.c	2	
2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
3	UNION	t2	ALL	NULL	NULL	NULL	NULL	4	Using where
NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
# t is defined in the with clause of a subquery
select t1.a,t1.b from t1,t2
where t1.a>t2.c and
t2.c in (with t as (select * from t1 where t1.a<5)
select t2.c from t2,t where t2.c=t.a);
a	b
4	aaaa
7	bb
7	bb
4	dd
7	bb
7	bb
4	ggg
select t1.a,t1.b from t1,t2
where t1.a>t2.c and
t2.c in (select t2.c
from t2,(select * from t1 where t1.a<5) as t
where t2.c=t.a);
a	b
4	aaaa
7	bb
7	bb
4	dd
7	bb
7	bb
4	ggg
explain
select t1.a,t1.b from t1,t2
where t1.a>t2.c and
t2.c in (with t as (select * from t1 where t1.a<5)
select t2.c from t2,t where t2.c=t.a);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	4	
1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where; Using join buffer (flat, BNL join)
2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	4	Using where
2	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	8	Using where; Using join buffer (flat, BNL join)
explain
select t1.a,t1.b from t1,t2
where t1.a>t2.c and
t2.c in (select t2.c
from t2,(select * from t1 where t1.a<5) as t
where t2.c=t.a);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	4	
1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where; Using join buffer (flat, BNL join)
2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	4	Using where
2	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	8	Using where; Using join buffer (flat, BNL join)
# two different definitions of t: one in the with clause of the main query,
# the other in the with clause of a subquery 
with t as (select c from t2 where c >= 4)
select t1.a,t1.b from t1,t
where t1.a=t.c and
t.c in (with t as (select * from t1 where t1.a<5)
select t2.c from t2,t where t2.c=t.a);
a	b
4	aaaa
4	dd
4	ggg
select t1.a,t1.b from t1, (select c from t2 where c >= 4) as t
where t1.a=t.c and
t.c in (select t2.c from t2,  (select * from t1 where t1.a<5) as t
where t2.c=t.a);
a	b
4	aaaa
4	dd
4	ggg
explain
with t as (select c from t2 where c >= 4)
select t1.a,t1.b from t1,t
where t1.a=t.c and
t.c in (with t as (select * from t1 where t1.a<5)
select t2.c from t2,t where t2.c=t.a);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	4	Using where
1	PRIMARY	<subquery3>	eq_ref	distinct_key	distinct_key	4	func	1	
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where; Using join buffer (flat, BNL join)
3	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	4	Using where
3	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	8	Using where; Using join buffer (flat, BNL join)
explain
select t1.a,t1.b from t1, (select c from t2 where c >= 4) as t
where t1.a=t.c and
t.c in (select t2.c from t2,  (select * from t1 where t1.a<5) as t
where t2.c=t.a);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	4	Using where
1	PRIMARY	<subquery3>	eq_ref	distinct_key	distinct_key	4	func	1	
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where; Using join buffer (flat, BNL join)
3	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	4	Using where
3	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	8	Using where; Using join buffer (flat, BNL join)
# another with table tt is defined in the with clause of a subquery
# from the specification of t
with t as (select * from t1
where a>2 and
b in (with tt as (select * from t2 where t2.c<5)
select t1.b from t1,tt where t1.a=tt.c))
select t.a, count(*) from t1,t where t1.a=t.a  group by t.a;
a	count(*)
3	1
4	9
select t.a, count(*)
from t1,
(select * from t1
where a>2 and
b in (select t1.b
from t1,
(select * from t2 where t2.c<5) as tt
where t1.a=tt.c)) as t
where t1.a=t.a  group by t.a;
a	count(*)
3	1
4	9
explain
with t as (select * from t1
where a>2 and
b in (with tt as (select * from t2 where t2.c<5)
select t1.b from t1,tt where t1.a=tt.c))
select t.a, count(*) from t1,t where t1.a=t.a  group by t.a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where; Using temporary; Using filesort
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where; Using join buffer (flat, BNL join)
1	PRIMARY	<subquery3>	eq_ref	distinct_key	distinct_key	35	func	1	
3	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	4	Using where
3	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	8	Using where; Using join buffer (flat, BNL join)
explain
select t.a, count(*)
from t1,
(select * from t1
where a>2 and
b in (select t1.b
from t1,
(select * from t2 where t2.c<5) as tt
where t1.a=tt.c)) as t
where t1.a=t.a  group by t.a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where; Using temporary; Using filesort
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where; Using join buffer (flat, BNL join)
1	PRIMARY	<subquery3>	eq_ref	distinct_key	distinct_key	35	func	1	
3	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	4	Using where
3	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	8	Using where; Using join buffer (flat, BNL join)
# with clause in the specification of a derived table
select *
from t1, 
(with t as (select a from t1 where b >= 'c') 
select * from t2,t where t2.c=t.a) as tt
where t1.b > 'f' and tt.a=t1.a;
a	b	c	a
4	ggg	4	4
4	ggg	4	4
select *
from t1, 
(select * from t2,
(select a from t1 where b >= 'c') as t
where t2.c=t.a) as tt
where t1.b > 'f' and tt.a=t1.a;
a	b	c	a
4	ggg	4	4
4	ggg	4	4
explain
select *
from t1, 
(with t as (select a from t1 where b >= 'c') 
select * from t2,t where t2.c=t.a) as tt
where t1.b > 'f' and tt.a=t1.a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	4	
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	8	Using where; Using join buffer (flat, BNL join)
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	8	Using where; Using join buffer (incremental, BNL join)
explain
select *
from t1, 
(select * from t2,
(select a from t1 where b >= 'c') as t
where t2.c=t.a) as tt
where t1.b > 'f' and tt.a=t1.a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	4	
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	8	Using where; Using join buffer (flat, BNL join)
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	8	Using where; Using join buffer (incremental, BNL join)
# with claused in the specification of a view
create view v1 as 
with t as (select a from t1 where b >= 'c') 
select * from t2,t where t2.c=t.a;
show create view v1;
View	Create View	character_set_client	collation_connection
v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t as (select `t1`.`a` AS `a` from `t1` where `t1`.`b` >= 'c')select `t2`.`c` AS `c`,`t`.`a` AS `a` from (`t2` join `t`) where `t2`.`c` = `t`.`a`	latin1	latin1_swedish_ci
select * from v1;
c	a
4	4
3	3
4	4
explain
select * from v1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	4	
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	8	Using where; Using join buffer (flat, BNL join)
# with claused in the specification of a materialized view
create view v2 as 
with t as (select a, count(*) from t1 where b >= 'c' group by a) 
select * from t2,t where t2.c=t.a;
show create view v2;
View	Create View	character_set_client	collation_connection
v2	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS with t as (select `t1`.`a` AS `a`,count(0) AS `count(*)` from `t1` where `t1`.`b` >= 'c' group by `t1`.`a`)select `t2`.`c` AS `c`,`t`.`a` AS `a`,`t`.`count(*)` AS `count(*)` from (`t2` join `t`) where `t2`.`c` = `t`.`a`	latin1	latin1_swedish_ci
select * from v2;
c	a	count(*)
4	4	2
3	3	1
explain
select * from v2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	4	Using where
1	PRIMARY	<derived3>	ref	key0	key0	5	test.t2.c	2	
3	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	8	Using where; Using temporary; Using filesort
# with clause in the specification of a view that whose definition
# table alias for a with table
create view v3 as
with t(c) as (select a from t1 where b >= 'c')
select * from t r1 where r1.c=4;
show create view v3;
View	Create View	character_set_client	collation_connection
v3	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v3` AS with t as (select `t1`.`a` AS `c` from `t1` where `t1`.`b` >= 'c')select `r1`.`c` AS `c` from `t` `r1` where `r1`.`c` = 4	latin1	latin1_swedish_ci
select * from v3;
c
4
4
# with clause in the specification of a view that whose definition
# two table aliases for for the same with table
create view v4(c,d) as
with t(c) as (select a from t1 where b >= 'c')
select * from t r1, t r2 where r1.c=r2.c and r2.c=4;
show create view v4;
View	Create View	character_set_client	collation_connection
v4	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v4` AS with t as (select `t1`.`a` AS `c` from `t1` where `t1`.`b` >= 'c')select `r1`.`c` AS `c`,`r2`.`c` AS `d` from (`t` `r1` join `t` `r2`) where `r1`.`c` = `r2`.`c` and `r2`.`c` = 4	latin1	latin1_swedish_ci
select * from v4;
c	d
4	4
4	4
4	4
4	4
explain
select * from v4;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	8	Using where; Using join buffer (flat, BNL join)
drop view v1,v2,v3,v4;
# currently any views containing with clause are not updatable
create view v1(a) as 
with t as (select a from t1 where b >= 'c') 
select t.a from t2,t where t2.c=t.a;
update v1 set a=0 where a > 4;
ERROR HY000: The target table v1 of the UPDATE is not updatable
drop view v1;
# prepare of a query containing a definition of a with table t
prepare stmt1 from "
with t as (select a from t1 where b >= 'c') 
  select * from t2,t where t2.c=t.a;
";
execute stmt1;
c	a
4	4
3	3
4	4
execute stmt1;
c	a
4	4
3	3
4	4
deallocate prepare stmt1;
# prepare of a query containing a definition of a materialized t
prepare stmt1 from "
with t as (select a, count(*) from t1 where b >= 'c' group by a) 
 select * from t2,t where t2.c=t.a;
";
execute stmt1;
c	a	count(*)
4	4	2
3	3	1
execute stmt1;
c	a	count(*)
4	4	2
3	3	1
deallocate prepare stmt1;
# prepare of a query containing two references to with table t
prepare stmt1 from "
with t as (select * from t1 where b >= 'c')
  select * from t as r1, t as r2 where r1.a=r2.a;
";
execute stmt1;
a	b	a	b
1	ccc	1	ccc
1	fff	1	ccc
4	dd	4	dd
4	ggg	4	dd
3	eee	3	eee
1	ccc	1	fff
1	fff	1	fff
4	dd	4	ggg
4	ggg	4	ggg
execute stmt1;
a	b	a	b
1	ccc	1	ccc
1	fff	1	ccc
4	dd	4	dd
4	ggg	4	dd
3	eee	3	eee
1	ccc	1	fff
1	fff	1	fff
4	dd	4	ggg
4	ggg	4	ggg
deallocate prepare stmt1;
with t(f) as (select * from t1 where b >= 'c') 
select * from t2,t where t2.c=t.f1;
ERROR HY000: WITH column list and SELECT field list have different column counts
with t(f1,f1) as (select * from t1 where b >= 'c') 
select * from t2,t where t2.c=t.f1;
ERROR 42S21: Duplicate column name 'f1'
with t as (select * from t2 where c>3),
t as (select a from t1 where a>2)
select * from t,t1 where t1.a=t.c;
ERROR HY000: Duplicate query name in WITH clause 't'
with t as (select a from s where a<5),
s as (select a from t1 where b>='d')
select * from t,s where t.a=s.a;
ERROR 42S02: Table 'test.s' doesn't exist
with recursive
t as (select a from s where a<5),
s as (select a from t1 where b>='d')
select * from t,s where t.a=s.a;
a	a
4	4
4	4
3	3
1	1
4	4
4	4
with recursive t as (select * from s where a>2),
s as (select a from t1,r where t1.a>r.c), 
r as (select c from t,t2 where t.a=t2.c) 
select * from r where r.c<7;
ERROR HY000: No anchors for recursive WITH element 't'
with recursive
t as (select * from s where a>2),
s as (select a from t1,r where t1.a>r.c), 
r as (select c from t,t2 where t.a=t2.c) 
select * from r where r.c<7;
ERROR HY000: No anchors for recursive WITH element 't'
with recursive
t as (select * from t1
where a in (select c from s where b<='ccc') and  b>'b'),
s as (select * from t1,t2
where t1.a=t2.c and t1.c in (select a from t where a<5))
select * from s where s.b>'aaa';
ERROR HY000: No anchors for recursive WITH element 't'
with recursive 
t as (select * from t1 where b>'aaa' and b <='d') 
select t.b from t,t2
where t.a=t2.c and
t2.c in (with recursive
s as (select t1.a from s,t1 where t1.a=s.a and t1.b<'c')
select * from s);
ERROR HY000: No anchors for recursive WITH element 's'
#erroneous definition of unreferenced with table t
with t as (select count(*) from t1 where d>='f' group by a)
select t1.b from t2,t1 where t1.a = t2.c;
ERROR 42S22: Unknown column 'd' in 'where clause'
with t as (select count(*) from t1 where b>='f' group by a)
select t1.b from t2,t1 where t1.a = t2.c;
b
aaaa
dd
eee
ggg
#erroneous definition of s referring to unreferenced t
with t(d) as (select count(*) from t1 where b<='ccc' group by b),
s as (select * from t1 where a in (select t2.d from t2,t where t2.c=t.d))
select t1.b from t1,t2 where t1.a=t2.c;
ERROR 42S22: Unknown column 't2.d' in 'field list'
with t(d) as (select count(*) from t1 where b<='ccc' group by b),
s as (select * from t1 where a in (select t2.c from t2,t where t2.c=t.c))
select t1.b from t1,t2 where t1.a=t2.c;
ERROR 42S22: Unknown column 't.c' in 'where clause'
with t(d) as (select count(*) from t1 where b<='ccc' group by b),
s as (select * from t1 where a in (select t2.c from t2,t where t2.c=t.d))
select t1.b from t1,t2 where t1.a=t2.c;
b
aaaa
dd
eee
ggg
#erroneous definition of unreferenced with table t
with t(f) as (select * from t1 where b >= 'c') 
select t1.b from t2,t1 where t1.a = t2.c;
ERROR HY000: WITH column list and SELECT field list have different column counts
#erroneous definition of unreferenced with table t
with t(f1,f1) as (select * from t1 where b >= 'c') 
select t1.b from t2,t1 where t1.a = t2.c;
ERROR 42S21: Duplicate column name 'f1'
# explain for query with unreferenced with table
explain 
with t as (select a from t1 where b >= 'c') 
select t1.b from t2,t1 where t1.a = t2.c;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	4	
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where; Using join buffer (flat, BNL join)
explain 
with t as (select a, count(*) from t1 where b >= 'c' group by a)
select t1.b from t2,t1 where t1.a = t2.c;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	4	
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where; Using join buffer (flat, BNL join)
# too many with elements in with clause
with s65 as (select * from t1), s64 as (select * from t1) , s63 as (select * from t1) , s62 as (select * from t1) , s61 as (select * from t1) , s60 as (select * from t1) , s59 as (select * from t1) , s58 as (select * from t1) , s57 as (select * from t1) , s56 as (select * from t1) , s55 as (select * from t1) , s54 as (select * from t1) , s53 as (select * from t1) , s52 as (select * from t1) , s51 as (select * from t1) , s50 as (select * from t1) , s49 as (select * from t1) , s48 as (select * from t1) , s47 as (select * from t1) , s46 as (select * from t1) , s45 as (select * from t1) , s44 as (select * from t1) , s43 as (select * from t1) , s42 as (select * from t1) , s41 as (select * from t1) , s40 as (select * from t1) , s39 as (select * from t1) , s38 as (select * from t1) , s37 as (select * from t1) , s36 as (select * from t1) , s35 as (select * from t1) , s34 as (select * from t1) , s33 as (select * from t1) , s32 as (select * from t1) , s31 as (select * from t1) , s30 as (select * from t1) , s29 as (select * from t1) , s28 as (select * from t1) , s27 as (select * from t1) , s26 as (select * from t1) , s25 as (select * from t1) , s24 as (select * from t1) , s23 as (select * from t1) , s22 as (select * from t1) , s21 as (select * from t1) , s20 as (select * from t1) , s19 as (select * from t1) , s18 as (select * from t1) , s17 as (select * from t1) , s16 as (select * from t1) , s15 as (select * from t1) , s14 as (select * from t1) , s13 as (select * from t1) , s12 as (select * from t1) , s11 as (select * from t1) , s10 as (select * from t1) , s9 as (select * from t1) , s8 as (select * from t1) , s7 as (select * from t1) , s6 as (select * from t1) , s5 as (select * from t1) , s4 as (select * from t1) , s3 as (select * from t1) , s2 as (select * from t1) , s1 as (select * from t1)  select * from s65;
ERROR HY000: Too many WITH elements in WITH clause
drop table t1,t2;
#
# Bug mdev-9937: View used in the specification of with table 
#                refers to the base table with the same name 
#
create table t1 (a int);
insert into t1 values (20), (30), (10);
create view v1 as select * from t1 where a > 10;
with t1 as (select * from v1) select * from t1;
a
20
30
drop view v1;
drop table t1;
#
# Bug mdev-10058: Invalid derived table with WITH clause  
#                 
CREATE TABLE t1 (a INT);
CREATE TABLE t2 (a INT);
CREATE TABLE t3 (a INT);
INSERT INTO t1 VALUES (1),(2),(3);
INSERT INTO t2 VALUES (1),(2),(3);
INSERT INTO t3 VALUES (1),(2),(3);
SELECT * FROM (WITH a AS (SELECT * FROM t1) (t2 NATURAL JOIN t3));
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 't2 NATURAL JOIN t3))' at line 1
SELECT * FROM (WITH a AS (SELECT * FROM t1) SELECT * FROM t2 NATURAL JOIN t3) AS d1;
a
1
2
3
DROP TABLE t1,t2,t3;
#
# Bug mdev-10344: the WITH clause of the query refers to a view that uses 
#     a base table with the same name as a  CTE table from the clause 
#
create table ten(a int primary key);
insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table one_k(a int primary key);
insert into one_k select A.a + B.a* 10 + C.a * 100 from ten A, ten B, ten C;
create view v1 as select * from ten;
select * from v1;
a
0
1
2
3
4
5
6
7
8
9
drop view v1;
drop table ten, one_k;
#
# MDEV-10057 : Crash with EXPLAIN + WITH + constant query
#
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1),(2),(3);
SELECT * FROM (WITH a AS (SELECT * FROM t1) SELECT 1) AS t1;
1
1
EXPLAIN SELECT * FROM (WITH a AS (SELECT * FROM t1) SELECT 1) AS t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	1	
2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
DROP TABLE t1;
#
# MDEV-10058: Suspicious EXPLAIN output for a derived table + WITH + joined table	
#
CREATE TABLE t1 (a INT);
CREATE TABLE t2 (a INT);
CREATE TABLE t3 (a INT);
INSERT INTO t1 VALUES (1),(2),(3);
INSERT INTO t2 VALUES (1),(2),(3);
INSERT INTO t3 VALUES (1),(2),(3);
EXPLAIN SELECT * FROM (WITH a AS (SELECT * FROM t1) (t2 NATURAL JOIN t3));
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 't2 NATURAL JOIN t3))' at line 1
explain SELECT * FROM (WITH a AS (SELECT * FROM t1) SELECT * FROM t2 NATURAL JOIN t3) AS d1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	
1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	3	Using where; Using join buffer (flat, BNL join)
DROP TABLE t1,t2,t3;
#
# MDEV-10729: Server crashes in st_select_lex::set_explain_type
#
CREATE TABLE t1 (i1 INT, KEY(i1)) ENGINE=MyISAM;
INSERT INTO t1 VALUES (4),(8);
CREATE TABLE t2 (a2 INT, b2 INT, KEY(b2)) ENGINE=MyISAM;
INSERT INTO t2 VALUES (8,7);
CREATE TABLE t3 (i3 INT) ENGINE=MyISAM;
INSERT INTO t3 VALUES (2),(6);
SELECT * FROM t1, t2 WHERE a2 = i1 and b2 >= i1 AND i1 IN ( SELECT i3 FROM t3 )
UNION
SELECT * FROM t1, t2 WHERE a2 = i1 and b2 >= i1 AND i1 IN ( SELECT i3 FROM t3 )
;
i1	a2	b2
DROP TABLE t1,t2,t3;
#
# MDEV-10923: mergeable CTE used twice in the query
#
create table employees (
name varchar(32),
dept varchar(32),
country varchar(8)
);
insert into employees 
values 
('Sergei Golubchik', 'Development', 'DE'),
('Claudio Nanni', 'Support', 'ES'),
('Sergei Petrunia', 'Development', 'RU');
with eng as 
(
select * from employees
where dept in ('Development','Support')
),
eu_eng  as 
(
select * from eng where country IN ('DE','ES','RU')
)
select * from eu_eng T1 
where 
not exists (select 1 from eu_eng T2 
where T2.country=T1.country
and T2.name <> T1.name);
name	dept	country
Sergei Golubchik	Development	DE
Claudio Nanni	Support	ES
Sergei Petrunia	Development	RU
drop table employees;
#
# MDEV-11818: EXPLAIN EXTENDED for a query with optimized away CTE table
#
CREATE TABLE t1 (i INT, c VARCHAR(3));
INSERT INTO t1 VALUES (1,'foo');
EXPLAIN EXTENDED
WITH cte AS ( SELECT * FROM t1 ) SELECT i FROM cte;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	100.00	
Warnings:
Note	1003	with cte as (select `test`.`t1`.`i` AS `i`,`test`.`t1`.`c` AS `c` from `test`.`t1`)select 1 AS `i` from dual
DROP TABLE t1;
#
# MDEV-12185: view defintion contains WITH clause with 
#             several specifications of CTE
#
with
alias1 as (select 1 as one),
alias2 as (select 2 as two)
select one, two from alias1, alias2;
one	two
1	2
create view v1 as 
with
alias1 as (select 1 as one),
alias2 as (select 2 as two)
select one, two from alias1, alias2;
select * from v1;
one	two
1	2
show create view v1;
View	Create View	character_set_client	collation_connection
v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with alias1 as (select 1 AS `one`), alias2 as (select 2 AS `two`)select `alias1`.`one` AS `one`,`alias2`.`two` AS `two` from (`alias1` join `alias2`)	latin1	latin1_swedish_ci
drop view v1;
#
# MDEV-12440: the same CTE table is used twice
#
create table t1 (a int, b  varchar(32));
insert into t1 values
(4,'aaaa' ), (7,'bb'), (1,'ccc'), (4,'dd');
# cte2 is used in the main query and in the spec for ct3
with
cte1 as (select * from t1 where  b >= 'c'),
cte2 as (select * from cte1 where a < 7),
cte3 as (select * from cte2 where a > 1)
select * from cte2, cte3 where cte2.a = cte3.a;
a	b	a	b
4	dd	4	dd
# cte2 is used twice in the spec for ct3
with
cte1 as (select * from t1 where  b >= 'b'),
cte2 as (select * from cte1 where b > 'c'),
cte3 as (select * from cte2 where a > 1 union select * from cte2 where a > 1)
select * from cte3;
a	b
4	dd
drop table t1;
#
# MDEV-12558: CTE with the same name as temporary table
#
CREATE TABLE t ENGINE=MyISAM AS SELECT 1 AS i;
CREATE TEMPORARY TABLE cte ENGINE=MyISAM AS SELECT 2 AS f;
WITH cte AS ( SELECT i FROM t ) SELECT * FROM cte;
i
1
WITH cte AS ( SELECT i FROM t GROUP BY i) SELECT * FROM cte;
i
1
SELECT * FROM cte;
f
2
DROP TABLE cte;
DROP TABLE t;
#
# MDEV-13107: SHOW TABLE STATUS, SHOW CREATE VIEW 
#             for CTEs that use derived tables
#
create table t1(a int) engine=myisam;
insert into t1 values (3), (1), (2);
create table t2 (b int) engine=myisam;
insert into t2 values (2), (10);
create view v1 as
with t as (select s.a from (select t1.a from t1) s),
r as(select t.a from t2, t where t2.b=t.a)
select a from r;
create view v2 as
with t as (select s.a from (select t1.a from t1) s),
r as(select t.a from t2, t where t2.b=t.a)
select a from t1;
show table status;
show create view v1;
View	Create View	character_set_client	collation_connection
v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t as (select `s`.`a` AS `a` from (select `test`.`t1`.`a` AS `a` from `test`.`t1`) `s`), r as (select `t`.`a` AS `a` from (`test`.`t2` join `t`) where `test`.`t2`.`b` = `t`.`a`)select `r`.`a` AS `a` from `r`	latin1	latin1_swedish_ci
show create view v2;
View	Create View	character_set_client	collation_connection
v2	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS with t as (select `s`.`a` AS `a` from (select `test`.`t1`.`a` AS `a` from `test`.`t1`) `s`), r as (select `t`.`a` AS `a` from (`test`.`t2` join `t`) where `test`.`t2`.`b` = `t`.`a`)select `test`.`t1`.`a` AS `a` from `test`.`t1`	latin1	latin1_swedish_ci
select * from v1;
a
2
select * from v2;
a
3
1
2
prepare stmt1 from "select * from v1";
execute stmt1;
a
2
execute stmt1;
a
2
prepare stmt2 from "select * from v2";
execute stmt2;
a
3
1
2
execute stmt2;
a
3
1
2
deallocate prepare stmt1;
deallocate prepare stmt2;
drop view v1,v2;
drop table t1,t2;
#
# MDEV-13796: UNION of two materialized CTEs
#
CREATE TABLE t1 (id int, k int);
CREATE TABLE t2 (id int);
INSERT INTO t1 VALUES (3,5), (1,7), (4,3);
INSERT INTO t2 VALUES (4), (3), (2);
WITH d1 AS (SELECT SUM(k) FROM t1, t2 as t2 WHERE t1.id = t2.id),
d2 AS (SELECT SUM(k) FROM t1, t2 as t2 WHERE t1.id = t2.id)
SELECT * FROM d1 UNION  SELECT * FROM d2;
SUM(k)
8
explain WITH d1 AS (SELECT SUM(k) FROM t1, t2 as t2 WHERE t1.id = t2.id),
d2 AS (SELECT SUM(k) FROM t1, t2 as t2 WHERE t1.id = t2.id)
SELECT * FROM d1 UNION  SELECT * FROM d2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	9	
2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	3	
2	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	3	Using where; Using join buffer (flat, BNL join)
4	UNION	<derived3>	ALL	NULL	NULL	NULL	NULL	9	
3	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	3	
3	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	3	Using where; Using join buffer (flat, BNL join)
NULL	UNION RESULT	<union1,4>	ALL	NULL	NULL	NULL	NULL	NULL	
DROP TABLE t1,t2;
#
# MDEV-13780: tower of embedding CTEs with multiple usage of them
#
create table t1 (a int);
insert into t1 values (3), (2), (4), (7), (1), (2), (5);
with cte_e as
(
with cte_o as
(
with cte_i as (select * from t1 where a < 7)
select * from cte_i where a > 1
)
select * from cte_o as cto_o1 where a < 3
union
select * from cte_o as cto_o2 where a > 4
)
select * from cte_e as cte_e1 where a > 1
union
select * from cte_e as cte_e2;
a
2
5
explain extended with cte_e as
(
with cte_o as
(
with cte_i as (select * from t1 where a < 7)
select * from cte_i where a > 1
)
select * from cte_o as cto_o1 where a < 3
union
select * from cte_o as cto_o2 where a > 4
)
select * from cte_e as cte_e1 where a > 1
union
select * from cte_e as cte_e2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	14	100.00	Using where
2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	7	100.00	Using where
5	UNION	t1	ALL	NULL	NULL	NULL	NULL	7	100.00	Using where
NULL	UNION RESULT	<union2,5>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
6	UNION	<derived9>	ALL	NULL	NULL	NULL	NULL	14	100.00	
9	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	7	100.00	Using where
12	UNION	t1	ALL	NULL	NULL	NULL	NULL	7	100.00	Using where
NULL	UNION RESULT	<union9,12>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
NULL	UNION RESULT	<union1,6>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
Warnings:
Note	1003	with cte_e as (with cte_o as (with cte_i as (select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 7)select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 1)select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 3 and `test`.`t1`.`a` > 1 and `test`.`t1`.`a` < 7 and `test`.`t1`.`a` > 1 union select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 4 and `test`.`t1`.`a` > 1 and `test`.`t1`.`a` < 7 and `test`.`t1`.`a` > 1)select `cte_e1`.`a` AS `a` from `cte_e` `cte_e1` where `cte_e1`.`a` > 1 union select `cte_e2`.`a` AS `a` from `cte_e` `cte_e2`
drop table t1;
#
# MDEV-13753: embedded CTE in a VIEW created in prepared statement
#
SET @sql_query = "
  CREATE OR REPLACE VIEW cte_test AS
   WITH  cte1  AS ( SELECT 1 as a from dual )
    ,    cte2  AS ( SELECT * FROM cte1 )
    SELECT * FROM cte2;
";
PREPARE stmt FROM @sql_query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SHOW CREATE VIEW cte_test;
View	Create View	character_set_client	collation_connection
cte_test	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `cte_test` AS with cte1 as (select 1 AS `a`), cte2 as (select `cte1`.`a` AS `a` from `cte1`)select `cte2`.`a` AS `a` from `cte2`	latin1	latin1_swedish_ci
SELECT * FROM cte_test;
a
1
DROP VIEW cte_test;
#
# mdev-14755 : PS for query using CTE in select with subquery
#
create table t1 (a int);
insert into t1 values
(7), (2), (8), (1), (3), (2), (7), (5), (4), (7), (9), (8);
with cte as
(select a from t1 where a between 4 and 7 group by a)
(select a from cte where exists( select a from t1 where cte.a=t1.a ))
union
(select a from t1 where a < 2);
a
7
5
4
1
prepare stmt from "with cte as
(select a from t1 where a between 4 and 7 group by a)
(select a from cte where exists( select a from t1 where cte.a=t1.a ))
union
(select a from t1 where a < 2)";
execute stmt;
a
7
5
4
1
execute stmt;
a
7
5
4
1
deallocate prepare stmt;
with cte as
(select a from t1 where a between 4 and 7 group by a)
(select a from t1 where a < 2)
union
(select a from cte where exists( select a from t1 where cte.a=t1.a ));
a
1
7
5
4
prepare stmt from "with cte as
(select a from t1 where a between 4 and 7 group by a)
(select a from t1 where a < 2)
union
(select a from cte where exists( select a from t1 where cte.a=t1.a ))";
execute stmt;
a
1
7
5
4
execute stmt;
a
1
7
5
4
deallocate prepare stmt;
with cte as
(select a from t1 where a between 4 and 7)
(select a from t1 where a < 2)
union
(select a from cte where exists( select a from t1 where cte.a=t1.a ));
a
1
7
5
4
prepare stmt from "with cte as
(select a from t1 where a between 4 and 7)
(select a from t1 where a < 2)
union
(select a from cte where exists( select a from t1 where cte.a=t1.a ))";
execute stmt;
a
1
7
5
4
execute stmt;
a
1
7
5
4
deallocate prepare stmt;
with cte as
(select a from t1 where a between 4 and 7)
(select a from cte
where exists( select a from t1 where t1.a < 2 and cte.a=t1.a ))
union
(select a from cte where exists( select a from t1 where cte.a=t1.a ));
a
7
5
4
prepare stmt from "with cte as
(select a from t1 where a between 4 and 7)
(select a from cte
where exists( select a from t1 where t1.a < 2 and cte.a=t1.a ))
union
(select a from cte where exists( select a from t1 where cte.a=t1.a ))";
execute stmt;
a
7
5
4
execute stmt;
a
7
5
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;