diff options
Diffstat (limited to 'mysql-test/main/cte_recursive.result')
| -rw-r--r-- | mysql-test/main/cte_recursive.result | 5971 |
1 files changed, 5971 insertions, 0 deletions
diff --git a/mysql-test/main/cte_recursive.result b/mysql-test/main/cte_recursive.result new file mode 100644 index 00000000..ef0920bf --- /dev/null +++ b/mysql-test/main/cte_recursive.result @@ -0,0 +1,5971 @@ +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'); +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 HY000: Unacceptable mutual recursion with anchored table 't' +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; +ERROR HY000: Unacceptable mutual recursion with anchored table 'a1' +drop table t1; +# 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); +# just WITH : s refers to t defined after s +with +s(a) as (select t.a + 10 from t), +t(a) as (select t1.a from t1) +select * from s; +ERROR 42S02: Table 'test.t' doesn't exist +# 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; +a +10 +11 +12 +13 +14 +# 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; +a +3 +1 +2 +4 +5 +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; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 30 +2 DERIVED t2 ALL NULL NULL NULL NULL 5 Using where +3 UNION t1 ALL NULL NULL NULL NULL 5 +3 UNION t2 ALL NULL NULL NULL NULL 5 Using where; Using join buffer (flat, BNL join) +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL +#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; +a +3 +4 +5 +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; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 5 +2 DERIVED t2 ALL NULL NULL NULL NULL 5 Using where +3 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 5 +3 RECURSIVE UNION t2 ALL NULL NULL NULL NULL 5 Using where; Using join buffer (flat, BNL join) +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL +# 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; +Field Type Null Key Default Extra +a bigint(12) YES NULL +# 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; +Field Type Null Key Default Extra +a int(11) YES NULL +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); +# simple recursion with one anchor and one recursive select +# 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; +id name dob father mother +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 +7 Grandma Sally 1943-08-23 NULL 6 +8 Grandpa Ben 1940-10-21 NULL NULL +6 Grandgrandma Martha 1923-05-17 NULL NULL +# simple recursion with one anchor and one recursive select +# 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; +id name dob father mother +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 +7 Grandma Sally 1943-08-23 NULL 6 +8 Grandpa Ben 1940-10-21 NULL NULL +6 Grandgrandma Martha 1923-05-17 NULL NULL +# simple recursion with one anchor and one recursive select +# 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; +id name dob father mother +67 Cousin Eddie 1992-02-28 25 27 +25 Uncle Jim 1968-11-18 8 7 +27 Auntie Melinda 1971-03-29 NULL NULL +7 Grandma Sally 1943-08-23 NULL 6 +8 Grandpa Ben 1940-10-21 NULL NULL +6 Grandgrandma Martha 1923-05-17 NULL NULL +# 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; +id name dob father mother +100 Me 2000-01-01 20 30 +98 Sister Amy 2001-06-20 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 +7 Grandma Sally 1943-08-23 NULL 6 +8 Grandpa Ben 1940-10-21 NULL NULL +6 Grandgrandma Martha 1923-05-17 NULL NULL +# 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; +name dob +Me 2000-01-01 +Dad 1970-02-02 +Mom 1975-03-03 +Grandpa Bill 1940-04-05 +Grandma Ann 1941-10-15 +Grandma Sally 1943-08-23 +Grandpa Ben 1940-10-21 +Grandgrandma Martha 1923-05-17 +# 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; +name dob +Me 2000-01-01 +Dad 1970-02-02 +Mom 1975-03-03 +Grandpa Bill 1940-04-05 +Grandpa Ben 1940-10-21 +Grandma Ann 1941-10-15 +Grandma Sally 1943-08-23 +Grandgrandma Martha 1923-05-17 +# simple recursion with one anchor and one recursive select +# 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; +id name dob father mother +10 Grandpa Bill 1940-04-05 NULL NULL +20 Dad 1970-02-02 10 9 +100 Me 2000-01-01 20 30 +98 Sister Amy 2001-06-20 20 30 +# simple recursion with one anchor and one recursive select +# 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; +id name dob father mother +7 Grandma Sally 1943-08-23 NULL 6 +30 Mom 1975-03-03 8 7 +25 Uncle Jim 1968-11-18 8 7 +100 Me 2000-01-01 20 30 +98 Sister Amy 2001-06-20 20 30 +67 Cousin Eddie 1992-02-28 25 27 +# 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); +id name dob father mother id name dob father mother +20 Dad 1970-02-02 10 9 30 Mom 1975-03-03 8 7 +10 Grandpa Bill 1940-04-05 NULL NULL 9 Grandma Ann 1941-10-15 NULL NULL +8 Grandpa Ben 1940-10-21 NULL NULL 7 Grandma Sally 1943-08-23 NULL 6 +# 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; +husband h_dob wife w_dob +Dad 1970-02-02 Mom 1975-03-03 +Grandpa Bill 1940-04-05 Grandma Ann 1941-10-15 +Grandpa Ben 1940-10-21 Grandma Sally 1943-08-23 +# 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; +id name dob father mother +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 +8 Grandpa Ben 1940-10-21 NULL NULL +9 Grandma Ann 1941-10-15 NULL NULL +7 Grandma Sally 1943-08-23 NULL 6 +6 Grandgrandma Martha 1923-05-17 NULL NULL +# 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; +h_name h_dob w_name w_dob +Dad 1970-02-02 Mom 1975-03-03 +Grandpa Bill 1940-04-05 Grandma Ann 1941-10-15 +Grandpa Ben 1940-10-21 Grandma Sally 1943-08-23 +# 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; +h_name h_dob w_name w_dob +Dad 1970-02-02 Mom 1975-03-03 +Grandpa Bill 1940-04-05 Grandma Ann 1941-10-15 +Grandpa Ben 1940-10-21 Grandma Sally 1943-08-23 +# 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; +h_name h_dob w_name w_dob +Dad 1970-02-02 Mom 1975-03-03 +Grandpa Bill 1940-04-05 Grandma Ann 1941-10-15 +Grandpa Ben 1940-10-21 Grandma Sally 1943-08-23 +# 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; +h_name h_dob w_name w_dob +Dad 1970-02-02 Mom 1975-03-03 +Grandpa Bill 1940-04-05 Grandma Ann 1941-10-15 +Grandpa Ben 1940-10-21 Grandma Sally 1943-08-23 +# 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; +h_id w_id +20 30 +10 9 +8 7 +# 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; +name dob name dob +Dad 1970-02-02 Mom 1975-03-03 +Grandpa Bill 1940-04-05 Grandma Ann 1941-10-15 +Grandpa Ben 1940-10-21 Grandma Sally 1943-08-23 +# 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; +name dob name dob +Dad 1970-02-02 Mom 1975-03-03 +Grandpa Bill 1940-04-05 Grandma Ann 1941-10-15 +Grandpa Ben 1940-10-21 Grandma Sally 1943-08-23 +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; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where +1 PRIMARY <derived3> ref key0 key0 5 c.h_id 2 100.00 +1 PRIMARY <derived3> ref key0 key0 5 c.w_id 2 100.00 +3 DERIVED folks ALL NULL NULL NULL NULL 12 100.00 Using where +4 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 2 100.00 +4 RECURSIVE UNION p ALL NULL NULL NULL NULL 12 100.00 Using where; Using join buffer (flat, BNL join) +5 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 2 100.00 +5 RECURSIVE UNION p ALL NULL NULL NULL NULL 12 100.00 Using where; Using join buffer (flat, BNL join) +NULL UNION RESULT <union3,4,5> ALL NULL NULL NULL NULL NULL NULL +2 DERIVED <derived3> ALL NULL NULL NULL NULL 12 100.00 Using where +Warnings: +Note 1003 with recursive ancestor_couple_ids(`h_id`,`w_id`) as (/* select#2 */ select `a`.`father` AS `h_id`,`a`.`mother` AS `w_id` 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#3 */ select `test`.`folks`.`id` AS `id`,`test`.`folks`.`name` AS `name`,`test`.`folks`.`dob` AS `dob`,`test`.`folks`.`father` AS `father`,`test`.`folks`.`mother` AS `mother` from `test`.`folks` where `test`.`folks`.`name` = 'Me' union all /* select#4 */ select `test`.`p`.`id` AS `id`,`test`.`p`.`name` AS `name`,`test`.`p`.`dob` AS `dob`,`test`.`p`.`father` AS `father`,`test`.`p`.`mother` AS `mother` from `test`.`folks` `p` join `ancestor_couple_ids` `fa` where `test`.`p`.`id` = `fa`.`h_id` union all /* select#5 */ select `test`.`p`.`id` AS `id`,`test`.`p`.`name` AS `name`,`test`.`p`.`dob` AS `dob`,`test`.`p`.`father` AS `father`,`test`.`p`.`mother` AS `mother` from `test`.`folks` `p` join `ancestor_couple_ids` `ma` where `test`.`p`.`id` = `ma`.`w_id`)/* select#1 */ select `h`.`name` AS `name`,`h`.`dob` AS `dob`,`w`.`name` AS `name`,`w`.`dob` AS `dob` from `ancestor_couple_ids` `c` join `coupled_ancestors` `h` join `coupled_ancestors` `w` where `h`.`id` = `c`.`h_id` and `w`.`id` = `c`.`w_id` +# 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; +h_id w_id +20 30 +10 9 +8 7 +NULL NULL +NULL NULL +NULL NULL +NULL 6 +NULL NULL +# 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; +name dob name dob +Dad 1970-02-02 Mom 1975-03-03 +Grandpa Bill 1940-04-05 Grandma Ann 1941-10-15 +Grandpa Ben 1940-10-21 Grandma Sally 1943-08-23 +# 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; +id name dob father mother +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 +7 Grandma Sally 1943-08-23 NULL 6 +8 Grandpa Ben 1940-10-21 NULL NULL +6 Grandgrandma Martha 1923-05-17 NULL NULL +execute stmt1; +id name dob father mother +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 +7 Grandma Sally 1943-08-23 NULL 6 +8 Grandpa Ben 1940-10-21 NULL NULL +6 Grandgrandma Martha 1923-05-17 NULL NULL +deallocate prepare stmt1; +# 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; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with recursive ancestors as (select `folks`.`id` AS `id`,`folks`.`name` AS `name`,`folks`.`dob` AS `dob`,`folks`.`father` AS `father`,`folks`.`mother` AS `mother` from `folks` where `folks`.`name` = 'Me' and `folks`.`dob` = '2000-01-01' union select `p`.`id` AS `id`,`p`.`name` AS `name`,`p`.`dob` AS `dob`,`p`.`father` AS `father`,`p`.`mother` AS `mother` from (`folks` `p` join `ancestors` `a`) where `p`.`id` = `a`.`father` or `p`.`id` = `a`.`mother`)select `ancestors`.`id` AS `id`,`ancestors`.`name` AS `name`,`ancestors`.`dob` AS `dob`,`ancestors`.`father` AS `father`,`ancestors`.`mother` AS `mother` from `ancestors` latin1 latin1_swedish_ci +select * from v1; +id name dob father mother +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 +7 Grandma Sally 1943-08-23 NULL 6 +8 Grandpa Ben 1940-10-21 NULL NULL +6 Grandgrandma Martha 1923-05-17 NULL NULL +create view v2 as +with recursive +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; +View Create View character_set_client collation_connection +v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS with recursive ancestors as (select `folks`.`id` AS `id`,`folks`.`name` AS `name`,`folks`.`dob` AS `dob`,`folks`.`father` AS `father`,`folks`.`mother` AS `mother` from `folks` where `folks`.`name` = 'Me' union select `p`.`id` AS `id`,`p`.`name` AS `name`,`p`.`dob` AS `dob`,`p`.`father` AS `father`,`p`.`mother` AS `mother` from (`folks` `p` join `ancestors` `fa`) where `p`.`id` = `fa`.`father` union select `p`.`id` AS `id`,`p`.`name` AS `name`,`p`.`dob` AS `dob`,`p`.`father` AS `father`,`p`.`mother` AS `mother` from (`folks` `p` join `ancestors` `ma`) where `p`.`id` = `ma`.`mother`)select `ancestors`.`id` AS `id`,`ancestors`.`name` AS `name`,`ancestors`.`dob` AS `dob`,`ancestors`.`father` AS `father`,`ancestors`.`mother` AS `mother` from `ancestors` latin1 latin1_swedish_ci +select * from v2; +id name dob father mother +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 +8 Grandpa Ben 1940-10-21 NULL NULL +9 Grandma Ann 1941-10-15 NULL NULL +7 Grandma Sally 1943-08-23 NULL 6 +6 Grandgrandma Martha 1923-05-17 NULL NULL +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; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 12 100.00 +2 DERIVED folks ALL NULL NULL NULL NULL 12 100.00 Using where +3 RECURSIVE UNION p ALL NULL NULL NULL NULL 12 100.00 +3 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 12 100.00 Using where; Using join buffer (flat, BNL join) +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 with recursive ancestors as (/* select#2 */ select `test`.`folks`.`id` AS `id`,`test`.`folks`.`name` AS `name`,`test`.`folks`.`dob` AS `dob`,`test`.`folks`.`father` AS `father`,`test`.`folks`.`mother` AS `mother` from `test`.`folks` where `test`.`folks`.`name` = 'Me' and `test`.`folks`.`dob` = DATE'2000-01-01' union /* select#3 */ select `p`.`id` AS `id`,`p`.`name` AS `name`,`p`.`dob` AS `dob`,`p`.`father` AS `father`,`p`.`mother` AS `mother` from `test`.`folks` `p` join `ancestors` `a` where `a`.`father` = `p`.`id` or `a`.`mother` = `p`.`id`)/* select#1 */ select `ancestors`.`id` AS `id`,`ancestors`.`name` AS `name`,`ancestors`.`dob` AS `dob`,`ancestors`.`father` AS `father`,`ancestors`.`mother` AS `mother` from `ancestors` +# 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; +id name dob father mother +20 Dad 1970-02-02 10 9 +30 Mom 1975-03-03 8 7 +10 Grandpa Bill 1940-04-05 NULL NULL +9 Grandma Ann 1941-10-15 NULL NULL +7 Grandma Sally 1943-08-23 NULL 6 +8 Grandpa Ben 1940-10-21 NULL NULL +6 Grandgrandma Martha 1923-05-17 NULL NULL +# 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; +id name dob father mother +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 +8 Grandpa Ben 1940-10-21 NULL NULL +9 Grandma Ann 1941-10-15 NULL NULL +7 Grandma Sally 1943-08-23 NULL 6 +6 Grandgrandma Martha 1923-05-17 NULL NULL +with recursive +ancestor_ids (id, generation) +as +( +select father, 1 from folks where name = '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; +ERROR HY000: Restrictions imposed on recursive definitions are violated for table 'ancestor_ids' +set standard_compliant_cte=0; +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; +ERROR HY000: WITH column list and SELECT field list have different column counts +with recursive +ancestor_ids (id, generation) +as +( +select father, 1 from folks where name = '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; +generation name +1 Dad +1 Mom +2 Grandpa Bill +2 Grandma Ann +2 Grandma Sally +2 Grandpa Ben +3 Grandgrandma Martha +set standard_compliant_cte=1; +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 HY000: Restrictions imposed on recursive definitions are violated for table 'coupled_ancestor_ids' +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; +id name dob father mother +20 Dad 1970-02-02 10 9 +30 Mom 1975-03-03 8 7 +10 Grandpa Bill 1940-04-05 NULL NULL +9 Grandma Ann 1941-10-15 NULL NULL +7 Grandma Sally 1943-08-23 NULL 6 +8 Grandpa Ben 1940-10-21 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 folks left join ancestor_ids a on folks.id = a.id +union +select mother from folks left join ancestor_ids a on folks.id = a.id +), +ancestors +as +( +select p.* from folks as p, ancestor_ids as a +where p.id = a.id +) +select * from ancestors; +ERROR HY000: Restrictions imposed on recursive definitions are violated for table 'ancestor_ids' +set statement 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; +id name dob father mother +20 Dad 1970-02-02 10 9 +30 Mom 1975-03-03 8 7 +10 Grandpa Bill 1940-04-05 NULL NULL +9 Grandma Ann 1941-10-15 NULL NULL +25 Uncle Jim 1968-11-18 8 7 +7 Grandma Sally 1943-08-23 NULL 6 +8 Grandpa Ben 1940-10-21 NULL NULL +6 Grandgrandma Martha 1923-05-17 NULL NULL +27 Auntie Melinda 1971-03-29 NULL NULL +with recursive +ancestor_ids (id, generation) +as +( +select father, 1 from folks where name = '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; +generation name +1 Dad +1 Mom +2 Grandpa Bill +2 Grandma Ann +2 Grandma Sally +2 Grandpa Ben +3 Grandgrandma Martha +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; +ERROR HY000: Restrictions imposed on recursive definitions are violated for table 'ancestor_ids' +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; +generation name +1 Dad +1 Mom +2 Grandpa Bill +2 Grandma Ann +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; +generation name +1 Dad +1 Mom +2 Grandpa Bill +2 Grandma Ann +2 Grandma Sally +2 Grandpa Ben +Warnings: +Warning 1931 Query execution was interrupted. The query exceeded max_recursive_iterations = 1. The query result may be incomplete +# 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; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 12 +2 DERIVED folks ALL NULL NULL NULL NULL 12 Using where +3 RECURSIVE UNION p ALL PRIMARY NULL NULL NULL 12 +3 RECURSIVE UNION <derived2> ref key0 key0 5 test.p.id 2 +4 RECURSIVE UNION p ALL PRIMARY NULL NULL NULL 12 +4 RECURSIVE UNION <derived2> ref key0 key0 5 test.p.id 2 +NULL UNION RESULT <union2,3,4> ALL NULL NULL NULL NULL NULL +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; +id name dob father mother +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 +8 Grandpa Ben 1940-10-21 NULL NULL +9 Grandma Ann 1941-10-15 NULL NULL +7 Grandma Sally 1943-08-23 NULL 6 +6 Grandgrandma Martha 1923-05-17 NULL NULL +# +# EXPLAIN FORMAT=JSON on a query where one recursive CTE uses another: +# +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; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived4> ALL NULL NULL NULL NULL 24 +4 DERIVED folks ALL NULL NULL NULL NULL 12 Using where +6 UNION <derived3> ALL NULL NULL NULL NULL 12 +5 RECURSIVE UNION <derived4> ALL NULL NULL NULL NULL 24 +NULL UNION RESULT <union4,6,5> ALL NULL NULL NULL NULL NULL +3 DERIVED folks ALL NULL NULL NULL NULL 12 Using where +2 RECURSIVE UNION folks ALL PRIMARY NULL NULL NULL 12 +2 RECURSIVE UNION <derived3> ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) +NULL UNION RESULT <union3,2> ALL NULL NULL NULL NULL NULL +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; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "nested_loop": [ + { + "table": { + "table_name": "<derived4>", + "access_type": "ALL", + "rows": 24, + "filtered": 100, + "materialized": { + "query_block": { + "recursive_union": { + "table_name": "<union4,6,5>", + "access_type": "ALL", + "query_specifications": [ + { + "query_block": { + "select_id": 4, + "nested_loop": [ + { + "table": { + "table_name": "folks", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "folks.`name` = 'Me2'" + } + } + ] + } + }, + { + "query_block": { + "select_id": 6, + "operation": "UNION", + "nested_loop": [ + { + "table": { + "table_name": "<derived3>", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "prev_gen.`id` < 345", + "materialized": { + "query_block": { + "recursive_union": { + "table_name": "<union3,2>", + "access_type": "ALL", + "query_specifications": [ + { + "query_block": { + "select_id": 3, + "nested_loop": [ + { + "table": { + "table_name": "folks", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "folks.`name` = 'Me'" + } + } + ] + } + }, + { + "query_block": { + "select_id": 2, + "operation": "UNION", + "nested_loop": [ + { + "table": { + "table_name": "folks", + "access_type": "ALL", + "possible_keys": ["PRIMARY"], + "rows": 12, + "filtered": 100 + } + }, + { + "block-nl-join": { + "table": { + "table_name": "<derived3>", + "access_type": "ALL", + "rows": 12, + "filtered": 100 + }, + "buffer_type": "flat", + "buffer_size": "686", + "join_type": "BNL", + "attached_condition": "prev_gen.father = folks.`id` or prev_gen.mother = folks.`id`" + } + } + ] + } + } + ] + } + } + } + } + } + ] + } + }, + { + "query_block": { + "select_id": 5, + "operation": "UNION", + "nested_loop": [ + { + "table": { + "table_name": "<derived4>", + "access_type": "ALL", + "rows": 24, + "filtered": 100, + "attached_condition": "ancestors.`id` < 234" + } + } + ] + } + } + ] + } + } + } + } + } + ] + } +} +# +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; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "nested_loop": [ + { + "table": { + "table_name": "<derived3>", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "materialized": { + "query_block": { + "recursive_union": { + "table_name": "<union3,2>", + "access_type": "ALL", + "query_specifications": [ + { + "query_block": { + "select_id": 3, + "nested_loop": [ + { + "table": { + "table_name": "v", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "v.`name` = 'Me' and v.father is not null and v.mother is not null" + } + }, + { + "table": { + "table_name": "h", + "access_type": "eq_ref", + "possible_keys": ["PRIMARY"], + "key": "PRIMARY", + "key_length": "4", + "used_key_parts": ["id"], + "ref": ["test.v.father"], + "rows": 1, + "filtered": 100 + } + }, + { + "table": { + "table_name": "w", + "access_type": "eq_ref", + "possible_keys": ["PRIMARY"], + "key": "PRIMARY", + "key_length": "4", + "used_key_parts": ["id"], + "ref": ["test.v.mother"], + "rows": 1, + "filtered": 100 + } + } + ] + } + }, + { + "query_block": { + "select_id": 2, + "operation": "UNION", + "nested_loop": [ + { + "table": { + "table_name": "<derived4>", + "access_type": "ALL", + "rows": 2, + "filtered": 100, + "attached_condition": "a.father is not null and a.mother is not null" + } + }, + { + "table": { + "table_name": "h", + "access_type": "eq_ref", + "possible_keys": ["PRIMARY"], + "key": "PRIMARY", + "key_length": "4", + "used_key_parts": ["id"], + "ref": ["a.father"], + "rows": 1, + "filtered": 100 + } + }, + { + "table": { + "table_name": "w", + "access_type": "eq_ref", + "possible_keys": ["PRIMARY"], + "key": "PRIMARY", + "key_length": "4", + "used_key_parts": ["id"], + "ref": ["a.mother"], + "rows": 1, + "filtered": 100 + } + } + ] + } + } + ] + } + } + } + } + } + ] + } +} +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; +id name dob father mother +20 Dad 1970-02-02 10 9 +30 Mom 1975-03-03 8 7 +10 Grandpa Bill 1940-04-05 NULL NULL +9 Grandma Ann 1941-10-15 NULL NULL +7 Grandma Sally 1943-08-23 NULL 6 +8 Grandpa Ben 1940-10-21 NULL NULL +6 Grandgrandma Martha 1923-05-17 NULL NULL +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; +id name dob father mother +20 Dad 1970-02-02 10 9 +30 Mom 1975-03-03 8 7 +10 Grandpa Bill 1940-04-05 NULL NULL +9 Grandma Ann 1941-10-15 NULL NULL +7 Grandma Sally 1943-08-23 NULL 6 +8 Grandpa Ben 1940-10-21 NULL NULL +6 Grandgrandma Martha 1923-05-17 NULL NULL +drop table my_ancestors; +WITH RECURSIVE +positions(i) AS ( +VALUES(0) +UNION SELECT ALL +i+1 FROM positions WHERE i < 4*4-1 +), +solutions(board, n_queens) AS ( +SELECT REPEAT('-', 4*4), 0 +FROM positions +UNION +SELECT +concat(substr(board, 1, i),'*',substr(board, i+2)),n_queens + 1 AS n_queens +FROM positions AS ps, solutions +WHERE n_queens < 4 +AND substr(board,1,i) != '*' + AND NOT EXISTS ( +SELECT 1 FROM positions WHERE +substr(board,i+1,1) = '*' AND +( +i % 4 = ps.i % 4 OR +i div 4 = ps.i div 4 OR +i div 4 + (i % 4) = ps.i div 4 + (ps.i % 4) OR +i div 4 - (i % 4) = ps.i div 4 - (ps.i % 4) +) +) +) +SELECT regexp_replace(board,concat('(',REPEAT('.', 4),')'),'\\1\n') n_queens FROM solutions WHERE n_queens = 4; +n_queens +--*- +*--- +---* +-*-- + +-*-- +---* +*--- +--*- + +# +# MDEV-10883: execution of prepared statement from SELECT +# with recursive CTE that renames columns +# +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; +id name dob father mother +20 Dad 1970-02-02 10 9 +30 Mom 1975-03-03 8 7 +10 Grandpa Bill 1940-04-05 NULL NULL +9 Grandma Ann 1941-10-15 NULL NULL +7 Grandma Sally 1943-08-23 NULL 6 +8 Grandpa Ben 1940-10-21 NULL NULL +6 Grandgrandma Martha 1923-05-17 NULL NULL +deallocate prepare stmt; +# +# MDEV-10881: execution of prepared statement from +# CREATE ... SELECT, INSERT ... SELECT +# +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; +id name dob father mother +20 Dad 1970-02-02 10 9 +30 Mom 1975-03-03 8 7 +10 Grandpa Bill 1940-04-05 NULL NULL +9 Grandma Ann 1941-10-15 NULL NULL +7 Grandma Sally 1943-08-23 NULL 6 +8 Grandpa Ben 1940-10-21 NULL NULL +6 Grandgrandma Martha 1923-05-17 NULL NULL +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; +id name dob father mother +20 Dad 1970-02-02 10 9 +30 Mom 1975-03-03 8 7 +10 Grandpa Bill 1940-04-05 NULL NULL +9 Grandma Ann 1941-10-15 NULL NULL +7 Grandma Sally 1943-08-23 NULL 6 +8 Grandpa Ben 1940-10-21 NULL NULL +6 Grandgrandma Martha 1923-05-17 NULL NULL +drop table my_ancestors; +# +# MDEV-10933: WITH clause together with SELECT in parenthesis +# CREATE SELECT +# +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; +id name dob father mother +20 Dad 1970-02-02 10 9 +30 Mom 1975-03-03 8 7 +10 Grandpa Bill 1940-04-05 NULL NULL +9 Grandma Ann 1941-10-15 NULL NULL +7 Grandma Sally 1943-08-23 NULL 6 +8 Grandpa Ben 1940-10-21 NULL NULL +6 Grandgrandma Martha 1923-05-17 NULL NULL +drop table my_ancestors; +drop table folks; +# +# MDEV-10372: [bb-10.2-mdev9864 tree] EXPLAIN with recursive CTE enters endless recursion +# +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; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "nested_loop": [ + { + "table": { + "table_name": "<derived2>", + "access_type": "ALL", + "rows": 10, + "filtered": 100, + "materialized": { + "query_block": { + "recursive_union": { + "table_name": "<union2,3>", + "access_type": "ALL", + "query_specifications": [ + { + "query_block": { + "select_id": 2, + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 10, + "filtered": 100 + } + } + ] + } + }, + { + "query_block": { + "select_id": 3, + "operation": "UNION", + "nested_loop": [ + { + "table": { + "table_name": "<derived2>", + "access_type": "ALL", + "rows": 10, + "filtered": 100, + "attached_condition": "t.a < 1000" + } + } + ] + } + } + ] + } + } + } + } + } + ] + } +} +drop table t1; +# +# MDEV-10737: recursive union with several anchors at the end +# +WITH RECURSIVE cte(n) AS +( SELECT n+1 FROM cte WHERE n < 5 UNION SELECT 1 UNION SELECT 1 ) +SELECT * FROM cte; +n +1 +2 +3 +4 +5 +# +# MDEV-10736: recursive definition with anchor over a table with blob +# +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; +f +DROP TABLE t1; +# +# MDEV-10899: mergeable derived in the spec of recursive CTE +# +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; +a +3 +4 +5 +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; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 5 +2 DERIVED t2 ALL NULL NULL NULL NULL 5 Using where +4 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 5 +4 RECURSIVE UNION t2 ALL NULL NULL NULL NULL 5 Using where; Using join buffer (flat, BNL join) +NULL UNION RESULT <union2,4> ALL NULL NULL NULL NULL NULL +drop table t1,t2; +# +# MDEV-11278: non-mergeable view in the spec of recursive CTE +# +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; +a +1 +2 +drop view v1; +drop table t1,t2; +# +# MDEV-11259: recursive CTE with concatenation operation +# +DROP TABLE IF EXISTS edges; +Warnings: +Note 1051 Unknown table 'test.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; +Warnings: +Note 1051 Unknown table 'test.edges2' +CREATE VIEW edges2 (a, b) AS +SELECT a, b FROM edges UNION ALL SELECT b, a FROM edges; +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; +a b distance path_string +1 3 1 1.3. +1 4 2 1.3.4. +1 5 2 1.3.5. +1 6 2 1.3.6. +1 6 3 1.3.5.6. +1 7 3 1.3.4.7. +2 1 1 2.1. +2 3 2 2.1.3. +2 4 1 2.4. +2 4 3 2.1.3.4. +2 5 3 2.1.3.5. +2 6 3 2.1.3.6. +2 6 4 2.1.3.5.6. +2 7 2 2.4.7. +2 7 4 2.1.3.4.7. +3 1 2 3.5.1. +3 1 2 3.6.1. +3 1 3 3.5.6.1. +3 4 1 3.4. +3 5 1 3.5. +3 6 1 3.6. +3 6 2 3.5.6. +3 7 2 3.4.7. +4 7 1 4.7. +5 1 1 5.1. +5 1 2 5.6.1. +5 3 2 5.1.3. +5 3 3 5.6.1.3. +5 4 3 5.1.3.4. +5 4 4 5.6.1.3.4. +5 6 1 5.6. +5 6 3 5.1.3.6. +5 7 4 5.1.3.4.7. +5 7 5 5.6.1.3.4.7. +6 1 1 6.1. +6 3 2 6.1.3. +6 4 3 6.1.3.4. +6 5 3 6.1.3.5. +6 7 4 6.1.3.4.7. +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; +a b distance path_string +1 6 2 1.3.6. +1 6 3 1.3.5.6. +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; +a b distance path_string +1 2 1 1.2. +1 2 3 1.3.4.2. +1 2 4 1.5.3.4.2. +1 2 4 1.6.3.4.2. +1 2 5 1.5.6.3.4.2. +1 2 5 1.6.5.3.4.2. +1 3 1 1.3. +1 3 2 1.5.3. +1 3 2 1.6.3. +1 3 3 1.2.4.3. +1 3 3 1.5.6.3. +1 3 3 1.6.5.3. +1 4 2 1.2.4. +1 4 2 1.3.4. +1 4 3 1.5.3.4. +1 4 3 1.6.3.4. +1 4 4 1.5.6.3.4. +1 4 4 1.6.5.3.4. +1 5 1 1.5. +1 5 2 1.3.5. +1 5 2 1.6.5. +1 5 3 1.3.6.5. +1 5 3 1.6.3.5. +1 5 4 1.2.4.3.5. +1 5 5 1.2.4.3.6.5. +1 6 1 1.6. +1 6 2 1.3.6. +1 6 2 1.5.6. +1 6 3 1.3.5.6. +1 6 3 1.5.3.6. +1 6 4 1.2.4.3.6. +1 6 5 1.2.4.3.5.6. +1 7 3 1.2.4.7. +1 7 3 1.3.4.7. +1 7 4 1.5.3.4.7. +1 7 4 1.6.3.4.7. +1 7 5 1.5.6.3.4.7. +1 7 5 1.6.5.3.4.7. +2 1 1 2.1. +2 1 3 2.4.3.1. +2 1 4 2.4.3.5.1. +2 1 4 2.4.3.6.1. +2 1 5 2.4.3.5.6.1. +2 1 5 2.4.3.6.5.1. +2 3 2 2.1.3. +2 3 2 2.4.3. +2 3 3 2.1.5.3. +2 3 3 2.1.6.3. +2 3 4 2.1.5.6.3. +2 3 4 2.1.6.5.3. +2 4 1 2.4. +2 4 3 2.1.3.4. +2 4 4 2.1.5.3.4. +2 4 4 2.1.6.3.4. +2 4 5 2.1.5.6.3.4. +2 4 5 2.1.6.5.3.4. +2 5 2 2.1.5. +2 5 3 2.1.3.5. +2 5 3 2.1.6.5. +2 5 3 2.4.3.5. +2 5 4 2.1.3.6.5. +2 5 4 2.1.6.3.5. +2 5 4 2.4.3.1.5. +2 5 4 2.4.3.6.5. +2 5 5 2.4.3.1.6.5. +2 5 5 2.4.3.6.1.5. +2 6 2 2.1.6. +2 6 3 2.1.3.6. +2 6 3 2.1.5.6. +2 6 3 2.4.3.6. +2 6 4 2.1.3.5.6. +2 6 4 2.1.5.3.6. +2 6 4 2.4.3.1.6. +2 6 4 2.4.3.5.6. +2 6 5 2.4.3.1.5.6. +2 6 5 2.4.3.5.1.6. +2 7 2 2.4.7. +2 7 4 2.1.3.4.7. +2 7 5 2.1.5.3.4.7. +2 7 5 2.1.6.3.4.7. +2 7 6 2.1.5.6.3.4.7. +2 7 6 2.1.6.5.3.4.7. +3 1 1 3.1. +3 1 2 3.5.1. +3 1 2 3.6.1. +3 1 3 3.4.2.1. +3 1 3 3.5.6.1. +3 1 3 3.6.5.1. +3 2 2 3.1.2. +3 2 2 3.4.2. +3 2 3 3.5.1.2. +3 2 3 3.6.1.2. +3 2 4 3.5.6.1.2. +3 2 4 3.6.5.1.2. +3 4 1 3.4. +3 4 3 3.1.2.4. +3 4 4 3.5.1.2.4. +3 4 4 3.6.1.2.4. +3 4 5 3.5.6.1.2.4. +3 4 5 3.6.5.1.2.4. +3 5 1 3.5. +3 5 2 3.1.5. +3 5 2 3.6.5. +3 5 3 3.1.6.5. +3 5 3 3.6.1.5. +3 5 4 3.4.2.1.5. +3 5 5 3.4.2.1.6.5. +3 6 1 3.6. +3 6 2 3.1.6. +3 6 2 3.5.6. +3 6 3 3.1.5.6. +3 6 3 3.5.1.6. +3 6 4 3.4.2.1.6. +3 6 5 3.4.2.1.5.6. +3 7 2 3.4.7. +3 7 4 3.1.2.4.7. +3 7 5 3.5.1.2.4.7. +3 7 5 3.6.1.2.4.7. +3 7 6 3.5.6.1.2.4.7. +3 7 6 3.6.5.1.2.4.7. +4 1 2 4.2.1. +4 1 2 4.3.1. +4 1 3 4.3.5.1. +4 1 3 4.3.6.1. +4 1 4 4.3.5.6.1. +4 1 4 4.3.6.5.1. +4 2 1 4.2. +4 2 3 4.3.1.2. +4 2 4 4.3.5.1.2. +4 2 4 4.3.6.1.2. +4 2 5 4.3.5.6.1.2. +4 2 5 4.3.6.5.1.2. +4 3 1 4.3. +4 3 3 4.2.1.3. +4 3 4 4.2.1.5.3. +4 3 4 4.2.1.6.3. +4 3 5 4.2.1.5.6.3. +4 3 5 4.2.1.6.5.3. +4 5 2 4.3.5. +4 5 3 4.2.1.5. +4 5 3 4.3.1.5. +4 5 3 4.3.6.5. +4 5 4 4.2.1.3.5. +4 5 4 4.2.1.6.5. +4 5 4 4.3.1.6.5. +4 5 4 4.3.6.1.5. +4 5 5 4.2.1.3.6.5. +4 5 5 4.2.1.6.3.5. +4 6 2 4.3.6. +4 6 3 4.2.1.6. +4 6 3 4.3.1.6. +4 6 3 4.3.5.6. +4 6 4 4.2.1.3.6. +4 6 4 4.2.1.5.6. +4 6 4 4.3.1.5.6. +4 6 4 4.3.5.1.6. +4 6 5 4.2.1.3.5.6. +4 6 5 4.2.1.5.3.6. +4 7 1 4.7. +5 1 1 5.1. +5 1 2 5.3.1. +5 1 2 5.6.1. +5 1 3 5.3.6.1. +5 1 3 5.6.3.1. +5 1 4 5.3.4.2.1. +5 1 5 5.6.3.4.2.1. +5 2 2 5.1.2. +5 2 3 5.3.1.2. +5 2 3 5.3.4.2. +5 2 3 5.6.1.2. +5 2 4 5.1.3.4.2. +5 2 4 5.3.6.1.2. +5 2 4 5.6.3.1.2. +5 2 4 5.6.3.4.2. +5 2 5 5.1.6.3.4.2. +5 2 5 5.6.1.3.4.2. +5 3 1 5.3. +5 3 2 5.1.3. +5 3 2 5.6.3. +5 3 3 5.1.6.3. +5 3 3 5.6.1.3. +5 3 4 5.1.2.4.3. +5 3 5 5.6.1.2.4.3. +5 4 2 5.3.4. +5 4 3 5.1.2.4. +5 4 3 5.1.3.4. +5 4 3 5.6.3.4. +5 4 4 5.1.6.3.4. +5 4 4 5.3.1.2.4. +5 4 4 5.6.1.2.4. +5 4 4 5.6.1.3.4. +5 4 5 5.3.6.1.2.4. +5 4 5 5.6.3.1.2.4. +5 6 1 5.6. +5 6 2 5.1.6. +5 6 2 5.3.6. +5 6 3 5.1.3.6. +5 6 3 5.3.1.6. +5 6 5 5.1.2.4.3.6. +5 6 5 5.3.4.2.1.6. +5 7 3 5.3.4.7. +5 7 4 5.1.2.4.7. +5 7 4 5.1.3.4.7. +5 7 4 5.6.3.4.7. +5 7 5 5.1.6.3.4.7. +5 7 5 5.3.1.2.4.7. +5 7 5 5.6.1.2.4.7. +5 7 5 5.6.1.3.4.7. +5 7 6 5.3.6.1.2.4.7. +5 7 6 5.6.3.1.2.4.7. +6 1 1 6.1. +6 1 2 6.3.1. +6 1 2 6.5.1. +6 1 3 6.3.5.1. +6 1 3 6.5.3.1. +6 1 4 6.3.4.2.1. +6 1 5 6.5.3.4.2.1. +6 2 2 6.1.2. +6 2 3 6.3.1.2. +6 2 3 6.3.4.2. +6 2 3 6.5.1.2. +6 2 4 6.1.3.4.2. +6 2 4 6.3.5.1.2. +6 2 4 6.5.3.1.2. +6 2 4 6.5.3.4.2. +6 2 5 6.1.5.3.4.2. +6 2 5 6.5.1.3.4.2. +6 3 1 6.3. +6 3 2 6.1.3. +6 3 2 6.5.3. +6 3 3 6.1.5.3. +6 3 3 6.5.1.3. +6 3 4 6.1.2.4.3. +6 3 5 6.5.1.2.4.3. +6 4 2 6.3.4. +6 4 3 6.1.2.4. +6 4 3 6.1.3.4. +6 4 3 6.5.3.4. +6 4 4 6.1.5.3.4. +6 4 4 6.3.1.2.4. +6 4 4 6.5.1.2.4. +6 4 4 6.5.1.3.4. +6 4 5 6.3.5.1.2.4. +6 4 5 6.5.3.1.2.4. +6 5 1 6.5. +6 5 2 6.1.5. +6 5 2 6.3.5. +6 5 3 6.1.3.5. +6 5 3 6.3.1.5. +6 5 5 6.1.2.4.3.5. +6 5 5 6.3.4.2.1.5. +6 7 3 6.3.4.7. +6 7 4 6.1.2.4.7. +6 7 4 6.1.3.4.7. +6 7 4 6.5.3.4.7. +6 7 5 6.1.5.3.4.7. +6 7 5 6.3.1.2.4.7. +6 7 5 6.5.1.2.4.7. +6 7 5 6.5.1.3.4.7. +6 7 6 6.3.5.1.2.4.7. +6 7 6 6.5.3.1.2.4.7. +7 1 3 7.4.2.1. +7 1 3 7.4.3.1. +7 1 4 7.4.3.5.1. +7 1 4 7.4.3.6.1. +7 1 5 7.4.3.5.6.1. +7 1 5 7.4.3.6.5.1. +7 2 2 7.4.2. +7 2 4 7.4.3.1.2. +7 2 5 7.4.3.5.1.2. +7 2 5 7.4.3.6.1.2. +7 2 6 7.4.3.5.6.1.2. +7 2 6 7.4.3.6.5.1.2. +7 3 2 7.4.3. +7 3 4 7.4.2.1.3. +7 3 5 7.4.2.1.5.3. +7 3 5 7.4.2.1.6.3. +7 3 6 7.4.2.1.5.6.3. +7 3 6 7.4.2.1.6.5.3. +7 4 1 7.4. +7 5 3 7.4.3.5. +7 5 4 7.4.2.1.5. +7 5 4 7.4.3.1.5. +7 5 4 7.4.3.6.5. +7 5 5 7.4.2.1.3.5. +7 5 5 7.4.2.1.6.5. +7 5 5 7.4.3.1.6.5. +7 5 5 7.4.3.6.1.5. +7 5 6 7.4.2.1.3.6.5. +7 5 6 7.4.2.1.6.3.5. +7 6 3 7.4.3.6. +7 6 4 7.4.2.1.6. +7 6 4 7.4.3.1.6. +7 6 4 7.4.3.5.6. +7 6 5 7.4.2.1.3.6. +7 6 5 7.4.2.1.5.6. +7 6 5 7.4.3.1.5.6. +7 6 5 7.4.3.5.1.6. +7 6 6 7.4.2.1.3.5.6. +7 6 6 7.4.2.1.5.3.6. +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; +a b dist +1 2 1 +1 3 1 +1 4 2 +1 5 1 +1 6 1 +1 7 3 +2 1 1 +2 3 2 +2 4 1 +2 5 2 +2 6 2 +2 7 2 +3 1 1 +3 2 2 +3 4 1 +3 5 1 +3 6 1 +3 7 2 +4 1 2 +4 2 1 +4 3 1 +4 5 2 +4 6 2 +4 7 1 +5 1 1 +5 2 2 +5 3 1 +5 4 2 +5 6 1 +5 7 3 +6 1 1 +6 2 2 +6 3 1 +6 4 2 +6 5 1 +6 7 3 +7 1 3 +7 2 2 +7 3 2 +7 4 1 +7 5 3 +7 6 3 +DROP VIEW edges2; +DROP TABLE edges; +# +# MDEV-11674: recursive CTE table that cannot be stored +# in a heap table +# +create table t1 (id int, test_data varchar(36)); +set @save_max_recursive_iterations= @@max_recursive_iterations; +set max_recursive_iterations= 200000; +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; +set max_recursive_iterations= @save_max_recursive_iterations; +# +# MDEV-10773: ANALYZE for query with recursive CTE +# +analyze format=json +with recursive src(counter) as +(select 1 +union +select counter+1 from src where counter<10 +) select * from src; +ANALYZE +{ + "query_optimization": { + "r_total_time_ms": "REPLACED" + }, + "query_block": { + "select_id": 1, + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "nested_loop": [ + { + "table": { + "table_name": "<derived2>", + "access_type": "ALL", + "r_loops": 1, + "rows": 2, + "r_rows": 10, + "r_table_time_ms": "REPLACED", + "r_other_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100, + "materialized": { + "query_block": { + "recursive_union": { + "table_name": "<union2,3>", + "access_type": "ALL", + "r_loops": 0, + "r_rows": null, + "query_specifications": [ + { + "query_block": { + "select_id": 2, + "table": { + "message": "No tables used" + } + } + }, + { + "query_block": { + "select_id": 3, + "operation": "UNION", + "r_loops": 10, + "r_total_time_ms": "REPLACED", + "nested_loop": [ + { + "table": { + "table_name": "<derived2>", + "access_type": "ALL", + "r_loops": 10, + "rows": 2, + "r_rows": 1, + "r_table_time_ms": "REPLACED", + "r_other_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 90, + "attached_condition": "src.counter < 10" + } + } + ] + } + } + ] + } + } + } + } + } + ] + } +} +# +# mdev-12360: recursive reference in left operand of LEFT JOIN +# +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; +id name dob father mother +20 Dad 1970-02-02 10 9 +30 Mom 1975-03-03 8 7 +10 Grandpa Bill 1940-04-05 NULL NULL +9 Grandma Ann 1941-10-15 NULL NULL +7 Grandma Sally 1943-08-23 NULL 6 +8 Grandpa Ben 1940-10-21 NULL NULL +6 Grandgrandma Martha 1923-05-17 NULL NULL +drop table folks; +# +# mdev-12368: crash with mutually recursive CTE +# that arenot Standard compliant +# +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)); +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; +ERROR HY000: Restrictions imposed on recursive definitions are violated for table 'applied_modules' +drop table value_nodes, module_nodes, module_arguments, module_results; +# +# mdev-12375: query using one of two mutually recursive CTEs +# whose non-recursive part returns an empty set +# +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; +m +m1 +m2 +Warnings: +Warning 1931 Query execution was interrupted. The query exceeded max_recursive_iterations = 2. The query result may be incomplete +drop table value_nodes, module_nodes, module_arguments, module_results; +# +# mdev-12519: recursive references in subqueries +# +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; +p +p1 +p2 +p3 +p7 +p4 +p8 +p5 +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; +p +p1 +p2 +p3 +p7 +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; +p +p1 +p2 +p3 +p7 +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; +p +p1 +p2 +p3 +p7 +execute stmt; +p +p1 +p2 +p3 +p7 +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; +v m +v3 init +v7 init +v9 init +v4 m1 +v1 m2 +v6 m2 +v10 m3 +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; +v m +v3 init +v7 init +v9 init +v4 m1 +v1 m2 +v6 m2 +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; +v m +v3 init +v7 init +v9 init +v4 m1 +v1 m2 +v6 m2 +execute stmt; +v m +v3 init +v7 init +v9 init +v4 m1 +v1 m2 +v6 m2 +deallocate prepare stmt; +drop table objects, modules, module_arguments, module_results; +set standard_compliant_cte=default; +select @@standard_compliant_cte; +@@standard_compliant_cte +1 +# +# mdev-12554: impossible where in recursive select +# +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; +f +1 +2 +DROP TABLE t1; +# +# mdev-12556: recursive execution uses Aria temporary tables +# +CREATE TABLE t (c1 varchar(255), c2 tinytext); +INSERT INTO t VALUES ('a','a'),('b','b'),('c','c'),('d','d'); +ANALYZE WITH RECURSIVE cte(f) AS ( +SELECT c1 FROM t +UNION +SELECT c1 FROM t, cte +) SELECT COUNT(*) FROM cte; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 4 4.00 100.00 100.00 +2 DERIVED t ALL NULL NULL NULL NULL 4 4.00 100.00 100.00 +3 RECURSIVE UNION t ALL NULL NULL NULL NULL 4 4.00 100.00 100.00 +3 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 4 4.00 100.00 100.00 Using join buffer (flat, BNL join) +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL 0.00 NULL NULL +WITH RECURSIVE cte(f) AS ( +SELECT c1 FROM t +UNION +SELECT c1 FROM t, cte +) SELECT COUNT(*) FROM cte; +COUNT(*) +4 +ANALYZE WITH RECURSIVE cte(f) AS ( +SELECT c2 FROM t +UNION +SELECT c2 FROM t, cte +) SELECT COUNT(*) FROM cte; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 4 4.00 100.00 100.00 +2 DERIVED t ALL NULL NULL NULL NULL 4 4.00 100.00 100.00 +3 RECURSIVE UNION t ALL NULL NULL NULL NULL 4 4.00 100.00 100.00 +3 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 4 4.00 100.00 100.00 Using join buffer (flat, BNL join) +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL 0.00 NULL NULL +WITH RECURSIVE cte(f) AS ( +SELECT c2 FROM t +UNION +SELECT c2 FROM t, cte +) SELECT COUNT(*) FROM cte; +COUNT(*) +4 +DROP TABLE t; +# +# mdev-12563: no recursive references on the top level of the CTE spec +# +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; +f +3 +1 +2 +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; +f +3 +1 +2 +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; +f +3 +1 +2 +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; +f +3 +1 +2 +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; +f +3 +1 +2 +set standard_compliant_cte=default; +DROP TABLE t; +# +# mdev-14184: recursive CTE embedded into CTE with multiple references +# +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; +n +1 +2 +3 +# +# mdev-14629: a user-defined variable is defined by the recursive CTE +# +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; +@var +3 +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; +@var +27 +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 +); +ERROR HY000: Unacceptable mutual recursion with anchored table 'cte_1' +drop table t1; +# +# mdev-14777: crash caused by the same as in mdev-14755 +# +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; +C P i +drop table t1,t2,t3; +# +# mdev-14879: subquery with recursive reference in WHERE of CTE +# +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; +city +Paris +Chicago +New York +Montreal +Seattle +Frankfurt +Los Angeles +Moscow +Tokyo +set standard_compliant_cte=0; +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; +city legs +Paris 1 +Chicago 2 +New York 3 +Montreal 3 +Seattle 4 +Frankfurt 5 +Los Angeles 5 +Moscow 6 +Tokyo 6 +explain extended 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; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 16 100.00 +2 DERIVED a ALL NULL NULL NULL NULL 16 100.00 Using where +3 RECURSIVE UNION b ALL NULL NULL NULL NULL 16 100.00 Using where +3 RECURSIVE UNION <derived2> ref key0 key0 35 test.b.departure 2 100.00 +4 DEPENDENT SUBQUERY <derived2> ALL NULL NULL NULL NULL 16 100.00 Using where +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 with recursive destinations(`city`,`legs`) as (/* select#2 */ select `test`.`a`.`arrival` AS `city`,1 AS `legs` from `test`.`flights` `a` where `test`.`a`.`departure` = 'Cairo' union /* select#3 */ select `test`.`b`.`arrival` AS `arrival`,`r`.`legs` + 1 AS `r.legs + 1` from `destinations` `r` join `test`.`flights` `b` where `r`.`city` = `test`.`b`.`departure` and !(<in_optimizer>(`test`.`b`.`arrival`,<exists>(/* select#4 */ select `destinations`.`city` from `destinations` where trigcond(`test`.`b`.`arrival` = `destinations`.`city` or `destinations`.`city` is null) having trigcond(`destinations`.`city` is null)))))/* select#1 */ select `destinations`.`city` AS `city`,`destinations`.`legs` AS `legs` from `destinations` +set standard_compliant_cte=default; +drop table flights; +# +# MDEV-15162: Setting user variable in recursive CTE +# +SET @c=1; +WITH RECURSIVE cte AS +(SELECT 5 +UNION +SELECT @c:=@c+1 FROM cte WHERE @c<3) +SELECT * FROM cte; +5 +5 +2 +3 +# +# MDEV-15575: using recursive cte with big_tables enabled +# +set tmp_memory_table_size=0; +with recursive qn as +(select 123 as a union all select 1+a from qn where a<130) +select * from qn; +a +123 +124 +125 +126 +127 +128 +129 +130 +set tmp_memory_table_size=default; +# +# MDEV-15571: using recursive cte with big_tables enabled +# +create table t1 (a bigint); +insert into t1 values(1); +set tmp_memory_table_size=0; +with recursive qn as +( +select a from t1 +union all +select a*2000 from qn where a<10000000000000000000 +) +select * from qn; +ERROR 22003: BIGINT value is out of range in '`qn`.`a` * 2000' +set tmp_memory_table_size=default; +drop table t1; +# +# MDEV-15556: using recursive cte with big_tables enabled +# when recursive tables are accessed by key +# +set tmp_memory_table_size=0; +CREATE TABLE t1 (id int, name char(10), leftpar int, rightpar int); +INSERT INTO t1 VALUES +(1, "A", 2, 3), (2, "LA", 4, 5), (4, "LLA", 6, 7), +(6, "LLLA", NULL, NULL), (7, "RLLA", NULL, NULL), (5, "RLA", 8, 9), +(8, "LRLA", NULL, NULL), (9, "RRLA", NULL, NULL), (3, "RA", 10, 11), +(10, "LRA", 12, 13), (11, "RRA", 14, 15), (15, "RRRA", NULL, NULL), +(16, "B", 17, 18), (17, "LB", NULL, NULL), (18, "RB", NULL, NULL); +CREATE TABLE t2 SELECT * FROM t1 ORDER BY rand(); +WITH RECURSIVE tree_of_a AS +(SELECT *, cast(id AS char(200)) AS path FROM t2 WHERE name="A" + UNION ALL +SELECT t2.*, concat(tree_of_a.path,",",t2.id) +FROM t2 JOIN tree_of_a ON t2.id=tree_of_a.leftpar +UNION ALL +SELECT t2.*, concat(tree_of_a.path,",",t2.id) +FROM t2 JOIN tree_of_a ON t2.id=tree_of_a.rightpar) +SELECT * FROM tree_of_a +ORDER BY path; +id name leftpar rightpar path +1 A 2 3 1 +2 LA 4 5 1,2 +4 LLA 6 7 1,2,4 +6 LLLA NULL NULL 1,2,4,6 +7 RLLA NULL NULL 1,2,4,7 +5 RLA 8 9 1,2,5 +8 LRLA NULL NULL 1,2,5,8 +9 RRLA NULL NULL 1,2,5,9 +3 RA 10 11 1,3 +10 LRA 12 13 1,3,10 +11 RRA 14 15 1,3,11 +15 RRRA NULL NULL 1,3,11,15 +EXPLAIN WITH RECURSIVE tree_of_a AS +(SELECT *, cast(id AS char(200)) AS path FROM t2 WHERE name="A" + UNION ALL +SELECT t2.*, concat(tree_of_a.path,",",t2.id) +FROM t2 JOIN tree_of_a ON t2.id=tree_of_a.leftpar +UNION ALL +SELECT t2.*, concat(tree_of_a.path,",",t2.id) +FROM t2 JOIN tree_of_a ON t2.id=tree_of_a.rightpar) +SELECT * FROM tree_of_a +ORDER BY path; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 15 Using filesort +2 DERIVED t2 ALL NULL NULL NULL NULL 15 Using where +3 RECURSIVE UNION t2 ALL NULL NULL NULL NULL 15 Using where +3 RECURSIVE UNION <derived2> ref key0 key0 5 test.t2.id 2 +4 RECURSIVE UNION t2 ALL NULL NULL NULL NULL 15 Using where +4 RECURSIVE UNION <derived2> ref key0 key0 5 test.t2.id 2 +NULL UNION RESULT <union2,3,4> ALL NULL NULL NULL NULL NULL +DROP TABLE t1,t2; +set tmp_memory_table_size=default; +# +# MDEV-15840: recursive tables are accessed by key +# (the same problem as for MDEV-15556) +# +CREATE TABLE t1 (p1 text,k2 int, p2 text, k1 int); +INSERT INTO t1 select seq, seq, seq, seq from seq_1_to_1000; +CREATE PROCEDURE getNums() +BEGIN +WITH RECURSIVE cte as +( +SELECT * FROM t1 +UNION +SELECT c.* FROM t1 c JOIN cte p ON c.p1 = p.p2 AND c.k2 = p.k1 +) +SELECT * FROM cte LIMIT 10; +END | +call getNums(); +p1 k2 p2 k1 +1 1 1 1 +2 2 2 2 +3 3 3 3 +4 4 4 4 +5 5 5 5 +6 6 6 6 +7 7 7 7 +8 8 8 8 +9 9 9 9 +10 10 10 10 +DROP PROCEDURE getNums; +DROP TABLE t1; +# +# MDEV-15894: aggregate/winfow functions in non-recorsive part +# +create table t1(b int); +insert into t1 values(10),(20),(10); +with recursive qn as +(select max(b) as a from t1 union +select a from qn) +select * from qn; +a +20 +with recursive qn as +(select rank() over (order by b) as a from t1 union +select a from qn) +select * from qn; +a +1 +3 +drop table t1; +# +# MDEV-16086: tmp table for CTE is created as ARIA tables +# +CREATE TABLE t1 ( +Id int(11) not null AUTO_INCREMENT, +Parent varchar(15) not null, +Child varchar(15) not null, +PRIMARY KEY (Id) +) ENGINE = MyISAM; +INSERT INTO t1 (Parent, Child) VALUES +('123', '456'),('456', '789'),('321', '654'),('654', '987'); +WITH RECURSIVE cte AS +( SELECT b.Parent, +b.Child, +CAST(CONCAT(b.Child,',') AS CHAR(513)) Path +FROM t1 b +LEFT OUTER JOIN t1 bc ON b.Child = bc.Parent +WHERE bc.Id IS NULL +UNION ALL SELECT c.Parent, +c.Child, +CONCAT(p.Path,c.Child,',') Path +FROM t1 c +INNER JOIN cte p ON c.Child = p.Parent) +SELECT * +FROM cte +ORDER BY Path; +Parent Child Path +456 789 789, +123 456 789,456, +654 987 987, +321 654 987,654, +DROP TABLE t1; +# +# MDEV-16212: recursive CTE with global ORDER BY +# +set statement max_recursive_iterations = 2 for +WITH RECURSIVE qn AS ( +SELECT 1 FROM dual UNION ALL +SELECT 1 FROM qn +ORDER BY (SELECT * FROM qn)) +SELECT count(*) FROM qn; +ERROR 42000: This version of MariaDB doesn't yet support 'global ORDER_BY/LIMIT in recursive CTE spec' +# +# MDEV-15581: mix of ALL and DISTINCT UNION in recursive CTE +# +create table t1(a int); +insert into t1 values(1),(2); +insert into t1 values(1),(2); +set @c=0, @d=0; +WITH RECURSIVE qn AS +( +select 1,0 as col from t1 +union distinct +select 1,0 from t1 +union all +select 3, 0*(@c:=@c+1) from qn where @c<1 +union all +select 3, 0*(@d:=@d+1) from qn where @d<1 +) +select * from qn; +ERROR 42000: This version of MariaDB doesn't yet support 'mix of ALL and DISTINCT UNION operations in recursive CTE spec' +drop table t1; +# +# MDEV-16629: function with recursive CTE using a base table +# +CREATE TABLE t1 (id int); +INSERT INTO t1 VALUES (0), (1),(2); +WITH recursive cte AS +(SELECT id FROM t1 UNION SELECT 3 FROM cte) +SELECT count(id) FROM cte; +count(id) +4 +CREATE OR REPLACE FUNCTION func() RETURNS int +RETURN +( +WITH recursive cte AS +(SELECT id FROM t1 UNION SELECT 3 FROM cte) +SELECT count(id) FROM cte +); +SELECT func(); +func() +4 +DROP FUNCTION func; +DROP TABLE t1; +# +# MDEV-16661: function with recursive CTE using no base tables +# (fixed by the patch for MDEV-16629) +# +CREATE OR REPLACE FUNCTION func() RETURNS int +RETURN +( +WITH RECURSIVE cte AS +(SELECT 1 as id UNION SELECT * FROM cte) +SELECT count(id) FROM cte +); +SELECT func(); +func() +1 +DROP FUNCTION func; +# +# MDEV-17024: two materialized CTEs using the same recursive CTE +# +create table t1 (id int); +insert into t1 values (1), (2), (3); +with recursive +rcte(a) as +(select 1 union select cast(a+1 as unsigned) from rcte where a < 10), +cte1 as +(select count(*) as c1 from rcte,t1 where a between 3 and 5 and id=a-3), +cte2 as +(select count(*) as c2 from rcte,t1 where a between 7 and 8 and id=a-7) +select * from cte1, cte2; +c1 c2 +2 1 +explain extended with recursive +rcte(a) as +(select 1 union select cast(a+1 as unsigned) from rcte where a < 10), +cte1 as +(select count(*) as c1 from rcte,t1 where a between 3 and 5 and id=a-3), +cte2 as +(select count(*) as c2 from rcte,t1 where a between 7 and 8 and id=a-7) +select * from cte1, cte2; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <derived4> ALL NULL NULL NULL NULL 6 100.00 +1 PRIMARY <derived5> ALL NULL NULL NULL NULL 6 100.00 Using join buffer (flat, BNL join) +2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +3 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL +4 DERIVED <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where +4 DERIVED t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) +5 DERIVED <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where +5 DERIVED t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 with recursive rcte(`a`) as (/* select#2 */ select 1 AS `a` union /* select#3 */ select cast(`rcte`.`a` + 1 as unsigned) AS `cast(a+1 as unsigned)` from `rcte` where `rcte`.`a` < 10), cte1 as (/* select#4 */ select count(0) AS `c1` from `rcte` join `test`.`t1` where `rcte`.`a` between 3 and 5 and `test`.`t1`.`id` = `rcte`.`a` - 3), cte2 as (/* select#5 */ select count(0) AS `c2` from `rcte` join `test`.`t1` where `rcte`.`a` between 7 and 8 and `test`.`t1`.`id` = `rcte`.`a` - 7)/* select#1 */ select `cte1`.`c1` AS `c1`,`cte2`.`c2` AS `c2` from `cte1` join `cte2` +prepare stmt from "with recursive +rcte(a) as +(select 1 union select cast(a+1 as unsigned) from rcte where a < 10), +cte1 as +(select count(*) as c1 from rcte,t1 where a between 3 and 5 and id=a-3), +cte2 as +(select count(*) as c2 from rcte,t1 where a between 7 and 8 and id=a-7) +select * from cte1, cte2"; +execute stmt; +c1 c2 +2 1 +execute stmt; +c1 c2 +2 1 +create table t2 (c1 int, c2 int); +create procedure p() insert into t2 with recursive +rcte(a) as +(select 1 union select cast(a+1 as unsigned) from rcte where a < 10), +cte1 as +(select count(*) as c1 from rcte,t1 where a between 3 and 5 and id=a-3), +cte2 as +(select count(*) as c2 from rcte,t1 where a between 7 and 8 and id=a-7) +select * from cte1, cte2; +call p(); +select * from t2; +c1 c2 +2 1 +with recursive +rcte(a) as +(select 1 union select cast(a+1 as unsigned) from rcte where a < 10), +cte1 as +(select count(*) as c1 from rcte,t1 where a between 3 and 5 and id=a-3), +cte2 as +(select count(*) as c2 from rcte,t1 where a between 7 and 8 and id=a-7) +select * from cte1; +c1 +2 +with recursive +rcte(a) as +(select 1 union select cast(a+1 as unsigned) from rcte where a < 10), +cte1 as +(select count(*) as c1 from t1), +cte2 as +(select count(*) as c2 from t2) +select * from cte1,cte2; +c1 c2 +3 1 +with recursive +rcte(a) as +(select 1 union select cast(a+1 as unsigned) from rcte where a < 10), +cte1 as +(select count(*) as c1 from rcte,t1 where a between 3 and 5 and id=a-3), +cte2 as +(select count(*) as c2 from rcte,t1 where a between 7 and 8 and id=a-7) +select * from cte1, cte2 where cte1.c1 = 3; +c1 c2 +with recursive +rcte(a) as +(select 1 union select cast(a+1 as unsigned) from rcte where a < 10), +cte1 as +(select count(*) as c1 from rcte,t1 where a between 3 and 5 and id=a-3), +cte2 as +(select count(*) as c2 from rcte,t1 where a between 7 and 8 and id=a-7) +select * from cte2, cte1; +c2 c1 +1 2 +explain extended with recursive +rcte(a) as +(select 1 union select cast(a+1 as unsigned) from rcte where a < 10), +cte1 as +(select count(*) as c1 from rcte,t1 where a between 3 and 5 and id=a-3), +cte2 as +(select count(*) as c2 from rcte,t1 where a between 7 and 8 and id=a-7) +select * from cte2, cte1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <derived5> ALL NULL NULL NULL NULL 6 100.00 +1 PRIMARY <derived4> ALL NULL NULL NULL NULL 6 100.00 Using join buffer (flat, BNL join) +2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +3 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL +5 DERIVED <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where +5 DERIVED t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) +4 DERIVED <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where +4 DERIVED t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 with recursive rcte(`a`) as (/* select#2 */ select 1 AS `a` union /* select#3 */ select cast(`rcte`.`a` + 1 as unsigned) AS `cast(a+1 as unsigned)` from `rcte` where `rcte`.`a` < 10), cte1 as (/* select#4 */ select count(0) AS `c1` from `rcte` join `test`.`t1` where `rcte`.`a` between 3 and 5 and `test`.`t1`.`id` = `rcte`.`a` - 3), cte2 as (/* select#5 */ select count(0) AS `c2` from `rcte` join `test`.`t1` where `rcte`.`a` between 7 and 8 and `test`.`t1`.`id` = `rcte`.`a` - 7)/* select#1 */ select `cte2`.`c2` AS `c2`,`cte1`.`c1` AS `c1` from `cte2` join `cte1` +prepare stmt from "with recursive +rcte(a) as +(select 1 union select cast(a+1 as unsigned) from rcte where a < 10), +cte1 as +(select count(*) as c1 from rcte,t1 where a between 3 and 5 and id=a-3), +cte2 as +(select count(*) as c2 from rcte,t1 where a between 7 and 8 and id=a-7) +select * from cte2, cte1"; +execute stmt; +c2 c1 +1 2 +execute stmt; +c2 c1 +1 2 +drop procedure p; +drop table t2; +create table t2 (c1 int, c2 int); +create procedure p() insert into t2 with recursive +rcte(a) as +(select 1 union select cast(a+1 as unsigned) from rcte where a < 10), +cte1 as +(select count(*) as c1 from rcte,t1 where a between 3 and 5 and id=a-3), +cte2 as +(select count(*) as c2 from rcte,t1 where a between 7 and 8 and id=a-7) +select * from cte2, cte1; +call p(); +select * from t2; +c1 c2 +1 2 +drop procedure p; +drop table t1,t2; +# +# MDEV-17201: recursive part with LIMIT +# +CREATE TABLE purchases ( +id int unsigned NOT NULL AUTO_INCREMENT, +pdate date NOT NULL, +quantity int unsigned NOT NULL, +p_id int unsigned NOT NULL, +PRIMARY KEY (id) +); +INSERT INTO purchases(pdate, quantity, p_id) VALUES +('2014-11-01',5 ,1),('2014-11-03', 3 ,1), +('2014-11-01',2 ,2),('2014-11-03', 4 ,2); +CREATE TABLE expired ( +edate date NOT NULL, +quantity int unsigned NOT NULL, +p_id int unsigned NOT NULL, +PRIMARY KEY (edate,p_id) +); +INSERT INTO expired VALUES ('2014-11-12', 5 ,1),('2014-11-08', 1 ,2); +WITH RECURSIVE expired_map AS ( +SELECT edate AS expired_date, +CAST(NULL AS date) AS purchase_date, +0 AS quantity, +e.p_id, +(SELECT MAX(id)+1 FROM purchases p +WHERE pdate <= edate AND p.p_id =p_id) AS purchase_processed, +quantity AS unresolved +FROM expired e +UNION +( SELECT expired_date, +pdate, +IF(p.quantity < m.unresolved, p.quantity, m.unresolved), +p.p_id, +p.id, +IF(p.quantity < m.unresolved, m.unresolved - p.quantity, 0) +FROM purchases p JOIN expired_map m ON p.p_id = m.p_id +WHERE p.id < m.purchase_processed AND m.unresolved > 0 +ORDER BY p.id DESC +LIMIT 1 +) +) +SELECT * FROM expired_map; +expired_date purchase_date quantity p_id purchase_processed unresolved +2014-11-12 NULL 0 1 5 5 +2014-11-08 NULL 0 2 5 1 +2014-11-08 2014-11-03 1 2 4 0 +DROP TABLE purchases, expired; +# +# MDEV-17635: Two recursive CTEs, the second using the first +# +WITH RECURSIVE +x AS (SELECT 0 as k UNION ALL SELECT k + 1 FROM x WHERE k < 1), +z AS +( SELECT k1 AS cx, k2 AS cy, k1, k2 +FROM (SELECT k AS k1 FROM x) x1 JOIN (SELECT k AS k2 FROM x) y1 +UNION +SELECT 1,1,1,1 FROM z) +SELECT * FROM z; +cx cy k1 k2 +0 0 0 0 +1 0 1 0 +0 1 0 1 +1 1 1 1 +# https://wiki.postgresql.org/wiki/Mandelbrot_set: +WITH RECURSIVE x(i) AS ( +SELECT CAST(0 AS DECIMAL(13, 10)) +UNION ALL +SELECT i + 1 +FROM x +WHERE i < 101 +), +Z(Ix, Iy, Cx, Cy, X, Y, I) AS ( +SELECT Ix, Iy, X, Y, X, Y, 0 +FROM (SELECT CAST(-2.2 + 0.031 * i AS DECIMAL(13, 10)) AS X, +i AS Ix FROM x) AS xgen +CROSS JOIN ( +SELECT CAST(-1.5 + 0.031 * i AS DECIMAL(13, 10)) AS Y, +i AS iY FROM x +) AS ygen +UNION ALL +SELECT Ix, Iy, Cx, Cy, +CAST(X * X - Y * Y + Cx AS DECIMAL(13, 10)) AS X, +CAST(Y * X * 2 + Cy AS DECIMAL(13, 10)), I + 1 +FROM Z +WHERE X * X + Y * Y < 16.0 +AND I < 27 +), +Zt (Ix, Iy, I) AS ( +SELECT Ix, Iy, MAX(I) AS I +FROM Z +GROUP BY Iy, Ix +ORDER BY Iy, Ix +) +SELECT GROUP_CONCAT( +SUBSTRING( +' .,,,-----++++%%%%@@@@#### ', +GREATEST(I, 1), +1 +) ORDER BY Ix SEPARATOR '' + ) AS 'Mandelbrot Set' + FROM Zt +GROUP BY Iy +ORDER BY Iy; +Mandelbrot Set + .................................................................................... + ....................................................................................... + ......................................................................................... + ........................................................................................... + ....................................................,,,,,,,,,................................. + ................................................,,,,,,,,,,,,,,,,,,............................. + ..............................................,,,,,,,,,,,,,,,,,,,,,,,,.......................... + ............................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,........................ + ..........................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,...................... + .........................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,.................... + ........................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,................... + .......................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,................. + .......................................,,,,,,,,,,,,,,,,,,,,,,,,--,,,,,,,,,,,,,,,,,,,,................ +......................................,,,,,,,,,,,,,,,,,,,,,,,,,,-+--,,,,,,,,,,,,,,,,,,,............... +....................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,-----,,,,,,,,,,,,,,,,,,,.............. +...................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,--- -----,,,,,,,,,,,,,,,,,............. +.................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,---++--++,,,,,,,,,,,,,,,,,,............ +................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-----%++---,,,,,,,,,,,,,,,,,............ +..............................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-----+%----,,,,,,,,,,,,,,,,,,........... +.............................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,----- %%+----,,,,,,,,,,,,,,,,,,.......... +...........................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,---%-+% ----,,,,,,,,,,,,,,,,,,,......... +..........................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,---+ +## %+%---,,,,,,,,,,,,,,,,,,......... +........................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-----# # +---,,,,,,,,,,,,,,,,,,........ +.......................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-------% %-----,,,,,,,,,,,,,,,,,........ +.....................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,---------+ ------,,,,,,,,,,,,,,,,,....... +....................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-----------+@ +-----------,,,,,,,,,,,,....... +..................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,----@-------++ ++-----------,,,,,,,,,,,,...... +.................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,--+@% ---+ +@%%@ %%+@+@%------+-,,,,,,,,,,,...... +................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,---- # ++% % @-----++--,,,,,,,,,,,..... +..............,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,----+ % %%++ %+%@-,,,,,,,,,,,..... +.............,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-----+# #% ++-,,,,,,,,,,,,.... +............,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,------+ @---,,,,,,,,,,,,.... +..........,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-------++% ---,,,,,,,,,,,,.... +.........,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,--------+ + %+---,,,,,,,,,,,,,... +........,,,,,,,,,,,,,,,,,,,,,--------------------@ +----,,,,,,,,,,,,... +.......,,,,,,,,,,,,,,,,,,,,,,- +-----------------+ ----,,,,,,,,,,,,... +.......,,,,,,,,,,,,,,,,,,,,,--++------+---------+% +++--,,,,,,,,,,,,.. +......,,,,,,,,,,,,,,,,,,,,,,--%+-----++--------- #+-,,,,,,,,,,,,.. +.....,,,,,,,,,,,,,,,,,,,,,,----#%++--+@ -+-----+% --,,,,,,,,,,,,.. +.....,,,,,,,,,,,,,,,,,,,,,,-----+## ++@ + +----% +--,,,,,,,,,,,,,.. +....,,,,,,,,,,,,,,,,,,,,,,------+@ @ @@++++# +--,,,,,,,,,,,,,.. +....,,,,,,,,,,,,,,,,,,,,,-------% #++% -,,,,,,,,,,,,,.. +...,,,,,,,,,,,,,,,,,,,,,------++%# %%@ %-,,,,,,,,,,,,,,. +...,,,,,,,,,,,,,,,,,,,--------+ % +--,,,,,,,,,,,,,,. +...,,,,,,,,,,,,,,,,,,-----+--++@ # --,,,,,,,,,,,,,,. +..,,,,,,,,,,,,,,,,,-------%+++% @--,,,,,,,,,,,,,,,. +..,,,,,,,,,,,-------------+ @#@ ---,,,,,,,,,,,,,,,. +..,,,,,,,,,---@--------@-+% +---,,,,,,,,,,,,,,,. +..,,,,,------- +-++++-+%%% +----,,,,,,,,,,,,,,,. +..,,,,,,------%--------++% +----,,,,,,,,,,,,,,,. +..,,,,,,,,,,--+----------++# ---,,,,,,,,,,,,,,,. +..,,,,,,,,,,,,------------+@@@% +--,,,,,,,,,,,,,,,. +..,,,,,,,,,,,,,,,,,------- +++% %--,,,,,,,,,,,,,,,. +...,,,,,,,,,,,,,,,,,,---------+@ @ --,,,,,,,,,,,,,,. +...,,,,,,,,,,,,,,,,,,,,------- # %@ +--,,,,,,,,,,,,,,. +...,,,,,,,,,,,,,,,,,,,,,-------++@ %+ %-,,,,,,,,,,,,,,. +....,,,,,,,,,,,,,,,,,,,,,------- %++% %-,,,,,,,,,,,,,.. +....,,,,,,,,,,,,,,,,,,,,,,------+# %# #@ ++++ +--,,,,,,,,,,,,,.. +.....,,,,,,,,,,,,,,,,,,,,,,-----+ %%++% +@+----+ +--,,,,,,,,,,,,,.. +.....,,,,,,,,,,,,,,,,,,,,,,,---%+++--+#+--------% #--,,,,,,,,,,,,.. +......,,,,,,,,,,,,,,,,,,,,,,--++-----%%--------- @#--,,,,,,,,,,,,.. +.......,,,,,,,,,,,,,,,,,,,,,---------------------+@ +-++,,,,,,,,,,,,... +........,,,,,,,,,,,,,,,,,,,,,--------------------+ ----,,,,,,,,,,,,... +.........,,,,,,,,,,,,,,,,,,,,----,,,------------- #+----,,,,,,,,,,,,... +..........,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-------+ + +---,,,,,,,,,,,,,... +...........,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,--------+%# #---,,,,,,,,,,,,.... +............,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,------+# @ @---,,,,,,,,,,,,.... +.............,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-----+# + @--,,,,,,,,,,,,.... +..............,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,---+% %+@ %+-+ +++%-,,,,,,,,,,,..... +................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,----% %@++ # % -----++-,,,,,,,,,,,,..... +.................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-- ++ ---+ + +%@ %++++++------%-,,,,,,,,,,,...... +...................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,---- -------++ +------------,,,,,,,,,,,,...... +....................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-----------+% +--------,,,,,,,,,,,,,,,....... +......................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,--------+# -----,,,,,,,,,,,,,,,,,,....... +.......................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-------+ #----,,,,,,,,,,,,,,,,,,........ +.........................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,----+% %#---,,,,,,,,,,,,,,,,,,,........ +..........................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,---+%+%@ %+%%--,,,,,,,,,,,,,,,,,,......... +............................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,---+-+% %----,,,,,,,,,,,,,,,,,,.......... +.............................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-----+%@+---,,,,,,,,,,,,,,,,,,,.......... +...............................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-----+%----,,,,,,,,,,,,,,,,,,........... +................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-----%+ +--,,,,,,,,,,,,,,,,,............ +..................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,---++----,,,,,,,,,,,,,,,,,............. +...................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,---@-----,,,,,,,,,,,,,,,,,............. +.....................................,,,,,,,,,,,,,,,,,,,,,,,,,,,-----,,,,,,,,,,,,,,,,,,,.............. + .....................................,,,,,,,,,,,,,,,,,,,,,,,,,,--%,,,,,,,,,,,,,,,,,,,,............... + .......................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,................. + ........................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,.................. + ........................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,................... + .........................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,.................... + ..........................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,...................... + ............................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,........................ + .............................................,,,,,,,,,,,,,,,,,,,,,,,,.......................... + ................................................,,,,,,,,,,,,,,,,,............................. + .....................................................,,,,.................................... + ........................................................................................... + ......................................................................................... + ...................................................................................... + .................................................................................... + ................................................................................. + .............................................................................. + ........................................................................... + ........................................................................ +# +# MDEV-17871: EXPLAIN for query with not used recursive cte +# +create table t1 (a int); +insert into t1 values (2), (1), (4), (3); +explain extended +with recursive cte as +(select * from t1 where a=1 union select a+1 from cte where a<3) +select * from cte as t; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 4 100.00 +2 DERIVED t1 ALL NULL NULL NULL NULL 4 100.00 Using where +3 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 4 100.00 Using where +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 with recursive cte as (/* select#2 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 1 union /* select#3 */ select `cte`.`a` + 1 AS `a+1` from `cte` where `cte`.`a` < 3)/* select#1 */ select `t`.`a` AS `a` from `cte` `t` +with recursive cte as +(select * from t1 where a=1 union select a+1 from cte where a<3) +select * from cte as t; +a +1 +2 +3 +explain extended +with recursive cte as +(select * from t1 where a=1 union select a+1 from cte where a<3) +select * from t1 as t; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t ALL NULL NULL NULL NULL 4 100.00 +Warnings: +Note 1003 with recursive cte as (/* select#2 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 1 union /* select#3 */ select `cte`.`a` + 1 AS `a+1` from `cte` where `cte`.`a` < 3)/* select#1 */ select `test`.`t`.`a` AS `a` from `test`.`t1` `t` +with recursive cte as +(select * from t1 where a=1 union select a+1 from cte where a<3) +select * from t1 as t; +a +2 +1 +4 +3 +create table t2 ( i1 int, i2 int); +insert into t2 values (1,1),(2,2); +explain +with recursive cte as +( select * from t2 union select s1.* from t2 as s1, cte where s1.i1 = cte.i2 ) +select * from t2 as t; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t ALL NULL NULL NULL NULL 2 +drop table t1,t2; +# +# MDEV-22042: ANALYZE of query using stored function and recursive CTE +# +create table t1 (a1 varchar(20),a2 varchar(20)) engine=myisam; +insert into t1 values (1,1),(2,2),(3,3); +create table t2 ( +a2 varchar(20) primary key, b1 varchar(20), key (b1) +) engine=myisam; +insert into t2 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7); +insert into t2 values (11,11),(12,12),(13,13),(14,14),(15,15),(16,16),(17,17); +create function f1(id varchar(20)) returns varchar(50) +begin +declare res varchar (50); +select a2 into res from t2 where a2=id and b1=1 limit 1; +return res; +end$$ +select fv +from (select t1.a1, f1(t1.a2) fv from t1) dt +where (dt.a1) in (with recursive cte as (select a2 from t2 where a2='2' + union select tt2.a2 from t2 tt2 join cte on tt2.b1=cte.a2) +select a2 from cte); +fv +NULL +explain select fv +from (select t1.a1, f1(t1.a2) fv from t1) dt +where (dt.a1) in (with recursive cte as (select a2 from t2 where a2='2' + union select tt2.a2 from t2 tt2 join cte on tt2.b1=cte.a2) +select a2 from cte); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where +1 PRIMARY <derived3> ref key0 key0 23 test.t1.a1 1 FirstMatch(t1) +3 DERIVED t2 const PRIMARY PRIMARY 22 const 1 Using index +4 RECURSIVE UNION <derived3> ALL NULL NULL NULL NULL 2 Using where +4 RECURSIVE UNION tt2 ref b1 b1 23 cte.a2 2 +NULL UNION RESULT <union3,4> ALL NULL NULL NULL NULL NULL +analyze format=json select fv +from (select t1.a1, f1(t1.a2) fv from t1) dt +where (dt.a1) in (with recursive cte as (select a2 from t2 where a2='2' + union select tt2.a2 from t2 tt2 join cte on tt2.b1=cte.a2) +select a2 from cte); +ANALYZE +{ + "query_optimization": { + "r_total_time_ms": "REPLACED" + }, + "query_block": { + "select_id": 1, + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "r_loops": 1, + "rows": 3, + "r_rows": 3, + "r_table_time_ms": "REPLACED", + "r_other_time_ms": "REPLACED", + "r_engine_stats": REPLACED, + "filtered": 100, + "r_filtered": 100, + "attached_condition": "t1.a1 is not null" + } + }, + { + "table": { + "table_name": "<derived3>", + "access_type": "ref", + "possible_keys": ["key0"], + "key": "key0", + "key_length": "23", + "used_key_parts": ["a2"], + "ref": ["test.t1.a1"], + "r_loops": 3, + "rows": 1, + "r_rows": 0.333333333, + "r_table_time_ms": "REPLACED", + "r_other_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100, + "first_match": "t1", + "materialized": { + "query_block": { + "recursive_union": { + "table_name": "<union3,4>", + "access_type": "ALL", + "r_loops": 0, + "r_rows": null, + "query_specifications": [ + { + "query_block": { + "select_id": 3, + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "nested_loop": [ + { + "table": { + "table_name": "t2", + "access_type": "const", + "possible_keys": ["PRIMARY"], + "key": "PRIMARY", + "key_length": "22", + "used_key_parts": ["a2"], + "ref": ["const"], + "r_loops": 0, + "rows": 1, + "r_rows": null, + "r_engine_stats": REPLACED, + "filtered": 100, + "r_filtered": null, + "using_index": true + } + } + ] + } + }, + { + "query_block": { + "select_id": 4, + "operation": "UNION", + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "nested_loop": [ + { + "table": { + "table_name": "<derived3>", + "access_type": "ALL", + "r_loops": 1, + "rows": 2, + "r_rows": 1, + "r_table_time_ms": "REPLACED", + "r_other_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100, + "attached_condition": "cte.a2 is not null" + } + }, + { + "table": { + "table_name": "tt2", + "access_type": "ref", + "possible_keys": ["b1"], + "key": "b1", + "key_length": "23", + "used_key_parts": ["b1"], + "ref": ["cte.a2"], + "r_loops": 1, + "rows": 2, + "r_rows": 1, + "r_table_time_ms": "REPLACED", + "r_other_time_ms": "REPLACED", + "r_engine_stats": REPLACED, + "filtered": 100, + "r_filtered": 100 + } + } + ] + } + } + ] + } + } + } + } + } + ] + } +} +drop function f1; +drop table t1,t2; +# +# MDEV-22748: two materialized CTEs using the same recursive CTE +# (see also test case for MDEV-17024) +# +CREATE TABLE t1 (YEAR int(4), d1 date , d2 date) ; +INSERT INTO t1 VALUES (2018,'2018-01-01','2018-09-20'); +CREATE TABLE t2 (id int, tm date); +INSERT INTO t2 VALUES (1,'2018-08-30'),(2,'2018-08-30'),(3,'2018-08-30'); +CREATE TABLE t3 (id int, tm date); +INSERT INTO t3 VALUES (1,'2018-08-30'),(2,'2018-08-30'); +WITH RECURSIVE +cte AS +(SELECT YEAR(t1.d1) AS YEAR, t1.d1 AS st, t1.d1 + INTERVAL 1 MONTH AS fn +FROM t1 +UNION ALL +SELECT YEAR(cte.st + INTERVAL 1 MONTH), +cte.st + INTERVAL 1 MONTH, t1.d2 + INTERVAL 1 DAY +FROM cte JOIN t1 +WHERE cte.st + INTERVAL 1 MONTH < t1.d2 ), +cte2 AS (SELECT YEAR, COUNT(*) +FROM cte JOIN t2 ON t2.tm BETWEEN cte.st AND cte.fn), +cte3 AS (SELECT YEAR, COUNT(*) +FROM cte JOIN t3 ON t3.tm BETWEEN cte.st AND cte.fn) +SELECT t1.* FROM t1 JOIN cte2 USING (YEAR) JOIN cte3 USING (YEAR); +YEAR d1 d2 +2018 2018-01-01 2018-09-20 +EXPLAIN EXTENDED WITH RECURSIVE +cte AS +(SELECT YEAR(t1.d1) AS YEAR, t1.d1 AS st, t1.d1 + INTERVAL 1 MONTH AS fn +FROM t1 +UNION ALL +SELECT YEAR(cte.st + INTERVAL 1 MONTH), +cte.st + INTERVAL 1 MONTH, t1.d2 + INTERVAL 1 DAY +FROM cte JOIN t1 +WHERE cte.st + INTERVAL 1 MONTH < t1.d2 ), +cte2 AS (SELECT YEAR, COUNT(*) +FROM cte JOIN t2 ON t2.tm BETWEEN cte.st AND cte.fn), +cte3 AS (SELECT YEAR, COUNT(*) +FROM cte JOIN t3 ON t3.tm BETWEEN cte.st AND cte.fn) +SELECT t1.* FROM t1 JOIN cte2 USING (YEAR) JOIN cte3 USING (YEAR); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00 +1 PRIMARY <derived5> ref key0 key0 5 const 0 0.00 +1 PRIMARY <derived4> ref key0 key0 5 const 0 0.00 +2 DERIVED t1 system NULL NULL NULL NULL 1 100.00 +3 RECURSIVE UNION t1 system NULL NULL NULL NULL 1 100.00 +3 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL +4 DERIVED <derived2> ALL NULL NULL NULL NULL 2 100.00 +4 DERIVED t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) +5 DERIVED <derived2> ALL NULL NULL NULL NULL 2 100.00 +5 DERIVED t3 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 with recursive cte as (/* select#2 */ select year(`test`.`t1`.`d1`) AS `YEAR`,`test`.`t1`.`d1` AS `st`,`test`.`t1`.`d1` + interval 1 month AS `fn` from `test`.`t1` union all /* select#3 */ select year(`cte`.`st` + interval 1 month) AS `YEAR(cte.st + INTERVAL 1 MONTH)`,`cte`.`st` + interval 1 month AS `cte.st + INTERVAL 1 MONTH`,`test`.`t1`.`d2` + interval 1 day AS `t1.d2 + INTERVAL 1 DAY` from `cte` join `test`.`t1` where `cte`.`st` + interval 1 month < `test`.`t1`.`d2`), cte2 as (/* select#4 */ select `cte`.`YEAR` AS `YEAR`,count(0) AS `COUNT(*)` from `cte` join `test`.`t2` where `test`.`t2`.`tm` between `cte`.`st` and `cte`.`fn`), cte3 as (/* select#5 */ select `cte`.`YEAR` AS `YEAR`,count(0) AS `COUNT(*)` from `cte` join `test`.`t3` where `test`.`t3`.`tm` between `cte`.`st` and `cte`.`fn`)/* select#1 */ select 2018 AS `YEAR`,'2018-01-01' AS `d1`,'2018-09-20' AS `d2` from `cte2` join `cte3` where `cte3`.`YEAR` = 2018 and `cte2`.`YEAR` = 2018 +PREPARE stmt FROM "WITH RECURSIVE +cte AS +(SELECT YEAR(t1.d1) AS YEAR, t1.d1 AS st, t1.d1 + INTERVAL 1 MONTH AS fn +FROM t1 +UNION ALL +SELECT YEAR(cte.st + INTERVAL 1 MONTH), +cte.st + INTERVAL 1 MONTH, t1.d2 + INTERVAL 1 DAY +FROM cte JOIN t1 +WHERE cte.st + INTERVAL 1 MONTH < t1.d2 ), +cte2 AS (SELECT YEAR, COUNT(*) +FROM cte JOIN t2 ON t2.tm BETWEEN cte.st AND cte.fn), +cte3 AS (SELECT YEAR, COUNT(*) +FROM cte JOIN t3 ON t3.tm BETWEEN cte.st AND cte.fn) +SELECT t1.* FROM t1 JOIN cte2 USING (YEAR) JOIN cte3 USING (YEAR)"; +EXECUTE stmt; +YEAR d1 d2 +2018 2018-01-01 2018-09-20 +EXECUTE stmt; +YEAR d1 d2 +2018 2018-01-01 2018-09-20 +CREATE TABLE t4 (YEAR int(4), d1 date , d2 date); +CREATE PROCEDURE p() INSERT INTO t4 WITH RECURSIVE +cte AS +(SELECT YEAR(t1.d1) AS YEAR, t1.d1 AS st, t1.d1 + INTERVAL 1 MONTH AS fn +FROM t1 +UNION ALL +SELECT YEAR(cte.st + INTERVAL 1 MONTH), +cte.st + INTERVAL 1 MONTH, t1.d2 + INTERVAL 1 DAY +FROM cte JOIN t1 +WHERE cte.st + INTERVAL 1 MONTH < t1.d2 ), +cte2 AS (SELECT YEAR, COUNT(*) +FROM cte JOIN t2 ON t2.tm BETWEEN cte.st AND cte.fn), +cte3 AS (SELECT YEAR, COUNT(*) +FROM cte JOIN t3 ON t3.tm BETWEEN cte.st AND cte.fn) +SELECT t1.* FROM t1 JOIN cte2 USING (YEAR) JOIN cte3 USING (YEAR); +CALL p(); +SELECT * FROM t4; +YEAR d1 d2 +2018 2018-01-01 2018-09-20 +DROP PROCEDURE p; +DROP TABLE t1,t2,t3,t4; +# +# MDEV-23619: recursive CTE used only in the second operand of UNION +# +create table t1 ( +a bigint(10) not null auto_increment, +b int(5) not null, +c bigint(10) default null, +primary key (a) +) engine myisam; +insert into t1 values +(1,3,12), (2,7,15), (3,1,3), (4,3,1); +explain with recursive r_cte as +( select * from t1 as s +union +select t1.* from t1, r_cte as r where t1.c = r.a ) +select 0 as b FROM dual union all select b FROM r_cte as t; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +2 DERIVED s ALL NULL NULL NULL NULL 4 +3 RECURSIVE UNION t1 ALL NULL NULL NULL NULL 4 Using where +3 RECURSIVE UNION <derived2> ref key0 key0 9 test.t1.c 2 +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL +4 UNION <derived2> ALL NULL NULL NULL NULL 4 +with recursive r_cte as +( select * from t1 as s +union +select t1.* from t1, r_cte as r where t1.c = r.a ) +select 0 as b FROM dual union all select b FROM r_cte as t; +b +0 +3 +7 +1 +3 +analyze format=json with recursive r_cte as +( select * from t1 as s +union +select t1.* from t1, r_cte as r where t1.c = r.a ) +select 0 as b FROM dual union all select b FROM r_cte as t; +ANALYZE +{ + "query_optimization": { + "r_total_time_ms": "REPLACED" + }, + "query_block": { + "union_result": { + "query_specifications": [ + { + "query_block": { + "select_id": 1, + "table": { + "message": "No tables used" + } + } + }, + { + "query_block": { + "select_id": 4, + "operation": "UNION", + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "nested_loop": [ + { + "table": { + "table_name": "<derived2>", + "access_type": "ALL", + "r_loops": 1, + "rows": 4, + "r_rows": 4, + "r_table_time_ms": "REPLACED", + "r_other_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100, + "materialized": { + "query_block": { + "recursive_union": { + "table_name": "<union2,3>", + "access_type": "ALL", + "r_loops": 0, + "r_rows": null, + "query_specifications": [ + { + "query_block": { + "select_id": 2, + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "nested_loop": [ + { + "table": { + "table_name": "s", + "access_type": "ALL", + "r_loops": 1, + "rows": 4, + "r_rows": 4, + "r_table_time_ms": "REPLACED", + "r_other_time_ms": "REPLACED", + "r_engine_stats": REPLACED, + "filtered": 100, + "r_filtered": 100 + } + } + ] + } + }, + { + "query_block": { + "select_id": 3, + "operation": "UNION", + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "r_loops": 1, + "rows": 4, + "r_rows": 4, + "r_table_time_ms": "REPLACED", + "r_other_time_ms": "REPLACED", + "r_engine_stats": REPLACED, + "filtered": 100, + "r_filtered": 100, + "attached_condition": "t1.c is not null" + } + }, + { + "table": { + "table_name": "<derived2>", + "access_type": "ref", + "possible_keys": ["key0"], + "key": "key0", + "key_length": "9", + "used_key_parts": ["a"], + "ref": ["test.t1.c"], + "r_loops": 4, + "rows": 2, + "r_rows": 0.5, + "r_table_time_ms": "REPLACED", + "r_other_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100 + } + } + ] + } + } + ] + } + } + } + } + } + ] + } + } + ] + } + } +} +prepare stmt from "with recursive r_cte as +( select * from t1 as s +union +select t1.* from t1, r_cte as r where t1.c = r.a ) +select 0 as b FROM dual union all select b FROM r_cte as t"; +execute stmt; +b +0 +3 +7 +1 +3 +execute stmt; +b +0 +3 +7 +1 +3 +deallocate prepare stmt; +#checking hanging cte that uses a recursive cte +explain with h_cte as +( with recursive r_cte as +( select * from t1 as s +union +select t1.* from t1, r_cte as r where t1.c = r.a ) +select 0 as b FROM dual union all select b FROM r_cte as t) +select * from t1 as tt; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY tt ALL NULL NULL NULL NULL 4 +with h_cte as +( with recursive r_cte as +( select * from t1 as s +union +select t1.* from t1, r_cte as r where t1.c = r.a ) +select 0 as b FROM dual union all select b FROM r_cte as t) +select * from t1 as tt; +a b c +1 3 12 +2 7 15 +3 1 3 +4 3 1 +analyze format=json with h_cte as +( with recursive r_cte as +( select * from t1 as s +union +select t1.* from t1, r_cte as r where t1.c = r.a ) +select 0 as b FROM dual union all select b FROM r_cte as t) +select * from t1 as tt; +ANALYZE +{ + "query_optimization": { + "r_total_time_ms": "REPLACED" + }, + "query_block": { + "select_id": 1, + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "nested_loop": [ + { + "table": { + "table_name": "tt", + "access_type": "ALL", + "r_loops": 1, + "rows": 4, + "r_rows": 4, + "r_table_time_ms": "REPLACED", + "r_other_time_ms": "REPLACED", + "r_engine_stats": REPLACED, + "filtered": 100, + "r_filtered": 100 + } + } + ] + } +} +prepare stmt from "with h_cte as +( with recursive r_cte as +( select * from t1 as s +union +select t1.* from t1, r_cte as r where t1.c = r.a ) +select 0 as b FROM dual union all select b FROM r_cte as t) +select * from t1 as tt"; +execute stmt; +a b c +1 3 12 +2 7 15 +3 1 3 +4 3 1 +execute stmt; +a b c +1 3 12 +2 7 15 +3 1 3 +4 3 1 +deallocate prepare stmt; +drop table t1; +# +# MDEV-24019: query with recursive CTE when no default database is set +# +create database dummy; +use dummy; +drop database dummy; +with recursive a as +(select 1 from dual union select * from a as r) +select * from a; +1 +1 +create database db1; +create table db1.t1 (a int); +insert into db1.t1 values (3), (7), (1); +with recursive cte as +(select * from db1.t1 union select * from (select * from cte) as t) +select * from cte; +a +3 +7 +1 +explain with recursive cte as +(select * from db1.t1 union select * from (select * from cte) as t) +select * from cte; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 3 +2 DERIVED t1 ALL NULL NULL NULL NULL 3 +3 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 3 +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL +prepare stmt from "with recursive cte as +(select * from db1.t1 union select * from (select * from cte) as t) +select * from cte"; +execute stmt; +a +3 +7 +1 +execute stmt; +a +3 +7 +1 +deallocate prepare stmt; +drop database db1; +use test; +# +# MDEV-23406: query with mutually recursive CTEs when big_tables=1 +# +set @save_big_tables=@@big_tables; +set big_tables=1; +Warnings: +Warning 1287 '@@big_tables' is deprecated and will be removed in a future release +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_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; +h_name h_dob w_name w_dob +Dad 1970-02-02 Mom 1975-03-03 +Grandpa Bill 1940-04-05 Grandma Ann 1941-10-15 +Grandpa Ben 1940-10-21 Grandma Sally 1943-08-23 +explain 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; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived3> ALL NULL NULL NULL NULL 1728 +4 DERIVED <derived3> ALL NULL NULL NULL NULL 1728 +5 RECURSIVE UNION <derived3> ALL NULL NULL NULL NULL 1728 +NULL UNION RESULT <union4,5> ALL NULL NULL NULL NULL NULL +3 DERIVED v ALL NULL NULL NULL NULL 12 Using where +3 DERIVED h ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) +3 DERIVED w ALL NULL NULL NULL NULL 12 Using where; Using join buffer (incremental, BNL join) +2 RECURSIVE UNION <derived4> ALL NULL NULL NULL NULL 2 +2 RECURSIVE UNION h ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) +2 RECURSIVE UNION w ALL NULL NULL NULL NULL 12 Using where; Using join buffer (incremental, BNL join) +NULL UNION RESULT <union3,2> ALL NULL NULL NULL NULL NULL +prepare stmt from "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"; +execute stmt; +h_name h_dob w_name w_dob +Dad 1970-02-02 Mom 1975-03-03 +Grandpa Bill 1940-04-05 Grandma Ann 1941-10-15 +Grandpa Ben 1940-10-21 Grandma Sally 1943-08-23 +execute stmt; +h_name h_dob w_name w_dob +Dad 1970-02-02 Mom 1975-03-03 +Grandpa Bill 1940-04-05 Grandma Ann 1941-10-15 +Grandpa Ben 1940-10-21 Grandma Sally 1943-08-23 +deallocate prepare stmt; +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; +h_name h_dob w_name w_dob +Dad 1970-02-02 Mom 1975-03-03 +Grandpa Bill 1940-04-05 Grandma Ann 1941-10-15 +Grandpa Ben 1940-10-21 Grandma Sally 1943-08-23 +explain 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; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 +3 DERIVED folks ALL NULL NULL NULL NULL 12 Using where +4 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 2 +5 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 2 +NULL UNION RESULT <union3,4,5> ALL NULL NULL NULL NULL NULL +2 DERIVED h ALL NULL NULL NULL NULL 12 +2 DERIVED w ALL NULL NULL NULL NULL 12 Using join buffer (flat, BNL join) +2 DERIVED <derived3> ALL NULL NULL NULL NULL 12 Using where; Using join buffer (incremental, BNL join) +prepare stmt from "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"; +execute stmt; +h_name h_dob w_name w_dob +Dad 1970-02-02 Mom 1975-03-03 +Grandpa Bill 1940-04-05 Grandma Ann 1941-10-15 +Grandpa Ben 1940-10-21 Grandma Sally 1943-08-23 +execute stmt; +h_name h_dob w_name w_dob +Dad 1970-02-02 Mom 1975-03-03 +Grandpa Bill 1940-04-05 Grandma Ann 1941-10-15 +Grandpa Ben 1940-10-21 Grandma Sally 1943-08-23 +deallocate prepare stmt; +drop table folks; +set big_tables=@save_big_tables; +Warnings: +Warning 1287 '@@big_tables' is deprecated and will be removed in a future release +# +# MDEV-26135: execution of PS for query with hanging recursive CTE +# +create table t1 (a int); +insert into t1 values (5), (7); +create table t2 (b int); +insert into t2 values (3), (7), (1); +with recursive r as (select a from t1 union select a+1 from r where a < 10) +select * from t2; +b +3 +7 +1 +prepare stmt from "with recursive r as (select a from t1 union select a+1 from r where a < 10) +select * from t2"; +execute stmt; +b +3 +7 +1 +execute stmt; +b +3 +7 +1 +deallocate prepare stmt; +drop table t1,t2; +# +# MDEV-26189: Unknown column reference within hanging recursive CTE +# +create table t1 (a int); +insert into t1 values (3), (7), (1); +with recursive +r as (select * from t1 union select s1.* from t1 as s1, r where s1.a = r.b) +select * from t1 as t; +ERROR 42S22: Unknown column 'r.b' in 'where clause' +explain with recursive +r as (select * from t1 union select s1.* from t1 as s1, r where s1.a = r.b) +select * from t1 as t; +ERROR 42S22: Unknown column 'r.b' in 'where clause' +create procedure sp1() with recursive +r as (select * from t1 union select s1.* from t1 as s1, r where s1.a = r.b) +select * from t1 as t; +call sp1(); +ERROR 42S22: Unknown column 'r.b' in 'where clause' +call sp1(); +ERROR 42S22: Unknown column 'r.b' in 'where clause' +with recursive +r as (select * from t1 union select s1.* from t1 as s1, r where s1.b = r.a) +select * from t1 as t; +ERROR 42S22: Unknown column 's1.b' in 'where clause' +explain with recursive +r as (select * from t1 union select s1.* from t1 as s1, r where s1.b = r.a) +select * from t1 as t; +ERROR 42S22: Unknown column 's1.b' in 'where clause' +create procedure sp2() with recursive +r as (select * from t1 union select s1.* from t1 as s1, r where s1.b = r.a) +select * from t1 as t; +call sp2(); +ERROR 42S22: Unknown column 's1.b' in 'where clause' +call sp2(); +ERROR 42S22: Unknown column 's1.b' in 'where clause' +drop procedure sp1; +drop procedure sp2; +drop table t1; +# +# MDEV-26202: Recursive CTE used indirectly twice +# (fixed by the patch forMDEV-26025) +# +with recursive +rcte as ( SELECT 1 AS a +UNION ALL +SELECT cast(a + 1 as unsigned int) FROM rcte WHERE a < 3), +cte1 AS (SELECT a FROM rcte), +cte2 AS (SELECT a FROM cte1), +cte3 AS ( SELECT a FROM cte2) +SELECT * FROM cte2, cte3; +a a +1 1 +2 1 +3 1 +1 2 +2 2 +3 2 +1 3 +2 3 +3 3 +# +# End of 10.2 tests +# +# +# MDEV-14217 [db crash] Recursive CTE when SELECT includes new field +# +CREATE TEMPORARY TABLE a_tbl ( +a VARCHAR(33) PRIMARY KEY, +b VARCHAR(33) +); +INSERT INTO a_tbl VALUES ('block0', 'block0'), ('block1', NULL); +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; +ERROR 21000: The used SELECT statements have a different number of columns +DROP TABLE a_tbl; +WITH RECURSIVE x AS (SELECT 1,2 UNION ALL SELECT 1 FROM x) SELECT * FROM x; +ERROR 21000: The used SELECT statements have a different number of columns +# +# MDEV-15162: Setting user variable in recursive CTE +# +SET @c=1; +WITH RECURSIVE cte AS +(SELECT 5 +UNION +SELECT @c:=@c+1 FROM cte WHERE @c<3) +SELECT * FROM cte; +5 +5 +2 +3 +# +# MDEV-14883: recursive references in operands of INTERSECT / EXCEPT +# +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; +city +Frankfurt +Amsterdam +Chicago +Los Angeles +New York +Montreal +Moscow +Dubai +Beijing +Tokyo +London +Bangkok +Reykjavik +Paris +Seattle +Cairo +Delhi +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; +city +Chicago +Los Angeles +New York +Montreal +London +Reykjavik +Paris +Seattle +Moscow +Dubai +Bangkok +Cairo +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; +departure arrival dist leg_no acc_mileage +Seattle Chicago 1733 1 1733 +Seattle Los Angeles 960 1 960 +Chicago New York 712 2 2445 +Chicago Montreal 746 2 2479 +Los Angeles New York 2446 2 3406 +New York London 3459 3 6865 +New York London 3459 3 5904 +New York Reykjavik 2613 3 6019 +New York Reykjavik 2613 3 5058 +New York Paris 3625 3 7031 +New York Paris 3625 3 6070 +Montreal Paris 3425 3 5904 +New York Seattle 2402 3 5808 +New York Seattle 2402 3 4847 +London Moscow 1554 4 7458 +London Moscow 1554 4 8419 +Moscow Dubai 2298 5 10717 +Moscow Dubai 2298 5 9756 +Dubai Bangkok 3050 6 12806 +Dubai Bangkok 3050 6 13767 +Dubai Cairo 1501 6 11257 +Dubai Cairo 1501 6 12218 +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; +city +Chicago +Los Angeles +New York +Montreal +London +Reykjavik +Paris +Seattle +Moscow +Dubai +Bangkok +Cairo +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; +city +Frankfurt +Amsterdam +Chicago +Los Angeles +Montreal +Beijing +Bangkok +Paris +drop table flights, distances; +# +# MDEV-15159: Forced nullability of columns in recursive CTE +# +WITH RECURSIVE cte AS ( +SELECT 1 AS a UNION ALL +SELECT NULL FROM cte WHERE a IS NOT NULL) +SELECT * FROM cte; +a +1 +NULL +CREATE TABLE t1 (a int NOT NULL); +INSERT INTO t1 VALUES (0); +WITH RECURSIVE cte AS +(SELECT a FROM t1 where a=0 UNION SELECT NULL FROM cte) +SELECT * FROM cte; +a +0 +NULL +DROP TABLE t1; +# +# MDEV-12325 Unexpected data type and truncation when using CTE +# +CREATE TABLE t1 +( +id INT, mid INT, name TEXT +); +INSERT INTO t1 VALUES (0,NULL,'Name'),(1,0,'Name1'),(2,0,'Name2'),(11,1,'Name11'),(12,1,'Name12'); +WITH RECURSIVE +cteReports (level, id, mid, name) AS +( +SELECT 1, id, mid, name FROM t1 WHERE mid IS NULL +UNION ALL +SELECT r.level + 1, e.id, e.mid + 1000000000000, e.name FROM t1 e +INNER JOIN cteReports r ON e.mid = r.id +) +SELECT +level, id, mid, name, +(SELECT name FROM t1 WHERE id= cteReports.mid) AS mname +FROM cteReports ORDER BY level, mid; +ERROR 22003: Out of range value for column 'mid' at row 2 +create table t2 as WITH RECURSIVE +cteReports (level, id, mid, name) AS +( +SELECT 1, id, mid, name FROM t1 WHERE mid IS NULL +UNION ALL +SELECT r.level + 1, e.id, e.mid + 1000000000000, e.name FROM t1 e +INNER JOIN cteReports r ON e.mid = r.id +) +SELECT +level, id, mid, name, +(SELECT name FROM t1 WHERE id= cteReports.mid) AS mname +FROM cteReports ORDER BY level, mid;; +ERROR 22003: Out of range value for column 'mid' at row 2 +create table t2 ignore as WITH RECURSIVE +cteReports (level, id, mid, name) AS +( +SELECT 1, id, mid, name FROM t1 WHERE mid IS NULL +UNION ALL +SELECT r.level + 1, e.id, e.mid + 1000000000000, e.name FROM t1 e +INNER JOIN cteReports r ON e.mid = r.id +) +SELECT +level, id, mid, name, +(SELECT name FROM t1 WHERE id= cteReports.mid) AS mname +FROM cteReports ORDER BY level, mid;; +Warnings: +Warning 1264 Out of range value for column 'mid' at row 2 +Warning 1264 Out of range value for column 'mid' at row 3 +Warning 1264 Out of range value for column 'mid' at row 4 +Warning 1264 Out of range value for column 'mid' at row 5 +show create table t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `level` int(1) DEFAULT NULL, + `id` int(11) DEFAULT NULL, + `mid` int(11) DEFAULT NULL, + `name` text DEFAULT NULL, + `mname` text DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +insert into t2 WITH RECURSIVE +cteReports (level, id, mid, name) AS +( +SELECT 1, id, mid, name FROM t1 WHERE mid IS NULL +UNION ALL +SELECT r.level + 1, e.id, e.mid + 1000000000000, e.name FROM t1 e +INNER JOIN cteReports r ON e.mid = r.id +) +SELECT +level, id, mid, name, +(SELECT name FROM t1 WHERE id= cteReports.mid) AS mname +FROM cteReports ORDER BY level, mid;; +ERROR 22003: Out of range value for column 'mid' at row 2 +insert ignore into t2 WITH RECURSIVE +cteReports (level, id, mid, name) AS +( +SELECT 1, id, mid, name FROM t1 WHERE mid IS NULL +UNION ALL +SELECT r.level + 1, e.id, e.mid + 1000000000000, e.name FROM t1 e +INNER JOIN cteReports r ON e.mid = r.id +) +SELECT +level, id, mid, name, +(SELECT name FROM t1 WHERE id= cteReports.mid) AS mname +FROM cteReports ORDER BY level, mid;; +Warnings: +Warning 1264 Out of range value for column 'mid' at row 2 +Warning 1264 Out of range value for column 'mid' at row 3 +Warning 1264 Out of range value for column 'mid' at row 4 +Warning 1264 Out of range value for column 'mid' at row 5 +drop table t2; +set @@sql_mode=""; +WITH RECURSIVE +cteReports (level, id, mid, name) AS +( +SELECT 1, id, mid, name FROM t1 WHERE mid IS NULL +UNION ALL +SELECT r.level + 1, e.id, e.mid + 1000000000000, e.name FROM t1 e +INNER JOIN cteReports r ON e.mid = r.id +) +SELECT +level, id, mid, name, +(SELECT name FROM t1 WHERE id= cteReports.mid) AS mname +FROM cteReports ORDER BY level, mid; +level id mid name mname +1 0 NULL Name NULL +2 1 2147483647 Name1 NULL +2 2 2147483647 Name2 NULL +3 11 2147483647 Name11 NULL +3 12 2147483647 Name12 NULL +Warnings: +Warning 1264 Out of range value for column 'mid' at row 2 +Warning 1264 Out of range value for column 'mid' at row 3 +Warning 1264 Out of range value for column 'mid' at row 4 +Warning 1264 Out of range value for column 'mid' at row 5 +create table t2 as WITH RECURSIVE +cteReports (level, id, mid, name) AS +( +SELECT 1, id, mid, name FROM t1 WHERE mid IS NULL +UNION ALL +SELECT r.level + 1, e.id, e.mid + 1000000000000, e.name FROM t1 e +INNER JOIN cteReports r ON e.mid = r.id +) +SELECT +level, id, mid, name, +(SELECT name FROM t1 WHERE id= cteReports.mid) AS mname +FROM cteReports ORDER BY level, mid;; +Warnings: +Warning 1264 Out of range value for column 'mid' at row 2 +Warning 1264 Out of range value for column 'mid' at row 3 +Warning 1264 Out of range value for column 'mid' at row 4 +Warning 1264 Out of range value for column 'mid' at row 5 +show create table t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `level` int(1) DEFAULT NULL, + `id` int(11) DEFAULT NULL, + `mid` int(11) DEFAULT NULL, + `name` text DEFAULT NULL, + `mname` text DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +set @@sql_mode=default; +drop table t1,t2; +# +# MDEV-29361: Embedded recursive / non-recursive CTE within +# the scope of another embedded CTE with the same name +# +create table t1 (a int); +insert into t1 values (4), (5); +create table t2 (a int); +insert into t2 values (6), (8); +create table t3 (a int); +insert into t3 values (1), (9); +with recursive +x as +( +select a from t1 union select a+1 from x as r1 where a < 7 +) +select * from x as s1; +a +4 +5 +6 +7 +with recursive +x as +( +select a from t2 +union +select a+2 from x as r2 where a < 10 +) +select a from x as s2; +a +6 +8 +10 +with +cte as +( +with recursive +x as +( +select a from t1 union select a+1 from x as r1 where a < 7 +) +select * from x as s1 +where s1.a in ( +with recursive +x as +( +select a from t2 +union +select a+2 from x as r2 where a < 10 +) +select a from x as s2 +) +) +select * from cte; +a +6 +with +cte as +( +with recursive +x(a) as +( +select a from t1 union select a+1 from x as r1 where a < 7 +) +select s1.a from x as s1, x +where s1.a = x.a and +x.a in ( +with recursive +x(a) as +( +select a from t2 +union +select a+2 from x as r2 where a < 10 +) +select a from x as s2 +) +) +select * from cte; +a +6 +with +cte as +( +with +x as +( +select a from t1 union select a+1 from x as r1 where a < 7 +) +select * from x as s1 +where s1.a in ( +with recursive +x as +( +select a from t2 +union +select a+2 from x as r2 where a < 10 +) +select a from x as s2 +) +) +select * from cte; +ERROR 42S02: Table 'test.x' doesn't exist +with +cte as +( +with recursive +x as +( +select a from t1 union select a+1 from x as r1 where a < 7 +) +select * from x as s1 +where s1.a in ( +with +x as +( +select a from t2 +union +select a+2 from x as r2 where a < 10 +) +select a from x as s2 +) +) +select * from cte; +a +6 +7 +with +cte as +( +with +x as +( +select a from t1 union select a+1 from x as r1 where a < 7 +) +select * from x as s1 +where s1.a in ( +with +x as +( +select a from t2 +union +select a+2 from x as r2 where a < 10 +) +select a from x as s2 +) +) +select * from cte; +ERROR 42S02: Table 'test.x' doesn't exist +with +cte as +( +with recursive +y as +( +select a from t1 union select a+1 from y as r1 where a < 7 +) +select * from y as s1 +where s1.a in ( +with +x as +( +select a from t2 +union +select a+2 from x as r2 where a < 10 +) +select a from x as s2 +) +) +select * from cte; +ERROR 42S02: Table 'test.x' doesn't exist +with +cte as +( +with +y(a) as +( +select a+5 from t1 +) +select * from y as s1 +where s1.a in ( +with +x as +( +select a from t2 +union +select a+2 from x as r2 where a < 10 +) +select a from x as s2 +) +) +select * from cte; +ERROR 42S02: Table 'test.x' doesn't exist +with +cte as +( +select ( +with +x as +( +select a from x as r1 +) +select * from x as s1 +where s1.a in ( +with recursive +x as +( +select a from t2 +union +select a+2 from x as r2 where a < 10 +) +select a from x as s2 +) +) as r +from t3 +) +select * from cte; +ERROR 42S02: Table 'test.x' doesn't exist +with +cte as +( +select ( +with +x as +( +select a from x as r1 +) +select * from x as s1 +where s1.a < 5 and +s1.a in ( +with +x as +( +select a from t2 +union +select a+2 from x as r2 where a < 10 +) +select a from x as s2 +) +) as r +from t3 +) +select * from cte; +ERROR 42S02: Table 'test.x' doesn't exist +with +cte as +( +select ( +with recursive +x(a) as +( +select a+3 from t1 union select a+1 from x as r1 where a < 7 +) +select * from x as s1 +where s1.a < 8 and +s1.a in ( +with recursive +x(a) as +( +select a-2 from t2 +union +select a+1 from x as r2 where a < 10 +) +select a from x as s2 +) +) as r +from t3 +) +select * from cte; +r +7 +7 +with +cte as +( +select ( +with recursive +x as +( +select a from t1 union select a+1 from x as r1 where a < 7 +) +select * from x as s1 +where s1.a in ( +with recursive +x as +( +select a from t2 +union +select a+2 from x as r2 where a < 10 +) +select a from x as s2 +) +) as r +from t3 +) +select * from cte; +r +6 +6 +create table x (a int); +insert into x values (3), (7), (1), (5), (6); +with +cte as +( +select ( +with +x as +( +select ( select a from x as r1 ) as a from t1 +) +select * from x as s1 +where s1.a in ( +with recursive +x as +( +select a from t2 +union +select a+2 from x as r2 where a < 10 +) +select a from x s2 +) +) as r +from t3 +) +select * from cte; +ERROR 21000: Subquery returns more than 1 row +with +cte as +( +select ( +with +x as +( +select ( select a from x ) as a from t1 +) +select exists ( +with recursive +x as +( +select a from t2 +union +select a+2 from x as r2 where a < 10 +) +select a from x +) +) as r +from t3 +) +select * from cte; +r +1 +1 +with +cte_e as +( +with +cte as +( +select ( +with +x as +( +select ( select a from x ) from t1 +) +select exists ( +with recursive +x as +( +select a from t2 +union +select a+2 from x as r2 where a < 10 +) +select a from x +) +) as r +from t3 +) +select * from cte +) +select s1.*, s2.* from cte_e as s1, cte_e as s2; +r r +1 1 +1 1 +1 1 +1 1 +with +x as +( +select a from t1 union select a+1 from x as r1 where a < 7 +) +select * from x as s1; +a +4 +5 +2 +6 +7 +with +x as +( +select a from t2 +union +select a+2 from x as r2 where a < 10 +) +select a from x as s2; +a +6 +8 +5 +9 +3 +7 +with recursive +x as +( +select a from t1 union select a+1 from x as r1 where a < 7 +) +select * from x as s1; +a +4 +5 +6 +7 +with recursive +x as +( +select a from t2 +union +select a+2 from x as r2 where a < 10 +) +select a from x as s2; +a +6 +8 +10 +with +cte as +( +with +x as +( +select a from t1 union select a+1 from x as r1 where a < 7 +) +select * from x as s1 +where s1.a in ( +with recursive +x as +( +select a from t2 +union +select a+2 from x as r2 where a < 10 +) +select a from x as s2 +) +) +select * from cte; +a +6 +with +cte as +( +with +x as +( +select a from t1 union select a+1 from x as r1 where a < 7 +) +select * from x as s1 +where s1.a in ( +with +x as +( +select a from t2 +union +select a+2 from x as r2 where a < 10 +) +select a from x as s2 +) +) +select * from cte; +a +4 +6 +7 +with +cte as +( +with recursive +y as +( +select a from t1 union select a+1 from y as r1 where a < 7 +) +select * from y as s1 +where s1.a in ( +with +x as +( +select a from t2 +union +select a+2 from x as r2 where a < 10 +) +select a from x as s2 +) +) +select * from cte; +a +5 +6 +7 +with +cte as +( +with +y(a) as +( +select a+5 from t1 +) +select * from y as s1 +where s1.a in ( +with +x as +( +select a from t2 +union +select a+2 from x as r2 where a < 10 +) +select a from x as s2 +) +) +select * from cte; +a +9 +with +cte as +( +select ( +with +x as +( +select a from x as r1 +) +select * from x as s1 +where s1.a in ( +with +recursive x as +( +select a from t2 +union +select a+2 from x as r2 where a < 10 +) +select a from x as s2 +) +) as r +from t3 +) +select * from cte; +r +6 +6 +with +cte as +( +select ( +with +x as +( +select a from x as r1 +) +select * from x as s1 +where s1.a < 5 and +s1.a in ( +with +x as +( +select a from t2 +union +select a+2 from x as r2 where a < 10 +) +select a from x as s2 +) +) as r +from t3 +) +select * from cte; +r +3 +3 +drop table t1,t2,t3,x; +# +# MDEV-30248: Embedded non-recursive CTE referring to base table 'x' +# within a CTE with name 'x' used in a subquery from +# select list of another CTE +# +CREATE TABLE x (a int) ENGINE=MyISAM; +INSERT INTO x VALUES (3),(7),(1); +CREATE TABLE t1 (b int) ENGINE=MYISAM; +INSERT INTO t1 VALUES (1); +WITH cte AS +( +SELECT +( +WITH x AS +(WITH x AS (SELECT a FROM x AS t) SELECT 1 AS b) +SELECT b FROM x AS r +) AS c +) +SELECT cte.c FROM cte; +c +1 +WITH cte AS +( +SELECT +( +WITH x AS +(WITH x AS (SELECT a FROM x AS t) SELECT b FROM t1) +SELECT b FROM x AS r +) AS c +) +SELECT cte.c FROM cte; +c +1 +WITH cte AS +( +SELECT +( +WITH x AS +(WITH y AS (SELECT a FROM x AS t) SELECT b FROM t1) +SELECT b FROM x AS r +) AS c +) +SELECT cte.c FROM cte; +c +1 +WITH cte AS +( +SELECT +( +WITH x AS +(WITH y(b) AS (SELECT a FROM x AS t LIMIT 1) SELECT b FROM y) +SELECT b FROM x AS r +) AS c +) +SELECT cte.c FROM cte; +c +3 +WITH cte AS +( +SELECT +( +WITH x AS +(WITH x(b) AS (SELECT a FROM x AS t LIMIT 1) SELECT b FROM x) +SELECT b FROM x AS r +) AS c +) +SELECT cte.c FROM cte; +c +3 +WITH x AS +( +SELECT +( +WITH x AS +(WITH x AS (SELECT a FROM x AS t) SELECT 1 AS b) +SELECT b FROM x AS r +) AS c +) +SELECT x.c from x; +c +1 +WITH cte AS +( +SELECT +( +WITH x AS +(WITH x AS (SELECT a FROM x AS t) SELECT 2 AS b) +SELECT r1.b FROM x AS r1, x AS r2 WHERE r1.b=r2.b +) AS c +) +SELECT cte.c from cte; +c +2 +DROP TABLE x; +WITH cte AS +( +SELECT +( +WITH x AS +(WITH x AS (SELECT a FROM x AS t) SELECT 1 AS b) +SELECT b FROM x AS r +) AS c +) +SELECT cte.c FROM cte; +ERROR 42S02: Table 'test.x' doesn't exist +WITH cte AS +( +SELECT +( +WITH x AS +(WITH x AS (SELECT a FROM x AS t) SELECT b FROM t1) +SELECT b FROM x AS r +) AS c +) +SELECT cte.c FROM cte; +ERROR 42S02: Table 'test.x' doesn't exist +WITH cte AS +( +SELECT +( +WITH x AS +(WITH y AS (SELECT a FROM x AS t) SELECT b FROM t1) +SELECT b FROM x AS r +) AS c +) +SELECT cte.c FROM cte; +ERROR 42S02: Table 'test.x' doesn't exist +WITH cte AS +( +SELECT +( +WITH x AS +(WITH y(b) AS (SELECT a FROM x AS t LIMIT 1) SELECT b FROM y) +SELECT b FROM x AS r +) AS c +) +SELECT cte.c FROM cte; +ERROR 42S02: Table 'test.x' doesn't exist +WITH cte AS +( +SELECT +( +WITH x AS +(WITH x(b) AS (SELECT a FROM x AS t LIMIT 1) SELECT b FROM x) +SELECT b FROM x AS r +) AS c +) +SELECT cte.c FROM cte; +ERROR 42S02: Table 'test.x' doesn't exist +WITH x AS +( +SELECT +( +WITH x AS +(WITH x AS (SELECT a FROM x AS t) SELECT 1 AS b) +SELECT b FROM x AS r +) AS c +) +SELECT x.c from x; +ERROR 42S02: Table 'test.x' doesn't exist +WITH cte AS +( +SELECT +( +WITH x AS +(WITH x AS (SELECT a FROM x AS t) SELECT 2 AS b) +SELECT r1.b FROM x AS r1, x AS r2 WHERE r1.b=r2.b +) AS c +) +SELECT cte.c from cte; +ERROR 42S02: Table 'test.x' doesn't exist +DROP TABLE t1; +# +# End of 10.3 tests +# +# +# MDEV-26108: Recursive CTE embedded into another CTE which is used twice +# +create table t1 (a int); +insert into t1 values (5), (7); +with cte_e as ( +with recursive cte_r as ( +select a from t1 union select a+1 as a from cte_r r where a < 10 +) select * from cte_r +) select * from cte_e s1, cte_e s2 where s1.a=s2.a; +a a +5 5 +7 7 +6 6 +8 8 +9 9 +10 10 +drop table t1; +# +# MDEV-20010 Equal on two RANK window functions create wrong result +# +create table t1 (a int, b int) engine= innodb; +insert into t1 values (4, -2), (3, -1); +SELECT RANK() OVER (ORDER BY D.C) = RANK() OVER (ORDER BY B.a) FROM +(SELECT 5 AS C FROM t1) as D, (SELECT t1.b AS A FROM t1) AS B; +RANK() OVER (ORDER BY D.C) = RANK() OVER (ORDER BY B.a) +1 +1 +0 +0 +select b, rank() over (order by c) , rank() over (order by dt1.b) +from +(select 5 as c from t1) as dt, +(select b from t1) as dt1; +b rank() over (order by c) rank() over (order by dt1.b) +-2 1 1 +-2 1 1 +-1 1 3 +-1 1 3 +select b, rank() over (order by c) , rank() over (order by dt1.b), +rank() over (order by c) = rank() over (order by dt1.b) +from +(select 5 as c from t1) as dt, +(select b from t1) as dt1; +b rank() over (order by c) rank() over (order by dt1.b) rank() over (order by c) = rank() over (order by dt1.b) +-2 1 1 1 +-2 1 1 1 +-1 1 3 0 +-1 1 3 0 +alter table t1 engine=myisam; +select b, rank() over (order by c) , rank() over (order by dt1.b) +from +(select 5 as c from t1) as dt, +(select b from t1) as dt1; +b rank() over (order by c) rank() over (order by dt1.b) +-2 1 1 +-2 1 1 +-1 1 3 +-1 1 3 +create view v1 as select b,5 as c from t1; +select b, rank() over (order by c) from v1 order by b; +b rank() over (order by c) +-2 1 +-1 1 +drop view v1; +drop table t1; +# End of 10.4 tests |