MySQL Bugs: #94988: Illegal mix of collations for derived tables

Bug #94988	Illegal mix of collations for derived tables
Submitted:	11 Apr 2019 13:27	Modified:	11 Apr 2019 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

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;

Thank you for the bug report.

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.

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.

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.

I just ran into this. In my case I can't always be sure what the original table collation was (the queries run against a variety of dev, CI and prod environments, with different MySQL versions) which makes working around this bug harder.