[#MDEV-16214] Incorrect plan taken by the optimizer , uses INDEX instead of ref access with ORDER BY

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,

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.

[MDEV-16214] Incorrect plan taken by the optimizer , uses INDEX instead of ref access with ORDER BY Created: 2018-05-18 Updated: 2019-05-20 Resolved: 2019-05-20
Status:	Closed
Project:	MariaDB Server
Component/s:	Optimizer
Affects Version/s:	10.0, 10.1, 10.2, 10.3
Fix Version/s:	10.3.16, 10.4.6