MySQL Bugs: #59362: Single ALTER TABLE behaves differently than two separate ALTER TABLES

Bug #59362	Single ALTER TABLE behaves differently than two separate ALTER TABLES
Submitted:	7 Jan 2011 21:10	Modified:	20 Aug 2014 20:14
Reporter:	Scott Noyes	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	5.1+	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Dropping and adding a column with the same name but different default value behaves differently if both operations are done in a single ALTER TABLE statement than if done with two separate ALTER TABLE statements.

How to repeat:
CREATE TABLE t (id int, a int default 1);
INSERT INTO t (id) VALUES (1);

SELECT * FROM t;

+------+------+
| id   | a    |
+------+------+
|    1 |    1 |
+------+------+
1 row in set (0.00 sec)

ALTER TABLE t DROP COLUMN a, ADD COLUMN a INT DEFAULT 2;

SELECT * FROM t;

+------+------+
| id   | a    |
+------+------+
|    1 |    1 |
+------+------+
1 row in set (0.00 sec)

ALTER TABLE t DROP COLUMN a;
ALTER TABLE t ADD COLUMN a INT DEFAULT 2;

SELECT * FROM t;
+------+------+
| id   | a    |
+------+------+
|    1 |    2 |
+------+------+
1 row in set (0.00 sec)

Suggested fix:
The single statement should perform the same as two separate statements.

Which versions are affected?

Verified with 5.1.55:

macbook-pro:trunk openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.55-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE t (id int, a int default 1);
Query OK, 0 rows affected (0.07 sec)

mysql> INSERT INTO t (id) VALUES (1);
Query OK, 1 row affected (0.03 sec)

mysql> 
mysql> SELECT * FROM t;
+------+------+
| id   | a    |
+------+------+
|    1 |    1 |
+------+------+
1 row in set (0.00 sec)

mysql> 
mysql> ALTER TABLE t DROP COLUMN a, ADD COLUMN a INT DEFAULT 2;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM t;
+------+------+
| id   | a    |
+------+------+
|    1 |    1 |
+------+------+
1 row in set (0.00 sec)

mysql> ALTER TABLE t DROP COLUMN a;
Query OK, 1 row affected (0.06 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE t ADD COLUMN a INT DEFAULT 2;
Query OK, 1 row affected (0.14 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM t;
+------+------+
| id   | a    |
+------+------+
|    1 |    2 |
+------+------+
1 row in set (0.00 sec)

Hello!

This issue has been also described in bug #61493 "REORDERING COLUMNS
TO POSITION FIRST CAN CAUSE DATA TO BE CORRUPTED" and was fixed by
patch that fixed the latter bug.
Therefore I am closing this bug report as a Duplicate.