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 for MySQL
5.1.
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:
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 Section 12.10.3, “Information Functions”.
