2
# Bug#36981 - "innodb crash when selecting for update"
9
INSERT INTO t1 VALUES ('3', null);
10
SELECT * FROM t1 WHERE c1='3' FOR UPDATE;
14
CREATE TABLE t1 (a INT);
15
INSERT INTO t1 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
16
CREATE TABLE t2 (a INT);
17
INSERT INTO t2 SELECT A.a + 10*(B.a + 10*C.a) FROM t1 A, t1 B, t1 C;
21
c3 CHAR(200) NOT NULL,
25
SELECT CONCAT('c-',1000+t2.a,'=w'), CONCAT('c-',1000+ t2.a,'=w'), 'filler'
28
SELECT CONCAT('c-',1000+t2.a,'=w'), CONCAT('c-',2000+t2.a,'=w'), 'filler-1'
31
SELECT CONCAT('c-',1000+t2.a,'=w'), CONCAT('c-',3000+t2.a,'=w'), 'filler-2'
33
SELECT c1,c3 FROM t3 WHERE c1 >= 'c-1994=w' and c1 != 'c-1996=w' FOR UPDATE;
52
# Bug#43360 - Server crash with a simple multi-table update
55
a CHAR(2) NOT NULL PRIMARY KEY,
56
b VARCHAR(20) NOT NULL,
60
a CHAR(2) NOT NULL PRIMARY KEY,
61
b VARCHAR(30) NOT NULL,
66
('JA','Sun Microsystems'),
73
('MS','United States of America'),
74
('IB','North America'),
75
('GO','South America');
76
UPDATE t1,t2 SET t1.b=UPPER(t1.b) WHERE t1.b LIKE 'United%';
77
SELECT * FROM t1 ORDER BY a;
84
SELECT * FROM t2 ORDER BY a;
90
MS United States of America
93
# Bug#40992 - InnoDB: Crash when engine_condition_pushdown is on
96
dummy INT PRIMARY KEY,
100
INSERT INTO t VALUES (1,1,1),(3,3,3),(5,5,5);
101
SELECT * FROM t WHERE a > 2 FOR UPDATE;
107
# Bug#35080 - Innodb crash at mem_block_get_len line 72
110
t1_autoinc INT(11) NOT NULL AUTO_INCREMENT,
111
uuid VARCHAR(36) DEFAULT NULL,
112
PRIMARY KEY (t1_autoinc),
116
t2_autoinc INT(11) NOT NULL AUTO_INCREMENT,
117
uuid VARCHAR(36) DEFAULT NULL,
118
date DATETIME DEFAULT NULL,
119
PRIMARY KEY (t2_autoinc),
123
SELECT t1_autoinc, uuid
125
WHERE (ISNULL(uuid) OR (uuid like '%-%'));
127
SELECT t2_autoinc, uuid, date
129
WHERE (ISNULL(uuid) OR (LENGTH(uuid) = 36));
130
CREATE PROCEDURE delete_multi (IN uuid CHAR(36))
131
DELETE v1, v2 FROM v1 INNER JOIN v2
133
WHERE v1.uuid = @uuid;
135
INSERT INTO v1 (uuid) VALUES (@uuid);
136
INSERT INTO v2 (uuid, date) VALUES (@uuid, '2009-09-09');
137
CALL delete_multi(@uuid);
138
DROP procedure delete_multi;
142
# Bug#41996 - multi-table delete crashes server (InnoDB table)
149
INSERT INTO t1 VALUES (2, 2);
150
DELETE t1 FROM t1 a, t1 WHERE a.i=t1.b;
153
# Bug#43448 - Server crashes on multi table delete with Innodb
156
id1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
168
CREATE PROCEDURE insert_data ()
170
DECLARE i1 INT DEFAULT 20;
174
INSERT INTO t1(t) VALUES (i1);
177
INSERT INTO t2(id2, t) VALUES (i1, i2);
180
INSERT INTO t3(id3, t) VALUES (i1, i2);
189
SELECT COUNT(*) FROM t1 WHERE id1 > 10;
192
SELECT COUNT(*) FROM t2 WHERE id2 > 10;
195
SELECT COUNT(*) FROM t3 WHERE id3 > 10;
200
WHERE t1.id1 = t2.id2 AND t2.id2 = t3.id3 AND t1.id1 > 3;
201
SELECT COUNT(*) FROM t1;
204
SELECT COUNT(*) FROM t2;
207
SELECT COUNT(*) FROM t3;
210
DROP PROCEDURE insert_data;
211
DROP TABLE t1, t2, t3;
213
# Bug#42580 - Innodb's ORDER BY ..LIMIT returns no rows for
214
# null-safe operator <=> NULL
225
INSERT INTO t1 VALUES('0000-00-00', '0000-00-00', '2008-01-04', '2008-01-05');
226
INSERT INTO t1 VALUES('2007-05-25', '2007-05-25', '2007-05-26', '2007-05-26');
227
INSERT INTO t1 VALUES('2008-01-01', NULL , '2008-01-02', '2008-01-03');
228
INSERT INTO t1 VALUES('2008-01-17', NULL , NULL , '2009-01-29');
229
INSERT INTO t1 VALUES('2009-01-29', '2009-01-29', '2009-01-29', '2009-01-29');
231
SELECT * FROM t1 WHERE c2 <=> NULL ORDER BY c1,c2;
233
2008-01-01 NULL 2008-01-02 00:00:00 2008-01-03 00:00:00
234
2008-01-17 NULL NULL 2009-01-29 00:00:00
236
SELECT * FROM t1 WHERE c2 <=> NULL ORDER BY c1,c2 LIMIT 2;
238
2008-01-01 NULL 2008-01-02 00:00:00 2008-01-03 00:00:00
239
2008-01-17 NULL NULL 2009-01-29 00:00:00
243
# Bug#43617 - Innodb returns wrong results with timestamp's range value
245
# (Note: Fixed by patch for BUG#42580)
248
c1 TIMESTAMP NOT NULL,
255
INSERT INTO t1 VALUES
256
('0000-00-00 00:00:00','0000-00-00 00:00:00','2008-01-04','2008-01-05 00:00:00'),
257
('1971-01-01 00:00:01','1980-01-01 00:00:01','2009-01-01','2009-01-02 00:00:00'),
258
('1999-01-01 00:00:00','1999-01-01 00:00:00', NULL, NULL),
259
('2007-05-23 09:15:28','2007-05-23 09:15:28','2007-05-24','2007-05-24 09:15:28'),
260
('2007-05-27 00:00:00','2007-05-25 00:00:00','2007-05-26','2007-05-26 00:00:00'),
261
('2008-01-01 00:00:00', NULL, '2008-01-02','2008-01-03 00:00:00'),
262
('2009-01-29 11:11:27','2009-01-29 11:11:27','2009-01-29','2009-01-29 11:11:27'),
263
('2038-01-09 03:14:07','2038-01-09 03:14:07','2009-01-05','2009-01-06 00:00:00');
267
WHERE c2 IN ('1971-01-01 00:00:01','2038-01-09 03:14:07')
270
2038-01-09 03:14:07 2038-01-09 03:14:07 2009-01-05 2009-01-06 00:00:00
274
WHERE c2 IN ('1971-01-01 00:00:01','2038-01-09 03:14:07')
277
2038-01-09 03:14:07 2038-01-09 03:14:07 2009-01-05 2009-01-06 00:00:00
281
WHERE c2 IN ('1971-01-01 00:00:01','2038-01-09 03:14:07')
284
2038-01-09 03:14:07 2038-01-09 03:14:07 2009-01-05 2009-01-06 00:00:00
288
WHERE c2 IN ('1971-01-01 00:00:01','2038-01-09 03:14:07')
289
ORDER BY c2 DESC LIMIT 2;
291
2038-01-09 03:14:07 2038-01-09 03:14:07 2009-01-05 2009-01-06 00:00:00
295
# Bug#43249 - Innodb returns zero time for the time column
296
# with <=> NULL order by limit
297
# (Note: Fixed by patch for BUG#42580)
306
INSERT INTO t1 VALUES('8:29:45',NULL,'2009-02-01');
308
SELECT * FROM t1 WHERE c2 <=> NULL ORDER BY c2 LIMIT 2;
310
08:29:45 NULL 2009-02-01
312
SELECT * FROM t1 WHERE c2 <=> NULL ORDER BY c2 LIMIT 2;
314
08:29:45 NULL 2009-02-01