matrixorigin / matrixorigin/matrixone

[Bug]: FULLTEXT2 multi-column indexes omit non-NULL content when another indexed column is NULL

Open
#28,986 1 comment 0 reactions 1 assignee Claimed by @VioletQwQ-0 View on GitHub
kind/bug severity/s1
Dominant language
Go
Stars
1.9k
Forks
311
Avg merge
1d 3h
Merged PRs (30d)
768

Description

### Is there an existing issue for the same bug?

- [x] I have checked the existing issues.

### Branch Name

main

### Commit ID

Runtime reproduction: `fc621e3616d229c7a29d0c80e73ed8eb1997459c`.
The same whole-document NULL guards remain in source at `66b1672403e9b9efb1971d00b7ea87572891fe2a` (source inspection, not a new runtime reproduction).

### Other Environment Information

- Host: mo-55, isolated standalone used for FULLTEXT2 validation.
- Parser: `json_value`; multi-column index with SQL NULL in one indexed content column.
- This is independent of host load, warm-cache visibility, and query latency: the initial CREATE fixture returns the wrong PK set without SQL errors.

### Actual Behavior

With a FULLTEXT2 index over `(left_doc, right_doc)`, a row with `left_doc IS NULL` and a searchable non-NULL `right_doc` is omitted. The symmetric case is also omitted.

Observed fixture results:

| Predicate | Expected PKs | Actual PKs |
| --- | --- | --- |
| `onlyrighttoken` | `[2]` | `[]` |
| `onlylefttoken` | `[3]` | `[]` |
| MUST terms from both non-NULL columns of row 1 | `[1]` | `[1]` |

CREATE succeeds and MATCH silently under-returns. Single-column JSON_VALUE dynamic checks passed; they do not cover this boundary.

### Expected Behavior

A SQL NULL content column contributes no tokens. Other non-NULL indexed columns in the same document must still be tokenized and searchable. All-NULL content may yield no searchable terms. This matches the classic FULLTEXT semantics established by #26557 / #26609 and reaffirmed in #26747.

### Steps to Reproduce

Minimal SQL form of the archived fixture (fresh database; experimental FULLTEXT2 enabled):

```sql
SET experimental_fulltext2_index = 1;
CREATE TABLE ft2_null_columns (
id BIGINT PRIMARY KEY,
left_doc JSON,
right_doc JSON
);
INSERT INTO ft2_null_columns VALUES
(1, '{"k":"leftboth"}', '{"k":"rightboth"}'),
(2, NULL, '{"k":"onlyrighttoken"}'),
(3, '{"k":"onlylefttoken"}', NULL);
CREATE FULLTEXT2 INDEX ft2_docs
ON ft2_null_columns(left_doc, right_doc) WITH PARSER json_value;
SELECT id FROM ft2_null_columns
WHERE MATCH(left_doc, right_doc) AGAINST('onlyrighttoken' IN BOOLEAN MODE)
ORDER BY id;
SELECT id FROM ft2_null_columns
WHERE MATCH(left_doc, right_doc) AGAINST('onlylefttoken' IN BOOLEAN MODE)
ORDER BY id;
SELECT id FROM ft2_null_columns
WHERE MATCH(left_doc, right_doc) AGAINST('+leftboth +rightboth' IN BOOLEAN MODE)
ORDER BY id;
```

The first two queries must return 2 and 3 respectively, not empty sets. The SQL above is a minimized transcription; the saved runtime boundary checks are the executed evidence.

### Additional information

**Source-confirmed cause**

- [`fulltext2State.rowTerms`](https://github.com/matrixorigin/matrixone/blob/66b1672403e9b9efb1971d00b7ea87572891fe2a/pkg/sql/colexec/table_function/fulltext2_create.go#L250-L261) scans all indexed text columns and returns `(nil, nil)` on any SQL NULL, before parser dispatch. The caller skips the document when no terms are returned.
- [`Fulltext2SqlWriter.rowText`](https://github.com/matrixorigin/matrixone/blob/66b1672403e9b9efb1971d00b7ea87572891fe2a/pkg/iscp/fulltext2_sqlwriter.go#L334-L349) likewise returns empty text on any NULL content column. CREATE and CDC therefore share the incorrect whole-document rule; this is NOT a CREATE-versus-CDC semantic discrepancy.
- `TestFulltext2WriterRowText` currently expects empty text for a partial-NULL multi-column row; that expectation needs correction.
- The guards precede parser selection, so other supported multi-column FULLTEXT2 parsers are potentially affected. Only the JSON_VALUE CREATE fixture above is runtime-confirmed here. INCLUDE NULLs and JSON literal `null` are not the same boundary.
- An UPDATE to partial-NULL content can be converted to an empty upsert, shadowing the old document including terms from still-non-NULL columns. This is a source-derived risk, not a completed multi-column runtime test.

**Related fix and evidence boundary**

#26609 fixed the classic `fulltext_tokenize` implementation by skipping NULL columns individually. FULLTEXT2 uses separate CREATE and CDC paths introduced with #25904, and still contains the whole-row guards. Do not treat classic FULLTEXT QA PASS as FULLTEXT2 coverage.

Saved evidence on mo-55:
`/home/sunyuze/fulltext2-current-main-static-fc621e3616-20260910/results/incremental-supplement/`
- `json-value-multicolumn-boundary.json`
- `json-dynamic-correction.md`
- `json-dynamic-current-main-20260915-r3/manual-checks.json`

The archived note's claim that CDC already skips individual NULL columns is incorrect; exact-source inspection corrects it as above. Multi-column dynamic stages were not run after the initial CREATE oracle mismatch.

**Proposed repair / acceptance scope**

1. Preserve non-NULL column content consistently in both CREATE and CDC without changing parser, field-boundary, position, or malformed-JSON semantics.
2. Cover left/right/all/no NULL, NULL-to-value and value-to-NULL updates, CREATE/INSERT/UPDATE/DELETE, and MERGE/REBUILD parity with full PK-set assertions and production SQL BVT.
3. Retain correct all-empty upsert shadowing; do not fix this by keeping stale terms.
4. Assess affected parser combinations separately; do not infer their runtime PASS from JSON_VALUE alone.
5. Document repair for existing indexes: cache refresh, restart, or MERGE cannot reconstruct omitted postings; affected indexes need REBUILD after all relevant writers are fixed.

QA required: yes — user-visible missing search results and persisted index/CDC semantics. No implementation or new runtime validation has been performed as part of filing this issue.

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.