Bug #61768 ALTER TABLE alter_specifications are order sensitive

Submitted: 6 Jul 2011 10:02 Modified: 6 Jul 2011 12:28
Reporter: Hailin Hu Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DDL Severity:S3 (Non-critical)
Version:5.0.95, 5.1.58, 5.1.55 OS:Any
Assigned to: 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)

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.039 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.