I was able to break the ascending order by using the same table:
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) */;
and the same initial inserts:
INSERT INTO `sorting` VALUES (9,'aa2'), (11,'aa2'), (12,'aa2') ;
but a different final insert:
INSERT INTO `sorting` VALUES (9,'aa3');
Now query the table:
SELECT id FROM sorting WHERE data = 'aa2' ORDER BY id ASC;
The following result shows up:
+----+ | id | +----+ | 11 | | 12 | | 9 | +----+
Strangely enough, removing the order by actually gives the correctly ordered result:
SELECT id FROM sorting WHERE data = `aa2`;
+----+ | id | +----+ | 9 | | 11 | | 12 | +----+
I was able to break the ascending order by using the same table:
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) */;
and the same initial inserts:
INSERT INTO `sorting` VALUES
(9,'aa2'),
(11,'aa2'),
(12,'aa2')
;
but a different final insert:
INSERT INTO `sorting` VALUES (9,'aa3');
Now query the table:
SELECT id FROM sorting WHERE data = 'aa2' ORDER BY id ASC;
The following result shows up:
+----+
| id |
+----+
| 11 |
| 12 |
| 9 |
+----+
Strangely enough, removing the order by actually gives the correctly ordered result:
SELECT id FROM sorting WHERE data = `aa2`;
+----+
| id |
+----+
| 9 |
| 11 |
| 12 |
+----+