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

Add UCA-14.0.0 collations

    XMLWordPrintable

Details

    Description

      In order to make utf8mb4 the default character set (see MDEV-19123), we need a reasonable default collation for utf8mb4.

      utf8mb4_general_ci is not good — it compares all supplementary characters (with code points in the range U10000 to U10FFFF) as equal to each other.
      In 2021 it's not acceptable — there are around 16 thousand supplementary charcters that have explicit weights in the DUCET table.

      Changing the default to utf8mb4_unicode_ci (Unicode-4.0.0 based) or utf8mb4_unicode_520_ci (Unicode-5.2.0 based) is not reasonable either — these standards are more than 10 years old.

      Let's add a number of Unicode collation algoritm (UCA) collation based on Unicode-14.0.0 (released in September 2021), so we can make it later the default for utf8mb4 (under terms of MDEV-19123).

      Under terms of this task we'll add the "root" collation, as well as all language specific collations that exist for the old Unicode version.

      Character sets

      New collations will be added for these Unicode character sets

      • utf8mb3
      • utf8mb4
      • ucs2
      • utf16
      • utf32

      Tailoring (language specific collations)

      We'll add collations for all 22 tailorings that exists for the old UCA-4.0.0 collations, except Thai.

      • icelandic
      • latvian
      • romanian
      • slovenian
      • polish
      • estonian
      • spanish
      • swedish
      • turkish
      • czech
      • danish
      • lithuanian
      • slovak
      • spanish2
      • roman
      • persian
      • esperanto
      • hungarian
      • sinhala
      • german2
      • croatian
      • vietnamese

      Note, with built-in contractions that will be supported in UCA-14.0.0 collations, a separate Thai collation won't be needed. Thai will work with the default root collation.

      Built-in contractions

      New collations will include built-in contractions from http://www.unicode.org/Public/UCA/14.0.0/allkeys.txt

      There are 939 built-in contractions in UCA-14.0.0.

      To mention a few of them:

      0E40 0E01 ; [.3339.0020.0002][.3373.0020.0002] # <THAI CHARACTER SARA E, THAI CHARACTER KO KAI>
      0E41 0E01 ; [.3339.0020.0002][.3374.0020.0002] # <THAI CHARACTER SARA AE, THAI CHARACTER KO KAI>
      0E42 0E01 ; [.3339.0020.0002][.3375.0020.0002] # <THAI CHARACTER SARA O, THAI CHARACTER KO KAI>
      0E43 0E01 ; [.3339.0020.0002][.3376.0020.0002] # <THAI CHARACTER SARA AI MAIMUAN, THAI CHARACTER KO KAI>
      0E44 0E01 ; [.3339.0020.0002][.3377.0020.0002] # <THAI CHARACTER SARA AI MAIMALAI, THAI CHARACTER KO KAI>

      Note the old collations based on UCA-4.0.0 and UCA-5.2.0 did not support built-in contractions.
      This will be a step towards better Unicode Collation Algorithm compliance. With built-in contractions, some languages (e.g. Thai) won't need specific collations and will just work with the default "root" collation.

      Performance

      The patch adding UCA-14.0.0 collations will be pushed together with these patches improving the performance:

      • MDEV-27266 Improve UCA collation performance for utf8mb3 and utf8mb4
      • MDEV-27265 Improve contraction performance in UCA collations

      Customization flags

      Under terms of this task we'll add support for the following flags

      • pad/nopad
      • accent sensitivity
      • case sensititivy

      Any arbitrary combination of these flags will be possible. That means, eight combinations for each tailoring (each language) will be added. For example, for Czech language, the following collations will be available:

      +---------------------------+
      | collation_name            |
      +---------------------------+
      | uca1400_czech_ai_ci       |
      | uca1400_czech_ai_cs       |
      | uca1400_czech_as_ci       |
      | uca1400_czech_as_cs       |
      | uca1400_czech_nopad_ai_ci |
      | uca1400_czech_nopad_ai_cs |
      | uca1400_czech_nopad_as_ci |
      | uca1400_czech_nopad_as_cs |
      +---------------------------+

      Naming convention

      Naming convention for the new collations is described in MDEV-27210.

      Short collation name

      The character set name in the collation name will be optional in all syntactic constructs:

      CREATE TABLE t1 (
        a CHAR(10) CHARACTER SET utf8mb4 COLLATE uca1400_as_ci,
        b CHAR(10) CHARACTER SET utf16 COLLATE uca1400_as_ci
      );

      Notice, it will be enough to specify just uca1400_as_ci without having to type the full names:

      • utf8mb4_uca1400_as_ci for the column "a", or
      • utf16_uca1400_as_ci for the column "b".

      The full name will be detected automatically according to the character set effective in the given context.

      SHOW CREATE statements

      SHOW CREATE statements will display long collations names.

      For example, SHOW CREATE for the table created using the CREATE TABLE statement will produce the following output:

      SHOW CREATE TABLE t1;

      +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Table | Create Table                                                                                                                                                                                                            |
      +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | t1    | CREATE TABLE `t1` (
        `a` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_as_ci DEFAULT NULL,
        `b` char(10) CHARACTER SET utf16 COLLATE utf16_uca1400_as_ci DEFAULT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
      +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

      Notice, the COLLATE clause will contain the full collation name, including the character set prefix.
      But this will possibly change in later versions to display short collation names.

      INFORMATION_SCHEMA.COLLATIONS changes

      The following columns in INFORMATION_SCHEMA.COLLATIONS will be changed to be NULL-able:

      • CHARACTER_SET_NAME
      • ID
      • IS_DEFAULT

      All new UCA1400 collations will be displayed as follows:

      • The COLLATION_NAME column will display the short name, without character set prefix.
      • The three NULL-able column mentioned above will display NULL

      For old collations nothing will change.

      For example, the output from this query:

      SELECT * FROM INFORMATION_SCHEMA.COLLATIONS
      WHERE COLLATION_NAME LIKE '%czech%';

      will look like this:

      +---------------------------+--------------------+------+------------+-------------+---------+
      | COLLATION_NAME            | CHARACTER_SET_NAME | ID   | IS_DEFAULT | IS_COMPILED | SORTLEN |
      +---------------------------+--------------------+------+------------+-------------+---------+
      | latin2_czech_cs           | latin2             |    2 |            | Yes         |       4 |
      | cp1250_czech_cs           | cp1250             |   34 |            | Yes         |       2 |
      | utf8mb3_czech_ci          | utf8mb3            |  202 |            | Yes         |       8 |
      | ucs2_czech_ci             | ucs2               |  138 |            | Yes         |       8 |
      | utf8mb4_czech_ci          | utf8mb4            |  234 |            | Yes         |       8 |
      | uca1400_czech_ai_ci       | NULL               | NULL | NULL       | Yes         |       8 |
      | uca1400_czech_ai_cs       | NULL               | NULL | NULL       | Yes         |       8 |
      | uca1400_czech_as_ci       | NULL               | NULL | NULL       | Yes         |       8 |
      | uca1400_czech_as_cs       | NULL               | NULL | NULL       | Yes         |       8 |
      | uca1400_czech_nopad_ai_ci | NULL               | NULL | NULL       | Yes         |       8 |
      | uca1400_czech_nopad_ai_cs | NULL               | NULL | NULL       | Yes         |       8 |
      | uca1400_czech_nopad_as_ci | NULL               | NULL | NULL       | Yes         |       8 |
      | uca1400_czech_nopad_as_cs | NULL               | NULL | NULL       | Yes         |       8 |
      | utf16_czech_ci            | utf16              |  111 |            | Yes         |       8 |
      | utf32_czech_ci            | utf32              |  170 |            | Yes         |       8 |
      +---------------------------+--------------------+------+------------+-------------+---------+

      The idea is that short collation names will be applicable to multiple character sets.
      We'll eventually remove columns CHARACTER_SET_NAME, ID, IS_DEFAULT from the table COLLATIONS. We'll keep them for now only for easier upgrades.

      INFORMATION_SCHEMA.COLLATION_CHARACTER_SET_APPLICABILITY changes

      In addition to columns COLLATION_NAME and CHARACTER_SET_NAME currently existing in the table INFORMATION_SCHEMA.COLLATION_CHARACTER_SET_APPLICABILITY, three new columns will be added:

      • FULL_COLLATION_NAME
      • ID
      • IS_DEFAULT

      So the new structure for INFORMATION_SCHEMA.COLLATION_CHARACTER_SET_APPLICABILITY will look like this:

      CREATE TEMPORARY TABLE `COLLATION_CHARACTER_SET_APPLICABILITY` (
        `COLLATION_NAME` varchar(64) NOT NULL,
        `CHARACTER_SET_NAME` varchar(32) NOT NULL,
        `FULL_COLLATION_NAME` varchar(64) NOT NULL,
        `ID` bigint(11) NOT NULL,
        `IS_DEFAULT` varchar(3) NOT NULL
      ) ENGINE=MEMORY DEFAULT CHARSET=utf8mb3;

      The column COLLATION_NAME will display:

      • short names, without the character set name prefix, for new UCA1400 collations.
      • long names for all old collations

      So for example, the output from this query:

      SELECT * FROM INFORMATION_SCHEMA.COLLATION_CHARACTER_SET_APPLICABILITY
      WHERE COLLATION_NAME LIKE '%czech%cs';

      will look about like this:

      +---------------------------+--------------------+-----------------------------------+------+------------+
      | COLLATION_NAME            | CHARACTER_SET_NAME | FULL_COLLATION_NAME               | ID   | IS_DEFAULT |
      +---------------------------+--------------------+-----------------------------------+------+------------+
      | latin2_czech_cs           | latin2             | latin2_czech_cs                   |    2 |            |
      | cp1250_czech_cs           | cp1250             | cp1250_czech_cs                   |   34 |            |
      | uca1400_czech_ai_cs       | utf8mb3            | utf8mb3_uca1400_czech_ai_cs       | 2129 |            |
      | uca1400_czech_as_cs       | utf8mb3            | utf8mb3_uca1400_czech_as_cs       | 2131 |            |
      | uca1400_czech_nopad_ai_cs | utf8mb3            | utf8mb3_uca1400_czech_nopad_ai_cs | 2133 |            |
      | uca1400_czech_nopad_as_cs | utf8mb3            | utf8mb3_uca1400_czech_nopad_as_cs | 2135 |            |
      | uca1400_czech_ai_cs       | ucs2               | ucs2_uca1400_czech_ai_cs          | 2641 |            |
      | uca1400_czech_as_cs       | ucs2               | ucs2_uca1400_czech_as_cs          | 2643 |            |
      | uca1400_czech_nopad_ai_cs | ucs2               | ucs2_uca1400_czech_nopad_ai_cs    | 2645 |            |
      | uca1400_czech_nopad_as_cs | ucs2               | ucs2_uca1400_czech_nopad_as_cs    | 2647 |            |
      | uca1400_czech_ai_cs       | utf8mb4            | utf8mb4_uca1400_czech_ai_cs       | 2385 |            |
      | uca1400_czech_as_cs       | utf8mb4            | utf8mb4_uca1400_czech_as_cs       | 2387 |            |
      | uca1400_czech_nopad_ai_cs | utf8mb4            | utf8mb4_uca1400_czech_nopad_ai_cs | 2389 |            |
      | uca1400_czech_nopad_as_cs | utf8mb4            | utf8mb4_uca1400_czech_nopad_as_cs | 2391 |            |
      | uca1400_czech_ai_cs       | utf16              | utf16_uca1400_czech_ai_cs         | 2897 |            |
      | uca1400_czech_as_cs       | utf16              | utf16_uca1400_czech_as_cs         | 2899 |            |
      | uca1400_czech_nopad_ai_cs | utf16              | utf16_uca1400_czech_nopad_ai_cs   | 2901 |            |
      | uca1400_czech_nopad_as_cs | utf16              | utf16_uca1400_czech_nopad_as_cs   | 2903 |            |
      | uca1400_czech_ai_cs       | utf32              | utf32_uca1400_czech_ai_cs         | 3153 |            |
      | uca1400_czech_as_cs       | utf32              | utf32_uca1400_czech_as_cs         | 3155 |            |
      | uca1400_czech_nopad_ai_cs | utf32              | utf32_uca1400_czech_nopad_ai_cs   | 3157 |            |
      | uca1400_czech_nopad_as_cs | utf32              | utf32_uca1400_czech_nopad_as_cs   | 3159 |            |
      +---------------------------+--------------------+-----------------------------------+------+------------+

      New ID ranges

      We'll use the range 2048-4095 for new UCA-14.0.0 collation.

      The ID will encode:

      • character set
      • tailoring
      • flags

      into 12 bits as follows:

      1ccc tttt tPST

      where

      - ccc   (3 bits) - the character set ID
                         (0 - utf8mb3, 1 - utf8mb4, 2 - ucs2, 3 - utf16, 4 - utf32)
       
      - ttttt (5 bits) - the tailoring ID
                         (0 - default, 1 - icelandic, 2 - latvian, etc)
       
      - P     (1 bit)  - the PAD flag (0 - PAD, 1 - NOPAD)
       
      - S     (1 bit)  - Secondary level
                         0 - disabled (accent insensitive)
                         1 - enabled  (accent sensitive)
       
      - T     (1 bit)  - Tertiary level
                         0 -  disabled (case insensitive)
                         1 -  enabled  (case sensitive)

      Attachments

        Issue Links

          Activity

            People

              bar Alexander Barkov
              bar Alexander Barkov
              Votes:
              3 Vote for this issue
              Watchers:
              13 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.