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

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.0, 10.2, 10.3, 10.1
    • Fix Version/s: 10.3.16, 10.4.6
    • Component/s: Optimizer
    • Labels:
      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

              Assignee:
              varun Varun Gupta (Inactive)
              Reporter:
              varun Varun Gupta (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: