Bug #109507 Illegal mix of collations when selecting from a view with a calculated column
Submitted: 1 Jan 2023 11:48 Modified: 2 Jan 2023 4:18
Reporter: Damien Regad Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Charsets Severity:S3 (Non-critical)
Version:8.0.31 OS:MacOS
Assigned to: CPU Architecture:Any

[1 Jan 2023 11:48] Damien Regad
Description:
Trying to select rows from a view with a where clause on a calculated column using a string constant, I'm getting Illegal mix of collations error, with a query like this:

select * from v1 where b = 'test1';

Database default collation is utf8mb4_unicode_ci, the calculated column's (b) is utf8mb4_unicode_ci also, per value of collation_connection at the time the view was created.

I would expect the collation of the where clause's string constant to be coerced to the view's calculated columns'.

How to repeat:
mysql> show variables like '%collat%';
+-------------------------------+--------------------+
| Variable_name                 | Value              |
+-------------------------------+--------------------+
| collation_connection          | utf8mb4_0900_ai_ci |
| collation_database            | utf8mb4_unicode_ci |
| collation_server              | utf8mb4_0900_ai_ci |
| default_collation_for_utf8mb4 | utf8mb4_0900_ai_ci |
+-------------------------------+--------------------+
4 rows in set (0.01 sec)

-- Create test table and view
create table t1 (a int primary key);
insert into t1 values (1),(2);
-- Set collation to match database's, so view's calculated column has consistent collation
set collation_connection='utf8mb4_unicode_ci';
create or replace view v1 as select a, concat('test', a) as b from t1;

-- Query works
mysql> select * from v1 where b = 'test1';
1 row in set (0.00 sec)

-- Revert to default collation for the connection
set collation_connection='utf8mb4_0900_ai_ci';

mysql> select * from v1 where b = 'test1';
ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_unicode_ci,COERCIBLE) and (utf8mb4_0900_ai_ci,COERCIBLE) for operation '='
[2 Jan 2023 4:18] MySQL Verification Team
Hello Damien,

Thank you for the report and feedback.

regards,
Umesh