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

Wrong query plan with Range Checked for Each Record and ORDER BY ... LIMIT

    XMLWordPrintable

    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

            Activity

              People

              Assignee:
              psergei Sergei Petrunia
              Reporter:
              psergei Sergei Petrunia
              Votes:
              0 Vote for this issue
              Watchers:
              1 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.