case-sensitivity issue when query information_schema.tables between TiDB and MySQL

[zhangyangyu]

Bug Report

Please answer these questions before submitting your issue. Thanks!

This seems to affect liquibase migration behaviour. It uses getTables and fire sql like:

SELECT TABLE_SCHEMA TABLE_CAT, NULL  TABLE_SCHEM,  TABLE_NAME, IF(TABLE_TYPE='BASE TABLE' or TABLE_TYPE='SYSTEM VERSIONED', 'TABLE', TABLE_TYPE) as TABLE_TYPE, TABLE_COMMENT REMARKS, NULL TYPE_CAT, NULL TYPE_SCHEM, NULL TYPE_NAME, NULL SELF_REFERENCING_COL_NAME,  NULL REF_GENERATION FROM INFORMATION_SCHEMA.TABLES  WHERE (ISNULL(database()) OR (TABLE_SCHEMA = database())) AND TABLE_NAME = 'databasechangelog'  AND TABLE_TYPE IN ('BASE TABLE','SYSTEM VERSIONED') ORDER BY TABLE_TYPE, TABLE_SCHEMA, TABLE_NAME;

1. Minimal reproduce step (Required)

use test;
CREATE TABLE `CATEGORIES` (`id` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`));
select * from information_schema.tables where table_schema='test'; // we can see `CATEGORIES` table
select * from information_schema.tables where table_schema='test' and table_name='categories';

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

In MySQL:

mysql> select * from information_schema.tables where table_schema='test' and table_name='categories';
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | ENGINE | VERSION | ROW_FORMAT | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | AUTO_INCREMENT | CREATE_TIME         | UPDATE_TIME | CHECK_TIME | TABLE_COLLATION | CHECKSUM | CREATE_OPTIONS | TABLE_COMMENT |
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------+
| def           | test         | categories | BASE TABLE | InnoDB |      10 | Dynamic    |          0 |              0 |       16384 |               0 |            0 |         0 |              1 | 2021-12-02 13:44:38 | NULL        | NULL       | utf8_general_ci |     NULL |                |               |
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------+

3. What did you see instead (Required)

mysql> select * from information_schema.tables where table_schema='test' and table_name='categories';
Empty set (0.01 sec)

4. What is your TiDB version? (Required)

mysql> select tidb_version();
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                                                                       |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v5.3.0
Edition: Community
Git Commit Hash: 4a1b2e9fe5b5afb1068c56de47adb07098d768d6
Git Branch: heads/refs/tags/v5.3.0
UTC Build Time: 2021-11-24 13:31:18
GoVersion: go1.16.4
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Both TiDB and MySQL has lower_case_table_names to 2 since I am on macOS.

[hawkingrei]

/assign

[zhangyangyu]

select from INFORMATION_SCHEMA.COLUMNS get the same problem. seems all columns like table_schema and table_name have the same problem.

[xiongjiwei]

dup of #29910

[xiongjiwei]

I will close this one. /cc @hawkingrei

[github-actions]

Please check whether the issue should be labeled with 'affects-x.y' or 'fixes-x.y.z', and then remove 'needs-more-info' label.

[zhangyangyu]

/remove-label needs-more-info