Bug #94988 Illegal mix of collations for derived tables
Submitted: 11 Apr 13:27 Modified: 11 Apr 13:44
Reporter: Oleg Komarov Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Charsets Severity:S3 (Non-critical)
Version:5.6/5.7/8.0 OS:Linux
Assigned to: CPU Architecture:Any
Tags: derived table, Illegal mix of collations

[11 Apr 13:27] Oleg Komarov
Description:
When a column collation differs from the collation_connection, a query that involves a derived table throws an error:

ERROR 1267: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8mb4_general_ci,COERCIBLE) for operation '='

This contradicts the expectations, as mentioned in documentation:

"For comparisons of strings with column values, collation_connection does not matter because columns have their own collation, which has a higher collation precedence" from https://dev.mysql.com/doc/refman/5.7/en/charset-connection.html

Expected behavior: no error.

How to repeat:
create table t1 (
  a varchar(255) primary key,
  b int
) default charset=latin1;
set collation_connection=utf8mb4_general_ci;
select * from t1 join (select 'test' as a, 1 as b) as s using (a);
drop table t1;
[11 Apr 13:44] Miguel Solorzano
Thank you for the bug report.
[12 Apr 6:21] Xing Zhang
Posted by developer:
 
"For comparisons of strings with column values, collation_connection does not matter...", but this is a comparison between columns (t1.a and s.a), the collation of s.a is utf8mb4_general_ci because of the collation_connection. So I think there is no problem to raise the "illegal mix of collations" error.
[12 Apr 10:21] Mattias Jonsson
From a user perspective saying that 'there is no problem to raise the "illegal mix of collations" error' makes no sense. It means that whenever you want to use such statement, it seems like you first needs to check the collation of the columns in the used table and then set the collation_connection accordingly to make the statement work.
[12 Apr 12:38] Roy Lyseng
There is a fairly simple workaround to the problem: Tell that the string literal should be interpreted to be in the same character set as the table's column.

  select * from t1 join (select _latin1'test' as a, 1 as b) as s using (a);

It creates a dependency, though, so later changing the character set of the column means that the query may have to be changed as well.