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
250
251
252
253
254
255
256
257
258
259
260
261
262
|
set @save_storage_engine= @@storage_engine;
set storage_engine=InnoDB;
#
# Bug#36981 - "innodb crash when selecting for update"
#
CREATE TABLE t1 (
c1 CHAR(1),
c2 CHAR(10),
KEY (c1)
);
INSERT INTO t1 VALUES ('3', null);
SELECT * FROM t1 WHERE c1='3' FOR UPDATE;
c1 c2
3 NULL
DROP TABLE t1;
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
CREATE TABLE t2 (a INT);
INSERT INTO t2 SELECT A.a + 10*(B.a + 10*C.a) FROM t1 A, t1 B, t1 C;
CREATE TABLE t3 (
c1 CHAR(10) NOT NULL,
c2 CHAR(10) NOT NULL,
c3 CHAR(200) NOT NULL,
KEY (c1)
);
INSERT INTO t3
SELECT CONCAT('c-',1000+t2.a,'=w'), CONCAT('c-',1000+ t2.a,'=w'), 'filler'
FROM t2;
INSERT INTO t3
SELECT CONCAT('c-',1000+t2.a,'=w'), CONCAT('c-',2000+t2.a,'=w'), 'filler-1'
FROM t2;
INSERT INTO t3
SELECT CONCAT('c-',1000+t2.a,'=w'), CONCAT('c-',3000+t2.a,'=w'), 'filler-2'
FROM t2;
SELECT c1,c3 FROM t3 WHERE c1 >= 'c-1994=w' and c1 != 'c-1996=w' FOR UPDATE;
c1 c3
c-1994=w filler
c-1994=w filler-1
c-1994=w filler-2
c-1995=w filler
c-1995=w filler-1
c-1995=w filler-2
c-1997=w filler
c-1997=w filler-1
c-1997=w filler-2
c-1998=w filler
c-1998=w filler-1
c-1998=w filler-2
c-1999=w filler
c-1999=w filler-1
c-1999=w filler-2
DROP TABLE t1,t2,t3;
#
# Bug#42580 - Innodb's ORDER BY ..LIMIT returns no rows for
# null-safe operator <=> NULL
#
CREATE TABLE t1(
c1 DATE NOT NULL,
c2 DATE NULL,
c3 DATETIME,
c4 TIMESTAMP,
PRIMARY KEY(c1),
UNIQUE(c2)
);
INSERT INTO t1 VALUES('0000-00-00', '0000-00-00', '2008-01-04', '2008-01-05');
INSERT INTO t1 VALUES('2007-05-25', '2007-05-25', '2007-05-26', '2007-05-26');
INSERT INTO t1 VALUES('2008-01-01', NULL , '2008-01-02', '2008-01-03');
INSERT INTO t1 VALUES('2008-01-17', NULL , NULL , '2009-01-29');
INSERT INTO t1 VALUES('2009-01-29', '2009-01-29', '2009-01-29', '2009-01-29');
SELECT * FROM t1 WHERE c2 <=> NULL ORDER BY c1,c2;
c1 c2 c3 c4
2008-01-01 NULL 2008-01-02 00:00:00 2008-01-03 00:00:00
2008-01-17 NULL NULL 2009-01-29 00:00:00
SELECT * FROM t1 WHERE c2 <=> NULL ORDER BY c1,c2 LIMIT 2;
c1 c2 c3 c4
2008-01-01 NULL 2008-01-02 00:00:00 2008-01-03 00:00:00
2008-01-17 NULL NULL 2009-01-29 00:00:00
DROP TABLE t1;
#
# Bug#40992 - InnoDB: Crash when engine_condition_pushdown is on
#
CREATE TABLE t (
dummy INT PRIMARY KEY,
a INT UNIQUE,
b INT
);
INSERT INTO t VALUES (1,1,1),(3,3,3),(5,5,5);
SELECT * FROM t WHERE a > 2 FOR UPDATE;
dummy a b
3 3 3
5 5 5
DROP TABLE t;
#
# Bug#35080 - Innodb crash at mem_block_get_len line 72
#
CREATE TABLE t1 (
t1_autoinc INT(11) NOT NULL AUTO_INCREMENT,
uuid VARCHAR(36) DEFAULT NULL,
PRIMARY KEY (t1_autoinc),
KEY k (uuid)
);
CREATE TABLE t2 (
t2_autoinc INT(11) NOT NULL AUTO_INCREMENT,
uuid VARCHAR(36) DEFAULT NULL,
date DATETIME DEFAULT NULL,
PRIMARY KEY (t2_autoinc),
KEY k (uuid)
);
CREATE VIEW v1 AS
SELECT t1_autoinc, uuid
FROM t1
WHERE (ISNULL(uuid) OR (uuid like '%-%'));
CREATE VIEW v2 AS
SELECT t2_autoinc, uuid, date
FROM t2
WHERE (ISNULL(uuid) OR (LENGTH(uuid) = 36));
CREATE PROCEDURE delete_multi (IN uuid CHAR(36))
DELETE v1, v2 FROM v1 INNER JOIN v2
ON v1.uuid = v2.uuid
WHERE v1.uuid = @uuid;
SET @uuid = UUID();
INSERT INTO v1 (uuid) VALUES (@uuid);
INSERT INTO v2 (uuid, date) VALUES (@uuid, '2009-09-09');
CALL delete_multi(@uuid);
DROP procedure delete_multi;
DROP table t1,t2;
DROP view v1,v2;
#
# Bug#41996 - multi-table delete crashes server (InnoDB table)
#
CREATE TABLE t1 (
b BIGINT,
i INT,
KEY (b)
);
INSERT INTO t1 VALUES (2, 2);
DELETE t1 FROM t1 a, t1 WHERE a.i=t1.b;
DROP TABLE t1;
#
# Bug#43448 - Server crashes on multi table delete with Innodb
#
CREATE TABLE t1 (
id1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
t CHAR(12)
);
CREATE TABLE t2 (
id2 INT NOT NULL,
t CHAR(12)
);
CREATE TABLE t3(
id3 INT NOT NULL,
t CHAR(12),
INDEX(id3)
);
CREATE PROCEDURE insert_data ()
BEGIN
DECLARE i1 INT DEFAULT 20;
DECLARE i2 INT;
DECLARE i3 INT;
WHILE (i1 > 0) DO
INSERT INTO t1(t) VALUES (i1);
SET i2 = 2;
WHILE (i2 > 0) DO
INSERT INTO t2(id2, t) VALUES (i1, i2);
SET i3 = 2;
WHILE (i3 > 0) DO
INSERT INTO t3(id3, t) VALUES (i1, i2);
SET i3 = i3 -1;
END WHILE;
SET i2 = i2 -1;
END WHILE;
SET i1 = i1 - 1;
END WHILE;
END |
CALL insert_data();
SELECT COUNT(*) FROM t1 WHERE id1 > 10;
COUNT(*)
10
SELECT COUNT(*) FROM t2 WHERE id2 > 10;
COUNT(*)
20
SELECT COUNT(*) FROM t3 WHERE id3 > 10;
COUNT(*)
40
DELETE t1, t2, t3
FROM t1, t2, t3
WHERE t1.id1 = t2.id2 AND t2.id2 = t3.id3 AND t1.id1 > 3;
SELECT COUNT(*) FROM t1;
COUNT(*)
3
SELECT COUNT(*) FROM t2;
COUNT(*)
6
SELECT COUNT(*) FROM t3;
COUNT(*)
12
DROP PROCEDURE insert_data;
DROP TABLE t1, t2, t3;
#
# Bug#57372 "Multi-table updates and deletes fail when running with ICP
# against InnoDB"
#
CREATE TABLE t1 (
a INT KEY,
b INT
);
CREATE TABLE t2 (
a INT KEY,
b INT
);
INSERT INTO t1 VALUES (1, 101), (2, 102), (3, 103), (4, 104), (5, 105);
INSERT INTO t2 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
UPDATE t1, t2
SET t1.a = t1.a + 100, t2.b = t1.a + 10
WHERE t1.a BETWEEN 2 AND 4 AND t2.a = t1.b - 100;
SELECT * FROM t1;
a b
1 101
102 102
103 103
104 104
5 105
SELECT * FROM t2;
a b
1 1
2 12
3 13
4 14
5 5
DROP TABLE t1, t2;
#
# Bug#59259 "Incorrect rows returned for a correlated subquery
# when ICP is on"
#
CREATE TABLE t1 (pk INTEGER PRIMARY KEY, i INTEGER NOT NULL) ENGINE=InnoDB;
INSERT INTO t1 VALUES (11,0);
INSERT INTO t1 VALUES (12,5);
INSERT INTO t1 VALUES (15,0);
CREATE TABLE t2 (pk INTEGER PRIMARY KEY, i INTEGER NOT NULL) ENGINE=InnoDB;
INSERT INTO t2 VALUES (11,1);
INSERT INTO t2 VALUES (12,2);
INSERT INTO t2 VALUES (15,4);
set @save_optimizer_switch= @@optimizer_switch;
set optimizer_switch='semijoin=off';
EXPLAIN
SELECT * FROM t1
WHERE pk IN (SELECT it.pk FROM t2 JOIN t2 AS it ON it.i=it.i WHERE it.pk-t1.i<10);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where
2 DEPENDENT SUBQUERY t2 index NULL PRIMARY 4 NULL 3 Using index
2 DEPENDENT SUBQUERY it eq_ref PRIMARY PRIMARY 4 func 1 Using index condition
SELECT * FROM t1
WHERE pk IN (SELECT it.pk FROM t2 JOIN t2 AS it ON it.i=it.i WHERE it.pk-t1.i<10);
pk i
12 5
set optimizer_switch=@save_optimizer_switch;
DROP TABLE t1, t2;
set storage_engine= @save_storage_engine;
|
