Description:
Reproduced on various 5.5 versions (5.5.29 on Windows, 5.5.46 on CentOS).
Having an InnoDB, partitioned table, with an integer PK and using:
INSERT INTO tbl (pk, non-pk columns)
SELECT NULL, (non-pk columns)
FROM tbl
ORDER BY pk
does not honor the rows order returned by SELECT and inserts the rows in a seemingly random order. See example for a very eloquent demonstration.
The issue does not occur in future versions of MySQL server (5.6, 5.7).
The issue does not occur on MyISAM tables.
The issue does not occur if the table is not partitioned.
How to repeat:
I have reduced the demonstration to this very simple example:
mysql> create table test(id int(10) unsigned NOT NULL AUTO_INCREMENT primary key, label varchar(255) not null) ENGINE=InnoDB PARTITION BY HASH (id) PARTITIONS 2;
Query OK, 0 rows affected (0.32 sec)
mysql> insert into test (label) values("one"), ("two");
Query OK, 2 rows affected (0.04 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from test order by id;
+----+-------+
| id | label |
+----+-------+
| 1 | one |
| 2 | two |
+----+-------+
2 rows in set (0.00 sec)
mysql> insert into test select null, label from test order by id;
Query OK, 2 rows affected (0.06 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from test order by id;
+----+-------+
| id | label |
+----+-------+
| 1 | one |
| 2 | two |
| 3 | two |
| 4 | one |
+----+-------+
4 rows in set (0.00 sec)
Suggested fix:
Since select * from test order by id returns the rows in the order "one", "two", it would be expected that the insert added them in the same order, thus the final select should return:
mysql> select * from test order by id;
+----+-------+
| id | label |
+----+-------+
| 1 | one |
| 2 | two |
| 3 | one |
| 4 | two |
+----+-------+
4 rows in set (0.00 sec)
Description: Reproduced on various 5.5 versions (5.5.29 on Windows, 5.5.46 on CentOS). Having an InnoDB, partitioned table, with an integer PK and using: INSERT INTO tbl (pk, non-pk columns) SELECT NULL, (non-pk columns) FROM tbl ORDER BY pk does not honor the rows order returned by SELECT and inserts the rows in a seemingly random order. See example for a very eloquent demonstration. The issue does not occur in future versions of MySQL server (5.6, 5.7). The issue does not occur on MyISAM tables. The issue does not occur if the table is not partitioned. How to repeat: I have reduced the demonstration to this very simple example: mysql> create table test(id int(10) unsigned NOT NULL AUTO_INCREMENT primary key, label varchar(255) not null) ENGINE=InnoDB PARTITION BY HASH (id) PARTITIONS 2; Query OK, 0 rows affected (0.32 sec) mysql> insert into test (label) values("one"), ("two"); Query OK, 2 rows affected (0.04 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from test order by id; +----+-------+ | id | label | +----+-------+ | 1 | one | | 2 | two | +----+-------+ 2 rows in set (0.00 sec) mysql> insert into test select null, label from test order by id; Query OK, 2 rows affected (0.06 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from test order by id; +----+-------+ | id | label | +----+-------+ | 1 | one | | 2 | two | | 3 | two | | 4 | one | +----+-------+ 4 rows in set (0.00 sec) Suggested fix: Since select * from test order by id returns the rows in the order "one", "two", it would be expected that the insert added them in the same order, thus the final select should return: mysql> select * from test order by id; +----+-------+ | id | label | +----+-------+ | 1 | one | | 2 | two | | 3 | one | | 4 | two | +----+-------+ 4 rows in set (0.00 sec)