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

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

    XMLWordPrintable

    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

          • Critical - Crashes, loss of data, severe memory leak.
          • 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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27576 Use of DESC indexes is disabled for MIN/MAX optimization

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

          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
          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

            Activity

              People

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