The following query:

SELECT alias1.f2 FROM t1 AS alias1 JOIN ( t1 AS alias2 FORCE KEY (f3) JOIN t1 AS alias3 FORCE KEY (f2) ON alias3.f2 = alias2.f2 AND alias3.f4 = alias2.f3 ) ON alias3.f1 <= alias2.f1

returns less rows when executed with jkl 6 and MRR = force . Not repeatable with jkl 4 or jkl 7 . Not repeatable with a smaller number of rows. The FORCE KEYs were added after the fact in order to make the query plan more robust.

Explain:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE alias1 index NULL f2 5 NULL 19 Using index
1 SIMPLE alias2 ALL f3 NULL NULL NULL 19 Using where; Using join buffer (flat, BNL join)
1 SIMPLE alias3 ref f2 f2 5 test.alias2.f2 1 Using index condition(BKA); Using where; Using join buffer (incremental, BKA join)

Test case:

--source include/have_innodb.inc

SET SESSION optimizer_use_mrr = 'force';
SET SESSION join_cache_level = 6;
CREATE TABLE t1 ( f1 int(11), f2 int(11), f3 varchar(1), f4 varchar(1), PRIMARY KEY (f1), KEY (f3), KEY (f2)) ENGINE=InnoDB;
INSERT INTO t1 VALUES ('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');

--let $query = SELECT alias1.f2 FROM t1 AS alias1 JOIN ( t1 AS alias2 FORCE KEY (f3) JOIN t1 AS alias3 FORCE KEY (f2) ON alias3.f2 = alias2.f2 AND alias3.f4 = alias2.f3 ) ON alias3.f1 <= alias2.f1

--eval EXPLAIN $query
--eval CREATE TABLE d1 AS $query

SET SESSION join_cache_level = 0;
--eval CREATE TABLE d2 AS $query

--let $diff_table_1 = test.d1
--let $diff_table_2 = test.d2
--source include/diff_tables.inc