1set @save_storage_engine= @@default_storage_engine; 2set default_storage_engine=InnoDB; 3create table t1(a int); 4show create table t1; 5Table Create Table 6t1 CREATE TABLE `t1` ( 7 `a` int(11) DEFAULT NULL 8) ENGINE=InnoDB DEFAULT CHARSET=latin1 9insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 10create table t2(a int); 11insert into t2 select A.a + 10*(B.a + 10*C.a) from t1 A, t1 B, t1 C; 12create table t3 ( 13a char(8) not null, b char(8) not null, filler char(200), 14key(a) 15); 16insert into t3 select @a:=concat('c-', 1000+ A.a, '=w'), @a, 'filler' from t2 A; 17insert into t3 select concat('c-', 1000+A.a, '=w'), concat('c-', 2000+A.a, '=w'), 18'filler-1' from t2 A; 19insert into t3 select concat('c-', 1000+A.a, '=w'), concat('c-', 3000+A.a, '=w'), 20'filler-2' from t2 A; 21select a,filler from t3 where a >= 'c-9011=w'; 22a filler 23select a,filler from t3 where a >= 'c-1011=w' and a <= 'c-1015=w'; 24a filler 25c-1011=w filler 26c-1011=w filler-1 27c-1011=w filler-2 28c-1012=w filler 29c-1012=w filler-1 30c-1012=w filler-2 31c-1013=w filler 32c-1013=w filler-1 33c-1013=w filler-2 34c-1014=w filler 35c-1014=w filler-1 36c-1014=w filler-2 37c-1015=w filler 38c-1015=w filler-1 39c-1015=w filler-2 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'); 42a filler 43c-1011=w filler 44c-1011=w filler-1 45c-1011=w filler-2 46c-1012=w filler 47c-1012=w filler-1 48c-1012=w filler-2 49c-1013=w filler 50c-1013=w filler-1 51c-1013=w filler-2 52c-1014=w filler 53c-1014=w filler-1 54c-1014=w filler-2 55c-1015=w filler 56c-1015=w filler-1 57c-1015=w filler-2 58insert into t3 values ('c-1013=z', 'c-1013=z', 'err'); 59insert into t3 values ('a-1014=w', 'a-1014=w', 'err'); 60select a,filler from t3 where (a>='c-1011=w' and a <= 'c-1013=w') or 61(a>='c-1014=w' and a <= 'c-1015=w'); 62a filler 63c-1011=w filler 64c-1011=w filler-1 65c-1011=w filler-2 66c-1012=w filler 67c-1012=w filler-1 68c-1012=w filler-2 69c-1013=w filler 70c-1013=w filler-1 71c-1013=w filler-2 72c-1014=w filler 73c-1014=w filler-1 74c-1014=w filler-2 75c-1015=w filler 76c-1015=w filler-1 77c-1015=w filler-2 78delete from t3 where b in ('c-1013=z', 'a-1014=w'); 79select a,filler from t3 where a='c-1011=w' or a='c-1012=w' or a='c-1013=w' or 80a='c-1014=w' or a='c-1015=w'; 81a filler 82c-1011=w filler 83c-1011=w filler-1 84c-1011=w filler-2 85c-1012=w filler 86c-1012=w filler-1 87c-1012=w filler-2 88c-1013=w filler 89c-1013=w filler-1 90c-1013=w filler-2 91c-1014=w filler 92c-1014=w filler-1 93c-1014=w filler-2 94c-1015=w filler 95c-1015=w filler-1 96c-1015=w filler-2 97insert into t3 values ('c-1013=w', 'del-me', 'inserted'); 98select a,filler from t3 where a='c-1011=w' or a='c-1012=w' or a='c-1013=w' or 99a='c-1014=w' or a='c-1015=w'; 100a filler 101c-1011=w filler 102c-1011=w filler-1 103c-1011=w filler-2 104c-1012=w filler 105c-1012=w filler-1 106c-1012=w filler-2 107c-1013=w filler 108c-1013=w filler-1 109c-1013=w filler-2 110c-1013=w inserted 111c-1014=w filler 112c-1014=w filler-1 113c-1014=w filler-2 114c-1015=w filler 115c-1015=w filler-1 116c-1015=w filler-2 117delete from t3 where b='del-me'; 118alter table t3 add primary key(b); 119select b,filler from t3 where (b>='c-1011=w' and b<= 'c-1018=w') or 120b IN ('c-1019=w', 'c-1020=w', 'c-1021=w', 121'c-1022=w', 'c-1023=w', 'c-1024=w'); 122b filler 123c-1011=w filler 124c-1012=w filler 125c-1013=w filler 126c-1014=w filler 127c-1015=w filler 128c-1016=w filler 129c-1017=w filler 130c-1018=w filler 131c-1019=w filler 132c-1020=w filler 133c-1021=w filler 134c-1022=w filler 135c-1023=w filler 136c-1024=w filler 137select b,filler from t3 where (b>='c-1011=w' and b<= 'c-1020=w') or 138b IN ('c-1021=w', 'c-1022=w', 'c-1023=w'); 139b filler 140c-1011=w filler 141c-1012=w filler 142c-1013=w filler 143c-1014=w filler 144c-1015=w filler 145c-1016=w filler 146c-1017=w filler 147c-1018=w filler 148c-1019=w filler 149c-1020=w filler 150c-1021=w filler 151c-1022=w filler 152c-1023=w filler 153select b,filler from t3 where (b>='c-1011=w' and b<= 'c-1018=w') or 154b IN ('c-1019=w', 'c-1020=w') or 155(b>='c-1021=w' and b<= 'c-1023=w'); 156b filler 157c-1011=w filler 158c-1012=w filler 159c-1013=w filler 160c-1014=w filler 161c-1015=w filler 162c-1016=w filler 163c-1017=w filler 164c-1018=w filler 165c-1019=w filler 166c-1020=w filler 167c-1021=w filler 168c-1022=w filler 169c-1023=w filler 170create table t4 (a varchar(10), b int, c char(10), filler char(200), 171key idx1 (a, b, c)); 172insert into t4 (filler) select concat('NULL-', 15-a) from t2 order by a limit 15; 173insert into t4 (a,b,c,filler) 174select 'b-1',NULL,'c-1', concat('NULL-', 15-a) from t2 order by a limit 15; 175insert into t4 (a,b,c,filler) 176select 'b-1',NULL,'c-222', concat('NULL-', 15-a) from t2 order by a limit 15; 177insert into t4 (a,b,c,filler) 178select 'bb-1',NULL,'cc-2', concat('NULL-', 15-a) from t2 order by a limit 15; 179insert into t4 (a,b,c,filler) 180select 'zz-1',NULL,'cc-2', 'filler-data' from t2 order by a limit 500; 181explain 182select * from t4 where a IS NULL and b IS NULL and (c IS NULL or c='no-such-row1' 183 or c='no-such-row2'); 184id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1851 SIMPLE t4 NULL range idx1 idx1 29 NULL 16 100.00 Using where 186Warnings: 187Note 1003 /* select#1 */ select `test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t4`.`c` AS `c`,`test`.`t4`.`filler` AS `filler` from `test`.`t4` where (isnull(`test`.`t4`.`a`) and isnull(`test`.`t4`.`b`) and (isnull(`test`.`t4`.`c`) or (`test`.`t4`.`c` = 'no-such-row1') or (`test`.`t4`.`c` = 'no-such-row2'))) 188select * from t4 where a IS NULL and b IS NULL and (c IS NULL or c='no-such-row1' 189 or c='no-such-row2'); 190a b c filler 191NULL NULL NULL NULL-15 192NULL NULL NULL NULL-14 193NULL NULL NULL NULL-13 194NULL NULL NULL NULL-12 195NULL NULL NULL NULL-11 196NULL NULL NULL NULL-10 197NULL NULL NULL NULL-9 198NULL NULL NULL NULL-8 199NULL NULL NULL NULL-7 200NULL NULL NULL NULL-6 201NULL NULL NULL NULL-5 202NULL NULL NULL NULL-4 203NULL NULL NULL NULL-3 204NULL NULL NULL NULL-2 205NULL NULL NULL NULL-1 206explain 207select * from t4 where (a ='b-1' or a='bb-1') and b IS NULL and (c='c-1' or c='cc-2'); 208id select_type table partitions type possible_keys key key_len ref rows filtered Extra 2091 SIMPLE t4 NULL range idx1 idx1 29 NULL 32 100.00 Using where 210Warnings: 211Note 1003 /* select#1 */ select `test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t4`.`c` AS `c`,`test`.`t4`.`filler` AS `filler` from `test`.`t4` where (((`test`.`t4`.`a` = 'b-1') or (`test`.`t4`.`a` = 'bb-1')) and isnull(`test`.`t4`.`b`) and ((`test`.`t4`.`c` = 'c-1') or (`test`.`t4`.`c` = 'cc-2'))) 212select * from t4 where (a ='b-1' or a='bb-1') and b IS NULL and (c='c-1' or c='cc-2'); 213a b c filler 214b-1 NULL c-1 NULL-15 215b-1 NULL c-1 NULL-14 216b-1 NULL c-1 NULL-13 217b-1 NULL c-1 NULL-12 218b-1 NULL c-1 NULL-11 219b-1 NULL c-1 NULL-10 220b-1 NULL c-1 NULL-9 221b-1 NULL c-1 NULL-8 222b-1 NULL c-1 NULL-7 223b-1 NULL c-1 NULL-6 224b-1 NULL c-1 NULL-5 225b-1 NULL c-1 NULL-4 226b-1 NULL c-1 NULL-3 227b-1 NULL c-1 NULL-2 228b-1 NULL c-1 NULL-1 229bb-1 NULL cc-2 NULL-15 230bb-1 NULL cc-2 NULL-14 231bb-1 NULL cc-2 NULL-13 232bb-1 NULL cc-2 NULL-12 233bb-1 NULL cc-2 NULL-11 234bb-1 NULL cc-2 NULL-10 235bb-1 NULL cc-2 NULL-9 236bb-1 NULL cc-2 NULL-8 237bb-1 NULL cc-2 NULL-7 238bb-1 NULL cc-2 NULL-6 239bb-1 NULL cc-2 NULL-5 240bb-1 NULL cc-2 NULL-4 241bb-1 NULL cc-2 NULL-3 242bb-1 NULL cc-2 NULL-2 243bb-1 NULL cc-2 NULL-1 244select * 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'); 245a b c filler 246b-1 NULL c-1 NULL-15 247b-1 NULL c-1 NULL-14 248b-1 NULL c-1 NULL-13 249b-1 NULL c-1 NULL-12 250b-1 NULL c-1 NULL-11 251b-1 NULL c-1 NULL-10 252b-1 NULL c-1 NULL-9 253b-1 NULL c-1 NULL-8 254b-1 NULL c-1 NULL-7 255b-1 NULL c-1 NULL-6 256b-1 NULL c-1 NULL-5 257b-1 NULL c-1 NULL-4 258b-1 NULL c-1 NULL-3 259b-1 NULL c-1 NULL-2 260b-1 NULL c-1 NULL-1 261bb-1 NULL cc-2 NULL-15 262bb-1 NULL cc-2 NULL-14 263bb-1 NULL cc-2 NULL-13 264bb-1 NULL cc-2 NULL-12 265bb-1 NULL cc-2 NULL-11 266bb-1 NULL cc-2 NULL-10 267bb-1 NULL cc-2 NULL-9 268bb-1 NULL cc-2 NULL-8 269bb-1 NULL cc-2 NULL-7 270bb-1 NULL cc-2 NULL-6 271bb-1 NULL cc-2 NULL-5 272bb-1 NULL cc-2 NULL-4 273bb-1 NULL cc-2 NULL-3 274bb-1 NULL cc-2 NULL-2 275bb-1 NULL cc-2 NULL-1 276drop table t1, t2, t3, t4; 277create table t1 (a int, b int not null,unique key (a,b),index(b)); 278insert 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); 279Warnings: 280Warning 1062 Duplicate entry '6-6' for key 'a' 281create table t2 like t1; 282insert into t2 select * from t1; 283alter 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)); 284select * from t1 where a is null; 285a b c 286NULL 7 0 287NULL 9 0 288NULL 9 0 289select * from t1 where (a is null or a > 0 and a < 3) and b > 7 limit 3; 290a b c 291NULL 9 0 292NULL 9 0 293select * from t1 where a is null and b=9 or a is null and b=7 limit 3; 294a b c 295NULL 7 0 296NULL 9 0 297NULL 9 0 298drop table t1, t2; 299CREATE TABLE t1 ( 300ID int(10) unsigned NOT NULL AUTO_INCREMENT, 301col1 int(10) unsigned DEFAULT NULL, 302key1 int(10) unsigned NOT NULL DEFAULT '0', 303key2 int(10) unsigned DEFAULT NULL, 304text1 text, 305text2 text, 306col2 smallint(6) DEFAULT '100', 307col3 enum('headers','bodyandsubject') NOT NULL DEFAULT 'bodyandsubject', 308col4 tinyint(3) unsigned NOT NULL DEFAULT '0', 309PRIMARY KEY (ID), 310KEY (key1), 311KEY (key2) 312) AUTO_INCREMENT=6 DEFAULT CHARSET=utf8; 313INSERT INTO t1 VALUES 314(1,NULL,1130,NULL,'Hello',NULL,100,'bodyandsubject',0), 315(2,NULL,1130,NULL,'bye',NULL,100,'bodyandsubject',0), 316(3,NULL,1130,NULL,'red',NULL,100,'bodyandsubject',0), 317(4,NULL,1130,NULL,'yellow',NULL,100,'bodyandsubject',0), 318(5,NULL,1130,NULL,'blue',NULL,100,'bodyandsubject',0); 319select * FROM t1 WHERE key1=1130 AND col1 IS NULL ORDER BY text1; 320ID col1 key1 key2 text1 text2 col2 col3 col4 3215 NULL 1130 NULL blue NULL 100 bodyandsubject 0 3222 NULL 1130 NULL bye NULL 100 bodyandsubject 0 3231 NULL 1130 NULL Hello NULL 100 bodyandsubject 0 3243 NULL 1130 NULL red NULL 100 bodyandsubject 0 3254 NULL 1130 NULL yellow NULL 100 bodyandsubject 0 326drop table t1; 327 328BUG#37851: Crash in test_if_skip_sort_order tab->select is zero 329 330CREATE TABLE t1 ( 331pk int(11) NOT NULL AUTO_INCREMENT, 332PRIMARY KEY (pk) 333); 334INSERT INTO t1 VALUES (1); 335CREATE TABLE t2 ( 336pk int(11) NOT NULL AUTO_INCREMENT, 337int_key int(11) DEFAULT NULL, 338PRIMARY KEY (pk), 339KEY int_key (int_key) 340); 341INSERT INTO t2 VALUES (1,1),(2,6),(3,0); 342EXPLAIN EXTENDED 343SELECT MIN(t1.pk) 344FROM t1 WHERE EXISTS ( 345SELECT t2.pk 346FROM t2 347WHERE t2.int_key IS NULL 348GROUP BY t2.pk 349); 350id select_type table partitions type possible_keys key key_len ref rows filtered Extra 3511 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 3522 SUBQUERY t2 NULL ref int_key int_key 5 const 1 100.00 Using where; Using index 353Warnings: 354Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 355Note 1003 /* select#1 */ select min(`test`.`t1`.`pk`) AS `MIN(t1.pk)` from `test`.`t1` where 0 356DROP TABLE t1, t2; 357# 358# BUG#42048 Discrepancy between MyISAM and Maria's ICP implementation 359# 360create table t0 (a int); 361insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 362create table t1 (a int, b char(20), filler char(200), key(a,b(10))); 363insert into t1 select A.a + 10*(B.a + 10*C.a), 'bbb','filler' from t0 A, t0 B, t0 C; 364update t1 set b=repeat(char(65+a), 20) where a < 25; 365This must show range + using index condition: 366explain select * from t1 where a < 10 and b = repeat(char(65+a), 20); 367id select_type table partitions type possible_keys key key_len ref rows filtered Extra 3681 SIMPLE t1 NULL range a a 5 NULL x x Using where 369Warnings: 370Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`filler` AS `filler` from `test`.`t1` where ((`test`.`t1`.`a` < 10) and (`test`.`t1`.`b` = repeat(char((65 + `test`.`t1`.`a`)),20))) 371select * from t1 where a < 10 and b = repeat(char(65+a), 20); 372a b filler 3730 AAAAAAAAAAAAAAAAAAAA filler 3741 BBBBBBBBBBBBBBBBBBBB filler 3752 CCCCCCCCCCCCCCCCCCCC filler 3763 DDDDDDDDDDDDDDDDDDDD filler 3774 EEEEEEEEEEEEEEEEEEEE filler 3785 FFFFFFFFFFFFFFFFFFFF filler 3796 GGGGGGGGGGGGGGGGGGGG filler 3807 HHHHHHHHHHHHHHHHHHHH filler 3818 IIIIIIIIIIIIIIIIIIII filler 3829 JJJJJJJJJJJJJJJJJJJJ filler 383drop table t0,t1; 384# 385# BUG#41136: ORDER BY + range access: EXPLAIN shows "Using MRR" while MRR is actually not used 386# 387create table t0 (a int); 388insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 389create table t1 (a int, b int, key(a)); 390insert 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; 391This mustn't show "Using MRR": 392explain select * from t1 where a < 20 order by a; 393id select_type table partitions type possible_keys key key_len ref rows filtered Extra 3941 SIMPLE t1 NULL range a a 5 NULL 20 100.00 Using where 395Warnings: 396Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (`test`.`t1`.`a` < 20) order by `test`.`t1`.`a` 397drop table t0, t1; 398set @read_rnd_buffer_size_save= @@read_rnd_buffer_size; 399set read_rnd_buffer_size=64; 400create table t1(a int); 401insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 402create table t2(a char(8), b char(8), c char(8), filler char(100), key k1(a,b,c) ); 403insert into t2 select 404concat('a-', 1000 + A.a, '-a'), 405concat('b-', 1000 + B.a, '-b'), 406concat('c-', 1000 + C.a, '-c'), 407'filler' 408from t1 A, t1 B, t1 C; 409EXPLAIN select count(length(a) + length(filler)) 410from t2 force index (k1) 411where a>='a-1000-a' and a <'a-1001-a'; 412id select_type table partitions type possible_keys key key_len ref rows filtered Extra 4131 SIMPLE t2 NULL range k1 k1 9 NULL 100 100.00 Using where 414Warnings: 415Note 1003 /* select#1 */ select count((length(`test`.`t2`.`a`) + length(`test`.`t2`.`filler`))) AS `count(length(a) + length(filler))` from `test`.`t2` FORCE INDEX (`k1`) where ((`test`.`t2`.`a` >= 'a-1000-a') and (`test`.`t2`.`a` < 'a-1001-a')) 416select count(length(a) + length(filler)) 417from t2 force index (k1) 418where a>='a-1000-a' and a <'a-1001-a'; 419count(length(a) + length(filler)) 420100 421drop table t2; 422create table t2 (a char(100), b char(100), c char(100), d int, 423filler char(10), key(d), primary key (a,b,c)); 424insert into t2 select A.a, B.a, B.a, A.a, 'filler' from t1 A, t1 B; 425explain select * from t2 force index (d) where d < 10; 426id select_type table partitions type possible_keys key key_len ref rows filtered Extra 4271 SIMPLE t2 NULL range d d 5 NULL # 100.00 Using where 428Warnings: 429Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d`,`test`.`t2`.`filler` AS `filler` from `test`.`t2` FORCE INDEX (`d`) where (`test`.`t2`.`d` < 10) 430drop table t2; 431drop table t1; 432set @@read_rnd_buffer_size= @read_rnd_buffer_size_save; 433create table t1 (f1 int not null, f2 int not null,f3 int not null, f4 char(1), primary key (f1,f2), key ix(f3)); 434select * from t1 where (f3>=5 and f3<=10) or (f3>=1 and f3<=4); 435f1 f2 f3 f4 4361 1 1 A 43710 10 10 A 4382 2 2 A 4393 3 3 A 4404 4 4 A 4415 5 5 A 4426 6 6 A 4437 7 7 A 4448 8 8 A 4459 9 9 A 446drop table t1; 447 448BUG#37977: Wrong result returned on GROUP BY + OR + Innodb 449 450CREATE TABLE t1 ( 451`pk` int(11) NOT NULL AUTO_INCREMENT, 452`int_nokey` int(11) NOT NULL, 453`int_key` int(11) NOT NULL, 454`date_key` date NOT NULL, 455`date_nokey` date NOT NULL, 456`time_key` time NOT NULL, 457`time_nokey` time NOT NULL, 458`datetime_key` datetime NOT NULL, 459`datetime_nokey` datetime NOT NULL, 460`varchar_key` varchar(5) DEFAULT NULL, 461`varchar_nokey` varchar(5) DEFAULT NULL, 462PRIMARY KEY (`pk`), 463KEY `int_key` (`int_key`), 464KEY `date_key` (`date_key`), 465KEY `time_key` (`time_key`), 466KEY `datetime_key` (`datetime_key`), 467KEY `varchar_key` (`varchar_key`) 468); 469INSERT IGNORE INTO t1 VALUES 470(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'), 471(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'), 472(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'), 473(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), 474(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'); 475Warnings: 476Warning 1264 Out of range value for column 'date_key' at row 2 477Warning 1264 Out of range value for column 'date_nokey' at row 2 478Warning 1264 Out of range value for column 'datetime_key' at row 2 479Warning 1264 Out of range value for column 'datetime_nokey' at row 2 480select pk from t1 WHERE `varchar_key` > 'kr' group by pk; 481pk 4821 4835 484select pk from t1 WHERE `int_nokey` IS NULL OR `varchar_key` > 'kr' group by pk; 485pk 4861 4875 488drop table t1; 489# 490# BUG#39447: Error with NOT NULL condition and LIMIT 1 491# 492CREATE TABLE t1 ( 493id int(11) NOT NULL, 494parent_id int(11) DEFAULT NULL, 495name varchar(10) DEFAULT NULL, 496PRIMARY KEY (id), 497KEY ind_parent_id (parent_id) 498); 499insert into t1 (id, parent_id, name) values 500(10,NULL,'A'), 501(20,10,'B'), 502(30,10,'C'), 503(40,NULL,'D'), 504(50,40,'E'), 505(60,40,'F'), 506(70,NULL,'J'); 507SELECT id FROM t1 WHERE parent_id IS NOT NULL ORDER BY id DESC LIMIT 1; 508id 50960 510This must show type=index, extra=Using where 511explain SELECT * FROM t1 WHERE parent_id IS NOT NULL ORDER BY id DESC LIMIT 1; 512id select_type table partitions type possible_keys key key_len ref rows filtered Extra 5131 SIMPLE t1 NULL index ind_parent_id PRIMARY 4 NULL 1 57.14 Using where 514Warnings: 515Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`parent_id` AS `parent_id`,`test`.`t1`.`name` AS `name` from `test`.`t1` where (`test`.`t1`.`parent_id` is not null) order by `test`.`t1`.`id` desc limit 1 516SELECT * FROM t1 WHERE parent_id IS NOT NULL ORDER BY id DESC LIMIT 1; 517id parent_id name 51860 40 F 519drop table t1; 520# 521# Bug#50381 "Assertion failing in handler.h:1283: 522# void COST_VECT::add_io(double, double)" 523# 524CREATE TABLE t1 ( 525c1 INT NOT NULL, 526c2 VARCHAR(1) DEFAULT NULL, 527PRIMARY KEY (c1) 528); 529CREATE TABLE t2 ( 530c1 INT NOT NULL, 531c2 VARCHAR(1) DEFAULT NULL, 532PRIMARY KEY (c1) 533); 534INSERT INTO t2 VALUES (10,'v'); 535INSERT INTO t2 VALUES (11,'r'); 536SELECT t1.c2 537FROM t2 STRAIGHT_JOIN t1 ON t1.c1 < t2.c1; 538c2 539DROP TABLE t1, t2; 540# 541# Bug#58463: Error Can't find record on SELECT with JOIN and ORDER BY 542# 543CREATE TABLE t1 ( 544pk INT NOT NULL, 545PRIMARY KEY (pk) 546) ENGINE=MyISAM; 547INSERT INTO t1 VALUES (2); 548CREATE TABLE t2 ( 549pk INT NOT NULL, 550i1 INT NOT NULL, 551i2 INT NOT NULL, 552c1 VARCHAR(1024) CHARACTER SET utf8, 553PRIMARY KEY (pk), 554KEY k1 (i1) 555); 556INSERT INTO t2 VALUES (3, 9, 1, NULL); 557EXPLAIN SELECT i1 558FROM t1 LEFT JOIN t2 ON t1.pk = t2.i2 559WHERE t2.i1 > 5 560AND t2.pk IS NULL 561ORDER BY i1; 562id select_type table partitions type possible_keys key key_len ref rows filtered Extra 5631 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table 564Warnings: 565Note 1003 /* select#1 */ select `test`.`t2`.`i1` AS `i1` from `test`.`t2` where ((`test`.`t2`.`i1` > 5) and isnull(`test`.`t2`.`pk`) and multiple equal('2', `test`.`t2`.`i2`)) order by `test`.`t2`.`i1` 566SELECT i1 567FROM t1 LEFT JOIN t2 ON t1.pk = t2.i2 568WHERE t2.i1 > 5 569AND t2.pk IS NULL 570ORDER BY i1; 571i1 572DROP TABLE t1, t2; 573# 574# Bug#12321461: CRASH IN DSMRR_IMPL::DSMRR_INIT ON SELECT STRAIGHT_JOIN 575# 576set @save_optimizer_switch = @@optimizer_switch; 577set optimizer_switch='block_nested_loop=off,batched_key_access=off'; 578CREATE TABLE t1 ( 579pk INTEGER, 580c1 VARCHAR(1) NOT NULL, 581PRIMARY KEY (pk) 582); 583CREATE TABLE t2 ( 584c1 VARCHAR(1) NOT NULL 585); 586INSERT INTO t2 VALUES ('v'), ('c'); 587EXPLAIN SELECT STRAIGHT_JOIN t1.c1 588FROM t1 RIGHT OUTER JOIN t2 ON t1.c1 = t2.c1 589WHERE t1.pk > 176; 590id select_type table partitions type possible_keys key key_len ref rows filtered Extra 5911 SIMPLE t2 NULL ALL NULL NULL NULL NULL 2 100.00 NULL 5921 SIMPLE t1 NULL range PRIMARY PRIMARY 4 NULL 1 100.00 Using where 593Warnings: 594Note 1003 /* select#1 */ select straight_join `test`.`t1`.`c1` AS `c1` from `test`.`t2` join `test`.`t1` where ((`test`.`t1`.`c1` = `test`.`t2`.`c1`) and (`test`.`t1`.`pk` > 176)) 595SELECT STRAIGHT_JOIN t1.c1 596FROM t1 RIGHT OUTER JOIN t2 ON t1.c1 = t2.c1 597WHERE t1.pk > 176; 598c1 599DROP TABLE t1,t2; 600set optimizer_switch= @save_optimizer_switch; 601# 602# Bug#13249966 MRR: RANDOM ERROR DUE TO UNINITIALIZED RES WITH 603# SMALL READ_RND_BUFFER_SIZE 604# 605set @read_rnd_buffer_size_save= @@read_rnd_buffer_size; 606set read_rnd_buffer_size=1; 607select @@read_rnd_buffer_size; 608@@read_rnd_buffer_size 6091 610CREATE TABLE t1 ( 611i1 INTEGER NOT NULL, 612i2 INTEGER NOT NULL, 613KEY (i2) 614); 615INSERT INTO t1 VALUES (0,1),(1,2),(2,3); 616EXPLAIN SELECT i1 617FROM t1 618WHERE i2 > 2; 619id select_type table partitions type possible_keys key key_len ref rows filtered Extra 6201 SIMPLE t1 NULL range i2 i2 4 NULL 1 100.00 Using where 621Warnings: 622Note 1003 /* select#1 */ select `test`.`t1`.`i1` AS `i1` from `test`.`t1` where (`test`.`t1`.`i2` > 2) 623SELECT i1 624FROM t1 625WHERE i2 > 2; 626i1 6272 628DROP TABLE t1; 629set @@read_rnd_buffer_size= @read_rnd_buffer_size_save; 630select @@read_rnd_buffer_size; 631@@read_rnd_buffer_size 632262144 633# 634# Bug 12365385 STRAIGHT_JOIN QUERY QUICKLY EXHAUSTS SYSTEM+VIRT. 635# MEMORY LEADING TO SYSTEM CRASH 636# 637CREATE TABLE ten (a INTEGER); 638INSERT INTO ten VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 639CREATE TABLE t1 ( 640pk INTEGER NOT NULL, 641i1 INTEGER NOT NULL, 642c1 VARCHAR(10) NOT NULL, 643PRIMARY KEY (pk) 644); 645INSERT INTO t1 646SELECT a, 1, 'MySQL' FROM ten; 647CREATE TABLE t2 ( 648pk INTEGER NOT NULL, 649c1 VARCHAR(10) NOT NULL, 650c2 varchar(10) NOT NULL, 651PRIMARY KEY (pk) 652); 653INSERT INTO t2 654SELECT a, 'MySQL', 'MySQL' FROM ten; 655CREATE TABLE t3 ( 656pk INTEGER NOT NULL, 657c1 VARCHAR(10) NOT NULL, 658PRIMARY KEY (pk) 659); 660INSERT INTO t3 661SELECT a, 'MySQL' FROM ten; 662CREATE TABLE t4 ( 663pk int(11) NOT NULL, 664c1_key varchar(10) CHARACTER SET utf8 NOT NULL, 665c2 varchar(10) NOT NULL, 666c3 varchar(10) NOT NULL, 667PRIMARY KEY (pk), 668KEY k1 (c1_key) 669); 670CREATE TABLE t5 ( 671pk INTEGER NOT NULL, 672c1 VARCHAR(10) NOT NULL, 673PRIMARY KEY (pk) 674); 675INSERT INTO t5 676SELECT a, 'MySQL' FROM ten; 677EXPLAIN SELECT STRAIGHT_JOIN * 678FROM 679(t1 LEFT JOIN 680(t2 LEFT JOIN 681(t3 LEFT OUTER JOIN t4 ON t3.c1 <= t4.c1_key) 682ON t2.c1 = t4.c3) 683ON t1.c1 = t4.c2) 684RIGHT OUTER JOIN t5 ON t2.c2 <= t5.c1 685WHERE t1.i1 = 1; 686id select_type table partitions type possible_keys key key_len ref rows filtered Extra 6871 SIMPLE t5 NULL ALL NULL NULL NULL NULL 10 100.00 NULL 6881 SIMPLE t1 NULL ALL NULL NULL NULL NULL 10 10.00 Using where; Using join buffer (Block Nested Loop) 6891 SIMPLE t2 NULL ALL NULL NULL NULL NULL 10 33.33 Using where; Using join buffer (Block Nested Loop) 6901 SIMPLE t3 NULL ALL NULL NULL NULL NULL 10 100.00 Using join buffer (Block Nested Loop) 6911 SIMPLE t4 NULL ALL k1 NULL NULL NULL 1 100.00 Range checked for each record (index map: 0x2) 692Warnings: 693Note 1003 /* select#1 */ select straight_join `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`i1` AS `i1`,`test`.`t1`.`c1` AS `c1`,`test`.`t2`.`pk` AS `pk`,`test`.`t2`.`c1` AS `c1`,`test`.`t2`.`c2` AS `c2`,`test`.`t3`.`pk` AS `pk`,`test`.`t3`.`c1` AS `c1`,`test`.`t4`.`pk` AS `pk`,`test`.`t4`.`c1_key` AS `c1_key`,`test`.`t4`.`c2` AS `c2`,`test`.`t4`.`c3` AS `c3`,`test`.`t5`.`pk` AS `pk`,`test`.`t5`.`c1` AS `c1` from `test`.`t5` join `test`.`t1` join `test`.`t2` join `test`.`t3` join `test`.`t4` where ((`test`.`t4`.`c3` = `test`.`t2`.`c1`) and (`test`.`t4`.`c2` = `test`.`t1`.`c1`) and (`test`.`t1`.`i1` = 1) and (`test`.`t2`.`c2` <= `test`.`t5`.`c1`) and (convert(`test`.`t3`.`c1` using utf8) <= `test`.`t4`.`c1_key`)) 694SELECT STRAIGHT_JOIN * 695FROM 696(t1 LEFT JOIN 697(t2 LEFT JOIN 698(t3 LEFT OUTER JOIN t4 ON t3.c1 <= t4.c1_key) 699ON t2.c1 = t4.c3) 700ON t1.c1 = t4.c2) 701RIGHT OUTER JOIN t5 ON t2.c2 <= t5.c1 702WHERE t1.i1 = 1; 703pk i1 c1 pk c1 c2 pk c1 pk c1_key c2 c3 pk c1 704DROP TABLE ten, t1, t2, t3, t4, t5; 705# 706# Bug 18172819 CRASH ON DSMRR_IMPL::CHOOSE_MRR_IMPL IN SQL/HANDLER.CC 707# 708set @big_tables_save= @@big_tables; 709set @optimizer_switch_save=@@optimizer_switch; 710set big_tables=ON; 711set optimizer_switch='derived_merge=off'; 712SELECT @@big_tables; 713@@big_tables 7141 715CREATE TABLE t1 ( 716i1 INTEGER NOT NULL, 717c1 VARCHAR(1) 718); 719INSERT INTO t1 VALUES (1,'a'), (2, NULL); 720CREATE TABLE t2 ( 721c1 VARCHAR(1), 722i1 INTEGER NOT NULL, 723KEY (c1) 724); 725INSERT INTO t2 VALUES ('a', 1), (NULL, 2); 726EXPLAIN SELECT * 727FROM 728( SELECT * FROM t1 ) AS d1 729WHERE d1.c1 IN ( SELECT c1 FROM t2 ) 730AND d1.c1 IS NULL 731ORDER BY d1.i1; 732id select_type table partitions type possible_keys key key_len ref rows filtered Extra 7331 PRIMARY <derived2> NULL ref <auto_key0> <auto_key0> 4 const 1 100.00 Using where; Using filesort 7343 DEPENDENT SUBQUERY t2 NULL index_subquery c1 c1 4 func 1 100.00 Using index 7352 DERIVED t1 NULL ALL NULL NULL NULL NULL 2 100.00 NULL 736Warnings: 737Note 1003 /* select#1 */ select `d1`.`i1` AS `i1`,`d1`.`c1` AS `c1` from (/* select#2 */ select `test`.`t1`.`i1` AS `i1`,`test`.`t1`.`c1` AS `c1` from `test`.`t1`) `d1` where (<in_optimizer>(`d1`.`c1`,<exists>(<index_lookup>(<cache>(`d1`.`c1`) in t2 on c1))) and isnull(`d1`.`c1`)) order by `d1`.`i1` 738SELECT * 739FROM 740( SELECT * FROM t1 ) AS d1 741WHERE d1.c1 IN ( SELECT c1 FROM t2 ) 742AND d1.c1 IS NULL 743ORDER BY d1.i1; 744i1 c1 745DROP TABLE t1, t2; 746set big_tables=@big_tables_save; 747set optimizer_switch=@optimizer_switch_save; 748# 749# Bug#41029 "MRR: SELECT FOR UPDATE fails to lock gaps (InnoDB table)" 750# 751SET AUTOCOMMIT=0; 752CREATE TABLE t1 ( 753dummy INT PRIMARY KEY, 754a INT UNIQUE, 755b INT 756) ENGINE=InnoDB; 757INSERT INTO t1 VALUES (1,1,1),(3,3,3),(5,5,5); 758COMMIT; 759SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; 760SELECT @@tx_isolation; 761@@tx_isolation 762REPEATABLE-READ 763Warnings: 764Warning 1287 '@@tx_isolation' is deprecated and will be removed in a future release. Please use '@@transaction_isolation' instead 765START TRANSACTION; 766EXPLAIN SELECT * FROM t1 WHERE a > 2 FOR UPDATE; 767id select_type table partitions type possible_keys key key_len ref rows filtered Extra 7681 SIMPLE t1 NULL range a a 5 NULL 2 100.00 Using index condition 769Warnings: 770Note 1003 /* select#1 */ select `test`.`t1`.`dummy` AS `dummy`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (`test`.`t1`.`a` > 2) 771SELECT * FROM t1 WHERE a > 2 FOR UPDATE; 772dummy a b 7733 3 3 7745 5 5 775SET AUTOCOMMIT=0; 776START TRANSACTION; 777INSERT INTO t1 VALUES (2,2,2); 778ERROR HY000: Lock wait timeout exceeded; try restarting transaction 779ROLLBACK; 780ROLLBACK; 781DROP TABLE t1; 782# 783# Bug#54286 "Server crash at lock timeout with MRR" 784# 785SET AUTOCOMMIT=0; 786CREATE TABLE t1 ( 787dummy INT PRIMARY KEY, 788a INT UNIQUE, 789b INT 790) ENGINE=InnoDB; 791COMMIT; 792INSERT INTO t1 VALUES (1,1,1),(3,3,3),(5,5,5); 793SET AUTOCOMMIT=0; 794SELECT * FROM t1 WHERE a > 2 FOR UPDATE; 795ERROR HY000: Lock wait timeout exceeded; try restarting transaction 796ROLLBACK; 797ROLLBACK; 798DROP TABLE t1; 799COMMIT; 800set default_storage_engine= @save_storage_engine; 801set optimizer_switch=default; 802