| 2016-12-02 16:01:54 |
Patric Stout (Fox-IT) |
description |
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). |
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). |
|
