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

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

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 5.5, 10.0, 10.1, 10.2, 10.3
    • 10.3.11
    • Optimizer
    • 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

              psergei Sergei Petrunia
              psergei Sergei Petrunia
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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