matrixorigin / matrixorigin/matrixone

[Bug]: LOAD DATA with synchronous FULLTEXT maintenance materializes the input in HashBuild

Open
#26,173 0 comments 0 reactions 1 assignee Claimed by @XuPeng-SH View on GitHub
kind/bug needs-triage
Dominant language
Go
Stars
1.9k
Forks
311
Avg merge
1d 3h
Merged PRs (30d)
768

Description

## Summary

`LOAD DATA` into a table with a synchronous FULLTEXT index creates a `hash build -> hash join -> delete` maintenance branch, even when the destination table and its fulltext hidden table are empty. The hash build materializes incoming batches and can grow with the entire load.

This was observed as a CN OOM while importing a 10M-row / 768-dimension CSV (~107.7 GB) in nightly regression. The failure happens before the later IVFFLAT `CREATE INDEX`; it is not an IVF build OOM.

## Confirmed plan-level reproduction

On a fresh local MatrixOne instance, I compared identical `LOAD DATA ... PARALLEL 'true'` statements; the only schema difference was `FULLTEXT idx_content(content) WITH PARSER ngram`.

Schema with the fulltext index:

```sql
CREATE TABLE with_ft (
id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
file_id BIGINT NOT NULL,
content TEXT,
embedding VECF32(3),
page_num INT NOT NULL DEFAULT 0,
meta JSON,
KEY idx_file(file_id),
FULLTEXT idx_content(content) WITH PARSER ngram
);
```

`EXPLAIN PHYPLAN LOAD DATA ... INTO TABLE with_ft ...` contains:

```text
DataSource: [doc_id __mo_fake_pk_col __mo_rowid]
Pipeline: delete
hash join
tablescan
PreScopes:
Pipeline: hash build
projection
merge
```

For the same table without only the `FULLTEXT` declaration (while retaining `KEY idx_file(file_id)`), the physical plan contains no `hash build` and no `hash join`.

## Why this is problematic

The fulltext path must tokenize and insert the new entries, but the generated stale-entry deletion branch is unnecessary for a plain append into a newly created/empty table. It still builds a hash join over the materialized input image. `HashBuild.build` deep-copies every build batch into `Batches`; on the broadcast path this does not use the current hash-build spill path. Memory therefore grows with the import rather than remaining bounded.

The nightly reproducer used a fresh database/table, so there are no old fulltext entries to delete.

## Expected behavior

For plain append-only `LOAD DATA` with no conflict-resolution semantics, do not build stale-fulltext-entry delete maintenance.

If a delete-maintenance path is required for other DML forms (for example REPLACE/ODKU), it should be enabled only when old rows can actually be replaced, and its build side must remain bounded/spillable for large inputs.

## Suggested acceptance criteria

- `EXPLAIN PHYPLAN LOAD DATA` into a fresh fulltext-indexed table has no stale-entry `hash build -> hash join -> delete` branch.
- Conflict-resolving DML keeps correct removal of old fulltext entries.
- Add a regression test for the physical-plan shape and a large/batched memory-safety test for fulltext-indexed LOAD/INSERT.
- Re-run the 10M wiki S3 import; CN memory remains bounded and the load completes.

## Related evidence

The nightly case creates `KEY idx_file(file_id)` and `FULLTEXT idx_content(content)` before the S3 load, then creates IVFFLAT only after the import. Subsequent recall SQL uses `embedding`, `file_id`, and `id`, not `content`; the FULLTEXT index made the unrelated memory path visible but the underlying issue affects any legitimate large fulltext-indexed ingestion.

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.