wrong result with join_cache_level = 4, join_cache_hashed, join_cache_incremental, outer_join_with_cache

[Philip Stoev]

The following query

SELECT STRAIGHT_JOIN table1.f7 FROM G AS table1 LEFT JOIN E AS table2 RIGHT JOIN M AS table3 ON table2.f8 = table3.f8 JOIN G AS table4 ON table4.f8 ON table1.f6 = table2.f10 WHERE table2.f7 IS NULL ;

returns "7" when executed with (incremental, BNLH join), (flat, BNL join) and (incremental, BNL join) and no rows when executed with join_cache_level=0.

Test case:

--source include/have_innodb.inc

SET SESSION storage_engine='InnoDB';
SET SESSION join_cache_level = 4;
SET SESSION optimizer_switch = 'join_cache_hashed=on';
SET SESSION optimizer_switch = 'join_cache_incremental=on';
SET SESSION optimizer_switch = 'outer_join_with_cache=on';

DROP TABLE IF EXISTS M;
CREATE TABLE M (
f8 int) ;
INSERT IGNORE INTO M VALUES ('5');

DROP TABLE IF EXISTS G;
CREATE TABLE G (
f7 int,
f8 int,
f6 int) ;
INSERT IGNORE INTO G VALUES ('7','8','0');

DROP TABLE IF EXISTS E;
CREATE TABLE E (
f7 int,
f8 int,
f10 int,
PRIMARY KEY (f8)) ;
INSERT IGNORE INTO E VALUES ('2','5','0');

SELECT STRAIGHT_JOIN table1.f7 FROM G AS table1 LEFT JOIN E AS table2 RIGHT JOIN M AS table3 ON table2.f8 = table3.f8 JOIN G AS table4 ON table4.f8 ON table1.f6 = table2.f10 WHERE table2.f7 IS NULL ;
EXPLAIN SELECT STRAIGHT_JOIN table1.f7 FROM G AS table1 LEFT JOIN E AS table2 RIGHT JOIN M AS table3 ON table2.f8 = table3.f8 JOIN G AS table4 ON table4.f8 ON table1.f6 = table2.f10 WHERE table2.f7 IS NULL ;
SET SESSION join_cache_level=0;
SELECT STRAIGHT_JOIN table1.f7 FROM G AS table1 LEFT JOIN E AS table2 RIGHT JOIN M AS table3 ON table2.f8 = table3.f8 JOIN G AS table4 ON table4.f8 ON table1.f6 = table2.f10 WHERE table2.f7 IS NULL ;
EXPLAIN SELECT STRAIGHT_JOIN table1.f7 FROM G AS table1 LEFT JOIN E AS table2 RIGHT JOIN M AS table3 ON table2.f8 = table3.f8 JOIN G AS table4 ON table4.f8 ON table1.f6 = table2.f10 WHERE table2.f7 IS NULL ;

[Philip Stoev (pstoev-askmonty)]

Issue is also reproducible with a 3-way join.