Selecting INT column with COLLATE utf8mb4_general_ci throws an error

[shawn0915]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

set names utf8mb4;
select 'TEST' collate utf8mb4_general_ci;
select 1 collate utf8mb4_general_ci;

2. What did you expect to see? (Required)

in MySQL, we can get this,

(root@127.1) [(none)] 09:17:46> select version();
+-----------+
| version() |
+-----------+
| 5.7.38    |
+-----------+
1 row in set (0.001 sec)

(root@127.1) [(none)] 09:17:50> create schema test;
Query OK, 1 row affected (0.002 sec)

(root@127.1) [(none)] 09:17:53> use test;
Database changed
(root@127.1) [test] 09:17:56> set names utf8mb4;
Query OK, 0 rows affected (0.001 sec)

(root@127.1) [test] 09:21:30> SELECT VARIABLE_NAME, VARIABLE_VALUE FROM performance_schema.session_variables WHERE VARIABLE_NAME LIKE 'character_set_c%' OR VARIABLE_NAME LIKE 'character_set_re%' OR VARIABLE_NAME LIKE 'collation_c%';
+--------------------------+--------------------+
| VARIABLE_NAME            | VARIABLE_VALUE     |
+--------------------------+--------------------+
| character_set_client     | utf8mb4            |
| character_set_connection | utf8mb4            |
| character_set_results    | utf8mb4            |
| collation_connection     | utf8mb4_general_ci |
+--------------------------+--------------------+
4 rows in set (0.004 sec)

(root@127.1) [performance_schema] 09:22:09> select 'TEST' collate utf8mb4_general_ci;
+-----------------------------------+
| 'TEST' collate utf8mb4_general_ci |
+-----------------------------------+
| TEST                              |
+-----------------------------------+
1 row in set (0.001 sec)

(root@127.1) [performance_schema] 09:22:28> select 1 collate utf8mb4_general_ci;
+------------------------------+
| 1 collate utf8mb4_general_ci |
+------------------------------+
| 1                            |
+------------------------------+
1 row in set (0.001 sec)

3. What did you see instead (Required)

but in TiDB, got this one,

TiDB [(none)] 08:37:17> select version();
+--------------------+
| version()          |
+--------------------+
| 5.7.25-TiDB-v6.0.0 |
+--------------------+
1 row in set (0.001 sec)

TiDB [test] 08:37:35> set names utf8mb4;
Query OK, 0 rows affected (0.000 sec)

TiDB [test] 08:37:41> SELECT VARIABLE_NAME, VARIABLE_VALUE FROM INFORMATION_SCHEMA.SESSION_VARIABLES WHERE VARIABLE_NAME LIKE 'character_set_c%' OR VARIABLE_NAME LIKE 'character_set_re%' OR VARIABLE_NAME LIKE 'collation_c%';
+--------------------------+----------------+
| VARIABLE_NAME            | VARIABLE_VALUE |
+--------------------------+----------------+
| character_set_connection | utf8mb4        |
| character_set_client     | utf8mb4        |
| collation_connection     | utf8mb4_bin    |
| character_set_results    | utf8mb4        |
+--------------------------+----------------+
4 rows in set (0.018 sec)

TiDB [test] 08:37:51> select 'TEST' collate utf8mb4_general_ci;
+-----------------------------------+
| 'TEST' collate utf8mb4_general_ci |
+-----------------------------------+
| TEST                              |
+-----------------------------------+
1 row in set (0.001 sec)

TiDB [test] 08:37:56> select 1 collate utf8mb4_general_ci;
ERROR 1253 (42000): COLLATION 'utf8mb4_general_ci' is not valid for CHARACTER SET 'binary'

4. What is your TiDB version? (Required)

tidb_version(): Release Version: v6.0.0
Edition: Community
Git Commit Hash: 36a9810441ca0e496cbd22064af274b3be771081
Git Branch: heads/refs/tags/v6.0.0
UTC Build Time: 2022-03-31 10:33:28
GoVersion: go1.18
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false

[aytrack]

Both mysql and tidb, the collation of 1 is binary:

MySQL root@127.0.0.1:test> select collation(1);
+--------------+
| collation(1) |
+--------------+
| binary       |
+--------------+

[xiongjiwei]

mysql> show create table t;
+-------+--------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                 |
+-------+--------------------------------------------------------------------------------------------------------------+
| t     | CREATE TABLE `t` (
  `a` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+--------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select a collate utf8mb4_general_ci from t;
+------------------------------+
| a collate utf8mb4_general_ci |
+------------------------------+
| 1                            |
+------------------------------+
1 row in set (0.00 sec)

seems number will convert to string if use collate clause

[seiya-annie]

/found customer