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

Incorrect plan taken by the optimizer , uses INDEX instead of ref access with ORDER BY

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.0, 10.1, 10.2, 10.3
    • 10.3.16, 10.4.6
    • Optimizer
    • None

    Description

      Create the test dataset

      --source include/have_innodb.inc
      create table t1(a int) engine=innodb;
      insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
       
      create table t2(
        id int primary key,
        key1 int,
        col1 int,
        key(key1)
      ) engine=innodb;
       
      insert into t2
        select
          A.a + B.a*10 + C.a*100 + D.a* 1000,
          A.a + 10*B.a,
          123456
      from t1 A, t1 B, t1 C, t1 D;
       
      alter table t2 add key2 int;
      update t2 set key2=key1;
      alter table t2 add key(key2);
      analyze table t2;
      flush tables;
      
      

      Then run the query

      explain select
         (SELECT
            concat(id, '-', key1, '-', col1)
          FROM t2
          WHERE t2.key1 = t1.a
          ORDER BY t2.key2 ASC LIMIT 1)
      from
        t1;
      

      The query plan is

      +------+--------------------+-------+------+---------------+------+---------+-----------+------+-----------------------------+
      | id   | select_type        | table | type | possible_keys | key  | key_len | ref       | rows | Extra                       |
      +------+--------------------+-------+------+---------------+------+---------+-----------+------+-----------------------------+
      |    1 | PRIMARY            | t1    | ALL  | NULL          | NULL | NULL    | NULL      |   10 |                             |
      |    2 | DEPENDENT SUBQUERY | t2    | ref  | key1          | key1 | 5       | test.t1.a |   49 | Using where; Using filesort |
      +------+--------------------+-------+------+---------------+------+---------+-----------+------+-----------------------------+
      

      Now I run the query

      explain select
           (SELECT
              concat(id, '-', key1, '-', col1)
           FROM t2
            WHERE t2.key1 = t1.a and t2.key1 is NOT NULL
           ORDER BY t2.key2 ASC LIMIT 1)
      from t1;
      

      +------+--------------------+-------+-------+---------------+------+---------+------+------+-------------+
      | id   | select_type        | table | type  | possible_keys | key  | key_len | ref  | rows | Extra       |
      +------+--------------------+-------+-------+---------------+------+---------+------+------+-------------+
      |    1 | PRIMARY            | t1    | ALL   | NULL          | NULL | NULL    | NULL |   10 |             |
      |    2 | DEPENDENT SUBQUERY | t2    | index | key1          | key2 | 5       | NULL |    2 | Using where |
      +------+--------------------+-------+-------+---------------+------+---------+------+------+-------------+
      

      As I see none of the elements for key1 is NULL, so the second query should behave exactly as the first one but it doesn't.

      Attachments

        Issue Links

          Activity

            People

              varun Varun Gupta (Inactive)
              varun Varun Gupta (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              2 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.