Bug #101693 outer join elimination rewrite rule not implemented
Submitted: 20 Nov 2020 7:45 Modified: 21 Nov 2020 7:50
Reporter: casa zhang (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S4 (Feature request)
Version:8.0 OS:Any
Assigned to: CPU Architecture:Any
Tags: Optimizer, transformation

[20 Nov 2020 7:45] casa zhang
Description:
The current optimizer has outer join to inner join transformation, it traverse the joined table binary tree to convert the outer join to inner join. But some transformation rules can be implemented, like outer join elimination, ie.

"select t1.* from t1 left join t2 on t1.a = t2.a;" # a is the unique key.

this sql can be rewritten to "select t1.* from t1;" under the following circumstances:
1. left join;
2. select lists is owned by left table;
3. join condition is unique key;

How to repeat:
mysql> explain select t1.* from t1 left join t2 on t1.a=t2.a;
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref       | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ALL    | NULL          | NULL    | NULL    | NULL      |    1 |   100.00 | NULL        |
|  1 | SIMPLE      | t2    | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | test.t1.a |    1 |   100.00 | Using index |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+
2 rows in set, 1 warning (0.01 sec)

can be the following:

mysql> explain select t1.* from t1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

Suggested fix:
implemented in simplify_joins()
[21 Nov 2020 7:50] MySQL Verification Team
Hello casa zhang,

Thank you for the feature request!

regards,
Umesh