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

DESC attribute is ignored when index is re-created within single ALTER on InnoDB table

Details

    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

      Attachments

        Issue Links

          is caused by

          Task - A task that needs to be done. MDEV-13756 Implement descending index: KEY (a DESC, b ASC)

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

          Activity

            Ascending order - Click to sort in descending order
            elenst Elena Stepanova created issue -
            elenst Elena Stepanova made changes -
            Field Original Value New Value
            elenst Elena Stepanova made changes -
            Description {code:sql}
            --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;
            {code}

            {code:sql|title=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
            {code}

            When the two ALTERs are executed separately, it works:
            {code:sql}
            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
            {code}

            Not reproducible with MyISAM, Aria.             		{code:sql}
            --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;
            {code}

            {code:sql|title=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
            {code}

            Not reproducible with MyISAM, Aria.

            When the two ALTERs are executed separately, it works:
            {code:sql}
            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
            {code}
            elenst Elena Stepanova made changes -
            Assignee Marko Mäkelä [ marko ] Sergei Golubchik [ serg ]
            serg Sergei Golubchik added a comment - - edited

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

            serg Sergei Golubchik added a comment - - edited cannot repeat on c13c1079b7d, likely fixed by commit for MDEV-27432
            serg Sergei Golubchik made changes -
            Assignee Sergei Golubchik [ serg ] Elena Stepanova [ elenst ]
            elenst Elena Stepanova added a comment -

            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.

            elenst Elena Stepanova added a comment - 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.
            elenst Elena Stepanova made changes -
            Fix Version/s 10.8.1 [ 26815 ]
            Fix Version/s 10.8 [ 26121 ]
            Assignee Elena Stepanova [ elenst ]
            elenst Elena Stepanova made changes -
            Resolution Fixed [ 1 ]
            Status Open [ 1 ] Closed [ 6 ]

            People

              Unassigned Unassigned
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              2 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.