matrixorigin / matrixorigin/matrixone
[Bug]: FULLTEXT2 multi-column indexes omit non-NULL content when another indexed column is NULL
- 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
Assessment
This issue has not been assessed yet.