Description:
In certain cases results aren't ordered by what's specified in ORDER BY
How to repeat:
CREATE TABLE order_test (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
val varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_cs NOT NULL,
PRIMARY KEY (id),
KEY val(val)
);
INSERT INTO order_test (val) VALUES ('Wä'), ('E');
-- Incorrect order
SELECT *, val = 'wä', val = 'Wä', val = 'E'
FROM order_test
ORDER BY val = 'wä' DESC, val = 'Wä' DESC, val = 'E' DESC;
+----+-----+-------------+-------------+-----------+
| id | val | val = 'wä' | val = 'Wä' | val = 'E' |
+----+-----+-------------+-------------+-----------+
| 2 | E | 0 | 0 | 1 |
| 1 | Wä | 0 | 1 | 0 |
+----+-----+-------------+-------------+-----------+
2 rows in set (0.00 sec)
-- Expected order
SELECT *, val = 'ka', val = 'Wä', val = 'E'
FROM order_test
ORDER BY val = 'ka' DESC, val = 'Wä' DESC, val = 'E' DESC;
+----+-----+------------+-------------+-----------+
| id | val | val = 'ka' | val = 'Wä' | val = 'E' |
+----+-----+------------+-------------+-----------+
| 1 | Wä | 0 | 1 | 0 |
| 2 | E | 0 | 0 | 1 |
+----+-----+------------+-------------+-----------+
2 rows in set (0.00 sec)
Description: In certain cases results aren't ordered by what's specified in ORDER BY How to repeat: CREATE TABLE order_test ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, val varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_cs NOT NULL, PRIMARY KEY (id), KEY val(val) ); INSERT INTO order_test (val) VALUES ('Wä'), ('E'); -- Incorrect order SELECT *, val = 'wä', val = 'Wä', val = 'E' FROM order_test ORDER BY val = 'wä' DESC, val = 'Wä' DESC, val = 'E' DESC; +----+-----+-------------+-------------+-----------+ | id | val | val = 'wä' | val = 'Wä' | val = 'E' | +----+-----+-------------+-------------+-----------+ | 2 | E | 0 | 0 | 1 | | 1 | Wä | 0 | 1 | 0 | +----+-----+-------------+-------------+-----------+ 2 rows in set (0.00 sec) -- Expected order SELECT *, val = 'ka', val = 'Wä', val = 'E' FROM order_test ORDER BY val = 'ka' DESC, val = 'Wä' DESC, val = 'E' DESC; +----+-----+------------+-------------+-----------+ | id | val | val = 'ka' | val = 'Wä' | val = 'E' | +----+-----+------------+-------------+-----------+ | 1 | Wä | 0 | 1 | 0 | | 2 | E | 0 | 0 | 1 | +----+-----+------------+-------------+-----------+ 2 rows in set (0.00 sec)