matrixorigin / matrixorigin/matrixone

[Bug]: DATA BRANCH FULLTEXT2 covered query returns deleted inherited row

Open
#28,847 1 comment 0 reactions 1 assignee Claimed by @gouhongshen View on GitHub
kind/bug needs-triage
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.