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

Range optimization doesn't use ON expressions from nested outer joins

    Details

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

      Description

      This is the range access part of MDEV-17493.

      create table t2(a int);
      insert into t2 values (0),(1);
      create table t1 (a int, b int, key(a));
      insert into t1 select A.a + B.a* 10 + C.a * 100, 12345 from ten A, ten B, ten C;
      

      Outer join with single-table on the inner side is able to use the ON expression to construct range access:

      mysql> explain select * from ten left join t1 on ten.a=t1.b and t1.a<5;
      +------+-------------+-------+-------+---------------+------+---------+------+------+-------------------------------------------------+
      | id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                                           |
      +------+-------------+-------+-------+---------------+------+---------+------+------+-------------------------------------------------+
      |    1 | SIMPLE      | ten   | ALL   | NULL          | NULL | NULL    | NULL |   10 |                                                 |
      |    1 | SIMPLE      | t1    | range | a             | a    | 5       | NULL |    5 | Using where; Using join buffer (flat, BNL join) |
      +------+-------------+-------+-------+---------------+------+---------+------+------+-------------------------------------------------+
      

      add another table there and range access is not used anymore:

      mysql> explain select * from ten left join (t1 join t2) on ten.a=t1.b and t1.a<5;
      +------+-------------+-------+------+---------------+------+---------+------+------+--------------------------------------------------------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                                  |
      +------+-------------+-------+------+---------------+------+---------+------+------+--------------------------------------------------------+
      |    1 | SIMPLE      | ten   | ALL  | NULL          | NULL | NULL    | NULL |   10 |                                                        |
      |    1 | SIMPLE      | t2    | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using join buffer (flat, BNL join)                     |
      |    1 | SIMPLE      | t1    | ALL  | a             | NULL | NULL    | NULL | 1000 | Using where; Using join buffer (incremental, BNL join) |
      +------+-------------+-------+------+---------------+------+---------+------+------+--------------------------------------------------------+
      

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                psergey Sergei Petrunia
                Reporter:
                psergey Sergei Petrunia
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: