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

Wrong result upon query using index_merge with DESC key

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Critical
    • Resolution: Fixed
    • Affects Version/s: N/A
    • Fix Version/s: 10.8.1
    • Component/s: Optimizer
    • Labels:
      None

      Description

      --source include/have_innodb.inc
       
      CREATE OR REPLACE TABLE t (pk INT, a INT, b int, KEY(a), PRIMARY KEY(pk DESC)) ENGINE=InnoDB;
      INSERT INTO t VALUES (1,4,5),(2,9,6),(3,NULL,7),(4,NULL,8);
       
      ANALYZE TABLE t PERSISTENT FOR ALL; # Optional, fails either way
       
      SELECT * FROM t WHERE pk > 10 OR a > 0;
       
      # Cleanup
      DROP TABLE t;
      

      The query returns an empty result set (the expected result is 2 rows):

      preview-10.8-MDEV-13756-desc-indexes 49b38c82

      SELECT * FROM t WHERE pk > 10 OR a > 0;
      pk	a	b
      

      Not reproducible with the same test case with ASC primary key.
      Not reproducible with the same test case with MyISAM.
      Not reproducible with index_merge=off.

      EXPLAIN

      EXPLAIN
      {
        "query_block": {
          "select_id": 1,
          "nested_loop": [
            {
              "table": {
                "table_name": "t",
                "access_type": "index_merge",
                "possible_keys": ["PRIMARY", "a"],
                "key_length": "5,4",
                "index_merge": {
                  "sort_union": [
                    {
                      "range": {
                        "key": "a",
                        "used_key_parts": ["a"]
                      }
                    },
                    {
                      "range": {
                        "key": "PRIMARY",
                        "used_key_parts": []
                      }
                    }
                  ]
                },
                "rows": 3,
                "filtered": 100,
                "attached_condition": "t.pk > 10 or t.a > 0"
              }
            }
          ]
        }
      }
      

        Attachments

          Issue Links

            Activity

              People

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