Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.0.2
-
None
-
None
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
|
Attachments
Issue Links
- relates to
-
MDEV-318 IF (NOT) EXIST clauses for ALTER TABLE (MWL #252)
-
- Closed
-
Activity
| Field | Original Value | New Value |
|---|---|---|
| Link | This issue relates to TODO-455 [ TODO-455 ] |
| Summary | IF NOT EXISTS in multi-action ALTER does not work the problem is created by a previous part of the ALTER | IF NOT EXISTS in multi-action ALTER does not work when the problem is created by a previous part of the ALTER |
| Fix Version/s | 10.0.4 [ 13101 ] | |
| Fix Version/s | 10.0.3 [ 12900 ] |
| Fix Version/s | 10.0.5 [ 13201 ] | |
| Fix Version/s | 10.0.4 [ 13101 ] |
| Fix Version/s | 10.0.6 [ 13202 ] | |
| Fix Version/s | 10.0.5 [ 13201 ] |
| Fix Version/s | 10.0.7 [ 14100 ] | |
| Fix Version/s | 10.0.6 [ 13202 ] |
| Fix Version/s | 10.0.8 [ 14200 ] | |
| Fix Version/s | 10.0.7 [ 14100 ] |
| Fix Version/s | 10.0.9 [ 14400 ] | |
| Fix Version/s | 10.0.8 [ 14200 ] |
| Fix Version/s | 10.0.10 [ 14500 ] | |
| Fix Version/s | 10.0.9 [ 14400 ] |
| Fix Version/s | 10.0.11 [ 15200 ] | |
| Fix Version/s | 10.0.10 [ 14500 ] |
| Fix Version/s | 10.0.12 [ 15201 ] | |
| Fix Version/s | 10.0.11 [ 15200 ] |
| Priority | Minor [ 4 ] | Critical [ 2 ] |
| Status | Open [ 1 ] | In Progress [ 3 ] |
| Resolution | Fixed [ 1 ] | |
| Status | In Progress [ 3 ] | Closed [ 6 ] |
| Workflow | defaullt [ 27233 ] | MariaDB v2 [ 42564 ] |
| Workflow | MariaDB v2 [ 42564 ] | MariaDB v3 [ 61533 ] |
| Workflow | MariaDB v3 [ 61533 ] | MariaDB v4 [ 146633 ] |
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.