matrixorigin / matrixorigin/matrixone
[Bug]: aggregate MATCH predicates in HAVING are not collected as FULLTEXT drivers
- 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 LogService / 2 CN.
- CN SQL endpoints: 16001 and 16002.
- Reproduced with both classic `FULLTEXT` and `FULLTEXT2`.
### Actual Behavior
An aggregate over a `MATCH` score cannot itself drive a FULLTEXT lookup when it is
used by `HAVING`. MatrixOne leaves the `MATCH` expression unreplaced and returns
error 20105:
```text
not supported: MATCH() AGAINST() function cannot be replaced by FULLTEXT INDEX
and full table scan with fulltext search is not supported yet.
```
This affects both alias and direct aggregate forms:
```sql
HAVING score > 0
HAVING MAX(MATCH(body) AGAINST('alpha')) > 0
```
The failure is not caused by the index or by aggregate score evaluation:
- direct `HAVING MATCH(body) AGAINST('alpha') > 0` drives the index and works;
- with that direct driver present, `MAX/MIN/AVG/SUM(MATCH(...))` in the aggregate
projection are rewritten and work;
- projecting the score in a CTE and applying `HAVING MAX(score)>0` outside works;
- the same behavior is deterministic on both CN endpoints and both fulltext
implementations.
### Expected Behavior
`HAVING MAX(MATCH(...)) > 0` and its alias form should provide a fulltext driver for
the grouped query, then replace the aggregate's `MATCH` argument with the score
column produced by the fulltext index. For groups keyed by the source row, it must
return the same matching IDs as the equivalent direct `HAVING MATCH(...) > 0` query.
### Steps to Reproduce
```sql
SET experimental_fulltext_index=1;
SET experimental_fulltext2_index=1;
CREATE DATABASE ft_agg_having;
USE ft_agg_having;
CREATE TABLE docs(id INT PRIMARY KEY, body TEXT);
INSERT INTO docs VALUES
(1,'alpha alpha common'),
(2,'alpha common'),
(3,'beta common'),
(4,'alpha beta'),
(5,'gamma'),
(6,'alpha alpha alpha');
CREATE FULLTEXT INDEX ft ON docs(body);
-- ERROR 20105: aggregate alias is the only MATCH-backed HAVING driver.
SELECT id,body,MAX(MATCH(body) AGAINST('alpha')) AS score
FROM docs
GROUP BY id,body
HAVING score > 0
ORDER BY id;
-- ERROR 20105: direct aggregate form has the same gap.
SELECT id,body,MAX(MATCH(body) AGAINST('alpha')) AS score
FROM docs
GROUP BY id,body
HAVING MAX(MATCH(body) AGAINST('alpha')) > 0
ORDER BY id;
-- Control: returns IDs 1,2,4,6 and also rewrites MAX(MATCH(...)).
SELECT id,body,MAX(MATCH(body) AGAINST('alpha')) AS score
FROM docs
GROUP BY id,body
HAVING MATCH(body) AGAINST('alpha') > 0
ORDER BY id;
-- Control/workaround: returns the maximum matching score.
WITH scored AS (
SELECT MATCH(body) AGAINST('alpha') AS score FROM docs
)
SELECT MAX(score) FROM scored HAVING MAX(score) > 0;
```
Creating an equivalent `FULLTEXT2` index and forcing its initial build produces the
same two 20105 errors and the same successful controls.
### Scope
The fix for #28681 rewrites aggregate expressions after another expression has
already established a fulltext index stream. This case is earlier in the planning
path: an aggregate `MATCH` referenced through `HAVING` is not collected as the
driver, so that rewrite never receives a served score mapping.
This is a deterministic small-query optimizer case. It belongs in planner UT and a
small SQL regression suite; big-data, chaos, and stability coverage are not needed.
Contributor guide
Research direction
Start with the planner UT and small SQL regression suite described in the issue, then trace how the #28681 aggregate rewrite obtains its fulltext score mapping. Reproduce the alias and direct aggregate HAVING queries and compare them with the working direct MATCH control; done when both aggregate forms provide a fulltext driver and return IDs 1,2,4,6 without error 20105.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- go, sql
- Domain
- databases, search
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Active
- Clarity
- Mostly clear
- Newbie friendliness
- 52/100