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: | |
| 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 2019 13:27]
Oleg Komarov
[11 Apr 2019 13:44]
MySQL Verification Team
Thank you for the bug report.
[12 Apr 2019 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 2019 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 2019 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.
[9 Nov 2022 15:48]
Sam Bryan
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.
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.084 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.