Details
Description
This is a followup to MDEV-25858.
The fix for MDEV-25858 handles backward index scan but not forward index scan.
Take the testcase from MDEV-25858 (from order_by_innodb.test), remove DESC from ORDER BY, and run EXPLAIN
explain
|
select
|
t1.id,t2.id
|
from
|
t1 left join |
t2 on t2.id2 = t1.id and |
t2.id = (select dd.id |
from t2 dd |
where |
dd.id2 = t1.id and |
d1 > '2019-02-06 00:00:00' |
order by |
dd.d1 , dd.d2 , dd.id limit 1
|
);
|
This shows:
id select_type table type possible_keys key key_len ref rows Extra
|
1 PRIMARY t1 index NULL PRIMARY 4 NULL 3 Using index
|
1 PRIMARY t2 eq_ref PRIMARY,id2 PRIMARY 4 func 1 Using where
|
2 DEPENDENT SUBQUERY dd ALL id2,for_latest_sort for_latest_sort 6 NULL 3 Range checked for each record (index map: 0x6)
|
Note the last line: Range checked for each record is used, but there are no provisions for producing rows in the order specified by the ORDER BY ... LIMIT clause.
Attachments
Issue Links
- relates to
-
MDEV-25858 Query results are incorrect when indexes are added.
-
- Closed
-