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

Change default Unicode collation to uca1400_ai_ci

Details

    Description

      Under terms of this task will change the default collation for Unicode character sets (utf8mb3, utf8mb4, ucs2, utf16, utf32) from xxx_general_ci to uca1400_ai_ci.

      We won't change the server default character set from latin1 to utf8mb4 yet. It will be done separately under terms of MDEV-19123.

      Therefore, we narrowed the original scope of this MDEV, which was about both:

      1. Changing the default collation for Unicode character sets from xxx_general_ci to a modern Unicode collation, with proper support for SMP characters (including emoji)
      2. Changing the server default character set from latin1 to utf8mb4

      to just #1.

      Under terms of this task we won't change the collation of INFORMATION_SCHEMA columns. They'll remain utf8mb3_general_ci. They'll probably also change eventually, but not now. Just one big change at a time!

      See also the section "The original task description from the reporter" below.

      Implementation details

      There were two different implementation ways to change the default collation:

      1. Change the default of the server system variable @@character_set_collations, recently added to 11.2 by MDEV-30164, to:

         "utf8mb3=uca1400_ai_ci,utf8mb4=uca1400_ai_ci,ucs2=uca1400_ai_ci,utf16=uca1400_ai_ci,utf32=uca1400_ai_ci";
        

      2. Change the hard-coded defaults inside the collation library (which resides in the /strings sub-directory of MariaDB sources)

      We'll go #1.

      The hard-coded defaults inside the collation library will remain xxx_general_ci for all Unicode character sets, for the following reasons:

      1. It's easier to do this way, because uca1400_ai_ci needs some initialization at the server startup, while xxx_general_ci works just out of the box. At the very early startup stage (before the collation library initialization), the server needs a functional CHARSET_INFO pointer to handle utf8 data.
      2. Hard-coding uca1400_ai_ci won't be good for all users anyway. Some users might prefer uca1400_as_ci (for accent sensitive comparison), or prefer xxx_general_cl for backward compatibility.

      Changing collation derivation for CAST(expr AS CHAR), CONVERT(expr USING cs), user variables

      Changing the default collation will cause "Illegal mix of collations" errors in two important scenarios:

      • Comparing a column with a non-default collation to a result of CAST/CONVERT
      • Comparing a column with a non-default collation to a string user variable

      This is especially important for queries to INFORMATION_SCHEMA tables, whose columns use utf8mb3_general_ci:

      SET character_set_collations='utf8mb3=uca1400_ai_ci' /*Emulate MDEV-25829 */;
      SET NAMES utf8mb3;
      SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME=CONCAT('a',CAST(1 AS CHAR CHARACTER SET utf8));
      

      ERROR 1267 (HY000): Illegal mix of collations (utf8mb3_general_ci,IMPLICIT) and (utf8mb3_uca1400_ai_ci,IMPLICIT) for operation '='
      

      SET character_set_collations='utf8mb3=uca1400_ai_ci' /*Emulate MDEV-25829 */;
      SET NAMES utf8mb3;
      SET @a='test';
      SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME=@a;
      

      ERROR 1267 (HY000): Illegal mix of collations (utf8mb3_general_ci,IMPLICIT) and (utf8mb3_uca1400_ai_ci,IMPLICIT) for operation '='
      

      To avoid these conflicts let's change collation derivations as follows:

      • for CAST/CONVERT: from IMPLICIT to a new derivation level CAST between SYSCONST and COERCIBLE
      • for string user variables: from IMPLICIT to COERCIBLE

      So after the change, derivations for various expressions look as follows:

          
            DERIVATION_IGNORABLE= 7, // Explicit NULL
          
            DERIVATION_NUMERIC= 6,   // Numbers in string context,
                                     // Numeric user variables
                                     // CAST(numeric_expr AS CHAR)
          
            DERIVATION_COERCIBLE= 5, // Literals, string user variables
          
            DERIVATION_CAST= 4,      // CAST(string_expr AS CHAR),
                                     // CONVERT(string_expr USING cs)
          
            DERIVATION_SYSCONST= 3,  // utf8 metadata functions, e.g. user(), database()
            DERIVATION_IMPLICIT= 2,  // Table columns, SP variables
            DERIVATION_NONE= 1,      // A mix (e.g. CONCAT) of two differrent collations
            DERIVATION_EXPLICIT= 0   // An explicit COLLATE clause
      

      The original task description from the reporter

      Since MySQL 8.0, they changed the default collation to utf8mb4_0900_ai_ci:

      MySQL includes character set support that enables you to store data using a variety of character sets and perform comparisons according to a variety of collations. The default MySQL server character set and collation are utf8mb4 and utf8mb4_0900_ai_ci, but you can specify character sets at the server, database, table, column, and string literal levels.

      https://dev.mysql.com/doc/refman/8.0/en/charset.html

      Frankly, if anything, this change came way too late. I've personally seen hundreds of development hours be wasted on MySQL databases created with wrong (default) collations and only through bug reports ('my characters get mangled') was it found out and fixed. Over. And over. Again. Because every dev that did 'create table whatever (...)' would forget to set the collation and first (back in the day) get a Swedish (why??) collation and then by mistake switch to the (crippled/broken) utf8, thinking it solved it... And then finally got wise and changed to utf8mb4, just to have the whole story start over on the next table someone created.

      I love MySQL, but man this part was a royal mess-up and it cost the world hundreds of millions if not billions of wasted dev hours. I mean this hit everyone running a MySQL server. And now with those emoji I mean I don't even dare to guess at the number of $ wasted.

      So imagine my surprise when I read

      In MariaDB, the default character set is latin1, and the default collation is latin1_swedish_ci

      https://mariadb.com/kb/en/setting-character-sets-and-collations/

      OMG, please say it isn't so!

      Please! Please change this. Like, NOW. This is costing sooooo much dev hours. Soooo much billions of dollars wasted! Devs are stupid ok? They don't understand character sets and collations ok? Never have, never will. This whole Unicode thing has been a personal interest for me for over a decade and I still don't fully grasp it. But one thing I do know for sure and that is: `latin1_swedish_ci` makes NO SENSE whatsoever, to anyone. This is not a good setting. For no one. Not even for Swedish people. Because it fits only 255 characters! I mean really? Do databases still get created for which this default actually makes any sense?

      Please fix it. Please! For love of the world. For peace. To end poverty. Out of good citizenship. Because it's just a few lines in the default server config. For the lulz. For whatever reason, but just do it. Please!

      Attachments

        Issue Links

          Activity

            thomaslauria Thomas Lauria added a comment -

            Hi, due this task we got some problems after updating MariaDB.
            I could reduce the problem to the following sets of commands.
            The question is now: Is that by purpose or is that a bug?

            SET NAMES utf8mb4;
            SET character_set_collations='utf8mb4=uca1400_ai_ci';
             
            CREATE TEMPORARY TABLE `test` (
              `value` text DEFAULT NULL
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
             
            INSERT INTO test VALUES ('');
             
            SELECT @a:=value FROM test;
             
            SELECT COLLATION(@a), COERCIBILITY(CONCAT('a', @a, 'b')), COLLATION(CONCAT('a', @a, 'b'));
            -- results see below
             
            -- now change to utf8mb3
            SET NAMES utf8mb3;
            SET character_set_collations='utf8mb3=uca1400_ai_ci';
            SELECT COLLATION(@a), COERCIBILITY(CONCAT('a', @a, 'b')), COLLATION(CONCAT('a', @a, 'b'));
            -- results see below
            

            Results:

            MariaDB [translate5]> SELECT COLLATION(@a), COERCIBILITY(CONCAT('a', @a, 'b')), COLLATION(CONCAT('a', @a, 'b'));
            +--------------------+------------------------------------+---------------------------------+
            | COLLATION(@a)      | COERCIBILITY(CONCAT('a', @a, 'b')) | COLLATION(CONCAT('a', @a, 'b')) |
            +--------------------+------------------------------------+---------------------------------+
            | utf8mb4_unicode_ci |                                  1 | utf8mb4_bin                     |
            +--------------------+------------------------------------+---------------------------------+
            and
            MariaDB [translate5]> SELECT COLLATION(@a), COERCIBILITY(CONCAT('a', @a, 'b')), COLLATION(CONCAT('a', @a, 'b'));
            +--------------------+------------------------------------+---------------------------------+
            | COLLATION(@a)      | COERCIBILITY(CONCAT('a', @a, 'b')) | COLLATION(CONCAT('a', @a, 'b')) |
            +--------------------+------------------------------------+---------------------------------+
            | utf8mb4_unicode_ci |                                  5 | utf8mb4_unicode_ci              |
            +--------------------+------------------------------------+---------------------------------+
            
            

            My main question is:
            Why is in case of NAMES = utf8mb4 (same charset as in table) the COERCIBILITY of the CONCAT 1 and therefore the COLLATION utf8mb4_bin and when using utf8mb3 (a different charset) COERCIBILITY is 5 and there fore the COLLATION utf8mb4_unicode_ci.

            Is that somehow by purpose or a bug?

            Thanks.

            thomaslauria Thomas Lauria added a comment - Hi, due this task we got some problems after updating MariaDB. I could reduce the problem to the following sets of commands. The question is now: Is that by purpose or is that a bug? SET NAMES utf8mb4; SET character_set_collations= 'utf8mb4=uca1400_ai_ci' ;   CREATE TEMPORARY TABLE `test` ( `value` text DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE =utf8mb4_unicode_ci;   INSERT INTO test VALUES ( '' );   SELECT @a:=value FROM test;   SELECT COLLATION(@a), COERCIBILITY(CONCAT( 'a' , @a, 'b' )), COLLATION(CONCAT( 'a' , @a, 'b' )); -- results see below   -- now change to utf8mb3 SET NAMES utf8mb3; SET character_set_collations= 'utf8mb3=uca1400_ai_ci' ; SELECT COLLATION(@a), COERCIBILITY(CONCAT( 'a' , @a, 'b' )), COLLATION(CONCAT( 'a' , @a, 'b' )); -- results see below Results: MariaDB [translate5]> SELECT COLLATION(@a), COERCIBILITY(CONCAT( 'a' , @a, 'b' )), COLLATION(CONCAT( 'a' , @a, 'b' )); + --------------------+------------------------------------+---------------------------------+ | COLLATION(@a) | COERCIBILITY(CONCAT( 'a' , @a, 'b' )) | COLLATION(CONCAT( 'a' , @a, 'b' )) | + --------------------+------------------------------------+---------------------------------+ | utf8mb4_unicode_ci | 1 | utf8mb4_bin | + --------------------+------------------------------------+---------------------------------+ and MariaDB [translate5]> SELECT COLLATION(@a), COERCIBILITY(CONCAT( 'a' , @a, 'b' )), COLLATION(CONCAT( 'a' , @a, 'b' )); + --------------------+------------------------------------+---------------------------------+ | COLLATION(@a) | COERCIBILITY(CONCAT( 'a' , @a, 'b' )) | COLLATION(CONCAT( 'a' , @a, 'b' )) | + --------------------+------------------------------------+---------------------------------+ | utf8mb4_unicode_ci | 5 | utf8mb4_unicode_ci | + --------------------+------------------------------------+---------------------------------+ My main question is: Why is in case of NAMES = utf8mb4 (same charset as in table) the COERCIBILITY of the CONCAT 1 and therefore the COLLATION utf8mb4_bin and when using utf8mb3 (a different charset) COERCIBILITY is 5 and there fore the COLLATION utf8mb4_unicode_ci. Is that somehow by purpose or a bug? Thanks.
            bar Alexander Barkov added a comment - - edited

            For simplicity I removed the third argument to CONCAT, as it has the same collation characteristics as the first one and therefore does not affect the result collation resolution.

            In case of utf8mb3:

            SET NAMES utf8mb3;
            SET character_set_collations='utf8mb3=uca1400_ai_ci';
            CREATE OR REPLACE TEMPORARY TABLE t1 (
              `value` text DEFAULT NULL
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
            INSERT INTO test VALUES ('');
            SELECT @a:=value FROM t1;
            SELECT
              COLLATION(@a), COERCIBILITY(@a),
              COLLATION('a'), COERCIBILITY('a'),
              COLLATION(CONCAT('a', @a)), COERCIBILITY(CONCAT('a', @a))\G
            

            *************************** 1. row ***************************
                            COLLATION(@a): utf8mb4_unicode_ci
                         COERCIBILITY(@a): 5
                           COLLATION('a'): utf8mb3_uca1400_ai_ci
                        COERCIBILITY('a'): 5
               COLLATION(CONCAT('a', @a)): utf8mb4_unicode_ci
            COERCIBILITY(CONCAT('a', @a)): 5
            

            The result collation resolution is done as follows:

            • There are two argument to CONCAT with different character sets (and collations), with equal collation coercibility levels 5 (COERCIBLE).
            • The utf8mb4 side wins, because utf8mb4 is a superset for utf8mb3.
            • The collation and the coercibility of the CONCAT are copied from the first argument `@a`

            In case of utf8mb4:

            SET NAMES utf8mb4;
            SET character_set_collations='utf8mb4=uca1400_ai_ci';
            CREATE OR REPLACE TEMPORARY TABLE t1 (
              `value` text DEFAULT NULL
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
            INSERT INTO test VALUES ('');
            SELECT @a:=value FROM t1;
            SELECT
              COLLATION(@a), COERCIBILITY(@a),
              COLLATION('a'), COERCIBILITY('a'),
              COLLATION(CONCAT('a', @a)), COERCIBILITY(CONCAT('a', @a))\G
            

            *************************** 1. row ***************************
                            COLLATION(@a): utf8mb4_unicode_ci
                         COERCIBILITY(@a): 5
                           COLLATION('a'): utf8mb4_uca1400_ai_ci
                        COERCIBILITY('a'): 5
               COLLATION(CONCAT('a', @a)): utf8mb4_bin
            COERCIBILITY(CONCAT('a', @a)): 1
            

            The result collation resolution is done as follows:

            • There are two arguments with equal character sets, different collations, equal collation coercibility levels.
            • None of the side wins.
            • The result collation is chosen as a neutral collation utf8mb4_bin, with a special collation coercibility level 1, which means "NONE" (or "CONFLICT"). Note, this conflicting state can be resolved to a non-conflicting state by adding an explicit COLLATE clause after the CONCAT.

            Observations

            The result collation resolution itself seems to work as expected in both cases (with utf8mb3 and utf8mb4).

            However this CONCAT() should rather return an error when used in the SELECT list without an explicit COLLATE clause following the CONCAT (which would resolve the conflict).

            bar Alexander Barkov added a comment - - edited For simplicity I removed the third argument to CONCAT, as it has the same collation characteristics as the first one and therefore does not affect the result collation resolution. In case of utf8mb3: SET NAMES utf8mb3; SET character_set_collations= 'utf8mb3=uca1400_ai_ci' ; CREATE OR REPLACE TEMPORARY TABLE t1 ( `value` text DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE =utf8mb4_unicode_ci; INSERT INTO test VALUES ( '' ); SELECT @a:=value FROM t1; SELECT COLLATION(@a), COERCIBILITY(@a), COLLATION( 'a' ), COERCIBILITY( 'a' ), COLLATION(CONCAT( 'a' , @a)), COERCIBILITY(CONCAT( 'a' , @a))\G *************************** 1. row *************************** COLLATION(@a): utf8mb4_unicode_ci COERCIBILITY(@a): 5 COLLATION('a'): utf8mb3_uca1400_ai_ci COERCIBILITY('a'): 5 COLLATION(CONCAT('a', @a)): utf8mb4_unicode_ci COERCIBILITY(CONCAT('a', @a)): 5 The result collation resolution is done as follows: There are two argument to CONCAT with different character sets (and collations), with equal collation coercibility levels 5 (COERCIBLE). The utf8mb4 side wins, because utf8mb4 is a superset for utf8mb3. The collation and the coercibility of the CONCAT are copied from the first argument `@a` In case of utf8mb4: SET NAMES utf8mb4; SET character_set_collations= 'utf8mb4=uca1400_ai_ci' ; CREATE OR REPLACE TEMPORARY TABLE t1 ( `value` text DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE =utf8mb4_unicode_ci; INSERT INTO test VALUES ( '' ); SELECT @a:=value FROM t1; SELECT COLLATION(@a), COERCIBILITY(@a), COLLATION( 'a' ), COERCIBILITY( 'a' ), COLLATION(CONCAT( 'a' , @a)), COERCIBILITY(CONCAT( 'a' , @a))\G *************************** 1. row *************************** COLLATION(@a): utf8mb4_unicode_ci COERCIBILITY(@a): 5 COLLATION('a'): utf8mb4_uca1400_ai_ci COERCIBILITY('a'): 5 COLLATION(CONCAT('a', @a)): utf8mb4_bin COERCIBILITY(CONCAT('a', @a)): 1 The result collation resolution is done as follows: There are two arguments with equal character sets, different collations, equal collation coercibility levels. None of the side wins. The result collation is chosen as a neutral collation utf8mb4_bin, with a special collation coercibility level 1, which means "NONE" (or "CONFLICT"). Note, this conflicting state can be resolved to a non-conflicting state by adding an explicit COLLATE clause after the CONCAT. Observations The result collation resolution itself seems to work as expected in both cases (with utf8mb3 and utf8mb4). However this CONCAT() should rather return an error when used in the SELECT list without an explicit COLLATE clause following the CONCAT (which would resolve the conflict).
            bar Alexander Barkov added a comment - - edited

            Just checked with MySQL-8.3.0. It also allows to use an expression with the coercibility level of 1 (NONE) in the SELECT list:

            drop table if exists t1, t2;
            create  table t1 (
              a varchar(10) character set utf8mb4 collate utf8mb4_general_ci,
              b varchar(10) character set utf8mb4 collate utf8mb4_unicode_ci
            );
            create table t2 as select concat(a,b) from t1;
            show create table t2;
            

            +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
            | Table | Create Table                                                                                                                                                              |
            +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
            | t2    | CREATE TABLE `t2` (
              `concat(a,b)` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
            +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
            

            Which I'd say is rather a bug. But this behavior has been this way for many years in MySQL and then MariaDB.

            Not sure if we should change this behavior in MariaDB at this point (i.e. raise an error instead of returning an utf8mb4_bin result).

            bar Alexander Barkov added a comment - - edited Just checked with MySQL-8.3.0. It also allows to use an expression with the coercibility level of 1 (NONE) in the SELECT list: drop table if exists t1, t2; create table t1 ( a varchar (10) character set utf8mb4 collate utf8mb4_general_ci, b varchar (10) character set utf8mb4 collate utf8mb4_unicode_ci ); create table t2 as select concat(a,b) from t1; show create table t2; +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | t2 | CREATE TABLE `t2` ( `concat(a,b)` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci | +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ Which I'd say is rather a bug. But this behavior has been this way for many years in MySQL and then MariaDB. Not sure if we should change this behavior in MariaDB at this point (i.e. raise an error instead of returning an utf8mb4_bin result).
            bar Alexander Barkov added a comment - - edited

            Possible workarounds to avoid having such a conflicting state in a SELECT list expression:

            • Use an explicit COLLATE with one of the argument:

              SELECT CONCAT('a' COLLATE utf8mb4_unicode_ci, @a);
              SELECT CONCAT('a', @a  COLLATE utf8mb4_unicode_ci);
              

            • Use an explicit COLLATE following the CONCAT, to resolve the conflicting state:

              SELECT CONCAT('a', @a)  COLLATE utf8mb4_unicode_ci;
              

            bar Alexander Barkov added a comment - - edited Possible workarounds to avoid having such a conflicting state in a SELECT list expression: Use an explicit COLLATE with one of the argument: SELECT CONCAT( 'a' COLLATE utf8mb4_unicode_ci, @a); SELECT CONCAT( 'a' , @a COLLATE utf8mb4_unicode_ci); Use an explicit COLLATE following the CONCAT, to resolve the conflicting state: SELECT CONCAT( 'a' , @a) COLLATE utf8mb4_unicode_ci;
            ycp Yuchen Pei added a comment - - edited

            greenman
            It looks like the kb documentation on COERCIBILITY needs an update since with this MDEV a new derivation DERIVATION_CAST is added between DERIVATION_SYSCONST and DERIVATION_COERCIBLE, and COERCIBLE applies to both literal strings and user variables now, and possible other changes.

            https://mariadb.com/kb/en/coercibility/

            modified   sql/sql_type.h
            @@ -3019,13 +3019,46 @@ static inline my_repertoire_t &operator|=(my_repertoire_t &a,
             
             enum Derivation
             {
            -  DERIVATION_IGNORABLE= 6,
            -  DERIVATION_NUMERIC= 5,
            -  DERIVATION_COERCIBLE= 4,
            +  DERIVATION_IGNORABLE= 7, // Explicit NULL
            +
            +  /*
            +    Explicit or implicit conversion from numeric/temporal data to string:
            +    - Numbers/temporals in string context
            +    - Numeric user variables
            +    - CAST(numeric_or_temporal_expr AS CHAR)
            +  */
            +  DERIVATION_NUMERIC= 6,
            +
            +  /*
            +    - String literals
            +    - String user variables
            +  */
            +  DERIVATION_COERCIBLE= 5,
            +
            +  /*
            +    String cast and conversion functions:
            +    - BINARY(expr)
            +    - CAST(string_expr AS CHAR)
            +    - CONVERT(expr USING cs)
            +  */
            +  DERIVATION_CAST= 4,
            +
            +  /*
            +    utf8 metadata functions:
            +    - DATABASE()
            +    - CURRENT_ROLE()
            +    - USER()
            +  */
               DERIVATION_SYSCONST= 3,
            +
            +  /*
            +    - Table columns
            +    - SP variables
            +    - BINARY(expr) and CAST(expr AS BINARY)
            +  */
               DERIVATION_IMPLICIT= 2,
            -  DERIVATION_NONE= 1,
            -  DERIVATION_EXPLICIT= 0
            +  DERIVATION_NONE= 1,      // A mix (e.g. CONCAT) of two differrent collations
            +  DERIVATION_EXPLICIT= 0   // An explicit COLLATE clause
             };
            

            UPDATE: also MDEV-35041 added another derivation

            ycp Yuchen Pei added a comment - - edited greenman It looks like the kb documentation on COERCIBILITY needs an update since with this MDEV a new derivation DERIVATION_CAST is added between DERIVATION_SYSCONST and DERIVATION_COERCIBLE, and COERCIBLE applies to both literal strings and user variables now, and possible other changes. https://mariadb.com/kb/en/coercibility/ modified sql/sql_type.h @@ -3019,13 +3019,46 @@ static inline my_repertoire_t &operator|=(my_repertoire_t &a, enum Derivation { - DERIVATION_IGNORABLE= 6, - DERIVATION_NUMERIC= 5, - DERIVATION_COERCIBLE= 4, + DERIVATION_IGNORABLE= 7, // Explicit NULL + + /* + Explicit or implicit conversion from numeric/temporal data to string: + - Numbers/temporals in string context + - Numeric user variables + - CAST(numeric_or_temporal_expr AS CHAR) + */ + DERIVATION_NUMERIC= 6, + + /* + - String literals + - String user variables + */ + DERIVATION_COERCIBLE= 5, + + /* + String cast and conversion functions: + - BINARY(expr) + - CAST(string_expr AS CHAR) + - CONVERT(expr USING cs) + */ + DERIVATION_CAST= 4, + + /* + utf8 metadata functions: + - DATABASE() + - CURRENT_ROLE() + - USER() + */ DERIVATION_SYSCONST= 3, + + /* + - Table columns + - SP variables + - BINARY(expr) and CAST(expr AS BINARY) + */ DERIVATION_IMPLICIT= 2, - DERIVATION_NONE= 1, - DERIVATION_EXPLICIT= 0 + DERIVATION_NONE= 1, // A mix (e.g. CONCAT) of two differrent collations + DERIVATION_EXPLICIT= 0 // An explicit COLLATE clause }; UPDATE: also MDEV-35041 added another derivation

            People

              bar Alexander Barkov
              StijnDeWitt Stijn de Witt
              Votes:
              1 Vote for this issue
              Watchers:
              10 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.