Create 'main' test directory and move 't' and 'r' there

1 files changed, 2310 insertions, 0 deletions

diff --git a/mysql-test/main/cte_recursive.test b/mysql-test/main/cte_recursive.test
new file mode 100644
index 00000000000..7ed55a1daaa
--- /dev/null
+++ b/mysql-test/main/cte_recursive.test
@@ -0,0 +1,2310 @@
+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');
+
+--ERROR ER_UNACCEPTABLE_MUTUAL_RECURSION
+with recursive
+t as 
+(
+  select * from t1 where t1.b >= 'c'
+  union
+  select * from r
+),
+r as
+(
+  select * from t
+  union
+  select t1.* from t1,r where r.a+1 = t1.a
+)
+select * from r;
+
+
+--ERROR ER_UNACCEPTABLE_MUTUAL_RECURSION
+with recursive
+a1(a,b) as
+(select * from t1 where t1.a>3
+union
+select * from b1 where b1.a >3
+union
+select * from c1 where c1.a>3),
+b1(a,b) as
+(select * from a1 where a1.b > 'ccc'
+union
+select * from c1 where c1.b > 'ddd'),
+c1(a,b) as
+(select * from a1 where a1.a<6 and a1.b< 'zz'
+union
+select * from b1 where b1.b > 'auu')
+select * from c1; 
+
+drop table t1;
+
+
+--echo # WITH RECURSIVE vs just WITH
+
+create table t1 (a int);
+insert into t1 values
+  (0), (1), (2), (3), (4);
+create table t2 (a int);
+insert into t2 values
+  (1), (2), (3), (4), (5);
+
+
+--echo # just WITH : s refers to t defined after s
+--ERROR ER_NO_SUCH_TABLE
+with 
+ s(a) as (select t.a + 10 from t),
+ t(a) as (select t1.a from t1)
+select * from s;
+
+--echo # WITH RECURSIVE: s refers to t defined after s
+with recursive 
+ s(a) as (select t.a + 10 from t),
+ t(a) as (select t1.a from t1)
+select * from s;
+
+--echo # just WITH : defined t1 is non-recursive and uses base tables t1,t2
+with
+t1 as
+(
+select a  from t2 where t2.a=3
+union
+select t2.a from t1,t2 where t1.a+1=t2.a
+)
+select * from t1;
+
+explain
+with
+t1 as
+(
+select a  from t2 where t2.a=3
+union
+select t2.a from t1,t2 where t1.a+1=t2.a
+)
+select * from t1;
+
+
+--echo #WITH RECURSIVE : defined t1 is recursive and uses only base table t2
+with recursive
+t1 as
+(
+select a  from t2 where t2.a=3
+union
+select t2.a from t1,t2 where t1.a+1=t2.a
+)
+select * from t1;
+
+explain
+with recursive
+t1 as
+(
+select a  from t2 where t2.a=3
+union
+select t2.a from t1,t2 where t1.a+1=t2.a
+)
+select * from t1;
+    
+--echo # just WITH : types of t1 columns are determined by all parts of union 
+
+create view v1 as
+with
+t1 as
+(
+select a  from t2 where t2.a=3
+union
+select t2.a+1 from t1,t2 where t1.a=t2.a
+)
+select * from t1;
+
+show columns from v1;
+
+
+--echo # WITH RECURSIVE : types of t1 columns are determined by anchor parts 
+
+create view v2 as
+with recursive
+t1 as
+(
+select a  from t2 where t2.a=3
+union
+select t2.a+1 from t1,t2 where t1.a=t2.a
+)
+select * from t1;
+
+show columns from v2;
+
+drop view v1,v2;
+
+drop table t1,t2;
+
+
+create table  folks(id int, name char(32), dob date, father int, mother int);
+
+insert into folks values
+(100, 'Me', '2000-01-01', 20, 30),
+(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),
+(25, 'Uncle Jim', '1968-11-18', 8, 7),
+(98, 'Sister Amy', '2001-06-20', 20, 30),
+(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); 
+
+--echo # simple recursion with one anchor and one recursive select
+--echo # the anchor is the first select in the specification 
+with recursive 
+ancestors
+as
+(
+  select *
+    from folks
+      where name = 'Me' and dob = '2000-01-01'
+  union 
+  select p.id, p.name, p.dob, p.father, p.mother
+    from folks as p, ancestors AS a
+      where p.id = a.father or p.id = a.mother
+)
+select * from ancestors;
+
+--echo # simple recursion with one anchor and one recursive select
+--echo # the anchor is the last select in the specification 
+with recursive 
+ancestors
+as
+(
+  select p.*
+    from folks as p, ancestors AS a
+      where p.id = a.father or p.id = a.mother
+  union
+  select *
+    from folks
+      where name = 'Me' and dob = '2000-01-01'
+)
+select * from ancestors;
+
+--echo # simple recursion with one anchor and one recursive select
+--echo # the anchor is the first select in the specification 
+with recursive
+ancestors
+as
+(
+  select *
+    from folks
+      where name = 'Cousin Eddie'
+  union 
+  select p.*
+    from folks as p, ancestors as a
+      where p.id = a.father or p.id = a.mother
+)
+select * from ancestors;
+
+--echo # simple recursion with or in anchor and or in recursive part 
+with recursive
+ancestors
+as
+(
+  select *
+    from  folks
+      where name = 'Me' or name='Sister Amy'
+   union 
+   select  p.*
+     from folks as p, ancestors as a
+       where p.id = a.father or p.id = a.mother
+)
+select * from ancestors;
+
+--echo # two recursive definition, one uses another
+with recursive 
+prev_gen
+as
+(
+  select folks.*
+    from folks, prev_gen
+      where folks.id=prev_gen.father or folks.id=prev_gen.mother
+  union
+  select * 
+    from folks
+      where name='Me'
+),
+ancestors
+as
+(
+  select *
+    from folks
+      where name='Me'
+  union 
+  select *
+    from ancestors
+  union
+    select *
+     from prev_gen
+)
+select ancestors.name, ancestors.dob from ancestors;
+
+--echo # recursive definition with two attached non-recursive
+with recursive 
+ancestors(id,name,dob)
+as
+(
+  with 
+  father(child_id,id,name,dob)
+  as
+  (
+    select folks.id, f.id, f.name, f.dob
+      from folks, folks f
+        where folks.father=f.id 
+        
+  ),
+  mother(child_id,id,name,dob)
+  as
+  (
+    select folks.id, m.id, m.name, m.dob
+      from folks, folks m
+        where folks.mother=m.id 
+        
+  )  
+  select folks.id, folks.name, folks.dob
+    from folks
+      where name='Me'
+  union 
+  select f.id, f.name, f.dob
+    from ancestors a, father f
+      where f.child_id=a.id
+  union 
+  select m.id, m.name, m.dob
+    from ancestors a, mother m
+      where m.child_id=a.id
+  
+)
+select ancestors.name, ancestors.dob from ancestors;
+
+--echo # simple recursion with one anchor and one recursive select
+--echo # the anchor is the first select in the specification 
+with recursive
+descendants
+as
+(
+  select *
+    from folks
+      where name = 'Grandpa Bill'
+  union 
+  select folks.*
+    from folks, descendants as d
+      where d.id=folks.father or d.id=folks.mother
+)
+select * from descendants;
+
+--echo # simple recursion with one anchor and one recursive select
+--echo # the anchor is the first select in the specification 
+with recursive
+descendants
+as
+(
+  select *
+    from folks
+      where name = 'Grandma Sally'
+  union 
+  select folks.*
+    from folks, descendants as d
+      where d.id=folks.father or d.id=folks.mother
+)
+select * from descendants;
+
+
+--echo # simple recursive table used three times in the main query
+with recursive
+ancestors
+as
+(
+  select *
+    from folks 
+      where name = 'Me' and dob = '2000-01-01'
+  union
+  select p.*   
+    from folks as p, ancestors AS a   
+      where p.id = a.father OR p.id = a.mother
+)
+select *
+  from ancestors t1, ancestors t2 
+    where exists (select * from ancestors a 
+                  where a.father=t1.id AND a.mother=t2.id);
+
+
+--echo # simple recursive table used three times in the main query
+with 
+ancestor_couples(husband, h_dob, wife, w_dob)
+as
+(
+with recursive 
+ancestors
+as
+(
+  select *
+    from folks 
+       where name = 'Me'
+  union
+  select p.*   
+    from folks as p, ancestors AS a   
+      where p.id = a.father OR p.id = a.mother
+)
+select t1.name, t1.dob, t2.name, t2.dob
+  from ancestors t1, ancestors t2 
+    where exists (select * from ancestors a 
+                    where a.father=t1.id AND a.mother=t2.id)
+)
+select * from ancestor_couples;
+
+
+--echo # simple recursion with two selects in recursive part
+with recursive
+ancestors
+as
+(
+  select *
+    from folks 
+      where name = 'Me'
+  union 
+  select p.*
+    from folks as p, ancestors as fa
+      where p.id = fa.father
+  union
+  select p.*
+    from folks as p, ancestors as ma
+      where p.id = ma.mother
+)
+select * from ancestors;
+
+
+--echo # mutual recursion with renaming
+with recursive
+ancestor_couples(h_id, h_name, h_dob, h_father, h_mother,
+                 w_id, w_name, w_dob, w_father, w_mother)
+as
+(
+  select h.*, w.*
+    from folks h, folks w,  coupled_ancestors a
+      where a.father = h.id AND a.mother = w.id
+  union
+  select h.*, w.*
+    from folks v, folks h, folks w
+      where v.name = 'Me' and
+            (v.father = h.id AND v.mother= w.id)
+),
+coupled_ancestors (id, name, dob, father, mother)
+as
+(
+  select h_id, h_name, h_dob, h_father, h_mother
+    from ancestor_couples
+  union
+  select w_id, w_name, w_dob, w_father, w_mother
+    from ancestor_couples
+)
+select h_name, h_dob, w_name, w_dob
+  from ancestor_couples;
+
+
+--echo # mutual recursion with union all
+with recursive
+ancestor_couples(h_id, h_name, h_dob, h_father, h_mother,
+                 w_id, w_name, w_dob, w_father, w_mother)
+as
+(
+  select h.*, w.*
+    from folks h, folks w,  coupled_ancestors a
+      where a.father = h.id AND a.mother = w.id
+  union 
+  select h.*, w.*
+    from folks v, folks h, folks w
+      where v.name = 'Me' and
+            (v.father = h.id AND v.mother= w.id)
+),
+coupled_ancestors (id, name, dob, father, mother)
+as
+(
+  select h_id, h_name, h_dob, h_father, h_mother
+    from ancestor_couples
+  union all
+  select w_id, w_name, w_dob, w_father, w_mother
+    from ancestor_couples
+)
+select h_name, h_dob, w_name, w_dob
+  from ancestor_couples;
+
+
+--echo # mutual recursion with renaming
+with recursive
+ancestor_couples(h_id, h_name, h_dob, h_father, h_mother,
+                 w_id, w_name, w_dob, w_father, w_mother)
+as
+(
+  select h.*, w.*
+    from folks h, folks w,  coupled_ancestors a
+      where a.father = h.id AND a.mother = w.id
+  union
+  select h.*, w.*
+    from folks v, folks h, folks w
+      where v.name = 'Me' and
+            (v.father = h.id AND v.mother= w.id)
+),
+coupled_ancestors (id, name, dob, father, mother)
+as
+(
+  select h_id, h_name, h_dob, h_father, h_mother
+    from ancestor_couples
+  union
+  select w_id, w_name, w_dob, w_father, w_mother
+    from ancestor_couples
+)
+select h_name, h_dob, w_name, w_dob
+  from ancestor_couples;
+
+
+--echo # mutual recursion with union all
+with recursive
+ancestor_couples(h_id, h_name, h_dob, h_father, h_mother,
+                 w_id, w_name, w_dob, w_father, w_mother)
+as
+(
+  select h.*, w.*
+    from folks h, folks w,  coupled_ancestors a
+      where a.father = h.id AND a.mother = w.id
+),
+coupled_ancestors (id, name, dob, father, mother)
+as
+(
+  select * 
+    from folks 
+      where name = 'Me'
+  union all
+  select h_id, h_name, h_dob, h_father, h_mother
+    from ancestor_couples
+  union all
+  select w_id, w_name, w_dob, w_father, w_mother
+    from ancestor_couples
+)
+select h_name, h_dob, w_name, w_dob
+  from ancestor_couples;
+
+--echo # mutual recursion with one select in the first definition
+with recursive
+ancestor_couple_ids(h_id, w_id)
+as
+(
+  select a.father, a.mother
+    from coupled_ancestors a
+      where a.father is not null and a.mother is not null
+),
+coupled_ancestors (id, name, dob, father, mother)
+as
+(
+  select * 
+    from folks 
+      where name = 'Me'
+  union all
+  select p.*
+    from folks p, ancestor_couple_ids fa
+      where p.id = fa.h_id
+  union all
+  select p.*
+    from folks p, ancestor_couple_ids ma
+      where p.id = ma.w_id
+)
+select *
+  from ancestor_couple_ids;
+
+
+--echo # join of a mutually recursive table with base tables
+with recursive
+ancestor_couple_ids(h_id, w_id)
+as
+(
+  select a.father, a.mother
+    from coupled_ancestors a
+      where a.father is not null and a.mother is not null
+),
+coupled_ancestors (id, name, dob, father, mother)
+as
+(
+  select * 
+    from folks 
+      where name = 'Me'
+  union all
+  select p.*
+    from folks p, ancestor_couple_ids fa
+      where p.id = fa.h_id
+  union all
+  select p.*
+    from folks p, ancestor_couple_ids ma
+      where p.id = ma.w_id
+)
+select h.name, h.dob, w.name, w.dob
+  from ancestor_couple_ids c, folks h, folks w
+    where c.h_id = h.id and c.w_id= w.id;
+
+
+--echo # join of two mutually recursive tables
+with recursive
+ancestor_couple_ids(h_id, w_id)
+as
+(
+  select a.father, a.mother
+    from coupled_ancestors a
+      where a.father is not null and a.mother is not null
+),
+coupled_ancestors (id, name, dob, father, mother)
+as
+(
+  select * 
+    from folks 
+      where name = 'Me'
+  union all
+  select p.*
+    from folks p, ancestor_couple_ids fa
+      where p.id = fa.h_id
+  union all
+  select p.*
+    from folks p, ancestor_couple_ids ma
+      where p.id = ma.w_id
+)
+select h.name, h.dob, w.name, w.dob
+  from ancestor_couple_ids c, coupled_ancestors h, coupled_ancestors w
+    where c.h_id = h.id and c.w_id= w.id;
+
+explain extended
+with recursive
+ancestor_couple_ids(h_id, w_id)
+as
+(
+  select a.father, a.mother
+    from coupled_ancestors a
+      where a.father is not null and a.mother is not null
+),
+coupled_ancestors (id, name, dob, father, mother)
+as
+(
+  select * 
+    from folks 
+      where name = 'Me'
+  union all
+  select p.*
+    from folks p, ancestor_couple_ids fa
+      where p.id = fa.h_id
+  union all
+  select p.*
+    from folks p, ancestor_couple_ids ma
+      where p.id = ma.w_id
+)
+select h.name, h.dob, w.name, w.dob
+  from ancestor_couple_ids c, coupled_ancestors h, coupled_ancestors w
+    where c.h_id = h.id and c.w_id= w.id;
+
+
+--echo # simple mutual recursion
+with recursive
+ancestor_couple_ids(h_id, w_id)
+as
+(
+  select a.father, a.mother
+    from coupled_ancestors a
+),
+coupled_ancestors (id, name, dob, father, mother)
+as
+(
+  select * 
+    from folks 
+      where name = 'Me'
+  union all
+  select p.*
+    from folks p, ancestor_couple_ids fa
+      where p.id = fa.h_id
+  union all
+  select p.*
+    from folks p, ancestor_couple_ids ma
+      where p.id = ma.w_id
+)
+select *
+  from ancestor_couple_ids;
+
+
+--echo # join of two mutually recursive tables
+with recursive
+ancestor_couple_ids(h_id, w_id)
+as
+(
+  select a.father, a.mother
+    from coupled_ancestors a
+),
+coupled_ancestors (id, name, dob, father, mother)
+as
+(
+  select * 
+    from folks 
+      where name = 'Me'
+  union all
+  select p.*
+    from folks p, ancestor_couple_ids fa
+      where p.id = fa.h_id
+  union all
+  select p.*
+    from folks p, ancestor_couple_ids ma
+      where p.id = ma.w_id
+)
+select h.name, h.dob, w.name, w.dob
+  from ancestor_couple_ids c, coupled_ancestors h, coupled_ancestors w
+    where c.h_id = h.id and c.w_id= w.id;
+
+
+--echo # execution of prepared query using a recursive table
+prepare stmt1 from "
+with recursive 
+ancestors
+as
+(
+  select *
+    from folks
+      where name = 'Me' and dob = '2000-01-01'
+  union 
+  select p.id, p.name, p.dob, p.father, p.mother
+    from folks as p, ancestors AS a
+      where p.id = a.father or p.id = a.mother
+)
+select * from ancestors;
+";
+
+execute stmt1;
+execute stmt1;
+
+deallocate prepare stmt1;
+
+
+--echo # view using a recursive table
+create view v1 as
+with recursive 
+ancestors
+as
+(
+  select *
+    from folks
+      where name = 'Me' and dob = '2000-01-01'
+  union 
+  select p.id, p.name, p.dob, p.father, p.mother
+    from folks as p, ancestors AS a
+      where p.id = a.father or p.id = a.mother
+)
+select * from ancestors;
+
+show create view v1;
+
+select * from v1;
+
+create view v2 as
+with recursive
+ancestors
+as
+(
+  select *
+    from folks 
+      where name = 'Me'
+  union 
+  select p.*
+    from folks as p, ancestors as fa
+      where p.id = fa.father
+  union
+  select p.*
+    from folks as p, ancestors as ma
+      where p.id = ma.mother
+)
+select * from ancestors;
+
+show create view v2;
+
+select * from v2;
+
+drop view v1,v2;
+
+
+explain extended
+with recursive 
+ancestors
+as
+(
+  select *
+    from folks
+      where name = 'Me' and dob = '2000-01-01'
+  union 
+  select p.id, p.name, p.dob, p.father, p.mother
+    from folks as p, ancestors AS a
+      where p.id = a.father or p.id = a.mother
+)
+select * from ancestors;
+
+
+--echo # recursive spec with two anchor selects and two recursive ones 
+with recursive
+ancestor_ids (id)
+as
+(
+  select father from folks where name = 'Me'
+  union
+  select mother from folks where name = 'Me'
+  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;
+
+
+--echo # recursive spec using union all 
+with recursive
+ancestors
+as
+(
+  select *
+    from folks 
+      where name = 'Me'
+  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_STANDARD_COMPLIANT_RECURSIVE
+with recursive
+ancestor_ids (id, generation)
+as
+(
+  select father, 1 from folks where name = 'Me' and  father is not null
+  union all
+  select mother, 1 from folks where name = 'Me' 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 standard_compliant_cte=0;
+
+--ERROR ER_WITH_COL_WRONG_LIST
+with recursive
+ancestor_ids (id, generation)
+as
+(
+  select father from folks where name = 'Me' and  father is not null
+  union all
+  select mother from folks where name = 'Me' 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 = 'Me' and  father is not null
+  union all
+  select mother, 1 from folks where name = 'Me' 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 standard_compliant_cte=1;
+
+--ERROR ER_NOT_STANDARD_COMPLIANT_RECURSIVE
+with recursive
+coupled_ancestor_ids (id)
+as
+(
+  select father from folks where name = 'Me' and father is not null
+  union 
+  select mother from folks where name = 'Me' 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 standard_compliant_cte=0 for
+with recursive
+coupled_ancestor_ids (id)
+as
+(
+  select father from folks where name = 'Me' and father is not null
+  union 
+  select mother from folks where name = 'Me' 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_STANDARD_COMPLIANT_RECURSIVE
+with recursive
+ancestor_ids (id)
+as
+(
+  select father from folks where name = 'Me'
+  union
+  select mother from folks where name = 'Me'
+  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 standard_compliant_cte=0 for
+with recursive
+ancestor_ids (id)
+as
+(
+  select father from folks where name = 'Me'
+  union
+  select mother from folks where name = 'Me'
+  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 = 'Me'
+  union
+  select mother, 1 from folks where name = 'Me'
+  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_STANDARD_COMPLIANT_RECURSIVE
+with recursive
+ancestor_ids (id, generation)
+as
+(
+  select father, 1 from folks where name = 'Me'
+  union
+  select mother, 1 from folks where name = 'Me'
+  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 standard_compliant_cte=0 for
+with recursive
+ancestor_ids (id, generation)
+as
+(
+  select father, 1 from folks where name = 'Me'
+  union
+  select mother, 1 from folks where name = 'Me'
+  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;
+
+set statement max_recursive_iterations=1 for
+with recursive
+ancestor_ids (id, generation)
+as
+(
+  select father, 1 from folks where name = 'Me'
+  union
+  select mother, 1 from folks where name = 'Me'
+  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;
+
+--echo # query with recursive tables using key access
+
+alter table folks add primary key (id);
+
+explain
+with recursive
+ancestors
+as
+(
+  select *
+    from folks 
+      where name = 'Me'
+  union 
+  select p.*
+    from folks as p, ancestors as fa
+      where p.id = fa.father
+  union
+  select p.*
+    from folks as p, ancestors as ma
+      where p.id = ma.mother
+)
+select * from ancestors;
+
+
+with recursive
+ancestors
+as
+(
+  select *
+    from folks 
+      where name = 'Me'
+  union 
+  select p.*
+    from folks as p, ancestors as fa
+      where p.id = fa.father
+  union
+  select p.*
+    from folks as p, ancestors as ma
+      where p.id = ma.mother
+)
+select * from ancestors;
+
+
+--echo #
+--echo # EXPLAIN FORMAT=JSON on a query where one recursive CTE uses another:
+--echo #
+explain 
+with recursive 
+prev_gen
+as
+(
+  select folks.*
+    from folks, prev_gen
+      where folks.id=prev_gen.father or folks.id=prev_gen.mother
+  union
+  select * 
+    from folks
+      where name='Me'
+),
+ancestors
+as
+(
+  select *
+    from folks
+      where name='Me'
+  union 
+  select *
+    from ancestors
+  union
+    select *
+     from prev_gen
+)
+select ancestors.name, ancestors.dob from ancestors;
+
+explain FORMAT=JSON
+with recursive 
+prev_gen
+as
+(
+  select folks.*
+    from folks, prev_gen
+      where folks.id=prev_gen.father or folks.id=prev_gen.mother
+  union
+  select * 
+    from folks
+      where name='Me'
+),
+ancestors
+as
+(
+  select *
+    from folks
+      where name='Me2'
+  union 
+  select *
+    from ancestors where id < 234
+  union
+    select *
+     from prev_gen where id < 345
+)
+select ancestors.name, ancestors.dob from ancestors;
+
+--echo #
+explain format=json
+with recursive
+ancestor_couples(h_id, h_name, h_dob, h_father, h_mother,
+                 w_id, w_name, w_dob, w_father, w_mother)
+as
+(
+  select h.*, w.*
+    from folks h, folks w,  coupled_ancestors a
+      where a.father = h.id AND a.mother = w.id
+  union 
+  select h.*, w.*
+    from folks v, folks h, folks w
+      where v.name = 'Me' and
+            (v.father = h.id AND v.mother= w.id)
+),
+coupled_ancestors (id, name, dob, father, mother)
+as
+(
+  select h_id, h_name, h_dob, h_father, h_mother
+    from ancestor_couples
+  union all
+  select w_id, w_name, w_dob, w_father, w_mother
+    from ancestor_couples
+)
+select h_name, h_dob, w_name, w_dob
+  from ancestor_couples;
+
+
+create table my_ancestors
+with recursive
+ancestor_ids (id)
+as
+(
+  select father from folks where name = 'Me'
+  union
+  select mother from folks where name = 'Me'
+  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
+)
+select p.* from folks as p, ancestor_ids as a where p.id = a.id;
+
+select * from my_ancestors;
+
+delete from my_ancestors;
+
+insert into my_ancestors 
+with recursive
+ancestor_ids (id)
+as
+(
+  select father from folks where name = 'Me'
+  union
+  select mother from folks where name = 'Me'
+  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
+)
+select p.* from folks as p, ancestor_ids as a where p.id = a.id;
+
+select * from my_ancestors;
+
+drop table my_ancestors;
+
+--echo #
+--echo # MDEV-10883: execution of prepared statement from SELECT
+--echo #             with recursive CTE that renames columns
+--echo #
+
+prepare stmt from"
+with recursive
+ancestor_ids (id)
+as
+(
+  select father from folks where name = 'Me'
+  union
+  select mother from folks where name = 'Me'
+  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
+)
+select p.* from folks as p, ancestor_ids as a where p.id = a.id;
+";
+execute stmt;
+deallocate prepare stmt;
+
+--echo #
+--echo # MDEV-10881: execution of prepared statement from
+--echo #             CREATE ... SELECT, INSERT ... SELECT       
+--echo #
+
+prepare stmt from"
+create table my_ancestors
+with recursive
+ancestor_ids (id)
+as
+(
+  select father from folks where name = 'Me'
+  union
+  select mother from folks where name = 'Me'
+  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
+)
+select p.* from folks as p, ancestor_ids as a where p.id = a.id;
+";
+execute stmt;
+deallocate prepare stmt;
+select * from my_ancestors;
+
+delete from my_ancestors;
+
+prepare stmt from"
+insert into my_ancestors
+with recursive
+ancestor_ids (id)
+as
+(
+  select father from folks where name = 'Me'
+  union
+  select mother from folks where name = 'Me'
+  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
+)
+select p.* from folks as p, ancestor_ids as a where p.id = a.id;
+";
+execute stmt;
+deallocate prepare stmt;
+select * from my_ancestors;
+
+drop table my_ancestors;
+
+--echo #
+--echo # MDEV-10933: WITH clause together with SELECT in parenthesis
+--echo #             CREATE SELECT      
+--echo #
+
+create table my_ancestors
+(
+with recursive
+ancestor_ids (id)
+as
+(
+  select father from folks where name = 'Me'
+  union
+  select mother from folks where name = 'Me'
+  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
+)
+select p.* from folks as p, ancestor_ids as a where p.id = a.id
+);
+select * from my_ancestors;
+drop table my_ancestors;
+
+drop table folks;
+
+--echo #
+--echo # MDEV-10372: [bb-10.2-mdev9864 tree] EXPLAIN with recursive CTE enters endless recursion
+--echo #
+create table t1(a int);
+insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+ 
+explain format=json 
+with recursive t as (select a from t1 union select a+10 from t where a < 1000) 
+select  * from t;
+
+drop table t1;
+
+
+--echo #
+--echo # MDEV-10737: recursive union with several anchors at the end
+--echo #
+
+WITH RECURSIVE cte(n) AS
+  ( SELECT n+1 FROM cte WHERE n < 5 UNION SELECT 1 UNION SELECT 1 )
+SELECT * FROM cte;
+
+--echo #
+--echo # MDEV-10736: recursive definition with anchor over a table with blob
+--echo #
+
+CREATE TABLE t1 (f VARCHAR(1024));
+WITH RECURSIVE cte(f) AS
+  (SELECT t1.f FROM t1 UNION ALL SELECT cte.f FROM cte) 
+SELECT * FROM cte as t;
+DROP TABLE t1;
+
+--echo #
+--echo # MDEV-10899: mergeable derived in the spec of recursive CTE
+--echo #
+
+create table t1 (a int);
+insert into t1 values
+  (0), (1), (2), (3), (4);
+create table t2 (a int);
+insert into t2 values
+  (1), (2), (3), (4), (5);
+
+with recursive
+t1 as
+(
+select x.a from (select a from t2 where t2.a=3) x
+union
+select t2.a from t1,t2 where t1.a+1=t2.a
+)
+select * from t1;
+
+explain
+with recursive
+t1 as
+(
+select x.a from (select a from t2 where t2.a=3) x
+union
+select t2.a from t1,t2 where t1.a+1=t2.a
+)
+select * from t1;
+
+drop table t1,t2;
+
+--echo #
+--echo # MDEV-11278: non-mergeable view in the spec of recursive CTE
+--echo #
+
+create table t1 (a int);
+insert into t1 values
+  (0), (1), (2), (3), (4);
+create table t2 (a int);
+insert into t2 values
+  (1), (2), (3), (4), (5);
+
+create view v1 as
+  select a from t2 where a < 3
+  union
+  select a from t2 where a > 4; 
+
+with recursive
+t1 as
+(
+select a from v1 where a=1 
+union
+select v1.a from t1,v1 where t1.a+1=v1.a
+)
+select * from t1;
+
+drop view v1;
+drop table t1,t2;
+
+
+--echo #
+--echo # MDEV-11259: recursive CTE with concatenation operation
+--echo #
+
+DROP TABLE IF EXISTS edges;
+CREATE TABLE edges(
+  a int(10) unsigned NOT NULL,
+  b int(10) unsigned NOT NULL,
+  PRIMARY KEY (a,b),
+  KEY b(b)
+);
+
+INSERT INTO edges
+  VALUES (1,3),(2,1),(2,4),(3,4),(3,5),(3,6),(4,7),(5,1),(5,6),(6,1);
+
+DROP TABLE IF EXISTS edges2;
+CREATE VIEW edges2 (a, b) AS 
+ SELECT a, b FROM edges   UNION ALL   SELECT b, a FROM edges;
+
+--sorted_result
+WITH RECURSIVE transitive_closure(a, b, distance, path_string) AS
+( SELECT a, b, 1 AS distance,
+   concat(a, '.', b, '.') AS path_string
+  FROM edges
+ 
+  UNION ALL
+ 
+  SELECT tc.a, e.b, tc.distance + 1,
+  concat(tc.path_string, e.b, '.') AS path_string
+  FROM edges AS e
+    JOIN transitive_closure AS tc
+      ON e.a = tc.b
+  WHERE tc.path_string NOT LIKE concat('%', e.b, '.%')
+)
+SELECT * FROM transitive_closure
+ORDER BY a, b, distance;
+
+--sorted_result
+WITH RECURSIVE transitive_closure(a, b, distance, path_string) AS
+( SELECT a, b, 1 AS distance,
+         concat(a, '.', b, '.') AS path_string
+  FROM edges
+ WHERE a = 1 -- source
+ 
+  UNION ALL
+ 
+  SELECT tc.a, e.b, tc.distance + 1,
+         concat(tc.path_string, e.b, '.') AS path_string
+  FROM edges AS e
+  JOIN transitive_closure AS tc ON e.a = tc.b
+ WHERE tc.path_string NOT LIKE concat('%', e.b, '.%')
+)
+  SELECT * FROM transitive_closure
+   WHERE b = 6 -- destination
+ORDER BY a, b, distance;
+
+--sorted_result
+WITH RECURSIVE transitive_closure(a, b, distance, path_string) AS
+( SELECT  a, b, 1 AS distance,
+          concat(a, '.', b, '.') AS path_string
+  FROM edges2
+ 
+  UNION ALL
+ 
+  SELECT tc.a, e.b, tc.distance + 1,
+  concat(tc.path_string, e.b, '.') AS path_string
+  FROM edges2 AS e
+    JOIN transitive_closure AS tc ON e.a = tc.b
+  WHERE tc.path_string NOT LIKE concat('%', e.b, '.%')
+)
+SELECT * FROM transitive_closure
+ORDER BY a, b, distance;
+
+--sorted_result
+WITH RECURSIVE transitive_closure(a, b, distance, path_string)
+AS
+( SELECT a, b, 1 AS distance,
+         concat(a, '.', b, '.') AS path_string
+  FROM edges2
+ 
+  UNION ALL
+ 
+  SELECT tc.a, e.b, tc.distance + 1,
+         concat(tc.path_string, e.b, '.') AS path_string
+  FROM edges2 AS e
+  JOIN transitive_closure AS tc ON e.a = tc.b
+ WHERE tc.path_string NOT LIKE concat('%', e.b, '.%')
+)
+SELECT a, b, min(distance) AS dist FROM transitive_closure
+GROUP BY a, b
+ORDER BY a, dist, b;
+
+DROP VIEW edges2;
+DROP TABLE edges;
+
+
+--echo #
+--echo # MDEV-11674: recursive CTE table that cannot be stored
+--echo #             in a heap table
+--echo #
+
+create table t1 (id int, test_data varchar(36));
+
+insert into t1(id, test_data)
+select id, test_data
+    from (
+        with recursive data_generator(id, test_data) as (
+                select 1 as id, uuid() as test_data
+                union all
+                select id + 1, uuid() from data_generator where id < 150000
+            )
+        select * from data_generator
+    ) as a;
+
+drop table t1;
+
+--echo #
+--echo # MDEV-10773: ANALYZE for query with recursive CTE
+--echo #
+
+--source include/analyze-format.inc
+analyze format=json 
+with recursive src(counter) as 
+(select 1 
+ union 
+ select counter+1 from src where counter<10
+) select * from src;
+
+--echo #
+--echo # mdev-12360: recursive reference in left operand of LEFT JOIN
+--echo #
+
+create table  folks(id int, name char(32), dob date, father int, mother int);
+
+insert into folks values
+(100, 'Me', '2000-01-01', 20, 30),
+(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),
+(25, 'Uncle Jim', '1968-11-18', 8, 7),
+(98, 'Sister Amy', '2001-06-20', 20, 30),
+(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); 
+
+with recursive
+ancestor_ids (id)
+as
+(
+  select father from folks where name = 'Me'
+  union
+  select mother from folks where name = 'Me'
+  union
+  select father from ancestor_ids as a left join folks on folks.id = a.id
+  union
+  select mother from ancestor_ids as a left join folks 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;
+
+drop table folks;
+
+--echo #
+--echo # mdev-12368: crash with mutually recursive CTE
+--echo #             that arenot Standard compliant 
+--echo #
+
+create table value_nodes (v char(4));
+create table module_nodes(m char(4));
+create table module_arguments(m char(4), v char(4));
+create table module_results(m char(4), v char(4));
+
+--ERROR ER_NOT_STANDARD_COMPLIANT_RECURSIVE
+with recursive
+reached_values as 
+(
+  select v from value_nodes where v in ('v3','v7','v9')
+  union
+  select module_results.v from module_results, applied_modules
+     where module_results.m = applied_modules.m
+),
+applied_modules as
+(
+  select module_nodes.m
+  from
+    module_nodes
+    left join
+    ( 
+      module_arguments
+      left join
+      reached_values
+      on module_arguments.v = reached_values.v
+     )
+     on reached_values.v is null and
+     module_nodes.m = module_arguments.m
+  where module_arguments.m is null
+)
+select * from reached_values;
+
+drop table value_nodes, module_nodes, module_arguments, module_results;
+
+--echo #
+--echo # mdev-12375: query using one of two mutually recursive CTEs
+--echo #             whose non-recursive part returns an empty set
+--echo #
+
+create table value_nodes (v char(4));
+insert into value_nodes values
+  ('v1'), ('v2'), ('v3'), ('v4'), ('v5'), ('v6'), ('v7'), ('v8'), ('v9'),
+  ('v10'), ('v11'), ('v12'), ('v13'), ('v14'), ('v15'), ('v16');
+create table module_nodes(m char(4));
+insert into module_nodes values
+  ('m1'), ('m2'), ('m3'), ('m4'), ('m5'), ('m6'), ('m7');
+create table module_arguments(m char(4), v char(4));
+insert into module_arguments values
+  ('m1','v3'), ('m1','v9'),
+  ('m2','v4'), ('m2','v3'), ('m2','v7'),
+  ('m3','v6'),
+  ('m4','v4'), ('m4','v1'),
+  ('m5','v10'), ('m5','v8'), ('m5','v3'),
+  ('m6','v8'), ('m6','v1'),
+  ('m7','v11'), ('m7','v12');
+create table module_results(m char(4), v char(4));
+insert into module_results values
+  ('m1','v4'),
+  ('m2','v1'), ('m2','v6'),
+  ('m3','v10'),
+  ('m4','v8'),
+  ('m5','v11'), ('m5','v9'),
+  ('m6','v12'), ('m6','v4'),
+  ('m7','v2');
+
+set statement max_recursive_iterations=2, standard_compliant_cte=0 for
+with recursive
+reached_values as 
+(
+  select v from value_nodes where v in ('v3','v7','v9')
+  union
+  select module_results.v from module_results, applied_modules
+     where module_results.m = applied_modules.m
+),
+applied_modules as
+(
+  select * from module_nodes where 1=0
+  union
+  select module_nodes.m
+  from
+    module_nodes
+    left join
+    ( 
+      module_arguments
+      left join
+      reached_values
+      on module_arguments.v = reached_values.v
+     )
+     on reached_values.v is null and
+     module_nodes.m = module_arguments.m
+  where module_arguments.m is null
+)
+select * from applied_modules;
+
+drop table value_nodes, module_nodes, module_arguments, module_results;
+
+--echo #
+--echo # mdev-12519: recursive references in subqueries 
+--echo #             
+
+create table t1 (lp char(4) not null, rp char(4) not null);
+insert into t1 values
+   ('p1','p2'), ('p2','p3'), ('p3','p4'), ('p4','p5'),
+   ('p2','p7'), ('p7','p8'), ('p8','p3'), ('p8','p4');
+
+set standard_compliant_cte=0;
+
+with recursive
+reachables(p) as 
+( 
+   select lp from t1 where lp = 'p1'
+   union 
+   select t1.rp from reachables, t1 
+       where t1.lp = reachables.p
+)
+select * from reachables;
+
+with recursive 
+reachables(p) as
+(
+   select lp from t1 where lp = 'p1'
+   union 
+   select t1.rp from reachables, t1 
+      where 'p3' not in (select * from reachables) and
+                 t1.lp = reachables.p
+)
+select * from reachables;
+
+with recursive 
+reachables(p) as
+(
+   select lp from t1 where lp = 'p1'
+   union 
+   select t1.rp from reachables, t1 
+      where 'p3' not in (select p from reachables where p <= 'p5'
+                         union
+                         select p from reachables where p > 'p5') and
+                 t1.lp = reachables.p
+)
+select * from reachables;
+
+prepare stmt from "
+with recursive 
+reachables(p) as
+(
+   select lp from t1 where lp = 'p1'
+   union 
+   select t1.rp from reachables, t1 
+      where 'p3' not in (select p from reachables where p <= 'p5'
+                         union
+                         select p from reachables where p > 'p5') and
+                 t1.lp = reachables.p
+)
+select * from reachables;
+";
+
+execute stmt;
+execute stmt;
+
+deallocate prepare stmt;
+
+drop table t1;
+
+create table objects(v char(4) not null);
+insert into objects values
+  ('v1'), ('v2'), ('v3'), ('v4'), ('v5'),
+  ('v6'), ('v7'), ('v8'), ('v9'), ('v10');
+
+create table modules(m char(4) not null);
+insert into modules values
+  ('m1'), ('m2'), ('m3'), ('m4');
+
+create table module_arguments(m char(4) not null, v char(4) not null);
+insert into module_arguments values
+  ('m1','v3'), ('m1','v9'),
+  ('m2','v4'), ('m2','v7'),
+  ('m3','v6'), ('m4','v2');
+
+create table module_results(m char(4) not null, v char(4) not null);
+insert into module_results values
+  ('m1','v4'),
+  ('m2','v1'), ('m2','v6'),
+  ('m3','v10'), ('m4','v7');
+
+set standard_compliant_cte=0;
+
+with recursive
+reached_objects as 
+(
+  select v, 'init' as m from objects where v in ('v3','v7','v9')
+  union
+  select module_results.v, module_results.m from module_results, applied_modules
+     where module_results.m = applied_modules.m
+),
+applied_modules as
+(
+  select * from modules where 1=0
+  union
+  select modules.m
+  from
+    modules
+    where
+      not exists (select * from module_arguments
+                    where module_arguments.m = modules.m and
+                          module_arguments.v not in 
+                          (select v from reached_objects))
+)
+select * from reached_objects;
+
+with recursive
+reached_objects as 
+(
+  select v, 'init' as m from objects where v in ('v3','v7','v9')
+  union
+  select module_results.v, module_results.m from module_results, applied_modules
+     where module_results.m = applied_modules.m
+),
+applied_modules as
+(
+  select * from modules where 1=0
+  union
+  select modules.m
+  from
+    modules
+    where 
+      'v6' not in (select v from reached_objects) and
+      not exists (select * from module_arguments
+                    where module_arguments.m = modules.m and
+                          module_arguments.v not in 
+                          (select v from reached_objects))
+)
+select * from reached_objects;
+
+prepare stmt from "
+with recursive
+reached_objects as 
+(
+  select v, 'init' as m from objects where v in ('v3','v7','v9')
+  union
+  select module_results.v, module_results.m from module_results, applied_modules
+     where module_results.m = applied_modules.m
+),
+applied_modules as
+(
+  select * from modules where 1=0
+  union
+  select modules.m
+  from
+    modules
+    where 
+      'v6' not in (select v from reached_objects) and
+      not exists (select * from module_arguments
+                    where module_arguments.m = modules.m and
+                          module_arguments.v not in 
+                          (select v from reached_objects))
+)
+select * from reached_objects;
+";
+
+execute stmt;
+execute stmt;
+
+deallocate prepare stmt;
+
+drop table objects, modules, module_arguments, module_results;
+
+set standard_compliant_cte=default;
+select @@standard_compliant_cte;
+
+--echo #
+--echo # mdev-12554: impossible where in recursive select 
+--echo #             
+
+CREATE TABLE t1 (i int);
+INSERT INTO t1 VALUES (1),(2);
+
+WITH RECURSIVE
+cte(f) AS ( SELECT i FROM t1 UNION SELECT f FROM t1, cte WHERE 1=0 )
+SELECT * FROM cte;
+
+DROP TABLE t1;
+
+--echo #
+--echo # mdev-12556: recursive execution uses Aria temporary tables 
+--echo #             
+
+CREATE TABLE t (c1 varchar(255), c2 tinytext);
+INSERT INTO t VALUES ('a','a'),('b','b'),('c','c'),('d','d');
+
+let $q1=
+WITH RECURSIVE cte(f) AS (
+  SELECT c1 FROM t
+  UNION
+  SELECT c1 FROM t, cte
+) SELECT COUNT(*) FROM cte;
+
+let $q2=
+WITH RECURSIVE cte(f) AS (
+  SELECT c2 FROM t
+  UNION
+  SELECT c2 FROM t, cte
+) SELECT COUNT(*) FROM cte;
+
+eval ANALYZE $q1;
+eval $q1;
+
+eval ANALYZE $q2;
+eval $q2;
+
+DROP TABLE t;
+
+--echo #
+--echo # mdev-12563: no recursive references on the top level of the CTE spec 
+--echo #  
+
+CREATE TABLE t (i int);
+INSERT INTO t VALUES (3), (1),(2);
+ 
+SET standard_compliant_cte=0;
+ 
+WITH RECURSIVE cte(f) AS (
+  SELECT i FROM t
+  UNION
+  SELECT i FROM t WHERE i NOT IN ( SELECT * FROM cte )
+) SELECT * FROM cte;           
+
+WITH RECURSIVE cte(f) AS (
+  SELECT i FROM t
+  UNION
+  SELECT i FROM t WHERE i NOT IN ( SELECT * FROM cte WHERE i < 2 )
+  UNION
+  SELECT i FROM t WHERE i NOT IN ( SELECT * FROM cte WHERE i > 2 )
+) SELECT * FROM cte;
+           
+WITH RECURSIVE cte(f) AS (
+  SELECT i FROM t
+  UNION
+  SELECT i FROM t
+    WHERE i NOT IN ( SELECT * FROM cte WHERE i < 2
+                     UNION
+                     SELECT * FROM cte WHERE i > 2)
+) SELECT * FROM cte;           
+
+WITH RECURSIVE cte(f) AS (
+  SELECT i FROM t
+  UNION
+  SELECT i FROM t 
+    WHERE i NOT IN ( SELECT * FROM t
+                       WHERE i IN ( SELECT * FROM cte ) GROUP BY i )
+) SELECT * FROM cte;           
+
+WITH RECURSIVE cte(f) AS (
+  SELECT i FROM t
+  UNION
+  SELECT i FROM t WHERE i NOT IN ( SELECT * FROM cte )
+  UNION 
+  SELECT * FROM cte WHERE f > 2
+) SELECT * FROM cte;           
+
+set standard_compliant_cte=default;
+
+DROP TABLE t;
+
+--echo #
+--echo # mdev-14184: recursive CTE embedded into CTE with multiple references 
+--echo #
+
+WITH
+cte1 AS (
+  SELECT n FROM (
+    WITH RECURSIVE rec_cte(n) AS (
+      SELECT 1 as n1
+      UNION ALL
+      SELECT n+1 as n2 FROM rec_cte WHERE n < 3
+    ) SELECT n FROM rec_cte
+  ) AS X
+),
+cte2 as (
+ SELECT 2 FROM cte1
+)
+SELECT *
+FROM cte1;
+
+--echo #
+--echo # mdev-14629: a user-defined variable is defined by the recursive CTE
+--echo #
+
+set @var=
+(
+  with recursive cte_tab(a) as (
+    select 1
+    union
+    select a+1 from cte_tab
+    where a<3)
+  select count(*) from cte_tab
+);
+
+select @var;
+
+create table t1(a int, b int);
+insert into t1 values (3,8),(1,5),(5,7),(7,4),(4,3);
+
+set @var=
+(
+  with recursive summ(a,s) as (
+    select 1, 0 union
+    select t1.b, t1.b+summ.s from summ, t1
+    where summ.a=t1.a)
+  select s from summ
+    order by a desc
+    limit 1
+);
+
+select @var;
+
+--ERROR ER_UNACCEPTABLE_MUTUAL_RECURSION
+set @var=
+(
+  with recursive
+  cte_1 as (
+    select 1
+    union
+    select * from cte_2),
+  cte_2 as (
+    select * from cte_1
+    union
+    select a from t1, cte_2
+    where t1.a=cte_2.a)
+  select * from cte_2
+  limit 1
+);
+
+drop table t1;
+
+--echo #
+--echo # mdev-14777: crash caused by the same as in mdev-14755
+--echo #
+
+--source include/have_sequence.inc
+
+CREATE TABLE t1 (i1 int NOT NULL, i2 int);
+CREATE TABLE t2 (d1 int NOT NULL PRIMARY KEY);
+CREATE TABLE t3 (i int );
+
+insert into t1 select seq,seq from seq_1_to_100000;
+insert into t2 select seq from  seq_1000_to_100000;
+insert into t3 select seq from seq_1_to_1000;
+
+SELECT *
+FROM
+(
+  SELECT *
+  FROM
+  (
+    WITH RECURSIVE rt AS
+    (
+      SELECT i2 P, i1 C FROM t1 WHERE i1 IN (SELECT d1 FROM t2)
+      UNION
+      SELECT t1.i2 P, rt.C C FROM t1, rt
+    )
+    SELECT C,P
+      FROM ( SELECT P,C FROM rt WHERE NOT EXISTS (SELECT 1 FROM t1) ) Y
+  ) X
+  WHERE 1 = 1
+) K, t3;
+
+drop table t1,t2,t3;
+
+--echo #
+--echo # mdev-14879: subquery with recursive reference in WHERE of CTE
+--echo #
+
+create table flights
+(departure varchar(32),
+ arrival varchar(32),
+ carrier varchar(20),
+ flight_number char(7));
+
+insert into flights values
+('Seattle', 'Frankfurt', 'Lufthansa', 'LH 491'),
+('Seattle', 'Chicago', 'American', 'AA 2573'),
+('Seattle', 'Los Angeles', 'Alaska Air', 'AS 410'),
+('Chicago', 'New York', 'American', 'AA 375'),
+('Chicago', 'Montreal', 'Air Canada', 'AC 3053'),
+('Los Angeles', 'New York', 'Delta', 'DL 1197'),
+('Moscow', 'Tokyo', 'Aeroflot', 'SU 264'),
+('New York', 'Paris', 'Air France', 'AF 23'),
+('Frankfurt', 'Moscow', 'Lufthansa', 'LH 1444'),
+('Tokyo', 'Seattle', 'ANA', 'NH 178'),
+('Los Angeles', 'Tokyo', 'ANA', 'NH 175'),
+('Moscow', 'Los Angeles', 'Aeroflot', 'SU 106'),
+('Montreal', 'Paris', 'Air Canada', 'AC 870'),
+('Cairo', 'Paris', 'Air France', 'AF 503'),
+('New York', 'Seattle', 'American', 'AA 45'),
+('Paris', 'Chicago', 'Air France', 'AF 6734');
+
+with recursive destinations (city) as
+( select a.arrival from flights a where a.departure='Cairo'
+  union
+  select b.arrival from destinations r, flights b where r.city=b.departure)
+select * from destinations;
+
+set standard_compliant_cte=0;
+
+let $q=
+with recursive destinations (city, legs) as
+(
+  select a.arrival, 1 from flights a where a.departure='Cairo'
+  union
+  select b.arrival, r.legs + 1 from destinations r, flights b
+  where r.city=b.departure and b.arrival not in (select city from destinations)
+)
+select * from destinations;
+
+eval $q;
+eval explain extended $q;
+
+set standard_compliant_cte=default;
+
+drop table flights;
+
+--echo #
+--echo # MDEV-15162: Setting user variable in recursive CTE
+--echo #
+
+SET @c=1;
+
+WITH RECURSIVE cte AS
+	(SELECT 5
+	UNION
+	SELECT @c:=@c+1 FROM cte WHERE @c<3)
+SELECT * FROM cte;
+
+
+--echo #
+--echo # MDEV-14217 [db crash] Recursive CTE when SELECT includes new field
+--echo #
+
+CREATE TEMPORARY TABLE a_tbl (
+  a VARCHAR(33) PRIMARY KEY,
+  b VARCHAR(33)
+);
+
+INSERT INTO a_tbl VALUES ('block0', 'block0'), ('block1', NULL);
+
+--error ER_WRONG_NUMBER_OF_COLUMNS_IN_SELECT
+WITH RECURSIVE Q0 AS (
+  SELECT T0.a, T0.b, 5
+  FROM a_tbl T0
+  WHERE b IS NULL
+  UNION ALL
+  SELECT T1.a, T1.b
+  FROM Q0
+  JOIN a_tbl T1
+  ON T1.a=Q0.a
+) SELECT distinct(Q0.a), Q0.b
+  FROM Q0;
+DROP TABLE a_tbl;
+
+--error ER_WRONG_NUMBER_OF_COLUMNS_IN_SELECT
+WITH RECURSIVE x AS (SELECT 1,2 UNION ALL SELECT 1 FROM x) SELECT * FROM x;
+
+--echo #
+--echo # MDEV-15162: Setting user variable in recursive CTE
+--echo #
+
+SET @c=1;
+
+WITH RECURSIVE cte AS
+	(SELECT 5
+	UNION
+	SELECT @c:=@c+1 FROM cte WHERE @c<3)
+SELECT * FROM cte;
+
+--echo #
+--echo # MDEV-14883: recursive references in operands of INTERSECT / EXCEPT
+--echo #
+
+create table flights
+(departure varchar(32),
+ arrival varchar(32),
+ carrier varchar(20),
+ flight_number char(7));
+
+insert into flights values
+('Seattle', 'Frankfurt', 'Lufthansa', 'LH 491'),
+('Seattle', 'Amsterdam', 'KLM', 'KL 6032'),
+('Seattle', 'Chicago', 'American', 'AA 2573'),
+('Seattle', 'Los Angeles', 'Alaska Air', 'AS 410'),
+('Chicago', 'New York', 'American', 'AA 375'),
+('Chicago', 'Montreal', 'Air Canada', 'AC 3053'),
+('Los Angeles', 'New York', 'Delta', 'DL 1197'),
+('New York', 'London', 'British Airways', 'BA 1511'),
+('London', 'Moscow', 'British Airways', 'BA 233'),
+('Moscow', 'Tokyo', 'Aeroflot', 'SU 264'),
+('Moscow', 'Dubai', 'Emirates', 'EK 2421'),
+('Dubai', 'Tokyo', 'Emirates', 'EK 318'),
+('Dubai', 'Bangkok', 'Emirates', 'EK 2142'),
+('Beijing', 'Bangkok', 'Air China', 'CA 757'),
+('Beijing', 'Tokyo', 'Air China', 'CA 6653'),
+('Moscow', 'Bangkok', 'Aeroflot', 'SU 270'),
+('New York', 'Reykjavik', 'Icelandair', 'FL 416'),
+('New York', 'Paris', 'Air France', 'AF 23'),
+('Amsterdam', 'Moscow', 'KLM', 'KL 903'),
+('Frankfurt', 'Dubai', 'Lufthansa', 'LH 630'),
+('Frankfurt', 'Moscow', 'Lufthansa', 'LH 1444'),
+('Reykjavik', 'London', 'British Airways', 'BA 2229'),
+('Frankfurt', 'Beijing', 'Air China', 'CA 966'),
+('Tokyo', 'Seattle', 'ANA', 'NH 178'),
+('Los Angeles', 'Tokyo', 'ANA', 'NH 175'),
+('Moscow', 'Los Angeles', 'Aeroflot', 'SU 106'),
+('Montreal', 'Paris', 'Air Canada', 'AC 870'),
+('London', 'Delhi', 'British Airways', 'BA 143'),
+('Delhi', 'Bangkok', 'Air India', 'AI 306'),
+('Delhi', 'Dubai', 'Air India', 'AI 995'),
+('Dubai', 'Cairo', 'Emirates', 'EK 927'),
+('Cairo', 'Paris', 'Air France', 'AF 503'),
+('Amsterdam', 'New York', 'Delta', 'DL 47'),
+('New York', 'Seattle', 'American', 'AA 45'),
+('Paris', 'Chicago', 'Air France', 'AF 6734');
+
+create table distances
+(city1 varchar(32),
+ city2 varchar(32),
+ dist int);
+
+insert into distances values
+('Seattle', 'Frankfurt', 5080),
+('Seattle', 'Amsterdam', 4859),
+('Seattle', 'Chicago', 1733),
+('Seattle', 'Los Angeles', 960),
+('Chicago', 'New York', 712),
+('Chicago', 'Montreal', 746),
+('Los Angeles', 'New York', 2446),
+('New York', 'London', 3459),
+('London', 'Moscow', 1554),
+('Moscow', 'Tokyo', 4647),
+('Moscow', 'Dubai', 2298),
+('Dubai', 'Tokyo', 4929),
+('Dubai', 'Bangkok', 3050),
+('Beijing', 'Bangkok', 2046),
+('Beijing', 'Tokyo', 1301),
+('Moscow', 'Bangkok', 4390),
+('New York', 'Reykjavik', 2613),
+('New York', 'Paris', 3625),
+('Amsterdam', 'Moscow', 1334),
+('Frankfurt', 'Dubai', 3003),
+('Frankfurt', 'Moscow', 1256),
+('Reykjavik', 'London', 1173),
+('Frankfurt', 'Beijing', 4836),
+('Tokyo', 'Seattle', 4783),
+('Los Angeles', 'Tokyo', 5479),
+('Moscow', 'Los Angeles', 6071),
+('Moscow', 'Reykjavik', 2052),
+('Montreal', 'Paris', 3425),
+('London', 'Delhi', 4159),
+('London', 'Paris', 214),
+('Delhi', 'Bangkok', 1810),
+('Delhi', 'Dubai', 1369),
+('Delhi', 'Beijing', 2350),
+('Dubai', 'Cairo', 1501),
+('Cairo', 'Paris', 1992),
+('Amsterdam', 'New York', 3643),
+('New York', 'Seattle', 2402),
+('Paris', 'Chicago', 4136),
+('Paris', 'Los Angeles', 5647);
+
+with recursive destinations (city) as
+(
+  select a.arrival from flights a where a.departure = 'Seattle'
+  union
+  select b.arrival from destinations r, flights b where r.city = b.departure
+)
+select * from destinations;
+
+with recursive destinations (city) as
+(
+  select a.arrival from flights a, distances d
+    where a.departure = 'Seattle' and
+          a.departure = d.city1 and a.arrival = d.city2 and
+          d.dist < 4000
+  union
+  select b.arrival from destinations r, flights b, distances d
+    where r.city = b.departure and
+          b.departure = d.city1 and b.arrival = d.city2 and
+          d.dist < 4000
+)
+select * from destinations;
+
+set standard_compliant_cte=0;
+
+with recursive legs_to_destinations
+  (departure, arrival, dist, leg_no, acc_mileage) as
+(
+  select a.departure, a.arrival, d.dist, 1, d.dist
+    from flights a, distances d
+    where a.departure = 'Seattle' and
+          a.departure = d.city1 and a.arrival = d.city2 and
+          d.dist < 4000
+  union all
+  select b.departure, b.arrival, d.dist, r.leg_no + 1, r.acc_mileage + d.dist
+    from legs_to_destinations r, flights b, distances d
+    where r.arrival = b.departure and
+          b.departure = d.city1 and b.arrival = d.city2 and
+          d.dist < 4000 and
+          b.arrival not in (select arrival from legs_to_destinations)
+)
+select * from legs_to_destinations;
+
+set standard_compliant_cte=default;
+
+with recursive destinations (city) as
+(
+  select a.arrival from flights a, distances d
+    where a.departure = 'Seattle' and
+          a.departure = d.city1 and a.arrival = d.city2 and
+          d.dist < 4000
+  union
+  select b.arrival from destinations r, flights b
+    where r.city = b.departure
+  intersect
+  select city2 from destinations s, distances d
+    where s.city = d.city1 and d.dist < 4000
+)
+select * from destinations;
+
+with recursive destinations (city) as
+(
+  select a.arrival from flights a where a.departure = 'Seattle'
+  union
+  select * from
+  (
+    select b.arrival from destinations r, flights b
+      where r.city = b.departure
+    except
+    select arrival from flights
+      where arrival in
+            ('New York', 'London', 'Moscow', 'Dubai', 'Cairo', 'Tokyo')
+  ) t
+)
+select * from destinations;
+
+drop table flights, distances;