matrixorigin / matrixorigin/matrixone
[Bug]: DATA BRANCH FULLTEXT2 covered query returns deleted inherited row
- Dominant language
- Go
- Stars
- 1.9k
- Forks
- 311
- Avg merge
- 1d 3h
- Merged PRs (30d)
- 768
Description
## Environment
- MatrixOne official `main`: `6987fda6c4a13295b8d525706342fc6033094d2e`
- Topology: 2 CN / 1 TN / 1 Log
- Client: MySQL Connector/J 8.4.0 (binary protocol)
## Problem
After a table with a synchronized `FULLTEXT2` index is copied with `DATA BRANCH CREATE TABLE`, deleting an inherited row from the branch does not fully shadow the copied full-text posting.
A covered full-text query that only projects columns stored by the index returns the deleted primary key. Projecting a non-covered base-table column introduces a join and happens to filter the ghost row, so two equivalent predicates return different row sets.
## Reproduction
```sql
SET experimental_fulltext2_index = 1;
CREATE DATABASE ft2_branch_delete;
USE ft2_branch_delete;
CREATE TABLE src (
id BIGINT PRIMARY KEY,
body TEXT NOT NULL,
tag INT NOT NULL
);
INSERT INTO src VALUES
(1, 'alpha inherited row', 10),
(2, 'beta retained row', 20);
CREATE FULLTEXT2 INDEX ft ON src(body) INCLUDE(tag);
ALTER TABLE src ALTER REINDEX ft FULLTEXT2 FORCE_SYNC;
DATA BRANCH CREATE TABLE leaf FROM src;
DELETE FROM leaf WHERE id = 1;
INSERT INTO leaf VALUES (3, 'alpha branch new', 30);
```
Wait until the branch FULLTEXT2 storage table has durably consumed the DML (`MAX(chunk_id)` for `index_id='cdc_tail' AND tag=1` advances). Do not execute `MATCH` while waiting, so the query cache is not pre-warmed.
```sql
SELECT id, body FROM leaf ORDER BY id;
-- 2 | beta retained row
-- 3 | alpha branch new
SELECT id
FROM leaf
WHERE MATCH(body) AGAINST('alpha')
ORDER BY id;
SELECT id, body
FROM leaf
WHERE MATCH(body) AGAINST('alpha')
ORDER BY id;
```
## Actual result
The covered query returns the deleted inherited row:
```text
SELECT id ... => 1, 3
SELECT id, body ... => 3 | alpha branch new
```
`EXPLAIN` shows that the first query uses the FULLTEXT2 covered path without a base-table join, while the second query joins the base table and therefore masks the stale posting.
The branch base table contains only ids `2` and `3`. Rebuilding the branch index with `ALTER TABLE leaf ALTER REINDEX ft FULLTEXT2 FORCE_SYNC` changes the covered result to the correct id `3`.
## Expected result
Both queries should return only id `3`. A DELETE consumed by branch index CDC must create a tombstone newer than the copied base posting, including on the covered-index execution path.
## Reproducibility and isolation
- Reproduced in 3/3 fresh databases.
- In every run, the hidden FULLTEXT2 tail watermark advanced before the first `MATCH` query.
- The source table's equivalent delete/insert sequence returned the correct result.
- A cold service restart also retained the branch ghost posting, so this is persisted index state rather than a warm-cache visibility delay.
- This differs from #27950: that issue covered branch index initialization plus UPDATE/INSERT; the missing operation here is DELETE of a row inherited from the copied FULLTEXT2 base.
- This differs from #28005: no `MATCH` query is issued before durable CDC-tail advancement, and the incorrect result survives a cold restart.
## Relevant implementation gap
`RestoreTable` copies special-index hidden tables and re-registers index CDC from the post-clone timestamp. FULLTEXT2 has plain tombstone/liveness unit coverage, but the copied-base plus branch-delete generation boundary is not covered; the persisted branch tail delete does not shadow the inherited base posting on the covered path.
Contributor guide
Assessment
This issue has not been assessed yet.