Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Minor
-
Resolution: Unresolved
-
5.2.14, 5.3.12, 5.5(EOL), 10.0(EOL), 10.1(EOL)
Description
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) |