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