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

Wrong result with DESC key on InnoDB table

Details

    Description

      --source include/have_innodb.inc
      		 
       
      		 
      CREATE TABLE t (id int, a tinyint, PRIMARY KEY (id DESC), KEY (a)) ENGINE=InnoDB;
      		 
      INSERT INTO t VALUES (1,1),(2,9),(3,4),(4,4),(5,1);
      		 
       
      		 
      SELECT * FROM t WHERE id > 4 AND a = 1;
      		 
       
      		 
      # Cleanup
      		 
      DROP TABLE t;

      preview-10.8-MDEV-13756-desc-indexes f3f68d411

      		 
      SELECT * FROM t WHERE id > 4 AND a = 1;
      		 
      id	a
      		 
      DROP TABLE t;

      The SELECT returns an empty result. It should return the row (5,1).
      Not reproducible with MyISAM.
      Not reproducible with an ascending key.
      Not reproducible on 7efd3c55e or on the current top of the branch but with the reverted patch 8be39fbc002

      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

            Descending order - Click to sort in ascending order
            marko Marko Mäkelä added a comment -

            I fixed this by removing all traces of MDEV-27582 from the branch. MDEV-27582 will have to be fixed in a different way later.

            marko Marko Mäkelä added a comment - I fixed this by removing all traces of MDEV-27582 from the branch. MDEV-27582 will have to be fixed in a different way later.
            marko Marko Mäkelä added a comment - - edited

            This was indeed caused by the attempted MDEV-27582 fix. We will develop an alternative fix that will avoid the file format change that was done as part of the initial MDEV-27582 fix.

            marko Marko Mäkelä added a comment - - edited This was indeed caused by the attempted MDEV-27582 fix. We will develop an alternative fix that will avoid the file format change that was done as part of the initial MDEV-27582 fix.
            marko Marko Mäkelä added a comment -

            I wonder if this might have been caused by the MDEV-27582 fix. I reasoned that we are free to use an internal ordering of KEY a(a ASC, id ASC) instead of KEY a(a ASC, id DESC) for the secondary index. If this query is executed as a covering index scan, then perhaps we should revert the current MDEV-27582 fix

            diff --git a/storage/innobase/dict/dict0dict.cc b/storage/innobase/dict/dict0dict.cc
            		 
            --- a/storage/innobase/dict/dict0dict.cc
            		 
            +++ b/storage/innobase/dict/dict0dict.cc
            		 
            @@ -2649,8 +2649,7 @@ dict_index_build_internal_non_clust(
            		 
             
             		if (!indexed[field->col->ind] || index->is_spatial()) {
            		 
             			dict_index_add_col(new_index, table, field->col,
            		 
            -					   field->prefix_len,
            		 
            -					   field->descending);
            		 
            +					   field->prefix_len);
            		 
             		}
            		 
             	}

            and fix MDEV-27582 in a different way, by changing the way the FULLTEXT INDEX code is reading the FTS_DOC_ID index inside InnoDB.

            marko Marko Mäkelä added a comment - I wonder if this might have been caused by the MDEV-27582 fix. I reasoned that we are free to use an internal ordering of KEY a(a ASC, id ASC) instead of KEY a(a ASC, id DESC) for the secondary index. If this query is executed as a covering index scan, then perhaps we should revert the current MDEV-27582 fix diff --git a/storage/innobase/dict/dict0dict.cc b/storage/innobase/dict/dict0dict.cc --- a/storage/innobase/dict/dict0dict.cc +++ b/storage/innobase/dict/dict0dict.cc @@ -2649,8 +2649,7 @@ dict_index_build_internal_non_clust( if (!indexed[field->col->ind] || index->is_spatial()) { dict_index_add_col(new_index, table, field->col, - field->prefix_len, - field->descending); + field->prefix_len); } } and fix MDEV-27582 in a different way, by changing the way the FULLTEXT INDEX code is reading the FTS_DOC_ID index inside InnoDB.

            People

              marko Marko Mäkelä
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              2 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.