dolthub / dolthub/dolt

ALTER TABLE MODIFY COLUMN does not recompute a STORED generated column, leaving rows and indexes stale

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

Description

### Summary

`ALTER TABLE ... MODIFY COLUMN` (and `CHANGE COLUMN`) that changes a **STORED** generated column's expression updates the schema but leaves existing rows holding values computed by the **old** expression. Any secondary index on the column keeps the stale values too, so a query for the correct value returns nothing while a query for the stale value returns the row.

MySQL 8.4 regenerates the stored values, because changing a generated column's expression requires a table rebuild.

Reproduced on **Dolt 2.3.3** (darwin-arm64), and on 2.3.1.

### Reproduction

```sql
CREATE TABLE t(id INT PRIMARY KEY, x INT, y INT, z INT AS (x+y) STORED);
CREATE INDEX iz ON t(z);
INSERT INTO t(id,x,y) VALUES(1,2,3),(2,10,20);
ALTER TABLE t MODIFY COLUMN z INT AS (x*y) STORED;

SELECT id, z AS stored_value, x*y AS expression FROM t ORDER BY id;
SELECT id FROM t WHERE z=6; -- the correct value for row 1
SELECT id FROM t WHERE z=5; -- the stale value for row 1
SELECT count(*) FROM t WHERE z <> x*y;
```

`SHOW CREATE TABLE t` reports `z int GENERATED ALWAYS AS ((`x` * `y`)) STORED` in both engines.

| query | Dolt 2.3.3 | MySQL 8.4 |
| --- | --- | --- |
| `z, x*y` for row 1 | `5, 6` | `6, 6` |
| `z, x*y` for row 2 | `30, 200` | `200, 200` |
| `WHERE z=6` | no rows | row 1 |
| `WHERE z=5` | row 1 | no rows |
| `count(*) WHERE z <> x*y` | **2** | **0** |

### Notes

- `CHANGE COLUMN` behaves the same: `ALTER TABLE t CHANGE COLUMN z z INT AS (x+y+100) STORED` leaves `z` at the pre-`ALTER` value.
- `ADD COLUMN` is **not** affected: `ALTER TABLE t ADD COLUMN w INT AS (x*100) STORED` materializes `w` correctly for existing rows.
- The stale value is durable: it survives `dolt_commit` and is still there in a new session.
- A no-op rewrite such as `UPDATE t SET x=x` does not fix the row. An `UPDATE` that actually changes a base column does, so rows drift into correctness one write at a time.
- It also surfaces after a merge. If one branch changes the expression while another modifies a row, the merged row keeps the value computed under the old expression, and within a single merge some rows can follow the new expression while others follow the old.
- VIRTUAL generated columns are unaffected, since they are evaluated on read.

### Self-contained repro script

Needs `dolt` on `PATH`; exits 1 on the bug.

```bash
#!/usr/bin/env bash
set -u
DOLT="${DOLT:-dolt}"
W=$(mktemp -d); trap 'rm -rf "$W"' EXIT
cd "$W" && "$DOLT" init --name repro --email repro@example.com >/dev/null 2>&1

"$DOLT" sql -q "
CREATE TABLE t(id INT PRIMARY KEY, x INT, y INT, z INT AS (x+y) STORED);
CREATE INDEX iz ON t(z);
INSERT INTO t(id,x,y) VALUES(1,2,3),(2,10,20);
ALTER TABLE t MODIFY COLUMN z INT AS (x*y) STORED;" >/dev/null 2>&1

echo "-- schema now says z = x*y:"
"$DOLT" sql -r csv -q "SHOW CREATE TABLE t" | grep -o 'GENERATED ALWAYS AS ([^)]*))'
echo "-- stored values vs the expression (MySQL 8.4: 6 and 200):"
"$DOLT" sql -r csv -q "SELECT id, z AS stored_value, x*y AS expression FROM t ORDER BY id"
echo "-- index lookup for the correct value (MySQL 8.4: returns 1):"
"$DOLT" sql -r csv -q "SELECT id FROM t WHERE z=6"
echo "-- index lookup for the stale value (MySQL 8.4: returns nothing):"
"$DOLT" sql -r csv -q "SELECT id FROM t WHERE z=5"

bad=$("$DOLT" sql -r csv -q "SELECT count(*) FROM t WHERE z <> x*y" | tail -1)
echo
echo "EXPECTED: 0 rows where z <> x*y (MySQL 8.4 gives 0)"
echo "GOT: $bad"
[ "$bad" = 0 ] || { echo "BUG: stored generated column not recomputed after expression change"; exit 1; }
```

Output on 2.3.3:

```
-- schema now says z = x*y:
GENERATED ALWAYS AS ((`x` * `y`))
-- stored values vs the expression (MySQL 8.4: 6 and 200):
id,stored_value,expression
1,5,6
2,30,200
-- index lookup for the correct value (MySQL 8.4: returns 1):
id
-- index lookup for the stale value (MySQL 8.4: returns nothing):
id
1

EXPECTED: 0 rows where z <> x*y (MySQL 8.4 gives 0)
GOT: 2
BUG: stored generated column not recomputed after expression change
```

Found while building [DoltLite](https://github.com/dolthub/doltlite) and oracling its merge behavior against Dolt. Related but distinct: #11065 (stale stored generated column after `ON UPDATE CASCADE`).

Contributor guide

No contributing guide indexed for this repository

Research direction

Start by running the self-contained bash reproduction with dolt and compare the ALTER TABLE MODIFY COLUMN and CHANGE COLUMN results against the stated MySQL behavior. Trace the ALTER TABLE path for changing a STORED generated column and verify completion by confirming existing rows and indexes use the new expression, including after queries and a durable commit.

Written by the indexing model from the issue text.

Assessment

Tech stack
go, sql
Domain
databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Active
Clarity
Mostly clear
Newbie friendliness
52/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.