Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-12099

LEFT JOIN merged from view with null rejected WHERE is not converted into INNER JOIN.

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 5.5, 10.0, 10.1, 10.2
    • 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

            People

            • Assignee:
              igor Igor Babaev
              Reporter:
              igor Igor Babaev
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: