SHOW cmd LIKE 'expr' filters on DBName not TableName

[wentaojin]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. What did you do? If possible, provide a recipe for reproducing the error.

TiDB cluster Show statistics can be perform fuzzy matching and equivalent query, The documentation is written for support, with a current documentation syntax description:

SHOW STATS_META[ShowLikeOrWhere] SHOW STATS_BUCKETS [ShowLikeOrWhere] SHOW STATS_STATISTICS [ShowLikeOrWhere]

After manual execute Analyze Table ，Unable to perform fuzzy matching, only equivalent query,As shown

2. What did you expect to see?

expect to see the result of fuzzy matching

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

mysql> select tidb_version(); | Release Version: v2.1.4 Git Commit Hash: a1d10c17039ec289ef490481b25c7091dba7ac31 Git Branch: release-2.1 UTC Build Time: 2019-02-15 06:24:33 GoVersion: go version go1.11.2 linux/amd64 Race Enabled: false TiKV Min Version: 2.1.0-alpha.1-ff3dd160846b7d1aed9079c389fc188f7f5ea13e Check Table Before Drop: false |

[winoros]

mysql> show stats_meta where table_name like 't%';
+---------+------------+---------------------+--------------+-----------+
| Db_name | Table_name | Update_time         | Modify_count | Row_count |
+---------+------------+---------------------+--------------+-----------+
| test    | t          | 2019-02-20 18:45:12 |            0 |         0 |
+---------+------------+---------------------+--------------+-----------+
1 row in set (0.01 sec)

mysql> show stats_meta where table_name like '%t';
+---------+------------+---------------------+--------------+-----------+
| Db_name | Table_name | Update_time         | Modify_count | Row_count |
+---------+------------+---------------------+--------------+-----------+
| test    | t          | 2019-02-20 18:45:12 |            0 |         0 |
+---------+------------+---------------------+--------------+-----------+
1 row in set (0.00 sec)

It's a little strange that i cannot reproduce this bug with the same TiDB version.

[wentaojin]

mysql> show stats_meta where table_name like 't%';
+---------+------------+---------------------+--------------+-----------+
| Db_name | Table_name | Update_time         | Modify_count | Row_count |
+---------+------------+---------------------+--------------+-----------+
| test    | t          | 2019-02-20 18:45:12 |            0 |         0 |
+---------+------------+---------------------+--------------+-----------+
1 row in set (0.01 sec)

mysql> show stats_meta where table_name like '%t';
+---------+------------+---------------------+--------------+-----------+
| Db_name | Table_name | Update_time         | Modify_count | Row_count |
+---------+------------+---------------------+--------------+-----------+
| test    | t          | 2019-02-20 18:45:12 |            0 |         0 |
+---------+------------+---------------------+--------------+-----------+
1 row in set (0.00 sec)

It's a little strange that i cannot reproduce this bug with the same TiDB version.

But my environment existed... I also feel very strange. Can you see here ?

[winoros]

@WentaoJin Emm, what's the result of like '%'?

[wentaojin]

@WentaoJin Emm, what's the result of like '%'?

show stats_meta where table_name like '%' ，this command can output results, as follow:

mysql> show stats_meta where table_name like '%'; +-----------+------------+---------------------+--------------+-----------+ | Db_name | Table_name | Update_time | Modify_count | Row_count | +-----------+------------+---------------------+--------------+-----------+ | MARVIN | tidb | 2019-02-19 19:23:25 | 0 | 0 | | MARVIN | foo_range | 2019-02-20 10:13:48 | 3 | 3 | | MARVIN | fo_range | 2019-02-20 10:15:38 | 0 | 0 | | bikeshare | trips | 2019-02-20 15:56:29 | 0 | 35041080 | +-----------+------------+---------------------+--------------+-----------+ 4 rows in set (0.00 sec)

mysql> show stats_meta where table_name like '%tidb'; Empty set (0.00 sec)

[winoros]

@WentaoJin What if you use an exact match like like 'trips'?

[wentaojin]

@WentaoJin What if you use an exact match like like 'trips'?

No，it also can not output result,but I found a strange phenomenon that the result of like '%trip%' is match with db_name, as follow：

mysql> show stats_meta; +-------------+------------+---------------------+--------------+-----------+ | Db_name | Table_name | Update_time | Modify_count | Row_count | +-------------+------------+---------------------+--------------+-----------+ | MARVIN | tidb | 2019-02-19 19:23:25 | 0 | 0 | | MARVIN | foo_range | 2019-02-20 10:13:48 | 3 | 3 | | MARVIN | fo_range | 2019-02-20 10:15:38 | 0 | 0 | | tidb | bike | 2019-02-21 16:26:20 | 0 | 7091771 | | tidb | tb2 | 2019-02-21 16:30:48 | 5 | 5 | | tidb_loader | checkpoint | 2019-02-21 17:42:48 | 1026 | 16 | | bike | tidb | 2019-02-21 17:40:59 | 0 | 0 | | bike | bike | 2019-02-21 17:43:16 | 0 | 7091773 | | bikeshare | trips | 2019-02-20 15:56:29 | 0 | 35041080 | +-------------+------------+---------------------+--------------+-----------+ 9 rows in set (0.00 sec)

mysql> show stats_meta like '%f%'; Empty set (0.00 sec)

mysql> show stats_meta like '%b%'; +-------------+------------+---------------------+--------------+-----------+ | Db_name | Table_name | Update_time | Modify_count | Row_count | +-------------+------------+---------------------+--------------+-----------+ | tidb | bike | 2019-02-21 16:26:20 | 0 | 7091771 | | tidb | tb2 | 2019-02-21 16:30:48 | 5 | 5 | | bike | tidb | 2019-02-21 17:40:59 | 0 | 0 | | bike | bike | 2019-02-21 17:43:16 | 0 | 7091773 | | bikeshare | trips | 2019-02-20 15:56:29 | 0 | 35041080 | | tidb_loader | checkpoint | 2019-02-21 17:42:48 | 1026 | 16 | +-------------+------------+---------------------+--------------+-----------+ 6 rows in set (0.00 sec)

mysql> show stats_meta where table_name like '%b%'; +-------------+------------+---------------------+--------------+-----------+ | Db_name | Table_name | Update_time | Modify_count | Row_count | +-------------+------------+---------------------+--------------+-----------+ | tidb | bike | 2019-02-21 16:26:20 | 0 | 7091771 | | tidb | tb2 | 2019-02-21 16:30:48 | 5 | 5 | | bike | tidb | 2019-02-21 17:40:59 | 0 | 0 | | bike | bike | 2019-02-21 17:43:16 | 0 | 7091773 | | bikeshare | trips | 2019-02-20 15:56:29 | 0 | 35041080 | | tidb_loader | checkpoint | 2019-02-21 17:42:48 | 1026 | 16 | +-------------+------------+---------------------+--------------+-----------+ 6 rows in set (0.00 sec)

mysql> show stats_meta where table_name like '%f%'; Empty set (0.00 sec)

[winoros]

@WentaoJin If you directly use show stats_meta like ..., it will match the first column which in this case is DBName. So this behavior is correct.

I want to provide you a TiDB binary with more log information to help us understand why this happened. Is this ok for you?

[wentaojin]

@WentaoJin If you directly use show stats_meta like ..., it will match the first column which in this case is DBName. So this behavior is correct.

I want to provide you a TiDB binary with more log information to help us understand why this happened. Is this ok for you?

So... Directly use show stats_meta like , It will match DBName that is correct , that's OK !!! Then where table_name like should macth tableName, Why not match instead of match DBName? Can not you Reproduce ？What information do you need tidb binary? Server version: 5.7.10-TiDB-v2.1.4 MySQL Community Server (Apache License 2.0)

[ghost]

I can confirm in master that the matching is on DBName, which doesn't really make sense, because it also applies filtering on the currently selected DBName. Here is a testcase:

DROP TABLE IF EXISTS ta1;
CREATE TABLE ta1 (id INT NOT NULL PRIMARY KEY auto_increment);
INSERT INTO ta1 VALUES (1);
ANALYZE TABLE ta1;
SELECT SLEEP(1);

SHOW STATS_META;
SHOW STATS_META LIKE 'ta1';
SHOW STATS_META LIKE 't%';
SHOW STATS_META LIKE 'ta%';
SHOW STATS_META LIKE 'te%';
..

mysql> SHOW STATS_META;
+---------+------------+----------------+---------------------+--------------+-----------+
| Db_name | Table_name | Partition_name | Update_time         | Modify_count | Row_count |
+---------+------------+----------------+---------------------+--------------+-----------+
| test    | ta1        |                | 2020-07-14 15:56:28 |            0 |         1 |
+---------+------------+----------------+---------------------+--------------+-----------+
1 row in set (0.00 sec)

mysql> SHOW STATS_META LIKE 'ta1';
Empty set (0.00 sec)

mysql> SHOW STATS_META LIKE 't%';
+---------+------------+----------------+---------------------+--------------+-----------+
| Db_name | Table_name | Partition_name | Update_time         | Modify_count | Row_count |
+---------+------------+----------------+---------------------+--------------+-----------+
| test    | ta1        |                | 2020-07-14 15:56:28 |            0 |         1 |
+---------+------------+----------------+---------------------+--------------+-----------+
1 row in set (0.00 sec)

mysql> SHOW STATS_META LIKE 'ta%';
Empty set (0.00 sec)

mysql> SHOW STATS_META LIKE 'te%';
+---------+------------+----------------+---------------------+--------------+-----------+
| Db_name | Table_name | Partition_name | Update_time         | Modify_count | Row_count |
+---------+------------+----------------+---------------------+--------------+-----------+
| test    | ta1        |                | 2020-07-14 15:56:28 |            0 |         1 |
+---------+------------+----------------+---------------------+--------------+-----------+
1 row in set (0.00 sec)

mysql> SELECT tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v4.0.0-beta.2-762-g77aecd4b2
Edition: Community
Git Commit Hash: 77aecd4b27e79a97215eb4fdd68f68f2ddf67d21
Git Branch: master
UTC Build Time: 2020-07-13 01:43:31
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)