Escaping underscores in LIKE isn't working properly

[dveeden]

Bug Report

In SQL for LIKE a % is a wildcard of zero or more characters and _ is a wildcard for a single character.

However in TiDB this doesn't seem to work consistently for variables.

1. Minimal reproduce step (Required)

SHOW SESSION VARIABLES LIKE 'character\_set\_%';
SHOW SESSION VARIABLES LIKE 'character\_set\\\_%';

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

With MySQL 8.1.0

sql> SHOW SESSION VARIABLES LIKE 'character\_set\_%';
+--------------------------+---------+
| Variable_name            | Value   |
+--------------------------+---------+
| character_set_client     | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database   | utf8mb4 |
| character_set_filesystem | binary  |
| character_set_results    | utf8mb4 |
| character_set_server     | utf8mb4 |
| character_set_system     | utf8mb3 |
+--------------------------+---------+
7 rows in set (0.0053 sec)

sql> SHOW SESSION VARIABLES LIKE 'character\_set\\\_%';
Empty set (0.0023 sec)

3. What did you see instead (Required)

With TiDB v7.3.0:

sql> SHOW SESSION VARIABLES LIKE 'character\_set\_%';
+--------------------------+--------------------------------------------------------+
| Variable_name            | Value                                                  |
+--------------------------+--------------------------------------------------------+
| character_set_client     | utf8mb4                                                |
| character_set_connection | utf8mb4                                                |
| character_set_database   | utf8mb4                                                |
| character_set_filesystem | binary                                                 |
| character_set_results    | utf8mb4                                                |
| character_set_server     | utf8mb4                                                |
| character_set_system     | utf8                                                   |
| character_sets_dir       | /usr/local/mysql-5.6.25-osx10.8-x86_64/share/charsets/ |
+--------------------------+--------------------------------------------------------+
8 rows in set (0.0010 sec)

sql> SHOW SESSION VARIABLES LIKE 'character\_set\\\_%';
+--------------------------+---------+
| Variable_name            | Value   |
+--------------------------+---------+
| character_set_client     | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database   | utf8mb4 |
| character_set_filesystem | binary  |
| character_set_results    | utf8mb4 |
| character_set_server     | utf8mb4 |
| character_set_system     | utf8    |
+--------------------------+---------+
7 rows in set (0.0029 sec)

4. What is your TiDB version? (Required)

sql> SELECT TIDB_VERSION()\G
*************************** 1. row ***************************
TIDB_VERSION(): Release Version: v7.3.0
Edition: Community
Git Commit Hash: 40b72e7a9a4fc9670d4c5d974dd503a3c6097471
Git Branch: heads/refs/tags/v7.3.0
UTC Build Time: 2023-08-08 10:08:14
GoVersion: go1.20.7
Race Enabled: false
Check Table Before Drop: false
Store: tikv
1 row in set (0.0012 sec)

[dveeden]

For the SHOW SESSION VARIABLES LIKE 'character\_set\_%' the character_sets_dir variable shouldn't match:

sql> SELECT 'character_sets_dir' LIKE 'character\_set\_%';
+-----------------------------------------------+
| 'character_sets_dir' LIKE 'character\_set\_%' |
+-----------------------------------------------+
|                                             0 |
+-----------------------------------------------+
1 row in set (0.0005 sec)

sql> SELECT 'character_set_client' LIKE 'character\_set\_%';
+-------------------------------------------------+
| 'character_set_client' LIKE 'character\_set\_%' |
+-------------------------------------------------+
|                                               1 |
+-------------------------------------------------+
1 row in set (0.0012 sec)

[djshow832]

I don't think it's major and I'll close all the cherry pick PRs.