1 files changed, 215 insertions, 0 deletions
diff --git a/mysql-test/t/cte_recursive.test b/mysql-test/t/cte_recursive.test
index 6fef24be34f..911c381d46a 100644
--- a/mysql-test/t/cte_recursive.test
+++ b/mysql-test/t/cte_recursive.test
@@ -338,6 +338,221 @@ select h_name, h_dob, w_name, w_dob
   from ancestor_couples;
 
 
+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;
+
+
+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;
+
+
+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;
+
+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;
+
+
+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;
+
+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;
+
+
+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;
+
+
 prepare stmt1 from "
 with recursive 
 ancestors