MDEV-8789 Implement non-recursive common table expressions

Initial implementation

1 files changed, 655 insertions, 0 deletions

diff --git a/mysql-test/r/cte_nonrecursive.result b/mysql-test/r/cte_nonrecursive.result
new file mode 100644
index 00000000000..07449bc6486
--- /dev/null
+++ b/mysql-test/r/cte_nonrecursive.result
@@ -0,0 +1,655 @@
+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	SUBQUERY	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
+# 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	SUBQUERY	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	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where; Using temporary
+2	SUBQUERY	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)
+# 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	SUBQUERY	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)
+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)
+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	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	32	
+2	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	4	
+2	DERIVED	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	<derived2>	ALL	NULL	NULL	NULL	NULL	8	
+2	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	4	Using where
+2	DERIVED	<derived3>	ref	key0	key0	5	test.t2.c	2	
+3	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where; Using temporary; Using filesort
+drop view v1,v2;
+# 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
+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 HY000: The definition of the table 't' refers to the table 's' defined later in a non-recursive with clause
+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: Recursive queries in with clause are not supported yet
+with 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: Recursive queries in with clause are not supported yet
+with 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: Recursive queries in with clause are not supported yet
+with 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 s as (select t1.a from s,t1 where t1.a=s.a and t1.b<'c')
+select * from s);
+ERROR HY000: Recursive queries in with clause are not supported yet
+#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'
+drop table t1,t2;