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 '='