SHOW TABLE STATUS case sensitivity

[morgo]

Please answer these questions before submitting your issue. Thanks!

1. What did you do?

MySQL [test]> show tables;
Empty set (0.00 sec)

MySQL [test]> CREATE TABLE t1 (a int);
Query OK, 0 rows affected (0.12 sec)

MySQL [test]> SELECT * FROM T1;
Empty set (0.11 sec)

MySQL [test]> SHOW TABLE STATUS like 't1';
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-----------------------------------+---------------------+---------------------+-----------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time                       | Update_time         | Check_time          | Collation       | Checksum | Create_options | Comment |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-----------------------------------+---------------------+---------------------+-----------------+----------+----------------+---------+
| t1   | InnoDB |      10 | Compact    |  100 |            100 |         100 |             100 |          100 |       100 |            100 | 2018-08-28 14:31:40.165 +0000 UTC | 2018-08-28 14:32:04 | 2018-08-28 14:32:04 | utf8_general_ci |          |                |         |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-----------------------------------+---------------------+---------------------+-----------------+----------+----------------+---------+
1 row in set (0.00 sec)

MySQL [test]> SHOW TABLE STATUS like 'T1';
Empty set (0.00 sec)

Also:

MySQL [test]> SELECT * FROM information_schema.tables where table_schema='test' and table_name='T1'\G
Empty set (0.02 sec)

This command is however correct:

MySQL [test]> SHOW CREATE TABLE T1;
+-------+------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                         |
+-------+------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin |
+-------+------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

(I checked some other commands too: SHOW INDEXES FROM T1 is also correct leading to some inconsistency.)

2. What did you expect to see?

SHOW TABLE STATUS should be case insensitive / behave the same as the select statement, since TiDB behaves according to lower_case_table_names=2.

3. What did you see instead?

No result.

4. What version of TiDB are you using (tidb-server -V or run select tidb_version(); on TiDB)?

MySQL [test]> SELECT tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v2.1.0-beta-229-g5404e2e
Git Commit Hash: 5404e2e
Git Branch: master
UTC Build Time: 2018-08-14 01:46:09
GoVersion: go version go1.10.2 linux/amd64
Race Enabled: false
TiKV Min Version: 2.1.0-alpha.1-ff3dd160846b7d1aed9079c389fc188f7f5ea13e
1 row in set (0.00 sec)

[iamzhoug37]

Let me fix this problem

[ghost]

Confirming this issue is still present:

..
mysql> SHOW TABLE STATUS like 'T1';
Empty set (0.01 sec)

mysql> SELECT tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v4.0.0-beta.2-859-gccfc9b2ad
Edition: Community
Git Commit Hash: ccfc9b2ad0dcf8e447210de5f559d7fc208db968
Git Branch: master
UTC Build Time: 2020-07-29 09:37:45
GoVersion: go1.13
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
1 row in set (0.00 sec)

[dveeden]

..
mysql> SHOW TABLE STATUS like 'T1';
Empty set (0.02 sec)

mysql> SELECT tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v5.3.0-alpha-1209-g19a2b3c31
Edition: Community
Git Commit Hash: 19a2b3c31106470f83696f8fdaf443f150061a66
Git Branch: master
UTC Build Time: 2021-10-26 12:13:58
GoVersion: go1.16.8
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
1 row in set (0.00 sec)

[likzn]

I found schemaleTables in InfoSchema.The schemaleTables has all tables of assigned schema and it has case insensitive method.

TableExists(schema, table model.CIStr) bool

How about add a rewrite(like "t%" -> in(t1,t2)),we can make a select in tidb rather than predicate pushdown to TiKV. And i think it can solve this problem and it will not break compatibility(#8066 (comment)) of TiKV. @dveeden @xiongjiwei @morgo

[hawkingrei]

It is fixed by #31919.

[dveeden]

@hawkingrei are you sure this is fixed?

Trying with a recent master build gives me this:

mysql> SELECT tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v6.1.0-alpha-536-g2c3f717af
Edition: Community
Git Commit Hash: 2c3f717af66c6925f46776da867790ca150ecfcc
Git Branch: master
UTC Build Time: 2022-05-31 06:40:11
GoVersion: go1.18
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
Store: tikv
1 row in set (0.00 sec)

mysql> show tables;
Empty set (0.00 sec)

mysql> CREATE TABLE t1 (a int);
Query OK, 0 rows affected (0.11 sec)

mysql> SELECT * FROM T1;
Empty set (0.00 sec)

mysql> SHOW TABLE STATUS like 't1';
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation   | Checksum | Create_options | Comment |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------+----------+----------------+---------+
| t1   | InnoDB |      10 | Compact    |    0 |              0 |           0 |               0 |            0 |         0 |           NULL | 2022-05-31 08:47:11 | NULL        | NULL       | utf8mb4_bin |          |                |         |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------+----------+----------------+---------+
1 row in set (0.01 sec)

mysql> SHOW TABLE STATUS like 'T1';
Empty set (0.02 sec)

mysql> SELECT * FROM information_schema.tables where table_schema='test' and table_name='T1'\G
Empty set (0.02 sec)

mysql> SHOW CREATE TABLE T1;
+-------+------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                               |
+-------+------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+-------+------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[hawkingrei]

Sorry, Show table is fixed by #31919. but show table status is not fixed. but I think it is easy that it has the same way to fix it.

[likzn]

/assign

[AricSu]

There seems to be still some parts of compatibility issues with MySQL. Please, reopen and fix it, thanks.
@hawkingrei