[Bug]: fulltext index select count+MATCH AGAINST failed

[tom-csf]

Is there an existing issue for the same bug?

• [X] I have checked the existing issues.

Branch Name

main

Commit ID

9a8c0975d8b1baa8c43a35d355d2945a146062b3

Other Environment Information

- Hardware parameters:
- OS type:
- Others:

Actual Behavior

Expected Behavior

No response

Steps to Reproduce

1、create table and fulltext index
2、select count(IF(MATCH(r_name) AGAINST('+MIDDLE' IN BOOLEAN MODE),1,NULL)) AS count from region;

Additional information

No response

[tom-csf]

join + MATCH AGAINST failed

[cpegeric]

From the document,

mysql> SELECT -> COUNT(IF(MATCH (title,body) AGAINST ('database' IN NATURAL LANGUAGE MODE), 1, NULL)) -> AS count -> FROM articles;

The second query is a full table scan which is not support for now. MATCH() AGAINST as a function argument seems impossible to run with fulltext index and force to have a full table scan.

The first query does some extra work (sorting the results by relevance) but also can use an index lookup based on the WHERE clause. The index lookup might make the first query faster if the search matches few rows. The second query performs a full table scan, which might be faster than the index lookup if the search term was present in most rows.

[cpegeric]

Quite complicated to work with JOIN, I don't think we can support JOIN for this version as well. Right now, fulltext index only work with project and table scan. MATCH() AGAINST() function inside JOIN is not supported.

[cpegeric]

but the error message is misleading... I will change the error message. The error message is "MATCH() AGAINST() function cannot be replaced by FULLTEXT INDEX and full table scan with fulltext search is not supported yet"