Re: [I] How to Filter Low Relevance Score Results for FULLTEXT Search (score() function) [doris-website]

["boluor (via GitHub)" <gi...@apache.org>]

boluor commented on issue #3264:
URL: https://github.com/apache/doris-website/issues/3264#issuecomment-4525904096

   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:
   
   - \`... WHERE content MATCH_ANY '检索测试' AND score() > 5\` → \`score() function requires WHERE clause with MATCH function, ORDER BY and LIMIT for optimization\`
   - Same error with \`HAVING score() > 5\`
   
   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.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org