Details
Description
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.
Attachments
Issue Links
- causes
-
MDEV-32351 Significant slowdown for query with many outer joins
- Closed