Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
None
-
None
-
None
Description
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