1# 2# Bug#36981 - "innodb crash when selecting for update" 3# 4CREATE TABLE t1 ( 5c1 CHAR(1), 6c2 CHAR(10), 7KEY (c1) 8); 9INSERT INTO t1 VALUES ('3', null); 10SELECT * FROM t1 WHERE c1='3' FOR UPDATE; 11c1 c2 123 NULL 13DROP TABLE t1; 14CREATE TABLE t1 (a INT); 15INSERT INTO t1 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 16CREATE TABLE t2 (a INT); 17INSERT INTO t2 SELECT A.a + 10*(B.a + 10*C.a) FROM t1 A, t1 B, t1 C; 18CREATE TABLE t3 ( 19c1 CHAR(10) NOT NULL, 20c2 CHAR(10) NOT NULL, 21c3 CHAR(200) NOT NULL, 22KEY (c1) 23); 24INSERT INTO t3 25SELECT CONCAT('c-',1000+t2.a,'=w'), CONCAT('c-',1000+ t2.a,'=w'), 'filler' 26 FROM t2; 27INSERT INTO t3 28SELECT CONCAT('c-',1000+t2.a,'=w'), CONCAT('c-',2000+t2.a,'=w'), 'filler-1' 29 FROM t2; 30INSERT INTO t3 31SELECT CONCAT('c-',1000+t2.a,'=w'), CONCAT('c-',3000+t2.a,'=w'), 'filler-2' 32 FROM t2; 33SELECT c1,c3 FROM t3 WHERE c1 >= 'c-1994=w' and c1 != 'c-1996=w' FOR UPDATE; 34c1 c3 35c-1994=w filler 36c-1994=w filler-1 37c-1994=w filler-2 38c-1995=w filler 39c-1995=w filler-1 40c-1995=w filler-2 41c-1997=w filler 42c-1997=w filler-1 43c-1997=w filler-2 44c-1998=w filler 45c-1998=w filler-1 46c-1998=w filler-2 47c-1999=w filler 48c-1999=w filler-1 49c-1999=w filler-2 50DROP TABLE t1,t2,t3; 51# 52# Bug#43360 - Server crash with a simple multi-table update 53# 54CREATE TABLE t1 ( 55a CHAR(2) NOT NULL PRIMARY KEY, 56b VARCHAR(20) NOT NULL, 57KEY (b) 58); 59CREATE TABLE t2 ( 60a CHAR(2) NOT NULL PRIMARY KEY, 61b VARCHAR(30) NOT NULL, 62KEY (b) 63); 64INSERT INTO t1 VALUES 65('AB','MySQL AB'), 66('JA','Sun Microsystems'), 67('MS','Microsoft'), 68('IB','IBM- Inc.'), 69('GO','Google Inc.'); 70INSERT INTO t2 VALUES 71('AB','Sweden'), 72('JA','USA'), 73('MS','United States of America'), 74('IB','North America'), 75('GO','South America'); 76UPDATE t1,t2 SET t1.b=UPPER(t1.b) WHERE t1.b LIKE 'United%'; 77SELECT * FROM t1 ORDER BY a; 78a b 79AB MySQL AB 80GO Google Inc. 81IB IBM- Inc. 82JA Sun Microsystems 83MS Microsoft 84SELECT * FROM t2 ORDER BY a; 85a b 86AB Sweden 87GO South America 88IB North America 89JA USA 90MS United States of America 91DROP TABLE t1,t2; 92# 93# Bug#40992 - InnoDB: Crash when engine_condition_pushdown is on 94# 95CREATE TABLE t ( 96dummy INT PRIMARY KEY, 97a INT UNIQUE, 98b INT 99); 100INSERT INTO t VALUES (1,1,1),(3,3,3),(5,5,5); 101SELECT * FROM t WHERE a > 2 FOR UPDATE; 102dummy a b 1033 3 3 1045 5 5 105DROP TABLE t; 106# 107# Bug#35080 - Innodb crash at mem_block_get_len line 72 108# 109CREATE TABLE t1 ( 110t1_autoinc INT(11) NOT NULL AUTO_INCREMENT, 111uuid VARCHAR(36) DEFAULT NULL, 112PRIMARY KEY (t1_autoinc), 113KEY k (uuid) 114); 115CREATE TABLE t2 ( 116t2_autoinc INT(11) NOT NULL AUTO_INCREMENT, 117uuid VARCHAR(36) DEFAULT NULL, 118date DATETIME DEFAULT NULL, 119PRIMARY KEY (t2_autoinc), 120KEY k (uuid) 121); 122CREATE VIEW v1 AS 123SELECT t1_autoinc, uuid 124FROM t1 125WHERE (ISNULL(uuid) OR (uuid like '%-%')); 126CREATE VIEW v2 AS 127SELECT t2_autoinc, uuid, date 128FROM t2 129WHERE (ISNULL(uuid) OR (LENGTH(uuid) = 36)); 130CREATE PROCEDURE delete_multi (IN uuid CHAR(36)) 131DELETE v1, v2 FROM v1 INNER JOIN v2 132ON v1.uuid = v2.uuid 133WHERE v1.uuid = @uuid; 134SET @uuid = UUID(); 135INSERT INTO v1 (uuid) VALUES (@uuid); 136INSERT INTO v2 (uuid, date) VALUES (@uuid, '2009-09-09'); 137CALL delete_multi(@uuid); 138DROP procedure delete_multi; 139DROP table t1,t2; 140DROP view v1,v2; 141# 142# Bug#41996 - multi-table delete crashes server (InnoDB table) 143# 144CREATE TABLE t1 ( 145b BIGINT, 146i INT, 147KEY (b) 148); 149INSERT INTO t1 VALUES (2, 2); 150DELETE t1 FROM t1 a, t1 WHERE a.i=t1.b; 151DROP TABLE t1; 152# 153# Bug#43448 - Server crashes on multi table delete with Innodb 154# 155CREATE TABLE t1 ( 156id1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 157t CHAR(12) 158); 159CREATE TABLE t2 ( 160id2 INT NOT NULL, 161t CHAR(12) 162); 163CREATE TABLE t3( 164id3 INT NOT NULL, 165t CHAR(12), 166INDEX(id3) 167); 168CREATE PROCEDURE insert_data () 169BEGIN 170DECLARE i1 INT DEFAULT 20; 171DECLARE i2 INT; 172DECLARE i3 INT; 173WHILE (i1 > 0) DO 174INSERT INTO t1(t) VALUES (i1); 175SET i2 = 2; 176WHILE (i2 > 0) DO 177INSERT INTO t2(id2, t) VALUES (i1, i2); 178SET i3 = 2; 179WHILE (i3 > 0) DO 180INSERT INTO t3(id3, t) VALUES (i1, i2); 181SET i3 = i3 -1; 182END WHILE; 183SET i2 = i2 -1; 184END WHILE; 185SET i1 = i1 - 1; 186END WHILE; 187END | 188CALL insert_data(); 189SELECT COUNT(*) FROM t1 WHERE id1 > 10; 190COUNT(*) 19110 192SELECT COUNT(*) FROM t2 WHERE id2 > 10; 193COUNT(*) 19420 195SELECT COUNT(*) FROM t3 WHERE id3 > 10; 196COUNT(*) 19740 198DELETE t1, t2, t3 199FROM t1, t2, t3 200WHERE t1.id1 = t2.id2 AND t2.id2 = t3.id3 AND t1.id1 > 3; 201SELECT COUNT(*) FROM t1; 202COUNT(*) 2033 204SELECT COUNT(*) FROM t2; 205COUNT(*) 2066 207SELECT COUNT(*) FROM t3; 208COUNT(*) 20912 210DROP PROCEDURE insert_data; 211DROP TABLE t1, t2, t3; 212# 213# Bug#42580 - Innodb's ORDER BY ..LIMIT returns no rows for 214# null-safe operator <=> NULL 215# 216CREATE TABLE t1( 217c1 DATE NOT NULL, 218c2 DATE NULL, 219c3 DATETIME, 220c4 TIMESTAMP, 221PRIMARY KEY(c1), 222UNIQUE(c2) 223); 224 225INSERT INTO t1 VALUES('0000-00-00', '0000-00-00', '2008-01-04', '2008-01-05'); 226INSERT INTO t1 VALUES('2007-05-25', '2007-05-25', '2007-05-26', '2007-05-26'); 227INSERT INTO t1 VALUES('2008-01-01', NULL , '2008-01-02', '2008-01-03'); 228INSERT INTO t1 VALUES('2008-01-17', NULL , NULL , '2009-01-29'); 229INSERT INTO t1 VALUES('2009-01-29', '2009-01-29', '2009-01-29', '2009-01-29'); 230 231SELECT * FROM t1 WHERE c2 <=> NULL ORDER BY c1,c2; 232c1 c2 c3 c4 2332008-01-01 NULL 2008-01-02 00:00:00 2008-01-03 00:00:00 2342008-01-17 NULL NULL 2009-01-29 00:00:00 235 236SELECT * FROM t1 WHERE c2 <=> NULL ORDER BY c1,c2 LIMIT 2; 237c1 c2 c3 c4 2382008-01-01 NULL 2008-01-02 00:00:00 2008-01-03 00:00:00 2392008-01-17 NULL NULL 2009-01-29 00:00:00 240 241DROP TABLE t1; 242# 243# Bug#43617 - Innodb returns wrong results with timestamp's range value 244# in IN clause 245# (Note: Fixed by patch for BUG#42580) 246# 247CREATE TABLE t1( 248c1 TIMESTAMP NOT NULL, 249c2 TIMESTAMP NULL, 250c3 DATE, 251c4 DATETIME, 252PRIMARY KEY(c1), 253UNIQUE INDEX(c2) 254); 255INSERT 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'); 264 265SELECT * 266FROM t1 267WHERE c2 IN ('1971-01-01 00:00:01','2038-01-09 03:14:07') 268ORDER BY c2; 269c1 c2 c3 c4 2702038-01-09 03:14:07 2038-01-09 03:14:07 2009-01-05 2009-01-06 00:00:00 271 272SELECT * 273FROM t1 274WHERE c2 IN ('1971-01-01 00:00:01','2038-01-09 03:14:07') 275ORDER BY c2 LIMIT 2; 276c1 c2 c3 c4 2772038-01-09 03:14:07 2038-01-09 03:14:07 2009-01-05 2009-01-06 00:00:00 278 279SELECT * 280FROM t1 281WHERE c2 IN ('1971-01-01 00:00:01','2038-01-09 03:14:07') 282ORDER BY c2 DESC; 283c1 c2 c3 c4 2842038-01-09 03:14:07 2038-01-09 03:14:07 2009-01-05 2009-01-06 00:00:00 285 286SELECT * 287FROM t1 288WHERE c2 IN ('1971-01-01 00:00:01','2038-01-09 03:14:07') 289ORDER BY c2 DESC LIMIT 2; 290c1 c2 c3 c4 2912038-01-09 03:14:07 2038-01-09 03:14:07 2009-01-05 2009-01-06 00:00:00 292 293DROP TABLE t1; 294# 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) 298# 299CREATE TABLE t1( 300c1 TIME NOT NULL, 301c2 TIME NULL, 302c3 DATE, 303PRIMARY KEY(c1), 304UNIQUE INDEX(c2) 305); 306INSERT INTO t1 VALUES('8:29:45',NULL,'2009-02-01'); 307 308SELECT * FROM t1 WHERE c2 <=> NULL ORDER BY c2 LIMIT 2; 309c1 c2 c3 31008:29:45 NULL 2009-02-01 311 312SELECT * FROM t1 WHERE c2 <=> NULL ORDER BY c2 LIMIT 2; 313c1 c2 c3 31408:29:45 NULL 2009-02-01 315DROP TABLE t1; 316# 317# BUG#43618: MyISAM&Maria returns wrong results with 'between' 318# on timestamp 319# 320CREATE TABLE t1( 321ts TIMESTAMP NOT NULL, 322c char NULL, 323PRIMARY KEY(ts) 324); 325INSERT INTO t1 VALUES 326('1971-01-01','a'), 327('2007-05-25','b'), 328('2008-01-01','c'), 329('2038-01-09','d'); 330 331# Execute select with invalid timestamp, desc ordering 332SELECT * 333FROM t1 334WHERE ts BETWEEN '0000-00-00' AND '2010-00-01 00:00:00' 335ORDER BY ts DESC 336LIMIT 2; 337ts c 3382008-01-01 00:00:00 c 3392007-05-25 00:00:00 b 340 341# Should use index condition 342EXPLAIN 343SELECT * 344FROM t1 345WHERE ts BETWEEN '0000-00-00' AND '2010-00-01 00:00:00' 346ORDER BY ts DESC 347LIMIT 2; 348id select_type table type possible_keys key key_len ref rows Extra 3491 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 4 Using where 350 351DROP TABLE t1; 352# 353# BUG#49906: Assertion failed - Field_varstring::val_str in field.cc 354# 355CREATE TABLE t1 ( 356f1 VARCHAR(1024), 357f2 VARCHAR(10), 358INDEX test_idx USING BTREE (f2,f1(5)) 359); 360INSERT INTO t1 VALUES ('a','c'), ('b','d'); 361SELECT f1 362FROM t1 363WHERE f2 LIKE 'd' 364ORDER BY f1; 365f1 366b 367DROP TABLE t1; 368# 369# Bug#52660 - "Perf. regr. using ICP for MyISAM on range queries on 370# an index containing TEXT" 371# 372CREATE TABLE t1 (a INT); 373INSERT INTO t1 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 374CREATE TABLE t2 (a INT); 375INSERT INTO t2 SELECT A.a + 10*(B.a) FROM t1 A, t1 B; 376CREATE TABLE t3 ( 377c1 TINYTEXT NOT NULL, 378i1 INT NOT NULL, 379KEY (c1(6),i1) 380); 381INSERT INTO t3 SELECT CONCAT('c-',1000+t2.a,'=w'), 1 FROM t2; 382EXPLAIN SELECT c1 FROM t3 WHERE c1 >= 'c-1004=w' and c1 <= 'c-1006=w' and i1 > 2; 383id select_type table type possible_keys key key_len ref rows Extra 3841 SIMPLE t3 range c1 c1 12 NULL 2 Using where 385SELECT c1 FROM t3 WHERE c1 >= 'c-1004=w' and c1 <= 'c-1006=w' and i1 > 2; 386c1 387DROP TABLE t1, t2, t3; 388# 389# Bug#57372 "Multi-table updates and deletes fail when running with ICP 390# against InnoDB" 391# 392CREATE TABLE t1 ( 393a INT KEY, 394b INT 395) ENGINE = INNODB; 396CREATE TABLE t2 ( 397a INT KEY, 398b INT 399) ENGINE = INNODB; 400INSERT INTO t1 VALUES (1, 101), (2, 102), (3, 103), (4, 104), (5, 105); 401INSERT INTO t2 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5); 402UPDATE t1, t2 403SET t1.a = t1.a + 100, t2.b = t1.a + 10 404WHERE t1.a BETWEEN 2 AND 4 AND t2.a = t1.b - 100; 405SELECT * FROM t1; 406a b 4071 101 408102 102 409103 103 410104 104 4115 105 412SELECT * FROM t2; 413a b 4141 1 4152 12 4163 13 4174 14 4185 5 419DROP TABLE t1, t2; 420# 421# Bug#52605 - "Adding LIMIT 1 clause to query with complex range 422# predicate causes wrong results" 423# 424CREATE TABLE t1 ( 425pk INT NOT NULL, 426c1 INT, 427PRIMARY KEY (pk), 428KEY k1 (c1) 429); 430INSERT INTO t1 VALUES (1,NULL); 431INSERT INTO t1 VALUES (2,6); 432INSERT INTO t1 VALUES (3,NULL); 433INSERT INTO t1 VALUES (4,6); 434INSERT INTO t1 VALUES (5,NULL); 435INSERT INTO t1 VALUES (6,NULL); 436INSERT INTO t1 VALUES (7,9); 437INSERT INTO t1 VALUES (8,0); 438SELECT pk, c1 439FROM t1 440WHERE (pk BETWEEN 4 AND 5 OR pk < 2) AND c1 < 240 441ORDER BY c1 442LIMIT 1; 443pk c1 4444 6 445EXPLAIN SELECT pk, c1 446FROM t1 447WHERE (pk BETWEEN 4 AND 5 OR pk < 2) AND c1 < 240 448ORDER BY c1 449LIMIT 1; 450id select_type table type possible_keys key key_len ref rows Extra 4511 SIMPLE t1 range PRIMARY,k1 k1 5 NULL 4 Using where 452DROP TABLE t1; 453# 454# Bug#42991 "invalid memory access and/or crash when using 455# index condition pushdown + InnoDB" 456# 457CREATE TABLE t1 ( 458c1 TINYTEXT NOT NULL, 459c2 INT NOT NULL, 460PRIMARY KEY (c2), 461KEY id1 (c1(4)) 462); 463INSERT INTO t1 VALUES ('Anastasia', 5); 464INSERT INTO t1 VALUES ('Karianne', 4); 465SELECT * FROM t1 WHERE (c1 <= '6566-06-15' AND c2 <> 3); 466c1 c2 467EXPLAIN SELECT * FROM t1 WHERE (c1 <= '6566-06-15' AND c2 <> 3); 468id select_type table type possible_keys key key_len ref rows Extra 4691 SIMPLE t1 range PRIMARY,id1 id1 6 NULL 1 Using where 470DROP TABLE t1; 471# 472# Bug#56529 - "Crash due to long semaphore wait in InnoDB 473# with ICP and subqueries" 474# 475CREATE TABLE t1 ( 476col_int_nokey INTEGER, 477col_int_key INTEGER, 478col_varchar_key VARCHAR(1), 479KEY (col_int_key), 480KEY (col_varchar_key, col_int_key) 481) stats_persistent=0; 482INSERT INTO t1 VALUES (NULL,2,'w'); 483INSERT INTO t1 VALUES (7,9,'m'); 484INSERT INTO t1 VALUES (9,3,'m'); 485INSERT INTO t1 VALUES (7,9,'k'); 486INSERT INTO t1 VALUES (4,NULL,'r'); 487INSERT INTO t1 VALUES (2,9,'t'); 488INSERT INTO t1 VALUES (6,3,'j'); 489INSERT INTO t1 VALUES (8,8,'u'); 490INSERT INTO t1 VALUES (NULL,8,'h'); 491INSERT INTO t1 VALUES (5,53,'o'); 492INSERT INTO t1 VALUES (NULL,0,NULL); 493INSERT INTO t1 VALUES (6,5,'k'); 494INSERT INTO t1 VALUES (188,166,'e'); 495INSERT INTO t1 VALUES (2,3,'n'); 496INSERT INTO t1 VALUES (1,0,'t'); 497INSERT INTO t1 VALUES (1,1,'c'); 498INSERT INTO t1 VALUES (0,9,'m'); 499INSERT INTO t1 VALUES (9,5,'y'); 500INSERT INTO t1 VALUES (NULL,6,'f'); 501CREATE TABLE t2 ( 502c1 INTEGER NOT NULL 503) stats_persistent=0; 504EXPLAIN SELECT table1.col_int_nokey 505FROM t1 AS table1 STRAIGHT_JOIN ( 506t1 AS table2 INNER JOIN t1 AS table3 507ON table3.col_varchar_key = table2.col_varchar_key) 508ON table3.col_int_nokey = table1.col_int_key 509WHERE table3.col_int_key != SOME ( SELECT c1 FROM t2 ); 510id select_type table type possible_keys key key_len ref rows Extra 5111 PRIMARY table1 ALL col_int_key NULL NULL NULL 19 NULL 5121 PRIMARY table3 ALL col_varchar_key NULL NULL NULL 19 Using where; Using join buffer (Block Nested Loop) 5131 PRIMARY table2 ref col_varchar_key col_varchar_key 4 test.table3.col_varchar_key 2 Using index 5142 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 515SELECT table1.col_int_nokey 516FROM t1 AS table1 STRAIGHT_JOIN ( 517t1 AS table2 INNER JOIN t1 AS table3 518ON table3.col_varchar_key = table2.col_varchar_key) 519ON table3.col_int_nokey = table1.col_int_key 520WHERE table3.col_int_key != SOME ( SELECT c1 FROM t2 ); 521col_int_nokey 522DROP TABLE t1, t2; 523# 524# Bug#58243 "RQG test optimizer_subquery causes server crash 525# when running with ICP" 526# 527CREATE TABLE t1 ( 528pk INTEGER NOT NULL, 529c1 INTEGER NOT NULL, 530c2 INTEGER NOT NULL, 531PRIMARY KEY (pk) 532); 533INSERT INTO t1 VALUES (1,6,7); 534CREATE TABLE t2 ( 535c1 INTEGER NOT NULL 536); 537EXPLAIN SELECT t1.c1 538FROM t1 539WHERE t1.pk < 317 AND 2 IN (SELECT COUNT(t2.c1) 540FROM t2) 541ORDER BY t1.c2; 542id select_type table type possible_keys key key_len ref rows Extra 5431 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 5442 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away 545SELECT t1.c1 546FROM t1 547WHERE t1.pk < 317 AND 2 IN (SELECT COUNT(t2.c1) 548FROM t2) 549ORDER BY t1.c2; 550c1 551DROP TABLE t1, t2; 552CREATE TABLE t1 ( 553i1 INTEGER NOT NULL, 554c1 VARCHAR(1) NOT NULL 555); 556INSERT INTO t1 VALUES (2,'w'); 557CREATE TABLE t2 ( 558i1 INTEGER NOT NULL, 559c1 VARCHAR(1) NOT NULL, 560c2 VARCHAR(1) NOT NULL, 561KEY (c1, i1) 562); 563INSERT INTO t2 VALUES (8,'d','d'); 564INSERT INTO t2 VALUES (4,'v','v'); 565CREATE TABLE t3 ( 566c1 VARCHAR(1) NOT NULL 567); 568INSERT INTO t3 VALUES ('v'); 569EXPLAIN SELECT i1 570FROM t1 571WHERE EXISTS (SELECT t2.c1 572FROM (t2 INNER JOIN t3 ON (t3.c1 = t2.c1)) 573WHERE t2.c2 != t1.c1 AND t2.c2 = (SELECT MIN(t3.c1) 574FROM t3)); 575id select_type table type possible_keys key key_len ref rows Extra 5761 PRIMARY t1 system NULL NULL NULL NULL 1 NULL 5772 DEPENDENT SUBQUERY t3 system NULL NULL NULL NULL 1 NULL 5782 DEPENDENT SUBQUERY t2 ref c1 c1 3 const 1 Using where 5793 SUBQUERY t3 system NULL NULL NULL NULL 1 NULL 580SELECT i1 581FROM t1 582WHERE EXISTS (SELECT t2.c1 583FROM (t2 INNER JOIN t3 ON (t3.c1 = t2.c1)) 584WHERE t2.c2 != t1.c1 AND t2.c2 = (SELECT MIN(t3.c1) 585FROM t3)); 586i1 5872 588DROP TABLE t1,t2,t3; 589# 590# Bug#58015 "Assert in row_sel_field_store_in_mysql_format 591# when running innodb_mrr_icp test" 592# 593create table t1 (a char(2) charset utf8,b double, primary key (a(1)),key (b)); 594insert into t1 values ('',1); 595select 1 from t1 where b <= 1 and a <> ''; 5961 597drop table t1; 598# 599# Bug#59259 "Incorrect rows returned for a correlated subquery 600# when ICP is on" 601# 602CREATE TABLE t1 (pk INTEGER PRIMARY KEY, i INTEGER NOT NULL) ENGINE=InnoDB; 603INSERT INTO t1 VALUES (11,0); 604INSERT INTO t1 VALUES (12,5); 605INSERT INTO t1 VALUES (15,0); 606CREATE TABLE t2 (pk INTEGER PRIMARY KEY, i INTEGER NOT NULL) ENGINE=InnoDB; 607INSERT INTO t2 VALUES (11,1); 608INSERT INTO t2 VALUES (12,2); 609INSERT INTO t2 VALUES (15,4); 610SELECT * FROM t1 611WHERE pk IN (SELECT it.pk FROM t2 JOIN t2 AS it ON it.i=it.i WHERE t1.i); 612pk i 61312 5 614DROP TABLE t1, t2; 615# 616# Bug #58816 "Extra temporary duplicate rows in result set when 617# switching ICP off" 618# 619set @save_optimizer_switch_bug58816= @@optimizer_switch; 620CREATE TABLE t1 ( 621pk INT NOT NULL, 622c1 INT NOT NULL, 623PRIMARY KEY (pk) 624); 625INSERT INTO t1 VALUES (1,9),(2,7),(3,6),(4,3),(5,1); 626EXPLAIN SELECT pk, c1 FROM t1 WHERE pk <> 3; 627id select_type table type possible_keys key key_len ref rows Extra 6281 SIMPLE t1 ALL PRIMARY NULL NULL NULL 5 Using where 629SET SESSION optimizer_switch='index_condition_pushdown=off'; 630SELECT pk, c1 FROM t1 WHERE pk <> 3; 631pk c1 6321 9 6332 7 6344 3 6355 1 636DROP TABLE t1; 637set optimizer_switch= @save_optimizer_switch_bug58816; 638# 639# Bug#58837: ICP crash or valgrind error due to uninitialized 640# value in innobase_index_cond 641# 642CREATE TABLE t1 ( 643t1_int INT, 644t1_time TIME 645); 646CREATE TABLE t2 ( 647t2_int int PRIMARY KEY, 648t2_int2 INT 649); 650INSERT INTO t2 VALUES (); 651INSERT INTO t1 VALUES (); 652 653SELECT * 654FROM t1 AS t1a 655WHERE NOT EXISTS 656(SELECT * 657FROM t1 AS t1b 658WHERE t1b.t1_int NOT IN 659(SELECT t2.t2_int 660FROM t2 661WHERE t1b.t1_time LIKE t1b.t1_int 662OR t1b.t1_time <> t2.t2_int2 663AND 6=7 664) 665) 666;; 667t1_int t1_time 668 669EXPLAIN SELECT * 670FROM t1 AS t1a 671WHERE NOT EXISTS 672(SELECT * 673FROM t1 AS t1b 674WHERE t1b.t1_int NOT IN 675(SELECT t2.t2_int 676FROM t2 677WHERE t1b.t1_time LIKE t1b.t1_int 678OR t1b.t1_time <> t2.t2_int2 679AND 6=7 680) 681) 682;; 683id select_type table type possible_keys key key_len ref rows Extra 6841 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 6852 SUBQUERY t1b system NULL NULL NULL NULL 1 NULL 6863 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 687 688DROP TABLE t1,t2; 689# 690# Bug#59186 Wrong results of join when ICP is enabled 691# 692CREATE TABLE t1 ( 693pk INTEGER NOT NULL, 694c1 VARCHAR(3) NOT NULL, 695PRIMARY KEY (pk) 696); 697INSERT INTO t1 VALUES (1,'y'),(0,'or'); 698CREATE TABLE t2 ( 699pk INTEGER NOT NULL, 700c1 VARCHAR(3) NOT NULL, 701c2 VARCHAR(6) NOT NULL, 702PRIMARY KEY (pk) 703); 704INSERT INTO t2 VALUES (6,'y','RPOYT'),(10,'m','JINQE'); 705EXPLAIN SELECT c2 FROM t1 JOIN t2 ON t1.c1 = t2.c1 706WHERE (t2.pk <= 4 AND t1.pk IN (2,1)) OR 707(t1.pk > 1 AND t2.pk BETWEEN 6 AND 6); 708id select_type table type possible_keys key key_len ref rows Extra 7091 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using where 7101 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 2 Using where; Using join buffer (Block Nested Loop) 711SELECT c2 FROM t1 JOIN t2 ON t1.c1 = t2.c1 712WHERE (t2.pk <= 4 AND t1.pk IN (2,1)) OR 713(t1.pk > 1 AND t2.pk BETWEEN 6 AND 6); 714c2 715DROP TABLE t1, t2; 716# 717# Bug#58838 "Wrong results with HAVING + LIMIT without GROUP BY when 718# ICP is enabled" 719# 720CREATE TABLE t1 ( 721pk INT NOT NULL, 722c1 INT, 723PRIMARY KEY (pk), 724KEY col_int_key (c1) 725); 726INSERT INTO t1 VALUES (1,37),(2,8),(3,-25),(4,NULL),(5,55); 727SELECT pk FROM t1 WHERE c1 <> 1 HAVING pk = 3 ORDER BY pk LIMIT 0; 728pk 729SELECT pk FROM t1 WHERE c1 <> 1 HAVING pk = 3 ORDER BY pk LIMIT 1; 730pk 7313 732SELECT pk FROM t1 WHERE c1 <> 1 HAVING pk = 3 ORDER BY pk LIMIT 2; 733pk 7343 735SELECT pk FROM t1 WHERE c1 <> 1 HAVING pk = 3 ORDER BY pk LIMIT 5; 736pk 7373 738DROP TABLE t1; 739# 740# Bug#59483 "Crash on INSERT/REPLACE in 741# rec_convert_dtuple_to_rec_comp with ICP on" 742# 743CREATE TABLE t1 ( 744pk INTEGER AUTO_INCREMENT PRIMARY KEY, 745i1 INTEGER, 746c1 CHAR(6), 747i2 INTEGER NOT NULL, 748KEY (i2) 749); 750INSERT INTO t1 VALUES 751(NULL, 4, 'that', 8), 752(NULL, 1, 'she', 6), 753(NULL, 6, 'tell', 2); 754SELECT * FROM t1 WHERE i2 IN (3, 6) LIMIT 2 FOR UPDATE; 755pk i1 c1 i2 7562 1 she 6 757INSERT INTO t1 (i2) VALUES (1); 758DROP TABLE t1; 759# 760# Bug #11766678 - 59843: 761# USING UNINITIALISED VALUE IN USES_INDEX_FIELDS_ONLY 762# 763CREATE TABLE t1 ( 764col999 FLOAT NOT NULL, 765COL1000 VARBINARY(179) NOT NULL, 766col1003 DATE DEFAULT NULL, 767KEY idx4267 (col1000, col1003) 768); 769INSERT INTO t1 VALUES (),(); 770Warnings: 771Warning 1364 Field 'col999' doesn't have a default value 772Warning 1364 Field 'COL1000' doesn't have a default value 773SELECT col999 FROM t1 WHERE col1000 = "3" AND col1003 <=> sysdate(); 774col999 775DROP TABLE t1; 776# 777# Bug#11873324 "WRONG RESULT WITH ICP AND STRAIGHT_JOIN" 778# 779CREATE TABLE t1 ( 780pk INTEGER NOT NULL, 781i1 INTEGER, 782PRIMARY KEY (pk), 783KEY col_int_key (i1) 784); 785INSERT INTO t1 VALUES (14,NULL), (18,133); 786CREATE TABLE t2 ( 787pk INTEGER NOT NULL, 788i1 INTEGER, 789c1 VARCHAR(1), 790PRIMARY KEY (pk), 791KEY col_int_key (i1) 792); 793INSERT INTO t2 VALUES (1,7,'f'); 794set @old_opt_switch=@@optimizer_switch; 795EXPLAIN SELECT t1.i1 796FROM t1 797WHERE t1.i1 NOT IN 798( SELECT STRAIGHT_JOIN SUBQUERY_t1.pk 799FROM t1 AS SUBQUERY_t1 800JOIN t2 AS SUBQUERY_t2 801ON SUBQUERY_t2.i1 = SUBQUERY_t1.pk 802WHERE SUBQUERY_t1.i1 > 0 803OR SUBQUERY_t2.c1 = 'a' 804); 805id select_type table type possible_keys key key_len ref rows Extra 8061 PRIMARY t1 index NULL col_int_key 5 NULL 2 Using where; Using index 8072 DEPENDENT SUBQUERY SUBQUERY_t1 eq_ref PRIMARY,col_int_key PRIMARY 4 func 1 Using where; Full scan on NULL key 8082 DEPENDENT SUBQUERY SUBQUERY_t2 ALL col_int_key NULL NULL NULL 1 Using where; Using join buffer (Block Nested Loop) 809SELECT t1.i1 810FROM t1 811WHERE t1.i1 NOT IN 812( SELECT STRAIGHT_JOIN SUBQUERY_t1.pk 813FROM t1 AS SUBQUERY_t1 814JOIN t2 AS SUBQUERY_t2 815ON SUBQUERY_t2.i1 = SUBQUERY_t1.pk 816WHERE SUBQUERY_t1.i1 > 0 817OR SUBQUERY_t2.c1 = 'a' 818); 819i1 820NULL 821133 822set @@optimizer_switch=@old_opt_switch; 823DROP TABLE t1,t2; 824# 825# Bug#11876420 "MISSING ROW IN RESULT WITH SUBQUERY + IN + XOR + 826# NULL VALUES AND ICP ENABLED" 827# 828CREATE TABLE t1 ( 829i1 INTEGER, 830c1 VARCHAR(1), 831KEY col_varchar_key (c1) 832); 833INSERT INTO t1 VALUES (1,'j'), (0,'e'), (210,'f'), (8,'v'), (7,'x'), 834(5,'m'), (NULL,'c'); 835CREATE TABLE t2 ( 836i1 INTEGER, 837c1 VARCHAR(1), 838KEY col_varchar_key (c1) 839); 840INSERT INTO t2 VALUES (8,NULL); 841CREATE TABLE t3 ( 842i1 INTEGER, 843c1 VARCHAR(1), 844KEY col_varchar_key (c1) 845) ENGINE=InnoDB; 846INSERT INTO t3 VALUES (NULL,'w'), (1,NULL), (2,'d'); 847set @old_opt_switch=@@optimizer_switch; 848EXPLAIN SELECT i1 849FROM t3 850WHERE c1 IN 851( SELECT t1.c1 852FROM t2 JOIN t1 853ON t2.i1 >= t1.i1 854WHERE t1.c1 > t2.c1 855) 856XOR i1; 857id select_type table type possible_keys key key_len ref rows Extra 8581 PRIMARY t3 ALL NULL NULL NULL NULL 3 Using where 8592 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 860SELECT i1 861FROM t3 862WHERE c1 IN 863( SELECT t1.c1 864FROM t2 JOIN t1 865ON t2.i1 >= t1.i1 866WHERE t1.c1 > t2.c1 867) 868XOR i1; 869i1 8701 8712 872set @@optimizer_switch=@old_opt_switch; 873DROP TABLE t1, t2, t3; 874# 875# Bug#12355958 "FAILING ASSERTION: TRX->LOCK.N_ACTIVE_THRS == 1" 876# 877CREATE TABLE t1 ( 878pk INTEGER PRIMARY KEY, 879a INTEGER NOT NULL, 880b CHAR(1), 881KEY(b) 882); 883INSERT INTO t1 VALUES (23,5,'d'); 884EXPLAIN SELECT a1.pk 885FROM t1 AS a1 JOIN (SELECT * FROM t1 LIMIT 1) AS a2 ON a2.b = a1.b 886WHERE a1.a = (SELECT pk FROM t1 LIMIT 1) 887AND (a1.a != a2.a OR a1.b IS NULL); 888id select_type table type possible_keys key key_len ref rows Extra 8891 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 8903 SUBQUERY t1 system NULL NULL NULL NULL 1 NULL 8912 DERIVED t1 system NULL NULL NULL NULL 1 NULL 892SELECT a1.pk 893FROM t1 AS a1 JOIN (SELECT * FROM t1 LIMIT 1) AS a2 ON a2.b = a1.b 894WHERE a1.a = (SELECT pk FROM t1 LIMIT 1) 895AND (a1.a != a2.a OR a1.b IS NULL); 896pk 897CREATE VIEW v1 AS SELECT * FROM t1; 898EXPLAIN SELECT a1.pk 899FROM v1 AS a1 JOIN (SELECT * FROM v1 LIMIT 1) AS a2 ON a2.b = a1.b 900WHERE a1.a = (SELECT pk FROM v1 LIMIT 1) 901AND (a1.a != a2.a OR a1.b IS NULL); 902id select_type table type possible_keys key key_len ref rows Extra 9031 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 9043 SUBQUERY t1 system NULL NULL NULL NULL 1 NULL 9052 DERIVED t1 system NULL NULL NULL NULL 1 NULL 906SELECT a1.pk 907FROM v1 AS a1 JOIN (SELECT * FROM v1 LIMIT 1) AS a2 ON a2.b = a1.b 908WHERE a1.a = (SELECT pk FROM v1 LIMIT 1) 909AND (a1.a != a2.a OR a1.b IS NULL); 910pk 911DROP VIEW v1; 912DROP TABLE t1; 913# 914# BUG#12601961 "SEGFAULT IN HANDLER::COMPARE_KEY2" 915# BUG#12724899 "SELECT STRAIGHT_JOIN QUERY GIVES 2 DATES VERSUS 916# 2 WARNINGS WITH ICP ON" 917# 918CREATE TABLE t1 ( 919pk INTEGER NOT NULL, 920i1 INTEGER NOT NULL, 921c1 VARCHAR(1) NOT NULL, 922PRIMARY KEY (pk) 923); 924INSERT INTO t1 VALUES (1,3,'j'), (20,8,'e'); 925EXPLAIN SELECT alias2.i1 926FROM t1 AS alias1 STRAIGHT_JOIN t1 AS alias2 927ON alias2.pk AND alias2.pk <= alias1.c1 928WHERE alias2.pk = 1; 929id select_type table type possible_keys key key_len ref rows Extra 9301 SIMPLE alias1 ALL NULL NULL NULL NULL 2 NULL 9311 SIMPLE alias2 const PRIMARY PRIMARY 4 const 1 Using where 932SELECT alias2.i1 933FROM t1 AS alias1 STRAIGHT_JOIN t1 AS alias2 934ON alias2.pk AND alias2.pk <= alias1.c1 935WHERE alias2.pk = 1; 936i1 937Warnings: 938Warning 1292 Truncated incorrect DOUBLE value: 'j' 939Warning 1292 Truncated incorrect DOUBLE value: 'e' 940DROP TABLE t1; 941# 942# BUG#12822678 - 2 MORE ROWS WHEN ICP=ON W/ STRAIGHT_JOIN 943# 944CREATE TABLE t1 ( 945i1 INTEGER NOT NULL, 946d1 DOUBLE, 947KEY k1 (d1) 948); 949INSERT INTO t1 VALUES (10,1), (17,NULL), (22,NULL); 950CREATE TABLE t2 ( 951pk INTEGER NOT NULL, 952i1 INTEGER NOT NULL, 953PRIMARY KEY (pk) 954); 955INSERT INTO t2 VALUES (4,1); 956EXPLAIN SELECT t1.d1, t2.pk, t2.i1 957FROM t1 STRAIGHT_JOIN t2 ON t2.i1 958WHERE t2.pk <> t1.d1 AND t2.pk = 4; 959id select_type table type possible_keys key key_len ref rows Extra 9601 SIMPLE t1 index NULL k1 9 NULL 3 Using index 9611 SIMPLE t2 const PRIMARY PRIMARY 4 const 1 Using where 962SELECT t1.d1, t2.pk, t2.i1 963FROM t1 STRAIGHT_JOIN t2 ON t2.i1 964WHERE t2.pk <> t1.d1 AND t2.pk = 4; 965d1 pk i1 9661 4 1 967DROP TABLE t1, t2; 968# 969# BUG#12838420 "DUPLICATE VALUES FOR GROUP-BY COLUMN WHEN JOIN 970# BUFFERING IS OFF" 971# 972CREATE TABLE t1 ( 973col_int_key INT, 974pk INT, 975PRIMARY KEY (pk), 976KEY (col_int_key) 977); 978INSERT INTO t1 VALUES (2,3),(3,2),(3,5),(4,6); 979CREATE TABLE t2 ( 980col_int_key INT, 981pk INT, 982PRIMARY KEY (pk), 983KEY (col_int_key) 984); 985INSERT INTO t2 VALUES (0,9),(3,10),(4,6),(6,1),(100,3),(200,5); 986set @old_opt_switch=@@optimizer_switch; 987SET optimizer_switch="block_nested_loop=off"; 988EXPLAIN SELECT t2.col_int_key AS field1 989FROM t2 USE INDEX(col_int_key) STRAIGHT_JOIN t1 ON t2.col_int_key 990WHERE t2.pk < 7 AND t2.col_int_key <> 7 991GROUP BY field1; 992id select_type table type possible_keys key key_len ref rows Extra 9931 SIMPLE t2 ALL col_int_key NULL NULL NULL 6 Using where; Using temporary; Using filesort 9941 SIMPLE t1 index NULL PRIMARY 4 NULL 4 Using index 995SELECT t2.col_int_key AS field1 996FROM t2 USE INDEX(col_int_key) STRAIGHT_JOIN t1 ON t2.col_int_key 997WHERE t2.pk < 7 AND t2.col_int_key <> 7 998GROUP BY field1; 999field1 10004 10016 1002100 1003200 1004SET @@optimizer_switch=@old_opt_switch; 1005DROP TABLE t1,t2; 1006# 1007# Bug#12976163 "CRASH IN INDEX CONDITION PUSHDOWN CODE AGAINST 1008# A MYISAM TABLE" 1009# 1010CREATE TABLE t1 ( 1011i1 INTEGER NOT NULL, 1012i2 INTEGER NOT NULL 1013); 1014INSERT INTO t1 VALUES (14,1), (15,2), (16,3); 1015CREATE TABLE t2 ( 1016i1 INTEGER NOT NULL, 1017i2 INTEGER NOT NULL, 1018c1 TINYTEXT 1019); 1020INSERT INTO t2 1021SELECT i1, 10 * i2, "MySQL" FROM t1; 1022CREATE PROCEDURE proc1(id INTEGER) 1023BEGIN 1024SELECT i2 1025FROM ( 1026(SELECT i1, i2, NULL AS a1 FROM t1) 1027UNION 1028(SELECT i1, i2, c1 AS a1 FROM t2) 1029) u1 1030WHERE i1 = id; 1031END$$ 1032CALL proc1(15); 1033i2 10342 103520 1036DROP PROCEDURE proc1; 1037DROP TABLE t1, t2; 1038# 1039# Bug#13655397 "CRASH IN SYNC_THREAD_LEVELS_NONEMPTY_TRX" 1040# 1041CREATE TABLE t1 ( 1042i1 INTEGER NOT NULL, 1043i2 INTEGER NOT NULL, 1044KEY (i1) 1045); 1046INSERT INTO t1 VALUES (4,4), (5,5); 1047CREATE TABLE t2 ( 1048pk INTEGER NOT NULL, 1049PRIMARY KEY (pk) 1050); 1051INSERT INTO t2 VALUES (1); 1052CREATE FUNCTION f1() RETURNS INTEGER 1053RETURN (SELECT MOD(COUNT(DISTINCT pk), 10) FROM t2); 1054EXPLAIN SELECT i1, i2 FROM t1 WHERE f1() = 1 AND i1 = 5; 1055id select_type table type possible_keys key key_len ref rows Extra 10561 SIMPLE t1 ref i1 i1 4 const 1 Using where 1057SELECT i1, i2 FROM t1 WHERE f1() = 1 AND i1 = 5; 1058i1 i2 10595 5 1060DROP FUNCTION f1; 1061DROP TABLE t1, t2; 1062set optimizer_switch=default; 1063