ALTER TABLE MODIFY COLUMN does not recompute a STORED generated column, leaving rows and indexes stale
- 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