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

[14 May 2007 20:43] Kent Wise
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.
[15 May 2007 8:00] Sveta Smirnova
Thank you for the report.

Verified as described.
[30 May 2007 6:19] Sveta Smirnova
Bug has been introduced in version 5.1.11
[1 Jun 2007 19:59] Bugs System
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.
[1 Jun 2007 21:19] Bugs System
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.
[1 Jun 2007 21:24] Bugs System
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.
[2 Jun 2007 17:44] Bugs System
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/
[4 Jun 2007 21:20] Bugs System
Pushed into 5.1.20-beta
[19 Jun 2007 0:52] Paul Dubois
Noted in 5.1.20 changelog.