[MDEV-27270] Wrong query plan with Range Checked for Each Record and ORDER BY ... LIMIT Created: 2021-12-15  Updated: 2022-11-08  Resolved: 2021-12-15

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.2
Fix Version/s: 10.2.42, 10.3.33, 10.4.23, 10.5.14, 10.6.6, 10.7.3

Type: Bug Priority: Major
Reporter: Sergei Petrunia Assignee: Sergei Petrunia
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-25858 Query results are incorrect when inde... Closed

 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.

 Comments   
Comment by Sergei Petrunia [ 2021-12-15 ]

bb-10.2-mdev2720

Generated at Thu Feb 08 09:51:39 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.