Wrong result with join_cache_level=4 , join_buffer_size = 164

[Philip Stoev]

When executing simple queries with join_cache_level=4 , join_buffer_size = 164 the following simple query

SELECT alias2.f3 FROM t1 AS alias1 STRAIGHT_JOIN t2 AS alias2 FORCE KEY (f10) ON alias2.f10 = alias1.f10 ;

returned "8", even though the ON condition is FALSE for all rows. This seems to happen only for very low values of join_buffer_size. In real life, the issue was also observed without STRAIGHT_JOIN or FORCE KEY . InnoDB appears to be required for at least one of the tables.

EXPLAIN:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE alias1 ALL NULL NULL NULL NULL 1 Using where
1 SIMPLE alias2 ref f10 f10 4 test.alias1.f10 2 Using join buffer (flat, BNLH join)

Test case:

--source include/have_innodb.inc

CREATE TABLE t1 ( f10 varchar(1) ) ENGINE=InnoDB;
INSERT INTO t1 VALUES ('o');

CREATE TABLE t2 ( f3 int(11), f10 varchar(1), KEY (f10)) ;
INSERT INTO t2 VALUES ('8',NULL);

SET SESSION join_cache_level = 4;
SET SESSION join_buffer_size = 164;

SELECT f3 FROM t1 STRAIGHT_JOIN t2 FORCE KEY (f10) ON t2.f10 = t1.f10 ;