Bug #97552 Regression: LEFT JOIN with Impossible ON condition performs slowly

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;

Hello Fredric Johansson,

Thank you for the report and test case.

regards,
Umesh

Documented fix in the MySQL 8.0.20 changelog as follows:

    Previous work in MySQL 8.0 to optimize impossible expressions
    such as a=b AND FALSE into just FALSE could make for less
    efficient execution when such expressions appeared as outer join
    conditions, due to the fact that the join was interpreted as a
    Cartesian product followed by a filter.

Closed.