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