Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.4, 10.5
-
None
Description
10.4+ has mysql.user view for the backward compatibility, but this compatibility is flawed.
Simple queries from like
|
10.5 deadec4e |
select * from mysql.user where is_role = 'Y'; |
can fail with ER_CANT_AGGREGATE_2COLLATIONS (illegal mix of collations) errors when it wouldn't happen before. For example,
MariaDB [test]> select collation_connection from information_schema.views where table_schema = 'mysql' and table_name = 'user'; |
+----------------------+ |
| collation_connection |
|
+----------------------+ |
| latin1_swedish_ci |
|
+----------------------+ |
1 row in set (0.001 sec) |
|
|
MariaDB [test]> set names latin2; |
Query OK, 0 rows affected (0.000 sec) |
|
|
MariaDB [test]> select count(*) from mysql.user where is_role = 'Y'; |
ERROR 1267 (HY000): Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (latin2_general_ci,COERCIBLE) for operation '=' |
Before 10.4 the query would work fine:
|
10.3 7d04ce6a |
MariaDB [test]> select table_collation from information_schema.tables where table_schema = 'mysql' and table_name = 'user'; |
+-------------------+ |
| table_collation |
|
+-------------------+ |
| latin1_swedish_ci |
|
+-------------------+ |
1 row in set (0.001 sec) |
|
|
MariaDB [test]> set names latin2; |
Query OK, 0 rows affected (0.000 sec) |
|
|
MariaDB [test]> select count(*) from mysql.user where is_role = 'Y'; |
+----------+ |
| count(*) | |
+----------+ |
| 0 |
|
+----------+ |
1 row in set (0.001 sec) |
Apparently it happens because coercibility of is_role column in mysql.user table is 2, while in mysql.user view it's 4, same as the literal's.