matrixorigin / matrixorigin/matrixone

[Bug]: aggregate MATCH predicates in HAVING are not collected as FULLTEXT drivers

Open
#29,065 0 comments 0 reactions 0 assignees View on GitHub
area/optimizer kind/bug needs-triage
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.