dolthub / dolthub/dolt

`SHOW CREATE TABLE` drops the primary key column order on a table with a generated column

Open
#11,444 0 comments 0 reactions 0 assignees View on GitHub
correctness sql
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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.