[MDEV-12099] LEFT JOIN merged from view with null rejected WHERE is not converted into INNER JOIN. - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL)
Fix Version/s: 5.5.55
Component/s: Optimizer
Labels:
None

Sprint:
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) |

+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Attachments

Activity

Ascending order - Click to sort in descending order

Igor Babaev (Inactive) created issue - 2017-02-21 00:13

Igor Babaev (Inactive) made changes - 2017-02-26 10:23

Field	Original Value	New Value
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: {noformat} 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; {noformat} The view is mergeable . Let's execute the following query: {noformat} 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 \| +------+------+------+------+------+------+ {noformat} 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. {noformat} 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) \| +-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ {noformat} Yet, if we manually merge the view we get the following: {noformat} 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) \| +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ {noformat} 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. {noformat} 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 -> select * -> from t1 left join (t2 left join t3 on t3.e=t2.d ) -> on t2.c=t1.b and t3.f=t1.a 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 \| 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) \| +-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ {noformat}	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: {noformat} 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; {noformat} The view is mergeable . Let's execute the following query: {noformat} 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 \| +------+------+------+------+------+------+ {noformat} 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. {noformat} 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) \| +-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ {noformat} Yet, if we manually merge the view we get the following: {noformat} 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) \| +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ {noformat} 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. {noformat} 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) \| +-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ {noformat}

Igor Babaev (Inactive) made changes - 2017-02-26 10:23

Status

Open [ 1 ]

In Progress [ 3 ]

Igor Babaev (Inactive) made changes - 2017-02-26 10:24

Sprint

10.0.30 [ 140 ]

Igor Babaev (Inactive) added a comment - 2017-02-26 23:06

This bug raises back to mysql-5.0. It can be reproduced for all versions of MariaDB. It can be reproduced for all versions of mysql including the latest mysql-8.0 as well.
The left join merged from the view of the test case for this bug is not converted into an inner join because after the merge the ON condition of the embedding outer join becomes the AND formula with abort_on_null set to false.
As a result of this for this ON condition not_null_tables() is calculated incorrectly.

Igor Babaev (Inactive) added a comment - 2017-02-26 23:06 This bug raises back to mysql-5.0. It can be reproduced for all versions of MariaDB. It can be reproduced for all versions of mysql including the latest mysql-8.0 as well. The left join merged from the view of the test case for this bug is not converted into an inner join because after the merge the ON condition of the embedding outer join becomes the AND formula with abort_on_null set to false. As a result of this for this ON condition not_null_tables() is calculated incorrectly.

Igor Babaev (Inactive) made changes - 2017-02-26 23:47

Assignee	Igor Babaev [ igor ]	Oleksandr Byelkin [ sanja ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Oleksandr Byelkin added a comment - 2017-02-27 13:30

OK to push

Oleksandr Byelkin added a comment - 2017-02-27 13:30 OK to push

Oleksandr Byelkin made changes - 2017-02-27 13:30

Assignee	Oleksandr Byelkin [ sanja ]	Igor Babaev [ igor ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Igor Babaev (Inactive) added a comment - 2017-02-28 07:00

The fix for this bug was pushed into the 5.5 tree.
It should applied upstream as it is.

Igor Babaev (Inactive) added a comment - 2017-02-28 07:00 The fix for this bug was pushed into the 5.5 tree. It should applied upstream as it is.

Igor Babaev (Inactive) made changes - 2017-02-28 07:00

Fix Version/s		5.5.55 [ 22311 ]
Resolution		Fixed [ 1 ]
Status	Stalled [ 10000 ]	Closed [ 6 ]

Sergei Golubchik made changes - 2021-12-06 21:44

Workflow

MariaDB v3 [ 79700 ]

MariaDB v4 [ 151730 ]

People

Assignee:: Igor Babaev (Inactive)

Reporter:: Igor Babaev (Inactive)

Votes:: 0 Vote for this issue

Watchers:: 2 Start watching this issue

Dates

Created:: 2017-02-21 00:13

Updated:: 2017-02-28 07:00

Resolved:: 2017-02-28 07:00

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server