Wrong result with jkl = 7, BKA, ICP in maria-5.3 + compound index

[Philip Stoev]

The following query returns less rows when executed with JKL, BKA and ICP v.s. any other plan. Repeatable in maria-5.3 and maria-5.3-mwl128

EXPLAIN:

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)

bzr version-info:

revision-id: <email address hidden>
date: 2011-03-04 18:54:30 +0300
build-date: 2011-03-06 15:53:17 +0200
revno: 2933
branch-nick: maria-5.3

test case:

SET SESSION optimizer_use_mrr = 'force';
SET SESSION join_cache_level = 7;
DROP TABLE IF EXISTS t1;
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');

--let $query = SELECT COUNT(alias2.f2) FROM t1 STRAIGHT_JOIN ( t1 AS alias2 FORCE KEY (f4) JOIN t1 AS alias3 ON alias3.f5 = alias2.f4 ) ON alias3.f1 < alias2.f3 ;

SET SESSION optimizer_switch = 'join_cache_hashed=off,join_cache_bka=on,index_condition_pushdown=on';
--eval $query
--eval EXPLAIN $query
SET SESSION optimizer_switch = 'join_cache_hashed=off,join_cache_bka=on,index_condition_pushdown=off';
--eval $query
--eval EXPLAIN $query

[Sergey Petrunia (sergefp)]

I've already made several attempts to analyze, and was not successful so far. There is something wrong either with index condition pushdown or with linked BKA, but I wasn't able to narrow down the problem so far.

[Sergey Petrunia (sergefp)]

To reproduce with current 5.3, one needs to

set optimizer_switch='optimize_join_buffer_size=on';

(the default for the flag was changed to OFF in revno:3111)