select from information_schema.columns contains duplicate records

[D3Hunter]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

this bug is found during import 10K tables using lightning, and it get duplicate columns from tidb

mysql> SELECT table_name, column_name, column_type, generation_expression, extra FROM information_schema.columns WHERE table_schema = 'coms' ORDER BY table_name, ordinal_position limit 10;
+------------+-------------+---------------+-----------------------+-------+
| table_name | column_name | column_type   | generation_expression | extra |
+------------+-------------+---------------+-----------------------+-------+
| PK_10200   | COL1        | varbinary(10) |                       |       |
| PK_10200   | COL1        | varbinary(10) |                       |       |
| PK_10200   | COL2        | varchar(20)   |                       |       |
| PK_10200   | COL2        | varchar(20)   |                       |       |
| PK_10200   | COL4        | datetime      |                       |       |
| PK_10200   | COL4        | datetime      |                       |       |
| PK_10200   | COL3        | bigint(20)    |                       |       |
| PK_10200   | COL3        | bigint(20)    |                       |       |
| PK_10200   | COL5        | float         |                       |       |
| PK_10200   | COL5        | float         |                       |       |
+------------+-------------+---------------+-----------------------+-------+
10 rows in set (0.15 sec)

it works if i filter by table_name

mysql> SELECT table_name, column_name, column_type, generation_expression, extra FROM information_schema.columns WHERE table_schema = 'coms' and table_name='PK_10200' ORDER BY table_name, ordinal_position;
+------------+-------------+---------------+-----------------------+-------+
| table_name | column_name | column_type   | generation_expression | extra |
+------------+-------------+---------------+-----------------------+-------+
| PK_10200   | COL1        | varbinary(10) |                       |       |
| PK_10200   | COL2        | varchar(20)   |                       |       |
| PK_10200   | COL4        | datetime      |                       |       |
| PK_10200   | COL3        | bigint(20)    |                       |       |
| PK_10200   | COL5        | float         |                       |       |
+------------+-------------+---------------+-----------------------+-------+

2. What did you expect to see? (Required)

no duplicate

3. What did you see instead (Required)

duplicate

4. What is your TiDB version? (Required)

mysql> select tidb_version();
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                             |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v8.0.0-alpha
Edition: Community
Git Commit Hash: 630651209d7c5163d6f32a90587b17c15dc7be13
Git Branch: heads/refs/tags/v8.0.0-alpha
UTC Build Time: 2024-03-14 11:45:40
GoVersion: go1.21.6
Race Enabled: false
Check Table Before Drop: false
Store: tikv |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

[ywqzzy]

Further more, I have found the query unstable with row count returned..

mysql --comments --host 10.2.12.147 --port 30098 -u root -e "SELECT * FROM information_schema.columns WHERE table_schema = 'coms';" > log.1

row count varies from 32552 to 32573

[ywqzzy]

The order of tables returned by is.SchemaTables(schema) is unstable.