CREATE TABLE t1 (a int) ENGINE=MyISAM;
|
INSERT INTO t1 VALUES (1),(2);
|
|
CREATE TABLE t2 (b int, c int) ENGINE=MyISAM;
|
INSERT INTO t2 VALUES (1,2),(3,4);
|
|
CREATE TABLE t3 (d int, KEY(d)) ENGINE=MyISAM;
|
ALTER TABLE t3 DISABLE KEYS;
|
INSERT INTO t3 VALUES (5),(6);
|
ALTER TABLE t3 ENABLE KEYS;
|
|
CREATE TABLE t4 (e int primary key) ENGINE=MyISAM;
|
INSERT INTO t4 VALUES (1),(2);
|
|
SET SESSION join_cache_level=2;
|
SELECT * FROM t1 LEFT JOIN ( ( t2 LEFT JOIN t3 ON c = d ) JOIN t4 ) ON b = e WHERE e IS NULL;
|
SET SESSION join_cache_level=4;
|
SELECT * FROM t1 LEFT JOIN ( ( t2 LEFT JOIN t3 ON c = d ) JOIN t4 ) ON b = e WHERE e IS NULL;
|
Result
|
SELECT * FROM t1 LEFT JOIN ( ( t2 LEFT JOIN t3 ON c = d ) JOIN t4 ) ON b = e WHERE e IS NULL;
|
a b c d e
|
SET SESSION join_cache_level=4;
|
SELECT * FROM t1 LEFT JOIN ( ( t2 LEFT JOIN t3 ON c = d ) JOIN t4 ) ON b = e WHERE e IS NULL;
|
a b c d e
|
1 NULL NULL NULL NULL
|
2 NULL NULL NULL NULL
|
The second result must be incorrect, because if the same query is executed without the WHERE clause, the result is (with both values of join_cache_level):
SELECT * FROM t1 LEFT JOIN ( ( t2 LEFT JOIN t3 ON c = d ) JOIN t4 ) ON b = e;
|
a b c d e
|
1 1 2 NULL 1
|
2 1 2 NULL 1
|
Reproducible with query_cache_level=4 or 5.
Query plan in case of the wrong result:
EXPLAIN EXTENDED
|
SELECT * FROM t1 LEFT JOIN ( ( t2 LEFT JOIN t3 ON c = d ) JOIN t4 ) ON b = e WHERE e IS NULL;
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00
|
1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join)
|
1 SIMPLE t3 hash_index d #hash#d:d 5:5 test.t2.c 2 50.00 Using where; Using index; Using join buffer (incremental, BNLH join)
|
1 SIMPLE t4 hash_index PRIMARY #hash#PRIMARY:PRIMARY 4:4 test.t2.b 2 50.00 Using where; Using index; Not exists; Using join buffer (incremental, BNLH join)
|
Warnings:
|
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t3`.`d` AS `d`,`test`.`t4`.`e` AS `e` from `test`.`t1` left join (`test`.`t2` left join `test`.`t3` on(`test`.`t3`.`d` = `test`.`t2`.`c` and `test`.`t2`.`c` is not null) join `test`.`t4`) on(`test`.`t4`.`e` = `test`.`t2`.`b` and `test`.`t2`.`b` is not null) where `test`.`t4`.`e` is null
|
Reproducible with all of 5.5-10.5.