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:Mac OS X (10.4.11)
Assigned to: CPU Architecture:Any
Triage: Triaged: D2 (Serious)

[1 Mar 2008 17:31] Dae San Hwang
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'
[1 Mar 2008 20:09] Peter Laursen
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)
[1 Mar 2008 23:55] Dae San Hwang
Thanks for the reply, Peter.

However, it's not the problem of backquoting. Same error occurs when 'email' is used instead of 'name'..
[2 Mar 2008 8:00] Valeriy Kravchuk
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'
[2 Mar 2008 10:06] Peter Laursen
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` ;
[11 Mar 2010 10:40] Igor Serebryany
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?
[29 Sep 2011 13:18] Paul Dubois
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.