SHOW DATABASES LIKE should be case insensitive

[espresso98]

Bug Report

1. Minimal reproduce step

DROP DATABASE IF EXISTS `TEST_$1`;
DROP DATABASE IF EXISTS `test_$2`;

CREATE DATABASE `TEST_$1`;
CREATE DATABASE `test_$2`;
SHOW DATABASES LIKE "TEST%";
SHOW DATABASES LIKE "test%";

DROP DATABASE `TEST_$1`;
DROP DATABASE `test_$2`;

2. What did you expect to see?

mysql> SHOW DATABASES LIKE "TEST%";
+------------------+
| Database (TEST%) |
+------------------+
| TEST_$1          |
| test_$2          |
+------------------+
2 rows in set (0.00 sec)

mysql> SHOW DATABASES LIKE "test%";
+------------------+
| Database (test%) |
+------------------+
| TEST_$1          |
| test_$2          |
+------------------+
2 rows in set (0.00 sec)

3. What did you see instead

SHOW DATABASES LIKE "TEST%";
+----------+
| Database |
+----------+
| TEST_$1  |
+----------+
1 row in set (0.00 sec)

mysql> SHOW DATABASES LIKE "test%";
+----------+
| Database |
+----------+
| test_$2  |
+----------+
1 row in set (0.00 sec)

4. What is your TiDB version?

tidb_version(): Release Version: v6.1.0-alpha-460-g2d44ac927
Edition: Community
Git Commit Hash: 2d44ac9274b4694ed237fc14e5d6500398e93164
Git Branch: master
UTC Build Time: 2022-05-19 04:40:29
GoVersion: go1.18.1
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false

[fanrenhoo]

This should be correct I guess. Under linux, even mysql will be case sensitive for Database name

[espresso98]

I worked on macOS 11.2.3 (20D91).

[morgo]

The bug here is with SHOW DATABASES LIKE. It should be showing databases that match in an insensitive manor, but it is infact case sensistive.

This is similar to #7518

[likzn]

Is this the reason of it? https://docs.pingcap.com/tidb/v6.0/character-set-and-collation
TiDB defaults to using a binary collation. This differs from MySQL, which uses a case-insensitive collation by default.

I ran the below test, it can work. I assign collate of utf8mb4_general_ci.

func TestShowDatabasesLike(t *testing.T) {
	store, clean := testkit.CreateMockStore(t)
	defer clean()
	tk := testkit.NewTestKit(t, store)
	require.True(t, tk.Session().Auth(&auth.UserIdentity{
		Username: "root", Hostname: "%"}, nil, nil))

	tk.MustExec("DROP DATABASE IF EXISTS `TEST$1`")
	tk.MustExec("DROP DATABASE IF EXISTS `test$2`")
	tk.MustExec("CREATE DATABASE `TEST$1`;")
	tk.MustExec("CREATE DATABASE `test$2`;")

	tk.MustQuery("SHOW DATABASES LIKE 'TEST$%' COLLATE utf8mb4_general_ci;").Check(testkit.Rows("TEST$1", "test$2"))
	tk.MustQuery("SHOW DATABASES LIKE 'test$%' COLLATE utf8mb4_general_ci;").Check(testkit.Rows("TEST$1", "test$2"))
}

[hawkingrei]

Is this the reason of it? https://docs.pingcap.com/tidb/v6.0/character-set-and-collation TiDB defaults to using a binary collation. This differs from MySQL, which uses a case-insensitive collation by default.

I ran the below test, it can work. I assign collate of utf8mb4_general_ci.

func TestShowDatabasesLike(t *testing.T) {
	store, clean := testkit.CreateMockStore(t)
	defer clean()
	tk := testkit.NewTestKit(t, store)
	require.True(t, tk.Session().Auth(&auth.UserIdentity{
		Username: "root", Hostname: "%"}, nil, nil))

	tk.MustExec("DROP DATABASE IF EXISTS `TEST$1`")
	tk.MustExec("DROP DATABASE IF EXISTS `test$2`")
	tk.MustExec("CREATE DATABASE `TEST$1`;")
	tk.MustExec("CREATE DATABASE `test$2`;")

	tk.MustQuery("SHOW DATABASES LIKE 'TEST$%' COLLATE utf8mb4_general_ci;").Check(testkit.Rows("TEST$1", "test$2"))
	tk.MustQuery("SHOW DATABASES LIKE 'test$%' COLLATE utf8mb4_general_ci;").Check(testkit.Rows("TEST$1", "test$2"))
}

Yes. but we also need to think of old users who are using the old collation.

[e1ijah1]

Hi, I'd like to work on this issue

[hawkingrei]

Hi, I'd like to work on this issue

Great! Thank you~

[hawkingrei]

SHOW TABLE had some problems and were fixed in #31919. so we add a filter plannercore.ShowPredicateExtractor in the ShowExec. It will bring the like expression to the place where to get meta and filter them for getting the right result.