path: root/mysql-test/main/myisam_mrr.result

drop table if exists t0, t1, t2, t3;
set @myisam_mrr_tmp=@@optimizer_switch;
set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on';
set optimizer_switch='optimize_join_buffer_size=off';
set @mrr_buffer_size_save= @@mrr_buffer_size;
set mrr_buffer_size=79;
Warnings:
Warning	1292	Truncated incorrect mrr_buffer_size value: '79'
create table t1(a int);
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t2(a int);
insert into t2 select A.a + 10*(B.a + 10*C.a) from t1 A, t1 B, t1 C;
create table t3 (
a char(8) not null, b char(8) not null, filler char(200),
key(a)
);
insert into t3 select @a:=concat('c-', 1000+ A.a, '=w'), @a, 'filler' from t2 A;
insert into t3 select concat('c-', 1000+A.a, '=w'), concat('c-', 2000+A.a, '=w'), 
'filler-1' from t2 A;
insert into t3 select concat('c-', 1000+A.a, '=w'), concat('c-', 3000+A.a, '=w'), 
'filler-2' from t2 A;
select a,filler from t3 where a >= 'c-9011=w';
a	filler
select a,filler from t3 where a >= 'c-1011=w' and a <= 'c-1015=w';
a	filler
c-1011=w	filler
c-1012=w	filler
c-1013=w	filler
c-1014=w	filler
c-1015=w	filler
c-1011=w	filler-1
c-1012=w	filler-1
c-1013=w	filler-1
c-1014=w	filler-1
c-1015=w	filler-1
c-1011=w	filler-2
c-1012=w	filler-2
c-1013=w	filler-2
c-1014=w	filler-2
c-1015=w	filler-2
select a,filler from t3 where (a>='c-1011=w' and a <= 'c-1013=w') or
(a>='c-1014=w' and a <= 'c-1015=w');
a	filler
c-1011=w	filler
c-1012=w	filler
c-1013=w	filler
c-1014=w	filler
c-1015=w	filler
c-1011=w	filler-1
c-1012=w	filler-1
c-1013=w	filler-1
c-1014=w	filler-1
c-1015=w	filler-1
c-1011=w	filler-2
c-1012=w	filler-2
c-1013=w	filler-2
c-1014=w	filler-2
c-1015=w	filler-2
insert into t3 values ('c-1013=z', 'c-1013=z', 'err');
insert into t3 values ('a-1014=w', 'a-1014=w', 'err');
select a,filler from t3 where (a>='c-1011=w' and a <= 'c-1013=w') or
(a>='c-1014=w' and a <= 'c-1015=w');
a	filler
c-1011=w	filler
c-1012=w	filler
c-1013=w	filler
c-1014=w	filler
c-1015=w	filler
c-1011=w	filler-1
c-1012=w	filler-1
c-1013=w	filler-1
c-1014=w	filler-1
c-1015=w	filler-1
c-1011=w	filler-2
c-1012=w	filler-2
c-1013=w	filler-2
c-1014=w	filler-2
c-1015=w	filler-2
delete from t3 where b in ('c-1013=z', 'a-1014=w');
select a,filler from t3 where a='c-1011=w' or a='c-1012=w' or a='c-1013=w' or
a='c-1014=w' or a='c-1015=w';
a	filler
c-1011=w	filler
c-1012=w	filler
c-1013=w	filler
c-1014=w	filler
c-1015=w	filler
c-1011=w	filler-1
c-1012=w	filler-1
c-1013=w	filler-1
c-1014=w	filler-1
c-1015=w	filler-1
c-1011=w	filler-2
c-1012=w	filler-2
c-1013=w	filler-2
c-1014=w	filler-2
c-1015=w	filler-2
insert into t3 values ('c-1013=w', 'del-me', 'inserted');
select a,filler from t3 where a='c-1011=w' or a='c-1012=w' or a='c-1013=w' or
a='c-1014=w' or a='c-1015=w';
a	filler
c-1011=w	filler
c-1012=w	filler
c-1013=w	filler
c-1014=w	filler
c-1015=w	filler
c-1011=w	filler-1
c-1012=w	filler-1
c-1013=w	filler-1
c-1014=w	filler-1
c-1015=w	filler-1
c-1011=w	filler-2
c-1012=w	filler-2
c-1013=w	filler-2
c-1014=w	filler-2
c-1015=w	filler-2
c-1013=w	inserted
delete from t3 where b='del-me';
alter table t3 add primary key(b);
select b,filler from t3 where (b>='c-1011=w' and b<= 'c-1018=w') or 
b IN ('c-1019=w', 'c-1020=w', 'c-1021=w', 
'c-1022=w', 'c-1023=w', 'c-1024=w');
b	filler
c-1011=w	filler
c-1012=w	filler
c-1013=w	filler
c-1014=w	filler
c-1015=w	filler
c-1016=w	filler
c-1017=w	filler
c-1018=w	filler
c-1019=w	filler
c-1020=w	filler
c-1021=w	filler
c-1022=w	filler
c-1023=w	filler
c-1024=w	filler
select b,filler from t3 where (b>='c-1011=w' and b<= 'c-1020=w') or 
b IN ('c-1021=w', 'c-1022=w', 'c-1023=w');
b	filler
c-1011=w	filler
c-1012=w	filler
c-1013=w	filler
c-1014=w	filler
c-1015=w	filler
c-1016=w	filler
c-1017=w	filler
c-1018=w	filler
c-1019=w	filler
c-1020=w	filler
c-1021=w	filler
c-1022=w	filler
c-1023=w	filler
select b,filler from t3 where (b>='c-1011=w' and b<= 'c-1018=w') or 
b IN ('c-1019=w', 'c-1020=w') or 
(b>='c-1021=w' and b<= 'c-1023=w');
b	filler
c-1011=w	filler
c-1012=w	filler
c-1013=w	filler
c-1014=w	filler
c-1015=w	filler
c-1016=w	filler
c-1017=w	filler
c-1018=w	filler
c-1019=w	filler
c-1020=w	filler
c-1021=w	filler
c-1022=w	filler
c-1023=w	filler
drop table if exists t4;
create table t4 (a varchar(10), b int, c char(10), filler char(200),
key idx1 (a, b, c));
insert into t4 (filler) select concat('NULL-', 15-a) from t2 order by a limit 15;
insert into t4 (a,b,c,filler) 
select 'b-1',NULL,'c-1', concat('NULL-', 15-a) from t2 order by a limit 15;
insert into t4 (a,b,c,filler) 
select 'b-1',NULL,'c-222', concat('NULL-', 15-a) from t2 order by a limit 15;
insert into t4 (a,b,c,filler) 
select 'bb-1',NULL,'cc-2', concat('NULL-', 15-a) from t2 order by a limit 15;
insert into t4 (a,b,c,filler) 
select 'zz-1',NULL,'cc-2', 'filler-data' from t2 order by a limit 500;
explain 
select * from t4 where a IS NULL and b IS NULL and (c IS NULL or c='no-such-row1'
                                                      or c='no-such-row2');
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t4	range	idx1	idx1	29	NULL	10	Using index condition; Using where; Rowid-ordered scan
select * from t4 where a IS NULL and b IS NULL and (c IS NULL or c='no-such-row1'
                                                    or c='no-such-row2');
a	b	c	filler
NULL	NULL	NULL	NULL-15
NULL	NULL	NULL	NULL-14
NULL	NULL	NULL	NULL-13
NULL	NULL	NULL	NULL-12
NULL	NULL	NULL	NULL-11
NULL	NULL	NULL	NULL-10
NULL	NULL	NULL	NULL-9
NULL	NULL	NULL	NULL-8
NULL	NULL	NULL	NULL-7
NULL	NULL	NULL	NULL-6
NULL	NULL	NULL	NULL-5
NULL	NULL	NULL	NULL-4
NULL	NULL	NULL	NULL-3
NULL	NULL	NULL	NULL-2
NULL	NULL	NULL	NULL-1
explain 
select * from t4 where (a ='b-1' or a='bb-1') and b IS NULL and (c='c-1' or c='cc-2');
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t4	range	idx1	idx1	29	NULL	21	Using index condition; Using where; Rowid-ordered scan
select * from t4 where (a ='b-1' or a='bb-1') and b IS NULL and (c='c-1' or c='cc-2');
a	b	c	filler
b-1	NULL	c-1	NULL-15
b-1	NULL	c-1	NULL-14
b-1	NULL	c-1	NULL-13
b-1	NULL	c-1	NULL-12
b-1	NULL	c-1	NULL-11
b-1	NULL	c-1	NULL-10
b-1	NULL	c-1	NULL-9
b-1	NULL	c-1	NULL-8
b-1	NULL	c-1	NULL-7
b-1	NULL	c-1	NULL-6
b-1	NULL	c-1	NULL-5
b-1	NULL	c-1	NULL-4
b-1	NULL	c-1	NULL-3
b-1	NULL	c-1	NULL-2
b-1	NULL	c-1	NULL-1
bb-1	NULL	cc-2	NULL-15
bb-1	NULL	cc-2	NULL-14
bb-1	NULL	cc-2	NULL-13
bb-1	NULL	cc-2	NULL-12
bb-1	NULL	cc-2	NULL-11
bb-1	NULL	cc-2	NULL-10
bb-1	NULL	cc-2	NULL-9
bb-1	NULL	cc-2	NULL-8
bb-1	NULL	cc-2	NULL-7
bb-1	NULL	cc-2	NULL-6
bb-1	NULL	cc-2	NULL-5
bb-1	NULL	cc-2	NULL-4
bb-1	NULL	cc-2	NULL-3
bb-1	NULL	cc-2	NULL-2
bb-1	NULL	cc-2	NULL-1
select * 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');
a	b	c	filler
b-1	NULL	c-1	NULL-15
b-1	NULL	c-1	NULL-14
b-1	NULL	c-1	NULL-13
b-1	NULL	c-1	NULL-12
b-1	NULL	c-1	NULL-11
b-1	NULL	c-1	NULL-10
b-1	NULL	c-1	NULL-9
b-1	NULL	c-1	NULL-8
b-1	NULL	c-1	NULL-7
b-1	NULL	c-1	NULL-6
b-1	NULL	c-1	NULL-5
b-1	NULL	c-1	NULL-4
b-1	NULL	c-1	NULL-3
b-1	NULL	c-1	NULL-2
b-1	NULL	c-1	NULL-1
bb-1	NULL	cc-2	NULL-15
bb-1	NULL	cc-2	NULL-14
bb-1	NULL	cc-2	NULL-13
bb-1	NULL	cc-2	NULL-12
bb-1	NULL	cc-2	NULL-11
bb-1	NULL	cc-2	NULL-10
bb-1	NULL	cc-2	NULL-9
bb-1	NULL	cc-2	NULL-8
bb-1	NULL	cc-2	NULL-7
bb-1	NULL	cc-2	NULL-6
bb-1	NULL	cc-2	NULL-5
bb-1	NULL	cc-2	NULL-4
bb-1	NULL	cc-2	NULL-3
bb-1	NULL	cc-2	NULL-2
bb-1	NULL	cc-2	NULL-1
drop table t1, t2, t3, t4;
create table t1 (a int, b int not null,unique key (a,b),index(b));
insert 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);
Warnings:
Warning	1062	Duplicate entry '6-6' for key 'a'
create table t2 like t1;
insert into t2 select * from t1;
alter 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));
select * from t1 where a is null;
a	b	c
NULL	7	0
NULL	9	0
NULL	9	0
select * from t1 where (a is null or a > 0 and a < 3) and b > 7 limit 3;
a	b	c
NULL	9	0
NULL	9	0
select * from t1 where a is null and b=9 or a is null and b=7 limit 3;
a	b	c
NULL	7	0
NULL	9	0
NULL	9	0
drop table t1, t2;
set @@mrr_buffer_size= @mrr_buffer_size_save;
CREATE TABLE t1 (
ID int(10) unsigned NOT NULL AUTO_INCREMENT,
col1 int(10) unsigned DEFAULT NULL,
key1 int(10) unsigned NOT NULL DEFAULT '0',
key2 int(10) unsigned DEFAULT NULL,
text1 text,
text2 text,
col2 smallint(6) DEFAULT '100',
col3 enum('headers','bodyandsubject') NOT NULL DEFAULT 'bodyandsubject',
col4 tinyint(3) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (ID),
KEY (key1),
KEY (key2)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
INSERT INTO t1 VALUES
(1,NULL,1130,NULL,'Hello',NULL,100,'bodyandsubject',0),
(2,NULL,1130,NULL,'bye',NULL,100,'bodyandsubject',0),
(3,NULL,1130,NULL,'red',NULL,100,'bodyandsubject',0),
(4,NULL,1130,NULL,'yellow',NULL,100,'bodyandsubject',0),
(5,NULL,1130,NULL,'blue',NULL,100,'bodyandsubject',0);
select * FROM t1 WHERE key1=1130 AND col1 IS NULL ORDER BY text1;
ID	col1	key1	key2	text1	text2	col2	col3	col4
5	NULL	1130	NULL	blue	NULL	100	bodyandsubject	0
2	NULL	1130	NULL	bye	NULL	100	bodyandsubject	0
1	NULL	1130	NULL	Hello	NULL	100	bodyandsubject	0
3	NULL	1130	NULL	red	NULL	100	bodyandsubject	0
4	NULL	1130	NULL	yellow	NULL	100	bodyandsubject	0
drop table t1;

BUG#37851: Crash in test_if_skip_sort_order tab->select is zero

CREATE TABLE t1 (
pk int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (pk)
);
INSERT INTO t1 VALUES (1);
CREATE TABLE t2 (
pk int(11) NOT NULL AUTO_INCREMENT,
int_key int(11) DEFAULT NULL,
PRIMARY KEY (pk),
KEY int_key (int_key)
);
INSERT INTO t2 VALUES (1,1),(2,6),(3,0);
EXPLAIN EXTENDED
SELECT MIN(t1.pk)
FROM t1 WHERE EXISTS (
SELECT t2.pk
FROM t2
WHERE t2.int_key IS NULL
GROUP BY t2.pk
);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
2	SUBQUERY	t2	ref	int_key	int_key	5	const	1	100.00	Using index condition
Warnings:
Note	1003	/* select#1 */ select min(`test`.`t1`.`pk`) AS `MIN(t1.pk)` from `test`.`t1` where 0
DROP TABLE t1, t2;
#
# BUG#42048 Discrepancy between MyISAM and Maria's ICP implementation
#
create table t0 (a int);
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t1 (a int, b char(20), filler char(200), key(a,b(10)));
insert into t1 select A.a + 10*(B.a + 10*C.a), 'bbb','filler' from t0 A, t0 B, t0 C;
update t1 set b=repeat(char(65+a), 20) where a < 25;
This must show range + using index condition:
explain select * from t1 where a < 10 and b = repeat(char(65+a), 20);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	a	a	5	NULL	19	Using index condition; Using where
select * from t1 where a < 10 and b = repeat(char(65+a), 20);
a	b	filler
0	AAAAAAAAAAAAAAAAAAAA	filler
1	BBBBBBBBBBBBBBBBBBBB	filler
2	CCCCCCCCCCCCCCCCCCCC	filler
3	DDDDDDDDDDDDDDDDDDDD	filler
4	EEEEEEEEEEEEEEEEEEEE	filler
5	FFFFFFFFFFFFFFFFFFFF	filler
6	GGGGGGGGGGGGGGGGGGGG	filler
7	HHHHHHHHHHHHHHHHHHHH	filler
8	IIIIIIIIIIIIIIIIIIII	filler
9	JJJJJJJJJJJJJJJJJJJJ	filler
drop table t0,t1;
#
# BUG#41136: ORDER BY + range access: EXPLAIN shows "Using MRR" while MRR is actually not used
#
create table t0 (a int);
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t1 (a int, b int, key(a));
insert 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;
This mustn't show "Using MRR":
explain select * from t1 where a < 20  order by a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	a	a	5	NULL	20	Using index condition
drop table t0, t1;
#
# Part of MWL#67: DS-MRR backport: add an @@optimizer_switch flag for
# index_condition pushdown: 
#   - engine_condition_pushdown does not affect ICP
select @@optimizer_switch like '%index_condition_pushdown=on%';
@@optimizer_switch like '%index_condition_pushdown=on%'
1
create table t0 (a int);
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t1 (a int, b int, key(a));
insert 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;
A query that will use ICP: 
explain select * from t1 where a < 20;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	a	a	5	NULL	20	Using index condition; Rowid-ordered scan
set @save_optimizer_switch=@@optimizer_switch;
set optimizer_switch='index_condition_pushdown=off';
explain select * from t1 where a < 20;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	a	a	5	NULL	20	Using where; Rowid-ordered scan
set optimizer_switch='index_condition_pushdown=on';
explain select * from t1 where a < 20;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	a	a	5	NULL	20	Using index condition; Rowid-ordered scan
set optimizer_switch=@save_optimizer_switch;
# 
# BUG#629684: Unreachable code in multi_range_read.cc in maria-5.3-dsmrr-cpk
#
delete from t0 where a > 2;
insert into t0 values (NULL),(NULL);
insert into t1 values (NULL, 1234), (NULL, 5678);
set @save_join_cache_level=@@join_cache_level;
set @@join_cache_level=6;
explain 
select * from t0, t1 where t0.a<=>t1.a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	5	
1	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
select * from t0, t1 where t0.a<=>t1.a;
a	a	b
0	0	0
1	1	1
2	2	2
NULL	NULL	1234
NULL	NULL	1234
NULL	NULL	5678
NULL	NULL	5678
set @@join_cache_level=@save_join_cache_level;
drop table t0, t1;
#
# BUG#625841: Assertion `!table || (!table->read_set || bitmap_is_set
#             (table->read_set, field_index))' on REPLACE ... SELECT with MRR
#
create table t0 (a int);
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t1 (
key1 varchar(10),
col1 char(255), col2 char(255),
col3 char(244), col4 char(255),
key(key1)
);
create table t2 like t1;
insert into t1
select
1000+A.a+100*B.a + 10*C.a,
'col1val', 'col2val',
'col3val', 'col4val'
from t0 A, t0 B, t0 C;
REPLACE INTO t2(col2,col3,col4)
SELECT col2,col3,col4
FROM t1
WHERE `key1` LIKE CONCAT( LEFT( '1' , 7 ) , '%' )
ORDER BY col1 LIMIT 7;
drop table t0, t1, t2;
#
# BUG#670417: Diverging results in maria-5.3-mwl128-dsmrr-cpk with join buffer (incremental, BKA join)
#
set @save_join_cache_level = @@join_cache_level;
set join_cache_level = 6;
set @save_join_buffer_size=@@join_buffer_size;
set join_buffer_size = 136;
CREATE TABLE t1 (
pk int(11) NOT NULL AUTO_INCREMENT,
col_int_key int(11) NOT NULL,
col_varchar_key varchar(1) NOT NULL,
col_varchar_nokey varchar(1) NOT NULL,
PRIMARY KEY (pk),
KEY col_varchar_key (col_varchar_key,col_int_key)
);
INSERT INTO t1 VALUES 
(10,8,'v','v'),(11,8,'f','f'), (12,5,'v','v'),
(13,8,'s','s'),(14,8,'a','a'),(15,6,'p','p'),
(16,7,'z','z'),(17,2,'a','a'),(18,5,'h','h'),
(19,7,'h','h'),(20,2,'v','v'),(21,9,'v','v'),
(22,142,'b','b'),(23,3,'y','y'),(24,0,'v','v'),
(25,3,'m','m'),(26,5,'z','z'),(27,9,'n','n'),
(28,1,'d','d'),(29,107,'a','a');
INSERT INTO t1 VALUES 
(110,8,'v','v'),(111,8,'f','f'), (112,5,'v','v'),
(113,8,'s','s'),(114,8,'a','a'),(115,6,'p','p'),
(116,7,'z','z'),(117,2,'a','a'),(118,5,'h','h'),
(119,7,'h','h'),(120,2,'v','v'),(121,9,'v','v'),
(122,142,'b','b'),(123,3,'y','y'),(124,0,'v','v'),
(125,3,'m','m'),(126,5,'z','z'),(127,9,'n','n'),
(128,1,'d','d'),(129,107,'a','a');
SELECT COUNT(*) 
FROM 
t1 AS table2, t1 AS table3 
where 
table3.col_varchar_key = table2.col_varchar_key AND 
table3.col_varchar_key = table2.col_varchar_nokey AND 
table3.pk<>0;
COUNT(*)
200
EXPLAIN SELECT COUNT(*) 
FROM 
t1 AS table2, t1 AS table3 
where 
table3.col_varchar_key = table2.col_varchar_key AND 
table3.col_varchar_key = table2.col_varchar_nokey AND 
table3.pk<>0;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	table2	ALL	col_varchar_key	NULL	NULL	NULL	40	Using where
1	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
set join_cache_level= @save_join_cache_level;
set join_buffer_size= @save_join_buffer_size;
drop table t1;
#
# BUG#730133: Wrong result with jkl = 7, BKA, ICP in maria-5.3 + compound index
#
set @tmp_730133_jcl= @@join_cache_level;
set join_cache_level = 7;
set @tmp_730133_os= @@optimizer_switch;
set optimizer_switch= 'join_cache_hashed=off,join_cache_bka=on,index_condition_pushdown=on,optimize_join_buffer_size=on';
CREATE TABLE t1 (f1 int, f2 int, f3 int, f4 int, f5 int, KEY (f4,f3));
INSERT IGNORE INTO t1 VALUES ('2','9','5','0','0'),('4','7','0','0','0'),
('6','97','190','0','0'),('7','3','6','0','0'),('11','101','186','0','0'),
('14','194','226','0','0'),('15','148','133','0','0'),
('16','9','6','0','0'),('17','9','3','0','0'),('18','1','8','0','0'),
('19','1','5','0','0'),('20','5','7','0','0');
explain
SELECT COUNT(alias2.f2) 
FROM 
t1 STRAIGHT_JOIN 
t1 AS alias3 STRAIGHT_JOIN 
t1 AS alias2 FORCE KEY (f4) 
WHERE 
alias2.f4=alias3.f5  AND 
alias2.f3 > alias3.f1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	NULL	f4	10	NULL	12	Using index
1	SIMPLE	alias3	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer (flat, BNL join)
1	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
SELECT COUNT(alias2.f2) 
FROM 
t1 STRAIGHT_JOIN 
t1 AS alias3 STRAIGHT_JOIN 
t1 AS alias2 FORCE KEY (f4) 
WHERE 
alias2.f4=alias3.f5  AND 
alias2.f3 > alias3.f1;
COUNT(alias2.f2)
768
set @@join_cache_level= @tmp_730133_jcl;
set @@optimizer_switch= @tmp_730133_os;
drop table t1;
#
# Test of MRR handler counters
#
flush status;
show status like 'Handler_mrr%';
Variable_name	Value
Handler_mrr_init	0
Handler_mrr_key_refills	0
Handler_mrr_rowid_refills	0
create table t0 (a int);
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t1 (a int, b int, filler char(200), key(a));
insert into t1 
select A.a+10*B.a+100*C.a+1000*D.a, 123,'filler' from t0 A, t0 B, t0 C, t0 D;
explain select sum(b) from t1 where a < 10;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	a	a	5	NULL	8	Using index condition; Rowid-ordered scan
# This should show one MRR scan and no re-fills:
flush status;
select sum(b) from t1 where a < 10;
sum(b)
1230
show status like 'handler_mrr%';
Variable_name	Value
Handler_mrr_init	1
Handler_mrr_key_refills	0
Handler_mrr_rowid_refills	0
set @mrr_buffer_size_save= @@mrr_buffer_size;
set mrr_buffer_size=128;
explain select sum(b) from t1 where a < 1600;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	a	a	5	NULL	1380	Using index condition; Rowid-ordered scan
# This should show one MRR scan and one extra rowid sort:
flush status;
select sum(b) from t1 where a < 1600;
sum(b)
196800
show status like 'handler_mrr%';
Variable_name	Value
Handler_mrr_init	1
Handler_mrr_key_refills	0
Handler_mrr_rowid_refills	1
set @@mrr_buffer_size= @mrr_buffer_size_save;
#Now, let's check BKA: 
set @join_cache_level_save= @@join_cache_level;
set @join_buffer_size_save= @@join_buffer_size;
set join_cache_level=6;
explain select sum(t1.b) from t0,t1 where t0.a=t1.a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	10	Using where
1	SIMPLE	t1	ref	a	a	5	test.t0.a	1	Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
flush status;
select sum(t1.b) from t0,t1 where t0.a=t1.a;
sum(t1.b)
1230
show status like 'handler_mrr%';
Variable_name	Value
Handler_mrr_init	1
Handler_mrr_key_refills	0
Handler_mrr_rowid_refills	0
set join_buffer_size=10;
explain select sum(t1.b) from t0,t1 where t0.a=t1.a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	10	Using where
1	SIMPLE	t1	ref	a	a	5	test.t0.a	1	Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
flush status;
select sum(t1.b) from t0,t1 where t0.a=t1.a;
sum(t1.b)
1230
show status like 'handler_mrr%';
Variable_name	Value
Handler_mrr_init	ok
Handler_mrr_key_refills	ok
Handler_mrr_rowid_refills	ok
set join_cache_level= @join_cache_level_save;
set join_buffer_size= @join_buffer_size_save;
drop table t0, t1;
set optimizer_switch= @myisam_mrr_tmp;