Bug #61768 ALTER TABLE alter_specifications are order sensitive

• Submitted: 6 Jul 2011 10:02
• Modified: 6 Jul 2011 12:28
• Reporter: Hailin Hu
• Status: Verified
• Category: MySQL Server: DDL
• Severity: S3 (Non-critical)
• Version: 5.0.95, 5.1.58, 5.1.55
• OS: Any
• CPU Architecture: Any

[6 Jul 2011 10:02] Hailin Hu

Description:
alter table add a column using AFTER another column, if the another column also in the alter table clause and using AFTER, error occurs.
if reverse the alter_specifications, it might work.

How to repeat:
mysql> create table dummy (foo int, bar int);
Query OK, 0 rows affected (0.01 sec)
mysql> alter table dummy add column baz int after foo, change column foo foo int after bar;
ERROR 1054 (42S22): Unknown column 'foo' in 'dummy'
mysql> alter table dummy change column foo foo int after bar, add column baz int after foo;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

[6 Jul 2011 12:28] Valeriy Kravchuk

Verified with current mysql-5.0 and mysql-5.5 on Mac OS X also:

macbook-pro:5.5 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 1
Server version: 5.5.15-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 dummy (foo int, bar int);
Query OK, 0 rows affected (0.25 sec)

mysql>  alter table dummy add column baz int after foo, change column foo foo int after
    -> bar;
ERROR 1054 (42S22): Unknown column 'foo' in 'dummy'
mysql> alter table dummy change column foo foo int after bar, add column baz int after
    -> foo;
Query OK, 0 rows affected (0.24 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table dummy\G
*************************** 1. row ***************************
       Table: dummy
Create Table: CREATE TABLE `dummy` (
  `bar` int(11) DEFAULT NULL,
  `foo` int(11) DEFAULT NULL,
  `baz` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)