Fixed many problems in the code of With_element::check_unrestricted_recursive().

Added the check whether there are set functions in the specifications of recursive CTE.
Added the check whether there are recursive references in subqueries.
Introduced boolean system variable 'standards_compliant_cte'. By default it's set to 'on'.
When it's set to 'off' non-standard compliant CTE can be executed.

13 files changed, 675 insertions, 52 deletions

diff --git a/mysql-test/r/cte_recursive.result b/mysql-test/r/cte_recursive.result
index aa9ed3fc28c..2b9c8f721ef 100644
--- a/mysql-test/r/cte_recursive.result
+++ b/mysql-test/r/cte_recursive.result
@@ -30,7 +30,8 @@ insert into folks values
 (9, 'Grandma Ann', '1941-10-15', null, null),
 (25, 'Uncle Jim', '1968-11-18', 8, 7),
 (98, 'Sister Amy', '2001-06-20', 20, 30),
-(8, 'Grandma Sally', '1943-08-23', 5, 6),
+(7, 'Grandma Sally', '1943-08-23', null, 6),
+(8, 'Grandpa Ben', '1940-10-21', null, null),
 (6, 'Grandgrandma Martha', '1923-05-17', null, null),
 (67, 'Cousin Eddie', '1992-02-28', 25, 27),
 (27, 'Auntie Melinda', '1971-03-29', null, null);
@@ -53,7 +54,8 @@ id	name	dob	father	mother
 30	Mom	1975-03-03	8	7
 10	Grandpa Bill	1940-04-05	NULL	NULL
 9	Grandma Ann	1941-10-15	NULL	NULL
-8	Grandma Sally	1943-08-23	5	6
+7	Grandma Sally	1943-08-23	NULL	6
+8	Grandpa Ben	1940-10-21	NULL	NULL
 6	Grandgrandma Martha	1923-05-17	NULL	NULL
 with recursive 
 ancestors
@@ -74,7 +76,8 @@ id	name	dob	father	mother
 30	Mom	1975-03-03	8	7
 10	Grandpa Bill	1940-04-05	NULL	NULL
 9	Grandma Ann	1941-10-15	NULL	NULL
-8	Grandma Sally	1943-08-23	5	6
+7	Grandma Sally	1943-08-23	NULL	6
+8	Grandpa Ben	1940-10-21	NULL	NULL
 6	Grandgrandma Martha	1923-05-17	NULL	NULL
 with recursive
 ancestors
@@ -93,7 +96,8 @@ id	name	dob	father	mother
 67	Cousin Eddie	1992-02-28	25	27
 25	Uncle Jim	1968-11-18	8	7
 27	Auntie Melinda	1971-03-29	NULL	NULL
-8	Grandma Sally	1943-08-23	5	6
+7	Grandma Sally	1943-08-23	NULL	6
+8	Grandpa Ben	1940-10-21	NULL	NULL
 6	Grandgrandma Martha	1923-05-17	NULL	NULL
 with recursive
 ancestors
@@ -115,7 +119,8 @@ id	name	dob	father	mother
 30	Mom	1975-03-03	8	7
 10	Grandpa Bill	1940-04-05	NULL	NULL
 9	Grandma Ann	1941-10-15	NULL	NULL
-8	Grandma Sally	1943-08-23	5	6
+7	Grandma Sally	1943-08-23	NULL	6
+8	Grandpa Ben	1940-10-21	NULL	NULL
 6	Grandgrandma Martha	1923-05-17	NULL	NULL
 with recursive 
 prev_gen
@@ -150,6 +155,7 @@ Mom	1975-03-03
 Grandpa Bill	1940-04-05
 Grandma Ann	1941-10-15
 Grandma Sally	1943-08-23
+Grandpa Ben	1940-10-21
 Grandgrandma Martha	1923-05-17
 with recursive
 descendants
@@ -183,7 +189,7 @@ where d.id=folks.father or d.id=folks.mother
 )
 select * from descendants;
 id	name	dob	father	mother
-8	Grandma Sally	1943-08-23	5	6
+7	Grandma Sally	1943-08-23	NULL	6
 30	Mom	1975-03-03	8	7
 25	Uncle Jim	1968-11-18	8	7
 100	Vasya	2000-01-01	20	30
@@ -208,6 +214,7 @@ where a.father=t1.id AND a.mother=t2.id);
 id	name	dob	father	mother	id	name	dob	father	mother
 20	Dad	1970-02-02	10	9	30	Mom	1975-03-03	8	7
 10	Grandpa Bill	1940-04-05	NULL	NULL	9	Grandma Ann	1941-10-15	NULL	NULL
+8	Grandpa Ben	1940-10-21	NULL	NULL	7	Grandma Sally	1943-08-23	NULL	6
 with 
 ancestor_couples(husband, h_dob, wife, w_dob)
 as
@@ -233,6 +240,7 @@ select * from ancestor_couples;
 husband	h_dob	wife	w_dob
 Dad	1970-02-02	Mom	1975-03-03
 Grandpa Bill	1940-04-05	Grandma Ann	1941-10-15
+Grandpa Ben	1940-10-21	Grandma Sally	1943-08-23
 with recursive
 ancestors
 as
@@ -256,7 +264,8 @@ id	name	dob	father	mother
 30	Mom	1975-03-03	8	7
 9	Grandma Ann	1941-10-15	NULL	NULL
 10	Grandpa Bill	1940-04-05	NULL	NULL
-8	Grandma Sally	1943-08-23	5	6
+8	Grandpa Ben	1940-10-21	NULL	NULL
+7	Grandma Sally	1943-08-23	NULL	6
 6	Grandgrandma Martha	1923-05-17	NULL	NULL
 with recursive
 ancestor_couples(h_id, h_name, h_dob, h_father, h_mother,
@@ -286,6 +295,7 @@ from ancestor_couples;
 h_name	h_dob	w_name	w_dob
 Dad	1970-02-02	Mom	1975-03-03
 Grandpa Bill	1940-04-05	Grandma Ann	1941-10-15
+Grandpa Ben	1940-10-21	Grandma Sally	1943-08-23
 prepare stmt1 from "
 with recursive 
 ancestors
@@ -308,7 +318,8 @@ id	name	dob	father	mother
 30	Mom	1975-03-03	8	7
 10	Grandpa Bill	1940-04-05	NULL	NULL
 9	Grandma Ann	1941-10-15	NULL	NULL
-8	Grandma Sally	1943-08-23	5	6
+7	Grandma Sally	1943-08-23	NULL	6
+8	Grandpa Ben	1940-10-21	NULL	NULL
 6	Grandgrandma Martha	1923-05-17	NULL	NULL
 execute stmt1;
 id	name	dob	father	mother
@@ -317,7 +328,8 @@ id	name	dob	father	mother
 30	Mom	1975-03-03	8	7
 10	Grandpa Bill	1940-04-05	NULL	NULL
 9	Grandma Ann	1941-10-15	NULL	NULL
-8	Grandma Sally	1943-08-23	5	6
+7	Grandma Sally	1943-08-23	NULL	6
+8	Grandpa Ben	1940-10-21	NULL	NULL
 6	Grandgrandma Martha	1923-05-17	NULL	NULL
 deallocate prepare stmt1;
 create view v1 as
@@ -344,7 +356,8 @@ id	name	dob	father	mother
 30	Mom	1975-03-03	8	7
 10	Grandpa Bill	1940-04-05	NULL	NULL
 9	Grandma Ann	1941-10-15	NULL	NULL
-8	Grandma Sally	1943-08-23	5	6
+7	Grandma Sally	1943-08-23	NULL	6
+8	Grandpa Ben	1940-10-21	NULL	NULL
 6	Grandgrandma Martha	1923-05-17	NULL	NULL
 create view v2 as
 with recursive
@@ -374,7 +387,8 @@ id	name	dob	father	mother
 30	Mom	1975-03-03	8	7
 9	Grandma Ann	1941-10-15	NULL	NULL
 10	Grandpa Bill	1940-04-05	NULL	NULL
-8	Grandma Sally	1943-08-23	5	6
+8	Grandpa Ben	1940-10-21	NULL	NULL
+7	Grandma Sally	1943-08-23	NULL	6
 6	Grandgrandma Martha	1923-05-17	NULL	NULL
 drop view v1,v2;
 explain extended
@@ -392,11 +406,314 @@ where p.id = a.father or p.id = a.mother
 )
 select * from ancestors;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	132	100.00	
-2	SUBQUERY	folks	ALL	NULL	NULL	NULL	NULL	11	100.00	Using where
-3	UNCACHEABLE UNION	p	ALL	NULL	NULL	NULL	NULL	11	100.00	
-3	UNCACHEABLE UNION	<derived2>	ALL	NULL	NULL	NULL	NULL	11	100.00	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	156	100.00	
+2	SUBQUERY	folks	ALL	NULL	NULL	NULL	NULL	12	100.00	Using where
+3	UNCACHEABLE UNION	p	ALL	NULL	NULL	NULL	NULL	12	100.00	
+3	UNCACHEABLE UNION	<derived2>	ALL	NULL	NULL	NULL	NULL	12	100.00	Using where; Using join buffer (flat, BNL join)
 NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
 Warnings:
 Note	1003	with recursive ancestors as (select `test`.`folks`.`id` AS `id`,`test`.`folks`.`name` AS `name`,`test`.`folks`.`dob` AS `dob`,`test`.`folks`.`father` AS `father`,`test`.`folks`.`mother` AS `mother` from `test`.`folks` where ((`test`.`folks`.`name` = 'Vasya') and (`test`.`folks`.`dob` = DATE'2000-01-01')) union select `p`.`id` AS `id`,`p`.`name` AS `name`,`p`.`dob` AS `dob`,`p`.`father` AS `father`,`p`.`mother` AS `mother` from `test`.`folks` `p` join `ancestors` `a` where ((`a`.`father` = `p`.`id`) or (`a`.`mother` = `p`.`id`)))select `ancestors`.`id` AS `id`,`ancestors`.`name` AS `name`,`ancestors`.`dob` AS `dob`,`ancestors`.`father` AS `father`,`ancestors`.`mother` AS `mother` from `ancestors`
+with recursive
+ancestor_ids (id)
+as
+(
+select father from folks where name = 'Vasya'
+  union
+select mother from folks where name = 'Vasya'
+  union
+select father from folks, ancestor_ids a  where folks.id = a.id
+union
+select mother from folks, ancestor_ids a  where folks.id = a.id
+),
+ancestors 
+as
+(
+select p.* from folks as p, ancestor_ids as a
+where p.id = a.id
+)
+select * from ancestors;
+id	name	dob	father	mother
+20	Dad	1970-02-02	10	9
+30	Mom	1975-03-03	8	7
+10	Grandpa Bill	1940-04-05	NULL	NULL
+8	Grandpa Ben	1940-10-21	NULL	NULL
+9	Grandma Ann	1941-10-15	NULL	NULL
+7	Grandma Sally	1943-08-23	NULL	6
+6	Grandgrandma Martha	1923-05-17	NULL	NULL
+with recursive
+ancestors
+as
+(
+select *
+from folks 
+where name = 'Vasya'
+  union all
+select p.*
+from folks as p, ancestors as fa
+where p.id = fa.father
+union all
+select p.*
+from folks as p, ancestors as ma
+where p.id = ma.mother
+)
+select * from ancestors;
+id	name	dob	father	mother
+100	Vasya	2000-01-01	20	30
+20	Dad	1970-02-02	10	9
+30	Mom	1975-03-03	8	7
+9	Grandma Ann	1941-10-15	NULL	NULL
+10	Grandpa Bill	1940-04-05	NULL	NULL
+8	Grandpa Ben	1940-10-21	NULL	NULL
+9	Grandma Ann	1941-10-15	NULL	NULL
+7	Grandma Sally	1943-08-23	NULL	6
+6	Grandgrandma Martha	1923-05-17	NULL	NULL
+with recursive
+ancestor_ids (id, generation)
+as
+(
+select father, 1 from folks where name = 'Vasya' and  father is not null
+union all
+select mother, 1 from folks where name = 'Vasya' and  mother is not null
+union all
+select father, fa.generation+1 from folks, ancestor_ids fa
+where folks.id = fa.id and (father not in (select id from ancestor_ids))
+union all
+select mother, ma.generation+1 from folks, ancestor_ids ma
+where folks.id = ma.id and (mother not in (select id from ancestor_ids))
+)
+select generation, name from ancestor_ids a, folks
+where a.id = folks.id;
+ERROR HY000: Restrictions imposed on recursive definitions are violated for table 'ancestor_ids'
+set standards_compliant_cte=0;
+set optimizer_switch='materialization=off,subquery_cache=off';
+with recursive
+ancestor_ids (id, generation)
+as
+(
+select father from folks where name = 'Vasya' and  father is not null
+union all
+select mother from folks where name = 'Vasya' and  mother is not null
+union all
+select father, fa.generation+1 from folks, ancestor_ids fa
+where folks.id = fa.id and (father not in (select id from ancestor_ids))
+union all
+select mother, ma.generation+1 from folks, ancestor_ids ma
+where folks.id = ma.id and (mother not in (select id from ancestor_ids))
+)
+select generation, name from ancestor_ids a, folks
+where a.id = folks.id;
+ERROR HY000: WITH column list and SELECT field list have different column counts
+with recursive
+ancestor_ids (id, generation)
+as
+(
+select father, 1 from folks where name = 'Vasya' and  father is not null
+union all
+select mother, 1 from folks where name = 'Vasya' and  mother is not null
+union all
+select father, fa.generation+1 from folks, ancestor_ids fa
+where folks.id = fa.id and father is not null and
+(father not in (select id from ancestor_ids))
+union all
+select mother, ma.generation+1 from folks, ancestor_ids ma
+where folks.id = ma.id and mother is not null and
+(mother not in (select id from ancestor_ids))
+)
+select generation, name from ancestor_ids a, folks
+where a.id = folks.id;
+generation	name
+1	Dad
+1	Mom
+2	Grandpa Bill
+2	Grandpa Ben
+2	Grandma Ann
+2	Grandma Sally
+3	Grandgrandma Martha
+set optimizer_switch=default;
+set standards_compliant_cte=1;
+with recursive
+coupled_ancestor_ids (id)
+as
+(
+select father from folks where name = 'Vasya' and father is not null
+union 
+select mother from folks where name = 'Vasya' and mother is not null
+union
+select n.father 
+from folks, coupled_ancestor_ids fa, coupled_ancestor_ids ma, folks n
+where folks.father = fa.id and folks.mother = ma.id and
+(fa.id = n.id or ma.id = n.id) and
+n.father is not null and n.mother is not null 
+union
+select n.mother 
+from folks, coupled_ancestor_ids fa, coupled_ancestor_ids ma, folks n
+where folks.father = fa.id and folks.mother = ma.id and 
+(fa.id = n.id or ma.id = n.id) and 
+n.father is not null and n.mother is not null 
+)
+select p.* from coupled_ancestor_ids a, folks p
+where a.id = p.id;
+ERROR HY000: Restrictions imposed on recursive definitions are violated for table 'coupled_ancestor_ids'
+set statement standards_compliant_cte=0 for
+with recursive
+coupled_ancestor_ids (id)
+as
+(
+select father from folks where name = 'Vasya' and father is not null
+union 
+select mother from folks where name = 'Vasya' and mother is not null
+union
+select n.father 
+from folks, coupled_ancestor_ids fa, coupled_ancestor_ids ma, folks n
+where folks.father = fa.id and folks.mother = ma.id and
+(fa.id = n.id or ma.id = n.id) and
+n.father is not null and n.mother is not null 
+union
+select n.mother 
+from folks, coupled_ancestor_ids fa, coupled_ancestor_ids ma, folks n
+where folks.father = fa.id and folks.mother = ma.id and 
+(fa.id = n.id or ma.id = n.id) and 
+n.father is not null and n.mother is not null 
+)
+select p.* from coupled_ancestor_ids a, folks p
+where a.id = p.id;
+id	name	dob	father	mother
+20	Dad	1970-02-02	10	9
+30	Mom	1975-03-03	8	7
+10	Grandpa Bill	1940-04-05	NULL	NULL
+8	Grandpa Ben	1940-10-21	NULL	NULL
+9	Grandma Ann	1941-10-15	NULL	NULL
+7	Grandma Sally	1943-08-23	NULL	6
+with recursive
+ancestor_ids (id)
+as
+(
+select father from folks where name = 'Vasya'
+  union
+select mother from folks where name = 'Vasya'
+  union
+select father from folks left join ancestor_ids a on folks.id = a.id
+union
+select mother from folks left join ancestor_ids a on folks.id = a.id
+),
+ancestors 
+as
+(
+select p.* from folks as p, ancestor_ids as a
+where p.id = a.id
+)
+select * from ancestors;
+ERROR HY000: Restrictions imposed on recursive definitions are violated for table 'ancestor_ids'
+set statement standards_compliant_cte=0 for
+with recursive
+ancestor_ids (id)
+as
+(
+select father from folks where name = 'Vasya'
+  union
+select mother from folks where name = 'Vasya'
+  union
+select father from folks left join ancestor_ids a on folks.id = a.id
+union
+select mother from folks left join ancestor_ids a on folks.id = a.id
+),
+ancestors 
+as
+(
+select p.* from folks as p, ancestor_ids as a
+where p.id = a.id
+)
+select * from ancestors;
+id	name	dob	father	mother
+20	Dad	1970-02-02	10	9
+30	Mom	1975-03-03	8	7
+10	Grandpa Bill	1940-04-05	NULL	NULL
+8	Grandpa Ben	1940-10-21	NULL	NULL
+25	Uncle Jim	1968-11-18	8	7
+9	Grandma Ann	1941-10-15	NULL	NULL
+7	Grandma Sally	1943-08-23	NULL	6
+6	Grandgrandma Martha	1923-05-17	NULL	NULL
+27	Auntie Melinda	1971-03-29	NULL	NULL
+with recursive
+ancestor_ids (id, generation)
+as
+(
+select father, 1 from folks where name = 'Vasya'
+  union
+select mother, 1 from folks where name = 'Vasya'
+  union
+select father, a.generation+1 from folks, ancestor_ids a
+where folks.id = a.id
+union
+select mother, a.generation+1 from folks, ancestor_ids a
+where folks.id = a.id
+),
+ancestors 
+as
+(
+select generation, name from folks as p, ancestor_ids as a
+where p.id = a.id
+)
+select * from ancestors;
+generation	name
+1	Dad
+1	Mom
+2	Grandpa Bill
+2	Grandpa Ben
+2	Grandma Ann
+2	Grandma Sally
+3	Grandgrandma Martha
+with recursive
+ancestor_ids (id, generation)
+as
+(
+select father, 1 from folks where name = 'Vasya'
+  union
+select mother, 1 from folks where name = 'Vasya'
+  union
+select max(father), max(a.generation)+1 from folks, ancestor_ids a
+where folks.id = a.id
+group by a.generation
+union
+select max(mother), max(a.generation)+1 from folks, ancestor_ids a
+where folks.id = a.id
+group by a.generation
+),
+ancestors 
+as
+(
+select generation, name from folks as p, ancestor_ids as a
+where p.id = a.id
+)
+select * from ancestors;
+ERROR HY000: Restrictions imposed on recursive definitions are violated for table 'ancestor_ids'
+set statement standards_compliant_cte=0 for
+with recursive
+ancestor_ids (id, generation)
+as
+(
+select father, 1 from folks where name = 'Vasya'
+  union
+select mother, 1 from folks where name = 'Vasya'
+  union
+select max(father), a.generation+1 from folks, ancestor_ids a
+where folks.id = a.id
+group by a.generation
+union
+select max(mother), a.generation+1 from folks, ancestor_ids a
+where folks.id = a.id
+group by a.generation
+),
+ancestors 
+as
+(
+select generation, name from folks as p, ancestor_ids as a
+where p.id = a.id
+)
+select * from ancestors;
+generation	name
+1	Dad
+1	Mom
+2	Grandpa Bill
+2	Grandma Ann
 drop table folks;
diff --git a/mysql-test/r/mysqld--help.result b/mysql-test/r/mysqld--help.result
index a35693eb93e..7a8b9dc3df4 100644
--- a/mysql-test/r/mysqld--help.result
+++ b/mysql-test/r/mysqld--help.result
@@ -1033,6 +1033,9 @@ The following options may be given as the first argument:
  NO_ENGINE_SUBSTITUTION, PAD_CHAR_TO_FULL_LENGTH
  --stack-trace       Print a symbolic stack trace on failure
  (Defaults to on; use --skip-stack-trace to disable.)
+ --standards-compliant-cte 
+ Allow only standards compiant CTE
+ (Defaults to on; use --skip-standards-compliant-cte to disable.)
  --stored-program-cache=# 
  The soft upper limit for number of cached stored routines
  for one connection.
@@ -1414,6 +1417,7 @@ slow-query-log FALSE
 sort-buffer-size 2097152
 sql-mode NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
 stack-trace TRUE
+standards-compliant-cte TRUE
 stored-program-cache 256
 strict-password-validation TRUE
 symbolic-links FALSE
diff --git a/mysql-test/t/cte_recursive.test b/mysql-test/t/cte_recursive.test
index 4982bf78adb..da4de155186 100644
--- a/mysql-test/t/cte_recursive.test
+++ b/mysql-test/t/cte_recursive.test
@@ -34,7 +34,8 @@ insert into folks values
 (9, 'Grandma Ann', '1941-10-15', null, null),
 (25, 'Uncle Jim', '1968-11-18', 8, 7),
 (98, 'Sister Amy', '2001-06-20', 20, 30),
-(8, 'Grandma Sally', '1943-08-23', 5, 6),
+(7, 'Grandma Sally', '1943-08-23', null, 6),
+(8, 'Grandpa Ben', '1940-10-21', null, null),
 (6, 'Grandgrandma Martha', '1923-05-17', null, null),
 (67, 'Cousin Eddie', '1992-02-28', 25, 27),
 (27, 'Auntie Melinda', '1971-03-29', null, null); 
@@ -320,5 +321,269 @@ as
 select * from ancestors;
 
 
+with recursive
+ancestor_ids (id)
+as
+(
+  select father from folks where name = 'Vasya'
+  union
+  select mother from folks where name = 'Vasya'
+  union
+  select father from folks, ancestor_ids a  where folks.id = a.id
+  union
+  select mother from folks, ancestor_ids a  where folks.id = a.id
+),
+ancestors 
+as
+(
+  select p.* from folks as p, ancestor_ids as a
+    where p.id = a.id
+)
+select * from ancestors;
+
+with recursive
+ancestors
+as
+(
+  select *
+    from folks 
+      where name = 'Vasya'
+  union all
+  select p.*
+    from folks as p, ancestors as fa
+      where p.id = fa.father
+  union all
+  select p.*
+    from folks as p, ancestors as ma
+      where p.id = ma.mother
+)
+select * from ancestors;
+
+
+
+
+--ERROR ER_NOT_STANDARDS_COMPLIANT_RECURSIVE
+with recursive
+ancestor_ids (id, generation)
+as
+(
+  select father, 1 from folks where name = 'Vasya' and  father is not null
+  union all
+  select mother, 1 from folks where name = 'Vasya' and  mother is not null
+  union all
+  select father, fa.generation+1 from folks, ancestor_ids fa
+    where folks.id = fa.id and (father not in (select id from ancestor_ids))
+  union all
+  select mother, ma.generation+1 from folks, ancestor_ids ma
+    where folks.id = ma.id and (mother not in (select id from ancestor_ids))
+)
+select generation, name from ancestor_ids a, folks
+  where a.id = folks.id;
+
+set standards_compliant_cte=0;
+set optimizer_switch='materialization=off,subquery_cache=off';
+
+--ERROR ER_WITH_COL_WRONG_LIST
+with recursive
+ancestor_ids (id, generation)
+as
+(
+  select father from folks where name = 'Vasya' and  father is not null
+  union all
+  select mother from folks where name = 'Vasya' and  mother is not null
+  union all
+  select father, fa.generation+1 from folks, ancestor_ids fa
+    where folks.id = fa.id and (father not in (select id from ancestor_ids))
+  union all
+  select mother, ma.generation+1 from folks, ancestor_ids ma
+    where folks.id = ma.id and (mother not in (select id from ancestor_ids))
+)
+select generation, name from ancestor_ids a, folks
+  where a.id = folks.id;
+
+with recursive
+ancestor_ids (id, generation)
+as
+(
+  select father, 1 from folks where name = 'Vasya' and  father is not null
+  union all
+  select mother, 1 from folks where name = 'Vasya' and  mother is not null
+  union all
+  select father, fa.generation+1 from folks, ancestor_ids fa
+    where folks.id = fa.id and father is not null and
+          (father not in (select id from ancestor_ids))
+  union all
+  select mother, ma.generation+1 from folks, ancestor_ids ma
+    where folks.id = ma.id and mother is not null and
+          (mother not in (select id from ancestor_ids))
+)
+select generation, name from ancestor_ids a, folks
+  where a.id = folks.id;
+
+set optimizer_switch=default;
+set standards_compliant_cte=1;
+
+--ERROR ER_NOT_STANDARDS_COMPLIANT_RECURSIVE
+with recursive
+coupled_ancestor_ids (id)
+as
+(
+  select father from folks where name = 'Vasya' and father is not null
+  union 
+  select mother from folks where name = 'Vasya' and mother is not null
+  union
+  select n.father 
+    from folks, coupled_ancestor_ids fa, coupled_ancestor_ids ma, folks n
+      where folks.father = fa.id and folks.mother = ma.id and
+            (fa.id = n.id or ma.id = n.id) and
+            n.father is not null and n.mother is not null 
+  union
+  select n.mother 
+    from folks, coupled_ancestor_ids fa, coupled_ancestor_ids ma, folks n
+      where folks.father = fa.id and folks.mother = ma.id and 
+            (fa.id = n.id or ma.id = n.id) and 
+            n.father is not null and n.mother is not null 
+)
+select p.* from coupled_ancestor_ids a, folks p
+  where a.id = p.id;
+
+set statement standards_compliant_cte=0 for
+with recursive
+coupled_ancestor_ids (id)
+as
+(
+  select father from folks where name = 'Vasya' and father is not null
+  union 
+  select mother from folks where name = 'Vasya' and mother is not null
+  union
+  select n.father 
+    from folks, coupled_ancestor_ids fa, coupled_ancestor_ids ma, folks n
+      where folks.father = fa.id and folks.mother = ma.id and
+            (fa.id = n.id or ma.id = n.id) and
+            n.father is not null and n.mother is not null 
+  union
+  select n.mother 
+    from folks, coupled_ancestor_ids fa, coupled_ancestor_ids ma, folks n
+      where folks.father = fa.id and folks.mother = ma.id and 
+            (fa.id = n.id or ma.id = n.id) and 
+            n.father is not null and n.mother is not null 
+)
+select p.* from coupled_ancestor_ids a, folks p
+  where a.id = p.id;
+
+--ERROR ER_NOT_STANDARDS_COMPLIANT_RECURSIVE
+with recursive
+ancestor_ids (id)
+as
+(
+  select father from folks where name = 'Vasya'
+  union
+  select mother from folks where name = 'Vasya'
+  union
+  select father from folks left join ancestor_ids a on folks.id = a.id
+  union
+  select mother from folks left join ancestor_ids a on folks.id = a.id
+),
+ancestors 
+as
+(
+  select p.* from folks as p, ancestor_ids as a
+    where p.id = a.id
+)
+select * from ancestors;
+
+set statement standards_compliant_cte=0 for
+with recursive
+ancestor_ids (id)
+as
+(
+  select father from folks where name = 'Vasya'
+  union
+  select mother from folks where name = 'Vasya'
+  union
+  select father from folks left join ancestor_ids a on folks.id = a.id
+  union
+  select mother from folks left join ancestor_ids a on folks.id = a.id
+),
+ancestors 
+as
+(
+  select p.* from folks as p, ancestor_ids as a
+    where p.id = a.id
+)
+select * from ancestors;
+
+with recursive
+ancestor_ids (id, generation)
+as
+(
+  select father, 1 from folks where name = 'Vasya'
+  union
+  select mother, 1 from folks where name = 'Vasya'
+  union
+  select father, a.generation+1 from folks, ancestor_ids a
+    where folks.id = a.id
+  union
+  select mother, a.generation+1 from folks, ancestor_ids a
+    where folks.id = a.id
+),
+ancestors 
+as
+(
+  select generation, name from folks as p, ancestor_ids as a
+    where p.id = a.id
+)
+select * from ancestors;
+
+--ERROR ER_NOT_STANDARDS_COMPLIANT_RECURSIVE
+with recursive
+ancestor_ids (id, generation)
+as
+(
+  select father, 1 from folks where name = 'Vasya'
+  union
+  select mother, 1 from folks where name = 'Vasya'
+  union
+  select max(father), max(a.generation)+1 from folks, ancestor_ids a
+    where folks.id = a.id
+      group by a.generation
+  union
+  select max(mother), max(a.generation)+1 from folks, ancestor_ids a
+    where folks.id = a.id
+      group by a.generation
+),
+ancestors 
+as
+(
+  select generation, name from folks as p, ancestor_ids as a
+    where p.id = a.id
+)
+select * from ancestors;
+
+set statement standards_compliant_cte=0 for
+with recursive
+ancestor_ids (id, generation)
+as
+(
+  select father, 1 from folks where name = 'Vasya'
+  union
+  select mother, 1 from folks where name = 'Vasya'
+  union
+  select max(father), a.generation+1 from folks, ancestor_ids a
+    where folks.id = a.id
+      group by a.generation
+  union
+  select max(mother), a.generation+1 from folks, ancestor_ids a
+    where folks.id = a.id
+      group by a.generation
+),
+ancestors 
+as
+(
+  select generation, name from folks as p, ancestor_ids as a
+    where p.id = a.id
+)
+select * from ancestors;
+
 drop table folks;
 
diff --git a/sql/share/errmsg-utf8.txt b/sql/share/errmsg-utf8.txt
index 38b05ca9dce..00228ee1062 100644
--- a/sql/share/errmsg-utf8.txt
+++ b/sql/share/errmsg-utf8.txt
@@ -7156,6 +7156,8 @@ ER_RECURSIVE_WITHOUT_ANCHORS
         eng "No anchors for recursive WITH element '%s'"
 ER_REF_TO_RECURSIVE_WITH_TABLE_IN_DERIVED
         eng "Reference to recursive WITH table '%s' in materiazed derived"
+ER_NOT_STANDARDS_COMPLIANT_RECURSIVE
+        eng "Restrictions imposed on recursive definitions are violated for table '%s'"
 #
 # Internal errors, not used
 #
diff --git a/sql/sql_class.h b/sql/sql_class.h
index 0100a9807f5..7e995c04b33 100644
--- a/sql/sql_class.h
+++ b/sql/sql_class.h
@@ -628,6 +628,7 @@ typedef struct system_variables
   my_bool old_alter_table;
   my_bool old_passwords;
   my_bool big_tables;
+  my_bool only_standards_compliant_cte;
   my_bool query_cache_strip_comments;
   my_bool sql_log_slow;
   my_bool sql_log_bin;
diff --git a/sql/sql_cte.cc b/sql/sql_cte.cc
index ffc54f50af1..77d2c7d24d3 100644
--- a/sql/sql_cte.cc
+++ b/sql/sql_cte.cc
@@ -173,8 +173,10 @@ bool With_element::check_dependencies_in_spec(THD *thd)
 }
 
 
-void With_element::check_dependencies_in_select(st_select_lex *sl, table_map &dep_map)
+void With_element::check_dependencies_in_select(st_select_lex *sl,
+                                                table_map &dep_map)
 {
+  bool is_sq_select= sl->master_unit()->item != NULL;
   for (TABLE_LIST *tbl= sl->table_list.first; tbl; tbl= tbl->next_local)
   {
     tbl->with_internal_reference_map= 0;
@@ -186,6 +188,8 @@ void With_element::check_dependencies_in_select(st_select_lex *sl, table_map &de
     {
       dep_map|= tbl->with->get_elem_map();
       tbl->with_internal_reference_map= get_elem_map();
+      if (is_sq_select)
+        sq_dep_map|= tbl->with->get_elem_map();
     }
   }
   st_select_lex_unit *inner_unit= sl->first_inner_unit();
@@ -730,7 +734,7 @@ bool TABLE_LIST::is_with_table_recursive_reference()
 
 
 
-bool st_select_lex::check_unrestricted_recursive()
+bool st_select_lex::check_unrestricted_recursive(bool only_standards_compliant)
 {
   With_element *with_elem= get_with_element();
   if (!with_elem ||!with_elem->is_recursive)
@@ -742,8 +746,16 @@ bool st_select_lex::check_unrestricted_recursive()
 					      encountered))
     return true;
   with_elem->owner->unrestricted|= unrestricted;
-  if (with_sum_func)
+  if (with_sum_func ||
+      (with_elem->sq_dep_map & with_elem->mutually_recursive))
     with_elem->owner->unrestricted|= with_elem->mutually_recursive;
+  if (only_standards_compliant && with_elem->is_unrestricted())
+  {
+    my_error(ER_NOT_STANDARDS_COMPLIANT_RECURSIVE,
+	     MYF(0), with_elem->query_name->str);
+    return true;
+  }
+
   return false;
 }
 
@@ -756,24 +768,27 @@ bool With_element::check_unrestricted_recursive(st_select_lex *sel,
   TABLE_LIST *tbl;
   while ((tbl= ti++))
   {
-    if (tbl->get_unit() && !tbl->is_with_table())
-    {
-      st_select_lex_unit *unit= tbl->get_unit();
-      if (tbl->is_materialized_derived())
+    st_select_lex_unit *unit= tbl->get_unit();
+    if (unit)
+    { 
+      if(!tbl->is_with_table())
       {
-        table_map dep_map;
-        check_dependencies_in_unit(unit, dep_map);
-        if (dep_map & get_elem_map())
+        if (tbl->is_materialized_derived())
         {
-	  my_error(ER_REF_TO_RECURSIVE_WITH_TABLE_IN_DERIVED,
-	  	   MYF(0), query_name->str);
-	  return true;
+          table_map dep_map;
+          check_dependencies_in_unit(unit, dep_map);
+          if (dep_map & get_elem_map())
+          {
+	    my_error(ER_REF_TO_RECURSIVE_WITH_TABLE_IN_DERIVED,
+	  	     MYF(0), query_name->str);
+	    return true;
+          }
         }
+        if (check_unrestricted_recursive(unit->first_select(), 
+					 unrestricted, 
+				         encountered))
+          return true;
       }
-      if (check_unrestricted_recursive(unit->first_select(), 
-					     unrestricted, 
-				             encountered))
-        return true;
       if (!(tbl->is_recursive_with_table() && unit->with_element->owner == owner))
         continue;
       With_element *with_elem= unit->with_element;
@@ -792,8 +807,8 @@ bool With_element::check_unrestricted_recursive(st_select_lex *sel,
     if (encountered & with_elem->get_elem_map())
     {
       uint cnt= 0;
-      table_map mutually_recursive= with_elem->mutually_recursive;
-      for (table_map map= mutually_recursive >> with_elem->number;
+      table_map encountered_mr= encountered & with_elem->mutually_recursive;
+      for (table_map map= encountered_mr >> with_elem->number;
            map != 0;
            map>>= 1) 
       {
diff --git a/sql/sql_cte.h b/sql/sql_cte.h
index 0312fcd0643..1c32f16258c 100644
--- a/sql/sql_cte.h
+++ b/sql/sql_cte.h
@@ -35,6 +35,7 @@ private:
     directly or indirectly from the i-th with element. 
   */
   table_map derived_dep_map; 
+  table_map sq_dep_map;
   table_map work_dep_map;  // dependency map used for work
   /* Dependency map of with elements mutually recursive with this with element */
   table_map mutually_recursive; 
@@ -90,7 +91,7 @@ public:
                List <LEX_STRING> list,
                st_select_lex_unit *unit)
     : next_elem(NULL), base_dep_map(0), derived_dep_map(0),
-      work_dep_map(0), mutually_recursive(0), 
+      sq_dep_map(0), work_dep_map(0), mutually_recursive(0), 
       references(0), table(NULL),
       query_name(name), column_list(list), spec(unit),
       is_recursive(false), with_anchor(false),
@@ -151,7 +152,8 @@ public:
 
   friend class With_clause;
   friend
-  bool st_select_lex::check_unrestricted_recursive();
+  bool 
+  st_select_lex::check_unrestricted_recursive(bool only_standard_compliant);
   friend
   bool TABLE_LIST::is_with_table_recursive_reference();
 };
@@ -241,9 +243,11 @@ public:
   friend class With_element;
 
   friend
-  bool check_dependencies_in_with_clauses(THD *thd, With_clause *with_clauses_list);
+  bool
+  check_dependencies_in_with_clauses(THD *thd, With_clause *with_clauses_list);
   friend
-  bool st_select_lex::check_unrestricted_recursive();
+  bool
+  st_select_lex::check_unrestricted_recursive(bool only_standard_compliant);
 
 };
 
diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc
index 95a7ee91435..0db94421aa5 100644
--- a/sql/sql_derived.cc
+++ b/sql/sql_derived.cc
@@ -771,7 +771,7 @@ exit:
   */
   if (res)
   {
-    if (derived->table)
+    if (derived->table && !derived->is_with_table_recursive_reference())
       free_tmp_table(thd, derived->table);
     delete derived->derived_result;
   }
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index 42058319fc9..27340bbec89 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -3189,6 +3189,8 @@ void st_select_lex_unit::set_limit(st_select_lex *sl)
 
 bool st_select_lex_unit::union_needs_tmp_table()
 {
+  if (with_element && with_element->is_recursive)
+    return true;
   return union_distinct != NULL ||
     global_parameters()->order_list.elements != 0 ||
     thd->lex->sql_command == SQLCOM_INSERT_SELECT ||
@@ -4236,6 +4238,7 @@ void st_select_lex::update_correlated_cache()
 
   while ((tl= ti++))
   {
+    //    is_correlated|= tl->is_with_table_recursive_reference();
     if (tl->on_expr)
       is_correlated|= MY_TEST(tl->on_expr->used_tables() & OUTER_REF_TABLE_BIT);
     for (TABLE_LIST *embedding= tl->embedding ; embedding ;
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index 09463635b94..b17e19276da 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -1108,7 +1108,7 @@ public:
     return master_unit()->with_element;
   }
   With_element *find_table_def_in_with_clauses(TABLE_LIST *table);
-  bool check_unrestricted_recursive(); 
+  bool check_unrestricted_recursive(bool only_standards_compliant); 
 
 
   List<Window_spec> window_specs;
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 71b672b6131..6792d7f5e2c 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -816,8 +816,6 @@ JOIN::prepare(TABLE_LIST *tables_init,
                           &hidden_group_fields,
                           &select_lex->select_n_reserved))
     DBUG_RETURN(-1);
-  if (select_lex->check_unrestricted_recursive())
-    DBUG_RETURN(-1);
   /* Resolve the ORDER BY that was skipped, then remove it. */
   if (skip_order_by && select_lex !=
                        select_lex->master_unit()->global_parameters())
@@ -861,6 +859,12 @@ JOIN::prepare(TABLE_LIST *tables_init,
   With_clause *with_clause=select_lex->get_with_clause();
   if (with_clause && with_clause->prepare_unreferenced_elements(thd))
     DBUG_RETURN(1);
+
+  With_element *with_elem= select_lex->get_with_element();
+  if (with_elem &&
+      select_lex->check_unrestricted_recursive(
+                      thd->variables.only_standards_compliant_cte))
+    DBUG_RETURN(-1);
   
   int res= check_and_do_in_subquery_rewrites(this);
 
diff --git a/sql/sql_union.cc b/sql/sql_union.cc
index 9308dc2a841..c43fdf30a64 100644
--- a/sql/sql_union.cc
+++ b/sql/sql_union.cc
@@ -266,14 +266,18 @@ void select_union::cleanup()
 
 void select_union_recursive::cleanup()
 {
-  select_union::cleanup();
-  free_tmp_table(thd, table);
+  if (table)
+  {
+    select_union::cleanup();
+    free_tmp_table(thd, table);
+  }
 
-  incr_table->file->extra(HA_EXTRA_RESET_STATE);
-  incr_table->file->ha_delete_all_rows();
-  //free_io_cache(incr_table);
-  //filesort_free_buffers(incr_table,0);
-  free_tmp_table(thd, incr_table);
+  if (incr_table)
+  {
+    incr_table->file->extra(HA_EXTRA_RESET_STATE);
+    incr_table->file->ha_delete_all_rows();
+    free_tmp_table(thd, incr_table);
+  }
 
   List_iterator<TABLE> it(rec_tables);
   TABLE *tab;
@@ -281,8 +285,6 @@ void select_union_recursive::cleanup()
   {
     tab->file->extra(HA_EXTRA_RESET_STATE);
     tab->file->ha_delete_all_rows();
-    //free_io_cache(tab);
-    //filesort_free_buffers(tab,0);
     free_tmp_table(thd, tab);
   }
 }
diff --git a/sql/sys_vars.cc b/sql/sys_vars.cc
index 4bf202813f3..c921fffc004 100644
--- a/sql/sys_vars.cc
+++ b/sql/sys_vars.cc
@@ -3156,6 +3156,12 @@ static Sys_var_charptr Sys_ssl_crlpath(
        READ_ONLY GLOBAL_VAR(opt_ssl_crlpath), SSL_OPT(OPT_SSL_CRLPATH),
        IN_FS_CHARSET, DEFAULT(0));
 
+static Sys_var_mybool Sys_standards_compliant_cte(
+       "standards_compliant_cte",
+       "Allow only standards compiant CTE",
+       SESSION_VAR(only_standards_compliant_cte), CMD_LINE(OPT_ARG),
+       DEFAULT(TRUE));
+
 
 // why ENUM and not BOOL ?
 static const char *updatable_views_with_limit_names[]= {"NO", "YES", 0};