apache / apache/doris-website

How to Filter Low Relevance Score Results for FULLTEXT Search (score() function)

Open
#3,264 1 comment 0 reactions 0 assignees View on GitHub
Dominant language
MDX
Stars
133
Forks
464
Avg merge
1d 1h
Merged PRs (30d)
50

Description

I am using Doris' FULLTEXT search feature with `MATCH_ANY`, and follow the official example to retrieve relevance scores via `score()` function. The basic search works fine, but I cannot filter out records with low relevance scores—any attempt triggers a syntax error about `score()` being restricted to SELECT clause only.

I want to ask:
1. Does Doris support filtering low relevance score results for FULLTEXT search?
2. If yes, what is the correct SQL syntax (based on the official demo below)?
3. If not, is there an alternative approach (without heavy performance cost like temporary tables)?

# Environment Information
- Doris Version: [Output of `SELECT VERSION();` 4.0.1]

I tried to filter records with relevance > 5 using the following SQL (ensuring score() is only in SELECT clause of subquery):
```
SELECT a.* FROM (
SELECT *, score() AS relevance
FROM search_demo
WHERE content MATCH_ANY '检索测试'
) a WHERE a.relevance > 5 ORDER BY a.relevance DESC LIMIT 10;
```
Exact Error Message Encountered

```
SQL 错误 [1105] [HY000]: errCode = 2, detailMessage = score() function can only be used in SELECT clause, not in WHERE clause
```

Contributor guide

No contributing guide indexed for this repository

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.