Comment 3 for bug 1646880

Revision history for this message
Jackie Xu (aeveus) wrote : Re: "order by desc" gives unordered results

Some additional observations:
- Problem appears to persist after restart (on my local VM running 5.7.11).
- Results are ordered correctly if the index columns are swapped (i.e. (`id`, `data`(2)) instead of (`data`(2), `id`).
- Looks like the values sorted in a partition are cut off at the part where a row appears with a different `data` value (but with the same part in the index).

Test values:
INSERT INTO `sorting` VALUES
 /* p10 */
 (1,'aa2'),
 (2,'aa2'),
 (3,'aa2'),
 /* p20 */
 (11,'aa2'),
 (12,'aa2'),
 (13,'aa2')
;

Introduce the following 'splitting point' values:
INSERT INTO `sorting` VALUES
 /* Break ASC order */
 (2,'aa3'),
 /* Break DESC order */
 (12,'aa1')
;

For the ascending order, MySQL sorts both partitions, and then appends the sorted p456 to p123. Insert a row in between the values of p123 with a greater alphabetical value will result in the rest of the partition being ordered by that greater alphabetical value.

Add an insert to make ordering more clear:
INSERT INTO `sorting` VALUES (4,'aa2'), (5, 'aa2');

+----+-----+
| 1 | aa2 |
| 2 | aa2 |
| 2 | aa3 | <-- Partition order splits here, all greater id values will be appended below p20
| 3 | aa2 |
| 4 | aa2 |
| 5 | aa2 |
+----+-----+
| 11 | aa2 |
| 12 | aa2 |
| 13 | aa2 |
+----+-----+

Resulting rows:

mysql> SELECT id FROM sorting WHERE data = 'aa2' ORDER BY id;
+----+
| id |
+----+
| 1 |
| 2 |
| 11 |
| 12 |
| 13 |
| 3 |
| 4 |
| 5 |
+----+

Same thing works for descending order and alphabetical ordering reversed.