Repository navigation
How to Filter Low Relevance Score Results for FULLTEXT Search (score() function) #3264
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:
- Does Doris support filtering low relevance score results for FULLTEXT search?
- If yes, what is the correct SQL syntax (based on the official demo below)?
- 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
Activity
boluor commentedon May 23, 2026
Doris does support filtering on `score()`, but the function is only allowed in the SELECT projection — the optimizer requires WHERE+MATCH, ORDER BY and LIMIT to be present in the same query, so you can't put `score()` directly in WHERE or HAVING. Your subquery workaround is the supported pattern; I confirmed it on a 4.0+ cluster:
```sql
SELECT a.* FROM (
SELECT *, score() AS relevance
FROM search_demo
WHERE content MATCH_ANY '检索测试'
ORDER BY relevance DESC LIMIT 1000
) a
WHERE a.relevance > 5
ORDER BY a.relevance DESC
LIMIT 10;
```
This runs cleanly and avoids the rewrite cost of a temporary table. The outer LIMIT and ORDER BY can be added if you want top-N. For comparison:
The `table-design/index/inverted-index/scoring.md` page could probably be made clearer about this; will look into adding a "filter by score" example in a follow-up.
[docs] Explain relevance score filtering across Doris versions (apach…