This is an automated email from the ASF dual-hosted git repository.
eldenmoon 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 97a17b9719d [doc](search) Explain two-valued SEARCH and VARIANT null
handling (#4419)
97a17b9719d is described below
commit 97a17b9719dd55b7810a5d24f7b7630a0a52e7e4
Author: lihangyu <[email protected]>
AuthorDate: Wed Oct 21 10:54:08 2026 +0800
[doc](search) Explain two-valued SEARCH and VARIANT null handling (#4419)
SEARCH documentation currently describes three-valued NULL handling,
while apache/doris#69347 makes SEARCH a non-nullable match predicate.
Document its two-valued rules with matching English and Chinese
examples: NULL and proven-absent paths do not match ordinary terms, and
both internal NOT and SQL NOT SEARCH include those rows. Ordinary SQL
comparisons and MATCH retain three-valued logic.
The VARIANT example distinguishes matching values, nonmatching values,
JSON null members, missing paths, and an entirely SQL NULL column.
Explain that stored object null members are removed, and that a required
unavailable index raises an error rather than producing zero matches.
Also correct the contradictory Lucene pure-NOT bullet and preserve the
existing NULL-section anchors.
Scope: dev and 4.x, in English and Chinese. Mark the two-valued matching
rules, NULL/NOT examples, and VARIANT index-error behavior as applying
since Doris 4.2. This follows the planned inclusion of
apache/doris#69347 in 4.2 and does not extend the new contract to
4.0/4.1. Japanese and Korean translations are managed separately by the
repository translation workflow.
Validation: all four pages compile as Markdown, frontmatter parses, and
example SQL/output blocks match across languages and versions.
Repository docs governance reports no errors: existing SEO warnings
remain and current/4.x language synchronization passes. `git diff
--check` passes. SQL expectations were checked against the PR regression
contracts and the VARIANT storage reference; no cluster execution or
full Docusaurus build was performed.
## Versions
- [x] dev
- [x] 4.x
- [ ] 3.x
- [ ] 2.1 or older (not covered by version/language sync gate)
## Languages
- [x] Chinese
- [x] English
## Docs Checklist
- [x] Checked by AI
- [ ] Test Cases Built
- [x] Updated required version and language counterparts, or explained
why not
- [x] If only one language changed, confirmed whether source/translation
counterparts need sync
---
.../index/inverted-index/search-function.md | 137 +++++++++++++++++++--
.../index/inverted-index/search-function.md | 137 +++++++++++++++++++--
.../index/inverted-index/search-function.md | 137 +++++++++++++++++++--
.../index/inverted-index/search-function.md | 137 +++++++++++++++++++--
4 files changed, 508 insertions(+), 40 deletions(-)
diff --git a/docs/table-design/index/inverted-index/search-function.md
b/docs/table-design/index/inverted-index/search-function.md
index 5c1d38fccaa..65506e70d99 100644
--- a/docs/table-design/index/inverted-index/search-function.md
+++ b/docs/table-design/index/inverted-index/search-function.md
@@ -42,7 +42,7 @@ SEARCH('<search_expression>', '<default_field>',
'<default_operator>')
**Usage notes:**
- **Position**: Used in a `WHERE` clause as a predicate participating in row
filtering
-- **Return type**: BOOLEAN (TRUE on a match)
+- **Return type**: Non-nullable BOOLEAN (TRUE on a match, FALSE otherwise)
When `default_field` is provided, Doris automatically expands bare terms or
functions to that field. For example:
@@ -78,17 +78,134 @@ WHERE SEARCH('apple banana',
'{"default_field":"title","default_operator":"and","mode":"lucene"}');
```
-### NULL and Three-Valued Logic
+### NULL and Two-Valued Matching {#null-and-three-valued-logic}
-`SEARCH()` follows SQL three-valued logic:
+**Version:** The two-valued matching rules, NULL/NOT examples, and VARIANT
index-error behavior below apply starting with Doris 4.2.
-- When all column values participating in the match are NULL, the result is
UNKNOWN (filtered out in `WHERE`)
-- When combined with other subexpressions, the result follows boolean
short-circuit rules. For example:
- - `TRUE OR NULL = TRUE`
- - `FALSE OR NULL = NULL`
- - `NOT NULL = NULL`
+`SEARCH` returns a non-nullable BOOLEAN: TRUE for a match and FALSE for a
nonmatch. An ordinary term does not match a NULL value or a VARIANT path proven
absent from storage. SEARCH does not propagate UNKNOWN.
-This behavior is consistent with text search operators.
+In standard mode, `AND` intersects match sets, `OR` unions them, and `NOT`
takes the complement relative to the rows visible to the query. Thus, both
`SEARCH('NOT a:hit')` and `NOT SEARCH('a:hit')` include NULL rows. Lucene mode
uses the same NULL/nonmatch rule, while retaining its own Boolean parsing rules
described below. Computing `score()` or using the query cache does not change
the complete match set; sorting and LIMIT can change which rows are returned.
+
+Ordinary SQL comparisons and `MATCH` operators retain SQL three-valued logic.
For a NULL input, `a != 'hit'` and `NOT (a MATCH_ANY 'hit')` are UNKNOWN and
are filtered out by `WHERE`. An outer SQL expression can still produce UNKNOWN
from its other nullable operands.
+
+#### Example: NULL and NOT
+
+Run the examples in a database where you have CREATE, INSERT, and SELECT
privileges. The single bucket and replica below are for a small demonstration
table.
+
+```sql
+CREATE TABLE search_null_demo (
+ id INT,
+ a VARCHAR(16) NULL,
+ INDEX idx_a(a) USING INVERTED PROPERTIES("parser"="unicode")
+)
+DUPLICATE KEY(id)
+DISTRIBUTED BY HASH(id) BUCKETS 1
+PROPERTIES("replication_num"="1");
+
+INSERT INTO search_null_demo VALUES (1, 'hit'), (2, 'miss'), (3, NULL);
+```
+
+```sql
+SELECT id FROM search_null_demo WHERE SEARCH('a:hit') ORDER BY id;
+```
+
+```text
+id
+1
+```
+
+```sql
+SELECT id FROM search_null_demo WHERE SEARCH('NOT a:hit') ORDER BY id;
+```
+
+```text
+id
+2
+3
+```
+
+```sql
+SELECT id FROM search_null_demo WHERE NOT SEARCH('a:hit') ORDER BY id;
+```
+
+```text
+id
+2
+3
+```
+
+To exclude NULL rows as well, add an explicit `IS NOT NULL` filter:
+
+```sql
+SELECT id FROM search_null_demo
+WHERE a IS NOT NULL AND NOT SEARCH('a:hit') ORDER BY id;
+```
+
+```text
+id
+2
+```
+
+For these three rows, the SQL condition `a != 'hit'` and the text search
condition `NOT (a MATCH_ANY 'hit')` also return only id 2.
+
+#### VARIANT: NULL, Missing Paths, and Unavailable Indexes
+
+An ordinary VARIANT term such as `v.a:hit` follows the same rule:
+
+| Value written to `v` | `v.a:hit` | `NOT v.a:hit` |
+| --- | --- | --- |
+| `{"a":"hit"}` | TRUE | FALSE |
+| `{"a":"miss"}` | FALSE | TRUE |
+| `{"a":null}` | FALSE | TRUE |
+| `{}` (path `a` is missing) | FALSE | TRUE |
+| SQL NULL (the entire VARIANT column is NULL) | FALSE | TRUE |
+
+For stored objects outside arrays, JSON `null` members are removed, so reading
that path returns SQL NULL, just like a missing member. This differs from a
JSON `null` computed within an expression; see [VARIANT NULL
semantics](../../../sql-manual/basic-element/sql-data-types/semi-structured/VARIANT.md#null-semantics).
+
+Use `parse_to_variant` to insert JSON objects. The following table disables
doc mode so the example uses materialized subpaths with executable indexes.
+
+```sql
+CREATE TABLE search_variant_null_demo (
+ id INT,
+ v VARIANT<PROPERTIES("variant_enable_doc_mode"="false")>,
+ INDEX idx_v(v) USING INVERTED PROPERTIES("parser"="unicode")
+)
+DUPLICATE KEY(id)
+DISTRIBUTED BY HASH(id) BUCKETS 1
+PROPERTIES("replication_num"="1");
+
+INSERT INTO search_variant_null_demo VALUES
+ (1, parse_to_variant('{"a":"hit"}')),
+ (2, parse_to_variant('{"a":"miss"}')),
+ (3, parse_to_variant('{"a":null}')),
+ (4, parse_to_variant('{}')),
+ (5, NULL);
+```
+
+```sql
+SELECT id FROM search_variant_null_demo WHERE SEARCH('v.a:hit') ORDER BY id;
+```
+
+```text
+id
+1
+```
+
+```sql
+SELECT id FROM search_variant_null_demo WHERE SEARCH('NOT v.a:hit') ORDER BY
id;
+```
+
+```text
+id
+2
+3
+4
+5
+```
+
+`NOT SEARCH('v.a:hit')` returns the same ids. If a path is absent from every
row, `SEARCH('v.absent:hit')` returns no rows and `SEARCH('NOT v.absent:hit')`
returns all five rows. To retain only rows with a non-NULL value at the target
path, add `v['a'] IS NOT NULL` outside SEARCH.
+
+**A missing path and an unavailable index are different states.** If the path
contains data but its required inverted index cannot be executed, SEARCH
reports `SEARCH index unavailable for Variant field 'v.a'`, including for
negated queries. Examples include historical rowsets whose index has not been
built after CREATE INDEX, or a path retained only in doc storage without an
executable leaf index. SEARCH does not treat these states as an empty match set
or automatically fall back to a s [...]
## Using SEARCH by Scenario
@@ -184,7 +301,7 @@ Lucene mode emulates the query_string behavior of
Elasticsearch/Lucene. Boolean
- AND/OR/NOT are modifiers that affect adjacent terms
- Operator precedence is left to right
- MUST/SHOULD/MUST_NOT are used internally (similar to Lucene's Occur enum)
-- A pure NOT query returns an empty result (a positive clause is required)
+- A pure NOT query adds a match-all condition and excludes the matching rows
**Enable Lucene mode:**
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/table-design/index/inverted-index/search-function.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/table-design/index/inverted-index/search-function.md
index 03eb87a354e..5bdd192ba28 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/table-design/index/inverted-index/search-function.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/table-design/index/inverted-index/search-function.md
@@ -42,7 +42,7 @@ SEARCH('<search_expression>', '<default_field>',
'<default_operator>')
**使用要点：**
- **位置**：用于 `WHERE` 子句，作为谓词参与行过滤
-- **返回类型**：BOOLEAN（匹配为 TRUE）
+- **返回类型**：不可空的 BOOLEAN（匹配为 TRUE，否则为 FALSE）
提供 `default_field` 后，Doris 会把裸词项或函数自动扩展到该字段。例如：
@@ -78,17 +78,134 @@ WHERE SEARCH('apple banana',
'{"default_field":"title","default_operator":"and","mode":"lucene"}');
```
-### NULL 与三值逻辑
+### NULL 与二值匹配 {#null-与三值逻辑}
-`SEARCH()` 遵循 SQL 三值逻辑：
+**版本说明：** 以下二值匹配规则、NULL/NOT 示例和 VARIANT 索引报错行为自 Doris 4.2 起适用。
-- 当所有参与匹配的列值均为 NULL 时，结果为 UNKNOWN（在 `WHERE` 中被过滤）
-- 与其他子表达式组合时，按布尔短路原则返回结果，例如：
- - `TRUE OR NULL = TRUE`
- - `FALSE OR NULL = NULL`
- - `NOT NULL = NULL`
+`SEARCH` 返回不可空的 BOOLEAN：匹配为 TRUE，不匹配为 FALSE。普通词项不匹配 NULL 值或经存储元数据确认缺失的 VARIANT
路径。SEARCH 不传播 UNKNOWN。
-该行为与文本检索算子保持一致。
+在 standard 模式下，`AND` 对匹配集合取交集，`OR` 取并集，`NOT` 相对于当前查询可见的行取补集。因此，`SEARCH('NOT
a:hit')` 和 `NOT SEARCH('a:hit')` 都会保留 NULL 行。Lucene 模式也将 NULL
视为不匹配，但仍遵循下文所述的布尔解析规则。计算 `score()` 或使用查询缓存不会改变完整命中集合；排序和 LIMIT 可能改变最终返回的行。
+
+普通 SQL 比较和 `MATCH` 算子仍遵循 SQL 三值逻辑。输入为 NULL 时，`a != 'hit'` 和 `NOT (a MATCH_ANY
'hit')` 的结果为 UNKNOWN，会被 `WHERE` 过滤。外层 SQL 表达式中的其他可空操作数仍可能使整个表达式得到 UNKNOWN。
+
+#### 示例：NULL 与 NOT
+
+请在具有 CREATE、INSERT 和 SELECT 权限的数据库中执行。以下单桶、单副本配置用于小规模演示。
+
+```sql
+CREATE TABLE search_null_demo (
+ id INT,
+ a VARCHAR(16) NULL,
+ INDEX idx_a(a) USING INVERTED PROPERTIES("parser"="unicode")
+)
+DUPLICATE KEY(id)
+DISTRIBUTED BY HASH(id) BUCKETS 1
+PROPERTIES("replication_num"="1");
+
+INSERT INTO search_null_demo VALUES (1, 'hit'), (2, 'miss'), (3, NULL);
+```
+
+```sql
+SELECT id FROM search_null_demo WHERE SEARCH('a:hit') ORDER BY id;
+```
+
+```text
+id
+1
+```
+
+```sql
+SELECT id FROM search_null_demo WHERE SEARCH('NOT a:hit') ORDER BY id;
+```
+
+```text
+id
+2
+3
+```
+
+```sql
+SELECT id FROM search_null_demo WHERE NOT SEARCH('a:hit') ORDER BY id;
+```
+
+```text
+id
+2
+3
+```
+
+如果还要排除 NULL 行，请显式添加 `IS NOT NULL`：
+
+```sql
+SELECT id FROM search_null_demo
+WHERE a IS NOT NULL AND NOT SEARCH('a:hit') ORDER BY id;
+```
+
+```text
+id
+2
+```
+
+对于这三行数据，SQL 条件 `a != 'hit'` 和文本检索条件 `NOT (a MATCH_ANY 'hit')` 也都只返回 id 2。
+
+#### VARIANT：NULL、路径缺失与索引不可用
+
+普通 VARIANT 词项（如 `v.a:hit`）遵循相同规则：
+
+| 写入 `v` 的值 | `v.a:hit` | `NOT v.a:hit` |
+| --- | --- | --- |
+| `{"a":"hit"}` | TRUE | FALSE |
+| `{"a":"miss"}` | FALSE | TRUE |
+| `{"a":null}` | FALSE | TRUE |
+| `{}`（缺少路径 `a`） | FALSE | TRUE |
+| SQL NULL（整个 VARIANT 列为空） | FALSE | TRUE |
+
+对于写入表中的数组外对象，值为 JSON `null` 的成员会被移除，读取该路径返回 SQL NULL，与成员缺失相同。这与查询表达式中计算出的 JSON
`null` 值不同，参见 [VARIANT 的 NULL
语义](../../../sql-manual/basic-element/sql-data-types/semi-structured/VARIANT.md#null-semantics)。
+
+使用 `parse_to_variant` 写入 JSON 对象。以下建表关闭 doc 模式，使示例使用已物化且具有可执行索引的子路径。
+
+```sql
+CREATE TABLE search_variant_null_demo (
+ id INT,
+ v VARIANT<PROPERTIES("variant_enable_doc_mode"="false")>,
+ INDEX idx_v(v) USING INVERTED PROPERTIES("parser"="unicode")
+)
+DUPLICATE KEY(id)
+DISTRIBUTED BY HASH(id) BUCKETS 1
+PROPERTIES("replication_num"="1");
+
+INSERT INTO search_variant_null_demo VALUES
+ (1, parse_to_variant('{"a":"hit"}')),
+ (2, parse_to_variant('{"a":"miss"}')),
+ (3, parse_to_variant('{"a":null}')),
+ (4, parse_to_variant('{}')),
+ (5, NULL);
+```
+
+```sql
+SELECT id FROM search_variant_null_demo WHERE SEARCH('v.a:hit') ORDER BY id;
+```
+
+```text
+id
+1
+```
+
+```sql
+SELECT id FROM search_variant_null_demo WHERE SEARCH('NOT v.a:hit') ORDER BY
id;
+```
+
+```text
+id
+2
+3
+4
+5
+```
+
+`NOT SEARCH('v.a:hit')` 返回相同的 id。如果某条路径在所有行中都不存在，`SEARCH('v.absent:hit')`
不返回任何行，`SEARCH('NOT v.absent:hit')` 返回全部五行。如果只想保留目标路径有非 NULL 值的行，可在 SEARCH 外添加
`v['a'] IS NOT NULL`。
+
+**路径缺失与索引不可用是两种状态。** 如果路径有数据，但所需倒排索引无法执行，SEARCH 会报错 `SEARCH index unavailable
for Variant field 'v.a'`，取反查询也一样。例如：CREATE INDEX 后历史 rowset 尚未构建索引，或路径仅保存在 doc
存储中且没有可执行的叶子索引。SEARCH 不会将这些情况当作零命中，也不会自动退回扫描。应确保所有待查询数据均具有所需路径的可执行索引，或改用普通 SQL
提取路径后查询。
## 按场景使用 SEARCH
@@ -184,7 +301,7 @@ Lucene 模式模拟 Elasticsearch/Lucene 的 query_string 行为，布尔操作
- AND/OR/NOT 是影响相邻词项的修饰符
- 操作符优先级从左到右
- 内部使用 MUST/SHOULD/MUST_NOT（类似 Lucene 的 Occur 枚举）
-- 纯 NOT 查询返回空结果（需要正向子句）
+- 纯 NOT 查询会补充全匹配条件，再排除匹配行
**启用 Lucene 模式：**
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-4.x/table-design/index/inverted-index/search-function.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-4.x/table-design/index/inverted-index/search-function.md
index 03eb87a354e..4ff63a2164a 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-4.x/table-design/index/inverted-index/search-function.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-4.x/table-design/index/inverted-index/search-function.md
@@ -42,7 +42,7 @@ SEARCH('<search_expression>', '<default_field>',
'<default_operator>')
**使用要点：**
- **位置**：用于 `WHERE` 子句，作为谓词参与行过滤
-- **返回类型**：BOOLEAN（匹配为 TRUE）
+- **返回类型**：不可空的 BOOLEAN（匹配为 TRUE，否则为 FALSE；自 Doris 4.2 起）
提供 `default_field` 后，Doris 会把裸词项或函数自动扩展到该字段。例如：
@@ -78,17 +78,134 @@ WHERE SEARCH('apple banana',
'{"default_field":"title","default_operator":"and","mode":"lucene"}');
```
-### NULL 与三值逻辑
+### NULL 与二值匹配 {#null-与三值逻辑}
-`SEARCH()` 遵循 SQL 三值逻辑：
+**版本说明：** 以下二值匹配规则、NULL/NOT 示例和 VARIANT 索引报错行为自 Doris 4.2 起适用。
-- 当所有参与匹配的列值均为 NULL 时，结果为 UNKNOWN（在 `WHERE` 中被过滤）
-- 与其他子表达式组合时，按布尔短路原则返回结果，例如：
- - `TRUE OR NULL = TRUE`
- - `FALSE OR NULL = NULL`
- - `NOT NULL = NULL`
+`SEARCH` 返回不可空的 BOOLEAN：匹配为 TRUE，不匹配为 FALSE。普通词项不匹配 NULL 值或经存储元数据确认缺失的 VARIANT
路径。SEARCH 不传播 UNKNOWN。
-该行为与文本检索算子保持一致。
+在 standard 模式下，`AND` 对匹配集合取交集，`OR` 取并集，`NOT` 相对于当前查询可见的行取补集。因此，`SEARCH('NOT
a:hit')` 和 `NOT SEARCH('a:hit')` 都会保留 NULL 行。Lucene 模式也将 NULL
视为不匹配，但仍遵循下文所述的布尔解析规则。计算 `score()` 或使用查询缓存不会改变完整命中集合；排序和 LIMIT 可能改变最终返回的行。
+
+普通 SQL 比较和 `MATCH` 算子仍遵循 SQL 三值逻辑。输入为 NULL 时，`a != 'hit'` 和 `NOT (a MATCH_ANY
'hit')` 的结果为 UNKNOWN，会被 `WHERE` 过滤。外层 SQL 表达式中的其他可空操作数仍可能使整个表达式得到 UNKNOWN。
+
+#### 示例：NULL 与 NOT
+
+请在具有 CREATE、INSERT 和 SELECT 权限的数据库中执行。以下单桶、单副本配置用于小规模演示。
+
+```sql
+CREATE TABLE search_null_demo (
+ id INT,
+ a VARCHAR(16) NULL,
+ INDEX idx_a(a) USING INVERTED PROPERTIES("parser"="unicode")
+)
+DUPLICATE KEY(id)
+DISTRIBUTED BY HASH(id) BUCKETS 1
+PROPERTIES("replication_num"="1");
+
+INSERT INTO search_null_demo VALUES (1, 'hit'), (2, 'miss'), (3, NULL);
+```
+
+```sql
+SELECT id FROM search_null_demo WHERE SEARCH('a:hit') ORDER BY id;
+```
+
+```text
+id
+1
+```
+
+```sql
+SELECT id FROM search_null_demo WHERE SEARCH('NOT a:hit') ORDER BY id;
+```
+
+```text
+id
+2
+3
+```
+
+```sql
+SELECT id FROM search_null_demo WHERE NOT SEARCH('a:hit') ORDER BY id;
+```
+
+```text
+id
+2
+3
+```
+
+如果还要排除 NULL 行，请显式添加 `IS NOT NULL`：
+
+```sql
+SELECT id FROM search_null_demo
+WHERE a IS NOT NULL AND NOT SEARCH('a:hit') ORDER BY id;
+```
+
+```text
+id
+2
+```
+
+对于这三行数据，SQL 条件 `a != 'hit'` 和文本检索条件 `NOT (a MATCH_ANY 'hit')` 也都只返回 id 2。
+
+#### VARIANT：NULL、路径缺失与索引不可用
+
+普通 VARIANT 词项（如 `v.a:hit`）遵循相同规则：
+
+| 写入 `v` 的值 | `v.a:hit` | `NOT v.a:hit` |
+| --- | --- | --- |
+| `{"a":"hit"}` | TRUE | FALSE |
+| `{"a":"miss"}` | FALSE | TRUE |
+| `{"a":null}` | FALSE | TRUE |
+| `{}`（缺少路径 `a`） | FALSE | TRUE |
+| SQL NULL（整个 VARIANT 列为空） | FALSE | TRUE |
+
+对于写入表中的数组外对象，值为 JSON `null` 的成员会被移除，读取该路径返回 SQL NULL，与成员缺失相同。这与查询表达式中计算出的 JSON
`null` 值不同，参见 [VARIANT
文档](../../../sql-manual/basic-element/sql-data-types/semi-structured/VARIANT.md)。
+
+使用 `parse_to_variant` 写入 JSON 对象。以下建表关闭 doc 模式，使示例使用已物化且具有可执行索引的子路径。
+
+```sql
+CREATE TABLE search_variant_null_demo (
+ id INT,
+ v VARIANT<PROPERTIES("variant_enable_doc_mode"="false")>,
+ INDEX idx_v(v) USING INVERTED PROPERTIES("parser"="unicode")
+)
+DUPLICATE KEY(id)
+DISTRIBUTED BY HASH(id) BUCKETS 1
+PROPERTIES("replication_num"="1");
+
+INSERT INTO search_variant_null_demo VALUES
+ (1, parse_to_variant('{"a":"hit"}')),
+ (2, parse_to_variant('{"a":"miss"}')),
+ (3, parse_to_variant('{"a":null}')),
+ (4, parse_to_variant('{}')),
+ (5, NULL);
+```
+
+```sql
+SELECT id FROM search_variant_null_demo WHERE SEARCH('v.a:hit') ORDER BY id;
+```
+
+```text
+id
+1
+```
+
+```sql
+SELECT id FROM search_variant_null_demo WHERE SEARCH('NOT v.a:hit') ORDER BY
id;
+```
+
+```text
+id
+2
+3
+4
+5
+```
+
+`NOT SEARCH('v.a:hit')` 返回相同的 id。如果某条路径在所有行中都不存在，`SEARCH('v.absent:hit')`
不返回任何行，`SEARCH('NOT v.absent:hit')` 返回全部五行。如果只想保留目标路径有非 NULL 值的行，可在 SEARCH 外添加
`v['a'] IS NOT NULL`。
+
+**路径缺失与索引不可用是两种状态。** 如果路径有数据，但所需倒排索引无法执行，SEARCH 会报错 `SEARCH index unavailable
for Variant field 'v.a'`，取反查询也一样。例如：CREATE INDEX 后历史 rowset 尚未构建索引，或路径仅保存在 doc
存储中且没有可执行的叶子索引。SEARCH 不会将这些情况当作零命中，也不会自动退回扫描。应确保所有待查询数据均具有所需路径的可执行索引，或改用普通 SQL
提取路径后查询。
## 按场景使用 SEARCH
@@ -184,7 +301,7 @@ Lucene 模式模拟 Elasticsearch/Lucene 的 query_string 行为，布尔操作
- AND/OR/NOT 是影响相邻词项的修饰符
- 操作符优先级从左到右
- 内部使用 MUST/SHOULD/MUST_NOT（类似 Lucene 的 Occur 枚举）
-- 纯 NOT 查询返回空结果（需要正向子句）
+- 纯 NOT 查询会补充全匹配条件，再排除匹配行
**启用 Lucene 模式：**
diff --git
a/versioned_docs/version-4.x/table-design/index/inverted-index/search-function.md
b/versioned_docs/version-4.x/table-design/index/inverted-index/search-function.md
index 5c1d38fccaa..2bc8403ed6d 100644
---
a/versioned_docs/version-4.x/table-design/index/inverted-index/search-function.md
+++
b/versioned_docs/version-4.x/table-design/index/inverted-index/search-function.md
@@ -42,7 +42,7 @@ SEARCH('<search_expression>', '<default_field>',
'<default_operator>')
**Usage notes:**
- **Position**: Used in a `WHERE` clause as a predicate participating in row
filtering
-- **Return type**: BOOLEAN (TRUE on a match)
+- **Return type**: Non-nullable BOOLEAN (TRUE on a match, FALSE otherwise;
since Doris 4.2)
When `default_field` is provided, Doris automatically expands bare terms or
functions to that field. For example:
@@ -78,17 +78,134 @@ WHERE SEARCH('apple banana',
'{"default_field":"title","default_operator":"and","mode":"lucene"}');
```
-### NULL and Three-Valued Logic
+### NULL and Two-Valued Matching {#null-and-three-valued-logic}
-`SEARCH()` follows SQL three-valued logic:
+**Version:** The two-valued matching rules, NULL/NOT examples, and VARIANT
index-error behavior below apply starting with Doris 4.2.
-- When all column values participating in the match are NULL, the result is
UNKNOWN (filtered out in `WHERE`)
-- When combined with other subexpressions, the result follows boolean
short-circuit rules. For example:
- - `TRUE OR NULL = TRUE`
- - `FALSE OR NULL = NULL`
- - `NOT NULL = NULL`
+`SEARCH` returns a non-nullable BOOLEAN: TRUE for a match and FALSE for a
nonmatch. An ordinary term does not match a NULL value or a VARIANT path proven
absent from storage. SEARCH does not propagate UNKNOWN.
-This behavior is consistent with text search operators.
+In standard mode, `AND` intersects match sets, `OR` unions them, and `NOT`
takes the complement relative to the rows visible to the query. Thus, both
`SEARCH('NOT a:hit')` and `NOT SEARCH('a:hit')` include NULL rows. Lucene mode
uses the same NULL/nonmatch rule, while retaining its own Boolean parsing rules
described below. Computing `score()` or using the query cache does not change
the complete match set; sorting and LIMIT can change which rows are returned.
+
+Ordinary SQL comparisons and `MATCH` operators retain SQL three-valued logic.
For a NULL input, `a != 'hit'` and `NOT (a MATCH_ANY 'hit')` are UNKNOWN and
are filtered out by `WHERE`. An outer SQL expression can still produce UNKNOWN
from its other nullable operands.
+
+#### Example: NULL and NOT
+
+Run the examples in a database where you have CREATE, INSERT, and SELECT
privileges. The single bucket and replica below are for a small demonstration
table.
+
+```sql
+CREATE TABLE search_null_demo (
+ id INT,
+ a VARCHAR(16) NULL,
+ INDEX idx_a(a) USING INVERTED PROPERTIES("parser"="unicode")
+)
+DUPLICATE KEY(id)
+DISTRIBUTED BY HASH(id) BUCKETS 1
+PROPERTIES("replication_num"="1");
+
+INSERT INTO search_null_demo VALUES (1, 'hit'), (2, 'miss'), (3, NULL);
+```
+
+```sql
+SELECT id FROM search_null_demo WHERE SEARCH('a:hit') ORDER BY id;
+```
+
+```text
+id
+1
+```
+
+```sql
+SELECT id FROM search_null_demo WHERE SEARCH('NOT a:hit') ORDER BY id;
+```
+
+```text
+id
+2
+3
+```
+
+```sql
+SELECT id FROM search_null_demo WHERE NOT SEARCH('a:hit') ORDER BY id;
+```
+
+```text
+id
+2
+3
+```
+
+To exclude NULL rows as well, add an explicit `IS NOT NULL` filter:
+
+```sql
+SELECT id FROM search_null_demo
+WHERE a IS NOT NULL AND NOT SEARCH('a:hit') ORDER BY id;
+```
+
+```text
+id
+2
+```
+
+For these three rows, the SQL condition `a != 'hit'` and the text search
condition `NOT (a MATCH_ANY 'hit')` also return only id 2.
+
+#### VARIANT: NULL, Missing Paths, and Unavailable Indexes
+
+An ordinary VARIANT term such as `v.a:hit` follows the same rule:
+
+| Value written to `v` | `v.a:hit` | `NOT v.a:hit` |
+| --- | --- | --- |
+| `{"a":"hit"}` | TRUE | FALSE |
+| `{"a":"miss"}` | FALSE | TRUE |
+| `{"a":null}` | FALSE | TRUE |
+| `{}` (path `a` is missing) | FALSE | TRUE |
+| SQL NULL (the entire VARIANT column is NULL) | FALSE | TRUE |
+
+For stored objects outside arrays, JSON `null` members are removed, so reading
that path returns SQL NULL, just like a missing member. This differs from a
JSON `null` computed within an expression; see [the VARIANT
guide](../../../sql-manual/basic-element/sql-data-types/semi-structured/VARIANT.md).
+
+Use `parse_to_variant` to insert JSON objects. The following table disables
doc mode so the example uses materialized subpaths with executable indexes.
+
+```sql
+CREATE TABLE search_variant_null_demo (
+ id INT,
+ v VARIANT<PROPERTIES("variant_enable_doc_mode"="false")>,
+ INDEX idx_v(v) USING INVERTED PROPERTIES("parser"="unicode")
+)
+DUPLICATE KEY(id)
+DISTRIBUTED BY HASH(id) BUCKETS 1
+PROPERTIES("replication_num"="1");
+
+INSERT INTO search_variant_null_demo VALUES
+ (1, parse_to_variant('{"a":"hit"}')),
+ (2, parse_to_variant('{"a":"miss"}')),
+ (3, parse_to_variant('{"a":null}')),
+ (4, parse_to_variant('{}')),
+ (5, NULL);
+```
+
+```sql
+SELECT id FROM search_variant_null_demo WHERE SEARCH('v.a:hit') ORDER BY id;
+```
+
+```text
+id
+1
+```
+
+```sql
+SELECT id FROM search_variant_null_demo WHERE SEARCH('NOT v.a:hit') ORDER BY
id;
+```
+
+```text
+id
+2
+3
+4
+5
+```
+
+`NOT SEARCH('v.a:hit')` returns the same ids. If a path is absent from every
row, `SEARCH('v.absent:hit')` returns no rows and `SEARCH('NOT v.absent:hit')`
returns all five rows. To retain only rows with a non-NULL value at the target
path, add `v['a'] IS NOT NULL` outside SEARCH.
+
+**A missing path and an unavailable index are different states.** If the path
contains data but its required inverted index cannot be executed, SEARCH
reports `SEARCH index unavailable for Variant field 'v.a'`, including for
negated queries. Examples include historical rowsets whose index has not been
built after CREATE INDEX, or a path retained only in doc storage without an
executable leaf index. SEARCH does not treat these states as an empty match set
or automatically fall back to a s [...]
## Using SEARCH by Scenario
@@ -184,7 +301,7 @@ Lucene mode emulates the query_string behavior of
Elasticsearch/Lucene. Boolean
- AND/OR/NOT are modifiers that affect adjacent terms
- Operator precedence is left to right
- MUST/SHOULD/MUST_NOT are used internally (similar to Lucene's Occur enum)
-- A pure NOT query returns an empty result (a positive clause is required)
+- A pure NOT query adds a match-all condition and excludes the matching rows
**Enable Lucene mode:**
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
