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
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
|
#
# Bug#23495283 WL3634:ASSERTION `0' FAILED IN FIELD* ITEM::TMP_TABLE_FIELD_FROM_FIELD_TYPE
#
CREATE TABLE t1(c1 DATETIME, c2 INT, KEY(c1));
WITH RECURSIVE cte AS ( SELECT a.c1 AS field1, 0 AS cycle FROM (t1 AS a)
UNION ALL SELECT b.c2 FROM cte AS a JOIN t1 AS b) SELECT * FROM cte;
ERROR 21000: The used SELECT statements have a different number of columns
DROP TABLE t1;
#
# Bug#23645090 WL3634: INVALID WRITE AND READ VALGRIND ERRORS
#
CREATE TABLE A (
col_date date DEFAULT NULL,
col_datetime_key datetime DEFAULT NULL,
col_time_key time DEFAULT NULL,
col_varchar_key varchar(1) DEFAULT NULL,
col_int_key int(11) DEFAULT NULL,
col_blob_key blob,
col_varchar varchar(1) DEFAULT NULL,
col_date_key date DEFAULT NULL,
col_time time DEFAULT NULL,
col_blob blob,
pk int(11) NOT NULL AUTO_INCREMENT,
col_int int(11) DEFAULT NULL,
col_datetime datetime DEFAULT NULL,
PRIMARY KEY (pk),
KEY col_datetime_key (col_datetime_key),
KEY col_time_key (col_time_key),
KEY col_varchar_key (col_varchar_key),
KEY col_int_key (col_int_key),
KEY col_blob_key (col_blob_key(255)),
KEY col_date_key (col_date_key)
) DEFAULT CHARSET=latin1;
Warnings:
Warning 1681 Integer display width is deprecated and will be removed in a future release.
Warning 1681 Integer display width is deprecated and will be removed in a future release.
Warning 1681 Integer display width is deprecated and will be removed in a future release.
CREATE TABLE AA (
col_varchar varchar(1) DEFAULT NULL,
col_date date DEFAULT NULL,
col_varchar_key varchar(1) DEFAULT NULL,
col_date_key date DEFAULT NULL,
col_datetime_key datetime DEFAULT NULL,
col_time_key time DEFAULT NULL,
pk int(11) NOT NULL AUTO_INCREMENT,
col_time time DEFAULT NULL,
col_int_key int(11) DEFAULT NULL,
col_datetime datetime DEFAULT NULL,
col_int int(11) DEFAULT NULL,
col_blob blob,
col_blob_key blob,
PRIMARY KEY (pk),
KEY col_varchar_key (col_varchar_key),
KEY col_date_key (col_date_key),
KEY col_datetime_key (col_datetime_key),
KEY col_time_key (col_time_key),
KEY col_int_key (col_int_key),
KEY col_blob_key (col_blob_key(255))
) DEFAULT CHARSET=latin1;
Warnings:
Warning 1681 Integer display width is deprecated and will be removed in a future release.
Warning 1681 Integer display width is deprecated and will be removed in a future release.
Warning 1681 Integer display width is deprecated and will be removed in a future release.
CREATE TABLE BB (
col_date date DEFAULT NULL,
col_blob_key blob,
col_time time DEFAULT NULL,
col_varchar_key varchar(1) DEFAULT NULL,
col_varchar varchar(1) DEFAULT NULL,
col_blob blob,
pk int(11) NOT NULL AUTO_INCREMENT,
col_int_key int(11) DEFAULT NULL,
col_datetime datetime DEFAULT NULL,
col_time_key time DEFAULT NULL,
col_datetime_key datetime DEFAULT NULL,
col_date_key date DEFAULT NULL,
col_int int(11) DEFAULT NULL,
PRIMARY KEY (pk),
KEY col_blob_key (col_blob_key(255)),
KEY col_varchar_key (col_varchar_key),
KEY col_int_key (col_int_key),
KEY col_time_key (col_time_key),
KEY col_datetime_key (col_datetime_key),
KEY col_date_key (col_date_key)
) AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;
Warnings:
Warning 1681 Integer display width is deprecated and will be removed in a future release.
Warning 1681 Integer display width is deprecated and will be removed in a future release.
Warning 1681 Integer display width is deprecated and will be removed in a future release.
CREATE TABLE D (
col_varchar_key varchar(1) DEFAULT NULL,
col_datetime datetime DEFAULT NULL,
col_date_key date DEFAULT NULL,
col_int int(11) DEFAULT NULL,
col_time time DEFAULT NULL,
col_blob blob,
col_int_key int(11) DEFAULT NULL,
col_blob_key blob,
col_varchar varchar(1) DEFAULT NULL,
col_datetime_key datetime DEFAULT NULL,
col_date date DEFAULT NULL,
col_time_key time DEFAULT NULL,
pk int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (pk),
KEY col_varchar_key (col_varchar_key),
KEY col_date_key (col_date_key),
KEY col_int_key (col_int_key),
KEY col_blob_key (col_blob_key(255)),
KEY col_datetime_key (col_datetime_key),
KEY col_time_key (col_time_key)
) DEFAULT CHARSET=latin1;
Warnings:
Warning 1681 Integer display width is deprecated and will be removed in a future release.
Warning 1681 Integer display width is deprecated and will be removed in a future release.
Warning 1681 Integer display width is deprecated and will be removed in a future release.
WITH RECURSIVE cte AS (
SELECT alias1 . `col_blob_key` AS field1, 0 AS cycle
FROM ( BB AS alias1 , ( D AS alias2 , AA AS alias3 ) )
WHERE (
alias1 . pk = 225
OR ( alias1 . col_int_key = 69 AND alias1 . col_blob_key = 'p' )
)
UNION ALL
SELECT t1.pk, t2.cycle
FROM cte AS t2 JOIN A AS t1
WHERE t2.field1 = t1.`col_int_key`
AND t2.cycle =1 ) SELECT * FROM cte;
field1 cycle
DROP TABLE IF EXISTS A, AA, BB, D;
#
# Bug#24962600 WL3634: SIG 11 IN HEAP_RRND AT STORAGE/HEAP/HP_RRND.C
#
create table t1(a int);
with recursive cte as (select * from t1 union select * from cte)
select * from cte;
a
insert into t1 values(1),(2);
with recursive cte as (select * from t1 where 0 union select * from cte)
select * from cte;
a
with recursive cte as (select * from t1 where a>3 union select * from cte)
select * from cte;
a
drop table t1;
#
# Bug#26501463 WL10792: ASSERTION `!TABLE->HAS_NULL_ROW()' FAILED
#
CREATE TABLE D (col_int INT);
CREATE TABLE C (
col_int2 INT,
pk INT NOT NULL,
col_int INT,
PRIMARY KEY (pk)
);
INSERT INTO C VALUES
(7,1,3),(7,2,3),(5,3,4),(1,4,6),(5,5,2),
(5,6,9),(4,7,9),(7,8,3),(3,9,0),(5,10,3);
CREATE TABLE BB (
pk INT NOT NULL,
col_int INT,
PRIMARY KEY (pk)
);
INSERT INTO BB VALUES (1,0),(2,6),(3,2),(4,5),(5,0);
WITH RECURSIVE cte AS (
SELECT alias2 . col_int2 AS field1 FROM
D AS alias1 RIGHT JOIN
( ( C AS alias2 LEFT JOIN BB AS alias3
ON (( alias3 . pk = alias2 . col_int ) AND ( alias3 . pk = alias2 . pk ) ) ) )
ON (alias3 . col_int <> alias2 . col_int2 )
HAVING field1 <= 0
UNION
SELECT cte.field1 FROM cte
)
SELECT * FROM cte;
field1
DROP TABLE BB,C,D;
#
# Bug#26556025 ASSERTION `!SELECT_LEX->IS_RECURSIVE() || !TMP_TABLES' FAILED.
#
SET SQL_BUFFER_RESULT = 1;
WITH RECURSIVE cte AS
(SELECT 1 AS n UNION SELECT n+1 FROM cte WHERE n<3)
SELECT * FROM cte;
n
1
2
3
WITH RECURSIVE cte AS
(SELECT 1 AS n UNION ALL SELECT n+1 FROM cte WHERE n<3)
SELECT * FROM cte;
n
1
2
3
SET SQL_BUFFER_RESULT = DEFAULT;
#
# Bug #30509580: SERVER CRASHED DURING EXPLAIN ANALYZE WITH RECURSIVE ...
#
EXPLAIN ANALYZE WITH RECURSIVE cte (n) AS
(
SELECT 1
UNION ALL
SELECT n + 1 FROM cte WHERE n < 5
)
SELECT * FROM cte;
EXPLAIN
-> Table scan on cte (...) (actual rows=5 loops=1)
-> Materialize recursive CTE cte (...) (actual rows=5 loops=1)
-> Rows fetched before execution (...) (actual rows=1 loops=1)
-> Repeat until convergence
-> Filter: (cte.n < 5) (...) (actual rows=2 loops=2)
-> Scan new records on cte (...) (actual rows=2.5 loops=2)
#
# Bug#31066001 INCORRECT RESULT FROM CTE SUBQUERY WITH OUTER REFERENCE
#
SELECT *
FROM
(VALUES ROW(1),ROW(1)) AS dt(a)
WHERE
EXISTS(
WITH RECURSIVE qn AS (SELECT a*0 AS b UNION ALL SELECT b+1 FROM qn WHERE b=0)
SELECT * FROM qn WHERE b=a
);
a
1
1
SELECT *
FROM
(VALUES ROW(1),ROW(1)) AS dt(a)
WHERE
NOT EXISTS(
WITH RECURSIVE qn AS (SELECT a*0 AS b UNION ALL SELECT b+1 FROM qn WHERE b=0)
SELECT * FROM qn WHERE b=a
);
a
#
# Bug #31603195: REGRESSION: CTE CRASHING IN FINDSINGLEITERATOROFTYPE
#
CREATE TABLE t1 (a INTEGER);
WITH RECURSIVE cte1 AS
(
SELECT 1 FROM t1
UNION ALL
SELECT 2 FROM cte1 WHERE FALSE
)
SELECT * FROM cte1;
1
DROP TABLE t1;
|
