pingcap / pingcap/tidb

TiDB(MUL) and MySQL(PRI) are incompatible with the COLUMN_KEY of information_schema. When creating a table using a multi-column UNIQUE INDEX

Open
#44,190 1 comment 0 reactions 1 assignee Claimed by @bb7133 View on GitHub
compatibility-mysql80 severity/moderate sig/sql-infra type/bug
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Bug Report

Please answer these questions before submitting your issue. Thanks!

### 1. Minimal reproduce step (Required)

1. The SQL
```sql
CREATE TABLE `company_info_tl` (
`company_id` bigint(20) NOT NULL COMMENT '表ID,主键,供其他表做外键',
`company_short_name` varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '公司简称',
`company_full_name` varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '公司全称',
`lang` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '语言',
`tenant_id` bigint(20) NOT NULL DEFAULT 0 COMMENT '租户ID,hpfm_tenant.tenant_id',
UNIQUE INDEX `company_info_tl_u1`(`company_id`, `lang`, `tenant_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = '公司附属信息多语言表' ROW_FORMAT = Dynamic;
```

2. According to the definition of ["If Key is MUL, the column is the first column of a nonunique index in which multiple occurrences of a given value are permitted within the column"](https://dev.mysql.com/doc/refman/8.0/en/show-columns.html), when we create a table with an `unique multi-column index`, the `COLUMN_KEY` field should display it as `PRI` instead of `MUL` of the first one of multi-columns.

```sql
mysql> select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,COLUMN_KEY from information_schema.columns where table_name='company_info_tl'; +--------------+----------------------+--------------------+------------+ | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | COLUMN_KEY |
+--------------+----------------------+--------------------+------------+
| test | company_info_tl | company_id | MUL |
| test | company_info_tl | company_short_name | |
| test | company_info_tl | company_full_name | |
| test | company_info_tl | lang | |
| test | company_info_tl | tenant_id | |
+--------------+----------------------+--------------------+------------+
5 rows in set (0.02 sec)

mysql> desc company_info_tl;
+--------------------+--------------+------+------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+--------------+------+------+---------+-------+
| company_id | bigint(20) | NO | MUL | NULL | |
| company_short_name | varchar(150) | NO | | NULL | |
| company_full_name | varchar(150) | NO | | NULL | |
| lang | varchar(30) | NO | | NULL | |
| tenant_id | bigint(20) | NO | | 0 | |
+--------------------+--------------+------+------+---------+-------+
5 rows in set (0.01 sec)

```

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

```sql

mysql> select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,COLUMN_KEY from information_schema.columns where table_name='company_info_tl';
+--------------+----------------------+--------------------+------------+
| TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | COLUMN_KEY |
+--------------+----------------------+--------------------+------------+
| test | company_info_tl | company_id | PRI |
| test | company_info_tl | company_short_name | |
| test | company_info_tl | company_full_name | |
| test | company_info_tl | lang | PRI |
| test | company_info_tl | tenant_id | PRI |
+--------------+----------------------+--------------------+------------+
5 rows in set (0.06 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.26-1 |
+-----------+
1 row in set (0.05 sec)

mysql> select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,COLUMN_KEY from information_schema.columns where table_name='company_info_tl'; +--------------+----------------------+--------------------+------------+
| TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | COLUMN_KEY |
+--------------+----------------------+--------------------+------------+
| test | company_info_tl | company_full_name | |
| test | company_info_tl | company_id | PRI |
| test | company_info_tl | company_short_name | |
| test | company_info_tl | lang | PRI |
| test | company_info_tl | tenant_id | PRI |
+--------------+----------------------+--------------------+------------+
5 rows in set (0.02 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.32 |
+-----------+
1 row in set (0.00 sec)

```

### 3. What did you see instead (Required)

```sql

mysql> select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,COLUMN_KEY from information_schema.columns where table_name='company_info_tl'; +--------------+----------------------+--------------------+------------+ | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | COLUMN_KEY |
+--------------+----------------------+--------------------+------------+
| test | company_info_tl | company_id | MUL |
| test | company_info_tl | company_short_name | |
| test | company_info_tl | company_full_name | |
| test | company_info_tl | lang | |
| test | company_info_tl | tenant_id | |
+--------------+----------------------+--------------------+------------+
5 rows in set (0.02 sec)

mysql> select version();
+--------------------+
| version() |
+--------------------+
| 5.7.25-TiDB-v7.0.0 |
+--------------------+
1 row in set (0.40 sec)
```

### 4. What is your TiDB version? (Required)

```sql
mysql> select version();
+--------------------+
| version() |
+--------------------+
| 5.7.25-TiDB-v7.0.0 |
+--------------------+
1 row in set (0.40 sec)
```

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.