dolthub / dolt

Dolt – Git for Data
Apache License 2.0
17.72k stars 501 forks source link

`SHOW CREATE` doesn't show indexes on virtual generated columns: #8275

Open nicktobey opened 4 weeks ago

nicktobey commented 4 weeks ago

To reproduce:

create table t(a int primary key, b int generated always as (a*a), unique key (b));
show create table t;

Result:

+-------+------------------------------------------------------------------+
| Table | Create Table                                                     |
+-------+------------------------------------------------------------------+
| t     | CREATE TABLE `t` (                                               |
|       |   `a` int NOT NULL,                                              |
|       |   `b` int GENERATED ALWAYS AS ((`a` * `a`)),                     |
|       |   PRIMARY KEY (`a`)                                              |
|       | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin |
+-------+------------------------------------------------------------------+

The index is present, and functions, as seen here:

> insert into t(a) values (2), (-2);
duplicate unique key given: [4]

But it is omitted from SHOW CREATE statements. This happens both in Dolt and in GMS.

nicktobey commented 4 weeks ago

Of note, this only happens with virtual generated columns. Indexes on stored generated columns display as normal.