(mysql.info) charset-collate-tricky

Info Catalog

(mysql.info) charset-binary-op

(mysql.info) charset-collations

(mysql.info) charset-collation-charset

 
 10.5.4 Some Special Cases Where the Collation Determination Is Tricky
 ---------------------------------------------------------------------
 
 In the great majority of statements, it is obvious what collation MySQL
 uses to resolve a comparison operation. For example, in the following
 cases, it should be clear that the collation is the collation of column
 `x':
 
      SELECT x FROM T ORDER BY x;
      SELECT x FROM T WHERE x = x;
      SELECT DISTINCT x FROM T;
 
 However, when multiple operands are involved, there can be ambiguity.
 For example:
 
      SELECT x FROM T WHERE x = 'Y';
 
 Should this query use the collation of the column `x', or of the string
 literal `'Y''?
 
 Standard SQL resolves such questions using what used to be called
 `coercibility' rules. Basically, this means: Both `x' and `'Y'' have
 collations, so which collation takes precedence? This can be difficult
 to resolve, but the following rules cover most situations:
 
    * An explicit `COLLATE' clause has a coercibility of 0. (Not
      coercible at all.)
 
    * The concatenation of two strings with different collations has a
      coercibility of 1.
 
    * The collation of a column or a stored routine parameter or local
      variable has a coercibility of 2.
 
    * A `system constant' (the string returned by functions such as
      `USER()' or `VERSION()') has a coercibility of 3.
 
    * A literal's collation has a coercibility of 4.
 
    * `NULL' or an expression that is derived from `NULL' has a
      coercibility of 5.
 
 The preceding coercibility values are current as of MySQL 5.0.3.  See
 the note later in this section for additional version-related
 information.
 
 Those rules resolve ambiguities in the following manner:
 
    * Use the collation with the lowest coercibility value.
 
    * If both sides have the same coercibility, then it is an error if
      the collations aren't the same.
 
 Examples:
 
 `column1 = 'A''                      Use collation of `column1'
 `column1 = 'A' COLLATE x'            Use collation of `'A''
 `column1 COLLATE x = 'A' COLLATE y'  Error
 
 The `COERCIBILITY()' function can be used to determine the coercibility
 of a string expression:
 
      mysql> SELECT COERCIBILITY('A' COLLATE latin1_swedish_ci);
              -> 0
      mysql> SELECT COERCIBILITY(VERSION());
              -> 3
      mysql> SELECT COERCIBILITY('A');
              -> 4
 
 See  information-functions.
 
 In MySQL 5.0 prior to release 5.0.3, there is no system constant or
 ignorable coercibility. Functions such as `USER()' have a coercibility
 of 2 rather than 3, and literals have a coercibility of 3 rather than 4.
 

Info Catalog

(mysql.info) charset-binary-op

(mysql.info) charset-collations

(mysql.info) charset-collation-charset