1 2create table t1(a int); 3show create table t1; 4insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 5create table t2(a int); 6insert into t2 select A.a + 10*(B.a + 10*C.a) from t1 A, t1 B, t1 C; 7 8 9create table t3 ( 10 a char(8) not null, b char(8) not null, filler char(200), 11 key(a) 12); 13insert into t3 select @a:=concat('c-', 1000+ A.a, '=w'), @a, 'filler' from t2 A; 14insert into t3 select concat('c-', 1000+A.a, '=w'), concat('c-', 2000+A.a, '=w'), 15 'filler-1' from t2 A; 16insert into t3 select concat('c-', 1000+A.a, '=w'), concat('c-', 3000+A.a, '=w'), 17 'filler-2' from t2 A; 18 19# Test empty result set 20select a,filler from t3 where a >= 'c-9011=w'; 21 22# Ok, t3.ref_length=6, limit is 64 => 10 elements fit into the buffer 23# Test the cases when buffer gets exhausted at different points in source 24# intervals: 25 26# 1. Split is in the middle of the range 27--sorted_result 28select a,filler from t3 where a >= 'c-1011=w' and a <= 'c-1015=w'; 29 30# 2. Split is at range edge 31--sorted_result 32select a,filler from t3 where (a>='c-1011=w' and a <= 'c-1013=w') or 33 (a>='c-1014=w' and a <= 'c-1015=w'); 34 35# 3. Split is at range edge, with some rows between ranges. 36insert into t3 values ('c-1013=z', 'c-1013=z', 'err'); 37insert into t3 values ('a-1014=w', 'a-1014=w', 'err'); 38 39--sorted_result 40select a,filler from t3 where (a>='c-1011=w' and a <= 'c-1013=w') or 41 (a>='c-1014=w' and a <= 'c-1015=w'); 42delete from t3 where b in ('c-1013=z', 'a-1014=w'); 43 44# 4. Split is within the equality range. 45--sorted_result 46select a,filler from t3 where a='c-1011=w' or a='c-1012=w' or a='c-1013=w' or 47 a='c-1014=w' or a='c-1015=w'; 48 49# 5. Split is at the edge of equality range. 50insert into t3 values ('c-1013=w', 'del-me', 'inserted'); 51--sorted_result 52select a,filler from t3 where a='c-1011=w' or a='c-1012=w' or a='c-1013=w' or 53 a='c-1014=w' or a='c-1015=w'; 54delete from t3 where b='del-me'; 55 56# PK tests are not included here. 57 58alter table t3 add primary key(b); 59 60## PK scan tests 61# 6. Split is between 'unique' PK ranges 62select b,filler from t3 where (b>='c-1011=w' and b<= 'c-1018=w') or 63 b IN ('c-1019=w', 'c-1020=w', 'c-1021=w', 64 'c-1022=w', 'c-1023=w', 'c-1024=w'); 65 66# 7. Between non-uniq and uniq range 67select b,filler from t3 where (b>='c-1011=w' and b<= 'c-1020=w') or 68 b IN ('c-1021=w', 'c-1022=w', 'c-1023=w'); 69 70# 8. Between uniq and non-uniq range 71select b,filler from t3 where (b>='c-1011=w' and b<= 'c-1018=w') or 72 b IN ('c-1019=w', 'c-1020=w') or 73 (b>='c-1021=w' and b<= 'c-1023=w'); 74## End of PK scan tests 75 76# 77# Now try different keypart types and special values 78# 79create table t4 (a varchar(10), b int, c char(10), filler char(200), 80 key idx1 (a, b, c)); 81 82# insert buffer_size * 1.5 all-NULL tuples 83insert into t4 (filler) select concat('NULL-', 15-a) from t2 order by a limit 15; 84 85insert into t4 (a,b,c,filler) 86 select 'b-1',NULL,'c-1', concat('NULL-', 15-a) from t2 order by a limit 15; 87insert into t4 (a,b,c,filler) 88 select 'b-1',NULL,'c-222', concat('NULL-', 15-a) from t2 order by a limit 15; 89insert into t4 (a,b,c,filler) 90 select 'bb-1',NULL,'cc-2', concat('NULL-', 15-a) from t2 order by a limit 15; 91insert into t4 (a,b,c,filler) 92 select 'zz-1',NULL,'cc-2', 'filler-data' from t2 order by a limit 500; 93 94-- disable_query_log 95-- disable_result_log 96ANALYZE TABLE t4; 97-- enable_result_log 98-- enable_query_log 99 100explain 101 select * from t4 where a IS NULL and b IS NULL and (c IS NULL or c='no-such-row1' 102 or c='no-such-row2'); 103select * from t4 where a IS NULL and b IS NULL and (c IS NULL or c='no-such-row1' 104 or c='no-such-row2'); 105 106explain 107 select * from t4 where (a ='b-1' or a='bb-1') and b IS NULL and (c='c-1' or c='cc-2'); 108select * from t4 where (a ='b-1' or a='bb-1') and b IS NULL and (c='c-1' or c='cc-2'); 109 110select * from t4 ignore index(idx1) where (a ='b-1' or a='bb-1') and b IS NULL and (c='c-1' or c='cc-2'); 111drop table t1, t2, t3, t4; 112 113# 114# Check how ICP works with NULLs and partially-covered indexes 115# 116create table t1 (a int, b int not null,unique key (a,b),index(b)); 117insert ignore into t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(null,7),(9,9),(8,8),(7,7),(null,9),(null,9),(6,6); 118create table t2 like t1; 119insert into t2 select * from t1; 120alter table t1 modify b blob not null, add c int not null, drop key a, add unique key (a,b(20),c), drop key b, add key (b(10)); 121 122select * from t1 where a is null; 123select * from t1 where (a is null or a > 0 and a < 3) and b > 7 limit 3; 124 125select * from t1 where a is null and b=9 or a is null and b=7 limit 3; 126drop table t1, t2; 127 128# 129# BUG#30622: Incorrect query results for MRR + filesort 130# 131CREATE TABLE t1 ( 132 ID int(10) unsigned NOT NULL AUTO_INCREMENT, 133 col1 int(10) unsigned DEFAULT NULL, 134 key1 int(10) unsigned NOT NULL DEFAULT '0', 135 key2 int(10) unsigned DEFAULT NULL, 136 text1 text, 137 text2 text, 138 col2 smallint(6) DEFAULT '100', 139 col3 enum('headers','bodyandsubject') NOT NULL DEFAULT 'bodyandsubject', 140 col4 tinyint(3) unsigned NOT NULL DEFAULT '0', 141 PRIMARY KEY (ID), 142 KEY (key1), 143 KEY (key2) 144) AUTO_INCREMENT=6 DEFAULT CHARSET=utf8; 145 146INSERT INTO t1 VALUES 147(1,NULL,1130,NULL,'Hello',NULL,100,'bodyandsubject',0), 148(2,NULL,1130,NULL,'bye',NULL,100,'bodyandsubject',0), 149(3,NULL,1130,NULL,'red',NULL,100,'bodyandsubject',0), 150(4,NULL,1130,NULL,'yellow',NULL,100,'bodyandsubject',0), 151(5,NULL,1130,NULL,'blue',NULL,100,'bodyandsubject',0); 152 153select * FROM t1 WHERE key1=1130 AND col1 IS NULL ORDER BY text1; 154 155drop table t1; 156 157 158--echo 159--echo BUG#37851: Crash in test_if_skip_sort_order tab->select is zero 160--echo 161CREATE TABLE t1 ( 162 pk int(11) NOT NULL AUTO_INCREMENT, 163 PRIMARY KEY (pk) 164); 165INSERT INTO t1 VALUES (1); 166 167CREATE TABLE t2 ( 168 pk int(11) NOT NULL AUTO_INCREMENT, 169 int_key int(11) DEFAULT NULL, 170 PRIMARY KEY (pk), 171 KEY int_key (int_key) 172); 173INSERT INTO t2 VALUES (1,1),(2,6),(3,0); 174 175-- disable_query_log 176-- disable_result_log 177ANALYZE TABLE t1; 178ANALYZE TABLE t2; 179-- enable_result_log 180-- enable_query_log 181 182EXPLAIN EXTENDED 183SELECT MIN(t1.pk) 184FROM t1 WHERE EXISTS ( 185 SELECT t2.pk 186 FROM t2 187 WHERE t2.int_key IS NULL 188 GROUP BY t2.pk 189); 190 191DROP TABLE t1, t2; 192 193-- echo # 194-- echo # BUG#42048 Discrepancy between MyISAM and Maria's ICP implementation 195-- echo # 196create table t0 (a int); 197insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 198create table t1 (a int, b char(20), filler char(200), key(a,b(10))); 199insert into t1 select A.a + 10*(B.a + 10*C.a), 'bbb','filler' from t0 A, t0 B, t0 C; 200update t1 set b=repeat(char(65+a), 20) where a < 25; 201 202-- disable_query_log 203-- disable_result_log 204ANALYZE TABLE t1; 205-- enable_result_log 206-- enable_query_log 207 208--echo This must show range + using index condition: 209--replace_column 10 x 11 x 210explain select * from t1 where a < 10 and b = repeat(char(65+a), 20); 211select * from t1 where a < 10 and b = repeat(char(65+a), 20); 212drop table t0,t1; 213 214-- echo # 215-- echo # BUG#41136: ORDER BY + range access: EXPLAIN shows "Using MRR" while MRR is actually not used 216-- echo # 217create table t0 (a int); 218insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 219create table t1 (a int, b int, key(a)); 220insert into t1 select A.a + 10 *(B.a + 10*C.a), A.a + 10 *(B.a + 10*C.a) from t0 A, t0 B, t0 C; 221-- disable_query_log 222-- disable_result_log 223ANALYZE TABLE t1; 224-- enable_result_log 225-- enable_query_log 226-- echo This mustn't show "Using MRR": 227explain select * from t1 where a < 20 order by a; 228drop table t0, t1; 229 230# Try big rowid sizes 231set @read_rnd_buffer_size_save= @@read_rnd_buffer_size; 232set read_rnd_buffer_size=64; 233 234# By default InnoDB will fill values only for key parts used by the query, 235# which will cause DS-MRR to supply an invalid tuple on scan restoration. 236# This test was originally developed for verifying that DS-MRR's code 237# extra(HA_EXTRA_RETRIEVE_ALL_COLS) call has effect. This has now been 238# replaced by using the table's read_set bitmap. 239create table t1(a int); 240insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 241create table t2(a char(8), b char(8), c char(8), filler char(100), key k1(a,b,c) ); 242 243insert into t2 select 244 concat('a-', 1000 + A.a, '-a'), 245 concat('b-', 1000 + B.a, '-b'), 246 concat('c-', 1000 + C.a, '-c'), 247 'filler' 248from t1 A, t1 B, t1 C; 249 250# The use of "force index" is to ensure the query is done as a range scan. 251# Without "force index", InnoDB's record count estimate is sometimes 252# ~400 instead of 1000, which causes a table scan. 253let query= 254select count(length(a) + length(filler)) 255from t2 force index (k1) 256where a>='a-1000-a' and a <'a-1001-a'; 257 258eval EXPLAIN $query; 259eval $query; 260 261drop table t2; 262 263# Try a very big rowid 264create table t2 (a char(100), b char(100), c char(100), d int, 265 filler char(10), key(d), primary key (a,b,c)); 266insert into t2 select A.a, B.a, B.a, A.a, 'filler' from t1 A, t1 B; 267-- disable_query_log 268-- disable_result_log 269ANALYZE TABLE t2; 270-- enable_result_log 271-- enable_query_log 272--replace_column 10 # 273explain select * from t2 force index (d) where d < 10; 274drop table t2; 275 276drop table t1; 277set @@read_rnd_buffer_size= @read_rnd_buffer_size_save; 278 279# 280# BUG#33033 "MySQL/InnoDB crashes with simple select range query" 281# 282create table t1 (f1 int not null, f2 int not null,f3 int not null, f4 char(1), primary key (f1,f2), key ix(f3)); 283 284--disable_query_log 285let $1=55; 286 287while ($1) 288{ 289 eval insert into t1(f1,f2,f3,f4) values ($1,$1,$1,'A'); 290 dec $1; 291} 292--enable_query_log 293 294# The following must not crash: 295--sorted_result 296select * from t1 where (f3>=5 and f3<=10) or (f3>=1 and f3<=4); 297 298drop table t1; 299 300--echo 301--echo BUG#37977: Wrong result returned on GROUP BY + OR + Innodb 302--echo 303CREATE TABLE t1 ( 304 `pk` int(11) NOT NULL AUTO_INCREMENT, 305 `int_nokey` int(11) NOT NULL, 306 `int_key` int(11) NOT NULL, 307 `date_key` date NOT NULL, 308 `date_nokey` date NOT NULL, 309 `time_key` time NOT NULL, 310 `time_nokey` time NOT NULL, 311 `datetime_key` datetime NOT NULL, 312 `datetime_nokey` datetime NOT NULL, 313 `varchar_key` varchar(5) DEFAULT NULL, 314 `varchar_nokey` varchar(5) DEFAULT NULL, 315 PRIMARY KEY (`pk`), 316 KEY `int_key` (`int_key`), 317 KEY `date_key` (`date_key`), 318 KEY `time_key` (`time_key`), 319 KEY `datetime_key` (`datetime_key`), 320 KEY `varchar_key` (`varchar_key`) 321); 322 323INSERT IGNORE INTO t1 VALUES 324(1,5,5,'2009-10-16','2009-10-16','09:28:15','09:28:15','2007-09-14 05:34:08','2007-09-14 05:34:08','qk','qk'), 325(2,6,6,'0000-00-00','0000-00-00','23:06:39','23:06:39','0000-00-00 00:00:00','0000-00-00 00:00:00','j','j'), 326(3,10,10,'2000-12-18','2000-12-18','22:16:19','22:16:19','2006-11-04 15:42:50','2006-11-04 15:42:50','aew','aew'), 327(4,0,0,'2001-09-18','2001-09-18','00:00:00','00:00:00','2004-03-23 13:23:35','2004-03-23 13:23:35',NULL,NULL), 328(5,6,6,'2007-08-16','2007-08-16','22:13:38','22:13:38','2004-08-19 11:01:28','2004-08-19 11:01:28','qu','qu'); 329select pk from t1 WHERE `varchar_key` > 'kr' group by pk; 330select pk from t1 WHERE `int_nokey` IS NULL OR `varchar_key` > 'kr' group by pk; 331drop table t1; 332 333--echo # 334--echo # BUG#39447: Error with NOT NULL condition and LIMIT 1 335--echo # 336CREATE TABLE t1 ( 337 id int(11) NOT NULL, 338 parent_id int(11) DEFAULT NULL, 339 name varchar(10) DEFAULT NULL, 340 PRIMARY KEY (id), 341 KEY ind_parent_id (parent_id) 342); 343 344insert into t1 (id, parent_id, name) values 345(10,NULL,'A'), 346(20,10,'B'), 347(30,10,'C'), 348(40,NULL,'D'), 349(50,40,'E'), 350(60,40,'F'), 351(70,NULL,'J'); 352 353-- disable_query_log 354-- disable_result_log 355ANALYZE TABLE t1; 356-- enable_result_log 357-- enable_query_log 358 359SELECT id FROM t1 WHERE parent_id IS NOT NULL ORDER BY id DESC LIMIT 1; 360--echo This must show type=index, extra=Using where 361# The filtering effect of IS NOT NULL is from stats of the range optimizer. 362explain SELECT * FROM t1 WHERE parent_id IS NOT NULL ORDER BY id DESC LIMIT 1; 363SELECT * FROM t1 WHERE parent_id IS NOT NULL ORDER BY id DESC LIMIT 1; 364drop table t1; 365 366--echo # 367--echo # Bug#50381 "Assertion failing in handler.h:1283: 368--echo # void COST_VECT::add_io(double, double)" 369--echo # 370 371CREATE TABLE t1 ( 372 c1 INT NOT NULL, 373 c2 VARCHAR(1) DEFAULT NULL, 374 PRIMARY KEY (c1) 375); 376 377CREATE TABLE t2 ( 378 c1 INT NOT NULL, 379 c2 VARCHAR(1) DEFAULT NULL, 380 PRIMARY KEY (c1) 381); 382 383INSERT INTO t2 VALUES (10,'v'); 384INSERT INTO t2 VALUES (11,'r'); 385 386SELECT t1.c2 387FROM t2 STRAIGHT_JOIN t1 ON t1.c1 < t2.c1; 388 389DROP TABLE t1, t2; 390 391--echo # 392--echo # Bug#58463: Error Can't find record on SELECT with JOIN and ORDER BY 393--echo # 394 395# To produce the same query plan as in the bug report the first table 396# must be stored in MyISAM. 397CREATE TABLE t1 ( 398 pk INT NOT NULL, 399 PRIMARY KEY (pk) 400) ENGINE=MyISAM; 401 402INSERT INTO t1 VALUES (2); 403 404CREATE TABLE t2 ( 405 pk INT NOT NULL, 406 i1 INT NOT NULL, 407 i2 INT NOT NULL, 408 c1 VARCHAR(1024) CHARACTER SET utf8, 409 PRIMARY KEY (pk), 410 KEY k1 (i1) 411); 412 413INSERT INTO t2 VALUES (3, 9, 1, NULL); 414 415-- disable_query_log 416-- disable_result_log 417ANALYZE TABLE t1; 418ANALYZE TABLE t2; 419-- enable_result_log 420-- enable_query_log 421 422let query= 423SELECT i1 424FROM t1 LEFT JOIN t2 ON t1.pk = t2.i2 425WHERE t2.i1 > 5 426AND t2.pk IS NULL 427ORDER BY i1; 428 429eval EXPLAIN $query; 430eval $query; 431 432DROP TABLE t1, t2; 433 434--echo # 435--echo # Bug#12321461: CRASH IN DSMRR_IMPL::DSMRR_INIT ON SELECT STRAIGHT_JOIN 436--echo # 437 438# This test should run without join buffering 439set @save_optimizer_switch = @@optimizer_switch; 440set optimizer_switch='block_nested_loop=off,batched_key_access=off'; 441 442CREATE TABLE t1 ( 443 pk INTEGER, 444 c1 VARCHAR(1) NOT NULL, 445 PRIMARY KEY (pk) 446); 447 448CREATE TABLE t2 ( 449 c1 VARCHAR(1) NOT NULL 450); 451 452INSERT INTO t2 VALUES ('v'), ('c'); 453 454-- disable_query_log 455-- disable_result_log 456ANALYZE TABLE t1; 457ANALYZE TABLE t2; 458-- enable_result_log 459-- enable_query_log 460 461let query= 462SELECT STRAIGHT_JOIN t1.c1 463FROM t1 RIGHT OUTER JOIN t2 ON t1.c1 = t2.c1 464WHERE t1.pk > 176; 465 466eval EXPLAIN $query; 467eval $query; 468 469DROP TABLE t1,t2; 470 471# Restore join buffer settings to their original values 472set optimizer_switch= @save_optimizer_switch; 473 474--echo # 475--echo # Bug#13249966 MRR: RANDOM ERROR DUE TO UNINITIALIZED RES WITH 476--echo # SMALL READ_RND_BUFFER_SIZE 477--echo # 478 479set @read_rnd_buffer_size_save= @@read_rnd_buffer_size; 480set read_rnd_buffer_size=1; 481select @@read_rnd_buffer_size; 482 483CREATE TABLE t1 ( 484 i1 INTEGER NOT NULL, 485 i2 INTEGER NOT NULL, 486 KEY (i2) 487); 488 489INSERT INTO t1 VALUES (0,1),(1,2),(2,3); 490 491-- disable_query_log 492-- disable_result_log 493ANALYZE TABLE t1; 494-- enable_result_log 495-- enable_query_log 496 497let query= 498SELECT i1 499FROM t1 500WHERE i2 > 2; 501 502eval EXPLAIN $query; 503eval $query; 504 505DROP TABLE t1; 506 507set @@read_rnd_buffer_size= @read_rnd_buffer_size_save; 508select @@read_rnd_buffer_size; 509 510--echo # 511--echo # Bug 12365385 STRAIGHT_JOIN QUERY QUICKLY EXHAUSTS SYSTEM+VIRT. 512--echo # MEMORY LEADING TO SYSTEM CRASH 513--echo # 514 515CREATE TABLE ten (a INTEGER); 516INSERT INTO ten VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 517 518CREATE TABLE t1 ( 519 pk INTEGER NOT NULL, 520 i1 INTEGER NOT NULL, 521 c1 VARCHAR(10) NOT NULL, 522 PRIMARY KEY (pk) 523); 524 525INSERT INTO t1 526 SELECT a, 1, 'MySQL' FROM ten; 527 528CREATE TABLE t2 ( 529 pk INTEGER NOT NULL, 530 c1 VARCHAR(10) NOT NULL, 531 c2 varchar(10) NOT NULL, 532 PRIMARY KEY (pk) 533); 534 535INSERT INTO t2 536 SELECT a, 'MySQL', 'MySQL' FROM ten; 537 538CREATE TABLE t3 ( 539 pk INTEGER NOT NULL, 540 c1 VARCHAR(10) NOT NULL, 541 PRIMARY KEY (pk) 542); 543 544INSERT INTO t3 545 SELECT a, 'MySQL' FROM ten; 546 547CREATE TABLE t4 ( 548 pk int(11) NOT NULL, 549 c1_key varchar(10) CHARACTER SET utf8 NOT NULL, 550 c2 varchar(10) NOT NULL, 551 c3 varchar(10) NOT NULL, 552 PRIMARY KEY (pk), 553 KEY k1 (c1_key) 554); 555 556# t4 is empty 557 558CREATE TABLE t5 ( 559 pk INTEGER NOT NULL, 560 c1 VARCHAR(10) NOT NULL, 561 PRIMARY KEY (pk) 562); 563 564INSERT INTO t5 565 SELECT a, 'MySQL' FROM ten; 566 567-- disable_query_log 568-- disable_result_log 569ANALYZE TABLE t1; 570ANALYZE TABLE t2; 571ANALYZE TABLE t3; 572ANALYZE TABLE t4; 573ANALYZE TABLE t5; 574-- enable_result_log 575-- enable_query_log 576 577let query= 578SELECT STRAIGHT_JOIN * 579FROM 580 (t1 LEFT JOIN 581 (t2 LEFT JOIN 582 (t3 LEFT OUTER JOIN t4 ON t3.c1 <= t4.c1_key) 583 ON t2.c1 = t4.c3) 584 ON t1.c1 = t4.c2) 585 RIGHT OUTER JOIN t5 ON t2.c2 <= t5.c1 586WHERE t1.i1 = 1; 587 588eval EXPLAIN $query; 589eval $query; 590 591DROP TABLE ten, t1, t2, t3, t4, t5; 592 593--echo # 594--echo # Bug 18172819 CRASH ON DSMRR_IMPL::CHOOSE_MRR_IMPL IN SQL/HANDLER.CC 595--echo # 596 597# Test needs to run with big_tables ON 598set @big_tables_save= @@big_tables; 599set @optimizer_switch_save=@@optimizer_switch; 600set big_tables=ON; 601set optimizer_switch='derived_merge=off'; 602SELECT @@big_tables; 603 604CREATE TABLE t1 ( 605 i1 INTEGER NOT NULL, 606 c1 VARCHAR(1) 607); 608 609INSERT INTO t1 VALUES (1,'a'), (2, NULL); 610 611CREATE TABLE t2 ( 612 c1 VARCHAR(1), 613 i1 INTEGER NOT NULL, 614 KEY (c1) 615); 616 617INSERT INTO t2 VALUES ('a', 1), (NULL, 2); 618 619-- disable_query_log 620-- disable_result_log 621ANALYZE TABLE t1; 622ANALYZE TABLE t2; 623-- enable_result_log 624-- enable_query_log 625 626let query= 627SELECT * 628FROM 629 ( SELECT * FROM t1 ) AS d1 630WHERE d1.c1 IN ( SELECT c1 FROM t2 ) 631 AND d1.c1 IS NULL 632ORDER BY d1.i1; 633 634eval EXPLAIN $query; 635eval $query; 636 637DROP TABLE t1, t2; 638 639# Restore the configuration for big_tables 640set big_tables=@big_tables_save; 641set optimizer_switch=@optimizer_switch_save; 642