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
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
1035
1036
1037
1038
1039
1040
1041
1042
1043
1044
1045
1046
1047
1048
1049
1050
1051
1052
1053
1054
1055
1056
1057
1058
1059
1060
1061
1062
#
# 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#43360 - Server crash with a simple multi-table update
#
CREATE TABLE t1 (
a CHAR(2) NOT NULL PRIMARY KEY,
b VARCHAR(20) NOT NULL,
KEY (b)
);
CREATE TABLE t2 (
a CHAR(2) NOT NULL PRIMARY KEY,
b VARCHAR(30) NOT NULL,
KEY (b)
);
INSERT INTO t1 VALUES
('AB','MySQL AB'),
('JA','Sun Microsystems'),
('MS','Microsoft'),
('IB','IBM- Inc.'),
('GO','Google Inc.');
INSERT INTO t2 VALUES
('AB','Sweden'),
('JA','USA'),
('MS','United States of America'),
('IB','North America'),
('GO','South America');
UPDATE t1,t2 SET t1.b=UPPER(t1.b) WHERE t1.b LIKE 'United%';
SELECT * FROM t1 ORDER BY a;
a b
AB MySQL AB
GO Google Inc.
IB IBM- Inc.
JA Sun Microsystems
MS Microsoft
SELECT * FROM t2 ORDER BY a;
a b
AB Sweden
GO South America
IB North America
JA USA
MS United States of America
DROP TABLE t1,t2;
#
# 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#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#43617 - Innodb returns wrong results with timestamp's range value
# in IN clause
# (Note: Fixed by patch for BUG#42580)
#
CREATE TABLE t1(
c1 TIMESTAMP NOT NULL,
c2 TIMESTAMP NULL,
c3 DATE,
c4 DATETIME,
PRIMARY KEY(c1),
UNIQUE INDEX(c2)
);
INSERT INTO t1 VALUES
('0000-00-00 00:00:00','0000-00-00 00:00:00','2008-01-04','2008-01-05 00:00:00'),
('1971-01-01 00:00:01','1980-01-01 00:00:01','2009-01-01','2009-01-02 00:00:00'),
('1999-01-01 00:00:00','1999-01-01 00:00:00', NULL, NULL),
('2007-05-23 09:15:28','2007-05-23 09:15:28','2007-05-24','2007-05-24 09:15:28'),
('2007-05-27 00:00:00','2007-05-25 00:00:00','2007-05-26','2007-05-26 00:00:00'),
('2008-01-01 00:00:00', NULL, '2008-01-02','2008-01-03 00:00:00'),
('2009-01-29 11:11:27','2009-01-29 11:11:27','2009-01-29','2009-01-29 11:11:27'),
('2038-01-09 03:14:07','2038-01-09 03:14:07','2009-01-05','2009-01-06 00:00:00');
SELECT *
FROM t1
WHERE c2 IN ('1971-01-01 00:00:01','2038-01-09 03:14:07')
ORDER BY c2;
c1 c2 c3 c4
2038-01-09 03:14:07 2038-01-09 03:14:07 2009-01-05 2009-01-06 00:00:00
SELECT *
FROM t1
WHERE c2 IN ('1971-01-01 00:00:01','2038-01-09 03:14:07')
ORDER BY c2 LIMIT 2;
c1 c2 c3 c4
2038-01-09 03:14:07 2038-01-09 03:14:07 2009-01-05 2009-01-06 00:00:00
SELECT *
FROM t1
WHERE c2 IN ('1971-01-01 00:00:01','2038-01-09 03:14:07')
ORDER BY c2 DESC;
c1 c2 c3 c4
2038-01-09 03:14:07 2038-01-09 03:14:07 2009-01-05 2009-01-06 00:00:00
SELECT *
FROM t1
WHERE c2 IN ('1971-01-01 00:00:01','2038-01-09 03:14:07')
ORDER BY c2 DESC LIMIT 2;
c1 c2 c3 c4
2038-01-09 03:14:07 2038-01-09 03:14:07 2009-01-05 2009-01-06 00:00:00
DROP TABLE t1;
#
# Bug#43249 - Innodb returns zero time for the time column
# with <=> NULL order by limit
# (Note: Fixed by patch for BUG#42580)
#
CREATE TABLE t1(
c1 TIME NOT NULL,
c2 TIME NULL,
c3 DATE,
PRIMARY KEY(c1),
UNIQUE INDEX(c2)
);
INSERT INTO t1 VALUES('8:29:45',NULL,'2009-02-01');
SELECT * FROM t1 WHERE c2 <=> NULL ORDER BY c2 LIMIT 2;
c1 c2 c3
08:29:45 NULL 2009-02-01
SELECT * FROM t1 WHERE c2 <=> NULL ORDER BY c2 LIMIT 2;
c1 c2 c3
08:29:45 NULL 2009-02-01
DROP TABLE t1;
#
# BUG#43618: MyISAM&Maria returns wrong results with 'between'
# on timestamp
#
CREATE TABLE t1(
ts TIMESTAMP NOT NULL,
c char NULL,
PRIMARY KEY(ts)
);
INSERT INTO t1 VALUES
('1971-01-01','a'),
('2007-05-25','b'),
('2008-01-01','c'),
('2038-01-09','d');
# Execute select with invalid timestamp, desc ordering
SELECT *
FROM t1
WHERE ts BETWEEN '0000-00-00' AND '2010-00-01 00:00:00'
ORDER BY ts DESC
LIMIT 2;
ts c
2008-01-01 00:00:00 c
2007-05-25 00:00:00 b
# Should use index condition
EXPLAIN
SELECT *
FROM t1
WHERE ts BETWEEN '0000-00-00' AND '2010-00-01 00:00:00'
ORDER BY ts DESC
LIMIT 2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 4 Using where
DROP TABLE t1;
#
# BUG#49906: Assertion failed - Field_varstring::val_str in field.cc
#
CREATE TABLE t1 (
f1 VARCHAR(1024),
f2 VARCHAR(10),
INDEX test_idx USING BTREE (f2,f1(5))
);
INSERT INTO t1 VALUES ('a','c'), ('b','d');
SELECT f1
FROM t1
WHERE f2 LIKE 'd'
ORDER BY f1;
f1
b
DROP TABLE t1;
#
# Bug#52660 - "Perf. regr. using ICP for MyISAM on range queries on
# an index containing TEXT"
#
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) FROM t1 A, t1 B;
CREATE TABLE t3 (
c1 TINYTEXT NOT NULL,
i1 INT NOT NULL,
KEY (c1(6),i1)
);
INSERT INTO t3 SELECT CONCAT('c-',1000+t2.a,'=w'), 1 FROM t2;
EXPLAIN SELECT c1 FROM t3 WHERE c1 >= 'c-1004=w' and c1 <= 'c-1006=w' and i1 > 2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t3 range c1 c1 12 NULL 2 Using where
SELECT c1 FROM t3 WHERE c1 >= 'c-1004=w' and c1 <= 'c-1006=w' and i1 > 2;
c1
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
) ENGINE = INNODB;
CREATE TABLE t2 (
a INT KEY,
b INT
) ENGINE = INNODB;
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#52605 - "Adding LIMIT 1 clause to query with complex range
# predicate causes wrong results"
#
CREATE TABLE t1 (
pk INT NOT NULL,
c1 INT,
PRIMARY KEY (pk),
KEY k1 (c1)
);
INSERT INTO t1 VALUES (1,NULL);
INSERT INTO t1 VALUES (2,6);
INSERT INTO t1 VALUES (3,NULL);
INSERT INTO t1 VALUES (4,6);
INSERT INTO t1 VALUES (5,NULL);
INSERT INTO t1 VALUES (6,NULL);
INSERT INTO t1 VALUES (7,9);
INSERT INTO t1 VALUES (8,0);
SELECT pk, c1
FROM t1
WHERE (pk BETWEEN 4 AND 5 OR pk < 2) AND c1 < 240
ORDER BY c1
LIMIT 1;
pk c1
4 6
EXPLAIN SELECT pk, c1
FROM t1
WHERE (pk BETWEEN 4 AND 5 OR pk < 2) AND c1 < 240
ORDER BY c1
LIMIT 1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range PRIMARY,k1 k1 5 NULL 4 Using where
DROP TABLE t1;
#
# Bug#42991 "invalid memory access and/or crash when using
# index condition pushdown + InnoDB"
#
CREATE TABLE t1 (
c1 TINYTEXT NOT NULL,
c2 INT NOT NULL,
PRIMARY KEY (c2),
KEY id1 (c1(4))
);
INSERT INTO t1 VALUES ('Anastasia', 5);
INSERT INTO t1 VALUES ('Karianne', 4);
SELECT * FROM t1 WHERE (c1 <= '6566-06-15' AND c2 <> 3);
c1 c2
EXPLAIN SELECT * FROM t1 WHERE (c1 <= '6566-06-15' AND c2 <> 3);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range PRIMARY,id1 id1 6 NULL 1 Using where
DROP TABLE t1;
#
# Bug#56529 - "Crash due to long semaphore wait in InnoDB
# with ICP and subqueries"
#
CREATE TABLE t1 (
col_int_nokey INTEGER,
col_int_key INTEGER,
col_varchar_key VARCHAR(1),
KEY (col_int_key),
KEY (col_varchar_key, col_int_key)
) stats_persistent=0;
INSERT INTO t1 VALUES (NULL,2,'w');
INSERT INTO t1 VALUES (7,9,'m');
INSERT INTO t1 VALUES (9,3,'m');
INSERT INTO t1 VALUES (7,9,'k');
INSERT INTO t1 VALUES (4,NULL,'r');
INSERT INTO t1 VALUES (2,9,'t');
INSERT INTO t1 VALUES (6,3,'j');
INSERT INTO t1 VALUES (8,8,'u');
INSERT INTO t1 VALUES (NULL,8,'h');
INSERT INTO t1 VALUES (5,53,'o');
INSERT INTO t1 VALUES (NULL,0,NULL);
INSERT INTO t1 VALUES (6,5,'k');
INSERT INTO t1 VALUES (188,166,'e');
INSERT INTO t1 VALUES (2,3,'n');
INSERT INTO t1 VALUES (1,0,'t');
INSERT INTO t1 VALUES (1,1,'c');
INSERT INTO t1 VALUES (0,9,'m');
INSERT INTO t1 VALUES (9,5,'y');
INSERT INTO t1 VALUES (NULL,6,'f');
CREATE TABLE t2 (
c1 INTEGER NOT NULL
) stats_persistent=0;
EXPLAIN SELECT table1.col_int_nokey
FROM t1 AS table1 STRAIGHT_JOIN (
t1 AS table2 INNER JOIN t1 AS table3
ON table3.col_varchar_key = table2.col_varchar_key)
ON table3.col_int_nokey = table1.col_int_key
WHERE table3.col_int_key != SOME ( SELECT c1 FROM t2 );
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY table1 ALL col_int_key NULL NULL NULL 19 NULL
1 PRIMARY table3 ALL col_varchar_key NULL NULL NULL 19 Using where; Using join buffer (Block Nested Loop)
1 PRIMARY table2 ref col_varchar_key col_varchar_key 4 test.table3.col_varchar_key 2 Using index
2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
SELECT table1.col_int_nokey
FROM t1 AS table1 STRAIGHT_JOIN (
t1 AS table2 INNER JOIN t1 AS table3
ON table3.col_varchar_key = table2.col_varchar_key)
ON table3.col_int_nokey = table1.col_int_key
WHERE table3.col_int_key != SOME ( SELECT c1 FROM t2 );
col_int_nokey
DROP TABLE t1, t2;
#
# Bug#58243 "RQG test optimizer_subquery causes server crash
# when running with ICP"
#
CREATE TABLE t1 (
pk INTEGER NOT NULL,
c1 INTEGER NOT NULL,
c2 INTEGER NOT NULL,
PRIMARY KEY (pk)
);
INSERT INTO t1 VALUES (1,6,7);
CREATE TABLE t2 (
c1 INTEGER NOT NULL
);
EXPLAIN SELECT t1.c1
FROM t1
WHERE t1.pk < 317 AND 2 IN (SELECT COUNT(t2.c1)
FROM t2)
ORDER BY t1.c2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
SELECT t1.c1
FROM t1
WHERE t1.pk < 317 AND 2 IN (SELECT COUNT(t2.c1)
FROM t2)
ORDER BY t1.c2;
c1
DROP TABLE t1, t2;
CREATE TABLE t1 (
i1 INTEGER NOT NULL,
c1 VARCHAR(1) NOT NULL
);
INSERT INTO t1 VALUES (2,'w');
CREATE TABLE t2 (
i1 INTEGER NOT NULL,
c1 VARCHAR(1) NOT NULL,
c2 VARCHAR(1) NOT NULL,
KEY (c1, i1)
);
INSERT INTO t2 VALUES (8,'d','d');
INSERT INTO t2 VALUES (4,'v','v');
CREATE TABLE t3 (
c1 VARCHAR(1) NOT NULL
);
INSERT INTO t3 VALUES ('v');
EXPLAIN SELECT i1
FROM t1
WHERE EXISTS (SELECT t2.c1
FROM (t2 INNER JOIN t3 ON (t3.c1 = t2.c1))
WHERE t2.c2 != t1.c1 AND t2.c2 = (SELECT MIN(t3.c1)
FROM t3));
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1 NULL
2 DEPENDENT SUBQUERY t3 system NULL NULL NULL NULL 1 NULL
2 DEPENDENT SUBQUERY t2 ref c1 c1 3 const 1 Using where
3 SUBQUERY t3 system NULL NULL NULL NULL 1 NULL
SELECT i1
FROM t1
WHERE EXISTS (SELECT t2.c1
FROM (t2 INNER JOIN t3 ON (t3.c1 = t2.c1))
WHERE t2.c2 != t1.c1 AND t2.c2 = (SELECT MIN(t3.c1)
FROM t3));
i1
2
DROP TABLE t1,t2,t3;
#
# Bug#58015 "Assert in row_sel_field_store_in_mysql_format
# when running innodb_mrr_icp test"
#
create table t1 (a char(2) charset utf8,b double, primary key (a(1)),key (b));
insert into t1 values ('',1);
select 1 from t1 where b <= 1 and a <> '';
1
drop table t1;
#
# 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);
SELECT * FROM t1
WHERE pk IN (SELECT it.pk FROM t2 JOIN t2 AS it ON it.i=it.i WHERE t1.i);
pk i
12 5
DROP TABLE t1, t2;
#
# Bug #58816 "Extra temporary duplicate rows in result set when
# switching ICP off"
#
set @save_optimizer_switch_bug58816= @@optimizer_switch;
CREATE TABLE t1 (
pk INT NOT NULL,
c1 INT NOT NULL,
PRIMARY KEY (pk)
);
INSERT INTO t1 VALUES (1,9),(2,7),(3,6),(4,3),(5,1);
EXPLAIN SELECT pk, c1 FROM t1 WHERE pk <> 3;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 5 Using where
SET SESSION optimizer_switch='index_condition_pushdown=off';
SELECT pk, c1 FROM t1 WHERE pk <> 3;
pk c1
1 9
2 7
4 3
5 1
DROP TABLE t1;
set optimizer_switch= @save_optimizer_switch_bug58816;
#
# Bug#58837: ICP crash or valgrind error due to uninitialized
# value in innobase_index_cond
#
CREATE TABLE t1 (
t1_int INT,
t1_time TIME
);
CREATE TABLE t2 (
t2_int int PRIMARY KEY,
t2_int2 INT
);
INSERT INTO t2 VALUES ();
INSERT INTO t1 VALUES ();
SELECT *
FROM t1 AS t1a
WHERE NOT EXISTS
(SELECT *
FROM t1 AS t1b
WHERE t1b.t1_int NOT IN
(SELECT t2.t2_int
FROM t2
WHERE t1b.t1_time LIKE t1b.t1_int
OR t1b.t1_time <> t2.t2_int2
AND 6=7
)
)
;;
t1_int t1_time
EXPLAIN SELECT *
FROM t1 AS t1a
WHERE NOT EXISTS
(SELECT *
FROM t1 AS t1b
WHERE t1b.t1_int NOT IN
(SELECT t2.t2_int
FROM t2
WHERE t1b.t1_time LIKE t1b.t1_int
OR t1b.t1_time <> t2.t2_int2
AND 6=7
)
)
;;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
2 SUBQUERY t1b system NULL NULL NULL NULL 1 NULL
3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
DROP TABLE t1,t2;
#
# Bug#59186 Wrong results of join when ICP is enabled
#
CREATE TABLE t1 (
pk INTEGER NOT NULL,
c1 VARCHAR(3) NOT NULL,
PRIMARY KEY (pk)
);
INSERT INTO t1 VALUES (1,'y'),(0,'or');
CREATE TABLE t2 (
pk INTEGER NOT NULL,
c1 VARCHAR(3) NOT NULL,
c2 VARCHAR(6) NOT NULL,
PRIMARY KEY (pk)
);
INSERT INTO t2 VALUES (6,'y','RPOYT'),(10,'m','JINQE');
EXPLAIN SELECT c2 FROM t1 JOIN t2 ON t1.c1 = t2.c1
WHERE (t2.pk <= 4 AND t1.pk IN (2,1)) OR
(t1.pk > 1 AND t2.pk BETWEEN 6 AND 6);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using where
1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 2 Using where; Using join buffer (Block Nested Loop)
SELECT c2 FROM t1 JOIN t2 ON t1.c1 = t2.c1
WHERE (t2.pk <= 4 AND t1.pk IN (2,1)) OR
(t1.pk > 1 AND t2.pk BETWEEN 6 AND 6);
c2
DROP TABLE t1, t2;
#
# Bug#58838 "Wrong results with HAVING + LIMIT without GROUP BY when
# ICP is enabled"
#
CREATE TABLE t1 (
pk INT NOT NULL,
c1 INT,
PRIMARY KEY (pk),
KEY col_int_key (c1)
);
INSERT INTO t1 VALUES (1,37),(2,8),(3,-25),(4,NULL),(5,55);
SELECT pk FROM t1 WHERE c1 <> 1 HAVING pk = 3 ORDER BY pk LIMIT 0;
pk
SELECT pk FROM t1 WHERE c1 <> 1 HAVING pk = 3 ORDER BY pk LIMIT 1;
pk
3
SELECT pk FROM t1 WHERE c1 <> 1 HAVING pk = 3 ORDER BY pk LIMIT 2;
pk
3
SELECT pk FROM t1 WHERE c1 <> 1 HAVING pk = 3 ORDER BY pk LIMIT 5;
pk
3
DROP TABLE t1;
#
# Bug#59483 "Crash on INSERT/REPLACE in
# rec_convert_dtuple_to_rec_comp with ICP on"
#
CREATE TABLE t1 (
pk INTEGER AUTO_INCREMENT PRIMARY KEY,
i1 INTEGER,
c1 CHAR(6),
i2 INTEGER NOT NULL,
KEY (i2)
);
INSERT INTO t1 VALUES
(NULL, 4, 'that', 8),
(NULL, 1, 'she', 6),
(NULL, 6, 'tell', 2);
SELECT * FROM t1 WHERE i2 IN (3, 6) LIMIT 2 FOR UPDATE;
pk i1 c1 i2
2 1 she 6
INSERT INTO t1 (i2) VALUES (1);
DROP TABLE t1;
#
# Bug #11766678 - 59843:
# USING UNINITIALISED VALUE IN USES_INDEX_FIELDS_ONLY
#
CREATE TABLE t1 (
col999 FLOAT NOT NULL,
COL1000 VARBINARY(179) NOT NULL,
col1003 DATE DEFAULT NULL,
KEY idx4267 (col1000, col1003)
);
INSERT INTO t1 VALUES (),();
Warnings:
Warning 1364 Field 'col999' doesn't have a default value
Warning 1364 Field 'COL1000' doesn't have a default value
SELECT col999 FROM t1 WHERE col1000 = "3" AND col1003 <=> sysdate();
col999
DROP TABLE t1;
#
# Bug#11873324 "WRONG RESULT WITH ICP AND STRAIGHT_JOIN"
#
CREATE TABLE t1 (
pk INTEGER NOT NULL,
i1 INTEGER,
PRIMARY KEY (pk),
KEY col_int_key (i1)
);
INSERT INTO t1 VALUES (14,NULL), (18,133);
CREATE TABLE t2 (
pk INTEGER NOT NULL,
i1 INTEGER,
c1 VARCHAR(1),
PRIMARY KEY (pk),
KEY col_int_key (i1)
);
INSERT INTO t2 VALUES (1,7,'f');
set @old_opt_switch=@@optimizer_switch;
EXPLAIN SELECT t1.i1
FROM t1
WHERE t1.i1 NOT IN
( SELECT STRAIGHT_JOIN SUBQUERY_t1.pk
FROM t1 AS SUBQUERY_t1
JOIN t2 AS SUBQUERY_t2
ON SUBQUERY_t2.i1 = SUBQUERY_t1.pk
WHERE SUBQUERY_t1.i1 > 0
OR SUBQUERY_t2.c1 = 'a'
);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 index NULL col_int_key 5 NULL 2 Using where; Using index
2 DEPENDENT SUBQUERY SUBQUERY_t1 eq_ref PRIMARY,col_int_key PRIMARY 4 func 1 Using where; Full scan on NULL key
2 DEPENDENT SUBQUERY SUBQUERY_t2 ALL col_int_key NULL NULL NULL 1 Using where; Using join buffer (Block Nested Loop)
SELECT t1.i1
FROM t1
WHERE t1.i1 NOT IN
( SELECT STRAIGHT_JOIN SUBQUERY_t1.pk
FROM t1 AS SUBQUERY_t1
JOIN t2 AS SUBQUERY_t2
ON SUBQUERY_t2.i1 = SUBQUERY_t1.pk
WHERE SUBQUERY_t1.i1 > 0
OR SUBQUERY_t2.c1 = 'a'
);
i1
NULL
133
set @@optimizer_switch=@old_opt_switch;
DROP TABLE t1,t2;
#
# Bug#11876420 "MISSING ROW IN RESULT WITH SUBQUERY + IN + XOR +
# NULL VALUES AND ICP ENABLED"
#
CREATE TABLE t1 (
i1 INTEGER,
c1 VARCHAR(1),
KEY col_varchar_key (c1)
);
INSERT INTO t1 VALUES (1,'j'), (0,'e'), (210,'f'), (8,'v'), (7,'x'),
(5,'m'), (NULL,'c');
CREATE TABLE t2 (
i1 INTEGER,
c1 VARCHAR(1),
KEY col_varchar_key (c1)
);
INSERT INTO t2 VALUES (8,NULL);
CREATE TABLE t3 (
i1 INTEGER,
c1 VARCHAR(1),
KEY col_varchar_key (c1)
) ENGINE=InnoDB;
INSERT INTO t3 VALUES (NULL,'w'), (1,NULL), (2,'d');
set @old_opt_switch=@@optimizer_switch;
EXPLAIN SELECT i1
FROM t3
WHERE c1 IN
( SELECT t1.c1
FROM t2 JOIN t1
ON t2.i1 >= t1.i1
WHERE t1.c1 > t2.c1
)
XOR i1;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 Using where
2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
SELECT i1
FROM t3
WHERE c1 IN
( SELECT t1.c1
FROM t2 JOIN t1
ON t2.i1 >= t1.i1
WHERE t1.c1 > t2.c1
)
XOR i1;
i1
1
2
set @@optimizer_switch=@old_opt_switch;
DROP TABLE t1, t2, t3;
#
# Bug#12355958 "FAILING ASSERTION: TRX->LOCK.N_ACTIVE_THRS == 1"
#
CREATE TABLE t1 (
pk INTEGER PRIMARY KEY,
a INTEGER NOT NULL,
b CHAR(1),
KEY(b)
);
INSERT INTO t1 VALUES (23,5,'d');
EXPLAIN SELECT a1.pk
FROM t1 AS a1 JOIN (SELECT * FROM t1 LIMIT 1) AS a2 ON a2.b = a1.b
WHERE a1.a = (SELECT pk FROM t1 LIMIT 1)
AND (a1.a != a2.a OR a1.b IS NULL);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
3 SUBQUERY t1 system NULL NULL NULL NULL 1 NULL
2 DERIVED t1 system NULL NULL NULL NULL 1 NULL
SELECT a1.pk
FROM t1 AS a1 JOIN (SELECT * FROM t1 LIMIT 1) AS a2 ON a2.b = a1.b
WHERE a1.a = (SELECT pk FROM t1 LIMIT 1)
AND (a1.a != a2.a OR a1.b IS NULL);
pk
CREATE VIEW v1 AS SELECT * FROM t1;
EXPLAIN SELECT a1.pk
FROM v1 AS a1 JOIN (SELECT * FROM v1 LIMIT 1) AS a2 ON a2.b = a1.b
WHERE a1.a = (SELECT pk FROM v1 LIMIT 1)
AND (a1.a != a2.a OR a1.b IS NULL);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
3 SUBQUERY t1 system NULL NULL NULL NULL 1 NULL
2 DERIVED t1 system NULL NULL NULL NULL 1 NULL
SELECT a1.pk
FROM v1 AS a1 JOIN (SELECT * FROM v1 LIMIT 1) AS a2 ON a2.b = a1.b
WHERE a1.a = (SELECT pk FROM v1 LIMIT 1)
AND (a1.a != a2.a OR a1.b IS NULL);
pk
DROP VIEW v1;
DROP TABLE t1;
#
# BUG#12601961 "SEGFAULT IN HANDLER::COMPARE_KEY2"
# BUG#12724899 "SELECT STRAIGHT_JOIN QUERY GIVES 2 DATES VERSUS
# 2 WARNINGS WITH ICP ON"
#
CREATE TABLE t1 (
pk INTEGER NOT NULL,
i1 INTEGER NOT NULL,
c1 VARCHAR(1) NOT NULL,
PRIMARY KEY (pk)
);
INSERT INTO t1 VALUES (1,3,'j'), (20,8,'e');
EXPLAIN SELECT alias2.i1
FROM t1 AS alias1 STRAIGHT_JOIN t1 AS alias2
ON alias2.pk AND alias2.pk <= alias1.c1
WHERE alias2.pk = 1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE alias1 ALL NULL NULL NULL NULL 2 NULL
1 SIMPLE alias2 const PRIMARY PRIMARY 4 const 1 Using where
SELECT alias2.i1
FROM t1 AS alias1 STRAIGHT_JOIN t1 AS alias2
ON alias2.pk AND alias2.pk <= alias1.c1
WHERE alias2.pk = 1;
i1
Warnings:
Warning 1292 Truncated incorrect DOUBLE value: 'j'
Warning 1292 Truncated incorrect DOUBLE value: 'e'
DROP TABLE t1;
#
# BUG#12822678 - 2 MORE ROWS WHEN ICP=ON W/ STRAIGHT_JOIN
#
CREATE TABLE t1 (
i1 INTEGER NOT NULL,
d1 DOUBLE,
KEY k1 (d1)
);
INSERT INTO t1 VALUES (10,1), (17,NULL), (22,NULL);
CREATE TABLE t2 (
pk INTEGER NOT NULL,
i1 INTEGER NOT NULL,
PRIMARY KEY (pk)
);
INSERT INTO t2 VALUES (4,1);
EXPLAIN SELECT t1.d1, t2.pk, t2.i1
FROM t1 STRAIGHT_JOIN t2 ON t2.i1
WHERE t2.pk <> t1.d1 AND t2.pk = 4;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 index NULL k1 9 NULL 3 Using index
1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1 Using where
SELECT t1.d1, t2.pk, t2.i1
FROM t1 STRAIGHT_JOIN t2 ON t2.i1
WHERE t2.pk <> t1.d1 AND t2.pk = 4;
d1 pk i1
1 4 1
DROP TABLE t1, t2;
#
# BUG#12838420 "DUPLICATE VALUES FOR GROUP-BY COLUMN WHEN JOIN
# BUFFERING IS OFF"
#
CREATE TABLE t1 (
col_int_key INT,
pk INT,
PRIMARY KEY (pk),
KEY (col_int_key)
);
INSERT INTO t1 VALUES (2,3),(3,2),(3,5),(4,6);
CREATE TABLE t2 (
col_int_key INT,
pk INT,
PRIMARY KEY (pk),
KEY (col_int_key)
);
INSERT INTO t2 VALUES (0,9),(3,10),(4,6),(6,1),(100,3),(200,5);
set @old_opt_switch=@@optimizer_switch;
SET optimizer_switch="block_nested_loop=off";
EXPLAIN SELECT t2.col_int_key AS field1
FROM t2 USE INDEX(col_int_key) STRAIGHT_JOIN t1 ON t2.col_int_key
WHERE t2.pk < 7 AND t2.col_int_key <> 7
GROUP BY field1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 ALL col_int_key NULL NULL NULL 6 Using where; Using temporary; Using filesort
1 SIMPLE t1 index NULL PRIMARY 4 NULL 4 Using index
SELECT t2.col_int_key AS field1
FROM t2 USE INDEX(col_int_key) STRAIGHT_JOIN t1 ON t2.col_int_key
WHERE t2.pk < 7 AND t2.col_int_key <> 7
GROUP BY field1;
field1
4
6
100
200
SET @@optimizer_switch=@old_opt_switch;
DROP TABLE t1,t2;
#
# Bug#12976163 "CRASH IN INDEX CONDITION PUSHDOWN CODE AGAINST
# A MYISAM TABLE"
#
CREATE TABLE t1 (
i1 INTEGER NOT NULL,
i2 INTEGER NOT NULL
);
INSERT INTO t1 VALUES (14,1), (15,2), (16,3);
CREATE TABLE t2 (
i1 INTEGER NOT NULL,
i2 INTEGER NOT NULL,
c1 TINYTEXT
);
INSERT INTO t2
SELECT i1, 10 * i2, "MySQL" FROM t1;
CREATE PROCEDURE proc1(id INTEGER)
BEGIN
SELECT i2
FROM (
(SELECT i1, i2, NULL AS a1 FROM t1)
UNION
(SELECT i1, i2, c1 AS a1 FROM t2)
) u1
WHERE i1 = id;
END$$
CALL proc1(15);
i2
2
20
DROP PROCEDURE proc1;
DROP TABLE t1, t2;
#
# Bug#13655397 "CRASH IN SYNC_THREAD_LEVELS_NONEMPTY_TRX"
#
CREATE TABLE t1 (
i1 INTEGER NOT NULL,
i2 INTEGER NOT NULL,
KEY (i1)
);
INSERT INTO t1 VALUES (4,4), (5,5);
CREATE TABLE t2 (
pk INTEGER NOT NULL,
PRIMARY KEY (pk)
);
INSERT INTO t2 VALUES (1);
CREATE FUNCTION f1() RETURNS INTEGER
RETURN (SELECT MOD(COUNT(DISTINCT pk), 10) FROM t2);
EXPLAIN SELECT i1, i2 FROM t1 WHERE f1() = 1 AND i1 = 5;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ref i1 i1 4 const 1 Using where
SELECT i1, i2 FROM t1 WHERE f1() = 1 AND i1 = 5;
i1 i2
5 5
DROP FUNCTION f1;
DROP TABLE t1, t2;
set optimizer_switch=default;