Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
5.5, 10.0, 10.1, 10.2, 10.3, 10.4
-
None
Description
I start mysqld on Linux with --lower-case-table-names=0, which is the default on Linux, and run this script:
CREATE OR REPLACE TABLE t1 (a INT); |
SELECT count(*) FROM information_schema.tables WHERE table_name='t1'; |
SELECT count(*) FROM information_schema.tables WHERE table_name='T1'; |
SELECT count(*) FROM information_schema.tables WHERE table_name LIKE 't1'; |
SELECT count(*) FROM information_schema.tables WHERE table_name LIKE 'T1'; |
SELECT COLLATION(table_name) FROM information_schema.tables LIMIT 1; |
+----------+
|
| count(*) |
|
+----------+
|
| 1 |
|
+----------+
|
+----------+
|
| count(*) |
|
+----------+
|
| 0 |
|
+----------+
|
+----------+
|
| count(*) |
|
+----------+
|
| 2 |
|
+----------+
|
+----------+
|
| count(*) |
|
+----------+
|
| 2 |
|
+----------+
|
+-----------------------+
|
| COLLATION(table_name) |
|
+-----------------------+
|
| utf8_general_ci |
|
+-----------------------+
|
The above results look inconsistent:
- The collation of the column table_name is reported as case insensitive
- However the comparison operator (=) is actually done case sensitively
- LIKE is indeed done case insensitively, but this is wrong: tables T1 and t1 must be uniqie
This inconsistency can be a reason of troubles when one wants to make a list of unique table names, e.g. in the following scenario:
CREATE OR REPLACE TABLE t1 (a INT); |
CREATE OR REPLACE TABLE T1 (a INT); |
CREATE OR REPLACE TABLE t2 AS |
SELECT DISTINCT table_name |
FROM information_schema.tables |
WHERE table_schema='test' |
AND table_name LIKE '%1'; |
SELECT * FROM t2; |
+------------+
|
| table_name |
|
+------------+
|
| t1 |
|
+------------+
|
DISTINCT changes the comparison style to be case sensitive, even for the operator '='.
The above result is wrong. Both tables 't1' and 'T1' should be in the result. The are unique table names!
Note, if I now remove the DISTINCT keyword, it returns a good result:
CREATE OR REPLACE TABLE t1 (a INT); |
CREATE OR REPLACE TABLE T1 (a INT); |
CREATE OR REPLACE TABLE t2 AS |
SELECT table_name |
FROM information_schema.tables |
WHERE table_schema='test' |
AND table_name LIKE '%1'; |
SELECT * FROM t2; |
+------------+
|
| table_name |
|
+------------+
|
| t1 |
|
| T1 |
|
+------------+
|
Conclusion:
The choice of utf8_general_ci for INFORMATION_SCHEMA.TABLES.TABLE_NAME does not look good..
It does not reflect table name uniqueness.