Bug #79372 INSERT ... SELECT does not preserve rows order in InnoDB partitioned tables
Submitted: 21 Nov 2015 11:40 Modified: 21 Nov 2015 13:32
Reporter: Radu Galesanu Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Partitions Severity:S2 (Serious)
Version:5.5, 5.5.48 OS:Any
Assigned to: CPU Architecture:Any
Tags: insert select partition innodb

[21 Nov 2015 11:40] Radu Galesanu
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)
[21 Nov 2015 13:32] Umesh Shastry
Hello Radu Galesanu,

Thank you for the report and test case.

Thanks,
Umesh