No conversion of outer join into inner join when a view is used

[Igor Babaev]

For the test case from bug #884184
we have in mariadb-5.2 (and mariadb-5.3):

MariaDB [test]> set optimizer_switch='table_elimination=off';
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> EXPLAIN EXTENDED SELECT alias1.* FROM t3 LEFT JOIN v1 as alias1 ON ( t3.a = alias1.b );
+----+-------------+-------+--------+---------------+---------+---------+-----------+------+----------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+--------+---------------+---------+---------+-----------+------+----------+--------------------------+
| 1 | SIMPLE | t3 | system | NULL | NULL | NULL | NULL | 1 | 100.00 | |
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |
| 1 | SIMPLE | t2 | eq_ref | PRIMARY | PRIMARY | 4 | test.t1.a | 1 | 100.00 | Using where; Using index |
+----+-------------+-------+--------+---------------+---------+---------+-----------+------+----------+--------------------------+
3 rows in set, 1 warning (0.00 sec)

MariaDB [test]> show warnings;
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1003 | select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t3` left join (`test`.`t1` left join `test`.`t2` on(((`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t1`.`a` is not null)))) on(((`test`.`t2`.`a` <> 0) and (`test`.`t1`.`b` = 'g'))) where 1 |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

We can see that the left join of inherited from the view is not converted into an inner join.

With a manual substitution of the view the conversion is performed:

MariaDB [test]> EXPLAIN EXTENDED SELECT t1.a, t1.b FROM t3 LEFT JOIN (t1 LEFT JOIN t2 ON t1.a = t2.a) ON t2.a <> 0 AND t3.a = t1.b;
+----+-------------+-------+--------+---------------+---------+---------+------+------+----------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+--------+---------------+---------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | t3 | system | NULL | NULL | NULL | NULL | 1 | 100.00 | |
| 1 | SIMPLE | t2 | index | PRIMARY | PRIMARY | 4 | NULL | 0 | 0.00 | Using where; Using index |
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |
+----+-------------+-------+--------+---------------+---------+---------+------+------+----------+--------------------------+
3 rows in set, 1 warning (0.00 sec)

MariaDB [test]> show warnings;
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1003 | select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t3` left join (`test`.`t1` join `test`.`t2`) on(((`test`.`t2`.`a` <> 0) and (`test`.`t1`.`b` = 'g') and (`test`.`t1`.`a` = `test`.`t2`.`a`))) where 1 |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)