Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.5, 10.6, 10.11, 11.0, 11.1, 11.2, 11.3, 10.6.16, 10.11.6
-
None
Description
When explicitly specifying collation on a non-character sort column , the column type is not taken into account and it may be sorted incorrectly. In 10.4 an error was thrown, but in 10.6 and 10.11 no error is thrown but sort results are unpredictable.
create table ct (ts datetime); |
insert into ct select now(); |
-- repeat a few more times
|
select * from ct order by ts COLLATE utf8_bin; |
+---------------------+ |
| ts |
|
+---------------------+ |
| 2024-01-26 10:25:18 |
|
| 2024-01-26 10:25:41 |
|
| 2024-01-26 10:25:40 |
|
| 2024-01-26 10:25:38 |
|
| 2024-01-26 10:25:34 |
|
| 2024-01-26 10:25:31 |
|
| 2024-01-26 10:25:28 |
|
| 2024-01-26 10:25:25 |
|
| 2024-01-26 10:25:22 |
|
| 2024-01-26 10:25:44 |
|
+---------------------+ |
10 rows in set (0.000 sec) |
-- note that timestamps are not actually sorted correctly and come in some pseudo-random order, not even order of insertion. |
in 10.4 the same SQL results in
ERROR 1253 (42000): COLLATION 'utf8_bin' is not valid for CHARACTER SET 'latin1' |
which is confusing because timestamp columns don't have collation, but at least it hints that there is a problem with the statement. Later versions silently produce wrong results instead.
Attachments
Issue Links
- is caused by
-
-
- Closed
-