MySQL Bugs: #28427: ALTER TABLE MODIFY COLUMN ... AFTER re-orders column names but not data

Bug #28427	ALTER TABLE MODIFY COLUMN ... AFTER re-orders column names but not data
Submitted:	14 May 2007 20:43	Modified:	19 Jun 2007 0:52
Reporter:	Kent Wise	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.1.17-beta-log	OS:	Linux (Fedora Core 5)
Assigned to:	Evgeny Potemkin	CPU Architecture:	Any
Tags:	regression

Description:
In trying to change the column order within a table, the column names change order in information_schema.columns, but the order of the data columns within the table remain unchanged.  Being unaware of this issue could lead to scrambled data.

Our 5.1.9-beta-log instance correctly re-orders both the column names and data, in both directions.

How to repeat:
Discovered using Navicat, I confirmed the issue using straight SQL against our 5.1.17 server:

DROP TABLE IF EXISTS test_table;
CREATE TABLE test_table (
  test_unid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  field_1  INT(10) NULL,  field_2  INT(10) NULL,
  field_3  INT(10) NULL,  field_4  INT(10) NULL,
  PRIMARY KEY (test_unid)
) ENGINE=InnoDb;

INSERT INTO test_table (field_1, field_2, field_3, field_4) VALUES (1, 2, 3, 4);
SELECT * FROM test_table;

ALTER TABLE test_table ADD COLUMN field_5 INT(10);
SELECT * FROM test_table;

ALTER TABLE test_table MODIFY COLUMN field_5 INT(10) AFTER field_2;
SELECT * FROM test_table;

ALTER TABLE test_table MODIFY COLUMN field_5 INT(10) AFTER field_4;
SELECT * FROM test_table;

DROP TABLE IF EXISTS test_table;
CREATE TABLE test_table (
  test_unid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  field_1  VARCHAR(12) NULL,  field_2  VARCHAR(12) NULL,
  field_3  VARCHAR(12) NULL,  field_4  VARCHAR(12) NULL,
  PRIMARY KEY (test_unid)
) ENGINE=InnoDb;

INSERT INTO test_table (field_1, field_2, field_3) VALUES (1, 'B', 'C');
SELECT * FROM test_table;

ALTER TABLE test_table ADD COLUMN field_5 VARCHAR(12);
SELECT * FROM test_table;

ALTER TABLE test_table MODIFY COLUMN field_5 VARCHAR(12) AFTER field_2;
SELECT * FROM test_table;

ALTER TABLE test_table MODIFY COLUMN field_5 VARCHAR(12) AFTER field_4;
SELECT * FROM test_table;

yields the results:

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 1 row affected (0.00 sec)

+-----------+---------+---------+---------+---------+
| test_unid | field_1 | field_2 | field_3 | field_4 |
+-----------+---------+---------+---------+---------+
|         1 |       1 |       2 |       3 |       4 |
+-----------+---------+---------+---------+---------+
1 row in set (0.00 sec)

Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

+-----------+---------+---------+---------+---------+---------+
| test_unid | field_1 | field_2 | field_3 | field_4 | field_5 |
+-----------+---------+---------+---------+---------+---------+
|         1 |       1 |       2 |       3 |       4 |    NULL |
+-----------+---------+---------+---------+---------+---------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

+-----------+---------+---------+---------+---------+---------+
| test_unid | field_1 | field_2 | field_5 | field_3 | field_4 |
+-----------+---------+---------+---------+---------+---------+
|         1 |       1 |       2 |       3 |       4 |    NULL |
+-----------+---------+---------+---------+---------+---------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

+-----------+---------+---------+---------+---------+---------+
| test_unid | field_1 | field_2 | field_3 | field_4 | field_5 |
+-----------+---------+---------+---------+---------+---------+
|         1 |       1 |       2 |       3 |       4 |    NULL |
+-----------+---------+---------+---------+---------+---------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

+-----------+---------+---------+---------+---------+
| test_unid | field_1 | field_2 | field_3 | field_4 |
+-----------+---------+---------+---------+---------+
|         1 |       1 | B       | C       | NULL    |
+-----------+---------+---------+---------+---------+
1 row in set (0.00 sec)

Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

+-----------+---------+---------+---------+---------+---------+
| test_unid | field_1 | field_2 | field_3 | field_4 | field_5 |
+-----------+---------+---------+---------+---------+---------+
|         1 |       1 | B       | C       | NULL    | NULL    |
+-----------+---------+---------+---------+---------+---------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

+-----------+---------+---------+---------+---------+---------+
| test_unid | field_1 | field_2 | field_5 | field_3 | field_4 |
+-----------+---------+---------+---------+---------+---------+
|         1 |       1 | B       | C       | NULL    | NULL    |
+-----------+---------+---------+---------+---------+---------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

+-----------+---------+---------+---------+---------+---------+
| test_unid | field_1 | field_2 | field_3 | field_4 | field_5 |
+-----------+---------+---------+---------+---------+---------+
|         1 |       1 | B       | C       | NULL    | NULL    |
+-----------+---------+---------+---------+---------+---------+
1 row in set (0.00 sec)

Using MyIsam instead of InnoDb yielded the same results, as did using an "AFTER" clause on the "ADD COLUMN" statement.  It also appears that if the columns involved are different data types, the re-ordering occurs properly.

Suggested fix:
Restore lost functionality.

Thank you for the report.

Verified as described.

Bug has been introduced in version 5.1.11

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/27962

ChangeSet@1.2518, 2007-06-01 23:56:45+04:00, evgen@moonbone.local +3 -0
  Bug#28427: Columns were renamed instead of moving by ALTER TABLE.
  
  To avoid unnecessary work the mysql_alter_table() function takes the
  list of table fields and applies all changes to it (drops/moves/renames/etc).
  Then this function compares the new list and the old one.
  If changes requres only .frm to be modified then the actual data isn't copied.
  To detects changed types, null/not null and other columns attributes but
  names are compared. If a column was moved and has replaced another column
  with all the same attributes except name then the mysql_alter_table() function
  would wrongly decide that two fields were renamed instead of one column moved.
  This leads to avoiding of columns data copying and thus returning wrong data
  for the moved column and all columns after it.
  
  Now the mysql_alter_table() function forces table data copying by setting
  the need_copy_table flag when it finds a moved column at the stage of the
  modified fields list creation.

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/27971

ChangeSet@1.2518, 2007-06-02 01:16:47+04:00, evgen@moonbone.local +3 -0
  Bug#28427: Columns were renamed instead of moving by ALTER TABLE.
  
  To avoid unnecessary work the mysql_alter_table function takes the
  list of table fields and applies all changes to it (drops/moves/renames/etc).
  Then this function compares the new list and the old one. If the changes
  require only .frm to be modified then the actual data isn't copied. To detect
  changes all columns attributes but names are compared. When a column has been
  moved and has replaced another column with the same attributes except name
  the mysql_alter_table function wrongly decides that two fields has bee just
  renamed. As a result the data from the moved column and from all columns
  after it is not copied.
  
  Now the mysql_alter_table function forces table data copying by setting
  the need_copy_table flag when it finds a moved column. The flag is set at
  the stage when the modified fields are created.

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/27972

ChangeSet@1.2518, 2007-06-02 01:21:18+04:00, evgen@moonbone.local +3 -0
  Bug#28427: Columns were renamed instead of moving by ALTER TABLE.
  
  To avoid unnecessary work the mysql_alter_table function takes the
  list of table fields and applies all changes to it (drops/moves/renames/etc).
  Then this function compares the new list and the old one. If the changes
  require only .frm to be modified then the actual data isn't copied. To detect
  changes all columns attributes but names are compared. When a column has been
  moved and has replaced another column with the same attributes except name
  the mysql_alter_table function wrongly decides that two fields has been just
  renamed. As a result the data from the moved column and from all columns
  after it is not copied.
  
  Now the mysql_alter_table function forces table data copying by setting
  the need_copy_table flag when it finds a moved column. The flag is set at
  the stage when the modified fields are created.

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/27991

ChangeSet@1.2520, 2007-06-02 21:43:04+04:00, sergefp@mysql.com +1 -0
  BUG#28427: fix typo, s/bool/uint/

Pushed into 5.1.20-beta

Noted in 5.1.20 changelog.