[MDEV-4440] IF NOT EXISTS in multi-action ALTER does not work when the problem is created by a previous part of the ALTER Created: 2013-04-25  Updated: 2014-06-10  Resolved: 2014-06-10

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

Type: Bug Priority: Critical
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   

If ALTER TABLE contains multiple parts, and one of them creates a problem for a next one, IF NOT EXISTS or IF EXISTS clause in the following one does not work, the statement still produces an error, and does not add the column at all:

MariaDB [test]> show create table t1;
 
+-------+---------------------------------------------------------------------------------------+
 
| Table | Create Table                                                                          |
 
+-------+---------------------------------------------------------------------------------------+
 
| t1    | CREATE TABLE `t1` (
 
  `i` int(11) DEFAULT NULL
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
 
+-------+---------------------------------------------------------------------------------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> alter table t1 add column a int, add column if not exists a int;
 
ERROR 1060 (42S21): Duplicate column name 'a'
 
 
 
MariaDB [test]> show create table t1;
 
+-------+---------------------------------------------------------------------------------------+
 
| Table | Create Table                                                                          |
 
+-------+---------------------------------------------------------------------------------------+
 
| t1    | CREATE TABLE `t1` (
 
  `i` int(11) DEFAULT NULL
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
 
+-------+---------------------------------------------------------------------------------------+
 
1 row in set (0.00 sec)

Same for DROP COLUMN.

Test case:

create table t1 (i int);
 
alter table t1 add column a int, add column if not exists a int;
 
show create table t1;
 

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



 Comments   
Comment by Sergei Golubchik [ 2014-01-14 ]

let's just document that. ALTER isn't executing its clauses sequentially, it's doing everything at once. So all IF [NOT] EXISTS clauses apply to the table structure before ALTER, there's no intermediate state.

Comment by Alexey Botchkov [ 2014-06-10 ]

Fixing patch:
http://lists.askmonty.org/pipermail/commits/2014-June/006190.html

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