TiDB doesn't give any result for upper case sql_mode on SHOW SESSION VARIABLES LIKE 'SQL_MODE'

[espresso98]

Bug Report

1. Minimal reproduce step

SET @@SQL_MODE='NO_BACKSLASH_ESCAPES';
SHOW SESSION VARIABLES LIKE 'SQL_MODE';
SHOW SESSION VARIABLES LIKE 'sql_mode';

2. What did you expect to see?

mysql> SET @@SQL_MODE='NO_BACKSLASH_ESCAPES';
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW SESSION VARIABLES LIKE 'SQL_MODE';
+---------------+----------------------+
| Variable_name | Value                |
+---------------+----------------------+
| sql_mode      | NO_BACKSLASH_ESCAPES |
+---------------+----------------------+
1 row in set (0.01 sec)

mysql> SHOW SESSION VARIABLES LIKE 'sql_mode';
+---------------+----------------------+
| Variable_name | Value                |
+---------------+----------------------+
| sql_mode      | NO_BACKSLASH_ESCAPES |
+---------------+----------------------+
1 row in set (0.00 sec)

3. What did you see instead

tidb> SET @@SQL_MODE='NO_BACKSLASH_ESCAPES';
Query OK, 0 rows affected (0.00 sec)

tidb> SHOW SESSION VARIABLES LIKE 'SQL_MODE';   -- doesn't show any result
Empty set (0.01 sec)

tidb> SHOW SESSION VARIABLES LIKE 'sql_mode';
+---------------+----------------------+
| Variable_name | Value                |
+---------------+----------------------+
| sql_mode      | NO_BACKSLASH_ESCAPES |
+---------------+----------------------+
1 row in set (0.01 sec)

4. What is your TiDB version?

tidb_version(): Release Version: v5.5.0-alpha-210-g11f4ca802
Edition: Community
Git Commit Hash: 11f4ca802083ee38d5972730ba8f9b72395316fb
Git Branch: master
UTC Build Time: 2022-02-24 04:52:01
GoVersion: go1.17.2
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
1 row in set (0.02 sec)

[Defined2014]

The root case is TiDB use utf8mb4_bin as the default collation. Whereas mysql use utf8mb4_0900_ai_ci as the default collation.

TiDB:
mysql> show session variables like '%collation%';
+---------------------------------------+-----------------+
| Variable_name                         | Value           |
+---------------------------------------+-----------------+
| collation_connection                  | utf8_general_ci |
| collation_database                    | utf8mb4_bin     |
| collation_server                      | utf8mb4_bin     |
| tidb_hash_exchange_with_new_collation | ON              |
+---------------------------------------+-----------------+

mysql:
mysql> show session variables like '%collation%';
+-------------------------------+--------------------+
| Variable_name                 | Value              |
+-------------------------------+--------------------+
| collation_connection          | utf8_general_ci    |
| collation_database            | utf8mb4_0900_ai_ci |
| collation_server              | utf8mb4_0900_ai_ci |
| default_collation_for_utf8mb4 | utf8mb4_0900_ai_ci |
+-------------------------------+--------------------+

[fanrenhoo]

/assign

[morgo]

This is also the case with the statement show status like 'x'. It's even more of a problem for status variables because many of them do have a specific case.