MySQL Bugs: #34972: ALTER TABLE statement doesn't identify correct column name.

Bug #34972	ALTER TABLE statement doesn't identify correct column name.
Submitted:	1 Mar 2008 17:31	Modified:	29 Sep 2011 13:18
Reporter:	Dae San Hwang	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S2 (Serious)
Version:	5.0.45, 5.0.56	OS:	MacOS (10.4.11)
Assigned to:		CPU Architecture:	Any

Description:
Given the following table:
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment | 
| name  | char(100)        | NO   |     |         |                | 
+-------+------------------+------+-----+---------+----------------+

The following statement causes unexpected error:
ALTER TABLE `people` ADD token char(16) NOT NULL DEFAULT "" AFTER name, MODIFY name char(100) NOT NULL DEFAULT "" AFTER id;

The error message is the following:
ERROR 1054 (42S22): Unknown column 'name' in 'people'

How to repeat:
mysq> CREATE TABLE `people` (
  id int(10) unsigned NOT NULL auto_increment,
  name char(100) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=ucs2;

mysql> ALTER TABLE `people` ADD token char(16) NOT NULL DEFAULT "" AFTER name, MODIFY name char(100) NOT NULL DEFAULT "" AFTER id;

ERROR 1054 (42S22): Unknown column 'name' in 'people'

I think you should use `backquotes` (or "double quotes" in ANSI mode)

try
ALTER TABLE `people` ADD token char(16) NOT NULL DEFAULT '' AFTER `name`, MODIFY `name`
char(100) NOT NULL DEFAULT '' AFTER id;

'name' is a reserved word!  Also singlequotes are 'more correct' for strings than doublequotes I think (but in non-ANSI mode they will both work). 

But error message could be better!

Peter
(not a MySQL person)

Thanks for the reply, Peter.

However, it's not the problem of backquoting. Same error occurs when 'email' is used instead of 'name'..

Thank you for a bug report. Verified just as described on 5.0.56:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3308 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.56-enterprise-gpl-nt MySQL Enterprise Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE `people` (
    ->   id int(10) unsigned NOT NULL auto_increment,
    ->   email char(100) not null default '',
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=ucs2;
Query OK, 0 rows affected (0.16 sec)

mysql> ALTER TABLE `people` ADD token char(16) NOT NULL DEFAULT '' AFTER `email`
, MODIFY `email` char(100) NOT NULL DEFAULT '' AFTER id;
ERROR 1054 (42S22): Unknown column 'email' in 'people'

Ok .. I see now.

further observations 

1) it is not ucs2 realted.  same with utf8

2) this (reversing the operations on table DDL) works

CREATE TABLE `people` (
  id int(10) unsigned NOT NULL auto_increment,
  email char(100) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=ucs2;

ALTER TABLE `people`  MODIFY `email` char(100) NOT NULL DEFAULT '' AFTER id , ADD token char(16) NOT NULL DEFAULT '' AFTER `email` ;

Just ran into the same bug on Linux -- Server version: 5.0.77-log Source distribution, on CentOS 5.3. It looks like maybe you can't use a column in an AFTER clause which you are also MODIFYing in the same ALTER statement?

Noted in 5.6.4 changelog.

An ALTER TABLE that included an ADD ... AFTER operation to add a new
column after a column that had been modified earlier in the statement
failed to find the existing column.