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 69f3727dde96 [docs](MIN_BY) Clarify ties and deterministic associated
minima (#4628)
69f3727dde96 is described below
commit 69f3727dde96d1200d1b8a2ad6265f037080d16d
Author: Mingyu Chen (Rayner) <[email protected]>
AuthorDate: Fri Nov 13 08:04:26 2026 +0800
[docs](MIN_BY) Clarify ties and deterministic associated minima (#4628)
The MIN_BY examples contain tied minimum keys, but display one
associated value without explaining how ties are handled. This can
suggest that MIN_BY returns the smallest associated value, which the
implementation does not guarantee.
Document the unspecified choice among tied keys, label existing tied
output as one possible result, and add a self-contained query that
returns `MIN(k1)` among rows with the minimum `k4`. For the issue's
data, this deterministically returns `3`.
Fixes #2312.
Source checked in master and branches 3.0, 3.1, 4.0, 4.1:
`AggregateFunctionMinByData` changes the associated value when the key
becomes strictly smaller (`change_if_less` / `set_if_smaller`),
including aggregate-state merging. It does not apply `MIN` to the
associated value on equal keys. Examples: [3.0
implementation](https://github.com/apache/doris/blob/4090f6c40d2238b7aff73185bcada48f96fbd6be/be/src/vec/aggregate_functions/aggregate_function_min_max_by.h#L130),
[master
implementation](https://github.com/apache/doris/blob/172120cbaa46b0a2eff6c37d86fb438c3a307375/be/src/exprs/aggregate/aggregate_function_min_max_by.h#L223).
## Review and validation
| Checkpoint | Conclusion |
| --- | --- |
| Goal | Explains tied-key behavior and provides the requested
deterministic result. |
| Scope | Tie note, output qualification and one standalone example per
page. Existing version-specific type behavior retained. |
| Versions/locales | Dev, 4.x, 3.x EN/ZH updated together. Archives
unchanged. |
| Links/navigation | Existing paths, headings, links and sidebar entries
retained; one example subheading added. |
| Framework | Not affected. |
| Validation | Six JSON front-matter blocks parsed; six Markdown pages
compiled with the installed MDX compiler; new SQL/output blocks
identical across copies; `git diff --check` passed. Independent SQLite
evaluation confirms the new query's expected row `(3)`; no Doris SQL
execution or full site build was run. |
| Other issues | No introduced issue found in self-review. Avoids
guaranteeing a first input row or a stable order under distributed
aggregation. |
## Versions
- [x] dev
- [x] 4.x
- [x] 3.x
- [ ] 2.1 or older
## Languages
- [x] Chinese
- [x] English
## Docs Checklist
- [x] Checked by AI
- [ ] Test Cases Built
- [x] Updated required version and language counterparts
- [x] Updated both languages
Co-authored-by: morningman <[email protected]>
---
.../sql-functions/aggregate-functions/min-by.md | 28 ++++++++++++++++++++++
.../sql-functions/aggregate-functions/min-by.md | 28 ++++++++++++++++++++++
.../sql-functions/aggregate-functions/min-by.md | 28 ++++++++++++++++++++++
.../sql-functions/aggregate-functions/min-by.md | 28 ++++++++++++++++++++++
.../sql-functions/aggregate-functions/min-by.md | 28 ++++++++++++++++++++++
.../sql-functions/aggregate-functions/min-by.md | 28 ++++++++++++++++++++++
6 files changed, 168 insertions(+)
diff --git a/docs/sql-manual/sql-functions/aggregate-functions/min-by.md
b/docs/sql-manual/sql-functions/aggregate-functions/min-by.md
index 9d586870ccbb..52f903109da9 100644
--- a/docs/sql-manual/sql-functions/aggregate-functions/min-by.md
+++ b/docs/sql-manual/sql-functions/aggregate-functions/min-by.md
@@ -14,6 +14,8 @@ When `<expr2>` is an Array, arrays are compared
lexicographically, element by el
Version difference: In the Doris 4.1 series since 4.1.5, and in Doris 4.2 and
later, a `NULL` element inside an array compares greater than a non-`NULL`
element. In Doris 4.0 and Doris 4.1 before 4.1.5, a `NULL` array element
compares smaller. A `NULL` array value is ignored in all these versions.
+If multiple rows share the minimum `<expr2>`, `MIN_BY` returns `<expr1>` from
one of those rows. Which row is selected is not guaranteed, and the result is
not necessarily the smallest associated value. For a deterministic result,
specify a tie-breaking rule, for example by filtering to the minimum `<expr2>`
and applying `MIN` to `<expr1>`.
+
## Syntax
```sql
@@ -59,6 +61,8 @@ Table created and data inserted successfully.
select min_by(k1, k2) from tbl;
```
+One possible result follows; returning either `3` or `4` is valid for this
example.
+
```text
+----------------+
| min_by(k1, k2) |
@@ -125,3 +129,27 @@ SELECT min_by(1, CAST(NULL AS ARRAY<INT>)) AS result;
```text
NULL
```
+
+### Deterministic handling of tied minimum values
+
+This self-contained example returns the smallest `k1` among rows with the
minimum `k4`. Even though two rows have the minimum `k4 = 1`, the result is
always `3`:
+
+```sql
+WITH data AS (
+ SELECT 0 AS k1, 100 AS k4
+ UNION ALL SELECT 1, 4
+ UNION ALL SELECT 4, 1
+ UNION ALL SELECT 3, 1
+)
+SELECT MIN(k1) AS min_k1
+FROM data
+WHERE k4 = (SELECT MIN(k4) FROM data);
+```
+
+```text
++--------+
+| min_k1 |
++--------+
+| 3 |
++--------+
+```
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/aggregate-functions/min-by.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/aggregate-functions/min-by.md
index 74a67b6cc132..67ead1bd0f0e 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/aggregate-functions/min-by.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/aggregate-functions/min-by.md
@@ -14,6 +14,8 @@ MIN_BY 函数用于根据指定列的最小值，返回对应的的关联值。
版本差异：Doris 4.1 系列自 4.1.5 起，以及 Doris 4.2 及后续版本中，数组内的 `NULL` 元素大于非 `NULL`
元素；Doris 4.0 和 4.1.5 之前的 4.1 版本中数组内的 `NULL` 元素小于非 `NULL` 元素。整个数组值为 `NULL`
的输入在这些版本中均被忽略。
+当多行的 `<expr2>` 同为最小值时，`MIN_BY` 返回其中一行的
`<expr1>`，不保证选择哪一行，也不保证返回这些关联值中的最小值。若需要确定的结果，请显式指定并列值的处理规则，例如先筛选出 `<expr2>`
最小的行，再对 `<expr1>` 使用 `MIN`。
+
## 语法
```sql
@@ -59,6 +61,8 @@ insert into tbl values
select min_by(k1, k2) from tbl;
```
+一种可能的结果如下；此示例中返回 `3` 或 `4` 都符合 `MIN_BY` 的定义。
+
```text
+----------------+
| min_by(k1, k2) |
@@ -125,3 +129,27 @@ SELECT min_by(1, CAST(NULL AS ARRAY<INT>)) AS result;
```text
NULL
```
+
+### 并列最小值的确定性处理
+
+下面的自包含示例返回最小 `k4` 所对应的最小 `k1`。即使最小的 `k4 = 1` 有两行，结果也固定为 `3`：
+
+```sql
+WITH data AS (
+ SELECT 0 AS k1, 100 AS k4
+ UNION ALL SELECT 1, 4
+ UNION ALL SELECT 4, 1
+ UNION ALL SELECT 3, 1
+)
+SELECT MIN(k1) AS min_k1
+FROM data
+WHERE k4 = (SELECT MIN(k4) FROM data);
+```
+
+```text
++--------+
+| min_k1 |
++--------+
+| 3 |
++--------+
+```
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-3.x/sql-manual/sql-functions/aggregate-functions/min-by.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-3.x/sql-manual/sql-functions/aggregate-functions/min-by.md
index b217ce23d3ac..b4935fffe2c3 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-3.x/sql-manual/sql-functions/aggregate-functions/min-by.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-3.x/sql-manual/sql-functions/aggregate-functions/min-by.md
@@ -10,6 +10,8 @@
MIN_BY 函数用于根据指定列的最小值，返回对应的的关联值。
+当多行的 `<expr2>` 同为最小值时，`MIN_BY` 返回其中一行的
`<expr1>`，不保证选择哪一行，也不保证返回这些关联值中的最小值。若需要确定的结果，请显式指定并列值的处理规则，例如先筛选出 `<expr2>`
最小的行，再对 `<expr1>` 使用 `MIN`。
+
## 语法
```sql
@@ -47,6 +49,8 @@ select * from tbl;
select min_by(k1, k4) from tbl;
```
+一种可能的结果如下；此示例中返回 `3` 或 `4` 都符合 `MIN_BY` 的定义。
+
```text
+--------------------+
| min_by(`k1`, `k4`) |
@@ -54,3 +58,27 @@ select min_by(k1, k4) from tbl;
| 4 |
+--------------------+
```
+
+### 并列最小值的确定性处理
+
+下面的自包含示例返回最小 `k4` 所对应的最小 `k1`。即使最小的 `k4 = 1` 有两行，结果也固定为 `3`：
+
+```sql
+WITH data AS (
+ SELECT 0 AS k1, 100 AS k4
+ UNION ALL SELECT 1, 4
+ UNION ALL SELECT 4, 1
+ UNION ALL SELECT 3, 1
+)
+SELECT MIN(k1) AS min_k1
+FROM data
+WHERE k4 = (SELECT MIN(k4) FROM data);
+```
+
+```text
++--------+
+| min_k1 |
++--------+
+| 3 |
++--------+
+```
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-4.x/sql-manual/sql-functions/aggregate-functions/min-by.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-4.x/sql-manual/sql-functions/aggregate-functions/min-by.md
index 2dbbaa5ee1c6..d4480b2748b8 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-4.x/sql-manual/sql-functions/aggregate-functions/min-by.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-4.x/sql-manual/sql-functions/aggregate-functions/min-by.md
@@ -14,6 +14,8 @@ MIN_BY 函数用于根据指定列的最小值，返回对应的的关联值。
版本差异：Doris 4.1 系列自 4.1.5 起，以及 Doris 4.2 及后续版本中，数组内的 `NULL` 元素大于非 `NULL`
元素；Doris 4.0 和 4.1.5 之前的 4.1 版本中数组内的 `NULL` 元素小于非 `NULL` 元素。整个数组值为 `NULL`
的输入在这些版本中均被忽略。
+当多行的 `<expr2>` 同为最小值时，`MIN_BY` 返回其中一行的
`<expr1>`，不保证选择哪一行，也不保证返回这些关联值中的最小值。若需要确定的结果，请显式指定并列值的处理规则，例如先筛选出 `<expr2>`
最小的行，再对 `<expr1>` 使用 `MIN`。
+
## 语法
```sql
@@ -59,6 +61,8 @@ insert into tbl values
select min_by(k1, k2) from tbl;
```
+一种可能的结果如下；此示例中返回 `3` 或 `4` 都符合 `MIN_BY` 的定义。
+
```text
+----------------+
| min_by(k1, k2) |
@@ -125,3 +129,27 @@ SELECT min_by(1, CAST(NULL AS ARRAY<INT>)) AS result;
```text
NULL
```
+
+### 并列最小值的确定性处理
+
+下面的自包含示例返回最小 `k4` 所对应的最小 `k1`。即使最小的 `k4 = 1` 有两行，结果也固定为 `3`：
+
+```sql
+WITH data AS (
+ SELECT 0 AS k1, 100 AS k4
+ UNION ALL SELECT 1, 4
+ UNION ALL SELECT 4, 1
+ UNION ALL SELECT 3, 1
+)
+SELECT MIN(k1) AS min_k1
+FROM data
+WHERE k4 = (SELECT MIN(k4) FROM data);
+```
+
+```text
++--------+
+| min_k1 |
++--------+
+| 3 |
++--------+
+```
diff --git
a/versioned_docs/version-3.x/sql-manual/sql-functions/aggregate-functions/min-by.md
b/versioned_docs/version-3.x/sql-manual/sql-functions/aggregate-functions/min-by.md
index d7af49dfb4cd..8231f3e446df 100644
---
a/versioned_docs/version-3.x/sql-manual/sql-functions/aggregate-functions/min-by.md
+++
b/versioned_docs/version-3.x/sql-manual/sql-functions/aggregate-functions/min-by.md
@@ -10,6 +10,8 @@
The MIN_BY function is used to return the corresponding associated value based
on the minimum value of the specified column.
+If multiple rows share the minimum `<expr2>`, `MIN_BY` returns `<expr1>` from
one of those rows. Which row is selected is not guaranteed, and the result is
not necessarily the smallest associated value. For a deterministic result,
specify a tie-breaking rule, for example by filtering to the minimum `<expr2>`
and applying `MIN` to `<expr1>`.
+
## Syntax
```sql
@@ -48,6 +50,8 @@ select * from tbl;
select min_by(k1, k4) from tbl;
```
+One possible result follows; returning either `3` or `4` is valid for this
example.
+
```text
+--------------------+
| min_by(`k1`, `k4`) |
@@ -55,3 +59,27 @@ select min_by(k1, k4) from tbl;
| 4 |
+--------------------+
```
+
+### Deterministic handling of tied minimum values
+
+This self-contained example returns the smallest `k1` among rows with the
minimum `k4`. Even though two rows have the minimum `k4 = 1`, the result is
always `3`:
+
+```sql
+WITH data AS (
+ SELECT 0 AS k1, 100 AS k4
+ UNION ALL SELECT 1, 4
+ UNION ALL SELECT 4, 1
+ UNION ALL SELECT 3, 1
+)
+SELECT MIN(k1) AS min_k1
+FROM data
+WHERE k4 = (SELECT MIN(k4) FROM data);
+```
+
+```text
++--------+
+| min_k1 |
++--------+
+| 3 |
++--------+
+```
diff --git
a/versioned_docs/version-4.x/sql-manual/sql-functions/aggregate-functions/min-by.md
b/versioned_docs/version-4.x/sql-manual/sql-functions/aggregate-functions/min-by.md
index cce5d69bfddd..091e95a2c178 100644
---
a/versioned_docs/version-4.x/sql-manual/sql-functions/aggregate-functions/min-by.md
+++
b/versioned_docs/version-4.x/sql-manual/sql-functions/aggregate-functions/min-by.md
@@ -14,6 +14,8 @@ When `<expr2>` is an Array, arrays are compared
lexicographically, element by el
Version difference: In the Doris 4.1 series since 4.1.5, and in Doris 4.2 and
later, a `NULL` element inside an array compares greater than a non-`NULL`
element. In Doris 4.0 and Doris 4.1 before 4.1.5, a `NULL` array element
compares smaller. A `NULL` array value is ignored in all these versions.
+If multiple rows share the minimum `<expr2>`, `MIN_BY` returns `<expr1>` from
one of those rows. Which row is selected is not guaranteed, and the result is
not necessarily the smallest associated value. For a deterministic result,
specify a tie-breaking rule, for example by filtering to the minimum `<expr2>`
and applying `MIN` to `<expr1>`.
+
## Syntax
```sql
@@ -59,6 +61,8 @@ Table created and data inserted successfully.
select min_by(k1, k2) from tbl;
```
+One possible result follows; returning either `3` or `4` is valid for this
example.
+
```text
+----------------+
| min_by(k1, k2) |
@@ -125,3 +129,27 @@ SELECT min_by(1, CAST(NULL AS ARRAY<INT>)) AS result;
```text
NULL
```
+
+### Deterministic handling of tied minimum values
+
+This self-contained example returns the smallest `k1` among rows with the
minimum `k4`. Even though two rows have the minimum `k4 = 1`, the result is
always `3`:
+
+```sql
+WITH data AS (
+ SELECT 0 AS k1, 100 AS k4
+ UNION ALL SELECT 1, 4
+ UNION ALL SELECT 4, 1
+ UNION ALL SELECT 3, 1
+)
+SELECT MIN(k1) AS min_k1
+FROM data
+WHERE k4 = (SELECT MIN(k4) FROM data);
+```
+
+```text
++--------+
+| min_k1 |
++--------+
+| 3 |
++--------+
+```
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
