8.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 these rules would take care of most situations:

• An explicit COLLATE clause has precedence 4.
• A concatenation of two strings with different collations has precedence 3.
• A column's collation has precedence 2.
• A literal's collation has precedence 1.

Those rules resolve ambiguities thus:

• Use the collation with the highest precedence.
• If both sides have the same precedence, then it's an error if the collations aren't the same.

Examples:

User Comments

Add your own comment.