matrixorigin / matrixorigin/matrixone
[Bug]: UPDATE of a generated column source leaves synchronous IVFFLAT index data stale
- Dominant language
- Go
- Stars
- 1.9k
- Forks
- 311
- Avg merge
- 1d 3h
- Merged PRs (30d)
- 768
Description
## Description
Updating a source column of a stored generated vector recomputes the base-table generated value but does not maintain a synchronous IVFFLAT index built on that generated column. Indexed Top-K continues to use the old vectors until the index is explicitly rebuilt.
## Environment
- Branch: `main`
- Commit: `01d60e1c4ded1b0f3fc1a4ecd75ce54e95e23b90`
- Deployment: local 1 Log / 1 TN / 2 CN; SQL endpoints 16001 and 16002
## Steps to reproduce
```sql
SET experimental_ivf_index=1;
SET probe_limit=1;
CREATE TABLE t(
id BIGINT PRIMARY KEY,
src VECF32(2),
vg VECF32(2) GENERATED ALWAYS AS (src) STORED,
note INT
);
INSERT INTO t(id,src,note)
SELECT result,
CAST(CONCAT('[',result,',0]') AS VECF32(2)),
result
FROM generate_series(1,100) g;
CREATE INDEX ix USING IVFFLAT ON t(vg)
LISTS=1 OP_TYPE 'vector_l2_ops';
UPDATE t SET src='[1000,0]' WHERE id=1;
UPDATE t SET src='[0.001,0]' WHERE id=100;
INSERT INTO t(id,src,note) VALUES(101,'[0.002,0]',101);
SELECT id FROM t
ORDER BY l2_distance(vg,'[0,0]')
LIMIT 5;
SELECT id FROM t
ORDER BY l2_distance(vg,'[0,0]')
LIMIT 5 BY RANK WITH OPTION 'mode=force';
```
## Actual behavior
The IVFFLAT path returns:
```text
101,1,2,3,4
```
The exact table-scan path returns:
```text
100,101,2,3,4
```
The generated base values are correct: id=1 has `vg=[1000,0]` and id=100 has `vg=[0.001,0]`. The active hidden IVFFLAT entries still contain the pre-update values:
```text
id=1 entry=[1,0]
id=100 entry=[100,0]
id=101 entry=[0.002,0]
```
Thus the result contains the row moved far away and omits the row moved nearest.
## Expected behavior
When UPDATE changes a source column of a stored generated vector, synchronous IVFFLAT maintenance must use the recomputed generated value. The indexed Top-K should return `100,101,2,3,4` without requiring a manual REINDEX.
## Stability and controls
- Reproduced in 3 independent databases on each of 2 CN endpoints.
- Both CNs returned the same stale indexed row set; FORCE returned the correct set.
- A synchronous IVFFLAT on a normal, non-generated vector column was maintained correctly by the equivalent UPDATEs.
- ASYNC IVFFLAT on the same generated-column schema converged to the correct set through CDC.
- HNSW on the same generated-column schema also converged to the correct set.
- `INSERT ... ON DUPLICATE KEY UPDATE src=VALUES(src)` and `REPLACE` maintained the synchronous IVFFLAT correctly; the gap is the ordinary UPDATE planning path.
- `ALTER TABLE t ALTER REINDEX ix IVFFLAT FORCE_SYNC` immediately restored the correct result on both CNs.
## Code analysis
`bindUpdate` calls `classifyIrregularIndexesForUpdate` using only `dmlCtx.updateCol2Expr`, before generated columns are recomputed (`pkg/sql/plan/bind_update.go`, around the call near line 429 and recomputation near line 962).
For `UPDATE ... SET src=...`, the affected-column check sees only `src`. The IVFFLAT index part is `vg`, so `irregularIndexAffectedByUpdatedColumnNames` reports the synchronous index group as unaffected and no inline maintenance pipeline is built. The planner later recomputes `vg` in the base-table final row image, but does not reclassify irregular indexes after adding generated columns to the assigned-column set.
ODKU/REPLACE and CDC-based indexes consume a complete final row image through different paths, explaining why those controls remain correct.
## Regression coverage
Add a synchronous IVFFLAT regression over a stored generated vector and update only its source column. Assert both the base generated value and indexed Top-K after single-row and multi-row UPDATE. Retain controls for a direct vector column, ODKU, ASYNC IVFFLAT, and explicit REINDEX recovery.
## Evidence
`evidence/ivfflat_generated_vector_update.py`
## Related
- Source exploration: #28943
Contributor guide
Research direction
Start with pkg/sql/plan/bind_update.go, especially the classifyIrregularIndexesForUpdate call near line 429 and generated-column recomputation near line 962. Run evidence/ivfflat_generated_vector_update.py and trace how ordinary UPDATE differs from ODKU, REPLACE, and CDC paths. Add regression coverage for single- and multi-row updates, asserting the generated value and synchronous IVFFLAT Top-K results, while retaining the listed controls and REINDEX recovery.
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
- Clearly specified
- Newbie friendliness
- 68/100