(mysql.info.gz) Charset-collate-tricky

Info Catalog

(mysql.info.gz) Charset-binary-op

(mysql.info.gz) Charset-defaults

(mysql.info.gz) Charset-collation-charset

 
 10.3.11 Some Special Cases Where the Collation Determination Is Tricky
 ----------------------------------------------------------------------
 
 In the great majority of queries, 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 will be "the column
 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. The essence is: Because `x' and `'Y'' both have
 collations, whose collation takes precedence? It's complex, but the
 following rules take care of most situations:
 
    * An explicit `COLLATE' clause has a coercibility of 0.  (Not
      coercible at all.)
 
    * A concatenation of two strings with different collations has a
      coercibility of 1.
 
    * A column's collation has a coercibility of 2.
 
    * A literal's collation has a coercibility of 3.
 
 
 Those rules resolve ambiguities thus:
 
    * 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('A');
              -> 3
 
  Information functions.
 

Info Catalog

(mysql.info.gz) Charset-binary-op

(mysql.info.gz) Charset-defaults

(mysql.info.gz) Charset-collation-charset