`SHOW CREATE TABLE` drops the primary key column order on a table with a generated column
- Dominant language
- Go
- Stars
- 24.5k
- Forks
- 873
- Avg merge
- 1d 8h
- Merged PRs (30d)
- 120
Description
`SHOW CREATE TABLE` reports the primary key in declaration order instead of key order when the table has a generated column. Given:
```sql
CREATE TABLE ord (a INT, b INT, c VARCHAR(20), v VARCHAR(20) GENERATED ALWAYS AS (concat(c,'!')), PRIMARY KEY (b, a));
```
```sh
# Identical table without generated columns reports `(b, a)`.
$ dolt sql -q "show create table ord" -r vertical
*************************** 1. row ***************************
Table: ord
Create Table: CREATE TABLE `ord` (
`a` int NOT NULL,
`b` int NOT NULL,
`c` varchar(20),
`v` varchar(20) GENERATED ALWAYS AS (concat(`c`,'!')),
PRIMARY KEY (`a`,`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin
```
```sh
$ mysql -e "SHOW CREATE TABLE ord\G"
*************************** 1. row ***************************
Table: ord
Create Table: CREATE TABLE `ord` (
`a` int NOT NULL,
`b` int NOT NULL,
`c` varchar(20) DEFAULT NULL,
`v` varchar(20) GENERATED ALWAYS AS (concat(`c`,_utf8mb4'!')) VIRTUAL,
PRIMARY KEY (`b`,`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
```
Reproduced on dolthub/dolt@78b1425c6b.
Contributor guide
No contributing guide indexed for this repository
Research direction
Reproduce the issue with the provided CREATE TABLE statement and `SHOW CREATE TABLE ord -r vertical` command, then trace the SHOW CREATE TABLE implementation in the Go code. Compare the generated-column case with the identical table without a generated column and verify that the reported primary-key order matches `(b, a)`.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- go, mysql
- Domain
- databases
- Issue type
- Bug
- Difficulty
- 3/5
- Estimated time
- 1-2 days
- Activity status
- Quiet
- Clarity
- Mostly clear
- Newbie friendliness
- 68/100