[MDEV-4437] ALTER TABLE .. ADD UNIQUE INDEX IF NOT EXISTS causes syntax error Created: 2013-04-25  Updated: 2013-11-14  Resolved: 2013-11-14

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.0.2
Fix Version/s: 10.0.6

Type: Bug Priority: Minor
Reporter: Elena Stepanova Assignee: Alexey Botchkov
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-318 IF (NOT) EXIST clauses for ALTER TABL... Closed

 Description   

Test case:

create table t1 (i int);
 
alter table t1 add unique index if not exists idx(i);

Output:

MariaDB [test]> create table t1 (i int);
 
Query OK, 0 rows affected (0.32 sec)
 
 
 
MariaDB [test]> alter table t1 add unique index if not exists idx(i);
 
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 not exists idx(i)' at line 1

Apparently this variation somehow slipped from the syntax changes. This one works:

MariaDB [test]> create unique index if not exists idx2 on t1 (i);
 
Query OK, 0 rows affected (0.42 sec)
 
Records: 0  Duplicates: 0  Warnings: 0

This of course works too:

MariaDB [test]> alter table t1 add unique index idx1(i);
 
Query OK, 0 rows affected (0.33 sec)
 
Records: 0  Duplicates: 0  Warnings: 0

And other modifiers work (with corresponding data types):

MariaDB [test]> alter table t2 add fulltext index if not exists idx(b);
 
Query OK, 0 rows affected (0.23 sec)               
Records: 0  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> alter table t2 add spatial index if not exists idx2(a);
 
Query OK, 0 rows affected (0.19 sec)               
Records: 0  Duplicates: 0  Warnings: 0
 

revision-id: bar@mariadb.org-20130424142022-u4xhikvoqggze9b0
 
revno: 3745
 
branch-nick: 10.0



 Comments   
Comment by Alexey Botchkov [ 2013-11-13 ]

Patch to be reivewed:
http://lists.askmonty.org/pipermail/commits/2013-November/005663.html

Comment by Sergei Golubchik [ 2013-11-13 ]

ok to push

Generated at Thu Feb 08 06:56:24 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.