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