Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL)
-
None
-
10.0.30
Description
This is a legacy defect: can be reproduced in mariadb-5.1, mariadb-5.2 and all versions of mysql.
Let's use these 3 tables and a view:
create table t1 (a int, b int, key(a)) engine=myisam;
|
insert into t1 values
|
(3,20), (7,10), (2,10), (4,30), (8,70),
|
(7,70), (9,100), (9,60), (8,80), (7,60);
|
create table t2 (c int, d int, key (c)) engine=myisam;
|
insert into t2 values
|
(50,100), (20, 200), (10,300),
|
(150,100), (120, 200), (110,300),
|
(250,100), (220, 200), (210,300);
|
create table t3(e int, f int not null, key(e), unique (f)) engine=myisam;
|
insert into t3 values
|
(100, 3), (300, 5), (400, 4), (300,7),
|
(300,2), (600, 13), (800, 15), (700, 14),
|
(600, 23), (800, 25), (700, 24);
|
create view v1 as
|
select * from t2 left join t3 on t3.e=t2.d where t3.f is not null;
|
The view is mergeable .
Let's execute the following query:
MariaDB [test]> select *
|
-> from t1 left join v1
|
-> on v1.c=t1.b
|
-> where t1.a < 5;
|
+------+------+------+------+------+------+
|
| a | b | c | d | e | f |
|
+------+------+------+------+------+------+
|
| 2 | 10 | 10 | 300 | 300 | 5 |
|
| 2 | 10 | 10 | 300 | 300 | 7 |
|
| 2 | 10 | 10 | 300 | 300 | 2 |
|
| 3 | 20 | NULL | NULL | NULL | NULL |
|
| 4 | 30 | NULL | NULL | NULL | NULL |
|
+------+------+------+------+------+------+
|
Here the optimizer does not convert LEFT JOIN of the view into an INNER JOIN,
though it can be converted, as the WHERE condition of the view does not accept
NULL complemented rows.
MariaDB [test]> explain extended
|
-> select *
|
-> from t1 left join v1
|
-> on v1.c=t1.b
|
-> where t1.a < 5;
|
+------+-------------+-------+-------+---------------+------+---------+-----------+------+----------+-----------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+-------+-------+---------------+------+---------+-----------+------+----------+-----------------------+
|
| 1 | SIMPLE | t1 | range | a | a | 5 | NULL | 3 | 100.00 | Using index condition |
|
| 1 | SIMPLE | t2 | ref | c | c | 5 | test.t1.b | 2 | 100.00 | Using where |
|
| 1 | SIMPLE | t3 | ref | e | e | 5 | test.t2.d | 2 | 100.00 | Using where |
|
+------+-------------+-------+-------+---------------+------+---------+-----------+------+----------+-----------------------+
|
MariaDB [test]> show warnings;
|
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Level | Code | Message |
|
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Note | 1003 | select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d`,`test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t1` left join (`test`.`t2` left join `test`.`t3` on(((`test`.`t3`.`e` = `test`.`t2`.`d`) and (`test`.`t2`.`d` is not null)))) on(((`test`.`t2`.`c` = `test`.`t1`.`b`) and (`test`.`t3`.`f` is not null) and (`test`.`t1`.`b` is not null))) where (`test`.`t1`.`a` < 5) |
|
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
Yet, if we manually merge the view we get the following:
MariaDB [test]> select *
|
-> from t1 left join (t2 left join t3 on t3.e=t2.d )
|
-> on t2.c=t1.b and t3.f is not null
|
-> where t1.a < 5;
|
+------+------+------+------+------+------+
|
| a | b | c | d | e | f |
|
+------+------+------+------+------+------+
|
| 2 | 10 | 10 | 300 | 300 | 5 |
|
| 2 | 10 | 10 | 300 | 300 | 7 |
|
| 2 | 10 | 10 | 300 | 300 | 2 |
|
| 3 | 20 | NULL | NULL | NULL | NULL |
|
| 4 | 30 | NULL | NULL | NULL | NULL |
|
+------+------+------+------+------+------+
|
MariaDB [test]> explain extended
|
-> select *
|
-> from t1 left join (t2 left join t3 on t3.e=t2.d )
|
-> on t2.c=t1.b and t3.f is not null
|
-> where t1.a < 5;
|
+------+-------------+-------+-------+---------------+------+---------+-----------+------+----------+-----------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+-------+-------+---------------+------+---------+-----------+------+----------+-----------------------+
|
| 1 | SIMPLE | t1 | range | a | a | 5 | NULL | 3 | 100.00 | Using index condition |
|
| 1 | SIMPLE | t2 | ref | c | c | 5 | test.t1.b | 2 | 100.00 | Using where |
|
| 1 | SIMPLE | t3 | ref | f,e | e | 5 | test.t2.d | 2 | 100.00 | Using where |
|
+------+-------------+-------+-------+---------------+------+---------+-----------+------+----------+-----------------------+
|
MariaDB [test]> show warnings;
|
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Level | Code | Message |
|
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Note | 1003 | select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d`,`test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(((`test`.`t2`.`c` = `test`.`t1`.`b`) and (`test`.`t3`.`e` = `test`.`t2`.`d`) and (`test`.`t3`.`f` is not null) and (`test`.`t1`.`b` is not null) and (`test`.`t2`.`d` is not null))) where (`test`.`t1`.`a` < 5) |
|
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
Here we see that the optimizer converted the embedded LEFT JOIN into an INNER JOIN.
It gives the optimizer more freedom when choosing a better execution plan.
The following query shows that the optimizer really can choose a different plan with
INNER JOIN instead of LEFT JOIN.
MariaDB [test]> explain extended
|
-> select *
|
-> from t1 left join v1
|
-> on v1.c=t1.b and v1.f=t1.a
|
-> where t1.a < 5;
|
+------+-------------+-------+-------+---------------+------+---------+-----------+------+----------+-----------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+-------+-------+---------------+------+---------+-----------+------+----------+-----------------------+
|
| 1 | SIMPLE | t1 | range | a | a | 5 | NULL | 3 | 100.00 | Using index condition |
|
| 1 | SIMPLE | t2 | ref | c | c | 5 | test.t1.b | 2 | 100.00 | Using where |
|
| 1 | SIMPLE | t3 | ref | e | e | 5 | test.t2.d | 2 | 100.00 | Using where |
|
+------+-------------+-------+-------+---------------+------+---------+-----------+------+----------+-----------------------+
|
MariaDB [test]> show warnings;
|
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Level | Code | Message |
|
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Note | 1003 | select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d`,`test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t1` left join (`test`.`t2` left join `test`.`t3` on(((`test`.`t3`.`e` = `test`.`t2`.`d`) and (`test`.`t2`.`d` is not null)))) on(((`test`.`t2`.`c` = `test`.`t1`.`b`) and (`test`.`t3`.`f` = `test`.`t1`.`a`) and (`test`.`t1`.`a` is not null) and (`test`.`t1`.`b` is not null))) where (`test`.`t1`.`a` < 5) |
|
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
MariaDB [test]> explain extended
|
 |
-----+-------------+-------+--------+---------------+------+---------+-----------+------+----------+-----------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+-------+--------+---------------+------+---------+-----------+------+----------+-----------------------+
|
| 1 | SIMPLE | t1 | range | a | a | 5 | NULL | 3 | 100.00 | Using index condition |
|
| 1 | SIMPLE | t3 | eq_ref | f,e | f | 4 | test.t1.a | 1 | 100.00 | Using where |
|
| 1 | SIMPLE | t2 | ref | c | c | 5 | test.t1.b | 2 | 100.00 | Using where |
|
+------+-------------+-------+--------+---------------+------+---------+-----------+------+----------+-----------------------+
|
MariaDB [test]> show warnings;
|
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Level | Code | Message |
|
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Note | 1003 | select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d`,`test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(((`test`.`t2`.`c` = `test`.`t1`.`b`) and (`test`.`t3`.`f` = `test`.`t1`.`a`) and (`test`.`t2`.`d` = `test`.`t3`.`e`) and (`test`.`t1`.`a` is not null) and (`test`.`t1`.`a` is not null) and (`test`.`t1`.`b` is not null))) where (`test`.`t1`.`a` < 5) |
|
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|