matrixorigin / matrixorigin/matrixone
[Bug]: arithmetic wrappers around MATCH score prevent FULLTEXT predicate rewrite
- Dominant language
- Go
- Stars
- 1.9k
- Forks
- 311
- Avg merge
- 1d 3h
- Merged PRs (30d)
- 768
Description
### Branch Name
`main`
### Commit ID
`01d60e1c4ded1b0f3fc1a4ecd75ce54e95e23b90`
### Other Environment Information
- Local MatrixOne cluster: 1 TN / 1 Log / 2 CN.
- CN SQL endpoints: 16001 and 16002.
- Both classic `FULLTEXT` and `FULLTEXT2` were covered.
### Actual Behavior
Arithmetic wrappers around a `MATCH` score prevent the predicate from driving the
fulltext rewrite. The following predicates all return `ERROR 20105`, even though the
same score arithmetic works in the projection and the equivalent bare/CAST/ROUND
predicates use the fulltext index:
```sql
MATCH(body) AGAINST('alpha') + 0 > 0
MATCH(body) AGAINST('alpha') * 1 > 0
MATCH(body) AGAINST('alpha') / 1 > 0
```
The behavior is the same for literal thresholds and prepared threshold parameters,
on both CN endpoints and with both fulltext implementations.
### Expected Behavior
An arithmetic expression that preserves the score and the membership implication
must be rewritten to use the score column produced by the matching fulltext index.
At minimum, the identity forms above should return the same rows as
`MATCH(body) AGAINST('alpha') > 0`.
### Steps to Reproduce
```sql
SET experimental_fulltext_index=1;
SET experimental_fulltext2_index=1;
CREATE DATABASE ft_score_arithmetic;
USE ft_score_arithmetic;
CREATE TABLE classic_t(id INT PRIMARY KEY, body TEXT);
INSERT INTO classic_t VALUES
(1,'alpha alpha common'),
(2,'alpha common'),
(3,'beta common'),
(4,'alpha beta');
CREATE FULLTEXT INDEX ft ON classic_t(body);
CREATE TABLE ft2_t LIKE classic_t;
INSERT INTO ft2_t SELECT * FROM classic_t;
CREATE FULLTEXT2 INDEX ft ON ft2_t(body);
ALTER TABLE ft2_t ALTER REINDEX ft FULLTEXT2 FORCE_SYNC;
-- Control: returns 1,2,4.
SELECT id FROM classic_t
WHERE MATCH(body) AGAINST('alpha') > 0 ORDER BY id;
-- Control: returns 1,2,4. Wrapped score projection is rewritten.
SELECT id, MATCH(body) AGAINST('alpha') + 0 AS score
FROM classic_t
WHERE MATCH(body) AGAINST('alpha')
ORDER BY id;
-- ERROR 20105. +0 does not change the score.
SELECT id FROM classic_t
WHERE MATCH(body) AGAINST('alpha') + 0 > 0 ORDER BY id;
-- The same error occurs for *1 and /1.
SELECT id FROM classic_t
WHERE MATCH(body) AGAINST('alpha') * 1 > 0 ORDER BY id;
SELECT id FROM classic_t
WHERE MATCH(body) AGAINST('alpha') / 1 > 0 ORDER BY id;
-- FULLTEXT2 has the same behavior.
SELECT id FROM ft2_t
WHERE MATCH(body) AGAINST('alpha') + 0 > 0 ORDER BY id;
-- Prepared form also fails for a safe bound of zero.
PREPARE p FROM 'SELECT id FROM ft2_t
WHERE MATCH(body) AGAINST(?) + 0 > ? ORDER BY id';
SET @term='alpha', @bound=0;
EXECUTE p USING @term,@bound;
```
### Scope and code location
`getWrappedFullTextMatches` relies on `collectDrivingFullTextMatches`, whose
`monotoneWrappedFullTextMatch` helper recognizes only bare `fulltext_match`, `round`,
`cast`, `floor`, and `ceil`. Arithmetic identity wrappers are therefore not harvested
as driving MATCH expressions. They remain on the table scan and reach execution as an
unreplaced `fulltext_match`, producing 20105.
The projection rewrite is recursive and already handles the same `+`, `*`, and `/`
expressions, which isolates the gap to predicate-driver discovery rather than score
evaluation.
This is a deterministic small-query planner regression case. It belongs in MOTR/BVT-
sized SQL regression coverage; big-data and chaos coverage are not required.
Contributor guide
Research direction
Start at getWrappedFullTextMatches and collectDrivingFullTextMatches, then inspect monotoneWrappedFullTextMatch and the recursive projection rewrite for the existing wrapper behavior. Add MOTR/BVT-sized SQL regression coverage based on the supplied classic FULLTEXT and FULLTEXT2 queries, including prepared thresholds. Done means +0, *1, and /1 predicates return the same rows as the bare MATCH predicate without error 20105.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- go, sql
- Domain
- databases, search
- Issue type
- Bug
- Difficulty
- 3/5
- Estimated time
- 1-2 days
- Activity status
- Active
- Clarity
- Clearly specified
- Newbie friendliness
- 74/100