[#MDEV-24584] Selecting INT column with COLLATE utf8mb4_general

Review below walk-through for how to reproduce the issue and the error messages involved.

Note it is not necessary for this to succeed- there should not be a reason to issue a COLLATE against an integer column like this. However, this behavior does not seem to be documented, so at a minimum if this is intended behavior, this should be more apparent.

If this can be made to work rather than error, this will ease how much code refactoring some users must perform when switching from MySQL/Oracle to MariaDB.

MariaDB [(none)]> SELECT VARIABLE_NAME, SESSION_VALUE

    -> FROM INFORMATION_SCHEMA.SYSTEM_VARIABLES WHERE

    -> VARIABLE_NAME LIKE 'character_set_c%' OR

    -> VARIABLE_NAME LIKE 'character_set_re%' OR

    -> VARIABLE_NAME LIKE 'collation_c%';

+--------------------------+-------------------+

| VARIABLE_NAME            | SESSION_VALUE     |

+--------------------------+-------------------+

| CHARACTER_SET_RESULTS    | latin1            |

| CHARACTER_SET_CONNECTION | latin1            |

| CHARACTER_SET_CLIENT     | latin1            |

| COLLATION_CONNECTION     | latin1_swedish_ci |

+--------------------------+-------------------+

4 rows in set (0.001 sec)

MariaDB [(none)]> select 1 collate utf8mb4_general_ci;

ERROR 1253 (42000): COLLATION 'utf8mb4_general_ci' is not valid for CHARACTER SET 'latin1'

MariaDB [(none)]> set names utf8mb4;

Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> SELECT VARIABLE_NAME, SESSION_VALUE FROM INFORMATION_SCHEMA.SYSTEM_VARIABLES WHERE VARIABLE_NAME LIKE 'character_set_c%' OR VARIABLE_NAME LIKE 'character_set_re%' OR VARIABLE_NAME LIKE 'collation_c%';

+--------------------------+--------------------+

| VARIABLE_NAME            | SESSION_VALUE      |

+--------------------------+--------------------+

| CHARACTER_SET_RESULTS    | utf8mb4            |

| CHARACTER_SET_CONNECTION | utf8mb4            |

| CHARACTER_SET_CLIENT     | utf8mb4            |

| COLLATION_CONNECTION     | utf8mb4_general_ci |

+--------------------------+--------------------+

4 rows in set (0.001 sec)

MariaDB [(none)]> select 1 collate utf8mb4_general_ci;

ERROR 1253 (42000): COLLATION 'utf8mb4_general_ci' is not valid for CHARACTER SET 'latin1'

MariaDB [(none)]> select 'TEST' collate utf8mb4_general_ci;

+-----------------------------------+

| 'TEST' collate utf8mb4_general_ci |

+-----------------------------------+

| TEST                              |

+-----------------------------------+

1 row in set (0.000 sec)

10.5 e4205fba

SET NAMES utf8mb4;

select 'TEST' collate utf8mb4_general_ci;

'TEST' collate utf8mb4_general_ci

TEST

select 1 collate utf8mb4_general_ci;

bug.t                                    [ fail ]

        Test ended at 2021-01-14 14:40:58

CURRENT_TEST: bug.t

mysqltest: At line 3: query 'select 1 collate utf8mb4_general_ci' failed: 1253: COLLATION 'utf8mb4_general_ci' is not valid for CHARACTER SET 'latin1'

MySQL 5.7.25

SET NAMES utf8mb4;

select 'TEST' collate utf8mb4_general_ci;

'TEST' collate utf8mb4_general_ci

TEST

select 1 collate utf8mb4_general_ci;

1 collate utf8mb4_general_ci

[MDEV-24584] Selecting INT column with COLLATE utf8mb4_general_ci throws an error Created: 2021-01-13 Updated: 2024-01-26 Resolved: 2021-11-08
Status:	Closed
Project:	MariaDB Server
Component/s:	Character Sets
Affects Version/s:	5.5, 10.1, 10.5.4, 10.5.6, 10.2, 10.3, 10.4, 10.5, 10.6
Fix Version/s:	10.5.14, 10.6.6, 10.7.2

[MDEV-24584] Selecting INT column with COLLATE utf8mb4_general_ci throws an error Created: 2021-01-13 Updated: 2024-01-26 Resolved: 2021-11-08