MySQL Bugs: #67775: Moving an existing column after a new one when altering a table causes wrong ord

Bug #67775	Moving an existing column after a new one when altering a table causes wrong ord
Submitted:	30 Nov 2012 22:15	Modified:	11 Feb 2013 18:11
Reporter:	Kostas Stroggylos	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Workbench: SQL Editor	Severity:	S3 (Non-critical)
Version:	5.2.44	OS:	Windows (Microsoft Windows 7 Enterprise Edition Service Pack 1 (build 7601), 64-bit)
Assigned to:		CPU Architecture:	Any
Tags:	WBBugReporter

Description:
When adding a new column in a table and moving an existing column after it, the ALTER commands are generated with the wrong order (i.e. ALTER (existing) COLUMN before ADD (new) column). This causes the first command to fail because the new column has not yet been created when attempting to move the existing one.

----- Developer Notes -----

MySQL Workbench CE for Windows version 5.2.44  revision 9933
OS: Microsoft Windows 7 Enterprise Edition Service Pack 1 (build 7601), 64-bit

How to repeat:
1. Select a table with one or more existing columns, and click 'Alter Table'
2. Add a new column and set it up
3. Move an existing column after the new column
4. Click Apply

Could you please try new Workbench 5.2.46 version?

Thank you for your interest in MySQL Workbench!

Could you please try new Workbench Version 5.2.46 .

Thank you for your interest in MySQL Workbench!.

No, it still behaves in the same manner.

For example, let's say I create a table named 'test' as follows:

CREATE  TABLE `test`.`test` (
  `test_id` INT NOT NULL ,
  `test_description` VARCHAR(50) NOT NULL ,
  PRIMARY KEY (`test_id`) );

Then:
- create a new column `test_name` VARCHAR(50)
- drag column `test_description` after `test_name`
- drag column `test_id` after `test_description`
- click 'Apply'

the statement generated is:
ALTER TABLE `test`.`test` CHANGE COLUMN `test_description` `test_description` VARCHAR(50) NOT NULL  AFTER `test_name` , ADD COLUMN `test_name` VARCHAR(50) NULL  FIRST ;

and when attempting to apply changes the following error is generated:

ERROR 1054: Unknown column 'test_name' in 'test'

SQL Statement:
ALTER TABLE `test`.`test` CHANGE COLUMN `test_description` `test_description` VARCHAR(50) NOT NULL  AFTER `test_name` , ADD COLUMN `test_name` VARCHAR(50) NULL  FIRST

ERROR: Error when running failback script. Details follow.

ERROR 1050: Table 'test' already exists

SQL Statement:
CREATE TABLE `test` (
  `test_id` int(11) NOT NULL,
  `test_description` varchar(50) NOT NULL,
  PRIMARY KEY (`test_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8