Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-14127

ORDER BY COLLATE 'utf8mb4_german2_ci' ASC is inconsistent with DESC

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Not a Bug
    • 5.5.57
    • N/A
    • Character Sets
    • None

    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)
      

      Attachments

        Activity

          People

            bar Alexander Barkov
            msakoda Mitur Sakoda
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.