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()