Description:
When doing a LEFT JOIN with an impossible ON condition, eg. 1=0, the query takes excessive time to perform compared to the LEFT JOIN without the condition or a JOIN.
The same queries works fine in v5.7 but not in at least v8.0.17 and v8.0.18
# LEFT JOIN without condition
mysql> SELECT * FROM tmp.t LEFT JOIN tmp.t2 ON t.z_id=t2.z_id LIMIT 10;
+------+------+---------------------+
| z_id | z_id | value |
+------+------+---------------------+
| 1 | 1 | 0.28233354937502514 |
| 2 | 2 | 0.2553500637927559 |
| 3 | 3 | 0.42974970157234904 |
| 4 | 4 | 0.3826983472171224 |
| 5 | 5 | 0.62424266210221 |
| 6 | 6 | 0.9731182995933279 |
| 7 | 7 | 0.9928635423936542 |
| 8 | 8 | 0.04496284392193225 |
| 9 | 9 | 0.24622362316234375 |
| 10 | 10 | 0.09622961477956699 |
+------+------+---------------------+
10 rows in set (0.00 sec)
mysql> EXPLAIN SELECT * FROM tmp.t LEFT JOIN tmp.t2 ON t.z_id=t2.z_id LIMIT 10;
+----+-------------+-------+------------+--------+---------------+---------+---------+------------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------+-------+----------+-------------+
| 1 | SIMPLE | t | NULL | index | NULL | PRIMARY | 8 | NULL | 10000 | 100.00 | Using index |
| 1 | SIMPLE | t2 | NULL | eq_ref | PRIMARY | PRIMARY | 8 | tmp.t.z_id | 1 | 100.00 | NULL |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------+-------+----------+-------------+
# LEFT JOIN with condition
mysql> SELECT * FROM tmp.t LEFT JOIN tmp.t2 ON t.z_id=t2.z_id AND 1=0 LIMIT 10;
+------+------+-------+
| z_id | z_id | value |
+------+------+-------+
| 1 | NULL | NULL |
| 2 | NULL | NULL |
| 3 | NULL | NULL |
| 4 | NULL | NULL |
| 5 | NULL | NULL |
| 6 | NULL | NULL |
| 7 | NULL | NULL |
| 8 | NULL | NULL |
| 9 | NULL | NULL |
| 10 | NULL | NULL |
+------+------+-------+
10 rows in set (4.94 sec)
mysql> EXPLAIN SELECT * FROM tmp.t LEFT JOIN tmp.t2 ON t.z_id=t2.z_id AND 1=0 LIMIT 10;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+----------------------------------------------------+
| 1 | SIMPLE | t | NULL | index | NULL | PRIMARY | 8 | NULL | 10000 | 100.00 | Using index |
| 1 | SIMPLE | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 10000 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)
# JOIN with condition
mysql> SELECT * FROM tmp.t JOIN tmp.t2 ON t.z_id=t2.z_id AND 1=0 LIMIT 10;
Empty set (0.00 sec)
mysql> EXPLAIN SELECT * FROM tmp.t JOIN tmp.t2 ON t.z_id=t2.z_id AND 1=0 LIMIT 10;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
1 row in set, 1 warning (0.00 sec)
# MySQL v5.7.28 LEFT JOIN with condition
mysql> SELECT * FROM tmp.t LEFT JOIN tmp.t2 ON t.z_id=t2.z_id AND 1=0 LIMIT 10;
+------+------+-------+
| z_id | z_id | value |
+------+------+-------+
| 1 | NULL | NULL |
| 2 | NULL | NULL |
| 3 | NULL | NULL |
| 4 | NULL | NULL |
| 5 | NULL | NULL |
| 6 | NULL | NULL |
| 7 | NULL | NULL |
| 8 | NULL | NULL |
| 9 | NULL | NULL |
| 10 | NULL | NULL |
+------+------+-------+
10 rows in set (0.00 sec)
mysql> EXPLAIN SELECT * FROM tmp.t LEFT JOIN tmp.t2 ON t.z_id=t2.z_id AND 1=0 LIMIT 10;
+----+-------------+-------+------------+--------+---------------+---------+---------+------------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------+-------+----------+-------------+
| 1 | SIMPLE | t | NULL | index | NULL | PRIMARY | 8 | NULL | 10000 | 100.00 | Using index |
| 1 | SIMPLE | t2 | NULL | eq_ref | PRIMARY | PRIMARY | 8 | tmp.t.z_id | 1 | 100.00 | Using where |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------+-------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
How to repeat:
SET @@cte_max_recursion_depth = 10001 ;
CREATE TABLE tmp.t2 (PRIMARY KEY (z_id))
WITH RECURSIVE cte (z_id, value) AS (
SELECT 1, RAND()
UNION ALL
SELECT z_id +1, RAND() FROM cte WHERE z_id < 10000
)
SELECT * FROM cte;
CREATE TABLE tmp.t (PRIMARY KEY (z_id))
SELECT z_id FROM tmp.t2;
EXPLAIN
SELECT *
FROM tmp.t
JOIN tmp.t2 ON t.z_id=t2.z_id AND 1=0;
EXPLAIN
SELECT *
FROM tmp.t
LEFT JOIN tmp.t2 ON t.z_id=t2.z_id AND 1=0;
