This is an automated email from the ASF dual-hosted git repository.
morningman pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/doris-website.git
The following commit(s) were added to refs/heads/master by this push:
new b1eae8340ae8 [docs] Explain relevance score filtering across Doris
versions (#4627)
b1eae8340ae8 is described below
commit b1eae8340ae81047d03a3569e482c254a1a1c5bb
Author: Mingyu Chen (Rayner) <[email protected]>
AuthorDate: Fri Nov 13 08:16:43 2026 +0800
[docs] Explain relevance score filtering across Doris versions (#4627)
Doris 4.0.0 through 4.0.3 reject `score()` predicates in `WHERE`, while
4.0.4 and later support direct minimum-score filtering. Document both
query forms in the English and Chinese dev/4.x relevance guides, with an
indexed sample table and input data. Explain supported score predicates,
threshold semantics, and the candidate limit required by the
older-version workaround. Fixes #3264.
The dev guides also describe the current implementation: an outermost
single-table query can compute and filter scores without Top-N, while a
scored SELECT nested in a subquery, view, CTE, or set operation still
needs score ordering and LIMIT. Update the activation conditions and FAQ
to match. The 4.x guides retain the Top-N requirement verified for 4.0.x
and 4.1.0 through 4.1.5.
Validation:
- Compared the score-filter implementation in the 4.0.0 through 4.0.4
release tags and 4.1.5-release, checked the 4.0.4 release notes and
[#60997](https://github.com/apache/doris/pull/60997), and checked
current master’s `BindScoreToOlapScan` and regression examples from
[#69292](https://github.com/apache/doris/pull/69292).
- All four pages pass MDX parsing after legacy HTML-comment removal,
front matter preservation, English/Chinese SQL example comparison, link
checks, locale/version sync checks, and `git diff --check`.
- No live cluster SQL or full website build was run. Existing page
routes, front matter, and navigation are preserved.
---------
Co-authored-by: morningman <[email protected]>
---
docs/table-design/index/inverted-index/scoring.md | 88 ++++++++++++++++++++--
.../table-design/index/inverted-index/scoring.md | 88 ++++++++++++++++++++--
.../table-design/index/inverted-index/scoring.md | 73 ++++++++++++++++--
.../table-design/index/inverted-index/scoring.md | 73 ++++++++++++++++--
4 files changed, 298 insertions(+), 24 deletions(-)
diff --git a/docs/table-design/index/inverted-index/scoring.md
b/docs/table-design/index/inverted-index/scoring.md
index a23850707f06..a4178c12f03e 100644
--- a/docs/table-design/index/inverted-index/scoring.md
+++ b/docs/table-design/index/inverted-index/scoring.md
@@ -48,11 +48,85 @@ After execution, Doris computes the score for each row
using the BM25 algorithm
## Activation Conditions
-For score calculation to take effect, the query must meet all three of the
following conditions:
+In the current development version, the `SELECT` that computes `score()` must
read one Doris internal table and contain at least one `MATCH_*` or `SEARCH`
predicate in its own `WHERE` clause. Joins, subqueries in that `SELECT`, and
lateral views are not supported.
-1. The `SELECT` clause explicitly calls the `score()` function.
-2. The `WHERE` clause contains at least one `MATCH_*` condition.
-3. The query is a Top-N query, and `ORDER BY` is based on the `score()` result.
+For an outermost, non-aggregate `SELECT`, `score()` can appear in the select
list, window functions, and `ORDER BY`; `ORDER BY` and `LIMIT` are optional. In
a query with `GROUP BY`, aggregate functions, or `DISTINCT`, `score()` can only
appear as a minimum-score filter in `WHERE`.
+
+A scored `SELECT` inside a subquery, view, CTE, or set operation still
requires ordering by its score with `LIMIT`. Correlated subqueries cannot call
`score()`. The Top-N form in the quick start remains supported.
+
+### Filter by Relevance Score
+
+First prepare a separate example table with a tokenized inverted index. Run
the following statements with permission to create the database and table, load
data, and query it:
+
+```sql
+CREATE DATABASE IF NOT EXISTS score_filter_example;
+USE score_filter_example;
+
+CREATE TABLE score_filter_demo (
+ id BIGINT,
+ content TEXT,
+ INDEX idx_content (content) USING INVERTED
+ PROPERTIES("parser" = "english", "support_phrase" = "true")
+)
+DUPLICATE KEY(id)
+DISTRIBUTED BY HASH(id) BUCKETS 1
+PROPERTIES ("replication_num" = "1");
+
+INSERT INTO score_filter_demo VALUES
+(1, 'Full text search engine test demo'),
+(2, 'Database performance analysis'),
+(3, 'Search engine examples');
+```
+
+Doris 4.0.4 and later support filtering the minimum score directly in `WHERE`.
The following Top-N form is compatible with Doris 4.0.4 and later:
+
+```sql
+SELECT *, score() AS relevance
+FROM score_filter_demo
+WHERE content MATCH_ANY 'text search test'
+ AND score() > 5
+ORDER BY relevance DESC
+LIMIT 10;
+```
+
+This returns up to 10 matching rows with a score greater than 5, ordered by
score from highest to lowest. If no matching row passes the threshold, the
result is empty. Scores depend on the query and corpus, so 5 is only an example
threshold.
+
+Use `score()` itself in `WHERE` and the select-list alias in `ORDER BY`.
Direct filtering supports one minimum-score predicate, `score() > <threshold>`
or `score() >= <threshold>`, where the threshold is a numeric constant. Use a
numeric literal for compatibility with released 4.x versions. Combine the score
and search predicates using `AND`. Upper bounds (`<`, `<=`), equality (`=`),
multiple score predicates, and score predicates nested inside `OR` are not
supported.
+
+#### Filtering Without Top-N in the Development Version
+
+In the current development version, an outermost single-table `SELECT` can
filter by score without `ORDER BY` or `LIMIT`:
+
+```sql
+SELECT id, content, score() AS relevance
+FROM score_filter_demo
+WHERE content MATCH_ANY 'text search test'
+ AND score() > 5;
+```
+
+This returns all matching rows with a score greater than 5, without imposing
an order or row limit. A scored `SELECT` inside a subquery, view, CTE, or set
operation still needs its own score ordering and `LIMIT`.
+
+#### Compatibility with Doris 4.0.0 to 4.0.3
+
+Doris 4.0.0 through 4.0.3 do not allow `score()` in `WHERE`. On these
versions, first select a bounded set of scored candidates in a subquery, with
the `MATCH_*` predicate, `ORDER BY` on the score alias, and `LIMIT` all inside
that subquery. Then filter the alias in the outer query:
+
+```sql
+SELECT a.*
+FROM (
+ SELECT *, score() AS relevance
+ FROM score_filter_demo
+ WHERE content MATCH_ANY 'text search test'
+ ORDER BY relevance DESC
+ LIMIT 1000
+) AS a
+WHERE a.relevance > 5
+ORDER BY a.relevance DESC
+LIMIT 10;
+```
+
+This returns up to 10 rows with a score greater than 5 from the top 1000
candidates. It returns no rows if none of those candidates passes the
threshold. Choose the candidate limit for your application; this pattern
filters that candidate set rather than returning every matching row above the
threshold.
+
+On Doris 4.0.0 through 4.0.3, keep the inner `LIMIT`: without it, the query no
longer has the required inner Top-N shape, and the optimizer can move the outer
score predicate into the scoring query. An outer `ORDER BY` and `LIMIT` alone
do not supply the required inner Top-N operation.
### Supported Index Types
@@ -142,9 +216,9 @@ Understanding the scoring results helps you use relevance
ranking more accuratel
Check the following in order:
-1. Whether the `score()` function is called in `SELECT`.
-2. Whether the `WHERE` clause contains a `MATCH_*` condition.
-3. Whether `ORDER BY` is based on `score()`.
+1. Whether `score()` is projected in `SELECT` when you want it in the output.
+2. Whether the same `SELECT` reads one internal table and contains a `MATCH_*`
or `SEARCH` predicate in `WHERE`.
+3. For a scored `SELECT` inside a subquery, view, CTE, or set operation,
whether it orders by its score and has its own `LIMIT`.
4. Whether the index is a tokenized inverted index.
**Q2: What is the difference between BM25 and TF-IDF?**
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/table-design/index/inverted-index/scoring.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/table-design/index/inverted-index/scoring.md
index a514a8fc5852..5b5a4ff512ee 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/table-design/index/inverted-index/scoring.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/table-design/index/inverted-index/scoring.md
@@ -48,11 +48,85 @@ LIMIT 10;
## 启用条件
-要使打分计算生效，查询需同时满足以下三个条件：
+在当前开发版本中，计算 `score()` 的 `SELECT` 必须读取一张 Doris 内表，并在自身的 `WHERE` 子句中包含至少一个
`MATCH_*` 或 `SEARCH` 条件。不支持在该 `SELECT` 中使用 JOIN、子查询或 LATERAL VIEW。
-1. `SELECT` 子句中显式调用 `score()` 函数
-2. `WHERE` 子句中至少包含一个 `MATCH_*` 条件
-3. 查询为 Top-N 类型，且 `ORDER BY` 基于 `score()` 结果排序
+对于最外层的非聚合 `SELECT`，`score()` 可以出现在查询列表、窗口函数和 `ORDER BY` 中，`ORDER BY` 和 `LIMIT`
均为可选项。对于包含 `GROUP BY`、聚合函数或 `DISTINCT` 的查询，`score()` 只能作为 `WHERE` 中的最低得分过滤条件。
+
+位于子查询、视图、CTE 或集合运算中的打分 `SELECT` 仍需按自身的得分排序并设置 `LIMIT`。相关子查询不能调用
`score()`。快速上手中的 Top-N 写法仍然适用。
+
+### 按相关性得分过滤
+
+先准备一张带英文分词倒排索引的独立示例表。执行以下语句需要建库、建表、导入和查询权限：
+
+```sql
+CREATE DATABASE IF NOT EXISTS score_filter_example;
+USE score_filter_example;
+
+CREATE TABLE score_filter_demo (
+ id BIGINT,
+ content TEXT,
+ INDEX idx_content (content) USING INVERTED
+ PROPERTIES("parser" = "english", "support_phrase" = "true")
+)
+DUPLICATE KEY(id)
+DISTRIBUTED BY HASH(id) BUCKETS 1
+PROPERTIES ("replication_num" = "1");
+
+INSERT INTO score_filter_demo VALUES
+(1, 'Full text search engine test demo'),
+(2, 'Database performance analysis'),
+(3, 'Search engine examples');
+```
+
+从 Doris 4.0.4 开始，可以直接在 `WHERE` 中按最低得分过滤。以下 Top-N 写法适用于 Doris 4.0.4 及后续版本：
+
+```sql
+SELECT *, score() AS relevance
+FROM score_filter_demo
+WHERE content MATCH_ANY 'text search test'
+ AND score() > 5
+ORDER BY relevance DESC
+LIMIT 10;
+```
+
+此查询按得分从高到低返回最多 10 条得分大于 5 的匹配记录；没有匹配记录达到阈值时，返回空结果。得分取决于查询和数据集，5 只是示例阈值。
+
+在 `WHERE` 中直接使用 `score()`，在 `ORDER BY` 中使用查询列表里的得分别名。直接过滤仅支持一个最低得分条件，即
`score() > <threshold>` 或 `score() >= <threshold>`，阈值需为数值常量。如需兼容已发布的 4.x
版本，请使用数值字面量。请用 `AND` 将得分条件与检索条件组合。不支持得分上限（`<`、`<=`）、等值（`=`）、多个得分条件，或嵌套在 `OR`
中的得分条件。
+
+#### 开发版本中不带 Top-N 的过滤
+
+在当前开发版本中，最外层单表 `SELECT` 可以省略 `ORDER BY` 和 `LIMIT`，直接按得分过滤：
+
+```sql
+SELECT id, content, score() AS relevance
+FROM score_filter_demo
+WHERE content MATCH_ANY 'text search test'
+ AND score() > 5;
+```
+
+此查询返回全部得分大于 5 的匹配记录，不限定顺序和返回条数。位于子查询、视图、CTE 或集合运算中的打分 `SELECT` 仍需在自身内部按得分排序并设置
`LIMIT`。
+
+#### Doris 4.0.0～4.0.3 的兼容写法
+
+Doris 4.0.0～4.0.3 不允许在 `WHERE` 中使用 `score()`。在这些版本中，请先在子查询中获取数量受限的打分候选结果，并将
`MATCH_*` 条件、按得分别名的 `ORDER BY` 和 `LIMIT` 都放在该子查询内，再在外层查询中按别名过滤：
+
+```sql
+SELECT a.*
+FROM (
+ SELECT *, score() AS relevance
+ FROM score_filter_demo
+ WHERE content MATCH_ANY 'text search test'
+ ORDER BY relevance DESC
+ LIMIT 1000
+) AS a
+WHERE a.relevance > 5
+ORDER BY a.relevance DESC
+LIMIT 10;
+```
+
+此查询从得分最高的 1000 条候选记录中返回最多 10 条得分大于 5
的记录；候选记录均未达到阈值时，返回空结果。应按应用需求选择候选数量；该写法过滤的是候选结果集，而不是返回全部得分超过阈值的匹配记录。
+
+在 Doris 4.0.0～4.0.3 中，请保留内层 `LIMIT`：缺少它时，子查询不再具备所需的 Top-N
结构，优化器可能将外层得分条件移入计算得分的查询。仅在外层设置 `ORDER BY` 和 `LIMIT` 不能替代内层 Top-N 操作。
### 支持的索引类型
@@ -142,9 +216,9 @@ VARIANT 子路径继承索引的 `norms` 属性，空间开销为 `行数 × 路
请依次确认：
-1. `SELECT` 中是否调用了 `score()` 函数
-2. `WHERE` 中是否包含 `MATCH_*` 条件
-3. `ORDER BY` 是否基于 `score()` 排序
+1. 需要输出得分时，是否在 `SELECT` 查询列表中调用了 `score()`
+2. 同一个 `SELECT` 是否读取一张内表，并在 `WHERE` 中包含 `MATCH_*` 或 `SEARCH` 条件
+3. 打分 `SELECT` 位于子查询、视图、CTE 或集合运算中时，是否在自身内部按得分排序并设置 `LIMIT`
4. 索引是否为分词型倒排索引
**Q2：BM25 与 TF-IDF 有什么区别？**
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-4.x/table-design/index/inverted-index/scoring.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-4.x/table-design/index/inverted-index/scoring.md
index a514a8fc5852..0fa8536c9e4d 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-4.x/table-design/index/inverted-index/scoring.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-4.x/table-design/index/inverted-index/scoring.md
@@ -48,11 +48,74 @@ LIMIT 10;
## 启用条件
-要使打分计算生效，查询需同时满足以下三个条件：
+使用上述 Top-N 打分写法时，计算 `score()` 的 `SELECT` 需同时满足以下三个条件：
1. `SELECT` 子句中显式调用 `score()` 函数
-2. `WHERE` 子句中至少包含一个 `MATCH_*` 条件
-3. 查询为 Top-N 类型，且 `ORDER BY` 基于 `score()` 结果排序
+2. 自身的 `WHERE` 子句中至少包含一个 `MATCH_*` 或 `SEARCH` 条件
+3. `ORDER BY` 仅按得分别名排序，并设置 `LIMIT`
+
+Doris 4.0.x 和 4.1.0～4.1.5 需要使用此 Top-N 写法。
+
+### 按相关性得分过滤
+
+先准备一张带英文分词倒排索引的独立示例表。执行以下语句需要建库、建表、导入和查询权限：
+
+```sql
+CREATE DATABASE IF NOT EXISTS score_filter_example;
+USE score_filter_example;
+
+CREATE TABLE score_filter_demo (
+ id BIGINT,
+ content TEXT,
+ INDEX idx_content (content) USING INVERTED
+ PROPERTIES("parser" = "english", "support_phrase" = "true")
+)
+DUPLICATE KEY(id)
+DISTRIBUTED BY HASH(id) BUCKETS 1
+PROPERTIES ("replication_num" = "1");
+
+INSERT INTO score_filter_demo VALUES
+(1, 'Full text search engine test demo'),
+(2, 'Database performance analysis'),
+(3, 'Search engine examples');
+```
+
+从 Doris 4.0.4 开始，可以直接在 `WHERE` 中按最低得分过滤。以下 Top-N 写法适用于 Doris 4.0.4 及后续版本：
+
+```sql
+SELECT *, score() AS relevance
+FROM score_filter_demo
+WHERE content MATCH_ANY 'text search test'
+ AND score() > 5
+ORDER BY relevance DESC
+LIMIT 10;
+```
+
+此查询按得分从高到低返回最多 10 条得分大于 5 的匹配记录；没有匹配记录达到阈值时，返回空结果。得分取决于查询和数据集，5 只是示例阈值。
+
+在 `WHERE` 中直接使用 `score()`，在 `ORDER BY` 中使用查询列表里的得分别名。直接过滤仅支持一个最低得分条件，即
`score() > <threshold>` 或 `score() >= <threshold>`，阈值需为数值字面量。请用 `AND`
将其与检索条件组合。不支持得分上限（`<`、`<=`）、等值（`=`）、多个得分条件，或嵌套在 `OR` 中的得分条件。
+
+#### Doris 4.0.0～4.0.3 的兼容写法
+
+Doris 4.0.0～4.0.3 不允许在 `WHERE` 中使用 `score()`。在这些版本中，请先在子查询中获取数量受限的打分候选结果，并将
`MATCH_*` 条件、按得分别名的 `ORDER BY` 和 `LIMIT` 都放在该子查询内，再在外层查询中按别名过滤：
+
+```sql
+SELECT a.*
+FROM (
+ SELECT *, score() AS relevance
+ FROM score_filter_demo
+ WHERE content MATCH_ANY 'text search test'
+ ORDER BY relevance DESC
+ LIMIT 1000
+) AS a
+WHERE a.relevance > 5
+ORDER BY a.relevance DESC
+LIMIT 10;
+```
+
+此查询从得分最高的 1000 条候选记录中返回最多 10 条得分大于 5
的记录；候选记录均未达到阈值时，返回空结果。应按应用需求选择候选数量；该写法过滤的是候选结果集，而不是返回全部得分超过阈值的匹配记录。
+
+在 Doris 4.0.0～4.0.3 中，请保留内层 `LIMIT`：缺少它时，子查询不再具备所需的 Top-N
结构，优化器可能将外层得分条件移入计算得分的查询。仅在外层设置 `ORDER BY` 和 `LIMIT` 不能替代内层 Top-N 操作。
### 支持的索引类型
@@ -143,8 +206,8 @@ VARIANT 子路径继承索引的 `norms` 属性，空间开销为 `行数 × 路
请依次确认：
1. `SELECT` 中是否调用了 `score()` 函数
-2. `WHERE` 中是否包含 `MATCH_*` 条件
-3. `ORDER BY` 是否基于 `score()` 排序
+2. 同一个 `SELECT` 的 `WHERE` 中是否包含 `MATCH_*` 或 `SEARCH` 条件
+3. 使用 Top-N 写法时，`ORDER BY` 是否按得分别名排序，并设置了 `LIMIT`
4. 索引是否为分词型倒排索引
**Q2：BM25 与 TF-IDF 有什么区别？**
diff --git
a/versioned_docs/version-4.x/table-design/index/inverted-index/scoring.md
b/versioned_docs/version-4.x/table-design/index/inverted-index/scoring.md
index a23850707f06..83f7802864cb 100644
--- a/versioned_docs/version-4.x/table-design/index/inverted-index/scoring.md
+++ b/versioned_docs/version-4.x/table-design/index/inverted-index/scoring.md
@@ -48,11 +48,74 @@ After execution, Doris computes the score for each row
using the BM25 algorithm
## Activation Conditions
-For score calculation to take effect, the query must meet all three of the
following conditions:
+To use the Top-N scoring form shown above, the `SELECT` that computes
`score()` must meet all three of the following conditions:
1. The `SELECT` clause explicitly calls the `score()` function.
-2. The `WHERE` clause contains at least one `MATCH_*` condition.
-3. The query is a Top-N query, and `ORDER BY` is based on the `score()` result.
+2. Its `WHERE` clause contains at least one `MATCH_*` or `SEARCH` predicate.
+3. It has a single `ORDER BY` key referencing the score alias, together with
`LIMIT`.
+
+Doris 4.0.x and 4.1.0 through 4.1.5 require this Top-N form.
+
+### Filter by Relevance Score
+
+First prepare a separate example table with a tokenized inverted index. Run
the following statements with permission to create the database and table, load
data, and query it:
+
+```sql
+CREATE DATABASE IF NOT EXISTS score_filter_example;
+USE score_filter_example;
+
+CREATE TABLE score_filter_demo (
+ id BIGINT,
+ content TEXT,
+ INDEX idx_content (content) USING INVERTED
+ PROPERTIES("parser" = "english", "support_phrase" = "true")
+)
+DUPLICATE KEY(id)
+DISTRIBUTED BY HASH(id) BUCKETS 1
+PROPERTIES ("replication_num" = "1");
+
+INSERT INTO score_filter_demo VALUES
+(1, 'Full text search engine test demo'),
+(2, 'Database performance analysis'),
+(3, 'Search engine examples');
+```
+
+Doris 4.0.4 and later support filtering the minimum score directly in `WHERE`.
The following Top-N form is compatible with Doris 4.0.4 and later:
+
+```sql
+SELECT *, score() AS relevance
+FROM score_filter_demo
+WHERE content MATCH_ANY 'text search test'
+ AND score() > 5
+ORDER BY relevance DESC
+LIMIT 10;
+```
+
+This returns up to 10 matching rows with a score greater than 5, ordered by
score from highest to lowest. If no matching row passes the threshold, the
result is empty. Scores depend on the query and corpus, so 5 is only an example
threshold.
+
+Use `score()` itself in `WHERE` and the select-list alias in `ORDER BY`.
Direct filtering supports one minimum-score predicate, `score() > <threshold>`
or `score() >= <threshold>`, where the threshold is a numeric literal. Combine
it with the search predicate using `AND`. Upper bounds (`<`, `<=`), equality
(`=`), multiple score predicates, and score predicates nested inside `OR` are
not supported.
+
+#### Compatibility with Doris 4.0.0 to 4.0.3
+
+Doris 4.0.0 through 4.0.3 do not allow `score()` in `WHERE`. On these
versions, first select a bounded set of scored candidates in a subquery, with
the `MATCH_*` predicate, `ORDER BY` on the score alias, and `LIMIT` all inside
that subquery. Then filter the alias in the outer query:
+
+```sql
+SELECT a.*
+FROM (
+ SELECT *, score() AS relevance
+ FROM score_filter_demo
+ WHERE content MATCH_ANY 'text search test'
+ ORDER BY relevance DESC
+ LIMIT 1000
+) AS a
+WHERE a.relevance > 5
+ORDER BY a.relevance DESC
+LIMIT 10;
+```
+
+This returns up to 10 rows with a score greater than 5 from the top 1000
candidates. It returns no rows if none of those candidates passes the
threshold. Choose the candidate limit for your application; this pattern
filters that candidate set rather than returning every matching row above the
threshold.
+
+On Doris 4.0.0 through 4.0.3, keep the inner `LIMIT`: without it, the query no
longer has the required inner Top-N shape, and the optimizer can move the outer
score predicate into the scoring query. An outer `ORDER BY` and `LIMIT` alone
do not supply the required inner Top-N operation.
### Supported Index Types
@@ -143,8 +206,8 @@ Understanding the scoring results helps you use relevance
ranking more accuratel
Check the following in order:
1. Whether the `score()` function is called in `SELECT`.
-2. Whether the `WHERE` clause contains a `MATCH_*` condition.
-3. Whether `ORDER BY` is based on `score()`.
+2. Whether the same `SELECT` contains a `MATCH_*` or `SEARCH` predicate in
`WHERE`.
+3. For the Top-N form, whether `ORDER BY` references the score alias and the
query has `LIMIT`.
4. Whether the index is a tokenized inverted index.
**Q2: What is the difference between BM25 and TF-IDF?**
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
