pingcap / pingcap/tidb

warn or quote if object identifier have heading or trailing invisible special space

Open
#61,081 0 comments 0 reactions 0 assignees View on GitHub
affects-7.5 component/ddl report/customer type/enhancement
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Enhancement

in https://dev.mysql.com/doc/refman/9.1/en/identifiers.html, `Extended: U+0080 .. U+FFFF` are valid chars for identifier, include some spacial space chars, such as non-break space U+00A0, https://www.compart.com/en/unicode/U+00A0, it's very hard to identify with naked eyes from the result of `show tables` and result of `select * from information_schema.views`, as they don't quote the identifier name

although we can see the special space from result of `show create view `, but normally we don't know the existance of the special space, so will not quote it.

not sure if the compatibility will break, if we add quote in the result of `show tables` and `information_schema` tables, but at least we can add a warning log to help dev to easily diagnose

an example
```
mysql> create view `v ` as select * from t; -- it's ok to create without quote, we add it just to make the non-break space obvious
Query OK, 0 rows affected (0.53 sec)

mysql> select * from v;
ERROR 1146 (42S02): Table 'test.v' doesn't exist
mysql> drop view v;
ERROR 1051 (42S02): Unknown table 'test.v'

-- offset to skip tidb_mdl_view
mysql> select * from information_schema.views limit 1 offset 1;
+---------------+--------------+------------+------------------------------------------------+--------------+--------------+---------+---------------+----------------------+----------------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | VIEW_DEFINITION | CHECK_OPTION | IS_UPDATABLE | DEFINER | SECURITY_TYPE | CHARACTER_SET_CLIENT | COLLATION_CONNECTION |
+---------------+--------------+------------+------------------------------------------------+--------------+--------------+---------+---------------+----------------------+----------------------+
| def | test | v  | SELECT `test`.`t`.`id` AS `id` FROM `test`.`t` | CASCADED | NO | root@% | DEFINER | utf8mb4 | utf8mb4_general_ci |
+---------------+--------------+------------+------------------------------------------------+--------------+--------------+---------+---------------+----------------------+----------------------+
1 row in set (0.00 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t |
| v  |
+----------------+
2 rows in set (0.01 sec)

Contributor guide

Open the contributing guide

Research direction

Start by tracing the SHOW TABLES and INFORMATION_SCHEMA output paths, then compare them with SHOW CREATE VIEW for identifiers containing leading or trailing invisible spaces. Done means the chosen warning or quoting behavior makes these identifiers diagnosable without breaking the existing output compatibility.

Written by the indexing model from the issue text.

Assessment

Tech stack
go, mysql
Domain
databases
Issue type
Feature
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.