MDEV-8789 Implement non-recursive common table expressions

Initial implementation

19 files changed, 2070 insertions, 30 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;
diff --git a/mysql-test/t/cte_nonrecursive.test b/mysql-test/t/cte_nonrecursive.test
new file mode 100644
index 00000000000..90a0cdcac8c
--- /dev/null
+++ b/mysql-test/t/cte_nonrecursive.test
@@ -0,0 +1,376 @@
+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);  
+
+--echo # 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;
+select * from t2, (select a from t1 where b >= 'c') as t
+   where t2.c=t.a;
+explain 
+with t as (select a from t1 where b >= 'c') 
+  select * from t2,t where t2.c=t.a;
+explain
+select * from t2, (select a from t1 where b >= 'c') as t
+   where t2.c=t.a;
+
+--echo # select '*' in the specification of t
+with t as (select * from t1 where b >= 'c') 
+  select * from t2,t where t2.c=t.a;
+select * from t2, (select * from t1 where b >= 'c') as t
+   where t2.c=t.a;
+explain
+with t as (select * from t1 where b >= 'c') 
+  select * from t2,t where t2.c=t.a;
+explain
+select * from t2, (select * from t1 where b >= 'c') as t
+   where t2.c=t.a;
+
+--echo # 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;
+explain
+with t(f1,f2) as (select * from t1 where b >= 'c') 
+  select * from t2,t where t2.c=t.f1;
+
+--echo # 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;
+select * from t2, (select a, count(*) from t1 where b >= 'c' group by a) as t 
+ where t2.c=t.a;
+explain
+with t as (select a, count(*) from t1 where b >= 'c' group by a) 
+ select * from t2,t where t2.c=t.a;
+explain
+select * from t2, (select a, count(*) from t1 where b >= 'c' group by a) as t 
+  where t2.c=t.a;
+
+--echo # 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);
+select * from t2 
+  where c in (select a from (select a from t1 where a<5) as t);
+explain
+with t as (select a from t1 where a<5)
+  select * from t2 where c in (select a from t);
+explain
+select * from t2 
+  where c in (select a from (select a from t1 where a<5) as t);
+
+--echo # 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);
+select * from t2
+  where c in (select c from (select count(*) as c from t1
+                               where b >= 'c' group by a) as t);
+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);
+explain
+select * from t2
+  where c in (select c from (select count(*) as c from t1
+                               where b >= 'c' group by a) as t);
+
+--echo # two references to t specified by a query
+--echo # 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;
+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;
+explain
+with t as (select a from t1 where b >= 'c')
+  select * from t as r1, t as r2 where r1.a=r2.a;
+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;
+
+--echo # 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;
+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;
+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;
+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;
+
+--echo # two references to t specified by a query
+--echo # 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;
+select * from (select * from t1 where b >= 'c') as r1,
+              (select * from t1 where b >= 'c') as r2
+  where r1.a=r2.a;
+explain
+with t as (select * from t1 where b >= 'c')
+  select * from t as r1, t as r2 where r1.a=r2.a;
+explain
+select * from (select * from t1 where b >= 'c') as r1,
+              (select * from t1 where b >= 'c') as r2
+  where r1.a=r2.a;
+
+--echo # 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;
+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;
+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;
+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;
+
+--echo # 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);
+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);
+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);
+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);
+
+--echo # two different definitions of t: one in the with clause of the main query,
+--echo # 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);
+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);
+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);
+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);
+
+--echo # another with table tt is defined in the with clause of a subquery
+--echo # 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;
+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;
+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;
+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;
+
+--echo # 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; 
+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; 
+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; 
+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; 
+
+--echo # 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;
+select * from v1;
+explain
+select * from v1;
+
+--echo # 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;
+select * from v2;
+explain
+select * from v2;
+
+drop view v1,v2;
+
+--echo # 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;
+execute stmt1;
+deallocate prepare stmt1;
+
+--echo # 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;
+execute stmt1;
+deallocate prepare stmt1;
+
+--echo # 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;
+execute stmt1;
+deallocate prepare stmt1;
+
+--ERROR ER_WITH_COL_WRONG_LIST
+with t(f) as (select * from t1 where b >= 'c') 
+  select * from t2,t where t2.c=t.f1;
+
+--ERROR ER_DUP_FIELDNAME
+with t(f1,f1) as (select * from t1 where b >= 'c') 
+  select * from t2,t where t2.c=t.f1;
+
+--ERROR ER_DUP_QUERY_NAME
+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 ER_WRONG_ORDER_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;
+
+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;
+
+--ERROR ER_RECURSIVE_QUERY_IN_WITH_CLAUSE
+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 ER_RECURSIVE_QUERY_IN_WITH_CLAUSE
+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 ER_RECURSIVE_QUERY_IN_WITH_CLAUSE
+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 ER_RECURSIVE_QUERY_IN_WITH_CLAUSE
+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);
+--echo #erroneous definition of unreferenced with table t
+--ERROR ER_BAD_FIELD_ERROR
+with t as (select count(*) from t1 where d>='f' group by a)
+  select t1.b from t2,t1 where t1.a = t2.c;
+
+with t as (select count(*) from t1 where b>='f' group by a)
+  select t1.b from t2,t1 where t1.a = t2.c;
+
+--echo #erroneous definition of s referring to unreferenced t
+--ERROR ER_BAD_FIELD_ERROR
+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 ER_BAD_FIELD_ERROR
+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;
+
+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;
+
+--echo #erroneous definition of unreferenced with table t
+--ERROR ER_WITH_COL_WRONG_LIST
+with t(f) as (select * from t1 where b >= 'c') 
+  select t1.b from t2,t1 where t1.a = t2.c;
+
+--echo #erroneous definition of unreferenced with table t
+--ERROR ER_DUP_FIELDNAME
+with t(f1,f1) as (select * from t1 where b >= 'c') 
+  select t1.b from t2,t1 where t1.a = t2.c;
+
+drop table t1,t2;
diff --git a/sql/CMakeLists.txt b/sql/CMakeLists.txt
index 9b0017c9124..da1d54ef81d 100644
--- a/sql/CMakeLists.txt
+++ b/sql/CMakeLists.txt
@@ -137,6 +137,7 @@ SET (SQL_SOURCE
                my_json_writer.cc my_json_writer.h
                rpl_gtid.cc rpl_parallel.cc
                sql_type.cc sql_type.h
+	       sql_cte.cc sql_cte.h
 	       ${WSREP_SOURCES}
                table_cache.cc encryption.cc
                ${CMAKE_CURRENT_BINARY_DIR}/sql_builtin.cc
diff --git a/sql/lex.h b/sql/lex.h
index 22ff4e6d360..da5fa2de137 100644
--- a/sql/lex.h
+++ b/sql/lex.h
@@ -470,6 +470,7 @@ static SYMBOL symbols[] = {
   { "REAL",		SYM(REAL)},
   { "REBUILD",		SYM(REBUILD_SYM)},
   { "RECOVER",          SYM(RECOVER_SYM)},
+  { "RECURSIVE",        SYM(RECURSIVE_SYM)},
   { "REDO_BUFFER_SIZE",	SYM(REDO_BUFFER_SIZE_SYM)},
   { "REDOFILE",         SYM(REDOFILE_SYM)},
   { "REDUNDANT",	SYM(REDUNDANT_SYM)},
diff --git a/sql/share/errmsg-utf8.txt b/sql/share/errmsg-utf8.txt
index 59908dc51c0..42a461bf406 100644
--- a/sql/share/errmsg-utf8.txt
+++ b/sql/share/errmsg-utf8.txt
@@ -7136,3 +7136,11 @@ ER_KILL_QUERY_DENIED_ERROR
         eng "You are not owner of query %lu"
         ger "Sie sind nicht Eigentümer von Abfrage %lu"
         rus "Вы не являетесь владельцем запроса %lu"
+ER_WITH_COL_WRONG_LIST
+        eng "With column list and SELECT field list have different column counts"
+ER_DUP_QUERY_NAME
+        eng "Duplicate query name in with clause"
+ER_WRONG_ORDER_IN_WITH_CLAUSE
+        eng "The definition of the table '%s' refers to the table '%s' defined later in a non-recursive with clause"
+ER_RECURSIVE_QUERY_IN_WITH_CLAUSE
+        eng "Recursive queries in with clause are not supported yet"
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index 5c3b7c236c0..03d10ac3c86 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -49,6 +49,7 @@
 #include "transaction.h"
 #include "sql_prepare.h"
 #include "sql_statistics.h"
+#include "sql_cte.h"
 #include <m_ctype.h>
 #include <my_dir.h>
 #include <hash.h>
@@ -3925,6 +3926,26 @@ open_and_process_table(THD *thd, LEX *lex, TABLE_LIST *tables,
     tables->table_name= tables->view_name.str;
     tables->table_name_length= tables->view_name.length;
   }
+  else if (tables->select_lex) 
+  {
+    /*
+      Check whether 'tables' refers to a table defined in a with clause.
+      If so set the reference to the definition in tables->with.
+    */ 
+    if (!tables->with)
+      tables->with= tables->select_lex->find_table_def_in_with_clauses(tables);
+    /*
+      If 'tables' is defined in a with clause set the pointer to the
+      specification from its definition in tables->derived.
+    */
+    if (tables->with)
+    {
+      if (tables->set_as_with_table(thd, tables->with))
+        DBUG_RETURN(1);
+      else
+        goto end;
+    }
+  }
   /*
     If this TABLE_LIST object is a placeholder for an information_schema
     table, create a temporary table to represent the information_schema
@@ -8418,7 +8439,7 @@ insert_fields(THD *thd, Name_resolution_context *context, const char *db_name,
         temporary table. Thus in this case we can be sure that 'item' is an
         Item_field.
       */
-      if (any_privileges)
+      if (any_privileges && !tables->is_with_table() && !tables->is_derived())
       {
         DBUG_ASSERT((tables->field_translation == NULL && table) ||
                     tables->is_natural_join);
diff --git a/sql/sql_cte.cc b/sql/sql_cte.cc
new file mode 100644
index 00000000000..031208dafdb
--- /dev/null
+++ b/sql/sql_cte.cc
@@ -0,0 +1,595 @@
+#include "sql_class.h"
+#include "sql_lex.h"
+#include "sql_cte.h"
+#include "sql_view.h"    // for make_valid_column_names
+#include "sql_parse.h"
+
+
+/**
+  @brief
+    Check dependencies between tables defined in a list of with clauses
+ 
+  @param 
+    with_clauses_list  Pointer to the first clause in the list
+
+  @details
+    The procedure just calls the method With_clause::check_dependencies
+    for each member of the given list.
+
+  @retval
+    false   on success
+    true    on failure
+*/
+
+bool check_dependencies_in_with_clauses(With_clause *with_clauses_list)
+{
+  for (With_clause *with_clause= with_clauses_list;
+       with_clause;
+       with_clause= with_clause->next_with_clause)
+  {
+    if (with_clause->check_dependencies())
+      return true;
+  }
+  return false;
+}
+
+
+/**
+  @brief
+    Check dependencies between tables defined in this with clause
+
+ @details
+    The method performs the following actions for this with clause:
+
+    1. Test for definitions of the tables with the same name.
+    2. For each table T defined in this with clause look for tables
+       from the same with clause that are used in the query that
+       specifies T and set the dependencies of T on these tables
+       in dependency_map. 
+    3. Build the transitive closure of the above direct dependencies
+       to find out all recursive definitions.
+    4. If this with clause is not specified as recursive then
+       for each with table T defined in this with clause check whether
+       it is used in any definition that follows the definition of T.
+
+  @retval
+    true    if an error is reported 
+    false   otherwise
+*/
+
+bool With_clause::check_dependencies()
+{
+  if (dependencies_are_checked)
+    return false;
+  /* 
+    Look for for definitions with the same query name.
+    When found report an error and return true immediately.
+    For each table T defined in this with clause look for all other tables from
+    the same with with clause that are used in the specification of T.
+    For each such table set the dependency bit in the dependency map of
+    with element for T.
+  */
+  for (With_element *with_elem= first_elem;
+       with_elem != NULL;
+       with_elem= with_elem->next_elem)
+  {
+    for (With_element *elem= first_elem;
+         elem != with_elem;
+         elem= elem->next_elem)
+    {
+      if (my_strcasecmp(system_charset_info, with_elem->query_name->str,
+                        elem->query_name->str) == 0)
+      {
+	my_error(ER_DUP_QUERY_NAME, MYF(0), with_elem->query_name->str);
+	return true;
+      }
+    }
+    with_elem->check_dependencies_in_unit(with_elem->spec);   
+  }
+  /* Build the transitive closure of the direct dependencies found above */
+  for (With_element *with_elem= first_elem;
+       with_elem != NULL;
+       with_elem= with_elem->next_elem)
+  {
+    table_map with_elem_map=  with_elem->get_elem_map();
+    for (With_element *elem= first_elem; elem != NULL; elem= elem->next_elem)
+    {
+      if (elem->dependency_map & with_elem_map)
+        elem->dependency_map |= with_elem->dependency_map;
+    }   
+  }
+
+  /*
+    Mark those elements where tables are defined with direct or indirect recursion.
+    Report an error when recursion (direct or indirect) is used to define a table.
+  */ 
+  for (With_element *with_elem= first_elem;
+       with_elem != NULL;
+       with_elem= with_elem->next_elem)
+  {
+    if (with_elem->dependency_map & with_elem->get_elem_map())
+      with_elem->is_recursive= true;
+  }   
+  for (With_element *with_elem= first_elem;
+       with_elem != NULL;
+       with_elem= with_elem->next_elem)
+  {
+    if (with_elem->is_recursive)
+    {  
+      my_error(ER_RECURSIVE_QUERY_IN_WITH_CLAUSE, MYF(0),
+               with_elem->query_name->str);
+      return true;
+    }
+  }
+
+  if (!with_recursive)
+  {
+    /* 
+      For each with table T defined in this with clause check whether
+      it is used in any definition that follows the definition of T.
+    */
+    for (With_element *with_elem= first_elem;
+         with_elem != NULL;
+         with_elem= with_elem->next_elem)
+    {
+      With_element *checked_elem= with_elem->next_elem;
+      for (uint i = with_elem->number+1;
+           i < elements;
+           i++, checked_elem= checked_elem->next_elem)
+      {
+        if (with_elem->check_dependency_on(checked_elem))
+        {
+          my_error(ER_WRONG_ORDER_IN_WITH_CLAUSE, MYF(0),
+                   with_elem->query_name->str, checked_elem->query_name->str);
+          return true;
+        }
+      }
+    }
+  }
+	
+  dependencies_are_checked= true;
+  return false;
+}
+
+
+/**
+  @brief
+    Check dependencies on the sibling with tables used in the given unit
+
+  @param unit  The unit where the siblings are to be searched for
+
+  @details
+    The method recursively looks through all from lists encountered
+    the given unit. If it finds a reference to a table that is
+    defined in the same with clause to which this element belongs
+    the method set the bit of dependency on this table in the
+    dependency_map of this element.
+*/
+
+void With_element::check_dependencies_in_unit(st_select_lex_unit *unit)
+{
+  st_select_lex *sl= unit->first_select();
+  for (; sl; sl= sl->next_select())
+  {
+    for (TABLE_LIST *tbl= sl->table_list.first; tbl; tbl= tbl->next_local)
+    {
+      if (!tbl->with)
+        tbl->with= owner->find_table_def(tbl);
+      if (!tbl->with && tbl->select_lex)
+        tbl->with= tbl->select_lex->find_table_def_in_with_clauses(tbl);
+      if (tbl->with && tbl->with->owner== this->owner)
+        set_dependency_on(tbl->with);
+    }
+    st_select_lex_unit *inner_unit= sl->first_inner_unit();
+    for (; inner_unit; inner_unit= inner_unit->next_unit())
+      check_dependencies_in_unit(inner_unit);
+  }
+}
+
+
+/**
+  @brief
+    Search for the definition of a table among the elements of this with clause
+ 
+  @param table    The reference to the table that is looked for
+
+  @details
+    The function looks through the elements of this with clause trying to find
+    the definition of the given table. When it encounters the element with
+    the same query name as the table's name it returns this element. If no
+    such definitions are found the function returns NULL.
+
+  @retval
+    found with element if the search succeeded
+    NULL - otherwise
+*/    
+
+With_element *With_clause::find_table_def(TABLE_LIST *table)
+{
+  for (With_element *with_elem= first_elem; 
+       with_elem != NULL;
+       with_elem= with_elem->next_elem)
+  {
+    if (my_strcasecmp(system_charset_info, with_elem->query_name->str, table->table_name) == 0) 
+    {
+      return with_elem;
+    }
+  }
+  return NULL;
+}
+
+
+/**
+  @brief
+    Perform context analysis for all unreferenced tables defined in with clause
+
+  @param thd   The context of the statement containing this with clause
+
+  @details
+    For each unreferenced table T defined in this with clause the method
+    calls the method With_element::prepare_unreferenced that performs
+    context analysis of the element with the definition of T.
+
+  @retval
+    false   If context analysis does not report any error
+    true    Otherwise
+*/
+
+bool With_clause::prepare_unreferenced_elements(THD *thd)
+{
+  for (With_element *with_elem= first_elem; 
+       with_elem != NULL;
+       with_elem= with_elem->next_elem)
+  {
+    if (!with_elem->is_referenced() && with_elem->prepare_unreferenced(thd))
+      return true;
+  }
+
+  return false;
+}
+
+
+/**
+  @brief
+    Save the specification of the given with table as a string
+
+  @param thd        The context of the statement containing this with element
+  @param spec_start The beginning of the specification in the input string
+  @param spec_end   The end of the specification in the input string
+
+  @details
+    The method creates for a string copy of the specification used in this element.
+    The method is called when the element is parsed. The copy may be used to
+    create clones of the specification whenever they are needed.
+
+  @retval
+    false   on success
+    true    on failure
+*/
+  
+bool With_element::set_unparsed_spec(THD *thd, char *spec_start, char *spec_end)
+{
+  unparsed_spec.length= spec_end - spec_start;
+  unparsed_spec.str= (char*) sql_memdup(spec_start, unparsed_spec.length+1);
+  unparsed_spec.str[unparsed_spec.length]= '\0';
+
+  if (!unparsed_spec.str)
+  {
+    my_error(ER_OUTOFMEMORY, MYF(ME_FATALERROR), 
+             static_cast<int>(unparsed_spec.length));
+    return true;
+  }
+  return false;
+}
+
+
+/**
+  @brief
+   Create a clone of the specification for the given with table
+    
+  @param thd        The context of the statement containing this with element
+  @param with_table The reference to the table defined in this element for which
+                     the clone is created.
+
+  @details
+    The method creates a clone of the specification used in this element.
+    The clone is created for the given reference to the table defined by
+    this element.
+    The clone is created when the string with the specification saved in
+    unparsed_spec is fed into the parser as an input string. The parsing
+    this string a unit object representing the specification is build.
+    A chain of all table references occurred in the specification is also
+    formed.
+    The method includes the new unit and its sub-unit into hierarchy of
+    the units of the main query. I also insert the constructed chain of the 
+    table references into the chain of all table references of the main query.
+
+  @note
+    Clones is created only for not first references to tables defined in
+    the with clause. They are necessary for merged specifications because
+    the optimizer handles any such specification as independent on the others.
+    When a table defined in the with clause is materialized in a temporary table
+    one could do without specification clones. However in this case they
+    are created as well, because currently different table references to a
+    the same temporary table cannot share the same definition structure.
+
+  @retval
+     pointer to the built clone if succeeds
+     NULL - otherwise
+*/
+
+st_select_lex_unit *With_element::clone_parsed_spec(THD *thd,
+                                                    TABLE_LIST *with_table)
+{
+  LEX *lex;
+  st_select_lex_unit *res= NULL; 
+  Query_arena backup;
+  Query_arena *arena= thd->activate_stmt_arena_if_needed(&backup);
+
+  if (!(lex= (LEX*) new(thd->mem_root) st_lex_local))
+  {
+    if (arena)
+      thd->restore_active_arena(arena, &backup);
+    return res;
+  }
+  LEX *old_lex= thd->lex;
+  thd->lex= lex;
+
+  bool parse_status= false;
+  Parser_state parser_state;
+  TABLE_LIST *spec_tables;
+  TABLE_LIST *spec_tables_tail;
+  st_select_lex *with_select;
+
+  if (parser_state.init(thd, unparsed_spec.str, unparsed_spec.length))
+    goto err;
+  lex_start(thd);
+  with_select= &lex->select_lex;
+  with_select->select_number= ++thd->select_number;
+  parse_status= parse_sql(thd, &parser_state, 0);
+  if (parse_status)
+    goto err;
+  spec_tables= lex->query_tables;
+  spec_tables_tail= 0;
+  for (TABLE_LIST *tbl= spec_tables;
+       tbl;
+       tbl= tbl->next_global)
+  {
+    tbl->grant.privilege= with_table->grant.privilege;
+    spec_tables_tail= tbl;
+  }
+  if (spec_tables)
+  {
+    if (with_table->next_global)
+    {
+      spec_tables_tail->next_global= with_table->next_global;
+      with_table->next_global->prev_global= &spec_tables_tail->next_global;
+    }
+    else
+    {
+      old_lex->query_tables_last= &spec_tables_tail->next_global;
+    }
+    spec_tables->prev_global= &with_table->next_global;
+    with_table->next_global= spec_tables;
+  }
+  res= &lex->unit;
+  
+  lex->unit.include_down(with_table->select_lex);
+  lex->unit.set_slave(with_select); 
+  old_lex->all_selects_list=
+    (st_select_lex*) (lex->all_selects_list->
+		      insert_chain_before(
+			(st_select_lex_node **) &(old_lex->all_selects_list),
+                        with_select));
+  lex_end(lex);
+err:
+  if (arena)
+    thd->restore_active_arena(arena, &backup);
+  thd->lex= old_lex;
+  return res;
+}
+
+
+/**
+  @brief
+    Process optional column list of this with element
+     
+  @details
+    The method processes the column list in this with element.    
+    It reports an error if the cardinality of this list differs from
+    the cardinality of the select lists in the specification of the table
+    defined by this with element. Otherwise it renames the columns
+    of these select lists and sets the flag column_list_is_processed to true
+    preventing processing the list for the second time.
+
+  @retval
+    true   if an error was reported 
+    false  otherwise
+*/    
+
+bool With_element::process_column_list()
+{
+  if (column_list_is_processed)
+    return false;
+
+  st_select_lex *select= spec->first_select();
+
+  if (column_list.elements)  //  The column list is optional
+  {
+    List_iterator_fast<Item> it(select->item_list);
+    List_iterator_fast<LEX_STRING> nm(column_list);
+    Item *item;
+    LEX_STRING *name;
+
+    if (column_list.elements != select->item_list.elements)
+    {
+      my_error(ER_WITH_COL_WRONG_LIST, MYF(0));
+      return true;
+    }
+    /* Rename the columns of the first select in the specification query */
+    while ((item= it++, name= nm++))
+    {
+      item->set_name(name->str, (uint) name->length, system_charset_info);
+      item->is_autogenerated_name= false;
+    }
+  }
+
+  make_valid_column_names(select->item_list);
+
+  column_list_is_processed= true;
+  return false;
+}
+
+
+/**
+  @brief
+    Perform context analysis the definition of an unreferenced table
+     
+  @param thd        The context of the statement containing this with element
+
+  @details
+    The method assumes that this with element contains the definition
+    of a table that is not used anywhere. In this case one has to check
+    that context conditions are met. 
+
+  @retval
+    true   if an error was reported 
+    false  otherwise
+*/    
+
+bool With_element::prepare_unreferenced(THD *thd)
+{
+  bool rc= false;
+  st_select_lex *first_sl= spec->first_select();
+
+  /* Prevent name resolution for field references out of with elements */
+  for (st_select_lex *sl= first_sl;
+       sl;
+       sl= sl->next_select())
+    sl->context.outer_context= 0;
+
+  thd->lex->context_analysis_only|= CONTEXT_ANALYSIS_ONLY_DERIVED;
+  if (!spec->prepared &&
+      (spec->prepare(thd, 0, 0) ||
+       process_column_list() ||
+       check_duplicate_names(first_sl->item_list, 1)))
+    rc= true;
+ 
+  thd->lex->context_analysis_only&= ~CONTEXT_ANALYSIS_ONLY_DERIVED;
+  return rc;
+}
+
+
+/**
+   @brief
+     Search for the definition of the given table referred in this select node
+
+  @param table  reference to the table whose definition is searched for
+     
+  @details  
+    The method looks for the definition the table whose reference is occurred
+    in the FROM list of this select node. First it searches for it in the
+    with clause attached to the unit this select node belongs to. If such a
+    definition is not found there the embedding units are looked through.
+
+  @retval
+    pointer to the found definition if the search has been successful
+    NULL -  otherwise
+*/    
+
+With_element *st_select_lex::find_table_def_in_with_clauses(TABLE_LIST *table)
+{
+  With_element *found= NULL;
+  for (st_select_lex *sl= this;
+       sl;
+       sl= sl->master_unit()->outer_select())
+  {
+    With_clause *with_clause=sl->get_with_clause();
+    if (with_clause && (found= with_clause->find_table_def(table)))
+      return found;      
+  }
+  return found;
+}
+
+
+/**
+   @brief
+     Set the specifying unit in this reference to a with table  
+     
+  @details  
+    The method assumes that the given element with_elem defines the table T
+    this table reference refers to.
+    If this is the first reference to T the method just sets its specification
+    in the field 'derived' as the unit that yields T. Otherwise the method  
+    first creates a clone specification and sets rather this clone in this field.
+ 
+  @retval
+    false   on success
+    true    on failure
+*/    
+
+bool TABLE_LIST::set_as_with_table(THD *thd, With_element *with_elem)
+{
+  with= with_elem;
+  if (!with_elem->is_referenced())
+    derived= with_elem->spec;
+  else 
+  {
+    if(!(derived= with_elem->clone_parsed_spec(thd, this)))
+      return true;
+    derived->with_element= with_elem;
+  }
+  with_elem->inc_references();
+  return false;
+}
+
+
+/**
+  @brief
+    Print this with clause
+  
+  @param str         Where to print to
+  @param query_type  The mode of printing 
+     
+  @details
+    The method prints a string representation of this clause in the 
+    string str. The parameter query_type specifies the mode of printing.
+*/    
+
+void With_clause::print(String *str, enum_query_type query_type)
+{
+  str->append(STRING_WITH_LEN("WITH "));
+  if (with_recursive)
+    str->append(STRING_WITH_LEN("RECURSIVE "));
+  for (With_element *with_elem= first_elem;
+       with_elem != NULL;
+       with_elem= with_elem->next_elem)
+  {
+    with_elem->print(str, query_type);
+    if (with_elem != first_elem)
+      str->append(", ");
+  }
+}
+
+
+/**
+   @brief
+     Print this with element
+  
+  @param str         Where to print to
+  @param query_type  The mode of printing 
+     
+  @details
+    The method prints a string representation of this with element in the 
+    string str. The parameter query_type specifies the mode of printing.
+*/
+
+void With_element::print(String *str, enum_query_type query_type)
+{
+  str->append(query_name);
+  str->append(STRING_WITH_LEN(" AS "));
+  str->append('(');
+  spec->print(str, query_type);
+  str->append(')');
+}
+
diff --git a/sql/sql_cte.h b/sql/sql_cte.h
new file mode 100644
index 00000000000..5d3c0000581
--- /dev/null
+++ b/sql/sql_cte.h
@@ -0,0 +1,180 @@
+#ifndef SQL_CTE_INCLUDED
+#define SQL_CTE_INCLUDED
+#include "sql_list.h"
+#include "sql_lex.h"
+
+class With_clause;
+
+/**
+  @class With_clause
+  @brief Set of with_elements
+
+  It has a reference to the first with element from this with clause.
+  This reference allows to navigate through all the elements of the with clause.
+  It contains a reference to the unit to which this with clause is attached.
+  It also contains a flag saying whether this with clause was specified as recursive.
+*/ 
+
+class With_element : public Sql_alloc
+{
+private:
+  With_clause *owner;      // with clause this object belongs to
+  With_element *next_elem; // next element in the with clause
+  uint number;  // number of the element in the with clause (starting from 0)
+  /* 
+    The map dependency_map has 1 in the i-th position if the query that
+    specifies this element contains a reference to the element number i
+    in the query FROM list.
+  */
+  table_map elem_map;  // The map where with only one 1 set in this->number   
+  table_map dependency_map;
+  /* 
+    Total number of references to this element in the FROM lists of
+    the queries that are in the scope of the element (including
+    subqueries and specifications of other with elements).
+  */ 
+  uint references;
+  /* 
+    Unparsed specification of the query that specifies this element.
+    It used to build clones of the specification if they are needed.
+  */
+  LEX_STRING unparsed_spec;
+
+  /* Return the map where 1 is set only in the position for this element */
+  table_map get_elem_map() { return 1 << number; }
+ 
+public:
+  /*
+    The name of the table introduced by this with elememt. The name
+     can be used in FROM lists of the queries in the scope of the element.
+  */
+  LEX_STRING *query_name;
+  /*
+    Optional list of column names to name the columns of the table introduced
+    by this with element. It is used in the case when the names are not
+    inherited from the query that specified the table. Otherwise the list is
+    always empty.
+  */
+  List <LEX_STRING> column_list;
+  /* The query that specifies the table introduced by this with element */
+  st_select_lex_unit *spec;
+  /* Set to true after column list has been processed in semantic analysis */
+  bool column_list_is_processed;
+  /* 
+    Set to true is recursion is used (directly or indirectly)
+    for the definition of this element
+  */
+  bool is_recursive;
+
+  With_element(LEX_STRING *name,
+               List <LEX_STRING> list,
+               st_select_lex_unit *unit)
+    : next_elem(NULL), dependency_map(0), references(0),
+      query_name(name), column_list(list), spec(unit),
+      column_list_is_processed(false), is_recursive(false) {}
+
+  void check_dependencies_in_unit(st_select_lex_unit *unit);
+
+  void  set_dependency_on(With_element *with_elem)
+  { dependency_map|= with_elem->get_elem_map(); }
+
+  bool check_dependency_on(With_element *with_elem)
+  { return dependency_map & with_elem->get_elem_map(); }
+
+  bool set_unparsed_spec(THD *thd, char *spec_start, char *spec_end);
+
+  st_select_lex_unit *clone_parsed_spec(THD *thd, TABLE_LIST *with_table);
+
+  bool process_column_list();
+
+  bool is_referenced() { return references != 0; }
+
+  void inc_references() { references++; }
+
+  bool prepare_unreferenced(THD *thd);
+
+  void print(String *str, enum_query_type query_type);
+
+  friend class With_clause;
+};
+
+
+/**
+  @class With_element
+  @brief Definition of a CTE table
+	
+  It contains a reference to the name of the table introduced by this with element,
+  and a reference to the unit that specificies this table. Also it contains
+  a reference to the with clause to which this element belongs to.	
+*/
+
+class With_clause : public Sql_alloc
+{
+private:
+  st_select_lex_unit *owner; // the unit this with clause attached to
+  With_element *first_elem; // the first definition in this with clause 
+  With_element **last_next; // here is set the link for the next added element
+  uint elements; // number of the elements/defintions in this with clauses 
+  /*
+    The with clause immediately containing this with clause if there is any,
+    otherwise NULL. Now used  only at parsing.
+  */
+  With_clause *embedding_with_clause;
+  /*
+    The next with the clause of the chain of with clauses encountered
+    in the current statement
+  */
+  With_clause *next_with_clause;
+  /* Set to true if dependencies between with elements have been checked */
+  bool dependencies_are_checked; 
+
+public:
+ /* If true the specifier RECURSIVE is present in the with clause */
+  bool with_recursive;
+
+  With_clause(bool recursive_fl, With_clause *emb_with_clause)
+    : owner(NULL), first_elem(NULL), elements(0),
+      embedding_with_clause(emb_with_clause), next_with_clause(NULL),
+    dependencies_are_checked(false),
+      with_recursive(recursive_fl)
+  { last_next= &first_elem; }
+
+  /* Add a new element to the current with clause */ 
+  bool add_with_element(With_element *elem)
+  { 
+    elem->owner= this;
+    elem->number= elements;
+    owner= elem->spec;
+    owner->with_element= elem;
+    *last_next= elem;
+    last_next= &elem->next_elem;
+    elements++;
+    return false;
+  }
+
+  /* Add this with clause to the list of with clauses used in the statement */
+  void add_to_list(With_clause ** &last_next)
+  {
+    *last_next= this;
+    last_next= &this->next_with_clause;
+  }
+
+  With_clause *pop() { return embedding_with_clause; }
+      
+  bool check_dependencies();
+
+  With_element *find_table_def(TABLE_LIST *table);
+
+  With_element *find_table_def_in_with_clauses(TABLE_LIST *table);
+
+  bool prepare_unreferenced_elements(THD *thd);
+
+  void print(String *str, enum_query_type query_type);
+
+  friend
+  bool check_dependencies_in_with_clauses(With_clause *with_clauses_list);
+
+};
+
+
+#endif /* SQL_CTE_INCLUDED */
diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc
index baba4a876b3..e932480393e 100644
--- a/sql/sql_derived.cc
+++ b/sql/sql_derived.cc
@@ -30,6 +30,7 @@
 #include "sql_base.h"
 #include "sql_view.h"                         // check_duplicate_names
 #include "sql_acl.h"                          // SELECT_ACL
+#include "sql_cte.h"
 
 typedef bool (*dt_processor)(THD *thd, LEX *lex, TABLE_LIST *derived);
 
@@ -670,6 +671,9 @@ bool mysql_derived_prepare(THD *thd, LEX *lex, TABLE_LIST *derived)
   // st_select_lex_unit::prepare correctly work for single select
   if ((res= unit->prepare(thd, derived->derived_result, 0)))
     goto exit;
+  if (derived->with &&
+      (res= derived->with->process_column_list()))
+    goto exit; 
   lex->context_analysis_only&= ~CONTEXT_ANALYSIS_ONLY_DERIVED;
   if ((res= check_duplicate_names(thd, unit->types, 0)))
     goto exit;
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index f0bc582985b..95e1c511738 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -29,6 +29,7 @@
 #include "sp_head.h"
 #include "sp.h"
 #include "sql_select.h"
+#include "sql_cte.h"
 
 static int lex_one_token(YYSTYPE *yylval, THD *thd);
 
@@ -471,11 +472,15 @@ void lex_start(THD *thd)
   /* 'parent_lex' is used in init_query() so it must be before it. */
   lex->select_lex.parent_lex= lex;
   lex->select_lex.init_query();
+  lex->curr_with_clause= 0;
+  lex->with_clauses_list= 0;
+  lex->with_clauses_list_last_next= &lex->with_clauses_list;
   lex->value_list.empty();
   lex->update_list.empty();
   lex->set_var_list.empty();
   lex->param_list.empty();
   lex->view_list.empty();
+  lex->with_column_list.empty();
   lex->with_persistent_for_clause= FALSE;
   lex->column_list= NULL;
   lex->index_list= NULL;
@@ -1875,6 +1880,8 @@ void st_select_lex_unit::init_query()
   found_rows_for_union= 0;
   insert_table_with_stored_vcol= 0;
   derived= 0;
+  with_clause= 0;
+  with_element= 0;
 }
 
 void st_select_lex::init_query()
@@ -2058,6 +2065,37 @@ void st_select_lex_node::fast_exclude()
 }
 
 
+/**
+  @brief
+    Insert a new chain of nodes into another chain before a particular link
+
+  @param in/out
+    ptr_pos_to_insert  the address of the chain pointer pointing to the link
+                       before which the subchain has to be inserted
+  @param   
+    end_chain_node     the last link of the subchain to be inserted
+
+  @details
+    The method inserts the chain of nodes starting from this node and ending
+    with the node nd_chain_node into another chain of nodes before the node
+    pointed to by *ptr_pos_to_insert.
+    It is assumed that ptr_pos_to_insert belongs to the chain where we insert.
+    So it must be updated.
+
+  @retval
+    The method returns the pointer to the first link of the inserted chain
+*/
+
+st_select_lex_node *st_select_lex_node:: insert_chain_before(
+				         st_select_lex_node **ptr_pos_to_insert,
+                                         st_select_lex_node *end_chain_node)
+{
+  end_chain_node->link_next= *ptr_pos_to_insert;
+  (*ptr_pos_to_insert)->link_prev= &end_chain_node->link_next;
+  this->link_prev= ptr_pos_to_insert;
+  return this;
+}
+
 /*
   Exclude a node from the tree lex structure, but leave it in the global
   list of nodes.
@@ -2447,6 +2485,8 @@ bool st_select_lex::setup_ref_array(THD *thd, uint order_group_num)
 void st_select_lex_unit::print(String *str, enum_query_type query_type)
 {
   bool union_all= !union_distinct;
+  if (with_clause)
+    with_clause->print(str, query_type);
   for (SELECT_LEX *sl= first_select(); sl; sl= sl->next_select())
   {
     if (sl != first_select())
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index 9eddd6d61ee..f6eb5314c3a 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -48,6 +48,8 @@ class Item_func_match;
 class File_parser;
 class Key_part_spec;
 struct sql_digest_state;
+class With_clause;
+
 
 #define ALLOC_ROOT_SET 1024
 
@@ -178,6 +180,7 @@ const LEX_STRING sp_data_access_name[]=
 
 #define DERIVED_SUBQUERY	1
 #define DERIVED_VIEW		2
+#define DERIVED_WITH            4
 
 enum enum_view_create_mode
 {
@@ -540,7 +543,9 @@ public:
                                         List<String> *partition_names= 0,
                                         LEX_STRING *option= 0);
   virtual void set_lock_for_tables(thr_lock_type lock_type) {}
-
+  void set_slave(st_select_lex_node *slave_arg) { slave= slave_arg; }
+  st_select_lex_node *insert_chain_before(st_select_lex_node **ptr_pos_to_insert,
+                                          st_select_lex_node *end_chain_node);
   friend class st_select_lex_unit;
   friend bool mysql_new_select(LEX *lex, bool move_down);
   friend bool mysql_make_view(THD *thd, TABLE_SHARE *share, TABLE_LIST *table,
@@ -638,6 +643,10 @@ public:
     derived tables/views handling.
   */
   TABLE_LIST *derived;
+  /* With clause attached to this unit (if any) */
+  With_clause *with_clause;
+  /* With element where this unit is used as the specification (if any) */
+  With_element *with_element;
   /* thread handler */
   THD *thd;
   /*
@@ -668,6 +677,7 @@ public:
   {
     return reinterpret_cast<st_select_lex*>(slave);
   }
+  void set_with_clause(With_clause *with_cl) { with_clause= with_cl; }
   st_select_lex_unit* next_unit()
   {
     return reinterpret_cast<st_select_lex_unit*>(next);
@@ -1062,6 +1072,19 @@ public:
 
   void set_non_agg_field_used(bool val) { m_non_agg_field_used= val; }
   void set_agg_func_used(bool val)      { m_agg_func_used= val; }
+  void set_with_clause(With_clause *with_clause)
+  {
+    master_unit()->with_clause= with_clause;
+  }
+  With_clause *get_with_clause()
+  {
+    return master_unit()->with_clause;
+  }
+  With_element *get_with_element()
+  {
+    return master_unit()->with_element;
+  }
+  With_element *find_table_def_in_with_clauses(TABLE_LIST *table);
 
 private:
   bool m_non_agg_field_used;
@@ -2387,7 +2410,16 @@ struct LEX: public Query_tables_list
   SELECT_LEX *current_select;
   /* list of all SELECT_LEX */
   SELECT_LEX *all_selects_list;
-  
+  /* current with clause in parsing if any, otherwise 0*/
+  With_clause *curr_with_clause;  
+  /* pointer to the first with clause in the current statemant */
+  With_clause *with_clauses_list;
+  /*
+    (*with_clauses_list_last_next) contains a pointer to the last
+     with clause in the current statement
+  */
+  With_clause **with_clauses_list_last_next;
+
   /* Query Plan Footprint of a currently running select  */
   Explain_query *explain;
 
@@ -2453,6 +2485,7 @@ public:
   List<Item_func_set_user_var> set_var_list; // in-query assignment list
   List<Item_param>    param_list;
   List<LEX_STRING>    view_list; // view list (list of field names in view)
+  List<LEX_STRING>    with_column_list; // list of column names in with_list_element
   List<LEX_STRING>   *column_list; // list of column names (in ANALYZE)
   List<LEX_STRING>   *index_list;  // list of index names (in ANALYZE)
   /*
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index 52dcc7ee5d6..5cb8a4cd03e 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -92,6 +92,7 @@
 #include "transaction.h"
 #include "sql_audit.h"
 #include "sql_prepare.h"
+#include "sql_cte.h"
 #include "debug_sync.h"
 #include "probes_mysql.h"
 #include "set_var.h"
@@ -5820,6 +5821,9 @@ static bool execute_sqlcom_select(THD *thd, TABLE_LIST *all_tables)
         new (thd->mem_root) Item_int(thd,
                                      (ulonglong) thd->variables.select_limit);
   }
+  if (check_dependencies_in_with_clauses(lex->with_clauses_list))
+    return 1;
+
   if (!(res= open_and_lock_tables(thd, all_tables, TRUE, 0)))
   {
     if (lex->describe)
diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc
index 1cb4b56d244..3220323ed65 100644
--- a/sql/sql_prepare.cc
+++ b/sql/sql_prepare.cc
@@ -102,6 +102,7 @@ When one supplies long data for a placeholder:
 #include "sql_acl.h"    // *_ACL
 #include "sql_derived.h" // mysql_derived_prepare,
                          // mysql_handle_derived
+#include "sql_cte.h"
 #include "sql_cursor.h"
 #include "sp_head.h"
 #include "sp.h"
@@ -1497,6 +1498,8 @@ static int mysql_test_select(Prepared_statement *stmt,
   lex->select_lex.context.resolve_in_select_list= TRUE;
 
   ulong privilege= lex->exchange ? SELECT_ACL | FILE_ACL : SELECT_ACL;
+  if (check_dependencies_in_with_clauses(lex->with_clauses_list))
+    goto error;
   if (tables)
   {
     if (check_table_access(thd, privilege, tables, FALSE, UINT_MAX, FALSE))
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 5b6d98cdf45..e04e2ef2002 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -53,6 +53,7 @@
 #include "log_slow.h"
 #include "sql_derived.h"
 #include "sql_statistics.h"
+#include "sql_cte.h"
 
 #include "debug_sync.h"          // DEBUG_SYNC
 #include <m_ctype.h>
@@ -828,6 +829,10 @@ JOIN::prepare(Item ***rref_pointer_array,
       DBUG_RETURN(-1);				/* purecov: inspected */
     thd->lex->allow_sum_func= save_allow_sum_func;
   }
+
+  With_clause *with_clause=select_lex->get_with_clause();
+  if (with_clause && with_clause->prepare_unreferenced_elements(thd))
+    DBUG_RETURN(1);
   
   int res= check_and_do_in_subquery_rewrites(this);
 
@@ -24154,9 +24159,8 @@ int JOIN::save_explain_data_intern(Explain_query *output, bool need_tmp_table,
 
   /* There should be no attempts to save query plans for merged selects */
   DBUG_ASSERT(!join->select_lex->master_unit()->derived ||
-              join->select_lex->master_unit()->derived->is_materialized_derived());
-
-  explain= NULL;
+              join->select_lex->master_unit()->derived->is_materialized_derived() ||
+              join->select_lex->master_unit()->derived->is_with_table());
 
   /* Don't log this into the slow query log */
 
@@ -24665,11 +24669,14 @@ void TABLE_LIST::print(THD *thd, table_map eliminated_tables, String *str,
     }
     else if (derived)
     {
-      // A derived table
-      str->append('(');
-      derived->print(str, query_type);
-      str->append(')');
-      cmp_name= "";                               // Force printing of alias
+      if (!derived->derived->is_with_table())
+      {
+        // A derived table
+        str->append('(');
+        derived->print(str, query_type);
+        str->append(')');
+        cmp_name= "";                               // Force printing of alias
+      }
     }
     else
     {
diff --git a/sql/sql_view.cc b/sql/sql_view.cc
index 62e6790a142..b04bfd7ca42 100644
--- a/sql/sql_view.cc
+++ b/sql/sql_view.cc
@@ -167,7 +167,7 @@ err:
   @param item_list  List of Items which should be checked
 */
 
-static void make_valid_column_names(THD *thd, List<Item> &item_list)
+void make_valid_column_names(THD *thd, List<Item> &item_list)
 {
   Item *item;
   uint name_len;
diff --git a/sql/sql_view.h b/sql/sql_view.h
index 9c75643fd48..9f3881661b5 100644
--- a/sql/sql_view.h
+++ b/sql/sql_view.h
@@ -60,4 +60,6 @@ bool mysql_rename_view(THD *thd, const char *new_db, const char *new_name,
 
 extern const LEX_STRING view_type;
 
+void make_valid_column_names(List<Item> &item_list);
+
 #endif /* SQL_VIEW_INCLUDED */
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 77877682ff9..0baf8d7e878 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -54,6 +54,7 @@
 #include "sql_handler.h"                       // Sql_cmd_handler_*
 #include "sql_signal.h"
 #include "sql_get_diagnostics.h"               // Sql_cmd_get_diagnostics
+#include "sql_cte.h"
 #include "event_parse_data.h"
 #include "create_options.h"
 #include <myisam.h>
@@ -63,6 +64,7 @@
 #include "rpl_mi.h"
 #include "lex_token.h"
 
+
 /* this is to get the bison compilation windows warnings out */
 #ifdef _MSC_VER
 /* warning C4065: switch statement contains 'default' but no 'case' labels */
@@ -959,6 +961,8 @@ bool LEX::set_bincmp(CHARSET_INFO *cs, bool bin)
   class sp_label *splabel;
   class sp_name *spname;
   class sp_variable *spvar;
+  class With_clause *with_clause;
+
   handlerton *db_type;
   st_select_lex *select_lex;
   struct p_elem_val *p_elem_value;
@@ -1456,6 +1460,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, ulong *yystacksize);
 %token  REAL                          /* SQL-2003-R */
 %token  REBUILD_SYM
 %token  RECOVER_SYM
+%token  RECURSIVE_SYM
 %token  REDOFILE_SYM
 %token  REDO_BUFFER_SIZE_SYM
 %token  REDUNDANT_SYM
@@ -1740,6 +1745,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, ulong *yystacksize);
         case_stmt_body opt_bin_mod
         opt_if_exists_table_element opt_if_not_exists_table_element
         opt_into opt_procedure_clause
+	opt_recursive
 
 %type <object_ddl_options>
         create_or_replace 
@@ -1981,6 +1987,10 @@ END_OF_INPUT
         THEN_SYM WHEN_SYM DIV_SYM MOD_SYM OR2_SYM AND_AND_SYM DELETE_SYM
         ROLE_SYM
 
+%type <with_clause> opt_with_clause with_clause
+
+%type <lex_str_ptr> query_name
+
 %%
 
 
@@ -8387,10 +8397,11 @@ opt_ignore_leaves:
 
 
 select:
-          select_init
+          opt_with_clause select_init
           {
             LEX *lex= Lex;
             lex->sql_command= SQLCOM_SELECT;
+            lex->current_select->set_with_clause($1);
           }
         ;
 
@@ -10816,20 +10827,20 @@ table_factor:
             and our parser. Possibly this rule could be replaced by our
             query_expression_body.
           */
-        | '(' get_select_lex select_derived_union ')' opt_table_alias
+        | '('opt_with_clause get_select_lex select_derived_union ')' opt_table_alias
           {
-            /* Use $2 instead of Lex->current_select as derived table will
+            /* Use $3 instead of Lex->current_select as derived table will
                alter value of Lex->current_select. */
-            if (!($3 || $5) && $2->embedding &&
-                !$2->embedding->nested_join->join_list.elements)
+            if (!($4 || $6) && $3->embedding &&
+                !$3->embedding->nested_join->join_list.elements)
             {
-              /* we have a derived table ($3 == NULL) but no alias,
+              /* we have a derived table ($4 == NULL) but no alias,
                  Since we are nested in further parentheses so we
                  can pass NULL to the outer level parentheses
                  Permits parsing of "((((select ...))) as xyz)" */
               $$= 0;
             }
-            else if (!$3)
+            else if (!$4)
             {
               /* Handle case of derived table, alias may be NULL if there
                  are no outer parentheses, add_table_to_list() will throw
@@ -10837,12 +10848,13 @@ table_factor:
               LEX *lex=Lex;
               SELECT_LEX *sel= lex->current_select;
               SELECT_LEX_UNIT *unit= sel->master_unit();
+              unit->set_with_clause($2);
               lex->current_select= sel= unit->outer_select();
               Table_ident *ti= new (thd->mem_root) Table_ident(unit);
               if (ti == NULL)
                 MYSQL_YYABORT;
               if (!($$= sel->add_table_to_list(lex->thd,
-                                               ti, $5, 0,
+                                               ti, $6, 0,
                                                TL_READ, MDL_SHARED_READ)))
 
                 MYSQL_YYABORT;
@@ -10859,11 +10871,11 @@ table_factor:
                 $2->select_n_where_fields+=
                   sel->select_n_where_fields;
             }
-            /*else if (($3->select_lex &&
-                      $3->select_lex->master_unit()->is_union() &&
-                      ($3->select_lex->master_unit()->first_select() ==
-                       $3->select_lex || !$3->lifted)) || $5)*/
-            else if ($5 != NULL)
+            /*else if (($4->select_lex &&
+                      $4->select_lex->master_unit()->is_union() &&
+                      ($4->select_lex->master_unit()->first_select() ==
+                       $4->select_lex || !$4->lifted)) || $6)*/
+            else if ($6 != NULL)
             {
               /*
                 Tables with or without joins within parentheses cannot
@@ -10876,7 +10888,7 @@ table_factor:
             {
               /* nested join: FROM (t1 JOIN t2 ...),
                  nest_level is the same as in the outer query */
-              $$= $3;
+              $$= $4;
             }
           }
         ;
@@ -13653,8 +13665,93 @@ temporal_literal:
         ;
 
 
+opt_with_clause:
+	  /*empty */ { $$= 0; }
+	| with_clause
+          {
+            $$= $1;
+            Lex->derived_tables|= DERIVED_WITH;
+          }
+	;
+
+
+with_clause:
+        WITH opt_recursive
+          {
+             With_clause *with_clause=
+             new With_clause($2, Lex->curr_with_clause);
+             if (with_clause == NULL)
+               MYSQL_YYABORT;
+             Lex->curr_with_clause= with_clause;
+             with_clause->add_to_list(Lex->with_clauses_list_last_next);
+          }
+        with_list
+          {
+            $$= Lex->curr_with_clause;
+            Lex->curr_with_clause= Lex->curr_with_clause->pop();
+          } 
+        ;
+
+
+opt_recursive:
+ 	  /*empty*/ { $$= 0; }
+	| RECURSIVE_SYM { $$= 1; }
+	;
+
+
+with_list:
+	  with_list_element 	
+	| with_list ',' with_list_element
+	;
+
+
+with_list_element:
+	  query_name
+	  opt_with_column_list 
+          AS '(' remember_name subselect remember_end ')'
+ 	  {
+            With_element *elem= new With_element($1, Lex->with_column_list, $6->master_unit());
+	    if (elem == NULL || Lex->curr_with_clause->add_with_element(elem))
+	      MYSQL_YYABORT;
+	    Lex->with_column_list.empty();
+	    if (elem->set_unparsed_spec(thd, $5+1, $7))
+              MYSQL_YYABORT;
+	  }
+	;
+
+
+opt_with_column_list:
+          /* empty */
+          {}
+        | '(' with_column_list ')'
+        ;
+
+
+with_column_list:
+          ident 
+          {
+            Lex->with_column_list.push_back((LEX_STRING*)
+                    thd->memdup(&$1, sizeof(LEX_STRING)));
+	  }
+        | with_column_list ',' ident
+          {
+            Lex->with_column_list.push_back((LEX_STRING*)
+                    thd->memdup(&$3, sizeof(LEX_STRING)));
+          }
+        ;
+
+
+query_name: 
+          ident
+          {
+            $$= (LEX_STRING *) thd->memdup(&$1, sizeof(LEX_STRING));
+            if ($$ == NULL)
+              MYSQL_YYABORT;
+          }
+        ;
 
 
+	
 /**********************************************************************
 ** Creating different items.
 **********************************************************************/
@@ -15929,9 +16026,10 @@ query_expression_body:
 
 /* Corresponds to <query expression> in the SQL:2003 standard. */
 subselect:
-          subselect_start query_expression_body subselect_end
+          subselect_start opt_with_clause query_expression_body subselect_end
           { 
-            $$= $2;
+	    $3->set_with_clause($2);
+            $$= $3;
           }
         ;
 
@@ -16158,7 +16256,7 @@ view_select:
             lex->parsing_options.allows_derived= FALSE;
             lex->create_view_select.str= (char *) YYLIP->get_cpp_ptr();
           }
-          view_select_aux view_check_option
+          opt_with_clause view_select_aux view_check_option
           {
             LEX *lex= Lex;
             uint len= YYLIP->get_cpp_ptr() - lex->create_view_select.str;
@@ -16170,6 +16268,7 @@ view_select:
             lex->parsing_options.allows_select_into= TRUE;
             lex->parsing_options.allows_select_procedure= TRUE;
             lex->parsing_options.allows_derived= TRUE;
+            lex->current_select->set_with_clause($2);
           }
         ;
 
diff --git a/sql/table.cc b/sql/table.cc
index 933cfaaf27e..f409fe9a544 100644
--- a/sql/table.cc
+++ b/sql/table.cc
@@ -7280,7 +7280,9 @@ bool TABLE_LIST::init_derived(THD *thd, bool init_view)
   */
   if (is_merged_derived())
   {
-    if (is_view() || unit->prepared)
+    if (is_view() ||
+        (unit->prepared &&
+	!(thd->lex->context_analysis_only & CONTEXT_ANALYSIS_ONLY_VIEW)))
       create_field_translation(thd);
   }
 
@@ -7422,6 +7424,11 @@ void TABLE_LIST::set_lock_type(THD *thd, enum thr_lock_type lock)
   }
 }
 
+bool TABLE_LIST::is_with_table()
+{
+  return derived && derived->with_element;
+}
+
 uint TABLE_SHARE::actual_n_key_parts(THD *thd)
 {
   return use_ext_keys &&
diff --git a/sql/table.h b/sql/table.h
index ab3960300e6..62c0459092b 100644
--- a/sql/table.h
+++ b/sql/table.h
@@ -48,6 +48,7 @@ class ACL_internal_schema_access;
 class ACL_internal_table_access;
 class Field;
 class Table_statistics;
+class With_element;
 class TDC_element;
 
 /*
@@ -1839,6 +1840,7 @@ struct TABLE_LIST
      derived tables. Use TABLE_LIST::is_anonymous_derived_table().
   */
   st_select_lex_unit *derived;		/* SELECT_LEX_UNIT of derived table */
+  With_element *with;                   /* With element of with_table */
   ST_SCHEMA_TABLE *schema_table;        /* Information_schema table */
   st_select_lex	*schema_select_lex;
   /*
@@ -2203,6 +2205,7 @@ struct TABLE_LIST
   {
     return (derived_type & DTYPE_TABLE);
   }
+  bool is_with_table();
   inline void set_view()
   {
     derived_type= DTYPE_VIEW;
@@ -2243,6 +2246,7 @@ struct TABLE_LIST
   {
     derived_type|= DTYPE_MULTITABLE;
   }
+  bool set_as_with_table(THD *thd, With_element *with_elem);
   void reset_const_table();
   bool handle_derived(LEX *lex, uint phases);