matrixorigin / matrixorigin/matrixone

[Bug]: UPDATE of a generated column source leaves synchronous IVFFLAT index data stale

Open
#29,053 1 comment 0 reactions 0 assignees View on GitHub
area/optimizer kind/bug needs-triage
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.