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

Wrong result upon query using index_merge with DESC key

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • N/A
    • 10.8.1
    • Optimizer
    • 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

          is caused by

          Task - A task that needs to be done. MDEV-13756 Implement descending index: KEY (a DESC, b ASC)

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed

          Activity

            psergei Sergei Petrunia added a comment -

            Notes: This quick select uses a special case variant of index_merge where one of the merged index scans is a scan on clustered PK.

            In that case, the execution gets here:

            (gdb) wher 10
            		 
              #0  QUICK_RANGE_SELECT::cmp_next (this=0x7fff9809e690, range_arg=0x7fff98017ef8) at /home/psergey/dev-git2/10.8-rev-index/sql/opt_range.cc:13079
            		 
              #1  0x0000555555d95cf4 in QUICK_RANGE_SELECT::row_in_ranges (this=0x7fff9809e690) at /home/psergey/dev-git2/10.8-rev-index/sql/opt_range.cc:12889
            		 
              #2  0x0000555555d94252 in read_keys_and_merge_scans (thd=0x7fff98000d78, head=0x7fff9801fe78, quick_selects=..., pk_quick_select=0x7fff9809e690, read_record=0x7fff980235d0, intersection=false, filtered_scans=0x0, unique_ptr=0x7fff98023560) at /home/psergey/dev-git2/10.8-rev-index/sql/opt_range.cc:12289
            		 
              #3  0x0000555555d9443f in QUICK_INDEX_MERGE_SELECT::read_keys_and_merge (this=0x7fff98023520) at /home/psergey/dev-git2/10.8-rev-index/sql/opt_range.cc:12323
            		 
              #4  0x0000555555d7857b in QUICK_INDEX_SORT_SELECT::reset (this=0x7fff98023520) at /home/psergey/dev-git2/10.8-rev-index/sql/opt_range.cc:1400

            and QUICK_RANGE_SELECT::cmp_next() apparently ignores the fact that some key parts are reverse-ordered.

            psergei Sergei Petrunia added a comment - Notes: This quick select uses a special case variant of index_merge where one of the merged index scans is a scan on clustered PK. In that case, the execution gets here: (gdb) wher 10 #0 QUICK_RANGE_SELECT::cmp_next (this=0x7fff9809e690, range_arg=0x7fff98017ef8) at /home/psergey/dev-git2/10.8-rev-index/sql/opt_range.cc:13079 #1 0x0000555555d95cf4 in QUICK_RANGE_SELECT::row_in_ranges (this=0x7fff9809e690) at /home/psergey/dev-git2/10.8-rev-index/sql/opt_range.cc:12889 #2 0x0000555555d94252 in read_keys_and_merge_scans (thd=0x7fff98000d78, head=0x7fff9801fe78, quick_selects=..., pk_quick_select=0x7fff9809e690, read_record=0x7fff980235d0, intersection=false, filtered_scans=0x0, unique_ptr=0x7fff98023560) at /home/psergey/dev-git2/10.8-rev-index/sql/opt_range.cc:12289 #3 0x0000555555d9443f in QUICK_INDEX_MERGE_SELECT::read_keys_and_merge (this=0x7fff98023520) at /home/psergey/dev-git2/10.8-rev-index/sql/opt_range.cc:12323 #4 0x0000555555d7857b in QUICK_INDEX_SORT_SELECT::reset (this=0x7fff98023520) at /home/psergey/dev-git2/10.8-rev-index/sql/opt_range.cc:1400 and QUICK_RANGE_SELECT::cmp_next() apparently ignores the fact that some key parts are reverse-ordered.

            People

              psergei Sergei Petrunia
              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.