Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
5.5.57
-
None
-
mysql Ver 14.14 Distrib 5.7.9, for osx10.9 (x86_64) using EditLine wrapper
Connection id: 2358
Current database: LIBRARY0
Current user: root@192.168.255.100
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.5.5-10.2.9-MariaDB-10.2.9+maria~trusty-log mariadb.org binary distribution
Protocol version: 10
Connection: tenant-mariadb.service.imanagecloud.com via TCP/IP
Server characterset: latin1
Db characterset: utf8mb4
Client characterset: utf8
Conn. characterset: utf8
mysql Ver 14.14 Distrib 5.7.9, for osx10.9 (x86_64) using EditLine wrapper Connection id: 2358 Current database: LIBRARY0 Current user: root@192.168.255.100 SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.5.5-10.2.9-MariaDB-10.2.9+maria~trusty-log mariadb.org binary distribution Protocol version: 10 Connection: tenant-mariadb.service.imanagecloud.com via TCP/IP Server characterset: latin1 Db characterset: utf8mb4 Client characterset: utf8 Conn. characterset: utf8
Description
Sorting with COLLATE 'utf8mb4_german2_ci' gives inconsistent results with ASC vs. DESC, they are not the reverse of each other.
The details should be clear from the example below.
mysql> SELECT C1, C2, C3 FROM CHARSET_IT_1508963224106 WHERE C2 = 'testGermanSortDIN5007v2' ORDER BY C1 COLLATE 'utf8mb4_german2_ci' ASC;
|
+----------+-------------------------+------+
|
| C1 | C2 | C3 |
|
+----------+-------------------------+------+
|
| Göbel | testGermanSortDIN5007v2 | 0 |
|
| Goethe | testGermanSortDIN5007v2 | 1 |
|
| Göthe | testGermanSortDIN5007v2 | 2 |
|
| Götz | testGermanSortDIN5007v2 | 3 |
|
| Goldmann | testGermanSortDIN5007v2 | 4 |
|
+----------+-------------------------+------+
|
5 rows in set (0.00 sec)
|
|
mysql> SELECT C1, C2, C3 FROM CHARSET_IT_1508963224106 WHERE C2 = 'testGermanSortDIN5007v2' ORDER BY C1 COLLATE 'utf8mb4_german2_ci' DESC;
|
+----------+-------------------------+------+
|
| C1 | C2 | C3 |
|
+----------+-------------------------+------+
|
| Goldmann | testGermanSortDIN5007v2 | 4 |
|
| Götz | testGermanSortDIN5007v2 | 3 |
|
| Goethe | testGermanSortDIN5007v2 | 1 |
|
| Göthe | testGermanSortDIN5007v2 | 2 |
|
| Göbel | testGermanSortDIN5007v2 | 0 |
|
+----------+-------------------------+------+
|
5 rows in set (0.00 sec)
|
|
mysql> show create table CHARSET_IT_1508963224106;
|
+--------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+--------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| CHARSET_IT_1508963224106 | CREATE TABLE `CHARSET_IT_1508963224106` (
|
`C1` varchar(256) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
|
`C2` varchar(256) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
|
`C3` int(11) DEFAULT NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
|
+--------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
1 row in set (0.00 sec)
|