1
2--echo #
3--echo # check errors
4--echo #
5
6--error ER_DUP_FIELDNAME
7WITH RECURSIVE cte AS (
8        SELECT 1 AS a UNION ALL
9        SELECT NULL FROM cte WHERE a IS NOT NULL)
10        CYCLE a, a RESTRICT
11SELECT * FROM cte;
12
13--error ER_BAD_FIELD_ERROR
14WITH RECURSIVE cte AS (
15        SELECT 1 AS a UNION ALL
16        SELECT NULL FROM cte WHERE a IS NOT NULL)
17        CYCLE b RESTRICT
18SELECT * FROM cte;
19
20--error ER_PARSE_ERROR
21WITH cte AS (
22        SELECT 1 AS a UNION ALL
23        SELECT NULL FROM cte WHERE a IS NOT NULL)
24        CYCLE b RESTRICT
25SELECT * FROM cte;
26
27
28--echo #
29--echo # A degenerate case
30--echo #
31
32WITH RECURSIVE cte AS (
33	SELECT 1 AS a, 2 as b)
34   CYCLE b RESTRICT
35SELECT * FROM cte;
36
37
38--echo #
39--echo # A simple case
40--echo #
41
42WITH RECURSIVE cte AS (
43	SELECT 1 AS a, 2 as b UNION ALL
44	SELECT 2, 2 FROM cte WHERE a IS NOT NULL)
45        CYCLE b RESTRICT
46SELECT * FROM cte;
47
48
49--echo #
50--echo # MDEV-20632 case (with fixed syntax)
51--echo #
52
53create table t1 (from_ int, to_ int);
54insert into t1 values (1,2), (1,100), (2,3), (3,4), (4,1);
55
56WITH RECURSIVE cte (depth, from_, to_) as (
57   SELECT 0,1,1
58   UNION
59   SELECT depth+1, t1.from_, t1.to_
60      FROM t1, cte WHERE t1.from_ = cte.to_
61) CYCLE from_, to_ RESTRICT
62select * from cte;
63
64create view v1 as WITH RECURSIVE cte (depth, from_, to_) as (
65   SELECT 0,1,1
66   UNION
67   SELECT depth+1, t1.from_, t1.to_
68      FROM t1, cte WHERE t1.from_ = cte.to_
69) CYCLE from_, to_ RESTRICT
70select * from cte;
71
72show create view v1;
73
74select * from v1;
75
76delete from t1;
77
78insert into t1 values (1,2), (1,NULL), (NULL,NULL), (NULL, 1);
79
80select * from v1;
81
82drop view v1;
83
84drop table t1;
85
86
87--echo #
88--echo # A simple blob case
89--echo #
90
91create table t1 (a int, b text);
92insert into t1 values (1, "a");
93
94WITH RECURSIVE cte AS (
95	SELECT a, b  from t1 UNION ALL
96	SELECT a, b FROM cte WHERE a IS NOT NULL)
97        CYCLE b RESTRICT
98SELECT * FROM cte;
99
100drop table t1;
101
102--echo #
103--echo # check bit types
104--echo #
105
106create table t1 (from_ bit(3), to_ bit(3));
107insert into t1 values (1,2), (1,7), (2,3), (3,4), (4,1);
108
109WITH RECURSIVE cte (depth, from_, to_) as (
110   SELECT 0,1,1
111   UNION
112   SELECT depth+1, t1.from_, t1.to_
113      FROM t1, cte WHERE t1.from_ = cte.to_
114) CYCLE from_, to_ RESTRICT
115select * from cte;
116drop table t1;
117
118--echo #
119--echo # check bit types with BLOBs (TEXT)
120--echo #
121
122create table t1 (from_ bit(3), to_ bit(3), load_ text);
123insert into t1 values (1,2,"A"), (1,7,"A"), (2,3,"A"), (3,4,"A"), (4,1,"A");
124
125WITH RECURSIVE cte (depth, from_, to_, load_) as (
126   SELECT 0,1,1,"A"
127   UNION
128   SELECT depth+1, t1.from_, t1.to_, t1.load_
129      FROM t1, cte WHERE t1.from_ = cte.to_
130) CYCLE from_, to_, load_ RESTRICT
131select * from cte;
132insert into t1 values (4,1,"B");
133WITH RECURSIVE cte (depth, from_, to_, load_) as (
134   SELECT 0,1,1,"A"
135   UNION
136   SELECT depth+1, t1.from_, t1.to_, t1.load_
137      FROM t1, cte WHERE t1.from_ = cte.to_
138) CYCLE from_, to_, load_ RESTRICT
139select * from cte;
140
141drop table t1;
142
143
144