dolthub / dolthub/dolt

Generated columns that reference a later generated column return `NULL`

Open
#11,338 0 comments 0 reactions 0 assignees View on GitHub
bug correctness sql
Dominant language
Go
Stars
24.4k
Forks
873
Avg merge
1d 8h
Merged PRs (30d)
120

Description

MySQL allows one generated column 's formula to reference another generated column, but only if that other column is defined *earlier* in the table. If defined *later* the table creation is rejected.

Dolt does not enforce the ordering rule. It accepts a `CREATE TABLE` and computes the offending column as `NULL`.

```sql
-- Dolt (v2.2.1)
CREATE TABLE fwd_gen (
a INT,
b INT AS (c + 1) VIRTUAL, -- references c, defined later
c INT AS (a + 1) VIRTUAL
);
INSERT INTO fwd_gen (a) VALUES (10);
SELECT a, b, c FROM fwd_gen;
+----+------+----+
| a | b | c |
+----+------+----+
| 10 | NULL | 11 |
+----+------+----+
1 row in set (0.00 sec)
```
```sql
-- MySQL (8.4.8)
CREATE TABLE fwd_gen (
a INT,
b INT AS (c + 1) VIRTUAL,
c INT AS (a + 1) VIRTUAL
);
-- ERROR 3107 (HY000): Generated column can refer only to generated columns defined prior to it.
```
`CREATE TABLE` and `ALTER TABLE` should reject the above shape.

Contributor guide

No contributing guide indexed for this repository

Research direction

Reproduce the CREATE TABLE example and then check the corresponding ALTER TABLE case. Trace the generated-column validation for both entry points and ensure a generated column cannot reference a later generated column. Done means both statements reject the forward reference instead of creating a column that evaluates to NULL.

Written by the indexing model from the issue text.

Assessment

Tech stack
mysql, sql
Domain
databases
Issue type
Bug
Difficulty
3/5
Estimated time
1-2 days
Activity status
Quiet
Clarity
Mostly clear
Newbie friendliness
55/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.