When some operation arguments have different collations, MariaDB uses so called collation aggregation. For example:

SELECT CONCAT(a,b) FROM t1;

SELECT * FROM t1 WHERE a=b;

Suppose that collations of "a" and "b" are different. Collation aggregation allows to determine:

• which collation to use for an operation result data type, like in CONCAT(a,b)
• which collation to use to perform a comparison, like in WHERE a=b

Collation aggregation is based on the collation strength of the arguments. The collation strength is called "collation derivation" (in early SQL standard version it was called "collation coercibility").

For more information about collation strength, see here:
https://mariadb.com/kb/en/coercibility/

Some details on how collation aggregation works can be found in MySQL user manual:
https://dev.mysql.com/doc/refman/8.0/en/charset-collation-coercibility.html

Collation aggregation is used at least in:

• UNION
• CASE (for WHEN and THEN elements) and its abberiations and variations: IF(), COALESCE(), IFNULL(), NULLIF()
• Comparison operators <,<=,<>,=,<=>,=>,>
• IN
• BETWEEN
• LIKE
• RLIKE
• MATCH ... AGAINST
• STRCMP()
• REGEX_INSTR()
• FIELD()
• FIND_IN_SET()
• LOCATE()
• JSON_ARRAY()
• JSON_OBJECTAGG()
• CONCAT()
• CONCAT_WS()
• REPLACE()
• REGEXP_REPLACE()
• REGEXP_SUBSTR()
• INSERT()
• SUBSTR_INDEX()
• ELT()
• MAKE_SET()
• PAD()
• EXPORT_SET()
• GROUP_CONCAT()
• A number of XML functions inside XPath implementation used in EXTRACT_VALUE() and UpdateXML()

For MariaDB compatibility, we should implement collation aggregation in MCS.