Comment 0 for bug 1646880

Take the following table structure:

CREATE TABLE `sorting` (
  `id` int(11) unsigned NOT NULL,
  `data` varchar(10) DEFAULT NULL,
  KEY `data_idx` (`data`(2),`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (`id`)
(PARTITION p10 VALUES LESS THAN (10) ENGINE = InnoDB,
 PARTITION p20 VALUES LESS THAN (20) ENGINE = InnoDB) */;

In them, introduce the following values:

INSERT INTO `sorting` VALUES
 (9,'aa2'),
 (11,'aa2'),
 (12,'aa2')
;

At this point, everything is as expected. Now introduce the following value:

INSERT INTO `sorting` VALUES (13,'aa1');

And query the table:

SELECT id FROM sorting WHERE data = 'aa2' ORDER BY id DESC;

You would expect an ordered list. But in result you get:

+----+
| id |
+----+
| 9 |
| 12 |
| 11 |
+----+

Observations / things to note:

- The INDEX is a combination of two fields
- The INDEX has a field which is a part of the full field
- The table uses partitions
- The bug does not happen if you insert in only one partition
- The bug does not happen if the part of the 'data' that is in the index is different; it only happens if that part is the same, but the remaining is different ('aa1' vs 'aa2')
- Restarting MySQl resolves the issue for the current values (but new values get the same error)
- Happens on 5.6.32, 5.6.34, 5.7.16
- ORDER BY ASC works as expected
- With more data, it can also happen dat ORDER BY DESC shows only 2 results, where ORDER BY ASC shows 40 results, for example (in other words, ORDER BY DESC gives partial results).