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

Wrong result: range scan on a multi-part index with DESC key parts misses the row equal to the lower bound

    XMLWordPrintable

Details

    • Unexpected results
    • Hide
      A query with range conditions on all columns of a three-part index that has descending key parts, such as (a DESC, c DESC, b), could miss the row whose first key part equals the bound of a ">=" condition. The query returned the row when the index was not used.
      Show
      A query with range conditions on all columns of a three-part index that has descending key parts, such as (a DESC, c DESC, b), could miss the row whose first key part equals the bound of a ">=" condition. The query returned the row when the index was not used.

    Description

      With an index (a DESC, c DESC, b), a query with range conditions on all three columns returns an empty set although one row satisfies all conditions. Replacing a >= 5 by a = 5, or using IGNORE INDEX, returns the row.

      How to reproduce

      CREATE TABLE t(a INT, b INT, c INT, KEY k(a DESC, c DESC, b));
      INSERT INTO t VALUES (9, 1, 1), (5, 5, 2);
       
      SELECT a FROM t IGNORE INDEX (k) WHERE c >= 2 AND b >= 2 AND a >= 5;
      -- 5   correct
       
      SELECT a FROM t WHERE c >= 2 AND b >= 2 AND a >= 5;
      -- expected: 5
      -- actual:   Empty set
       
      SELECT a FROM t WHERE c >= 2 AND b >= 2 AND a = 5;
      -- 5   correct
      

      Notes

      The two-part versions (a DESC, c DESC), (a DESC, c) and (a, c DESC) return the correct result; the third key part is needed.

      Attachments

        Issue Links

          Activity

            People

              psergei Sergei Petrunia
              Ann C Ann C
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.