1--echo # 2--echo # Bug#36981 - "innodb crash when selecting for update" 3--echo # 4 5# 6# Test 1: Test based on the reproduction test case for this bug. 7# This query resulted in a crash in InnoDB due to 8# InnoDB changing from using the index which the push condition 9# where for to use the clustered index due to "SELECT ... FOR UPDATE". 10# 11 12CREATE TABLE t1 ( 13 c1 CHAR(1), 14 c2 CHAR(10), 15 KEY (c1) 16); 17 18INSERT INTO t1 VALUES ('3', null); 19 20SELECT * FROM t1 WHERE c1='3' FOR UPDATE; 21 22DROP TABLE t1; 23 24# 25# Test 2: Extended test case to test that the correct rows are returned. 26# This test is for ensuring that if InnoDB refuses to accept 27# the pushed index condition it is still evaluated. 28# 29 30CREATE TABLE t1 (a INT); 31INSERT INTO t1 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 32 33CREATE TABLE t2 (a INT); 34INSERT INTO t2 SELECT A.a + 10*(B.a + 10*C.a) FROM t1 A, t1 B, t1 C; 35 36CREATE TABLE t3 ( 37 c1 CHAR(10) NOT NULL, 38 c2 CHAR(10) NOT NULL, 39 c3 CHAR(200) NOT NULL, 40 KEY (c1) 41); 42 43INSERT INTO t3 44 SELECT CONCAT('c-',1000+t2.a,'=w'), CONCAT('c-',1000+ t2.a,'=w'), 'filler' 45 FROM t2; 46 47INSERT INTO t3 48 SELECT CONCAT('c-',1000+t2.a,'=w'), CONCAT('c-',2000+t2.a,'=w'), 'filler-1' 49 FROM t2; 50 51INSERT INTO t3 52 SELECT CONCAT('c-',1000+t2.a,'=w'), CONCAT('c-',3000+t2.a,'=w'), 'filler-2' 53 FROM t2; 54 55--sorted_result 56SELECT c1,c3 FROM t3 WHERE c1 >= 'c-1994=w' and c1 != 'c-1996=w' FOR UPDATE; 57 58DROP TABLE t1,t2,t3; 59 60--echo # 61--echo # Bug#43360 - Server crash with a simple multi-table update 62--echo # 63 64CREATE TABLE t1 ( 65 a CHAR(2) NOT NULL PRIMARY KEY, 66 b VARCHAR(20) NOT NULL, 67 KEY (b) 68); 69 70CREATE TABLE t2 ( 71 a CHAR(2) NOT NULL PRIMARY KEY, 72 b VARCHAR(30) NOT NULL, 73 KEY (b) 74); 75 76INSERT INTO t1 VALUES 77('AB','MySQL AB'), 78('JA','Sun Microsystems'), 79('MS','Microsoft'), 80('IB','IBM- Inc.'), 81('GO','Google Inc.'); 82 83INSERT INTO t2 VALUES 84('AB','Sweden'), 85('JA','USA'), 86('MS','United States of America'), 87('IB','North America'), 88('GO','South America'); 89 90UPDATE t1,t2 SET t1.b=UPPER(t1.b) WHERE t1.b LIKE 'United%'; 91 92SELECT * FROM t1 ORDER BY a; 93 94SELECT * FROM t2 ORDER BY a; 95 96DROP TABLE t1,t2; 97 98--echo # 99--echo # Bug#40992 - InnoDB: Crash when engine_condition_pushdown is on 100--echo # 101 102CREATE TABLE t ( 103 dummy INT PRIMARY KEY, 104 a INT UNIQUE, 105 b INT 106); 107 108INSERT INTO t VALUES (1,1,1),(3,3,3),(5,5,5); 109 110SELECT * FROM t WHERE a > 2 FOR UPDATE; 111 112DROP TABLE t; 113 114--echo # 115--echo # Bug#35080 - Innodb crash at mem_block_get_len line 72 116--echo # 117 118CREATE TABLE t1 ( 119 t1_autoinc INT(11) NOT NULL AUTO_INCREMENT, 120 uuid VARCHAR(36) DEFAULT NULL, 121 PRIMARY KEY (t1_autoinc), 122 KEY k (uuid) 123); 124 125CREATE TABLE t2 ( 126 t2_autoinc INT(11) NOT NULL AUTO_INCREMENT, 127 uuid VARCHAR(36) DEFAULT NULL, 128 date DATETIME DEFAULT NULL, 129 PRIMARY KEY (t2_autoinc), 130 KEY k (uuid) 131); 132 133CREATE VIEW v1 AS 134 SELECT t1_autoinc, uuid 135 FROM t1 136 WHERE (ISNULL(uuid) OR (uuid like '%-%')); 137 138CREATE VIEW v2 AS 139 SELECT t2_autoinc, uuid, date 140 FROM t2 141 WHERE (ISNULL(uuid) OR (LENGTH(uuid) = 36)); 142 143CREATE PROCEDURE delete_multi (IN uuid CHAR(36)) 144 DELETE v1, v2 FROM v1 INNER JOIN v2 145 ON v1.uuid = v2.uuid 146 WHERE v1.uuid = @uuid; 147 148SET @uuid = UUID(); 149 150INSERT INTO v1 (uuid) VALUES (@uuid); 151INSERT INTO v2 (uuid, date) VALUES (@uuid, '2009-09-09'); 152 153CALL delete_multi(@uuid); 154 155DROP procedure delete_multi; 156DROP table t1,t2; 157DROP view v1,v2; 158 159--echo # 160--echo # Bug#41996 - multi-table delete crashes server (InnoDB table) 161--echo # 162 163CREATE TABLE t1 ( 164 b BIGINT, 165 i INT, 166 KEY (b) 167); 168 169INSERT INTO t1 VALUES (2, 2); 170 171DELETE t1 FROM t1 a, t1 WHERE a.i=t1.b; 172 173DROP TABLE t1; 174 175--echo # 176--echo # Bug#43448 - Server crashes on multi table delete with Innodb 177--echo # 178 179CREATE TABLE t1 ( 180 id1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 181 t CHAR(12) 182); 183 184CREATE TABLE t2 ( 185 id2 INT NOT NULL, 186 t CHAR(12) 187); 188 189CREATE TABLE t3( 190 id3 INT NOT NULL, 191 t CHAR(12), 192 INDEX(id3) 193); 194 195delimiter |; 196 197CREATE PROCEDURE insert_data () 198BEGIN 199 DECLARE i1 INT DEFAULT 20; 200 DECLARE i2 INT; 201 DECLARE i3 INT; 202 203 WHILE (i1 > 0) DO 204 INSERT INTO t1(t) VALUES (i1); 205 SET i2 = 2; 206 WHILE (i2 > 0) DO 207 INSERT INTO t2(id2, t) VALUES (i1, i2); 208 SET i3 = 2; 209 WHILE (i3 > 0) DO 210 INSERT INTO t3(id3, t) VALUES (i1, i2); 211 SET i3 = i3 -1; 212 END WHILE; 213 SET i2 = i2 -1; 214 END WHILE; 215 SET i1 = i1 - 1; 216 END WHILE; 217END | 218 219delimiter ;| 220 221CALL insert_data(); 222 223SELECT COUNT(*) FROM t1 WHERE id1 > 10; 224SELECT COUNT(*) FROM t2 WHERE id2 > 10; 225SELECT COUNT(*) FROM t3 WHERE id3 > 10; 226 227DELETE t1, t2, t3 228FROM t1, t2, t3 229WHERE t1.id1 = t2.id2 AND t2.id2 = t3.id3 AND t1.id1 > 3; 230 231SELECT COUNT(*) FROM t1; 232SELECT COUNT(*) FROM t2; 233SELECT COUNT(*) FROM t3; 234 235DROP PROCEDURE insert_data; 236DROP TABLE t1, t2, t3; 237 238--echo # 239--echo # Bug#42580 - Innodb's ORDER BY ..LIMIT returns no rows for 240--echo # null-safe operator <=> NULL 241--echo # 242 243CREATE TABLE t1( 244 c1 DATE NOT NULL, 245 c2 DATE NULL, 246 c3 DATETIME, 247 c4 TIMESTAMP, 248 PRIMARY KEY(c1), 249 UNIQUE(c2) 250); 251 252--echo 253INSERT INTO t1 VALUES('0000-00-00', '0000-00-00', '2008-01-04', '2008-01-05'); 254INSERT INTO t1 VALUES('2007-05-25', '2007-05-25', '2007-05-26', '2007-05-26'); 255INSERT INTO t1 VALUES('2008-01-01', NULL , '2008-01-02', '2008-01-03'); 256INSERT INTO t1 VALUES('2008-01-17', NULL , NULL , '2009-01-29'); 257INSERT INTO t1 VALUES('2009-01-29', '2009-01-29', '2009-01-29', '2009-01-29'); 258 259--echo 260SELECT * FROM t1 WHERE c2 <=> NULL ORDER BY c1,c2; 261--echo 262SELECT * FROM t1 WHERE c2 <=> NULL ORDER BY c1,c2 LIMIT 2; 263 264--echo 265DROP TABLE t1; 266 267--echo # 268--echo # Bug#43617 - Innodb returns wrong results with timestamp's range value 269--echo # in IN clause 270--echo # (Note: Fixed by patch for BUG#42580) 271--echo # 272 273CREATE TABLE t1( 274 c1 TIMESTAMP NOT NULL, 275 c2 TIMESTAMP NULL, 276 c3 DATE, 277 c4 DATETIME, 278 PRIMARY KEY(c1), 279 UNIQUE INDEX(c2) 280); 281 282INSERT INTO t1 VALUES 283 ('0000-00-00 00:00:00','0000-00-00 00:00:00','2008-01-04','2008-01-05 00:00:00'), 284 ('1971-01-01 00:00:01','1980-01-01 00:00:01','2009-01-01','2009-01-02 00:00:00'), 285 ('1999-01-01 00:00:00','1999-01-01 00:00:00', NULL, NULL), 286 ('2007-05-23 09:15:28','2007-05-23 09:15:28','2007-05-24','2007-05-24 09:15:28'), 287 ('2007-05-27 00:00:00','2007-05-25 00:00:00','2007-05-26','2007-05-26 00:00:00'), 288 ('2008-01-01 00:00:00', NULL, '2008-01-02','2008-01-03 00:00:00'), 289 ('2009-01-29 11:11:27','2009-01-29 11:11:27','2009-01-29','2009-01-29 11:11:27'), 290 ('2038-01-09 03:14:07','2038-01-09 03:14:07','2009-01-05','2009-01-06 00:00:00'); 291 292--echo 293SELECT * 294FROM t1 295WHERE c2 IN ('1971-01-01 00:00:01','2038-01-09 03:14:07') 296ORDER BY c2; 297 298--echo 299SELECT * 300FROM t1 301WHERE c2 IN ('1971-01-01 00:00:01','2038-01-09 03:14:07') 302ORDER BY c2 LIMIT 2; 303 304--echo 305SELECT * 306FROM t1 307WHERE c2 IN ('1971-01-01 00:00:01','2038-01-09 03:14:07') 308ORDER BY c2 DESC; 309 310--echo 311SELECT * 312FROM t1 313WHERE c2 IN ('1971-01-01 00:00:01','2038-01-09 03:14:07') 314ORDER BY c2 DESC LIMIT 2; 315 316--echo 317DROP TABLE t1; 318 319--echo # 320--echo # Bug#43249 - Innodb returns zero time for the time column 321--echo # with <=> NULL order by limit 322--echo # (Note: Fixed by patch for BUG#42580) 323--echo # 324 325CREATE TABLE t1( 326 c1 TIME NOT NULL, 327 c2 TIME NULL, 328 c3 DATE, 329 PRIMARY KEY(c1), 330 UNIQUE INDEX(c2) 331); 332 333INSERT INTO t1 VALUES('8:29:45',NULL,'2009-02-01'); 334 335--echo 336# First time, good results: 337SELECT * FROM t1 WHERE c2 <=> NULL ORDER BY c2 LIMIT 2; 338 339--echo 340# Second time, bad results: 341SELECT * FROM t1 WHERE c2 <=> NULL ORDER BY c2 LIMIT 2; 342 343DROP TABLE t1; 344 345--echo # 346--echo # BUG#43618: MyISAM&Maria returns wrong results with 'between' 347--echo # on timestamp 348--echo # 349 350CREATE TABLE t1( 351 ts TIMESTAMP NOT NULL, 352 c char NULL, 353 PRIMARY KEY(ts) 354); 355 356INSERT INTO t1 VALUES 357 ('1971-01-01','a'), 358 ('2007-05-25','b'), 359 ('2008-01-01','c'), 360 ('2038-01-09','d'); 361 362-- disable_query_log 363-- disable_result_log 364ANALYZE TABLE t1; 365-- enable_result_log 366-- enable_query_log 367 368--disable_warnings 369 370--echo 371--echo # Execute select with invalid timestamp, desc ordering 372SELECT * 373FROM t1 374WHERE ts BETWEEN '0000-00-00' AND '2010-00-01 00:00:00' 375ORDER BY ts DESC 376LIMIT 2; 377 378--echo 379--echo # Should use index condition 380EXPLAIN 381SELECT * 382FROM t1 383WHERE ts BETWEEN '0000-00-00' AND '2010-00-01 00:00:00' 384ORDER BY ts DESC 385LIMIT 2; 386--echo 387 388--enable_warnings 389 390DROP TABLE t1; 391 392--echo # 393--echo # BUG#49906: Assertion failed - Field_varstring::val_str in field.cc 394--echo # 395 396CREATE TABLE t1 ( 397 f1 VARCHAR(1024), 398 f2 VARCHAR(10), 399 INDEX test_idx USING BTREE (f2,f1(5)) 400); 401 402INSERT INTO t1 VALUES ('a','c'), ('b','d'); 403 404SELECT f1 405FROM t1 406WHERE f2 LIKE 'd' 407ORDER BY f1; 408 409DROP TABLE t1; 410 411--echo # 412--echo # Bug#52660 - "Perf. regr. using ICP for MyISAM on range queries on 413--echo # an index containing TEXT" 414--echo # 415 416CREATE TABLE t1 (a INT); 417INSERT INTO t1 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 418 419CREATE TABLE t2 (a INT); 420INSERT INTO t2 SELECT A.a + 10*(B.a) FROM t1 A, t1 B; 421 422CREATE TABLE t3 ( 423 c1 TINYTEXT NOT NULL, 424 i1 INT NOT NULL, 425 KEY (c1(6),i1) 426); 427 428-- disable_query_log 429-- disable_result_log 430ANALYZE TABLE t1; 431ANALYZE TABLE t2; 432ANALYZE TABLE t3; 433-- enable_result_log 434-- enable_query_log 435 436INSERT INTO t3 SELECT CONCAT('c-',1000+t2.a,'=w'), 1 FROM t2; 437 438let query=SELECT c1 FROM t3 WHERE c1 >= 'c-1004=w' and c1 <= 'c-1006=w' and i1 > 2; 439 440eval EXPLAIN $query; 441eval $query; 442 443DROP TABLE t1, t2, t3; 444 445--echo # 446--echo # Bug#57372 "Multi-table updates and deletes fail when running with ICP 447--echo # against InnoDB" 448--echo # 449 450CREATE TABLE t1 ( 451 a INT KEY, 452 b INT 453) ENGINE = INNODB; 454 455CREATE TABLE t2 ( 456 a INT KEY, 457 b INT 458) ENGINE = INNODB; 459 460INSERT INTO t1 VALUES (1, 101), (2, 102), (3, 103), (4, 104), (5, 105); 461INSERT INTO t2 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5); 462 463UPDATE t1, t2 464SET t1.a = t1.a + 100, t2.b = t1.a + 10 465WHERE t1.a BETWEEN 2 AND 4 AND t2.a = t1.b - 100; 466 467--sorted_result 468SELECT * FROM t1; 469--sorted_result 470SELECT * FROM t2; 471 472DROP TABLE t1, t2; 473 474--echo # 475--echo # Bug#52605 - "Adding LIMIT 1 clause to query with complex range 476--echo # predicate causes wrong results" 477--echo # 478 479CREATE TABLE t1 ( 480 pk INT NOT NULL, 481 c1 INT, 482 PRIMARY KEY (pk), 483 KEY k1 (c1) 484); 485 486INSERT INTO t1 VALUES (1,NULL); 487INSERT INTO t1 VALUES (2,6); 488INSERT INTO t1 VALUES (3,NULL); 489INSERT INTO t1 VALUES (4,6); 490INSERT INTO t1 VALUES (5,NULL); 491INSERT INTO t1 VALUES (6,NULL); 492INSERT INTO t1 VALUES (7,9); 493INSERT INTO t1 VALUES (8,0); 494 495-- disable_query_log 496-- disable_result_log 497ANALYZE TABLE t1; 498-- enable_result_log 499-- enable_query_log 500 501SELECT pk, c1 502FROM t1 503WHERE (pk BETWEEN 4 AND 5 OR pk < 2) AND c1 < 240 504ORDER BY c1 505LIMIT 1; 506 507EXPLAIN SELECT pk, c1 508FROM t1 509WHERE (pk BETWEEN 4 AND 5 OR pk < 2) AND c1 < 240 510ORDER BY c1 511LIMIT 1; 512 513DROP TABLE t1; 514 515--echo # 516--echo # Bug#42991 "invalid memory access and/or crash when using 517--echo # index condition pushdown + InnoDB" 518--echo # 519 520# Note that you need to run with --valgrind to see the warnings 521# about invalid memory accesses. 522 523CREATE TABLE t1 ( 524 c1 TINYTEXT NOT NULL, 525 c2 INT NOT NULL, 526 PRIMARY KEY (c2), 527 KEY id1 (c1(4)) 528); 529 530INSERT INTO t1 VALUES ('Anastasia', 5); 531INSERT INTO t1 VALUES ('Karianne', 4); 532 533-- disable_query_log 534-- disable_result_log 535ANALYZE TABLE t1; 536-- enable_result_log 537-- enable_query_log 538 539SELECT * FROM t1 WHERE (c1 <= '6566-06-15' AND c2 <> 3); 540 541EXPLAIN SELECT * FROM t1 WHERE (c1 <= '6566-06-15' AND c2 <> 3); 542 543DROP TABLE t1; 544 545--echo # 546--echo # Bug#56529 - "Crash due to long semaphore wait in InnoDB 547--echo # with ICP and subqueries" 548--echo # 549 550CREATE TABLE t1 ( 551 col_int_nokey INTEGER, 552 col_int_key INTEGER, 553 col_varchar_key VARCHAR(1), 554 555 KEY (col_int_key), 556 KEY (col_varchar_key, col_int_key) 557) stats_persistent=0; 558 559INSERT INTO t1 VALUES (NULL,2,'w'); 560INSERT INTO t1 VALUES (7,9,'m'); 561INSERT INTO t1 VALUES (9,3,'m'); 562INSERT INTO t1 VALUES (7,9,'k'); 563INSERT INTO t1 VALUES (4,NULL,'r'); 564INSERT INTO t1 VALUES (2,9,'t'); 565INSERT INTO t1 VALUES (6,3,'j'); 566INSERT INTO t1 VALUES (8,8,'u'); 567INSERT INTO t1 VALUES (NULL,8,'h'); 568INSERT INTO t1 VALUES (5,53,'o'); 569INSERT INTO t1 VALUES (NULL,0,NULL); 570INSERT INTO t1 VALUES (6,5,'k'); 571INSERT INTO t1 VALUES (188,166,'e'); 572INSERT INTO t1 VALUES (2,3,'n'); 573INSERT INTO t1 VALUES (1,0,'t'); 574INSERT INTO t1 VALUES (1,1,'c'); 575INSERT INTO t1 VALUES (0,9,'m'); 576INSERT INTO t1 VALUES (9,5,'y'); 577INSERT INTO t1 VALUES (NULL,6,'f'); 578 579CREATE TABLE t2 ( 580 c1 INTEGER NOT NULL 581) stats_persistent=0; 582 583let query= 584SELECT table1.col_int_nokey 585FROM t1 AS table1 STRAIGHT_JOIN ( 586 t1 AS table2 INNER JOIN t1 AS table3 587 ON table3.col_varchar_key = table2.col_varchar_key) 588 ON table3.col_int_nokey = table1.col_int_key 589WHERE table3.col_int_key != SOME ( SELECT c1 FROM t2 ); 590 591eval EXPLAIN $query; 592eval $query; 593 594DROP TABLE t1, t2; 595 596--echo # 597--echo # Bug#58243 "RQG test optimizer_subquery causes server crash 598--echo # when running with ICP" 599--echo # 600 601# Test case 1: This test case makes item->const_item() return true 602# in uses_index_fields_only() for an item tree 603# containing a subquery. This triggered the subquery 604# to be pushed down to InnoDB. 605 606CREATE TABLE t1 ( 607 pk INTEGER NOT NULL, 608 c1 INTEGER NOT NULL, 609 c2 INTEGER NOT NULL, 610 611 PRIMARY KEY (pk) 612); 613 614INSERT INTO t1 VALUES (1,6,7); 615 616CREATE TABLE t2 ( 617 c1 INTEGER NOT NULL 618); 619 620-- disable_query_log 621-- disable_result_log 622ANALYZE TABLE t1; 623-- enable_result_log 624-- enable_query_log 625 626let query= 627SELECT t1.c1 628FROM t1 629WHERE t1.pk < 317 AND 2 IN (SELECT COUNT(t2.c1) 630 FROM t2) 631ORDER BY t1.c2; 632 633eval EXPLAIN $query; 634eval $query; 635 636DROP TABLE t1, t2; 637 638# Test case 2: This test case makes item->used_tables() return 0 639# in uses_index_fields_only() for an item tree 640# containg a subquery. This triggered the subquery 641# to be pushed down to InnoDB. 642 643CREATE TABLE t1 ( 644 i1 INTEGER NOT NULL, 645 c1 VARCHAR(1) NOT NULL 646); 647 648INSERT INTO t1 VALUES (2,'w'); 649 650CREATE TABLE t2 ( 651 i1 INTEGER NOT NULL, 652 c1 VARCHAR(1) NOT NULL, 653 c2 VARCHAR(1) NOT NULL, 654 KEY (c1, i1) 655); 656 657INSERT INTO t2 VALUES (8,'d','d'); 658INSERT INTO t2 VALUES (4,'v','v'); 659 660CREATE TABLE t3 ( 661 c1 VARCHAR(1) NOT NULL 662); 663 664INSERT INTO t3 VALUES ('v'); 665 666-- disable_query_log 667-- disable_result_log 668ANALYZE TABLE t1; 669ANALYZE TABLE t2; 670ANALYZE TABLE t3; 671-- enable_result_log 672-- enable_query_log 673 674let query= 675SELECT i1 676FROM t1 677WHERE EXISTS (SELECT t2.c1 678 FROM (t2 INNER JOIN t3 ON (t3.c1 = t2.c1)) 679 WHERE t2.c2 != t1.c1 AND t2.c2 = (SELECT MIN(t3.c1) 680 FROM t3)); 681 682eval EXPLAIN $query; 683eval $query; 684 685DROP TABLE t1,t2,t3; 686 687--echo # 688--echo # Bug#58015 "Assert in row_sel_field_store_in_mysql_format 689--echo # when running innodb_mrr_icp test" 690--echo # 691 692create table t1 (a char(2) charset utf8,b double, primary key (a(1)),key (b)); 693insert into t1 values ('',1); 694select 1 from t1 where b <= 1 and a <> ''; 695drop table t1; 696 697--echo # 698--echo # Bug#59259 "Incorrect rows returned for a correlated subquery 699--echo # when ICP is on" 700--echo # 701 702CREATE TABLE t1 (pk INTEGER PRIMARY KEY, i INTEGER NOT NULL) ENGINE=InnoDB; 703 704INSERT INTO t1 VALUES (11,0); 705INSERT INTO t1 VALUES (12,5); 706INSERT INTO t1 VALUES (15,0); 707 708CREATE TABLE t2 (pk INTEGER PRIMARY KEY, i INTEGER NOT NULL) ENGINE=InnoDB; 709 710INSERT INTO t2 VALUES (11,1); 711INSERT INTO t2 VALUES (12,2); 712INSERT INTO t2 VALUES (15,4); 713 714SELECT * FROM t1 715WHERE pk IN (SELECT it.pk FROM t2 JOIN t2 AS it ON it.i=it.i WHERE t1.i); 716 717DROP TABLE t1, t2; 718 719--echo # 720--echo # Bug #58816 "Extra temporary duplicate rows in result set when 721--echo # switching ICP off" 722--echo # 723 724# Save optimizer switch setting 725set @save_optimizer_switch_bug58816= @@optimizer_switch; 726 727CREATE TABLE t1 ( 728 pk INT NOT NULL, 729 c1 INT NOT NULL, 730 PRIMARY KEY (pk) 731); 732 733INSERT INTO t1 VALUES (1,9),(2,7),(3,6),(4,3),(5,1); 734 735-- disable_query_log 736-- disable_result_log 737ANALYZE TABLE t1; 738-- enable_result_log 739-- enable_query_log 740 741EXPLAIN SELECT pk, c1 FROM t1 WHERE pk <> 3; 742 743SET SESSION optimizer_switch='index_condition_pushdown=off'; 744 745SELECT pk, c1 FROM t1 WHERE pk <> 3; 746 747DROP TABLE t1; 748 749# Restore optimzer switch setting 750set optimizer_switch= @save_optimizer_switch_bug58816; 751 752--echo # 753--echo # Bug#58837: ICP crash or valgrind error due to uninitialized 754--echo # value in innobase_index_cond 755--echo # 756 757CREATE TABLE t1 ( 758 t1_int INT, 759 t1_time TIME 760); 761 762CREATE TABLE t2 ( 763 t2_int int PRIMARY KEY, 764 t2_int2 INT 765); 766 767--disable_warnings 768INSERT INTO t2 VALUES (); 769INSERT INTO t1 VALUES (); 770--enable_warnings 771 772-- disable_query_log 773-- disable_result_log 774ANALYZE TABLE t1; 775ANALYZE TABLE t2; 776-- enable_result_log 777-- enable_query_log 778 779let $query= 780SELECT * 781FROM t1 AS t1a 782WHERE NOT EXISTS 783 (SELECT * 784 FROM t1 AS t1b 785 WHERE t1b.t1_int NOT IN 786 (SELECT t2.t2_int 787 FROM t2 788 WHERE t1b.t1_time LIKE t1b.t1_int 789 OR t1b.t1_time <> t2.t2_int2 790 AND 6=7 791 ) 792) 793; 794 795--echo 796--eval $query; 797--echo 798--eval EXPLAIN $query; 799--echo 800 801DROP TABLE t1,t2; 802 803--echo # 804--echo # Bug#59186 Wrong results of join when ICP is enabled 805--echo # 806 807CREATE TABLE t1 ( 808 pk INTEGER NOT NULL, 809 c1 VARCHAR(3) NOT NULL, 810 PRIMARY KEY (pk) 811); 812 813INSERT INTO t1 VALUES (1,'y'),(0,'or'); 814 815CREATE TABLE t2 ( 816 pk INTEGER NOT NULL, 817 c1 VARCHAR(3) NOT NULL, 818 c2 VARCHAR(6) NOT NULL, 819 PRIMARY KEY (pk) 820); 821 822INSERT INTO t2 VALUES (6,'y','RPOYT'),(10,'m','JINQE'); 823 824-- disable_query_log 825-- disable_result_log 826ANALYZE TABLE t1; 827ANALYZE TABLE t2; 828-- enable_result_log 829-- enable_query_log 830 831let query= 832SELECT c2 FROM t1 JOIN t2 ON t1.c1 = t2.c1 833WHERE (t2.pk <= 4 AND t1.pk IN (2,1)) OR 834 (t1.pk > 1 AND t2.pk BETWEEN 6 AND 6); 835 836eval EXPLAIN $query; 837eval $query; 838 839DROP TABLE t1, t2; 840 841--echo # 842--echo # Bug#58838 "Wrong results with HAVING + LIMIT without GROUP BY when 843--echo # ICP is enabled" 844--echo # 845 846CREATE TABLE t1 ( 847 pk INT NOT NULL, 848 c1 INT, 849 PRIMARY KEY (pk), 850 KEY col_int_key (c1) 851); 852 853INSERT INTO t1 VALUES (1,37),(2,8),(3,-25),(4,NULL),(5,55); 854 855SELECT pk FROM t1 WHERE c1 <> 1 HAVING pk = 3 ORDER BY pk LIMIT 0; 856SELECT pk FROM t1 WHERE c1 <> 1 HAVING pk = 3 ORDER BY pk LIMIT 1; 857SELECT pk FROM t1 WHERE c1 <> 1 HAVING pk = 3 ORDER BY pk LIMIT 2; 858SELECT pk FROM t1 WHERE c1 <> 1 HAVING pk = 3 ORDER BY pk LIMIT 5; 859 860DROP TABLE t1; 861 862--echo # 863--echo # Bug#59483 "Crash on INSERT/REPLACE in 864--echo # rec_convert_dtuple_to_rec_comp with ICP on" 865--echo # 866 867CREATE TABLE t1 ( 868 pk INTEGER AUTO_INCREMENT PRIMARY KEY, 869 i1 INTEGER, 870 c1 CHAR(6), 871 i2 INTEGER NOT NULL, 872 KEY (i2) 873); 874 875INSERT INTO t1 VALUES 876 (NULL, 4, 'that', 8), 877 (NULL, 1, 'she', 6), 878 (NULL, 6, 'tell', 2); 879 880SELECT * FROM t1 WHERE i2 IN (3, 6) LIMIT 2 FOR UPDATE; 881INSERT INTO t1 (i2) VALUES (1); 882 883DROP TABLE t1; 884 885--echo # 886--echo # Bug #11766678 - 59843: 887--echo # USING UNINITIALISED VALUE IN USES_INDEX_FIELDS_ONLY 888--echo # 889 890CREATE TABLE t1 ( 891 col999 FLOAT NOT NULL, 892 COL1000 VARBINARY(179) NOT NULL, 893 col1003 DATE DEFAULT NULL, 894 KEY idx4267 (col1000, col1003) 895); 896 897INSERT INTO t1 VALUES (),(); 898SELECT col999 FROM t1 WHERE col1000 = "3" AND col1003 <=> sysdate(); 899 900DROP TABLE t1; 901 902--echo # 903--echo # Bug#11873324 "WRONG RESULT WITH ICP AND STRAIGHT_JOIN" 904--echo # 905 906CREATE TABLE t1 ( 907 pk INTEGER NOT NULL, 908 i1 INTEGER, 909 PRIMARY KEY (pk), 910 KEY col_int_key (i1) 911); 912 913INSERT INTO t1 VALUES (14,NULL), (18,133); 914 915CREATE TABLE t2 ( 916 pk INTEGER NOT NULL, 917 i1 INTEGER, 918 c1 VARCHAR(1), 919 PRIMARY KEY (pk), 920 KEY col_int_key (i1) 921); 922 923INSERT INTO t2 VALUES (1,7,'f'); 924 925if (`SELECT UPPER(@@default_storage_engine) = 'INNODB'`) 926{ 927-- disable_query_log 928-- disable_result_log 929ANALYZE TABLE t1; 930ANALYZE TABLE t2; 931-- enable_result_log 932-- enable_query_log 933} 934 935# Bug was specific of IN->EXISTS: 936set @old_opt_switch=@@optimizer_switch; 937--disable_query_log 938if (`select locate('materialization', @@optimizer_switch) > 0`) 939{ 940 set optimizer_switch='materialization=off'; 941} 942--enable_query_log 943 944let query= 945SELECT t1.i1 946FROM t1 947WHERE t1.i1 NOT IN 948( SELECT STRAIGHT_JOIN SUBQUERY_t1.pk 949 FROM t1 AS SUBQUERY_t1 950 JOIN t2 AS SUBQUERY_t2 951 ON SUBQUERY_t2.i1 = SUBQUERY_t1.pk 952 WHERE SUBQUERY_t1.i1 > 0 953 OR SUBQUERY_t2.c1 = 'a' 954); 955 956eval EXPLAIN $query; 957eval $query; 958 959set @@optimizer_switch=@old_opt_switch; 960DROP TABLE t1,t2; 961 962--echo # 963--echo # Bug#11876420 "MISSING ROW IN RESULT WITH SUBQUERY + IN + XOR + 964--echo # NULL VALUES AND ICP ENABLED" 965--echo # 966 967CREATE TABLE t1 ( 968 i1 INTEGER, 969 c1 VARCHAR(1), 970 KEY col_varchar_key (c1) 971); 972 973INSERT INTO t1 VALUES (1,'j'), (0,'e'), (210,'f'), (8,'v'), (7,'x'), 974 (5,'m'), (NULL,'c'); 975 976CREATE TABLE t2 ( 977 i1 INTEGER, 978 c1 VARCHAR(1), 979 KEY col_varchar_key (c1) 980); 981 982INSERT INTO t2 VALUES (8,NULL); 983 984CREATE TABLE t3 ( 985 i1 INTEGER, 986 c1 VARCHAR(1), 987 KEY col_varchar_key (c1) 988) ENGINE=InnoDB; 989 990INSERT INTO t3 VALUES (NULL,'w'), (1,NULL), (2,'d'); 991 992-- disable_query_log 993-- disable_result_log 994ANALYZE TABLE t1; 995ANALYZE TABLE t2; 996ANALYZE TABLE t3; 997-- enable_result_log 998-- enable_query_log 999 1000# Bug was specific of IN->EXISTS: 1001set @old_opt_switch=@@optimizer_switch; 1002--disable_query_log 1003if (`select locate('materialization', @@optimizer_switch) > 0`) 1004{ 1005 set optimizer_switch='materialization=off'; 1006} 1007--enable_query_log 1008 1009let query= 1010SELECT i1 1011FROM t3 1012WHERE c1 IN 1013 ( SELECT t1.c1 1014 FROM t2 JOIN t1 1015 ON t2.i1 >= t1.i1 1016 WHERE t1.c1 > t2.c1 1017 ) 1018 XOR i1; 1019 1020eval EXPLAIN $query; 1021eval $query; 1022 1023set @@optimizer_switch=@old_opt_switch; 1024DROP TABLE t1, t2, t3; 1025 1026--echo # 1027--echo # Bug#12355958 "FAILING ASSERTION: TRX->LOCK.N_ACTIVE_THRS == 1" 1028--echo # 1029 1030CREATE TABLE t1 ( 1031 pk INTEGER PRIMARY KEY, 1032 a INTEGER NOT NULL, 1033 b CHAR(1), 1034 KEY(b) 1035); 1036 1037INSERT INTO t1 VALUES (23,5,'d'); 1038 1039-- disable_query_log 1040-- disable_result_log 1041ANALYZE TABLE t1; 1042-- enable_result_log 1043-- enable_query_log 1044 1045let query= 1046SELECT a1.pk 1047FROM t1 AS a1 JOIN (SELECT * FROM t1 LIMIT 1) AS a2 ON a2.b = a1.b 1048WHERE a1.a = (SELECT pk FROM t1 LIMIT 1) 1049 AND (a1.a != a2.a OR a1.b IS NULL); 1050 1051eval EXPLAIN $query; 1052eval $query; 1053 1054# Re-run the same query using a view. This will test the code with 1055# an Item_ref object in the condition tree (note: in order for this 1056# to trigger the bug the patch for Bug#59696 needs to be applied first). 1057 1058CREATE VIEW v1 AS SELECT * FROM t1; 1059 1060let query= 1061SELECT a1.pk 1062FROM v1 AS a1 JOIN (SELECT * FROM v1 LIMIT 1) AS a2 ON a2.b = a1.b 1063WHERE a1.a = (SELECT pk FROM v1 LIMIT 1) 1064 AND (a1.a != a2.a OR a1.b IS NULL); 1065 1066eval EXPLAIN $query; 1067eval $query; 1068 1069DROP VIEW v1; 1070DROP TABLE t1; 1071 1072--echo # 1073--echo # BUG#12601961 "SEGFAULT IN HANDLER::COMPARE_KEY2" 1074--echo # BUG#12724899 "SELECT STRAIGHT_JOIN QUERY GIVES 2 DATES VERSUS 1075--echo # 2 WARNINGS WITH ICP ON" 1076--echo # 1077 1078CREATE TABLE t1 ( 1079 pk INTEGER NOT NULL, 1080 i1 INTEGER NOT NULL, 1081 c1 VARCHAR(1) NOT NULL, 1082 PRIMARY KEY (pk) 1083); 1084 1085INSERT INTO t1 VALUES (1,3,'j'), (20,8,'e'); 1086 1087-- disable_query_log 1088-- disable_result_log 1089ANALYZE TABLE t1; 1090-- enable_result_log 1091-- enable_query_log 1092 1093let query= 1094SELECT alias2.i1 1095FROM t1 AS alias1 STRAIGHT_JOIN t1 AS alias2 1096ON alias2.pk AND alias2.pk <= alias1.c1 1097WHERE alias2.pk = 1; 1098 1099eval EXPLAIN $query; 1100eval $query; 1101 1102DROP TABLE t1; 1103 1104--echo # 1105--echo # BUG#12822678 - 2 MORE ROWS WHEN ICP=ON W/ STRAIGHT_JOIN 1106--echo # 1107 1108CREATE TABLE t1 ( 1109 i1 INTEGER NOT NULL, 1110 d1 DOUBLE, 1111 KEY k1 (d1) 1112); 1113 1114INSERT INTO t1 VALUES (10,1), (17,NULL), (22,NULL); 1115 1116CREATE TABLE t2 ( 1117 pk INTEGER NOT NULL, 1118 i1 INTEGER NOT NULL, 1119 PRIMARY KEY (pk) 1120); 1121 1122INSERT INTO t2 VALUES (4,1); 1123 1124-- disable_query_log 1125-- disable_result_log 1126ANALYZE TABLE t1; 1127ANALYZE TABLE t2; 1128-- enable_result_log 1129-- enable_query_log 1130 1131let query= 1132SELECT t1.d1, t2.pk, t2.i1 1133FROM t1 STRAIGHT_JOIN t2 ON t2.i1 1134WHERE t2.pk <> t1.d1 AND t2.pk = 4; 1135 1136eval EXPLAIN $query; 1137eval $query; 1138 1139DROP TABLE t1, t2; 1140 1141--echo # 1142--echo # BUG#12838420 "DUPLICATE VALUES FOR GROUP-BY COLUMN WHEN JOIN 1143--echo # BUFFERING IS OFF" 1144--echo # 1145CREATE TABLE t1 ( 1146 col_int_key INT, 1147 pk INT, 1148 PRIMARY KEY (pk), 1149 KEY (col_int_key) 1150); 1151INSERT INTO t1 VALUES (2,3),(3,2),(3,5),(4,6); 1152 1153CREATE TABLE t2 ( 1154 col_int_key INT, 1155 pk INT, 1156 PRIMARY KEY (pk), 1157 KEY (col_int_key) 1158); 1159INSERT INTO t2 VALUES (0,9),(3,10),(4,6),(6,1),(100,3),(200,5); 1160 1161-- disable_query_log 1162-- disable_result_log 1163ANALYZE TABLE t1; 1164ANALYZE TABLE t2; 1165-- enable_result_log 1166-- enable_query_log 1167 1168let $query= 1169SELECT t2.col_int_key AS field1 1170FROM t2 USE INDEX(col_int_key) STRAIGHT_JOIN t1 ON t2.col_int_key 1171WHERE t2.pk < 7 AND t2.col_int_key <> 7 1172GROUP BY field1; 1173 1174set @old_opt_switch=@@optimizer_switch; 1175# With BNL, a temp table is used for GROUP BY, we don't want this 1176SET optimizer_switch="block_nested_loop=off"; 1177eval EXPLAIN $query; 1178eval $query; 1179 1180SET @@optimizer_switch=@old_opt_switch; 1181DROP TABLE t1,t2; 1182 1183--echo # 1184--echo # Bug#12976163 "CRASH IN INDEX CONDITION PUSHDOWN CODE AGAINST 1185--echo # A MYISAM TABLE" 1186--echo # 1187 1188CREATE TABLE t1 ( 1189 i1 INTEGER NOT NULL, 1190 i2 INTEGER NOT NULL 1191); 1192 1193INSERT INTO t1 VALUES (14,1), (15,2), (16,3); 1194 1195CREATE TABLE t2 ( 1196 i1 INTEGER NOT NULL, 1197 i2 INTEGER NOT NULL, 1198 c1 TINYTEXT 1199); 1200 1201INSERT INTO t2 1202SELECT i1, 10 * i2, "MySQL" FROM t1; 1203 1204DELIMITER $$; 1205 1206CREATE PROCEDURE proc1(id INTEGER) 1207BEGIN 1208 1209SELECT i2 1210 FROM ( 1211 (SELECT i1, i2, NULL AS a1 FROM t1) 1212 UNION 1213 (SELECT i1, i2, c1 AS a1 FROM t2) 1214 ) u1 1215WHERE i1 = id; 1216 1217END$$ 1218 1219DELIMITER ;$$ 1220 1221CALL proc1(15); 1222 1223DROP PROCEDURE proc1; 1224 1225DROP TABLE t1, t2; 1226 1227--echo # 1228--echo # Bug#13655397 "CRASH IN SYNC_THREAD_LEVELS_NONEMPTY_TRX" 1229--echo # 1230 1231CREATE TABLE t1 ( 1232 i1 INTEGER NOT NULL, 1233 i2 INTEGER NOT NULL, 1234 KEY (i1) 1235); 1236 1237INSERT INTO t1 VALUES (4,4), (5,5); 1238 1239CREATE TABLE t2 ( 1240 pk INTEGER NOT NULL, 1241 PRIMARY KEY (pk) 1242); 1243 1244INSERT INTO t2 VALUES (1); 1245 1246-- disable_query_log 1247-- disable_result_log 1248ANALYZE TABLE t1; 1249ANALYZE TABLE t2; 1250-- enable_result_log 1251-- enable_query_log 1252 1253CREATE FUNCTION f1() RETURNS INTEGER 1254RETURN (SELECT MOD(COUNT(DISTINCT pk), 10) FROM t2); 1255 1256let query= 1257SELECT i1, i2 FROM t1 WHERE f1() = 1 AND i1 = 5; 1258 1259eval EXPLAIN $query; 1260eval $query; 1261 1262DROP FUNCTION f1; 1263DROP TABLE t1, t2; 1264