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

ALTER INDEX is inconsistent with ADD/DROP/RENAME index

Details

    Description

      The new sub-command ALTER TABLE .. ALTER INDEX, added within the scope of MDEV-7317 (Ignored indexes), is inconsistent with its sibling sub-commands.

      ALTER doesn't accept KEY as a synonym for INDEX, while ADD/DROP/RENAME do :

      10.6 03ff588d1

      MariaDB [test]> alter table t drop key a;
      Query OK, 0 rows affected (0.023 sec)
      Records: 0  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> alter table t add key a(a);
      Query OK, 0 rows affected (0.061 sec)
      Records: 0  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> alter table t alter key a ignore;
      ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'key a ignore' at line 1
      

      ALTER doesn't accept IF EXISTS clause, while DROP/RENAME do, and ADD accepts IF NOT EXISTS:

      MariaDB [test]> alter table t drop index if exists b;
      Query OK, 0 rows affected, 1 warning (0.001 sec)
      Records: 0  Duplicates: 0  Warnings: 1
       
      MariaDB [test]> alter table t alter index if exists b ignore;
      ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'if exists b ignore' at line 1
      

      Attachments

        Activity

          Pushed d9c5eb2f33afd5764519330b909d59e1215b3d19, Varun's patch adding ALTER TABLE ALTER KEY [NOT] IGNORED.

          psergei Sergei Petrunia added a comment - Pushed d9c5eb2f33afd5764519330b909d59e1215b3d19, Varun's patch adding ALTER TABLE ALTER KEY [NOT] IGNORED .

          Supporting if exists seems like a lower priority for me.

          psergei Sergei Petrunia added a comment - Supporting if exists seems like a lower priority for me.

          psergey:
          Maybe it makes sense to extract "if exists" into a separate item and close this one, to avoid confusion in the commit/release history (when the same bug appears as fixed in different releases).

          elenst Elena Stepanova added a comment - psergey : Maybe it makes sense to extract "if exists" into a separate item and close this one, to avoid confusion in the commit/release history (when the same bug appears as fixed in different releases).

          Ok adding IF EXISTS turned out to be easy: http://lists.askmonty.org/pipermail/commits/2021-May/014603.html , bb-10.6-mdev25078. Need a review for this.

          psergei Sergei Petrunia added a comment - Ok adding IF EXISTS turned out to be easy: http://lists.askmonty.org/pipermail/commits/2021-May/014603.html , bb-10.6-mdev25078. Need a review for this.

          holyfoot , please review the above IF EXISTS patch

          psergei Sergei Petrunia added a comment - holyfoot , please review the above IF EXISTS patch

          fix for the IF EXISTS clause looks fine.

          holyfoot Alexey Botchkov added a comment - fix for the IF EXISTS clause looks fine.

          People

            psergei Sergei Petrunia
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            4 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.