Wrong result with join_cache_level=7 , (flat, BNLH join)

[Philip Stoev]

Somewhat similar to bug 707827 , but shows up even with the default value for join_buffer_size. The query returns rows for which the ON condition does not match.

Query:

 SELECT STRAIGHT_JOIN * FROM t1 JOIN t2 FORCE KEY (f10) ON t1.f3 AND t2.f10 = t1.f11 ORDER BY t1.f1;

(STRAIGHT_JOIN and FORCE KEY are only in order to fix the execution plan)

EXPLAIN:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 1 Using where; Using temporary; Using filesort
1 SIMPLE t2 ref f10 f10 4 test.t1.f11 2 Using join buffer (flat, BNLH join)

Test case:

--source include/have_innodb.inc
SET SESSION join_cache_level = 7;
SET SESSION optimizer_switch = 'join_cache_bka=off';

CREATE TABLE t2 ( f2 int(11), f10 varchar(1), KEY (f10) ) ;
INSERT IGNORE INTO t2 VALUES (NULL,NULL);

CREATE TABLE t1 ( f1 int(11), f3 int(11), f11 varchar(1) ) ENGINE=InnoDB;
INSERT IGNORE INTO t1 VALUES (16,3,'d');

SELECT STRAIGHT_JOIN * FROM t1 JOIN t2 FORCE KEY (f10) ON t1.f3 AND t2.f10 = t1.f11 ORDER BY t1.f1;

[Igor Babaev (igorb-seattle)]

This bug is a duplicate of bug 707827. The fact the test case for this bug does not change the value
for join_buffer_size does not matter as the optimizer employs a buffer of a most suitable size and
in this case it's small.