matrixorigin / matrixorigin/matrixone
[Bug]: FULLTEXT MATCH is not rewritten beneath a window operator
- Dominant language
- Go
- Stars
- 1.9k
- Forks
- 311
- Avg merge
- 1d 3h
- Merged PRs (30d)
- 768
Description
## Branch and commit
- Branch: `main`
- Commit: `d5c4e8b832ad0275766f6d8f8f0be7ec4c0fb544`
- Environment: local two-CN MatrixOne; each case was executed through both CN SQL ports.
## Problem
A top-level `MATCH() AGAINST()` predicate can be served by a FULLTEXT or FULLTEXT2 index. Adding a normal window function to the same select prevents that rewrite. The planner leaves `fulltext_match(...)` on a base-table scan, and execution returns `ERROR 20105` because a full table scan cannot evaluate MATCH.
This is separate from the previously closed IN-subquery case: the MATCH predicate here is directly in the query block's `WHERE` clause; the only additional relational operator is `ROW_NUMBER()`.
## Reproduction
```sql
SET experimental_fulltext2_index = 1;
CREATE TABLE docs (
id BIGINT PRIMARY KEY,
body TEXT,
v VECF32(3),
FULLTEXT2 ft(body) WITH PARSER ngram
);
INSERT INTO docs VALUES
(1,'alpha','[0,0,0]'),
(2,'beta','[0.1,0,0]'),
(3,'alpha','[0.2,0,0]'),
(4,'alpha','[0.3,0,0]');
ALTER TABLE docs ALTER REINDEX ft FULLTEXT2 FORCE_SYNC;
-- This returns 1,3,4.
SELECT id FROM docs
WHERE MATCH(body) AGAINST('alpha' IN BOOLEAN MODE)
ORDER BY id;
-- This is rejected with ERROR 20105.
SELECT id,
ROW_NUMBER() OVER (ORDER BY l2_distance(v,'[0,0,0]'), id) AS rn
FROM docs
WHERE MATCH(body) AGAINST('alpha' IN BOOLEAN MODE)
ORDER BY id;
```
The same result occurs with classic `FULLTEXT INDEX ft(body) WITH PARSER ngram`, with the corresponding `experimental_fulltext_index` session setting.
## Observed plan
```text
Project
-> Sort
-> Window
-> Table Scan on docs
Filter Cond: fulltext_match('alpha', 3, docs.body)
```
There is no `fulltext2_search`/`fulltext_index_scan` table function, so the unreplaced MATCH reaches execution and returns:
```text
ERROR 20105 (HY000): not supported: MATCH() AGAINST() function cannot be replaced by FULLTEXT INDEX and full table scan with fulltext search is not supported yet.
```
## Scope and controls
- FULLTEXT2: three freshly-created databases; after synchronous reindex, direct MATCH returned `1,3,4` on both CNs; the window query returned the same 20105 on both CNs in all three runs.
- Classic FULLTEXT: three freshly-created databases; direct MATCH returned `1,3,4` on both CNs; the window query returned the same 20105 on both CNs in all three runs.
- Without MATCH, the same `ROW_NUMBER()` expression returned `1:1,2:2,3:3,4:4` on both CNs in all six databases.
## Expected behavior
The MATCH predicate should still be rewritten to the available fulltext index beneath the window operator, so the query returns `(1,1)`, `(3,2)`, and `(4,3)`.
Contributor guide
Assessment
This issue has not been assessed yet.