Details
-
Task
-
Status: Closed (View Workflow)
-
Blocker
-
Resolution: Fixed
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
-
MDEV-27300 Result differs with DESC index upon comparison of timestamp with incompatible value
- Open
-
MDEV-27303 Table corruption after insert into a non-InnoDB table with DESC index
- Closed
-
MDEV-27309 Server crash or ASAN memcpy-param-overlap upon INSERT into Aria/MyISAM table with DESC key
- Closed
-
MDEV-27330 Wrong sorting order with DESC index and empty strings in MyISAM/Aria table
- Closed
-
MDEV-27340 NULL gets lost (becomes empty string), SELECT hangs with DESC index on MyISAM/Aria table
- Closed
-
MDEV-27347 MyISAM/Aria table is marked as crashed upon updating NULL values in DESC index
- Closed
-
MDEV-27374 InnoDB table becomes corrupt after renaming DESC-indexed column
- Closed
-
MDEV-27392 DESC attribute is ignored when index is re-created within single ALTER on InnoDB table
- Closed
-
MDEV-27393 Timezone tables cannot have descending indexes
- Closed
-
MDEV-27396 DESC index attribute remains in Archive table definition, despite being apparently ignored
- Closed
-
MDEV-27398 DESC index causes wrong (empty) result on Federated tables
- Closed
-
MDEV-27406 Index on a HEAP table retains DESC attribute despite being hash
- Closed
-
MDEV-27407 Different ASC/DESC index attributes on MERGE and underlying table can cause wrong results
- Closed
-
MDEV-27408 DESC index on a Mroonga table causes wrong order of result set
- Closed
-
MDEV-27419 MariaDB doesn't choose DESC index for ORDER BY DESC when MySQL does
- Confirmed
-
MDEV-27420 Spider tables retain DESC attribute on indexes even though it has no meaning
- Closed
-
MDEV-27426 Wrong result upon query using index_merge with DESC key
- Closed
-
MDEV-27432 ASC/DESC primary and unique keys cause index inconsistency between InnoDB and server
- Closed
-
MDEV-27434 DESC attribute does not work with auto-increment on secondary column of multi-part index
- Closed
-
MDEV-27439 AUTO_INCREMENT columns with DESC index do not work
- Closed
-
MDEV-27445 Index inconsistency and assertion failure after renaming virtual column with DESC key
- Closed
-
MDEV-27529 Wrong result upon query using index_merge with DESC key (#2)
- Closed
-
MDEV-27576 Use DESC indexes for MIN/MAX optimization
- Closed
-
MDEV-27578 DESC attribute upon spatial index creation prevents ER_DUP_INDEX warning
- Open
-
MDEV-27580 Bogus warning ER_TRUNCATED_WRONG_VALUE with DESC key on partitioned Spider table
- Open
-
MDEV-27581 Wrong result with DESC key on partitioned Spider table
- Closed
-
MDEV-27585 Auto-increment on secondary column increments unexpectedly with DESC key
- Closed
-
MDEV-27586 Auto-increment does not work with DESC on MERGE table
- Closed
-
MDEV-27588 MyISAM/Aria descending index compression is not as efficient as ascending
- Closed
-
MDEV-27589 OQGRAPH returns a different result set if backing table has descending PK
- Open
-
MDEV-27590 Auto-increment on Spider tables with DESC PK does not work properly
- Closed
-
MDEV-27591 Connect tables (FIX/DOS) don't work with DESC keys - wrong results
- Closed
-
MDEV-27592 DESC keys: Data corruption or Assertion `index->n_core_null_bytes <= (((index->n_nullable) + 7) >> 3) || index->in_instant_init' failed
- Closed
-
MDEV-27617 HANDLER KEY > (x) does not work with DESC keys, Spider is affected and returns wrong results
- Closed
-
MDEV-27623 Wrong result with DESC key on InnoDB table
- Closed
-
MDEV-27633 Wrong result with different direction of index on Spider and underlying tables in handler mode
- Open
-
MDEV-27683 EXCHANGE PARTITION allows different index direction, but causes further errors
- Closed
-
MDEV-29047 RocksDB retains unsupported DESC key option after error, causes further failures
- Open
-
MDEV-30845 filesort still select when desc/asc index matches ORDER BY
- Open
-
MDEV-35169 ALTER TABLE...IMPORT TABLESPACE does not work with INDEX DESC
- Confirmed
- includes
-
MDEV-26938 support descending indexes internally in InnoDB
- Closed
-
MDEV-26939 support descending indexes for simple ORDER BY
- Closed
-
MDEV-26996 Support descending indexes in the range optimizer
- Closed
- is part of
-
MDEV-27373 Q1 2022 release merge
- Closed
- relates to
-
MDEV-4621 select returns null for information_schema.statistics.collation field
- Closed
-
MDEV-23571 InnoDB does not raise a warning if ALTER TABLE index operations are optimized away
- Open
-
MDEV-27577 Recovery behaves differently with DESC PK in innodb.leaf_page_corrupted_during_recovery
- Closed
-
MDEV-27302 Backward index scan is not reflected in EXPLAIN output
- Open
-
MDEV-27582 Fulltext search DDL decrements FTS_DOC_ID
- Closed