Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.4(EOL), 10.5, 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.11, 11.0(EOL)
Description
I create a table:
CREATE OR REPLACE TABLE t1 |
(
|
id INT, |
data VARCHAR(20), |
KEY data_id (data,id) |
) COLLATE utf8mb3_unicode_nopad_ci ENGINE=MyISAM |
PARTITION BY RANGE COLUMNS (id) |
(
|
PARTITION p10 VALUES LESS THAN (20), |
PARTITION p20 VALUES LESS THAN MAXVALUE |
);
|
INSERT INTO t1 VALUES (30, 'ss '), (10, 'ß '); |
Now I run ORDER BY queries:
SELECT id FROM t1 WHERE data='ss ' ORDER BY id; |
+------+
|
| id |
|
+------+
|
| 10 |
|
| 30 |
|
+------+
|
SELECT id FROM t1 WHERE data='ss ' ORDER BY id DESC; |
+------+
|
| id |
|
+------+
|
| 30 |
|
| 10 |
|
+------+
|
Looks good so far.
Now I alter the table changing the full key to a prefix key:
ALTER TABLE t1 DROP KEY data_id, ADD KEY data_id2(data(10),id); |
And return ORDER BY queries again:
SELECT id FROM t1 WHERE data='ss ' ORDER BY id; |
+------+
|
| id |
|
+------+
|
| 30 |
|
| 10 |
|
+------+
|
SELECT id FROM t1 WHERE data='ss ' ORDER BY id DESC; |
+------+
|
| id |
|
+------+
|
| 10 |
|
| 30 |
|
+------+
|
Notice the order changed to opposite.
Attachments
Issue Links
- is caused by
-
-
- Closed
-
- relates to
-
-
- Closed
-
-
-
- Open
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-