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
-
Let's set the system join_cache_level to 4 and create and populate tables t1,t2,t3,t4 with the following commands:
After this we have:
MariaDB [test]> 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 | 2 | 1 |
| 2 | 1 | 2 | 2 | 1 |
| 1 | 2 | 4 | NULL | 2 |
| 2 | 2 | 4 | NULL | 2 |
+------+------+------+------+------+
and the result set is correct as we also have:
MariaDB [test]> SELECT * FROM t2 LEFT JOIN t3 ON c = d;
+------+------+------+
| b | c | d |
+------+------+------+
| 1 | 2 | 2 |
| 2 | 4 | NULL |
+------+------+------+
MariaDB [test]> SELECT * FROM (t2 LEFT JOIN t3 ON c = d) JOIN t4;
+------+------+------+---+
| b | c | d | e |
+------+------+------+---+
| 1 | 2 | 2 | 1 |
| 2 | 4 | NULL | 1 |
| 1 | 2 | 2 | 2 |
| 2 | 4 | NULL | 2 |
+------+------+------+---+
Yet here we have a wrong result:
MariaDB [test]> 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 |
+------+------+------+------+------+
This result is wrong as the query without the condition (e IS NULL) returns rows without NULLs in column e and the column is not nullable.
Let's look at the EXPLAIN for the query returning a wrong result set:
MariaDB [test]> EXPLAIN
-> 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 | Extra |
+------+-------------+-------+------------+---------------+-----------------------+---------+-----------+------+----------------------------------------------------------------------------------+
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 2 | |
| 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 2 | Using where; Using join buffer (flat, BNL join) |
| 1 | SIMPLE | t3 | hash_index | d | #hash#d:d | 5:5 | test.t2.c | 2 | 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 | Using where; Using index; Not exists; Using join buffer (incremental, BNLH join) |
+------+-------------+-------+------------+---------------+-----------------------+---------+-----------+------+----------------------------------------------------------------------------------+
For the query without where condition the EXPLAIN output is:
MariaDB [test]> EXPLAIN
-> SELECT * FROM t1 LEFT JOIN ( ( t2 LEFT JOIN t3 ON c = d ) JOIN t4 ) ON b = e
-> ;
+------+-------------+-------+------------+---------------+-----------------------+---------+-----------+------+----------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+------------+---------------+-----------------------+---------+-----------+------+----------------------------------------------------------------------+
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 2 | |
| 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 2 | Using where; Using join buffer (flat, BNL join) |
| 1 | SIMPLE | t3 | hash_index | d | #hash#d:d | 5:5 | test.t2.c | 2 | 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 | Using index; Using join buffer (incremental, BNLH join) |
+------+-------------+-------+------------+---------------+-----------------------+---------+-----------+------+----------------------------------------------------------------------+
The execution plans for both queries are similar. The only differences are in the forth lines.