Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.6.16, 10.11.6, 10.5, 10.6, 10.11, 11.0(EOL), 11.1(EOL), 11.2(EOL), 11.3(EOL)
-
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
-
Hello serg,
The patch at
https://github.com/MariaDB/server/commit/8fc362f73f1bd14d38a75969b9e546b6d857622a
is almost fine.
I suggest instead of:
> +--echo # End of 10.6 tests> diff --git a/sql/item_strfunc.cc b/sql/item_strfunc.cc> index ae855525060d6..cd1020164aba9 100644> --- a/sql/item_strfunc.cc> +++ b/sql/item_strfunc.cc> }> collation.set(m_set_collation, DERIVATION_EXPLICIT,> args[0]->collation.repertoire);> - max_length= args[0]->max_length;> + max_length= args[0]->max_length * collation.collation->mbmaxlen> + / args[0]->collation.collation->mbmaxlen;> }to use:
ulonglong char_length= (ulonglong) args[0]->max_char_length() * collation.collation->mbmaxlen;fix_char_length_ulonglong(char_length);