[#MDEV-24657] Illegal mix of collation errors due to mysql.user having been converted into view

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.

[MDEV-24657] Illegal mix of collation errors due to mysql.user having been converted into view Created: 2021-01-22 Updated: 2021-01-22
Status:	Open
Project:	MariaDB Server
Component/s:	Character Sets
Affects Version/s:	10.4, 10.5
Fix Version/s:	10.4, 10.5

[MDEV-24657] Illegal mix of collation errors due to mysql.user having been converted into view Created: 2021-01-22 Updated: 2021-01-22