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

Implement descending index: KEY (a DESC, b ASC)

Details

    Description

      MySQL 8.0 introduced support for descending indexes in WL#1074. This involved some changes in InnoDB and many in the optimizer.

      MariaDB and older versions of MySQL silently ignore the ASC/DESC attribute in KEY definitions. All index fields (KEY_PART) are sorted in ascending order.

      The Oracle database defines the NULLS FIRST and NULLS LAST attributes for the ORDER BY clause, but not for CREATE INDEX. The defaults are the exact opposite of InnoDB behaviour:

      NULLS LAST is the default for ascending order, and NULLS FIRST is the default for descending order.

      InnoDB in MariaDB and before MySQL 8.0 supports only ascending order, and it sorts NULLS FIRST, not NULLS LAST like the above quote suggests Oracle to do.

      Attachments

        Issue Links

          causes

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27300 Result differs with DESC index upon comparison of timestamp with incompatible value

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27303 Table corruption after insert into a non-InnoDB table with DESC index

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27309 Server crash or ASAN memcpy-param-overlap upon INSERT into Aria/MyISAM table with DESC key

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27330 Wrong sorting order with DESC index and empty strings in MyISAM/Aria table

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27340 NULL gets lost (becomes empty string), SELECT hangs with DESC index on MyISAM/Aria table

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27347 MyISAM/Aria table is marked as crashed upon updating NULL values in DESC index

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27374 InnoDB table becomes corrupt after renaming DESC-indexed column

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27392 DESC attribute is ignored when index is re-created within single ALTER on InnoDB table

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27393 Timezone tables cannot have descending indexes

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27396 DESC index attribute remains in Archive table definition, despite being apparently ignored

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27398 DESC index causes wrong (empty) result on Federated tables

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27406 Index on a HEAP table retains DESC attribute despite being hash

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27407 Different ASC/DESC index attributes on MERGE and underlying table can cause wrong results

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27408 DESC index on a Mroonga table causes wrong order of result set

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27419 MariaDB doesn't choose DESC index for ORDER BY DESC when MySQL does

          • Major - Major loss of function.
          • Confirmed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27420 Spider tables retain DESC attribute on indexes even though it has no meaning

          • Trivial - Cosmetic problem like misspelt words or misaligned text.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27426 Wrong result upon query using index_merge with DESC key

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27432 ASC/DESC primary and unique keys cause index inconsistency between InnoDB and server

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27434 DESC attribute does not work with auto-increment on secondary column of multi-part index

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27439 AUTO_INCREMENT columns with DESC index do not work

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27445 Index inconsistency and assertion failure after renaming virtual column with DESC key

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27529 Wrong result upon query using index_merge with DESC key (#2)

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Task - A task that needs to be done. MDEV-27576 Use DESC indexes for MIN/MAX optimization

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27578 DESC attribute upon spatial index creation prevents ER_DUP_INDEX warning

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27580 Bogus warning ER_TRUNCATED_WRONG_VALUE with DESC key on partitioned Spider table

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27581 Wrong result with DESC key on partitioned Spider table

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27585 Auto-increment on secondary column increments unexpectedly with DESC key

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27586 Auto-increment does not work with DESC on MERGE table

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27588 MyISAM/Aria descending index compression is not as efficient as ascending

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27589 OQGRAPH returns a different result set if backing table has descending PK

          • Trivial - Cosmetic problem like misspelt words or misaligned text.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27590 Auto-increment on Spider tables with DESC PK does not work properly

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27591 Connect tables (FIX/DOS) don't work with DESC keys - wrong results

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27592 DESC keys: Data corruption or Assertion `index->n_core_null_bytes <= (((index->n_nullable) + 7) >> 3) || index->in_instant_init' failed

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27617 HANDLER KEY > (x) does not work with DESC keys, Spider is affected and returns wrong results

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27623 Wrong result with DESC key on InnoDB table

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27633 Wrong result with different direction of index on Spider and underlying tables in handler mode

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27683 EXCHANGE PARTITION allows different index direction, but causes further errors

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-29047 RocksDB retains unsupported DESC key option after error, causes further failures

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30845 filesort still select when desc/asc index matches ORDER BY

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-35169 ALTER TABLE...IMPORT TABLESPACE does not work with INDEX DESC

          • Major - Major loss of function.
          • Closed
          includes

          Task - A task that needs to be done. MDEV-26938 support descending indexes internally in InnoDB

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Task - A task that needs to be done. MDEV-26939 support descending indexes for simple ORDER BY

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Task - A task that needs to be done. MDEV-26996 Support descending indexes in the range optimizer

          • Major - Major loss of function.
          • Closed
          is part of

          Task - A task that needs to be done. MDEV-27373 Q1 2022 release merge

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-4621 select returns null for information_schema.statistics.collation field

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-23571 InnoDB does not raise a warning if ALTER TABLE index operations are optimized away

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27577 Recovery behaves differently with DESC PK in innodb.leaf_page_corrupted_during_recovery

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27302 Backward index scan is not reflected in EXPLAIN output

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27582 Fulltext search DDL decrements FTS_DOC_ID

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-35849 InnoDB: index records in a wrong order upon CHECK with DESC index

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

          Activity

            Ascending order - Click to sort in descending order
            View 4 older comments
            elenst Elena Stepanova added a comment - - edited

            I think the last [partially] tested revision, preview-10.8-MDEV-13756-desc-indexes / 1c4cbb946, can be pushed into the main branch and released with 10.8.1.

            There are a few known issues remain, but they are not deemed critical for the initial release.
            Examples (not an exhaustive list):

            • fulltext search on tables with descending PK may misbehave (MDEV-27582),
            • different direction of indexes on a Spider table and an underlying table should be avoided (MDEV-27633).

            Due to existing legacy issues causing wrong query results, adding descending indexes to tables or converting ascending ones into descending sometimes bring different results comparing to what they were before. It happens because the change in the execution plan can trigger an old issue or, on the contrary, circumvent an old issue so that the result becomes correct while it was wrong before. These problems are usually reproducible even after replacing the use of the descending index with slight tweaks in query/schema/data, and thus need to be dealt with out of the scope of this task. Many of such legacy issues already exist in MariaDB bug tracking system, possibly there are more to be revealed. If in doubt whether it's a known problem or not, users should report the ones that they encounter.

            elenst Elena Stepanova added a comment - - edited I think the last [partially] tested revision, preview-10.8-MDEV-13756-desc-indexes / 1c4cbb946 , can be pushed into the main branch and released with 10.8.1. There are a few known issues remain, but they are not deemed critical for the initial release. Examples (not an exhaustive list): fulltext search on tables with descending PK may misbehave ( MDEV-27582 ), different direction of indexes on a Spider table and an underlying table should be avoided ( MDEV-27633 ). Due to existing legacy issues causing wrong query results, adding descending indexes to tables or converting ascending ones into descending sometimes bring different results comparing to what they were before. It happens because the change in the execution plan can trigger an old issue or, on the contrary, circumvent an old issue so that the result becomes correct while it was wrong before. These problems are usually reproducible even after replacing the use of the descending index with slight tweaks in query/schema/data, and thus need to be dealt with out of the scope of this task. Many of such legacy issues already exist in MariaDB bug tracking system, possibly there are more to be revealed. If in doubt whether it's a known problem or not, users should report the ones that they encounter.
            philip_38 Philip orleans added a comment -

            I need this feature for INNODB, is there any version that already has it?
            Are there plans to have it?

            philip_38 Philip orleans added a comment - I need this feature for INNODB, is there any version that already has it? Are there plans to have it?
            maxmether Max Mether added a comment -

            This was added to MariaDB Community Server 10.8. Any subsequent release series will also have the feature.

            maxmether Max Mether added a comment - This was added to MariaDB Community Server 10.8. Any subsequent release series will also have the feature.
            rjasdfiii Rick James added a comment -

            Be aware that `ORDER BY a DESC, b DESC` with `INDEX(a,b)` has always been supported – by scanning the index backward.

            I think this is already optimized:
            WHERE a=constant
            ORDER BY a ASC, b DESC

            The feature missing is when `ORDER BY` has multiple columns, but not in the same direction.

            rjasdfiii Rick James added a comment - Be aware that `ORDER BY a DESC, b DESC` with `INDEX(a,b)` has always been supported – by scanning the index backward. I think this is already optimized: WHERE a=constant ORDER BY a ASC, b DESC The feature missing is when `ORDER BY` has multiple columns, but not in the same direction.
            archon810 Artem Russakovskii added a comment -

            @Rick James, I tested on 11.0.3-MariaDB-log and 10.11.3-MariaDB-log, and as soon as I add DESC to b, it adds "using filesort" to explain and the query takes twice as much time.

            archon810 Artem Russakovskii added a comment - @Rick James, I tested on 11.0.3-MariaDB-log and 10.11.3-MariaDB-log, and as soon as I add DESC to b, it adds "using filesort" to explain and the query takes twice as much time.

            People

              serg Sergei Golubchik
              marko Marko Mäkelä
              Votes:
              24 Vote for this issue
              Watchers:
              24 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.