[MDEV-27392] DESC attribute is ignored when index is re-created within single ALTER on InnoDB table Created: 2021-12-30  Updated: 2022-01-08  Resolved: 2022-01-08

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Alter Table, Storage Engine - InnoDB
Affects Version/s: N/A
Fix Version/s: 10.8.1

Type: Bug Priority: Critical
Reporter: Elena Stepanova Assignee: Unassigned
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Problem/Incident
is caused by MDEV-13756 Implement descending index: KEY (a DE... Closed

 Description    

--source include/have_innodb.inc
 
 
 
create or replace table t (pk int primary key, i int, key idx(i)) engine=InnoDB;
 
show create table t;
 
alter table t drop key idx, add key idx(i desc);
 
show create table t;
 
 
 
# This works:
 
alter table t drop key idx;
 
alter table t add key idx(i desc);
 
show create table t;
 
 
 
# Cleanup
 
drop table t;

preview-10.8-MDEV-13756-desc-indexes 383b51d68

 
alter table t drop key idx, add key idx(i desc);
 
show create table t;
 
Table	Create Table
 
t	CREATE TABLE `t` (
 
  `pk` int(11) NOT NULL,
 
  `i` int(11) DEFAULT NULL,
 
  PRIMARY KEY (`pk`),
 
  KEY `idx` (`i`)
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Not reproducible with MyISAM, Aria.

When the two ALTERs are executed separately, it works:

alter table t drop key idx;
 
alter table t add key idx(i desc);
 
show create table t;
 
Table	Create Table
 
t	CREATE TABLE `t` (
 
  `pk` int(11) NOT NULL,
 
  `i` int(11) DEFAULT NULL,
 
  PRIMARY KEY (`pk`),
 
  KEY `idx` (`i` DESC)
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1



 Comments   
Comment by Sergei Golubchik [ 2022-01-08 ]

cannot repeat on c13c1079b7d, likely fixed by commit for MDEV-27432

Comment by Elena Stepanova [ 2022-01-08 ]

Right, it was fixed by 

commit 353c608db9663edc9e557270bae227c8fd25a7e7
 
Author: Thirunarayanan Balathandayuthapani
 
Date:   Fri Jan 7 19:25:12 2022 +0530
 
 
 
    MDEV-27432 ASC/DESC primary and unique keys cause index inconsistency between InnoDB and server

This is a bit disturbing because it was earlier said to be unrelated.

Generated at Thu Feb 08 09:52:34 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.