MySQL Bugs: #10474: alter table error

Bug #10474	alter table error
Submitted:	9 May 2005 13:24	Modified:	29 Mar 2011 18:33
Reporter:	Roberto Spadim (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S4 (Feature request)
Version:	4.1.9	OS:	Linux (linux)
Assigned to:		CPU Architecture:	Any

Description:
ALTER TABLE `spamov_itens_digitacao` 
ADD `d_frete` DOUBLE not null AFTER `d_ispa`,
ADD `d_pag` DOUBLE not null AFTER `d_frete`,
CHANGE `d_ispa` `d_itab` double not null

return:

Unknown column 'd_ispa' in 'spamov_itens_digitacao'

but!! field d_ispa exists, so server changed the name to d_itab before execute add

How to repeat:
create table spamov_itens_digitacao(
d_ispa double not null
)

ALTER TABLE `spamov_itens_digitacao` 
ADD `d_frete` DOUBLE not null AFTER `d_ispa`,
ADD `d_pag` DOUBLE not null AFTER `d_frete`,
CHANGE `d_ispa` `d_itab` double not null

ALTER TABLE `spamov_itens_digitacao` CHANGE `d_ispa` `d_itab` double not null;

alone works fine but as part of the combined ALTER statement it fails.

right

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/14437

ChangeSet@1.2535, 2006-10-26 09:50:11-07:00, acurtis@xiphis.org +3 -0
  Bug#10474
    "Alter table forgets original column names when handling 'AFTER'"
    always compare AFTER columns against original column names.

Quoting email exchange with PeterG:
-----------------------------------------------------------------------------
Konstantin Osipov wrote:
> Hi,
>
> Is the request in the bug report in accordance with the standard?

No. The standard ALTER TABLE syntax is
"
<alter table statement> ::=
ALTER TABLE <table name> <alter table action>
<alter table action> ::=
<add column definition>
| <alter column definition>
| <drop column definition>
| <add table constraint definition>
| <drop table constraint definition>
"
In other words, you cannot "add column definition"
and "alter column definition" in a single statement.

Oracle allows multiple clauses but with respect to
"rename_column_clause" the Oracle manual says:
"
rename_column_clause
...
Restrictions on Renaming Columns Renaming columns is subject to the following
+restrictions:
* You cannot combine this clause with any of the other column_clauses in the
+same statement.
"
http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_3001.htm#i210392...
So the Bug#10474 syntax is illegal in Oracle too.
---------------------------------------------------------------------------
In other words, this is not standard. Some users may expect an error in accordance with the standard.
Setting to "Won't fix".

in a table with more than 10 million rows this could help, only one query is faster and more secure than many queries, for example with myisam i should execure lock table, alter 1, alter 2, unlock tables, if i have only one query, just run it and wait
ok, it's not ok with standards but could be a feature or mysql sql language extension?

i´m closing since we can disable/enable and i tested in newer versions and it work without problems