[MDEV-13756] Implement descending index: KEY (a DESC, b ASC) - Jira

XML

Word

Printable

Details

Type: Task
Status: Closed (View Workflow)
Priority: Blocker
Resolution: Fixed
Fix Version/s: 10.8.1
Component/s: Optimizer, Storage Engine - InnoDB
Labels:

PM Planning:
- P1
- RM_ES23_OPTIMIZER

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

Closed

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

Closed

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

MDEV-35849 InnoDB: index records in a wrong order upon CHECK with DESC index

Closed

(35 causes, 3 includes, 1 is part of, 6 relates to)

Activity

People

Assignee:: Sergei Golubchik

Reporter:: Marko Mäkelä

Votes:: 24 Vote for this issue

Watchers:: 23 Start watching this issue

Dates

Created:: 2017-09-07 12:30

Updated:: 2025-01-15 05:28

Resolved:: 2022-01-26 18:15

Time Tracking

Estimated:

45d

Remaining:

Logged:

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.