summaryrefslogtreecommitdiffstats
path: root/mysql-test/main/cte_recursive.test
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2024-04-13 12:24:36 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2024-04-13 12:24:36 +0000
commit06eaf7232e9a920468c0f8d74dcf2fe8b555501c (patch)
treee2c7b5777f728320e5b5542b6213fd3591ba51e2 /mysql-test/main/cte_recursive.test
parentInitial commit. (diff)
downloadmariadb-06eaf7232e9a920468c0f8d74dcf2fe8b555501c.tar.xz
mariadb-06eaf7232e9a920468c0f8d74dcf2fe8b555501c.zip
Adding upstream version 1:10.11.6.upstream/1%10.11.6
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'mysql-test/main/cte_recursive.test')
-rw-r--r--mysql-test/main/cte_recursive.test4058
1 files changed, 4058 insertions, 0 deletions
diff --git a/mysql-test/main/cte_recursive.test b/mysql-test/main/cte_recursive.test
new file mode 100644
index 00000000..4ecdaa4f
--- /dev/null
+++ b/mysql-test/main/cte_recursive.test
@@ -0,0 +1,4058 @@
+--source include/default_optimizer_switch.inc
+# This is too slow on MSAN
+--source include/not_msan.inc
+--source include/not_valgrind.inc
+--source include/have_innodb.inc
+
+create table t1 (a int, b varchar(32));
+insert into t1 values
+(4,'aaaa' ), (7,'bb'), (1,'ccc'), (4,'dd');
+insert into t1 values
+(3,'eee'), (7,'bb'), (1,'fff'), (4,'ggg');
+
+--ERROR ER_UNACCEPTABLE_MUTUAL_RECURSION
+with recursive
+t as
+(
+ select * from t1 where t1.b >= 'c'
+ union
+ select * from r
+),
+r as
+(
+ select * from t
+ union
+ select t1.* from t1,r where r.a+1 = t1.a
+)
+select * from r;
+
+
+--ERROR ER_UNACCEPTABLE_MUTUAL_RECURSION
+with recursive
+a1(a,b) as
+(select * from t1 where t1.a>3
+union
+select * from b1 where b1.a >3
+union
+select * from c1 where c1.a>3),
+b1(a,b) as
+(select * from a1 where a1.b > 'ccc'
+union
+select * from c1 where c1.b > 'ddd'),
+c1(a,b) as
+(select * from a1 where a1.a<6 and a1.b< 'zz'
+union
+select * from b1 where b1.b > 'auu')
+select * from c1;
+
+drop table t1;
+
+
+--echo # WITH RECURSIVE vs just WITH
+
+create table t1 (a int);
+insert into t1 values
+ (0), (1), (2), (3), (4);
+create table t2 (a int);
+insert into t2 values
+ (1), (2), (3), (4), (5);
+
+
+--echo # just WITH : s refers to t defined after s
+--ERROR ER_NO_SUCH_TABLE
+with
+ s(a) as (select t.a + 10 from t),
+ t(a) as (select t1.a from t1)
+select * from s;
+
+--echo # WITH RECURSIVE: s refers to t defined after s
+with recursive
+ s(a) as (select t.a + 10 from t),
+ t(a) as (select t1.a from t1)
+select * from s;
+
+--echo # just WITH : defined t1 is non-recursive and uses base tables t1,t2
+with
+t1 as
+(
+select a from t2 where t2.a=3
+union
+select t2.a from t1,t2 where t1.a+1=t2.a
+)
+select * from t1;
+
+explain
+with
+t1 as
+(
+select a from t2 where t2.a=3
+union
+select t2.a from t1,t2 where t1.a+1=t2.a
+)
+select * from t1;
+
+
+--echo #WITH RECURSIVE : defined t1 is recursive and uses only base table t2
+with recursive
+t1 as
+(
+select a from t2 where t2.a=3
+union
+select t2.a from t1,t2 where t1.a+1=t2.a
+)
+select * from t1;
+
+explain
+with recursive
+t1 as
+(
+select a from t2 where t2.a=3
+union
+select t2.a from t1,t2 where t1.a+1=t2.a
+)
+select * from t1;
+
+--echo # just WITH : types of t1 columns are determined by all parts of union
+
+create view v1 as
+with
+t1 as
+(
+select a from t2 where t2.a=3
+union
+select t2.a+1 from t1,t2 where t1.a=t2.a
+)
+select * from t1;
+
+show columns from v1;
+
+
+--echo # WITH RECURSIVE : types of t1 columns are determined by anchor parts
+
+create view v2 as
+with recursive
+t1 as
+(
+select a from t2 where t2.a=3
+union
+select t2.a+1 from t1,t2 where t1.a=t2.a
+)
+select * from t1;
+
+show columns from v2;
+
+drop view v1,v2;
+
+drop table t1,t2;
+
+
+create table folks(id int, name char(32), dob date, father int, mother int);
+
+insert into folks values
+(100, 'Me', '2000-01-01', 20, 30),
+(20, 'Dad', '1970-02-02', 10, 9),
+(30, 'Mom', '1975-03-03', 8, 7),
+(10, 'Grandpa Bill', '1940-04-05', null, null),
+(9, 'Grandma Ann', '1941-10-15', null, null),
+(25, 'Uncle Jim', '1968-11-18', 8, 7),
+(98, 'Sister Amy', '2001-06-20', 20, 30),
+(7, 'Grandma Sally', '1943-08-23', null, 6),
+(8, 'Grandpa Ben', '1940-10-21', null, null),
+(6, 'Grandgrandma Martha', '1923-05-17', null, null),
+(67, 'Cousin Eddie', '1992-02-28', 25, 27),
+(27, 'Auntie Melinda', '1971-03-29', null, null);
+
+--echo # simple recursion with one anchor and one recursive select
+--echo # the anchor is the first select in the specification
+with recursive
+ancestors
+as
+(
+ select *
+ from folks
+ where name = 'Me' and dob = '2000-01-01'
+ union
+ select p.id, p.name, p.dob, p.father, p.mother
+ from folks as p, ancestors AS a
+ where p.id = a.father or p.id = a.mother
+)
+select * from ancestors;
+
+--echo # simple recursion with one anchor and one recursive select
+--echo # the anchor is the last select in the specification
+with recursive
+ancestors
+as
+(
+ select p.*
+ from folks as p, ancestors AS a
+ where p.id = a.father or p.id = a.mother
+ union
+ select *
+ from folks
+ where name = 'Me' and dob = '2000-01-01'
+)
+select * from ancestors;
+
+--echo # simple recursion with one anchor and one recursive select
+--echo # the anchor is the first select in the specification
+with recursive
+ancestors
+as
+(
+ select *
+ from folks
+ where name = 'Cousin Eddie'
+ union
+ select p.*
+ from folks as p, ancestors as a
+ where p.id = a.father or p.id = a.mother
+)
+select * from ancestors;
+
+--echo # simple recursion with or in anchor and or in recursive part
+with recursive
+ancestors
+as
+(
+ select *
+ from folks
+ where name = 'Me' or name='Sister Amy'
+ union
+ select p.*
+ from folks as p, ancestors as a
+ where p.id = a.father or p.id = a.mother
+)
+select * from ancestors;
+
+--echo # two recursive definition, one uses another
+with recursive
+prev_gen
+as
+(
+ select folks.*
+ from folks, prev_gen
+ where folks.id=prev_gen.father or folks.id=prev_gen.mother
+ union
+ select *
+ from folks
+ where name='Me'
+),
+ancestors
+as
+(
+ select *
+ from folks
+ where name='Me'
+ union
+ select *
+ from ancestors
+ union
+ select *
+ from prev_gen
+)
+select ancestors.name, ancestors.dob from ancestors;
+
+--echo # recursive definition with two attached non-recursive
+with recursive
+ancestors(id,name,dob)
+as
+(
+ with
+ father(child_id,id,name,dob)
+ as
+ (
+ select folks.id, f.id, f.name, f.dob
+ from folks, folks f
+ where folks.father=f.id
+
+ ),
+ mother(child_id,id,name,dob)
+ as
+ (
+ select folks.id, m.id, m.name, m.dob
+ from folks, folks m
+ where folks.mother=m.id
+
+ )
+ select folks.id, folks.name, folks.dob
+ from folks
+ where name='Me'
+ union
+ select f.id, f.name, f.dob
+ from ancestors a, father f
+ where f.child_id=a.id
+ union
+ select m.id, m.name, m.dob
+ from ancestors a, mother m
+ where m.child_id=a.id
+
+)
+select ancestors.name, ancestors.dob from ancestors;
+
+--echo # simple recursion with one anchor and one recursive select
+--echo # the anchor is the first select in the specification
+with recursive
+descendants
+as
+(
+ select *
+ from folks
+ where name = 'Grandpa Bill'
+ union
+ select folks.*
+ from folks, descendants as d
+ where d.id=folks.father or d.id=folks.mother
+)
+select * from descendants;
+
+--echo # simple recursion with one anchor and one recursive select
+--echo # the anchor is the first select in the specification
+with recursive
+descendants
+as
+(
+ select *
+ from folks
+ where name = 'Grandma Sally'
+ union
+ select folks.*
+ from folks, descendants as d
+ where d.id=folks.father or d.id=folks.mother
+)
+select * from descendants;
+
+
+--echo # simple recursive table used three times in the main query
+with recursive
+ancestors
+as
+(
+ select *
+ from folks
+ where name = 'Me' and dob = '2000-01-01'
+ union
+ select p.*
+ from folks as p, ancestors AS a
+ where p.id = a.father OR p.id = a.mother
+)
+select *
+ from ancestors t1, ancestors t2
+ where exists (select * from ancestors a
+ where a.father=t1.id AND a.mother=t2.id);
+
+
+--echo # simple recursive table used three times in the main query
+with
+ancestor_couples(husband, h_dob, wife, w_dob)
+as
+(
+with recursive
+ancestors
+as
+(
+ select *
+ from folks
+ where name = 'Me'
+ union
+ select p.*
+ from folks as p, ancestors AS a
+ where p.id = a.father OR p.id = a.mother
+)
+select t1.name, t1.dob, t2.name, t2.dob
+ from ancestors t1, ancestors t2
+ where exists (select * from ancestors a
+ where a.father=t1.id AND a.mother=t2.id)
+)
+select * from ancestor_couples;
+
+
+--echo # simple recursion with two selects in recursive part
+with recursive
+ancestors
+as
+(
+ select *
+ from folks
+ where name = 'Me'
+ union
+ select p.*
+ from folks as p, ancestors as fa
+ where p.id = fa.father
+ union
+ select p.*
+ from folks as p, ancestors as ma
+ where p.id = ma.mother
+)
+select * from ancestors;
+
+
+--echo # mutual recursion with renaming
+with recursive
+ancestor_couples(h_id, h_name, h_dob, h_father, h_mother,
+ w_id, w_name, w_dob, w_father, w_mother)
+as
+(
+ select h.*, w.*
+ from folks h, folks w, coupled_ancestors a
+ where a.father = h.id AND a.mother = w.id
+ union
+ select h.*, w.*
+ from folks v, folks h, folks w
+ where v.name = 'Me' and
+ (v.father = h.id AND v.mother= w.id)
+),
+coupled_ancestors (id, name, dob, father, mother)
+as
+(
+ select h_id, h_name, h_dob, h_father, h_mother
+ from ancestor_couples
+ union
+ select w_id, w_name, w_dob, w_father, w_mother
+ from ancestor_couples
+)
+select h_name, h_dob, w_name, w_dob
+ from ancestor_couples;
+
+
+--echo # mutual recursion with union all
+with recursive
+ancestor_couples(h_id, h_name, h_dob, h_father, h_mother,
+ w_id, w_name, w_dob, w_father, w_mother)
+as
+(
+ select h.*, w.*
+ from folks h, folks w, coupled_ancestors a
+ where a.father = h.id AND a.mother = w.id
+ union
+ select h.*, w.*
+ from folks v, folks h, folks w
+ where v.name = 'Me' and
+ (v.father = h.id AND v.mother= w.id)
+),
+coupled_ancestors (id, name, dob, father, mother)
+as
+(
+ select h_id, h_name, h_dob, h_father, h_mother
+ from ancestor_couples
+ union all
+ select w_id, w_name, w_dob, w_father, w_mother
+ from ancestor_couples
+)
+select h_name, h_dob, w_name, w_dob
+ from ancestor_couples;
+
+
+--echo # mutual recursion with renaming
+with recursive
+ancestor_couples(h_id, h_name, h_dob, h_father, h_mother,
+ w_id, w_name, w_dob, w_father, w_mother)
+as
+(
+ select h.*, w.*
+ from folks h, folks w, coupled_ancestors a
+ where a.father = h.id AND a.mother = w.id
+ union
+ select h.*, w.*
+ from folks v, folks h, folks w
+ where v.name = 'Me' and
+ (v.father = h.id AND v.mother= w.id)
+),
+coupled_ancestors (id, name, dob, father, mother)
+as
+(
+ select h_id, h_name, h_dob, h_father, h_mother
+ from ancestor_couples
+ union
+ select w_id, w_name, w_dob, w_father, w_mother
+ from ancestor_couples
+)
+select h_name, h_dob, w_name, w_dob
+ from ancestor_couples;
+
+
+--echo # mutual recursion with union all
+with recursive
+ancestor_couples(h_id, h_name, h_dob, h_father, h_mother,
+ w_id, w_name, w_dob, w_father, w_mother)
+as
+(
+ select h.*, w.*
+ from folks h, folks w, coupled_ancestors a
+ where a.father = h.id AND a.mother = w.id
+),
+coupled_ancestors (id, name, dob, father, mother)
+as
+(
+ select *
+ from folks
+ where name = 'Me'
+ union all
+ select h_id, h_name, h_dob, h_father, h_mother
+ from ancestor_couples
+ union all
+ select w_id, w_name, w_dob, w_father, w_mother
+ from ancestor_couples
+)
+select h_name, h_dob, w_name, w_dob
+ from ancestor_couples;
+
+--echo # mutual recursion with one select in the first definition
+with recursive
+ancestor_couple_ids(h_id, w_id)
+as
+(
+ select a.father, a.mother
+ from coupled_ancestors a
+ where a.father is not null and a.mother is not null
+),
+coupled_ancestors (id, name, dob, father, mother)
+as
+(
+ select *
+ from folks
+ where name = 'Me'
+ union all
+ select p.*
+ from folks p, ancestor_couple_ids fa
+ where p.id = fa.h_id
+ union all
+ select p.*
+ from folks p, ancestor_couple_ids ma
+ where p.id = ma.w_id
+)
+select *
+ from ancestor_couple_ids;
+
+
+--echo # join of a mutually recursive table with base tables
+with recursive
+ancestor_couple_ids(h_id, w_id)
+as
+(
+ select a.father, a.mother
+ from coupled_ancestors a
+ where a.father is not null and a.mother is not null
+),
+coupled_ancestors (id, name, dob, father, mother)
+as
+(
+ select *
+ from folks
+ where name = 'Me'
+ union all
+ select p.*
+ from folks p, ancestor_couple_ids fa
+ where p.id = fa.h_id
+ union all
+ select p.*
+ from folks p, ancestor_couple_ids ma
+ where p.id = ma.w_id
+)
+select h.name, h.dob, w.name, w.dob
+ from ancestor_couple_ids c, folks h, folks w
+ where c.h_id = h.id and c.w_id= w.id;
+
+
+--echo # join of two mutually recursive tables
+with recursive
+ancestor_couple_ids(h_id, w_id)
+as
+(
+ select a.father, a.mother
+ from coupled_ancestors a
+ where a.father is not null and a.mother is not null
+),
+coupled_ancestors (id, name, dob, father, mother)
+as
+(
+ select *
+ from folks
+ where name = 'Me'
+ union all
+ select p.*
+ from folks p, ancestor_couple_ids fa
+ where p.id = fa.h_id
+ union all
+ select p.*
+ from folks p, ancestor_couple_ids ma
+ where p.id = ma.w_id
+)
+select h.name, h.dob, w.name, w.dob
+ from ancestor_couple_ids c, coupled_ancestors h, coupled_ancestors w
+ where c.h_id = h.id and c.w_id= w.id;
+
+explain extended
+with recursive
+ancestor_couple_ids(h_id, w_id)
+as
+(
+ select a.father, a.mother
+ from coupled_ancestors a
+ where a.father is not null and a.mother is not null
+),
+coupled_ancestors (id, name, dob, father, mother)
+as
+(
+ select *
+ from folks
+ where name = 'Me'
+ union all
+ select p.*
+ from folks p, ancestor_couple_ids fa
+ where p.id = fa.h_id
+ union all
+ select p.*
+ from folks p, ancestor_couple_ids ma
+ where p.id = ma.w_id
+)
+select h.name, h.dob, w.name, w.dob
+ from ancestor_couple_ids c, coupled_ancestors h, coupled_ancestors w
+ where c.h_id = h.id and c.w_id= w.id;
+
+
+--echo # simple mutual recursion
+with recursive
+ancestor_couple_ids(h_id, w_id)
+as
+(
+ select a.father, a.mother
+ from coupled_ancestors a
+),
+coupled_ancestors (id, name, dob, father, mother)
+as
+(
+ select *
+ from folks
+ where name = 'Me'
+ union all
+ select p.*
+ from folks p, ancestor_couple_ids fa
+ where p.id = fa.h_id
+ union all
+ select p.*
+ from folks p, ancestor_couple_ids ma
+ where p.id = ma.w_id
+)
+select *
+ from ancestor_couple_ids;
+
+
+--echo # join of two mutually recursive tables
+with recursive
+ancestor_couple_ids(h_id, w_id)
+as
+(
+ select a.father, a.mother
+ from coupled_ancestors a
+),
+coupled_ancestors (id, name, dob, father, mother)
+as
+(
+ select *
+ from folks
+ where name = 'Me'
+ union all
+ select p.*
+ from folks p, ancestor_couple_ids fa
+ where p.id = fa.h_id
+ union all
+ select p.*
+ from folks p, ancestor_couple_ids ma
+ where p.id = ma.w_id
+)
+select h.name, h.dob, w.name, w.dob
+ from ancestor_couple_ids c, coupled_ancestors h, coupled_ancestors w
+ where c.h_id = h.id and c.w_id= w.id;
+
+
+--echo # execution of prepared query using a recursive table
+prepare stmt1 from "
+with recursive
+ancestors
+as
+(
+ select *
+ from folks
+ where name = 'Me' and dob = '2000-01-01'
+ union
+ select p.id, p.name, p.dob, p.father, p.mother
+ from folks as p, ancestors AS a
+ where p.id = a.father or p.id = a.mother
+)
+select * from ancestors;
+";
+
+execute stmt1;
+execute stmt1;
+
+deallocate prepare stmt1;
+
+
+--echo # view using a recursive table
+create view v1 as
+with recursive
+ancestors
+as
+(
+ select *
+ from folks
+ where name = 'Me' and dob = '2000-01-01'
+ union
+ select p.id, p.name, p.dob, p.father, p.mother
+ from folks as p, ancestors AS a
+ where p.id = a.father or p.id = a.mother
+)
+select * from ancestors;
+
+show create view v1;
+
+select * from v1;
+
+create view v2 as
+with recursive
+ancestors
+as
+(
+ select *
+ from folks
+ where name = 'Me'
+ union
+ select p.*
+ from folks as p, ancestors as fa
+ where p.id = fa.father
+ union
+ select p.*
+ from folks as p, ancestors as ma
+ where p.id = ma.mother
+)
+select * from ancestors;
+
+show create view v2;
+
+select * from v2;
+
+drop view v1,v2;
+
+
+explain extended
+with recursive
+ancestors
+as
+(
+ select *
+ from folks
+ where name = 'Me' and dob = '2000-01-01'
+ union
+ select p.id, p.name, p.dob, p.father, p.mother
+ from folks as p, ancestors AS a
+ where p.id = a.father or p.id = a.mother
+)
+select * from ancestors;
+
+
+--echo # recursive spec with two anchor selects and two recursive ones
+with recursive
+ancestor_ids (id)
+as
+(
+ select father from folks where name = 'Me'
+ union
+ select mother from folks where name = 'Me'
+ union
+ select father from folks, ancestor_ids a where folks.id = a.id
+ union
+ select mother from folks, ancestor_ids a where folks.id = a.id
+),
+ancestors
+as
+(
+ select p.* from folks as p, ancestor_ids as a
+ where p.id = a.id
+)
+select * from ancestors;
+
+
+--echo # recursive spec using union all
+with recursive
+ancestors
+as
+(
+ select *
+ from folks
+ where name = 'Me'
+ union all
+ select p.*
+ from folks as p, ancestors as fa
+ where p.id = fa.father
+ union all
+ select p.*
+ from folks as p, ancestors as ma
+ where p.id = ma.mother
+)
+select * from ancestors;
+
+
+--ERROR ER_NOT_STANDARD_COMPLIANT_RECURSIVE
+with recursive
+ancestor_ids (id, generation)
+as
+(
+ select father, 1 from folks where name = 'Me' and father is not null
+ union all
+ select mother, 1 from folks where name = 'Me' and mother is not null
+ union all
+ select father, fa.generation+1 from folks, ancestor_ids fa
+ where folks.id = fa.id and (father not in (select id from ancestor_ids))
+ union all
+ select mother, ma.generation+1 from folks, ancestor_ids ma
+ where folks.id = ma.id and (mother not in (select id from ancestor_ids))
+)
+select generation, name from ancestor_ids a, folks
+ where a.id = folks.id;
+
+set standard_compliant_cte=0;
+
+--ERROR ER_WITH_COL_WRONG_LIST
+with recursive
+ancestor_ids (id, generation)
+as
+(
+ select father from folks where name = 'Me' and father is not null
+ union all
+ select mother from folks where name = 'Me' and mother is not null
+ union all
+ select father, fa.generation+1 from folks, ancestor_ids fa
+ where folks.id = fa.id and (father not in (select id from ancestor_ids))
+ union all
+ select mother, ma.generation+1 from folks, ancestor_ids ma
+ where folks.id = ma.id and (mother not in (select id from ancestor_ids))
+)
+select generation, name from ancestor_ids a, folks
+ where a.id = folks.id;
+
+with recursive
+ancestor_ids (id, generation)
+as
+(
+ select father, 1 from folks where name = 'Me' and father is not null
+ union all
+ select mother, 1 from folks where name = 'Me' and mother is not null
+ union all
+ select father, fa.generation+1 from folks, ancestor_ids fa
+ where folks.id = fa.id and father is not null and
+ (father not in (select id from ancestor_ids))
+ union all
+ select mother, ma.generation+1 from folks, ancestor_ids ma
+ where folks.id = ma.id and mother is not null and
+ (mother not in (select id from ancestor_ids))
+)
+select generation, name from ancestor_ids a, folks
+ where a.id = folks.id;
+
+set standard_compliant_cte=1;
+
+--ERROR ER_NOT_STANDARD_COMPLIANT_RECURSIVE
+with recursive
+coupled_ancestor_ids (id)
+as
+(
+ select father from folks where name = 'Me' and father is not null
+ union
+ select mother from folks where name = 'Me' and mother is not null
+ union
+ select n.father
+ from folks, coupled_ancestor_ids fa, coupled_ancestor_ids ma, folks n
+ where folks.father = fa.id and folks.mother = ma.id and
+ (fa.id = n.id or ma.id = n.id) and
+ n.father is not null and n.mother is not null
+ union
+ select n.mother
+ from folks, coupled_ancestor_ids fa, coupled_ancestor_ids ma, folks n
+ where folks.father = fa.id and folks.mother = ma.id and
+ (fa.id = n.id or ma.id = n.id) and
+ n.father is not null and n.mother is not null
+)
+select p.* from coupled_ancestor_ids a, folks p
+ where a.id = p.id;
+
+set statement standard_compliant_cte=0 for
+with recursive
+coupled_ancestor_ids (id)
+as
+(
+ select father from folks where name = 'Me' and father is not null
+ union
+ select mother from folks where name = 'Me' and mother is not null
+ union
+ select n.father
+ from folks, coupled_ancestor_ids fa, coupled_ancestor_ids ma, folks n
+ where folks.father = fa.id and folks.mother = ma.id and
+ (fa.id = n.id or ma.id = n.id) and
+ n.father is not null and n.mother is not null
+ union
+ select n.mother
+ from folks, coupled_ancestor_ids fa, coupled_ancestor_ids ma, folks n
+ where folks.father = fa.id and folks.mother = ma.id and
+ (fa.id = n.id or ma.id = n.id) and
+ n.father is not null and n.mother is not null
+)
+select p.* from coupled_ancestor_ids a, folks p
+ where a.id = p.id;
+
+--ERROR ER_NOT_STANDARD_COMPLIANT_RECURSIVE
+with recursive
+ancestor_ids (id)
+as
+(
+ select father from folks where name = 'Me'
+ union
+ select mother from folks where name = 'Me'
+ union
+ select father from folks left join ancestor_ids a on folks.id = a.id
+ union
+ select mother from folks left join ancestor_ids a on folks.id = a.id
+),
+ancestors
+as
+(
+ select p.* from folks as p, ancestor_ids as a
+ where p.id = a.id
+)
+select * from ancestors;
+
+set statement standard_compliant_cte=0 for
+with recursive
+ancestor_ids (id)
+as
+(
+ select father from folks where name = 'Me'
+ union
+ select mother from folks where name = 'Me'
+ union
+ select father from folks left join ancestor_ids a on folks.id = a.id
+ union
+ select mother from folks left join ancestor_ids a on folks.id = a.id
+),
+ancestors
+as
+(
+ select p.* from folks as p, ancestor_ids as a
+ where p.id = a.id
+)
+select * from ancestors;
+
+with recursive
+ancestor_ids (id, generation)
+as
+(
+ select father, 1 from folks where name = 'Me'
+ union
+ select mother, 1 from folks where name = 'Me'
+ union
+ select father, a.generation+1 from folks, ancestor_ids a
+ where folks.id = a.id
+ union
+ select mother, a.generation+1 from folks, ancestor_ids a
+ where folks.id = a.id
+),
+ancestors
+as
+(
+ select generation, name from folks as p, ancestor_ids as a
+ where p.id = a.id
+)
+select * from ancestors;
+
+--ERROR ER_NOT_STANDARD_COMPLIANT_RECURSIVE
+with recursive
+ancestor_ids (id, generation)
+as
+(
+ select father, 1 from folks where name = 'Me'
+ union
+ select mother, 1 from folks where name = 'Me'
+ union
+ select max(father), max(a.generation)+1 from folks, ancestor_ids a
+ where folks.id = a.id
+ group by a.generation
+ union
+ select max(mother), max(a.generation)+1 from folks, ancestor_ids a
+ where folks.id = a.id
+ group by a.generation
+),
+ancestors
+as
+(
+ select generation, name from folks as p, ancestor_ids as a
+ where p.id = a.id
+)
+select * from ancestors;
+
+set statement standard_compliant_cte=0 for
+with recursive
+ancestor_ids (id, generation)
+as
+(
+ select father, 1 from folks where name = 'Me'
+ union
+ select mother, 1 from folks where name = 'Me'
+ union
+ select max(father), a.generation+1 from folks, ancestor_ids a
+ where folks.id = a.id
+ group by a.generation
+ union
+ select max(mother), a.generation+1 from folks, ancestor_ids a
+ where folks.id = a.id
+ group by a.generation
+),
+ancestors
+as
+(
+ select generation, name from folks as p, ancestor_ids as a
+ where p.id = a.id
+)
+select * from ancestors;
+
+set statement max_recursive_iterations=1 for
+with recursive
+ancestor_ids (id, generation)
+as
+(
+ select father, 1 from folks where name = 'Me'
+ union
+ select mother, 1 from folks where name = 'Me'
+ union
+ select father, a.generation+1 from folks, ancestor_ids a
+ where folks.id = a.id
+ union
+ select mother, a.generation+1 from folks, ancestor_ids a
+ where folks.id = a.id
+),
+ancestors
+as
+(
+ select generation, name from folks as p, ancestor_ids as a
+ where p.id = a.id
+)
+select * from ancestors;
+
+--echo # query with recursive tables using key access
+
+alter table folks add primary key (id);
+
+explain
+with recursive
+ancestors
+as
+(
+ select *
+ from folks
+ where name = 'Me'
+ union
+ select p.*
+ from folks as p, ancestors as fa
+ where p.id = fa.father
+ union
+ select p.*
+ from folks as p, ancestors as ma
+ where p.id = ma.mother
+)
+select * from ancestors;
+
+
+with recursive
+ancestors
+as
+(
+ select *
+ from folks
+ where name = 'Me'
+ union
+ select p.*
+ from folks as p, ancestors as fa
+ where p.id = fa.father
+ union
+ select p.*
+ from folks as p, ancestors as ma
+ where p.id = ma.mother
+)
+select * from ancestors;
+
+
+--echo #
+--echo # EXPLAIN FORMAT=JSON on a query where one recursive CTE uses another:
+--echo #
+explain
+with recursive
+prev_gen
+as
+(
+ select folks.*
+ from folks, prev_gen
+ where folks.id=prev_gen.father or folks.id=prev_gen.mother
+ union
+ select *
+ from folks
+ where name='Me'
+),
+ancestors
+as
+(
+ select *
+ from folks
+ where name='Me'
+ union
+ select *
+ from ancestors
+ union
+ select *
+ from prev_gen
+)
+select ancestors.name, ancestors.dob from ancestors;
+
+explain FORMAT=JSON
+with recursive
+prev_gen
+as
+(
+ select folks.*
+ from folks, prev_gen
+ where folks.id=prev_gen.father or folks.id=prev_gen.mother
+ union
+ select *
+ from folks
+ where name='Me'
+),
+ancestors
+as
+(
+ select *
+ from folks
+ where name='Me2'
+ union
+ select *
+ from ancestors where id < 234
+ union
+ select *
+ from prev_gen where id < 345
+)
+select ancestors.name, ancestors.dob from ancestors;
+
+--echo #
+explain format=json
+with recursive
+ancestor_couples(h_id, h_name, h_dob, h_father, h_mother,
+ w_id, w_name, w_dob, w_father, w_mother)
+as
+(
+ select h.*, w.*
+ from folks h, folks w, coupled_ancestors a
+ where a.father = h.id AND a.mother = w.id
+ union
+ select h.*, w.*
+ from folks v, folks h, folks w
+ where v.name = 'Me' and
+ (v.father = h.id AND v.mother= w.id)
+),
+coupled_ancestors (id, name, dob, father, mother)
+as
+(
+ select h_id, h_name, h_dob, h_father, h_mother
+ from ancestor_couples
+ union all
+ select w_id, w_name, w_dob, w_father, w_mother
+ from ancestor_couples
+)
+select h_name, h_dob, w_name, w_dob
+ from ancestor_couples;
+
+
+create table my_ancestors
+with recursive
+ancestor_ids (id)
+as
+(
+ select father from folks where name = 'Me'
+ union
+ select mother from folks where name = 'Me'
+ union
+ select father from folks, ancestor_ids a where folks.id = a.id
+ union
+ select mother from folks, ancestor_ids a where folks.id = a.id
+)
+select p.* from folks as p, ancestor_ids as a where p.id = a.id;
+
+select * from my_ancestors;
+
+delete from my_ancestors;
+
+insert into my_ancestors
+with recursive
+ancestor_ids (id)
+as
+(
+ select father from folks where name = 'Me'
+ union
+ select mother from folks where name = 'Me'
+ union
+ select father from folks, ancestor_ids a where folks.id = a.id
+ union
+ select mother from folks, ancestor_ids a where folks.id = a.id
+)
+select p.* from folks as p, ancestor_ids as a where p.id = a.id;
+
+select * from my_ancestors;
+
+drop table my_ancestors;
+
+#
+# MDEV-17967 Add a solution of the 8 queens problem to the regression test for CTE
+#
+# adapted to MariaDB from https://rosettacode.org/wiki/N-queens_problem#SQL
+#
+let $N=4; # 8 takes too long for a test
+eval WITH RECURSIVE
+ positions(i) AS (
+ VALUES(0)
+ UNION SELECT ALL
+ i+1 FROM positions WHERE i < $N*$N-1
+ ),
+ solutions(board, n_queens) AS (
+ SELECT REPEAT('-', $N*$N), 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 < $N
+ AND substr(board,1,i) != '*'
+ AND NOT EXISTS (
+ SELECT 1 FROM positions WHERE
+ substr(board,i+1,1) = '*' AND
+ (
+ i % $N = ps.i % $N OR
+ i div $N = ps.i div $N OR
+ i div $N + (i % $N) = ps.i div $N + (ps.i % $N) OR
+ i div $N - (i % $N) = ps.i div $N - (ps.i % $N)
+ )
+ )
+ )
+SELECT regexp_replace(board,concat('(',REPEAT('.', $N),')'),'\\\\1\\n') n_queens FROM solutions WHERE n_queens = $N;
+
+--echo #
+--echo # MDEV-10883: execution of prepared statement from SELECT
+--echo # with recursive CTE that renames columns
+--echo #
+
+prepare stmt from"
+with recursive
+ancestor_ids (id)
+as
+(
+ select father from folks where name = 'Me'
+ union
+ select mother from folks where name = 'Me'
+ union
+ select father from folks, ancestor_ids a where folks.id = a.id
+ union
+ select mother from folks, ancestor_ids a where folks.id = a.id
+)
+select p.* from folks as p, ancestor_ids as a where p.id = a.id;
+";
+execute stmt;
+deallocate prepare stmt;
+
+--echo #
+--echo # MDEV-10881: execution of prepared statement from
+--echo # CREATE ... SELECT, INSERT ... SELECT
+--echo #
+
+prepare stmt from"
+create table my_ancestors
+with recursive
+ancestor_ids (id)
+as
+(
+ select father from folks where name = 'Me'
+ union
+ select mother from folks where name = 'Me'
+ union
+ select father from folks, ancestor_ids a where folks.id = a.id
+ union
+ select mother from folks, ancestor_ids a where folks.id = a.id
+)
+select p.* from folks as p, ancestor_ids as a where p.id = a.id;
+";
+execute stmt;
+deallocate prepare stmt;
+select * from my_ancestors;
+
+delete from my_ancestors;
+
+prepare stmt from"
+insert into my_ancestors
+with recursive
+ancestor_ids (id)
+as
+(
+ select father from folks where name = 'Me'
+ union
+ select mother from folks where name = 'Me'
+ union
+ select father from folks, ancestor_ids a where folks.id = a.id
+ union
+ select mother from folks, ancestor_ids a where folks.id = a.id
+)
+select p.* from folks as p, ancestor_ids as a where p.id = a.id;
+";
+execute stmt;
+deallocate prepare stmt;
+select * from my_ancestors;
+
+drop table my_ancestors;
+
+--echo #
+--echo # MDEV-10933: WITH clause together with SELECT in parenthesis
+--echo # CREATE SELECT
+--echo #
+
+create table my_ancestors
+(
+with recursive
+ancestor_ids (id)
+as
+(
+ select father from folks where name = 'Me'
+ union
+ select mother from folks where name = 'Me'
+ union
+ select father from folks, ancestor_ids a where folks.id = a.id
+ union
+ select mother from folks, ancestor_ids a where folks.id = a.id
+)
+select p.* from folks as p, ancestor_ids as a where p.id = a.id
+);
+select * from my_ancestors;
+drop table my_ancestors;
+
+drop table folks;
+
+--echo #
+--echo # MDEV-10372: [bb-10.2-mdev9864 tree] EXPLAIN with recursive CTE enters endless recursion
+--echo #
+create table t1(a int);
+insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+
+explain format=json
+with recursive t as (select a from t1 union select a+10 from t where a < 1000)
+select * from t;
+
+drop table t1;
+
+
+--echo #
+--echo # MDEV-10737: recursive union with several anchors at the end
+--echo #
+
+WITH RECURSIVE cte(n) AS
+ ( SELECT n+1 FROM cte WHERE n < 5 UNION SELECT 1 UNION SELECT 1 )
+SELECT * FROM cte;
+
+--echo #
+--echo # MDEV-10736: recursive definition with anchor over a table with blob
+--echo #
+
+CREATE TABLE t1 (f VARCHAR(1024));
+WITH RECURSIVE cte(f) AS
+ (SELECT t1.f FROM t1 UNION ALL SELECT cte.f FROM cte)
+SELECT * FROM cte as t;
+DROP TABLE t1;
+
+--echo #
+--echo # MDEV-10899: mergeable derived in the spec of recursive CTE
+--echo #
+
+create table t1 (a int);
+insert into t1 values
+ (0), (1), (2), (3), (4);
+create table t2 (a int);
+insert into t2 values
+ (1), (2), (3), (4), (5);
+
+with recursive
+t1 as
+(
+select x.a from (select a from t2 where t2.a=3) x
+union
+select t2.a from t1,t2 where t1.a+1=t2.a
+)
+select * from t1;
+
+explain
+with recursive
+t1 as
+(
+select x.a from (select a from t2 where t2.a=3) x
+union
+select t2.a from t1,t2 where t1.a+1=t2.a
+)
+select * from t1;
+
+drop table t1,t2;
+
+--echo #
+--echo # MDEV-11278: non-mergeable view in the spec of recursive CTE
+--echo #
+
+create table t1 (a int);
+insert into t1 values
+ (0), (1), (2), (3), (4);
+create table t2 (a int);
+insert into t2 values
+ (1), (2), (3), (4), (5);
+
+create view v1 as
+ select a from t2 where a < 3
+ union
+ select a from t2 where a > 4;
+
+with recursive
+t1 as
+(
+select a from v1 where a=1
+union
+select v1.a from t1,v1 where t1.a+1=v1.a
+)
+select * from t1;
+
+drop view v1;
+drop table t1,t2;
+
+
+--echo #
+--echo # MDEV-11259: recursive CTE with concatenation operation
+--echo #
+
+DROP TABLE IF EXISTS edges;
+CREATE TABLE edges(
+ a int(10) unsigned NOT NULL,
+ b int(10) unsigned NOT NULL,
+ PRIMARY KEY (a,b),
+ KEY b(b)
+);
+
+INSERT INTO edges
+ VALUES (1,3),(2,1),(2,4),(3,4),(3,5),(3,6),(4,7),(5,1),(5,6),(6,1);
+
+DROP TABLE IF EXISTS edges2;
+CREATE VIEW edges2 (a, b) AS
+ SELECT a, b FROM edges UNION ALL SELECT b, a FROM edges;
+
+--sorted_result
+WITH RECURSIVE transitive_closure(a, b, distance, path_string) AS
+( SELECT a, b, 1 AS distance,
+ concat(a, '.', b, '.') AS path_string
+ FROM edges
+
+ UNION ALL
+
+ SELECT tc.a, e.b, tc.distance + 1,
+ concat(tc.path_string, e.b, '.') AS path_string
+ FROM edges AS e
+ JOIN transitive_closure AS tc
+ ON e.a = tc.b
+ WHERE tc.path_string NOT LIKE concat('%', e.b, '.%')
+)
+SELECT * FROM transitive_closure
+ORDER BY a, b, distance;
+
+--sorted_result
+WITH RECURSIVE transitive_closure(a, b, distance, path_string) AS
+( SELECT a, b, 1 AS distance,
+ concat(a, '.', b, '.') AS path_string
+ FROM edges
+ WHERE a = 1 -- source
+
+ UNION ALL
+
+ SELECT tc.a, e.b, tc.distance + 1,
+ concat(tc.path_string, e.b, '.') AS path_string
+ FROM edges AS e
+ JOIN transitive_closure AS tc ON e.a = tc.b
+ WHERE tc.path_string NOT LIKE concat('%', e.b, '.%')
+)
+ SELECT * FROM transitive_closure
+ WHERE b = 6 -- destination
+ORDER BY a, b, distance;
+
+--sorted_result
+WITH RECURSIVE transitive_closure(a, b, distance, path_string) AS
+( SELECT a, b, 1 AS distance,
+ concat(a, '.', b, '.') AS path_string
+ FROM edges2
+
+ UNION ALL
+
+ SELECT tc.a, e.b, tc.distance + 1,
+ concat(tc.path_string, e.b, '.') AS path_string
+ FROM edges2 AS e
+ JOIN transitive_closure AS tc ON e.a = tc.b
+ WHERE tc.path_string NOT LIKE concat('%', e.b, '.%')
+)
+SELECT * FROM transitive_closure
+ORDER BY a, b, distance;
+
+--sorted_result
+WITH RECURSIVE transitive_closure(a, b, distance, path_string)
+AS
+( SELECT a, b, 1 AS distance,
+ concat(a, '.', b, '.') AS path_string
+ FROM edges2
+
+ UNION ALL
+
+ SELECT tc.a, e.b, tc.distance + 1,
+ concat(tc.path_string, e.b, '.') AS path_string
+ FROM edges2 AS e
+ JOIN transitive_closure AS tc ON e.a = tc.b
+ WHERE tc.path_string NOT LIKE concat('%', e.b, '.%')
+)
+SELECT a, b, min(distance) AS dist FROM transitive_closure
+GROUP BY a, b
+ORDER BY a, dist, b;
+
+DROP VIEW edges2;
+DROP TABLE edges;
+
+
+--echo #
+--echo # MDEV-11674: recursive CTE table that cannot be stored
+--echo # in a heap table
+--echo #
+
+create table t1 (id int, test_data varchar(36));
+
+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;
+
+--echo #
+--echo # MDEV-10773: ANALYZE for query with recursive CTE
+--echo #
+
+--source include/analyze-format.inc
+analyze format=json
+with recursive src(counter) as
+(select 1
+ union
+ select counter+1 from src where counter<10
+) select * from src;
+
+--echo #
+--echo # mdev-12360: recursive reference in left operand of LEFT JOIN
+--echo #
+
+create table folks(id int, name char(32), dob date, father int, mother int);
+
+insert into folks values
+(100, 'Me', '2000-01-01', 20, 30),
+(20, 'Dad', '1970-02-02', 10, 9),
+(30, 'Mom', '1975-03-03', 8, 7),
+(10, 'Grandpa Bill', '1940-04-05', null, null),
+(9, 'Grandma Ann', '1941-10-15', null, null),
+(25, 'Uncle Jim', '1968-11-18', 8, 7),
+(98, 'Sister Amy', '2001-06-20', 20, 30),
+(7, 'Grandma Sally', '1943-08-23', null, 6),
+(8, 'Grandpa Ben', '1940-10-21', null, null),
+(6, 'Grandgrandma Martha', '1923-05-17', null, null),
+(67, 'Cousin Eddie', '1992-02-28', 25, 27),
+(27, 'Auntie Melinda', '1971-03-29', null, null);
+
+with recursive
+ancestor_ids (id)
+as
+(
+ select father from folks where name = 'Me'
+ union
+ select mother from folks where name = 'Me'
+ union
+ select father from ancestor_ids as a left join folks on folks.id = a.id
+ union
+ select mother from ancestor_ids as a left join folks on folks.id = a.id
+),
+ancestors
+as
+(
+ select p.* from folks as p, ancestor_ids as a
+ where p.id = a.id
+)
+select * from ancestors;
+
+drop table folks;
+
+--echo #
+--echo # mdev-12368: crash with mutually recursive CTE
+--echo # that arenot Standard compliant
+--echo #
+
+create table value_nodes (v char(4));
+create table module_nodes(m char(4));
+create table module_arguments(m char(4), v char(4));
+create table module_results(m char(4), v char(4));
+
+--ERROR ER_NOT_STANDARD_COMPLIANT_RECURSIVE
+with recursive
+reached_values as
+(
+ select v from value_nodes where v in ('v3','v7','v9')
+ union
+ select module_results.v from module_results, applied_modules
+ where module_results.m = applied_modules.m
+),
+applied_modules as
+(
+ select module_nodes.m
+ from
+ module_nodes
+ left join
+ (
+ module_arguments
+ left join
+ reached_values
+ on module_arguments.v = reached_values.v
+ )
+ on reached_values.v is null and
+ module_nodes.m = module_arguments.m
+ where module_arguments.m is null
+)
+select * from reached_values;
+
+drop table value_nodes, module_nodes, module_arguments, module_results;
+
+--echo #
+--echo # mdev-12375: query using one of two mutually recursive CTEs
+--echo # whose non-recursive part returns an empty set
+--echo #
+
+create table value_nodes (v char(4));
+insert into value_nodes values
+ ('v1'), ('v2'), ('v3'), ('v4'), ('v5'), ('v6'), ('v7'), ('v8'), ('v9'),
+ ('v10'), ('v11'), ('v12'), ('v13'), ('v14'), ('v15'), ('v16');
+create table module_nodes(m char(4));
+insert into module_nodes values
+ ('m1'), ('m2'), ('m3'), ('m4'), ('m5'), ('m6'), ('m7');
+create table module_arguments(m char(4), v char(4));
+insert into module_arguments values
+ ('m1','v3'), ('m1','v9'),
+ ('m2','v4'), ('m2','v3'), ('m2','v7'),
+ ('m3','v6'),
+ ('m4','v4'), ('m4','v1'),
+ ('m5','v10'), ('m5','v8'), ('m5','v3'),
+ ('m6','v8'), ('m6','v1'),
+ ('m7','v11'), ('m7','v12');
+create table module_results(m char(4), v char(4));
+insert into module_results values
+ ('m1','v4'),
+ ('m2','v1'), ('m2','v6'),
+ ('m3','v10'),
+ ('m4','v8'),
+ ('m5','v11'), ('m5','v9'),
+ ('m6','v12'), ('m6','v4'),
+ ('m7','v2');
+
+set statement max_recursive_iterations=2, standard_compliant_cte=0 for
+with recursive
+reached_values as
+(
+ select v from value_nodes where v in ('v3','v7','v9')
+ union
+ select module_results.v from module_results, applied_modules
+ where module_results.m = applied_modules.m
+),
+applied_modules as
+(
+ select * from module_nodes where 1=0
+ union
+ select module_nodes.m
+ from
+ module_nodes
+ left join
+ (
+ module_arguments
+ left join
+ reached_values
+ on module_arguments.v = reached_values.v
+ )
+ on reached_values.v is null and
+ module_nodes.m = module_arguments.m
+ where module_arguments.m is null
+)
+select * from applied_modules;
+
+drop table value_nodes, module_nodes, module_arguments, module_results;
+
+--echo #
+--echo # mdev-12519: recursive references in subqueries
+--echo #
+
+create table t1 (lp char(4) not null, rp char(4) not null);
+insert into t1 values
+ ('p1','p2'), ('p2','p3'), ('p3','p4'), ('p4','p5'),
+ ('p2','p7'), ('p7','p8'), ('p8','p3'), ('p8','p4');
+
+set standard_compliant_cte=0;
+
+with recursive
+reachables(p) as
+(
+ select lp from t1 where lp = 'p1'
+ union
+ select t1.rp from reachables, t1
+ where t1.lp = reachables.p
+)
+select * from reachables;
+
+with recursive
+reachables(p) as
+(
+ select lp from t1 where lp = 'p1'
+ union
+ select t1.rp from reachables, t1
+ where 'p3' not in (select * from reachables) and
+ t1.lp = reachables.p
+)
+select * from reachables;
+
+with recursive
+reachables(p) as
+(
+ select lp from t1 where lp = 'p1'
+ union
+ select t1.rp from reachables, t1
+ where 'p3' not in (select p from reachables where p <= 'p5'
+ union
+ select p from reachables where p > 'p5') and
+ t1.lp = reachables.p
+)
+select * from reachables;
+
+prepare stmt from "
+with recursive
+reachables(p) as
+(
+ select lp from t1 where lp = 'p1'
+ union
+ select t1.rp from reachables, t1
+ where 'p3' not in (select p from reachables where p <= 'p5'
+ union
+ select p from reachables where p > 'p5') and
+ t1.lp = reachables.p
+)
+select * from reachables;
+";
+
+execute stmt;
+execute stmt;
+
+deallocate prepare stmt;
+
+drop table t1;
+
+create table objects(v char(4) not null);
+insert into objects values
+ ('v1'), ('v2'), ('v3'), ('v4'), ('v5'),
+ ('v6'), ('v7'), ('v8'), ('v9'), ('v10');
+
+create table modules(m char(4) not null);
+insert into modules values
+ ('m1'), ('m2'), ('m3'), ('m4');
+
+create table module_arguments(m char(4) not null, v char(4) not null);
+insert into module_arguments values
+ ('m1','v3'), ('m1','v9'),
+ ('m2','v4'), ('m2','v7'),
+ ('m3','v6'), ('m4','v2');
+
+create table module_results(m char(4) not null, v char(4) not null);
+insert into module_results values
+ ('m1','v4'),
+ ('m2','v1'), ('m2','v6'),
+ ('m3','v10'), ('m4','v7');
+
+set standard_compliant_cte=0;
+
+with recursive
+reached_objects as
+(
+ select v, 'init' as m from objects where v in ('v3','v7','v9')
+ union
+ select module_results.v, module_results.m from module_results, applied_modules
+ where module_results.m = applied_modules.m
+),
+applied_modules as
+(
+ select * from modules where 1=0
+ union
+ select modules.m
+ from
+ modules
+ where
+ not exists (select * from module_arguments
+ where module_arguments.m = modules.m and
+ module_arguments.v not in
+ (select v from reached_objects))
+)
+select * from reached_objects;
+
+with recursive
+reached_objects as
+(
+ select v, 'init' as m from objects where v in ('v3','v7','v9')
+ union
+ select module_results.v, module_results.m from module_results, applied_modules
+ where module_results.m = applied_modules.m
+),
+applied_modules as
+(
+ select * from modules where 1=0
+ union
+ select modules.m
+ from
+ modules
+ where
+ 'v6' not in (select v from reached_objects) and
+ not exists (select * from module_arguments
+ where module_arguments.m = modules.m and
+ module_arguments.v not in
+ (select v from reached_objects))
+)
+select * from reached_objects;
+
+prepare stmt from "
+with recursive
+reached_objects as
+(
+ select v, 'init' as m from objects where v in ('v3','v7','v9')
+ union
+ select module_results.v, module_results.m from module_results, applied_modules
+ where module_results.m = applied_modules.m
+),
+applied_modules as
+(
+ select * from modules where 1=0
+ union
+ select modules.m
+ from
+ modules
+ where
+ 'v6' not in (select v from reached_objects) and
+ not exists (select * from module_arguments
+ where module_arguments.m = modules.m and
+ module_arguments.v not in
+ (select v from reached_objects))
+)
+select * from reached_objects;
+";
+
+execute stmt;
+execute stmt;
+
+deallocate prepare stmt;
+
+drop table objects, modules, module_arguments, module_results;
+
+set standard_compliant_cte=default;
+select @@standard_compliant_cte;
+
+--echo #
+--echo # mdev-12554: impossible where in recursive select
+--echo #
+
+CREATE TABLE t1 (i int);
+INSERT INTO t1 VALUES (1),(2);
+
+WITH RECURSIVE
+cte(f) AS ( SELECT i FROM t1 UNION SELECT f FROM t1, cte WHERE 1=0 )
+SELECT * FROM cte;
+
+DROP TABLE t1;
+
+--echo #
+--echo # mdev-12556: recursive execution uses Aria temporary tables
+--echo #
+
+CREATE TABLE t (c1 varchar(255), c2 tinytext);
+INSERT INTO t VALUES ('a','a'),('b','b'),('c','c'),('d','d');
+
+let $q1=
+WITH RECURSIVE cte(f) AS (
+ SELECT c1 FROM t
+ UNION
+ SELECT c1 FROM t, cte
+) SELECT COUNT(*) FROM cte;
+
+let $q2=
+WITH RECURSIVE cte(f) AS (
+ SELECT c2 FROM t
+ UNION
+ SELECT c2 FROM t, cte
+) SELECT COUNT(*) FROM cte;
+
+eval ANALYZE $q1;
+eval $q1;
+
+eval ANALYZE $q2;
+eval $q2;
+
+DROP TABLE t;
+
+--echo #
+--echo # mdev-12563: no recursive references on the top level of the CTE spec
+--echo #
+
+CREATE TABLE t (i int);
+INSERT INTO t VALUES (3), (1),(2);
+
+SET standard_compliant_cte=0;
+
+WITH RECURSIVE cte(f) AS (
+ SELECT i FROM t
+ UNION
+ SELECT i FROM t WHERE i NOT IN ( SELECT * FROM cte )
+) SELECT * FROM cte;
+
+WITH RECURSIVE cte(f) AS (
+ SELECT i FROM t
+ UNION
+ SELECT i FROM t WHERE i NOT IN ( SELECT * FROM cte WHERE i < 2 )
+ UNION
+ SELECT i FROM t WHERE i NOT IN ( SELECT * FROM cte WHERE i > 2 )
+) SELECT * FROM cte;
+
+WITH RECURSIVE cte(f) AS (
+ SELECT i FROM t
+ UNION
+ SELECT i FROM t
+ WHERE i NOT IN ( SELECT * FROM cte WHERE i < 2
+ UNION
+ SELECT * FROM cte WHERE i > 2)
+) SELECT * FROM cte;
+
+WITH RECURSIVE cte(f) AS (
+ SELECT i FROM t
+ UNION
+ SELECT i FROM t
+ WHERE i NOT IN ( SELECT * FROM t
+ WHERE i IN ( SELECT * FROM cte ) GROUP BY i )
+) SELECT * FROM cte;
+
+WITH RECURSIVE cte(f) AS (
+ SELECT i FROM t
+ UNION
+ SELECT i FROM t WHERE i NOT IN ( SELECT * FROM cte )
+ UNION
+ SELECT * FROM cte WHERE f > 2
+) SELECT * FROM cte;
+
+set standard_compliant_cte=default;
+
+DROP TABLE t;
+
+--echo #
+--echo # mdev-14184: recursive CTE embedded into CTE with multiple references
+--echo #
+
+WITH
+cte1 AS (
+ SELECT n FROM (
+ WITH RECURSIVE rec_cte(n) AS (
+ SELECT 1 as n1
+ UNION ALL
+ SELECT n+1 as n2 FROM rec_cte WHERE n < 3
+ ) SELECT n FROM rec_cte
+ ) AS X
+),
+cte2 as (
+ SELECT 2 FROM cte1
+)
+SELECT *
+FROM cte1;
+
+--echo #
+--echo # mdev-14629: a user-defined variable is defined by the recursive CTE
+--echo #
+
+set @var=
+(
+ with recursive cte_tab(a) as (
+ select 1
+ union
+ select a+1 from cte_tab
+ where a<3)
+ select count(*) from cte_tab
+);
+
+select @var;
+
+create table t1(a int, b int);
+insert into t1 values (3,8),(1,5),(5,7),(7,4),(4,3);
+
+set @var=
+(
+ with recursive summ(a,s) as (
+ select 1, 0 union
+ select t1.b, t1.b+summ.s from summ, t1
+ where summ.a=t1.a)
+ select s from summ
+ order by a desc
+ limit 1
+);
+
+select @var;
+
+--ERROR ER_UNACCEPTABLE_MUTUAL_RECURSION
+set @var=
+(
+ with recursive
+ cte_1 as (
+ select 1
+ union
+ select * from cte_2),
+ cte_2 as (
+ select * from cte_1
+ union
+ select a from t1, cte_2
+ where t1.a=cte_2.a)
+ select * from cte_2
+ limit 1
+);
+
+drop table t1;
+
+--echo #
+--echo # mdev-14777: crash caused by the same as in mdev-14755
+--echo #
+
+--source include/have_sequence.inc
+
+CREATE TABLE t1 (i1 int NOT NULL, i2 int);
+CREATE TABLE t2 (d1 int NOT NULL PRIMARY KEY);
+CREATE TABLE t3 (i int );
+
+insert into t1 select seq,seq from seq_1_to_100000;
+insert into t2 select seq from seq_1000_to_100000;
+insert into t3 select seq from seq_1_to_1000;
+
+SELECT *
+FROM
+(
+ SELECT *
+ FROM
+ (
+ WITH RECURSIVE rt AS
+ (
+ SELECT i2 P, i1 C FROM t1 WHERE i1 IN (SELECT d1 FROM t2)
+ UNION
+ SELECT t1.i2 P, rt.C C FROM t1, rt
+ )
+ SELECT C,P
+ FROM ( SELECT P,C FROM rt WHERE NOT EXISTS (SELECT 1 FROM t1) ) Y
+ ) X
+ WHERE 1 = 1
+) K, t3;
+
+drop table t1,t2,t3;
+
+--echo #
+--echo # mdev-14879: subquery with recursive reference in WHERE of CTE
+--echo #
+
+create table flights
+(departure varchar(32),
+ arrival varchar(32),
+ carrier varchar(20),
+ flight_number char(7));
+
+insert into flights values
+('Seattle', 'Frankfurt', 'Lufthansa', 'LH 491'),
+('Seattle', 'Chicago', 'American', 'AA 2573'),
+('Seattle', 'Los Angeles', 'Alaska Air', 'AS 410'),
+('Chicago', 'New York', 'American', 'AA 375'),
+('Chicago', 'Montreal', 'Air Canada', 'AC 3053'),
+('Los Angeles', 'New York', 'Delta', 'DL 1197'),
+('Moscow', 'Tokyo', 'Aeroflot', 'SU 264'),
+('New York', 'Paris', 'Air France', 'AF 23'),
+('Frankfurt', 'Moscow', 'Lufthansa', 'LH 1444'),
+('Tokyo', 'Seattle', 'ANA', 'NH 178'),
+('Los Angeles', 'Tokyo', 'ANA', 'NH 175'),
+('Moscow', 'Los Angeles', 'Aeroflot', 'SU 106'),
+('Montreal', 'Paris', 'Air Canada', 'AC 870'),
+('Cairo', 'Paris', 'Air France', 'AF 503'),
+('New York', 'Seattle', 'American', 'AA 45'),
+('Paris', 'Chicago', 'Air France', 'AF 6734');
+
+with recursive destinations (city) as
+( select a.arrival from flights a where a.departure='Cairo'
+ union
+ select b.arrival from destinations r, flights b where r.city=b.departure)
+select * from destinations;
+
+set standard_compliant_cte=0;
+
+let $q=
+with recursive destinations (city, legs) as
+(
+ select a.arrival, 1 from flights a where a.departure='Cairo'
+ union
+ select b.arrival, r.legs + 1 from destinations r, flights b
+ where r.city=b.departure and b.arrival not in (select city from destinations)
+)
+select * from destinations;
+
+eval $q;
+eval explain extended $q;
+
+set standard_compliant_cte=default;
+
+drop table flights;
+
+--echo #
+--echo # MDEV-15162: Setting user variable in recursive CTE
+--echo #
+
+SET @c=1;
+
+WITH RECURSIVE cte AS
+ (SELECT 5
+ UNION
+ SELECT @c:=@c+1 FROM cte WHERE @c<3)
+SELECT * FROM cte;
+
+--echo #
+--echo # MDEV-15575: using recursive cte with big_tables enabled
+--echo #
+
+set tmp_memory_table_size=0; # force on-disk tmp table
+
+with recursive qn as
+(select 123 as a union all select 1+a from qn where a<130)
+select * from qn;
+
+set tmp_memory_table_size=default;
+
+--echo #
+--echo # MDEV-15571: using recursive cte with big_tables enabled
+--echo #
+
+create table t1 (a bigint);
+insert into t1 values(1);
+
+set tmp_memory_table_size=0; # force on-disk tmp table
+
+--error ER_DATA_OUT_OF_RANGE
+with recursive qn as
+(
+ select a from t1
+ union all
+ select a*2000 from qn where a<10000000000000000000
+)
+select * from qn;
+
+set tmp_memory_table_size=default;
+
+drop table t1;
+
+--echo #
+--echo # MDEV-15556: using recursive cte with big_tables enabled
+--echo # when recursive tables are accessed by key
+--echo #
+
+set tmp_memory_table_size=0; # force on-disk tmp table
+
+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();
+
+let $q=
+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;
+
+eval $q;
+eval EXPLAIN $q;
+
+DROP TABLE t1,t2;
+
+set tmp_memory_table_size=default;
+
+--echo #
+--echo # MDEV-15840: recursive tables are accessed by key
+--echo # (the same problem as for MDEV-15556)
+--echo #
+
+--source include/have_sequence.inc
+
+CREATE TABLE t1 (p1 text,k2 int, p2 text, k1 int);
+INSERT INTO t1 select seq, seq, seq, seq from seq_1_to_1000;
+
+DELIMITER |;
+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 |
+
+DELIMITER ;|
+call getNums();
+
+DROP PROCEDURE getNums;
+DROP TABLE t1;
+
+--echo #
+--echo # MDEV-15894: aggregate/winfow functions in non-recorsive part
+--echo #
+
+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;
+
+with recursive qn as
+ (select rank() over (order by b) as a from t1 union
+ select a from qn)
+select * from qn;
+
+drop table t1;
+
+--echo #
+--echo # MDEV-16086: tmp table for CTE is created as ARIA tables
+--echo #
+
+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;
+
+DROP TABLE t1;
+
+--echo #
+--echo # MDEV-16212: recursive CTE with global ORDER BY
+--echo #
+
+--error ER_NOT_SUPPORTED_YET
+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;
+
+--echo #
+--echo # MDEV-15581: mix of ALL and DISTINCT UNION in recursive CTE
+--echo #
+
+create table t1(a int);
+insert into t1 values(1),(2);
+insert into t1 values(1),(2);
+
+set @c=0, @d=0;
+--error ER_NOT_SUPPORTED_YET
+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;
+
+drop table t1;
+
+--echo #
+--echo # MDEV-16629: function with recursive CTE using a base table
+--echo #
+
+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;
+
+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();
+
+DROP FUNCTION func;
+DROP TABLE t1;
+
+--echo #
+--echo # MDEV-16661: function with recursive CTE using no base tables
+--echo # (fixed by the patch for MDEV-16629)
+--echo #
+
+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();
+
+DROP FUNCTION func;
+
+--echo #
+--echo # MDEV-17024: two materialized CTEs using the same recursive CTE
+--echo #
+
+create table t1 (id int);
+insert into t1 values (1), (2), (3);
+
+let $q=
+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;
+
+eval $q;
+eval explain extended $q;
+eval prepare stmt from "$q";
+execute stmt;
+execute stmt;
+
+create table t2 (c1 int, c2 int);
+eval create procedure p() insert into t2 $q;
+call p();
+select * from t2;
+
+let $q1=
+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;
+
+eval $q1;
+
+let $q2=
+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;
+
+eval $q2;
+
+let $q3=
+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;
+
+eval $q3;
+
+let $q4=
+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;
+
+eval $q4;
+eval explain extended $q4;
+eval prepare stmt from "$q4";
+execute stmt;
+execute stmt;
+
+drop procedure p;
+drop table t2;
+
+create table t2 (c1 int, c2 int);
+eval create procedure p() insert into t2 $q4;
+call p();
+select * from t2;
+
+drop procedure p;
+drop table t1,t2;
+
+--echo #
+--echo # MDEV-17201: recursive part with LIMIT
+--echo #
+
+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;
+
+DROP TABLE purchases, expired;
+
+--echo #
+--echo # MDEV-17635: Two recursive CTEs, the second using the first
+--echo #
+
+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;
+
+--echo # 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;
+
+--echo #
+--echo # MDEV-17871: EXPLAIN for query with not used recursive cte
+--echo #
+
+create table t1 (a int);
+insert into t1 values (2), (1), (4), (3);
+
+let $rec_cte =
+with recursive cte as
+ (select * from t1 where a=1 union select a+1 from cte where a<3);
+
+eval
+explain extended
+$rec_cte
+select * from cte as t;
+
+eval
+$rec_cte
+select * from cte as t;
+
+eval
+explain extended
+$rec_cte
+select * from t1 as t;
+
+eval
+$rec_cte
+select * from t1 as t;
+
+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;
+
+drop table t1,t2;
+
+
+--echo #
+--echo # MDEV-22042: ANALYZE of query using stored function and recursive CTE
+--echo #
+
+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);
+
+delimiter $$;
+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$$
+delimiter ;$$
+
+let q=
+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);
+
+eval $q;
+eval explain $q;
+--source include/analyze-format.inc
+eval analyze format=json $q;
+
+drop function f1;
+drop table t1,t2;
+
+--echo #
+--echo # MDEV-22748: two materialized CTEs using the same recursive CTE
+--echo # (see also test case for MDEV-17024)
+--echo #
+
+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');
+
+let $q=
+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);
+
+eval $q;
+eval EXPLAIN EXTENDED $q;
+eval PREPARE stmt FROM "$q";
+EXECUTE stmt;
+EXECUTE stmt;
+
+CREATE TABLE t4 (YEAR int(4), d1 date , d2 date);
+eval CREATE PROCEDURE p() INSERT INTO t4 $q;
+CALL p();
+SELECT * FROM t4;
+
+DROP PROCEDURE p;
+DROP TABLE t1,t2,t3,t4;
+
+--echo #
+--echo # MDEV-23619: recursive CTE used only in the second operand of UNION
+--echo #
+
+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);
+
+let $q=
+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;
+
+eval explain $q;
+eval $q;
+--source include/analyze-format.inc
+eval analyze format=json $q;
+eval prepare stmt from "$q";
+execute stmt;
+execute stmt;
+deallocate prepare stmt;
+
+--echo #checking hanging cte that uses a recursive cte
+let $q1=
+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;
+
+eval explain $q1;
+eval $q1;
+--source include/analyze-format.inc
+eval analyze format=json $q1;
+eval prepare stmt from "$q1";
+execute stmt;
+execute stmt;
+deallocate prepare stmt;
+
+drop table t1;
+
+--echo #
+--echo # MDEV-24019: query with recursive CTE when no default database is set
+--echo #
+
+create database dummy;
+use dummy;
+drop database dummy;
+
+let $q=
+with recursive a as
+ (select 1 from dual union select * from a as r)
+select * from a;
+
+eval $q;
+
+create database db1;
+create table db1.t1 (a int);
+insert into db1.t1 values (3), (7), (1);
+
+let $q=
+with recursive cte as
+ (select * from db1.t1 union select * from (select * from cte) as t)
+select * from cte;
+
+eval $q;
+eval explain $q;
+
+eval prepare stmt from "$q";
+execute stmt;
+execute stmt;
+deallocate prepare stmt;
+
+drop database db1;
+
+use test;
+
+--echo #
+--echo # MDEV-23406: query with mutually recursive CTEs when big_tables=1
+--echo #
+
+set @save_big_tables=@@big_tables;
+set big_tables=1;
+
+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);
+
+let q=
+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;
+
+eval $q;
+eval explain $q;
+eval prepare stmt from "$q";
+execute stmt;
+execute stmt;
+deallocate prepare stmt;
+
+let $q=
+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;
+
+eval $q;
+eval explain $q;
+eval prepare stmt from "$q";
+execute stmt;
+execute stmt;
+deallocate prepare stmt;
+
+drop table folks;
+
+set big_tables=@save_big_tables;
+
+--echo #
+--echo # MDEV-26135: execution of PS for query with hanging recursive CTE
+--echo #
+
+create table t1 (a int);
+insert into t1 values (5), (7);
+create table t2 (b int);
+insert into t2 values (3), (7), (1);
+
+let $q=
+with recursive r as (select a from t1 union select a+1 from r where a < 10)
+select * from t2;
+
+eval $q;
+eval prepare stmt from "$q";
+execute stmt;
+execute stmt;
+deallocate prepare stmt;
+
+drop table t1,t2;
+
+--echo #
+--echo # MDEV-26189: Unknown column reference within hanging recursive CTE
+--echo #
+
+create table t1 (a int);
+insert into t1 values (3), (7), (1);
+
+let $q1=
+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 ER_BAD_FIELD_ERROR
+eval $q1;
+--ERROR ER_BAD_FIELD_ERROR
+eval explain $q1;
+
+eval create procedure sp1() $q1;
+--ERROR ER_BAD_FIELD_ERROR
+call sp1();
+--ERROR ER_BAD_FIELD_ERROR
+call sp1();
+
+let $q2=
+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 ER_BAD_FIELD_ERROR
+eval $q2;
+--ERROR ER_BAD_FIELD_ERROR
+eval explain $q2;
+
+eval create procedure sp2() $q2;
+--ERROR ER_BAD_FIELD_ERROR
+call sp2();
+--ERROR ER_BAD_FIELD_ERROR
+call sp2();
+
+drop procedure sp1;
+drop procedure sp2;
+
+drop table t1;
+
+--echo #
+--echo # MDEV-26202: Recursive CTE used indirectly twice
+--echo # (fixed by the patch forMDEV-26025)
+--echo #
+
+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;
+
+--echo #
+--echo # End of 10.2 tests
+--echo #
+
+--echo #
+--echo # MDEV-14217 [db crash] Recursive CTE when SELECT includes new field
+--echo #
+
+CREATE TEMPORARY TABLE a_tbl (
+ a VARCHAR(33) PRIMARY KEY,
+ b VARCHAR(33)
+);
+
+INSERT INTO a_tbl VALUES ('block0', 'block0'), ('block1', NULL);
+
+--error ER_WRONG_NUMBER_OF_COLUMNS_IN_SELECT
+WITH RECURSIVE Q0 AS (
+ SELECT T0.a, T0.b, 5
+ FROM a_tbl T0
+ WHERE b IS NULL
+ UNION ALL
+ SELECT T1.a, T1.b
+ FROM Q0
+ JOIN a_tbl T1
+ ON T1.a=Q0.a
+) SELECT distinct(Q0.a), Q0.b
+ FROM Q0;
+DROP TABLE a_tbl;
+
+--error ER_WRONG_NUMBER_OF_COLUMNS_IN_SELECT
+WITH RECURSIVE x AS (SELECT 1,2 UNION ALL SELECT 1 FROM x) SELECT * FROM x;
+
+--echo #
+--echo # MDEV-15162: Setting user variable in recursive CTE
+--echo #
+
+SET @c=1;
+
+WITH RECURSIVE cte AS
+ (SELECT 5
+ UNION
+ SELECT @c:=@c+1 FROM cte WHERE @c<3)
+SELECT * FROM cte;
+
+--echo #
+--echo # MDEV-14883: recursive references in operands of INTERSECT / EXCEPT
+--echo #
+
+create table flights
+(departure varchar(32),
+ arrival varchar(32),
+ carrier varchar(20),
+ flight_number char(7));
+
+insert into flights values
+('Seattle', 'Frankfurt', 'Lufthansa', 'LH 491'),
+('Seattle', 'Amsterdam', 'KLM', 'KL 6032'),
+('Seattle', 'Chicago', 'American', 'AA 2573'),
+('Seattle', 'Los Angeles', 'Alaska Air', 'AS 410'),
+('Chicago', 'New York', 'American', 'AA 375'),
+('Chicago', 'Montreal', 'Air Canada', 'AC 3053'),
+('Los Angeles', 'New York', 'Delta', 'DL 1197'),
+('New York', 'London', 'British Airways', 'BA 1511'),
+('London', 'Moscow', 'British Airways', 'BA 233'),
+('Moscow', 'Tokyo', 'Aeroflot', 'SU 264'),
+('Moscow', 'Dubai', 'Emirates', 'EK 2421'),
+('Dubai', 'Tokyo', 'Emirates', 'EK 318'),
+('Dubai', 'Bangkok', 'Emirates', 'EK 2142'),
+('Beijing', 'Bangkok', 'Air China', 'CA 757'),
+('Beijing', 'Tokyo', 'Air China', 'CA 6653'),
+('Moscow', 'Bangkok', 'Aeroflot', 'SU 270'),
+('New York', 'Reykjavik', 'Icelandair', 'FL 416'),
+('New York', 'Paris', 'Air France', 'AF 23'),
+('Amsterdam', 'Moscow', 'KLM', 'KL 903'),
+('Frankfurt', 'Dubai', 'Lufthansa', 'LH 630'),
+('Frankfurt', 'Moscow', 'Lufthansa', 'LH 1444'),
+('Reykjavik', 'London', 'British Airways', 'BA 2229'),
+('Frankfurt', 'Beijing', 'Air China', 'CA 966'),
+('Tokyo', 'Seattle', 'ANA', 'NH 178'),
+('Los Angeles', 'Tokyo', 'ANA', 'NH 175'),
+('Moscow', 'Los Angeles', 'Aeroflot', 'SU 106'),
+('Montreal', 'Paris', 'Air Canada', 'AC 870'),
+('London', 'Delhi', 'British Airways', 'BA 143'),
+('Delhi', 'Bangkok', 'Air India', 'AI 306'),
+('Delhi', 'Dubai', 'Air India', 'AI 995'),
+('Dubai', 'Cairo', 'Emirates', 'EK 927'),
+('Cairo', 'Paris', 'Air France', 'AF 503'),
+('Amsterdam', 'New York', 'Delta', 'DL 47'),
+('New York', 'Seattle', 'American', 'AA 45'),
+('Paris', 'Chicago', 'Air France', 'AF 6734');
+
+create table distances
+(city1 varchar(32),
+ city2 varchar(32),
+ dist int);
+
+insert into distances values
+('Seattle', 'Frankfurt', 5080),
+('Seattle', 'Amsterdam', 4859),
+('Seattle', 'Chicago', 1733),
+('Seattle', 'Los Angeles', 960),
+('Chicago', 'New York', 712),
+('Chicago', 'Montreal', 746),
+('Los Angeles', 'New York', 2446),
+('New York', 'London', 3459),
+('London', 'Moscow', 1554),
+('Moscow', 'Tokyo', 4647),
+('Moscow', 'Dubai', 2298),
+('Dubai', 'Tokyo', 4929),
+('Dubai', 'Bangkok', 3050),
+('Beijing', 'Bangkok', 2046),
+('Beijing', 'Tokyo', 1301),
+('Moscow', 'Bangkok', 4390),
+('New York', 'Reykjavik', 2613),
+('New York', 'Paris', 3625),
+('Amsterdam', 'Moscow', 1334),
+('Frankfurt', 'Dubai', 3003),
+('Frankfurt', 'Moscow', 1256),
+('Reykjavik', 'London', 1173),
+('Frankfurt', 'Beijing', 4836),
+('Tokyo', 'Seattle', 4783),
+('Los Angeles', 'Tokyo', 5479),
+('Moscow', 'Los Angeles', 6071),
+('Moscow', 'Reykjavik', 2052),
+('Montreal', 'Paris', 3425),
+('London', 'Delhi', 4159),
+('London', 'Paris', 214),
+('Delhi', 'Bangkok', 1810),
+('Delhi', 'Dubai', 1369),
+('Delhi', 'Beijing', 2350),
+('Dubai', 'Cairo', 1501),
+('Cairo', 'Paris', 1992),
+('Amsterdam', 'New York', 3643),
+('New York', 'Seattle', 2402),
+('Paris', 'Chicago', 4136),
+('Paris', 'Los Angeles', 5647);
+
+with recursive destinations (city) as
+(
+ select a.arrival from flights a where a.departure = 'Seattle'
+ union
+ select b.arrival from destinations r, flights b where r.city = b.departure
+)
+select * from destinations;
+
+with recursive destinations (city) as
+(
+ select a.arrival from flights a, distances d
+ where a.departure = 'Seattle' and
+ a.departure = d.city1 and a.arrival = d.city2 and
+ d.dist < 4000
+ union
+ select b.arrival from destinations r, flights b, distances d
+ where r.city = b.departure and
+ b.departure = d.city1 and b.arrival = d.city2 and
+ d.dist < 4000
+)
+select * from destinations;
+
+set standard_compliant_cte=0;
+
+with recursive legs_to_destinations
+ (departure, arrival, dist, leg_no, acc_mileage) as
+(
+ select a.departure, a.arrival, d.dist, 1, d.dist
+ from flights a, distances d
+ where a.departure = 'Seattle' and
+ a.departure = d.city1 and a.arrival = d.city2 and
+ d.dist < 4000
+ union all
+ select b.departure, b.arrival, d.dist, r.leg_no + 1, r.acc_mileage + d.dist
+ from legs_to_destinations r, flights b, distances d
+ where r.arrival = b.departure and
+ b.departure = d.city1 and b.arrival = d.city2 and
+ d.dist < 4000 and
+ b.arrival not in (select arrival from legs_to_destinations)
+)
+select * from legs_to_destinations;
+
+set standard_compliant_cte=default;
+
+with recursive destinations (city) as
+(
+ select a.arrival from flights a, distances d
+ where a.departure = 'Seattle' and
+ a.departure = d.city1 and a.arrival = d.city2 and
+ d.dist < 4000
+ union
+ select b.arrival from destinations r, flights b
+ where r.city = b.departure
+ intersect
+ select city2 from destinations s, distances d
+ where s.city = d.city1 and d.dist < 4000
+)
+select * from destinations;
+
+with recursive destinations (city) as
+(
+ select a.arrival from flights a where a.departure = 'Seattle'
+ union
+ select * from
+ (
+ select b.arrival from destinations r, flights b
+ where r.city = b.departure
+ except
+ select arrival from flights
+ where arrival in
+ ('New York', 'London', 'Moscow', 'Dubai', 'Cairo', 'Tokyo')
+ ) t
+)
+select * from destinations;
+
+drop table flights, distances;
+
+--echo #
+--echo # MDEV-15159: Forced nullability of columns in recursive CTE
+--echo #
+
+WITH RECURSIVE cte AS (
+ SELECT 1 AS a UNION ALL
+ SELECT NULL FROM cte WHERE a IS NOT NULL)
+SELECT * FROM cte;
+
+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;
+
+DROP TABLE t1;
+
+--echo #
+--echo # MDEV-12325 Unexpected data type and truncation when using CTE
+--echo #
+
+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');
+
+let $query=
+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 ER_WARN_DATA_OUT_OF_RANGE
+--eval $query
+--error ER_WARN_DATA_OUT_OF_RANGE
+--eval create table t2 as $query;
+--eval create table t2 ignore as $query;
+show create table t2;
+--error ER_WARN_DATA_OUT_OF_RANGE
+--eval insert into t2 $query;
+--eval insert ignore into t2 $query;
+drop table t2;
+set @@sql_mode="";
+--eval $query
+--eval create table t2 as $query;
+show create table t2;
+set @@sql_mode=default;
+drop table t1,t2;
+
+--echo #
+--echo # MDEV-29361: Embedded recursive / non-recursive CTE within
+--echo # the scope of another embedded CTE with the same name
+--echo #
+
+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;
+
+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;
+
+# All recursive CTEs with name x are embedded in in the definition of 'cte',
+# without this embedding CTE the bug could not be reproduced
+
+# two recursive CTEs with name x, the second CTE is in the scope
+# of the first one, but does not use it
+# before fix of this bug: wrong result set
+
+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;
+
+
+# two recursive CTEs with name x, the second CTE is in the scope of the first
+# one, but does not use it; there are two non-recursive references to the latter
+# before fix of this bug: wrong result set
+
+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;
+
+
+# x as r1 belongs to the definition of CTE x from non-RECURSIVE with clause
+# before fix of this bug: infinite sequence of recursive calls
+
+--error ER_NO_SUCH_TABLE
+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;
+
+
+# x as r2 belongs to the definition of CTE x from non-RECURSIVE with clause
+# yet it is in the scope of another CTE with the same name
+# before fix of this bug: crash in With_element::get_name()
+
+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;
+
+
+# x as r1 is in the definition of CTE x from non-RECURSIVE with clause, thus
+# although x as r2 is in the scope of the first CTE x an error is expected
+# before fix of this bug: crash in With_element::get_name()
+
+--error ER_NO_SUCH_TABLE
+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;
+
+
+# x as r2 belongs to the definition of CTE x from non-RECURSIVE with clause
+# and in the scope of recursive CTE y, but does not use the latter
+# before fix of this bug: crash in With_element::get_name()
+
+--error ER_NO_SUCH_TABLE
+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;
+
+
+# x as r2 belongs to the definition of CTE x from non-RECURSIVE with clause
+# and in the scope of non-recursive CTE y, but does not use the latter
+# before fix of this bug: crash in With_element::get_name()
+
+--error ER_NO_SUCH_TABLE
+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;
+
+
+# in the subquery of the embedding CTE cte:
+# x as r1 is in the definition of CTE x from non-RECURSIVE with clause;
+# x as t2 is in the definition of CTE x from RECURSIVE with clause;
+# an error is expected to be reported for x as r1
+# before fix of this bug: infinite sequence of recursive calls
+
+--error ER_NO_SUCH_TABLE
+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;
+
+
+# in the subquery of the embedding CTE cte:
+# x as r1 is in the definition of CTE x from non-RECURSIVE with clause, thus
+# although x as r2 is in the scope of the first CTE x an error is expected
+# before fix of this bug: crash in With_element::get_name()
+
+--error ER_NO_SUCH_TABLE
+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;
+
+
+# in the subquery of the embedding CTE cte:
+# two recursive CTEs with name x, the second CTE is in the scope
+# of the first one, but does not use it
+# before fix of this bug: wrong result set
+
+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;
+
+
+# in the subquery of the embedding CTE cte:
+# two recursive CTEs with name x, the second CTE is in the scope
+# of the first one, but does not use it
+# before fix of this bug: Subquery returns more than 1 row
+
+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;
+
+
+create table x (a int);
+insert into x values (3), (7), (1), (5), (6);
+
+
+# in the subquery of the embedding CTE cte:
+# one non-recursive CTEs with name x using table t in a subquery, the second
+# CTE x is recursive and is in the scope of the first one, but does not use it;
+# the query uses both CTE with name x.
+# before fix of this bug: infinite sequence of recursive calls
+
+--error ER_SUBQUERY_NO_1_ROW
+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;
+
+
+# in the subquery of the embedding CTE cte:
+# one non-recursive CTEs with name x using table t, the second CTE x is
+# recursive and is in the scope of the first one, but does not use it;
+# the query uses only the second CTE with name x.
+# before fix of this bug: 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;
+
+
+# in the subquery of the embedding CTE cte embedded in the CTE cte_e
+# one non-recursive CTEs with name x uses table t1, the second CTE x is
+# recursive and is in the scope of the first one, but does not use it;
+# CTE cte uses only the second CTE with name x;
+# the query has two refeences to cte_e
+# before fix of this bug: infinite sequence of recursive calls
+
+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;
+
+
+# check : with base table x all queries abobe that returned error
+# message ER_NO_SUCH_TABLE now return proper result sets
+
+with
+x as
+(
+ select a from t1 union select a+1 from x as r1 where a < 7
+)
+select * from x as s1;
+
+with
+x as
+(
+ select a from t2
+ union
+ select a+2 from x as r2 where a < 10
+)
+select a from x as s2;
+
+with recursive
+x as
+(
+ select a from t1 union select a+1 from x as r1 where a < 7
+)
+select * from x as s1;
+
+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;
+
+
+# x as r1 is bound to table x, x as s1 is bound to the first CTE x
+# x as r2 and x as s2 are bound to the second CTE x
+# before fix of this bug: infinite sequence of recursive calls
+
+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;
+
+
+# x as r1 is bound to table x, x as s1 is bound to the first CTE x
+# x as r1 is bound to the first CTE x, x as s2 is bound to the second CTE x
+# before fix of this bug: crash in With_element::get_name()
+
+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;
+
+
+# x as r2 is bound to table x, x as s2 is bound to CTE x
+# before fix of this bug: crash in With_element::get_name()
+
+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;
+
+
+# x as r2 is bound to table x, x as s2 is bound to CTE x
+# before fix of this bug: crash in With_element::get_name()
+
+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;
+
+
+# x as r1 is bound to table x, x as s1 is bound to the first CTE x
+# x as r2 and x as s2 are bound to the second CTE x
+# before fix of this bug: infinite sequence of recursive calls
+
+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;
+
+
+# x as r1 is bound to table x, x as s1 is bound to the first CTE x
+# x as r2 is bound to the first CTE x, x as s2 is bound to the second CTE x
+# before fix of this bug: crash in With_element::get_name()
+
+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;
+
+
+drop table t1,t2,t3,x;
+
+--echo #
+--echo # MDEV-30248: Embedded non-recursive CTE referring to base table 'x'
+--echo # within a CTE with name 'x' used in a subquery from
+--echo # select list of another CTE
+--echo #
+
+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);
+
+let $q1=
+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;
+eval $q1;
+
+let $q2=
+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;
+eval $q2;
+
+let $q3=
+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;
+eval $q3;
+
+
+let $q4=
+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;
+eval $q4;
+
+let $q5=
+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;
+eval $q5;
+
+let $q6=
+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;
+eval $q6;
+
+let $q7=
+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;
+eval $q7;
+
+
+DROP TABLE x;
+
+--ERROR ER_NO_SUCH_TABLE
+eval $q1;
+--ERROR ER_NO_SUCH_TABLE
+eval $q2;
+--ERROR ER_NO_SUCH_TABLE
+eval $q3;
+--ERROR ER_NO_SUCH_TABLE
+eval $q4;
+--ERROR ER_NO_SUCH_TABLE
+eval $q5;
+--ERROR ER_NO_SUCH_TABLE
+eval $q6;
+--ERROR ER_NO_SUCH_TABLE
+eval $q7;
+
+DROP TABLE t1;
+
+--echo #
+--echo # End of 10.3 tests
+--echo #
+
+--echo #
+--echo # MDEV-26108: Recursive CTE embedded into another CTE which is used twice
+--echo #
+
+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;
+
+drop table t1;
+
+--echo #
+--echo # MDEV-20010 Equal on two RANK window functions create wrong result
+--echo #
+
+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;
+
+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;
+
+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;
+
+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;
+
+create view v1 as select b,5 as c from t1;
+select b, rank() over (order by c) from v1 order by b;
+
+drop view v1;
+drop table t1;
+
+--echo # End of 10.4 tests