diff options
Diffstat (limited to 'mysql-test/main/cte_recursive.test')
| -rw-r--r-- | mysql-test/main/cte_recursive.test | 2310 |
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; |