This script:

SET NAMES utf8;

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci);

INSERT INTO t1 VALUES ('123');

SELECT * FROM t1 WHERE a=CAST(123 AS CHAR);

returns an error

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

The error is correct:

• According to the standard, table columns and CAST() have the same collation strength (derivation) of IMPLICIT.
• The CAST() without a character set specification in MariaDB uses @@character_set_connection as a character set and @@collation_connection as a collation (which is utf8_general_ci in this example)

Therefore, the comparison operator gets two arguments with different collations with the same derivation. Hence, the conflict.

It would be nice to have a syntax to resolve conflicts in such cases.

An obvious solution would be to use the COLLATE clause:

SELECT * FROM t1 WHERE a=CAST(123 AS CHAR) COLLATE utf8_unicode_ci;

But if at some point we decide to issue and ALTER TABLE changing the character set or the collation of the column t1.a, similar query will very likely return a wrong result. The intent is to use the collation of the column!

Possible solutions:
1. Add a syntax to change the collation derivation of an expression, for example:

SELECT  * FROM t1 WHERE a = CAST('123' AS CHAR) COLLATE COERCIBLE;

So the above COLLATE clause does not change the collation of the argument, but changes the collation derivation (making it weaker in this example).

This is the full list of the currently supported collation derivation levels:

• NUMERIC (MariaDB extension)
• IGNORABLE (MariaDB extension)
• COERCIBLE (MariaDB extension, was Standard in the older SQL versions)
• IMPLICIT (Standard)
• SYSCONST (MariaDB extension)
• EXPLICIT (Standard)
• NONE (Standard)

2. Extend COLLATE clause use any kind of constant string expressions rather than just explicitly typed literal collation names:

SELECT * FROM t1 WHERE a = CAST('123' AS CHAR) COLLATE COLLATION(a);