[MDEV-25078] ALTER INDEX is inconsistent with ADD/DROP/RENAME index Created: 2021-03-07  Updated: 2021-05-25  Resolved: 2021-05-24

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Alter Table
Affects Version/s: 10.6
Fix Version/s: 10.6.2

Type: Bug Priority: Minor
Reporter: Elena Stepanova Assignee: Sergei Petrunia
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates

 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



 Comments   
Comment by Sergei Petrunia [ 2021-03-17 ]

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

Comment by Sergei Petrunia [ 2021-03-17 ]

Supporting if exists seems like a lower priority for me.

Comment by Elena Stepanova [ 2021-03-17 ]

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).

Comment by Sergei Petrunia [ 2021-05-14 ]

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.

Comment by Sergei Petrunia [ 2021-05-18 ]

holyfoot , please review the above IF EXISTS patch

Comment by Alexey Botchkov [ 2021-05-24 ]

fix for the IF EXISTS clause looks fine.

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