1drop table if exists t0, t1, t2, t3; 2set @myisam_mrr_tmp=@@optimizer_switch; 3set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; 4set optimizer_switch='optimize_join_buffer_size=on'; 5set @mrr_buffer_size_save= @@mrr_buffer_size; 6set mrr_buffer_size=79; 7Warnings: 8Warning 1292 Truncated incorrect mrr_buffer_size value: '79' 9create table t1(a int); 10show create table t1; 11Table Create Table 12t1 CREATE TABLE `t1` ( 13 `a` int(11) DEFAULT NULL 14) ENGINE=MyISAM DEFAULT CHARSET=latin1 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 ( 19a char(8) not null, b char(8) not null, filler char(200), 20key(a) 21); 22insert into t3 select @a:=concat('c-', 1000+ A.a, '=w'), @a, 'filler' from t2 A; 23insert into t3 select concat('c-', 1000+A.a, '=w'), concat('c-', 2000+A.a, '=w'), 24'filler-1' from t2 A; 25insert into t3 select concat('c-', 1000+A.a, '=w'), concat('c-', 3000+A.a, '=w'), 26'filler-2' from t2 A; 27select a,filler from t3 where a >= 'c-9011=w'; 28a filler 29select a,filler from t3 where a >= 'c-1011=w' and a <= 'c-1015=w'; 30a filler 31c-1011=w filler 32c-1012=w filler 33c-1013=w filler 34c-1014=w filler 35c-1015=w filler 36c-1011=w filler-1 37c-1012=w filler-1 38c-1013=w filler-1 39c-1014=w filler-1 40c-1015=w filler-1 41c-1011=w filler-2 42c-1012=w filler-2 43c-1013=w filler-2 44c-1014=w filler-2 45c-1015=w filler-2 46select a,filler from t3 where (a>='c-1011=w' and a <= 'c-1013=w') or 47(a>='c-1014=w' and a <= 'c-1015=w'); 48a filler 49c-1011=w filler 50c-1012=w filler 51c-1013=w filler 52c-1014=w filler 53c-1015=w filler 54c-1011=w filler-1 55c-1012=w filler-1 56c-1013=w filler-1 57c-1014=w filler-1 58c-1015=w filler-1 59c-1011=w filler-2 60c-1012=w filler-2 61c-1013=w filler-2 62c-1014=w filler-2 63c-1015=w filler-2 64insert into t3 values ('c-1013=z', 'c-1013=z', 'err'); 65insert into t3 values ('a-1014=w', 'a-1014=w', 'err'); 66select a,filler from t3 where (a>='c-1011=w' and a <= 'c-1013=w') or 67(a>='c-1014=w' and a <= 'c-1015=w'); 68a filler 69c-1011=w filler 70c-1012=w filler 71c-1013=w filler 72c-1014=w filler 73c-1015=w filler 74c-1011=w filler-1 75c-1012=w filler-1 76c-1013=w filler-1 77c-1014=w filler-1 78c-1015=w filler-1 79c-1011=w filler-2 80c-1012=w filler-2 81c-1013=w filler-2 82c-1014=w filler-2 83c-1015=w filler-2 84delete from t3 where b in ('c-1013=z', 'a-1014=w'); 85select a,filler from t3 where a='c-1011=w' or a='c-1012=w' or a='c-1013=w' or 86a='c-1014=w' or a='c-1015=w'; 87a filler 88c-1011=w filler 89c-1012=w filler 90c-1013=w filler 91c-1014=w filler 92c-1015=w filler 93c-1011=w filler-1 94c-1012=w filler-1 95c-1013=w filler-1 96c-1014=w filler-1 97c-1015=w filler-1 98c-1011=w filler-2 99c-1012=w filler-2 100c-1013=w filler-2 101c-1014=w filler-2 102c-1015=w filler-2 103insert into t3 values ('c-1013=w', 'del-me', 'inserted'); 104select a,filler from t3 where a='c-1011=w' or a='c-1012=w' or a='c-1013=w' or 105a='c-1014=w' or a='c-1015=w'; 106a filler 107c-1011=w filler 108c-1012=w filler 109c-1013=w filler 110c-1014=w filler 111c-1015=w filler 112c-1011=w filler-1 113c-1012=w filler-1 114c-1013=w filler-1 115c-1014=w filler-1 116c-1015=w filler-1 117c-1011=w filler-2 118c-1012=w filler-2 119c-1013=w filler-2 120c-1014=w filler-2 121c-1015=w filler-2 122c-1013=w inserted 123delete from t3 where b='del-me'; 124alter table t3 add primary key(b); 125select b,filler from t3 where (b>='c-1011=w' and b<= 'c-1018=w') or 126b IN ('c-1019=w', 'c-1020=w', 'c-1021=w', 127'c-1022=w', 'c-1023=w', 'c-1024=w'); 128b filler 129c-1011=w filler 130c-1012=w filler 131c-1013=w filler 132c-1014=w filler 133c-1015=w filler 134c-1016=w filler 135c-1017=w filler 136c-1018=w filler 137c-1019=w filler 138c-1020=w filler 139c-1021=w filler 140c-1022=w filler 141c-1023=w filler 142c-1024=w filler 143select b,filler from t3 where (b>='c-1011=w' and b<= 'c-1020=w') or 144b IN ('c-1021=w', 'c-1022=w', 'c-1023=w'); 145b filler 146c-1011=w filler 147c-1012=w filler 148c-1013=w filler 149c-1014=w filler 150c-1015=w filler 151c-1016=w filler 152c-1017=w filler 153c-1018=w filler 154c-1019=w filler 155c-1020=w filler 156c-1021=w filler 157c-1022=w filler 158c-1023=w filler 159select b,filler from t3 where (b>='c-1011=w' and b<= 'c-1018=w') or 160b IN ('c-1019=w', 'c-1020=w') or 161(b>='c-1021=w' and b<= 'c-1023=w'); 162b filler 163c-1011=w filler 164c-1012=w filler 165c-1013=w filler 166c-1014=w filler 167c-1015=w filler 168c-1016=w filler 169c-1017=w filler 170c-1018=w filler 171c-1019=w filler 172c-1020=w filler 173c-1021=w filler 174c-1022=w filler 175c-1023=w filler 176drop table if exists t4; 177create table t4 (a varchar(10), b int, c char(10), filler char(200), 178key idx1 (a, b, c)); 179insert into t4 (filler) select concat('NULL-', 15-a) from t2 order by a limit 15; 180insert into t4 (a,b,c,filler) 181select 'b-1',NULL,'c-1', concat('NULL-', 15-a) from t2 order by a limit 15; 182insert into t4 (a,b,c,filler) 183select 'b-1',NULL,'c-222', concat('NULL-', 15-a) from t2 order by a limit 15; 184insert into t4 (a,b,c,filler) 185select 'bb-1',NULL,'cc-2', concat('NULL-', 15-a) from t2 order by a limit 15; 186insert into t4 (a,b,c,filler) 187select 'zz-1',NULL,'cc-2', 'filler-data' from t2 order by a limit 500; 188explain 189select * from t4 where a IS NULL and b IS NULL and (c IS NULL or c='no-such-row1' 190 or c='no-such-row2'); 191id select_type table type possible_keys key key_len ref rows Extra 1921 SIMPLE t4 range idx1 idx1 29 NULL 10 Using index condition; Rowid-ordered scan 193select * from t4 where a IS NULL and b IS NULL and (c IS NULL or c='no-such-row1' 194 or c='no-such-row2'); 195a b c filler 196NULL NULL NULL NULL-15 197NULL NULL NULL NULL-14 198NULL NULL NULL NULL-13 199NULL NULL NULL NULL-12 200NULL NULL NULL NULL-11 201NULL NULL NULL NULL-10 202NULL NULL NULL NULL-9 203NULL NULL NULL NULL-8 204NULL NULL NULL NULL-7 205NULL NULL NULL NULL-6 206NULL NULL NULL NULL-5 207NULL NULL NULL NULL-4 208NULL NULL NULL NULL-3 209NULL NULL NULL NULL-2 210NULL NULL NULL NULL-1 211explain 212select * from t4 where (a ='b-1' or a='bb-1') and b IS NULL and (c='c-1' or c='cc-2'); 213id select_type table type possible_keys key key_len ref rows Extra 2141 SIMPLE t4 range idx1 idx1 29 NULL 20 Using index condition; Rowid-ordered scan 215select * from t4 where (a ='b-1' or a='bb-1') and b IS NULL and (c='c-1' or c='cc-2'); 216a b c filler 217b-1 NULL c-1 NULL-15 218b-1 NULL c-1 NULL-14 219b-1 NULL c-1 NULL-13 220b-1 NULL c-1 NULL-12 221b-1 NULL c-1 NULL-11 222b-1 NULL c-1 NULL-10 223b-1 NULL c-1 NULL-9 224b-1 NULL c-1 NULL-8 225b-1 NULL c-1 NULL-7 226b-1 NULL c-1 NULL-6 227b-1 NULL c-1 NULL-5 228b-1 NULL c-1 NULL-4 229b-1 NULL c-1 NULL-3 230b-1 NULL c-1 NULL-2 231b-1 NULL c-1 NULL-1 232bb-1 NULL cc-2 NULL-15 233bb-1 NULL cc-2 NULL-14 234bb-1 NULL cc-2 NULL-13 235bb-1 NULL cc-2 NULL-12 236bb-1 NULL cc-2 NULL-11 237bb-1 NULL cc-2 NULL-10 238bb-1 NULL cc-2 NULL-9 239bb-1 NULL cc-2 NULL-8 240bb-1 NULL cc-2 NULL-7 241bb-1 NULL cc-2 NULL-6 242bb-1 NULL cc-2 NULL-5 243bb-1 NULL cc-2 NULL-4 244bb-1 NULL cc-2 NULL-3 245bb-1 NULL cc-2 NULL-2 246bb-1 NULL cc-2 NULL-1 247select * 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'); 248a b c filler 249b-1 NULL c-1 NULL-15 250b-1 NULL c-1 NULL-14 251b-1 NULL c-1 NULL-13 252b-1 NULL c-1 NULL-12 253b-1 NULL c-1 NULL-11 254b-1 NULL c-1 NULL-10 255b-1 NULL c-1 NULL-9 256b-1 NULL c-1 NULL-8 257b-1 NULL c-1 NULL-7 258b-1 NULL c-1 NULL-6 259b-1 NULL c-1 NULL-5 260b-1 NULL c-1 NULL-4 261b-1 NULL c-1 NULL-3 262b-1 NULL c-1 NULL-2 263b-1 NULL c-1 NULL-1 264bb-1 NULL cc-2 NULL-15 265bb-1 NULL cc-2 NULL-14 266bb-1 NULL cc-2 NULL-13 267bb-1 NULL cc-2 NULL-12 268bb-1 NULL cc-2 NULL-11 269bb-1 NULL cc-2 NULL-10 270bb-1 NULL cc-2 NULL-9 271bb-1 NULL cc-2 NULL-8 272bb-1 NULL cc-2 NULL-7 273bb-1 NULL cc-2 NULL-6 274bb-1 NULL cc-2 NULL-5 275bb-1 NULL cc-2 NULL-4 276bb-1 NULL cc-2 NULL-3 277bb-1 NULL cc-2 NULL-2 278bb-1 NULL cc-2 NULL-1 279drop table t1, t2, t3, t4; 280create table t1 (a int, b int not null,unique key (a,b),index(b)); 281insert 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); 282Warnings: 283Warning 1062 Duplicate entry '6-6' for key 'a' 284create table t2 like t1; 285insert into t2 select * from t1; 286alter 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)); 287select * from t1 where a is null; 288a b c 289NULL 7 0 290NULL 9 0 291NULL 9 0 292select * from t1 where (a is null or a > 0 and a < 3) and b > 7 limit 3; 293a b c 294NULL 9 0 295NULL 9 0 296select * from t1 where a is null and b=9 or a is null and b=7 limit 3; 297a b c 298NULL 7 0 299NULL 9 0 300NULL 9 0 301drop table t1, t2; 302set @@mrr_buffer_size= @mrr_buffer_size_save; 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) ENGINE=MyISAM 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 type possible_keys key key_len ref rows filtered Extra 3551 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 3562 SUBQUERY t2 ref int_key int_key 5 const 1 100.00 Using index condition 357Warnings: 358Note 1003 /* select#1 */ select min(`test`.`t1`.`pk`) AS `MIN(t1.pk)` from `test`.`t1` where 0 359DROP TABLE t1, t2; 360# 361# BUG#42048 Discrepancy between MyISAM and Maria's ICP implementation 362# 363create table t0 (a int); 364insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 365create table t1 (a int, b char(20), filler char(200), key(a,b(10))); 366insert into t1 select A.a + 10*(B.a + 10*C.a), 'bbb','filler' from t0 A, t0 B, t0 C; 367update t1 set b=repeat(char(65+a), 20) where a < 25; 368This must show range + using index condition: 369explain select * from t1 where a < 10 and b = repeat(char(65+a), 20); 370id select_type table type possible_keys key key_len ref rows Extra 3711 SIMPLE t1 range a a 5 NULL 19 Using index condition; Using where 372select * from t1 where a < 10 and b = repeat(char(65+a), 20); 373a b filler 3740 AAAAAAAAAAAAAAAAAAAA filler 3751 BBBBBBBBBBBBBBBBBBBB filler 3762 CCCCCCCCCCCCCCCCCCCC filler 3773 DDDDDDDDDDDDDDDDDDDD filler 3784 EEEEEEEEEEEEEEEEEEEE filler 3795 FFFFFFFFFFFFFFFFFFFF filler 3806 GGGGGGGGGGGGGGGGGGGG filler 3817 HHHHHHHHHHHHHHHHHHHH filler 3828 IIIIIIIIIIIIIIIIIIII filler 3839 JJJJJJJJJJJJJJJJJJJJ filler 384drop table t0,t1; 385# 386# BUG#41136: ORDER BY + range access: EXPLAIN shows "Using MRR" while MRR is actually not used 387# 388create table t0 (a int); 389insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 390create table t1 (a int, b int, key(a)); 391insert 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; 392This mustn't show "Using MRR": 393explain select * from t1 where a < 20 order by a; 394id select_type table type possible_keys key key_len ref rows Extra 3951 SIMPLE t1 range a a 5 NULL 20 Using index condition 396drop table t0, t1; 397# 398# Part of MWL#67: DS-MRR backport: add an @@optimizer_switch flag for 399# index_condition pushdown: 400# - engine_condition_pushdown does not affect ICP 401select @@optimizer_switch like '%index_condition_pushdown=on%'; 402@@optimizer_switch like '%index_condition_pushdown=on%' 4031 404create table t0 (a int); 405insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 406create table t1 (a int, b int, key(a)); 407insert 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; 408A query that will use ICP: 409explain select * from t1 where a < 20; 410id select_type table type possible_keys key key_len ref rows Extra 4111 SIMPLE t1 range a a 5 NULL 20 Using index condition; Rowid-ordered scan 412set @save_optimizer_switch=@@optimizer_switch; 413set optimizer_switch='index_condition_pushdown=off'; 414explain select * from t1 where a < 20; 415id select_type table type possible_keys key key_len ref rows Extra 4161 SIMPLE t1 range a a 5 NULL 20 Using where; Rowid-ordered scan 417set optimizer_switch='index_condition_pushdown=on'; 418explain select * from t1 where a < 20; 419id select_type table type possible_keys key key_len ref rows Extra 4201 SIMPLE t1 range a a 5 NULL 20 Using index condition; Rowid-ordered scan 421set optimizer_switch=@save_optimizer_switch; 422# 423# BUG#629684: Unreachable code in multi_range_read.cc in maria-5.3-dsmrr-cpk 424# 425delete from t0 where a > 2; 426insert into t0 values (NULL),(NULL); 427insert into t1 values (NULL, 1234), (NULL, 5678); 428set @save_join_cache_level=@@join_cache_level; 429set @@join_cache_level=6; 430explain 431select * from t0, t1 where t0.a<=>t1.a; 432id select_type table type possible_keys key key_len ref rows Extra 4331 SIMPLE t0 ALL NULL NULL NULL NULL 5 4341 SIMPLE t1 ref a a 5 test.t0.a 1 Using index condition(BKA); Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan 435select * from t0, t1 where t0.a<=>t1.a; 436a a b 4370 0 0 4381 1 1 4392 2 2 440NULL NULL 1234 441NULL NULL 1234 442NULL NULL 5678 443NULL NULL 5678 444set @@join_cache_level=@save_join_cache_level; 445drop table t0, t1; 446# 447# BUG#625841: Assertion `!table || (!table->read_set || bitmap_is_set 448# (table->read_set, field_index))' on REPLACE ... SELECT with MRR 449# 450create table t0 (a int); 451insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 452create table t1 ( 453key1 varchar(10), 454col1 char(255), col2 char(255), 455col3 char(244), col4 char(255), 456key(key1) 457); 458create table t2 like t1; 459insert into t1 460select 4611000+A.a+100*B.a + 10*C.a, 462'col1val', 'col2val', 463'col3val', 'col4val' 464from t0 A, t0 B, t0 C; 465REPLACE INTO t2(col2,col3,col4) 466SELECT col2,col3,col4 467FROM t1 468WHERE `key1` LIKE CONCAT( LEFT( '1' , 7 ) , '%' ) 469ORDER BY col1 LIMIT 7; 470drop table t0, t1, t2; 471# 472# BUG#670417: Diverging results in maria-5.3-mwl128-dsmrr-cpk with join buffer (incremental, BKA join) 473# 474set @save_join_cache_level = @@join_cache_level; 475set join_cache_level = 6; 476set @save_join_buffer_size=@@join_buffer_size; 477set join_buffer_size = 136; 478CREATE TABLE t1 ( 479pk int(11) NOT NULL AUTO_INCREMENT, 480col_int_key int(11) NOT NULL, 481col_varchar_key varchar(1) NOT NULL, 482col_varchar_nokey varchar(1) NOT NULL, 483PRIMARY KEY (pk), 484KEY col_varchar_key (col_varchar_key,col_int_key) 485); 486INSERT INTO t1 VALUES 487(10,8,'v','v'),(11,8,'f','f'), (12,5,'v','v'), 488(13,8,'s','s'),(14,8,'a','a'),(15,6,'p','p'), 489(16,7,'z','z'),(17,2,'a','a'),(18,5,'h','h'), 490(19,7,'h','h'),(20,2,'v','v'),(21,9,'v','v'), 491(22,142,'b','b'),(23,3,'y','y'),(24,0,'v','v'), 492(25,3,'m','m'),(26,5,'z','z'),(27,9,'n','n'), 493(28,1,'d','d'),(29,107,'a','a'); 494INSERT INTO t1 VALUES 495(110,8,'v','v'),(111,8,'f','f'), (112,5,'v','v'), 496(113,8,'s','s'),(114,8,'a','a'),(115,6,'p','p'), 497(116,7,'z','z'),(117,2,'a','a'),(118,5,'h','h'), 498(119,7,'h','h'),(120,2,'v','v'),(121,9,'v','v'), 499(122,142,'b','b'),(123,3,'y','y'),(124,0,'v','v'), 500(125,3,'m','m'),(126,5,'z','z'),(127,9,'n','n'), 501(128,1,'d','d'),(129,107,'a','a'); 502SELECT COUNT(*) 503FROM 504t1 AS table2, t1 AS table3 505where 506table3.col_varchar_key = table2.col_varchar_key AND 507table3.col_varchar_key = table2.col_varchar_nokey AND 508table3.pk<>0; 509COUNT(*) 510200 511EXPLAIN SELECT COUNT(*) 512FROM 513t1 AS table2, t1 AS table3 514where 515table3.col_varchar_key = table2.col_varchar_key AND 516table3.col_varchar_key = table2.col_varchar_nokey AND 517table3.pk<>0; 518id select_type table type possible_keys key key_len ref rows Extra 5191 SIMPLE table2 ALL col_varchar_key NULL NULL NULL 40 Using where 5201 SIMPLE table3 ref PRIMARY,col_varchar_key col_varchar_key 3 test.table2.col_varchar_key 5 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan 521set join_cache_level= @save_join_cache_level; 522set join_buffer_size= @save_join_buffer_size; 523drop table t1; 524# 525# BUG#730133: Wrong result with jkl = 7, BKA, ICP in maria-5.3 + compound index 526# 527set @tmp_730133_jcl= @@join_cache_level; 528set join_cache_level = 7; 529set @tmp_730133_os= @@optimizer_switch; 530set optimizer_switch= 'join_cache_hashed=off,join_cache_bka=on,index_condition_pushdown=on,optimize_join_buffer_size=on'; 531CREATE TABLE t1 (f1 int, f2 int, f3 int, f4 int, f5 int, KEY (f4,f3)); 532INSERT IGNORE INTO t1 VALUES ('2','9','5','0','0'),('4','7','0','0','0'), 533('6','97','190','0','0'),('7','3','6','0','0'),('11','101','186','0','0'), 534('14','194','226','0','0'),('15','148','133','0','0'), 535('16','9','6','0','0'),('17','9','3','0','0'),('18','1','8','0','0'), 536('19','1','5','0','0'),('20','5','7','0','0'); 537explain 538SELECT COUNT(alias2.f2) 539FROM 540t1 STRAIGHT_JOIN 541t1 AS alias3 STRAIGHT_JOIN 542t1 AS alias2 FORCE KEY (f4) 543WHERE 544alias2.f4=alias3.f5 AND 545alias2.f3 > alias3.f1; 546id select_type table type possible_keys key key_len ref rows Extra 5471 SIMPLE t1 index NULL f4 10 NULL 12 Using index 5481 SIMPLE alias3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) 5491 SIMPLE alias2 ref f4 f4 5 test.alias3.f5 2 Using index condition(BKA); Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan 550SELECT COUNT(alias2.f2) 551FROM 552t1 STRAIGHT_JOIN 553t1 AS alias3 STRAIGHT_JOIN 554t1 AS alias2 FORCE KEY (f4) 555WHERE 556alias2.f4=alias3.f5 AND 557alias2.f3 > alias3.f1; 558COUNT(alias2.f2) 559768 560set @@join_cache_level= @tmp_730133_jcl; 561set @@optimizer_switch= @tmp_730133_os; 562drop table t1; 563# 564# Test of MRR handler counters 565# 566flush status; 567show status like 'Handler_mrr%'; 568Variable_name Value 569Handler_mrr_init 0 570Handler_mrr_key_refills 0 571Handler_mrr_rowid_refills 0 572create table t0 (a int); 573insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 574create table t1 (a int, b int, filler char(200), key(a)); 575insert into t1 576select A.a+10*B.a+100*C.a+1000*D.a, 123,'filler' from t0 A, t0 B, t0 C, t0 D; 577explain select sum(b) from t1 where a < 10; 578id select_type table type possible_keys key key_len ref rows Extra 5791 SIMPLE t1 range a a 5 NULL 9 Using index condition; Rowid-ordered scan 580# This should show one MRR scan and no re-fills: 581flush status; 582select sum(b) from t1 where a < 10; 583sum(b) 5841230 585show status like 'handler_mrr%'; 586Variable_name Value 587Handler_mrr_init 1 588Handler_mrr_key_refills 0 589Handler_mrr_rowid_refills 0 590set @mrr_buffer_size_save= @@mrr_buffer_size; 591set mrr_buffer_size=128; 592explain select sum(b) from t1 where a < 1600; 593id select_type table type possible_keys key key_len ref rows Extra 5941 SIMPLE t1 range a a 5 NULL 1380 Using index condition; Rowid-ordered scan 595# This should show one MRR scan and one extra rowid sort: 596flush status; 597select sum(b) from t1 where a < 1600; 598sum(b) 599196800 600show status like 'handler_mrr%'; 601Variable_name Value 602Handler_mrr_init 1 603Handler_mrr_key_refills 0 604Handler_mrr_rowid_refills 1 605set @@mrr_buffer_size= @mrr_buffer_size_save; 606#Now, let's check BKA: 607set @join_cache_level_save= @@join_cache_level; 608set @join_buffer_size_save= @@join_buffer_size; 609set join_cache_level=6; 610explain select sum(t1.b) from t0,t1 where t0.a=t1.a; 611id select_type table type possible_keys key key_len ref rows Extra 6121 SIMPLE t0 ALL NULL NULL NULL NULL 10 Using where 6131 SIMPLE t1 ref a a 5 test.t0.a 1 Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan 614flush status; 615select sum(t1.b) from t0,t1 where t0.a=t1.a; 616sum(t1.b) 6171230 618show status like 'handler_mrr%'; 619Variable_name Value 620Handler_mrr_init 1 621Handler_mrr_key_refills 1 622Handler_mrr_rowid_refills 1 623set join_buffer_size=10; 624explain select sum(t1.b) from t0,t1 where t0.a=t1.a; 625id select_type table type possible_keys key key_len ref rows Extra 6261 SIMPLE t0 ALL NULL NULL NULL NULL 10 Using where 6271 SIMPLE t1 ref a a 5 test.t0.a 1 Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan 628flush status; 629select sum(t1.b) from t0,t1 where t0.a=t1.a; 630sum(t1.b) 6311230 632show status like 'handler_mrr%'; 633Variable_name Value 634Handler_mrr_init ok 635Handler_mrr_key_refills ok 636Handler_mrr_rowid_refills ok 637set join_cache_level= @join_cache_level_save; 638set join_buffer_size= @join_buffer_size_save; 639drop table t0, t1; 640set optimizer_switch= @myisam_mrr_tmp; 641