1set optimizer_switch='semijoin=on,materialization=on,firstmatch=on,loosescan=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=off'; 2set @read_rnd_buffer_size_save= @@read_rnd_buffer_size; 3set read_rnd_buffer_size=79; 4select @@read_rnd_buffer_size; 5@@read_rnd_buffer_size 679 7create table t1(a int); 8show create table t1; 9Table Create Table 10t1 CREATE TABLE `t1` ( 11 `a` int(11) DEFAULT NULL 12) ENGINE=MyISAM DEFAULT CHARSET=latin1 13insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 14create table t2(a int); 15insert into t2 select A.a + 10*(B.a + 10*C.a) from t1 A, t1 B, t1 C; 16create table t3 ( 17a char(8) not null, b char(8) not null, filler char(200), 18key(a) 19); 20insert into t3 select @a:=concat('c-', 1000+ A.a, '=w'), @a, 'filler' from t2 A; 21insert into t3 select concat('c-', 1000+A.a, '=w'), concat('c-', 2000+A.a, '=w'), 22'filler-1' from t2 A; 23insert into t3 select concat('c-', 1000+A.a, '=w'), concat('c-', 3000+A.a, '=w'), 24'filler-2' from t2 A; 25select a,filler from t3 where a >= 'c-9011=w'; 26a filler 27select a,filler from t3 where a >= 'c-1011=w' and a <= 'c-1015=w'; 28a filler 29c-1011=w filler 30c-1011=w filler-1 31c-1011=w filler-2 32c-1012=w filler 33c-1012=w filler-1 34c-1012=w filler-2 35c-1013=w filler 36c-1013=w filler-1 37c-1013=w filler-2 38c-1014=w filler 39c-1014=w filler-1 40c-1014=w filler-2 41c-1015=w filler 42c-1015=w filler-1 43c-1015=w filler-2 44select a,filler from t3 where (a>='c-1011=w' and a <= 'c-1013=w') or 45(a>='c-1014=w' and a <= 'c-1015=w'); 46a filler 47c-1011=w filler 48c-1011=w filler-1 49c-1011=w filler-2 50c-1012=w filler 51c-1012=w filler-1 52c-1012=w filler-2 53c-1013=w filler 54c-1013=w filler-1 55c-1013=w filler-2 56c-1014=w filler 57c-1014=w filler-1 58c-1014=w filler-2 59c-1015=w filler 60c-1015=w filler-1 61c-1015=w filler-2 62insert into t3 values ('c-1013=z', 'c-1013=z', 'err'); 63insert into t3 values ('a-1014=w', 'a-1014=w', 'err'); 64select a,filler from t3 where (a>='c-1011=w' and a <= 'c-1013=w') or 65(a>='c-1014=w' and a <= 'c-1015=w'); 66a filler 67c-1011=w filler 68c-1011=w filler-1 69c-1011=w filler-2 70c-1012=w filler 71c-1012=w filler-1 72c-1012=w filler-2 73c-1013=w filler 74c-1013=w filler-1 75c-1013=w filler-2 76c-1014=w filler 77c-1014=w filler-1 78c-1014=w filler-2 79c-1015=w filler 80c-1015=w filler-1 81c-1015=w filler-2 82delete from t3 where b in ('c-1013=z', 'a-1014=w'); 83select a,filler from t3 where a='c-1011=w' or a='c-1012=w' or a='c-1013=w' or 84a='c-1014=w' or a='c-1015=w'; 85a filler 86c-1011=w filler 87c-1011=w filler-1 88c-1011=w filler-2 89c-1012=w filler 90c-1012=w filler-1 91c-1012=w filler-2 92c-1013=w filler 93c-1013=w filler-1 94c-1013=w filler-2 95c-1014=w filler 96c-1014=w filler-1 97c-1014=w filler-2 98c-1015=w filler 99c-1015=w filler-1 100c-1015=w filler-2 101insert into t3 values ('c-1013=w', 'del-me', 'inserted'); 102select a,filler from t3 where a='c-1011=w' or a='c-1012=w' or a='c-1013=w' or 103a='c-1014=w' or a='c-1015=w'; 104a filler 105c-1011=w filler 106c-1011=w filler-1 107c-1011=w filler-2 108c-1012=w filler 109c-1012=w filler-1 110c-1012=w filler-2 111c-1013=w filler 112c-1013=w filler-1 113c-1013=w filler-2 114c-1013=w inserted 115c-1014=w filler 116c-1014=w filler-1 117c-1014=w filler-2 118c-1015=w filler 119c-1015=w filler-1 120c-1015=w filler-2 121delete from t3 where b='del-me'; 122alter table t3 add primary key(b); 123select b,filler from t3 where (b>='c-1011=w' and b<= 'c-1018=w') or 124b IN ('c-1019=w', 'c-1020=w', 'c-1021=w', 125'c-1022=w', 'c-1023=w', 'c-1024=w'); 126b filler 127c-1011=w filler 128c-1012=w filler 129c-1013=w filler 130c-1014=w filler 131c-1015=w filler 132c-1016=w filler 133c-1017=w filler 134c-1018=w filler 135c-1019=w filler 136c-1020=w filler 137c-1021=w filler 138c-1022=w filler 139c-1023=w filler 140c-1024=w filler 141select b,filler from t3 where (b>='c-1011=w' and b<= 'c-1020=w') or 142b IN ('c-1021=w', 'c-1022=w', 'c-1023=w'); 143b filler 144c-1011=w filler 145c-1012=w filler 146c-1013=w filler 147c-1014=w filler 148c-1015=w filler 149c-1016=w filler 150c-1017=w filler 151c-1018=w filler 152c-1019=w filler 153c-1020=w filler 154c-1021=w filler 155c-1022=w filler 156c-1023=w filler 157select b,filler from t3 where (b>='c-1011=w' and b<= 'c-1018=w') or 158b IN ('c-1019=w', 'c-1020=w') or 159(b>='c-1021=w' and b<= 'c-1023=w'); 160b filler 161c-1011=w filler 162c-1012=w filler 163c-1013=w filler 164c-1014=w filler 165c-1015=w filler 166c-1016=w filler 167c-1017=w filler 168c-1018=w filler 169c-1019=w filler 170c-1020=w filler 171c-1021=w filler 172c-1022=w filler 173c-1023=w filler 174create table t4 (a varchar(10), b int, c char(10), filler char(200), 175key idx1 (a, b, c)); 176insert into t4 (filler) select concat('NULL-', 15-a) from t2 order by a limit 15; 177insert into t4 (a,b,c,filler) 178select 'b-1',NULL,'c-1', concat('NULL-', 15-a) from t2 order by a limit 15; 179insert into t4 (a,b,c,filler) 180select 'b-1',NULL,'c-222', concat('NULL-', 15-a) from t2 order by a limit 15; 181insert into t4 (a,b,c,filler) 182select 'bb-1',NULL,'cc-2', concat('NULL-', 15-a) from t2 order by a limit 15; 183insert into t4 (a,b,c,filler) 184select 'zz-1',NULL,'cc-2', 'filler-data' from t2 order by a limit 500; 185explain 186select * from t4 where a IS NULL and b IS NULL and (c IS NULL or c='no-such-row1' 187 or c='no-such-row2'); 188id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1891 SIMPLE t4 NULL range idx1 idx1 29 NULL 10 100.00 Using index condition; Using MRR 190Warnings: 191Note 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'))) 192select * from t4 where a IS NULL and b IS NULL and (c IS NULL or c='no-such-row1' 193 or c='no-such-row2'); 194a b c filler 195NULL NULL NULL NULL-15 196NULL NULL NULL NULL-14 197NULL NULL NULL NULL-13 198NULL NULL NULL NULL-12 199NULL NULL NULL NULL-11 200NULL NULL NULL NULL-10 201NULL NULL NULL NULL-9 202NULL NULL NULL NULL-8 203NULL NULL NULL NULL-7 204NULL NULL NULL NULL-6 205NULL NULL NULL NULL-5 206NULL NULL NULL NULL-4 207NULL NULL NULL NULL-3 208NULL NULL NULL NULL-2 209NULL NULL NULL NULL-1 210explain 211select * from t4 where (a ='b-1' or a='bb-1') and b IS NULL and (c='c-1' or c='cc-2'); 212id select_type table partitions type possible_keys key key_len ref rows filtered Extra 2131 SIMPLE t4 NULL range idx1 idx1 29 NULL 21 100.00 Using index condition; Using MRR 214Warnings: 215Note 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'))) 216select * from t4 where (a ='b-1' or a='bb-1') and b IS NULL and (c='c-1' or c='cc-2'); 217a b c filler 218b-1 NULL c-1 NULL-15 219b-1 NULL c-1 NULL-14 220b-1 NULL c-1 NULL-13 221b-1 NULL c-1 NULL-12 222b-1 NULL c-1 NULL-11 223b-1 NULL c-1 NULL-10 224b-1 NULL c-1 NULL-9 225b-1 NULL c-1 NULL-8 226b-1 NULL c-1 NULL-7 227b-1 NULL c-1 NULL-6 228b-1 NULL c-1 NULL-5 229b-1 NULL c-1 NULL-4 230b-1 NULL c-1 NULL-3 231b-1 NULL c-1 NULL-2 232b-1 NULL c-1 NULL-1 233bb-1 NULL cc-2 NULL-15 234bb-1 NULL cc-2 NULL-14 235bb-1 NULL cc-2 NULL-13 236bb-1 NULL cc-2 NULL-12 237bb-1 NULL cc-2 NULL-11 238bb-1 NULL cc-2 NULL-10 239bb-1 NULL cc-2 NULL-9 240bb-1 NULL cc-2 NULL-8 241bb-1 NULL cc-2 NULL-7 242bb-1 NULL cc-2 NULL-6 243bb-1 NULL cc-2 NULL-5 244bb-1 NULL cc-2 NULL-4 245bb-1 NULL cc-2 NULL-3 246bb-1 NULL cc-2 NULL-2 247bb-1 NULL cc-2 NULL-1 248select * 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'); 249a b c filler 250b-1 NULL c-1 NULL-15 251b-1 NULL c-1 NULL-14 252b-1 NULL c-1 NULL-13 253b-1 NULL c-1 NULL-12 254b-1 NULL c-1 NULL-11 255b-1 NULL c-1 NULL-10 256b-1 NULL c-1 NULL-9 257b-1 NULL c-1 NULL-8 258b-1 NULL c-1 NULL-7 259b-1 NULL c-1 NULL-6 260b-1 NULL c-1 NULL-5 261b-1 NULL c-1 NULL-4 262b-1 NULL c-1 NULL-3 263b-1 NULL c-1 NULL-2 264b-1 NULL c-1 NULL-1 265bb-1 NULL cc-2 NULL-15 266bb-1 NULL cc-2 NULL-14 267bb-1 NULL cc-2 NULL-13 268bb-1 NULL cc-2 NULL-12 269bb-1 NULL cc-2 NULL-11 270bb-1 NULL cc-2 NULL-10 271bb-1 NULL cc-2 NULL-9 272bb-1 NULL cc-2 NULL-8 273bb-1 NULL cc-2 NULL-7 274bb-1 NULL cc-2 NULL-6 275bb-1 NULL cc-2 NULL-5 276bb-1 NULL cc-2 NULL-4 277bb-1 NULL cc-2 NULL-3 278bb-1 NULL cc-2 NULL-2 279bb-1 NULL cc-2 NULL-1 280drop table t1, t2, t3, t4; 281create table t1 (a int, b int not null,unique key (a,b),index(b)); 282insert 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); 283Warnings: 284Warning 1062 Duplicate entry '6-6' for key 'a' 285create table t2 like t1; 286insert into t2 select * from t1; 287alter 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)); 288select * from t1 where a is null; 289a b c 290NULL 7 0 291NULL 9 0 292NULL 9 0 293select * from t1 where (a is null or a > 0 and a < 3) and b > 7 limit 3; 294a b c 295NULL 9 0 296NULL 9 0 297select * from t1 where a is null and b=9 or a is null and b=7 limit 3; 298a b c 299NULL 7 0 300NULL 9 0 301NULL 9 0 302drop table t1, t2; 303CREATE TABLE t1 ( 304ID int(10) unsigned NOT NULL AUTO_INCREMENT, 305col1 int(10) unsigned DEFAULT NULL, 306key1 int(10) unsigned NOT NULL DEFAULT '0', 307key2 int(10) unsigned DEFAULT NULL, 308text1 text, 309text2 text, 310col2 smallint(6) DEFAULT '100', 311col3 enum('headers','bodyandsubject') NOT NULL DEFAULT 'bodyandsubject', 312col4 tinyint(3) unsigned NOT NULL DEFAULT '0', 313PRIMARY KEY (ID), 314KEY (key1), 315KEY (key2) 316) AUTO_INCREMENT=6 DEFAULT CHARSET=utf8; 317INSERT INTO t1 VALUES 318(1,NULL,1130,NULL,'Hello',NULL,100,'bodyandsubject',0), 319(2,NULL,1130,NULL,'bye',NULL,100,'bodyandsubject',0), 320(3,NULL,1130,NULL,'red',NULL,100,'bodyandsubject',0), 321(4,NULL,1130,NULL,'yellow',NULL,100,'bodyandsubject',0), 322(5,NULL,1130,NULL,'blue',NULL,100,'bodyandsubject',0); 323select * FROM t1 WHERE key1=1130 AND col1 IS NULL ORDER BY text1; 324ID col1 key1 key2 text1 text2 col2 col3 col4 3255 NULL 1130 NULL blue NULL 100 bodyandsubject 0 3262 NULL 1130 NULL bye NULL 100 bodyandsubject 0 3271 NULL 1130 NULL Hello NULL 100 bodyandsubject 0 3283 NULL 1130 NULL red NULL 100 bodyandsubject 0 3294 NULL 1130 NULL yellow NULL 100 bodyandsubject 0 330drop table t1; 331 332BUG#37851: Crash in test_if_skip_sort_order tab->select is zero 333 334CREATE TABLE t1 ( 335pk int(11) NOT NULL AUTO_INCREMENT, 336PRIMARY KEY (pk) 337); 338INSERT INTO t1 VALUES (1); 339CREATE TABLE t2 ( 340pk int(11) NOT NULL AUTO_INCREMENT, 341int_key int(11) DEFAULT NULL, 342PRIMARY KEY (pk), 343KEY int_key (int_key) 344); 345INSERT INTO t2 VALUES (1,1),(2,6),(3,0); 346EXPLAIN EXTENDED 347SELECT MIN(t1.pk) 348FROM t1 WHERE EXISTS ( 349SELECT t2.pk 350FROM t2 351WHERE t2.int_key IS NULL 352GROUP BY t2.pk 353); 354id select_type table partitions type possible_keys key key_len ref rows filtered Extra 3551 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 3562 SUBQUERY t2 NULL ref int_key int_key 5 const 1 100.00 Using index condition 357Warnings: 358Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 359Note 1003 /* select#1 */ select min(`test`.`t1`.`pk`) AS `MIN(t1.pk)` from `test`.`t1` where 0 360DROP TABLE t1, t2; 361# 362# BUG#42048 Discrepancy between MyISAM and Maria's ICP implementation 363# 364create table t0 (a int); 365insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 366create table t1 (a int, b char(20), filler char(200), key(a,b(10))); 367insert into t1 select A.a + 10*(B.a + 10*C.a), 'bbb','filler' from t0 A, t0 B, t0 C; 368update t1 set b=repeat(char(65+a), 20) where a < 25; 369This must show range + using index condition: 370explain select * from t1 where a < 10 and b = repeat(char(65+a), 20); 371id select_type table partitions type possible_keys key key_len ref rows filtered Extra 3721 SIMPLE t1 NULL range a a 5 NULL x x Using index condition; Using where 373Warnings: 374Note 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))) 375select * from t1 where a < 10 and b = repeat(char(65+a), 20); 376a b filler 3770 AAAAAAAAAAAAAAAAAAAA filler 3781 BBBBBBBBBBBBBBBBBBBB filler 3792 CCCCCCCCCCCCCCCCCCCC filler 3803 DDDDDDDDDDDDDDDDDDDD filler 3814 EEEEEEEEEEEEEEEEEEEE filler 3825 FFFFFFFFFFFFFFFFFFFF filler 3836 GGGGGGGGGGGGGGGGGGGG filler 3847 HHHHHHHHHHHHHHHHHHHH filler 3858 IIIIIIIIIIIIIIIIIIII filler 3869 JJJJJJJJJJJJJJJJJJJJ filler 387drop table t0,t1; 388# 389# BUG#41136: ORDER BY + range access: EXPLAIN shows "Using MRR" while MRR is actually not used 390# 391create table t0 (a int); 392insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 393create table t1 (a int, b int, key(a)); 394insert 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; 395This mustn't show "Using MRR": 396explain select * from t1 where a < 20 order by a; 397id select_type table partitions type possible_keys key key_len ref rows filtered Extra 3981 SIMPLE t1 NULL range a a 5 NULL 20 100.00 Using index condition 399Warnings: 400Note 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` 401drop table t0, t1; 402set @read_rnd_buffer_size_save= @@read_rnd_buffer_size; 403set read_rnd_buffer_size=64; 404create table t1(a int); 405insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 406create table t2(a char(8), b char(8), c char(8), filler char(100), key k1(a,b,c) ); 407insert into t2 select 408concat('a-', 1000 + A.a, '-a'), 409concat('b-', 1000 + B.a, '-b'), 410concat('c-', 1000 + C.a, '-c'), 411'filler' 412from t1 A, t1 B, t1 C; 413EXPLAIN select count(length(a) + length(filler)) 414from t2 force index (k1) 415where a>='a-1000-a' and a <'a-1001-a'; 416id select_type table partitions type possible_keys key key_len ref rows filtered Extra 4171 SIMPLE t2 NULL range k1 k1 9 NULL 92 100.00 Using index condition; Using MRR 418Warnings: 419Note 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')) 420select count(length(a) + length(filler)) 421from t2 force index (k1) 422where a>='a-1000-a' and a <'a-1001-a'; 423count(length(a) + length(filler)) 424100 425drop table t2; 426create table t2 (a char(100), b char(100), c char(100), d int, 427filler char(10), key(d), primary key (a,b,c)); 428insert into t2 select A.a, B.a, B.a, A.a, 'filler' from t1 A, t1 B; 429explain select * from t2 force index (d) where d < 10; 430id select_type table partitions type possible_keys key key_len ref rows filtered Extra 4311 SIMPLE t2 NULL range d d 5 NULL # 100.00 Using index condition; Using MRR 432Warnings: 433Note 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) 434drop table t2; 435drop table t1; 436set @@read_rnd_buffer_size= @read_rnd_buffer_size_save; 437create table t1 (f1 int not null, f2 int not null,f3 int not null, f4 char(1), primary key (f1,f2), key ix(f3)); 438select * from t1 where (f3>=5 and f3<=10) or (f3>=1 and f3<=4); 439f1 f2 f3 f4 4401 1 1 A 44110 10 10 A 4422 2 2 A 4433 3 3 A 4444 4 4 A 4455 5 5 A 4466 6 6 A 4477 7 7 A 4488 8 8 A 4499 9 9 A 450drop table t1; 451 452BUG#37977: Wrong result returned on GROUP BY + OR + Innodb 453 454CREATE TABLE t1 ( 455`pk` int(11) NOT NULL AUTO_INCREMENT, 456`int_nokey` int(11) NOT NULL, 457`int_key` int(11) NOT NULL, 458`date_key` date NOT NULL, 459`date_nokey` date NOT NULL, 460`time_key` time NOT NULL, 461`time_nokey` time NOT NULL, 462`datetime_key` datetime NOT NULL, 463`datetime_nokey` datetime NOT NULL, 464`varchar_key` varchar(5) DEFAULT NULL, 465`varchar_nokey` varchar(5) DEFAULT NULL, 466PRIMARY KEY (`pk`), 467KEY `int_key` (`int_key`), 468KEY `date_key` (`date_key`), 469KEY `time_key` (`time_key`), 470KEY `datetime_key` (`datetime_key`), 471KEY `varchar_key` (`varchar_key`) 472); 473INSERT IGNORE INTO t1 VALUES 474(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'), 475(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'), 476(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'), 477(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), 478(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'); 479Warnings: 480Warning 1264 Out of range value for column 'date_key' at row 2 481Warning 1264 Out of range value for column 'date_nokey' at row 2 482Warning 1264 Out of range value for column 'datetime_key' at row 2 483Warning 1264 Out of range value for column 'datetime_nokey' at row 2 484select pk from t1 WHERE `varchar_key` > 'kr' group by pk; 485pk 4861 4875 488select pk from t1 WHERE `int_nokey` IS NULL OR `varchar_key` > 'kr' group by pk; 489pk 4901 4915 492drop table t1; 493# 494# BUG#39447: Error with NOT NULL condition and LIMIT 1 495# 496CREATE TABLE t1 ( 497id int(11) NOT NULL, 498parent_id int(11) DEFAULT NULL, 499name varchar(10) DEFAULT NULL, 500PRIMARY KEY (id), 501KEY ind_parent_id (parent_id) 502); 503insert into t1 (id, parent_id, name) values 504(10,NULL,'A'), 505(20,10,'B'), 506(30,10,'C'), 507(40,NULL,'D'), 508(50,40,'E'), 509(60,40,'F'), 510(70,NULL,'J'); 511SELECT id FROM t1 WHERE parent_id IS NOT NULL ORDER BY id DESC LIMIT 1; 512id 51360 514This must show type=index, extra=Using where 515explain SELECT * FROM t1 WHERE parent_id IS NOT NULL ORDER BY id DESC LIMIT 1; 516id select_type table partitions type possible_keys key key_len ref rows filtered Extra 5171 SIMPLE t1 NULL index ind_parent_id PRIMARY 4 NULL 1 57.14 Using where 518Warnings: 519Note 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 520SELECT * FROM t1 WHERE parent_id IS NOT NULL ORDER BY id DESC LIMIT 1; 521id parent_id name 52260 40 F 523drop table t1; 524# 525# Bug#50381 "Assertion failing in handler.h:1283: 526# void COST_VECT::add_io(double, double)" 527# 528CREATE TABLE t1 ( 529c1 INT NOT NULL, 530c2 VARCHAR(1) DEFAULT NULL, 531PRIMARY KEY (c1) 532); 533CREATE TABLE t2 ( 534c1 INT NOT NULL, 535c2 VARCHAR(1) DEFAULT NULL, 536PRIMARY KEY (c1) 537); 538INSERT INTO t2 VALUES (10,'v'); 539INSERT INTO t2 VALUES (11,'r'); 540SELECT t1.c2 541FROM t2 STRAIGHT_JOIN t1 ON t1.c1 < t2.c1; 542c2 543DROP TABLE t1, t2; 544# 545# Bug#58463: Error Can't find record on SELECT with JOIN and ORDER BY 546# 547CREATE TABLE t1 ( 548pk INT NOT NULL, 549PRIMARY KEY (pk) 550) ENGINE=MyISAM; 551INSERT INTO t1 VALUES (2); 552CREATE TABLE t2 ( 553pk INT NOT NULL, 554i1 INT NOT NULL, 555i2 INT NOT NULL, 556c1 VARCHAR(1024) CHARACTER SET utf8, 557PRIMARY KEY (pk), 558KEY k1 (i1) 559); 560INSERT INTO t2 VALUES (3, 9, 1, NULL); 561EXPLAIN SELECT i1 562FROM t1 LEFT JOIN t2 ON t1.pk = t2.i2 563WHERE t2.i1 > 5 564AND t2.pk IS NULL 565ORDER BY i1; 566id select_type table partitions type possible_keys key key_len ref rows filtered Extra 5671 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 568Warnings: 569Note 1003 /* select#1 */ select '9' AS `i1` from dual where 0 order by '9' 570SELECT i1 571FROM t1 LEFT JOIN t2 ON t1.pk = t2.i2 572WHERE t2.i1 > 5 573AND t2.pk IS NULL 574ORDER BY i1; 575i1 576DROP TABLE t1, t2; 577# 578# Bug#12321461: CRASH IN DSMRR_IMPL::DSMRR_INIT ON SELECT STRAIGHT_JOIN 579# 580set @save_optimizer_switch = @@optimizer_switch; 581set optimizer_switch='block_nested_loop=off,batched_key_access=off'; 582CREATE TABLE t1 ( 583pk INTEGER, 584c1 VARCHAR(1) NOT NULL, 585PRIMARY KEY (pk) 586); 587CREATE TABLE t2 ( 588c1 VARCHAR(1) NOT NULL 589); 590INSERT INTO t2 VALUES ('v'), ('c'); 591EXPLAIN SELECT STRAIGHT_JOIN t1.c1 592FROM t1 RIGHT OUTER JOIN t2 ON t1.c1 = t2.c1 593WHERE t1.pk > 176; 594id select_type table partitions type possible_keys key key_len ref rows filtered Extra 5951 SIMPLE t2 NULL ALL NULL NULL NULL NULL 2 100.00 NULL 5961 SIMPLE t1 NULL range PRIMARY PRIMARY 4 NULL 1 100.00 Using index condition; Using where; Using MRR 597Warnings: 598Note 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)) 599SELECT STRAIGHT_JOIN t1.c1 600FROM t1 RIGHT OUTER JOIN t2 ON t1.c1 = t2.c1 601WHERE t1.pk > 176; 602c1 603DROP TABLE t1,t2; 604set optimizer_switch= @save_optimizer_switch; 605# 606# Bug#13249966 MRR: RANDOM ERROR DUE TO UNINITIALIZED RES WITH 607# SMALL READ_RND_BUFFER_SIZE 608# 609set @read_rnd_buffer_size_save= @@read_rnd_buffer_size; 610set read_rnd_buffer_size=1; 611select @@read_rnd_buffer_size; 612@@read_rnd_buffer_size 6131 614CREATE TABLE t1 ( 615i1 INTEGER NOT NULL, 616i2 INTEGER NOT NULL, 617KEY (i2) 618); 619INSERT INTO t1 VALUES (0,1),(1,2),(2,3); 620EXPLAIN SELECT i1 621FROM t1 622WHERE i2 > 2; 623id select_type table partitions type possible_keys key key_len ref rows filtered Extra 6241 SIMPLE t1 NULL range i2 i2 4 NULL 2 100.00 Using index condition 625Warnings: 626Note 1003 /* select#1 */ select `test`.`t1`.`i1` AS `i1` from `test`.`t1` where (`test`.`t1`.`i2` > 2) 627SELECT i1 628FROM t1 629WHERE i2 > 2; 630i1 6312 632DROP TABLE t1; 633set @@read_rnd_buffer_size= @read_rnd_buffer_size_save; 634select @@read_rnd_buffer_size; 635@@read_rnd_buffer_size 63679 637# 638# Bug 12365385 STRAIGHT_JOIN QUERY QUICKLY EXHAUSTS SYSTEM+VIRT. 639# MEMORY LEADING TO SYSTEM CRASH 640# 641CREATE TABLE ten (a INTEGER); 642INSERT INTO ten VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 643CREATE TABLE t1 ( 644pk INTEGER NOT NULL, 645i1 INTEGER NOT NULL, 646c1 VARCHAR(10) NOT NULL, 647PRIMARY KEY (pk) 648); 649INSERT INTO t1 650SELECT a, 1, 'MySQL' FROM ten; 651CREATE TABLE t2 ( 652pk INTEGER NOT NULL, 653c1 VARCHAR(10) NOT NULL, 654c2 varchar(10) NOT NULL, 655PRIMARY KEY (pk) 656); 657INSERT INTO t2 658SELECT a, 'MySQL', 'MySQL' FROM ten; 659CREATE TABLE t3 ( 660pk INTEGER NOT NULL, 661c1 VARCHAR(10) NOT NULL, 662PRIMARY KEY (pk) 663); 664INSERT INTO t3 665SELECT a, 'MySQL' FROM ten; 666CREATE TABLE t4 ( 667pk int(11) NOT NULL, 668c1_key varchar(10) CHARACTER SET utf8 NOT NULL, 669c2 varchar(10) NOT NULL, 670c3 varchar(10) NOT NULL, 671PRIMARY KEY (pk), 672KEY k1 (c1_key) 673); 674CREATE TABLE t5 ( 675pk INTEGER NOT NULL, 676c1 VARCHAR(10) NOT NULL, 677PRIMARY KEY (pk) 678); 679INSERT INTO t5 680SELECT a, 'MySQL' FROM ten; 681EXPLAIN SELECT STRAIGHT_JOIN * 682FROM 683(t1 LEFT JOIN 684(t2 LEFT JOIN 685(t3 LEFT OUTER JOIN t4 ON t3.c1 <= t4.c1_key) 686ON t2.c1 = t4.c3) 687ON t1.c1 = t4.c2) 688RIGHT OUTER JOIN t5 ON t2.c2 <= t5.c1 689WHERE t1.i1 = 1; 690id select_type table partitions type possible_keys key key_len ref rows filtered Extra 6911 SIMPLE t5 NULL ALL NULL NULL NULL NULL 10 100.00 NULL 6921 SIMPLE t1 NULL ALL NULL NULL NULL NULL 10 10.00 Using where; Using join buffer (Block Nested Loop) 6931 SIMPLE t2 NULL ALL NULL NULL NULL NULL 10 33.33 Using where; Using join buffer (Block Nested Loop) 6941 SIMPLE t3 NULL ALL NULL NULL NULL NULL 10 100.00 Using join buffer (Block Nested Loop) 6951 SIMPLE t4 NULL ALL k1 NULL NULL NULL 0 0.00 Range checked for each record (index map: 0x2) 696Warnings: 697Note 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`)) 698SELECT STRAIGHT_JOIN * 699FROM 700(t1 LEFT JOIN 701(t2 LEFT JOIN 702(t3 LEFT OUTER JOIN t4 ON t3.c1 <= t4.c1_key) 703ON t2.c1 = t4.c3) 704ON t1.c1 = t4.c2) 705RIGHT OUTER JOIN t5 ON t2.c2 <= t5.c1 706WHERE t1.i1 = 1; 707pk i1 c1 pk c1 c2 pk c1 pk c1_key c2 c3 pk c1 708DROP TABLE ten, t1, t2, t3, t4, t5; 709# 710# Bug 18172819 CRASH ON DSMRR_IMPL::CHOOSE_MRR_IMPL IN SQL/HANDLER.CC 711# 712set @big_tables_save= @@big_tables; 713set @optimizer_switch_save=@@optimizer_switch; 714set big_tables=ON; 715set optimizer_switch='derived_merge=off'; 716SELECT @@big_tables; 717@@big_tables 7181 719CREATE TABLE t1 ( 720i1 INTEGER NOT NULL, 721c1 VARCHAR(1) 722); 723INSERT INTO t1 VALUES (1,'a'), (2, NULL); 724CREATE TABLE t2 ( 725c1 VARCHAR(1), 726i1 INTEGER NOT NULL, 727KEY (c1) 728); 729INSERT INTO t2 VALUES ('a', 1), (NULL, 2); 730EXPLAIN SELECT * 731FROM 732( SELECT * FROM t1 ) AS d1 733WHERE d1.c1 IN ( SELECT c1 FROM t2 ) 734AND d1.c1 IS NULL 735ORDER BY d1.i1; 736id select_type table partitions type possible_keys key key_len ref rows filtered Extra 7371 PRIMARY <derived2> NULL ref <auto_key0> <auto_key0> 4 const 1 100.00 Using where; Using filesort 7381 PRIMARY t2 NULL ref c1 c1 4 d1.c1 1 100.00 Using index; FirstMatch(<derived2>) 7392 DERIVED t1 NULL ALL NULL NULL NULL NULL 2 100.00 NULL 740Warnings: 741Note 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` semi join (`test`.`t2`) where ((`test`.`t2`.`c1` = `d1`.`c1`) and isnull(`d1`.`c1`)) order by `d1`.`i1` 742SELECT * 743FROM 744( SELECT * FROM t1 ) AS d1 745WHERE d1.c1 IN ( SELECT c1 FROM t2 ) 746AND d1.c1 IS NULL 747ORDER BY d1.i1; 748i1 c1 749DROP TABLE t1, t2; 750set big_tables=@big_tables_save; 751set optimizer_switch=@optimizer_switch_save; 752set @@read_rnd_buffer_size= @read_rnd_buffer_size_save; 753set optimizer_switch=default; 754