github-actions[bot] commented on code in PR #3462:
URL: https://github.com/apache/doris-website/pull/3462#discussion_r2928981562
##########
i18n/zh-CN/docusaurus-plugin-content-docs/current/query-data/asof-join.md:
##########
@@ -0,0 +1,422 @@
+---
+{
+ "title": "ASOF JOIN",
+ "language": "zh-CN",
+ "description": "ASOF JOIN 基于日期时间条件，将左表的每一行与右表中满足条件的最近一行进行匹配，常用于时序数据分析场景。"
+}
+---
+
+## 概述
+
Review Comment:
**Missing version callout (Chinese current docs):** Same issue as the
English current docs — this file is missing the `:::info` version note present
in
`i18n/zh-CN/docusaurus-plugin-content-docs/version-4.x/query-data/asof-join.md`.
Consider adding:
```
:::info
此功能自 Apache Doris 4.0.5 和 4.1.0 版本起支持。
:::
```
##########
docs/query-data/asof-join.md:
##########
@@ -0,0 +1,422 @@
+---
+{
+ "title": "ASOF JOIN",
+ "language": "en",
+ "description": "ASOF JOIN matches each row of the left table to the
nearest qualifying row in the right table based on a date/time condition,
commonly used in time-series analysis."
+}
+---
+
+## Overview
+
Review Comment:
**Missing version callout:** The
`versioned_docs/version-4.x/query-data/asof-join.md` file includes a `:::info`
admonition stating that this feature is supported since Doris 4.0.5 and 4.1.0,
but this current (dev) version is missing that note. Users who land on the dev
docs from an older version would not know when this feature was introduced.
Consider adding:
```
:::info
This feature is supported since Apache Doris versions 4.0.5 and 4.1.0.
:::
```
If `docs/` is intentionally treated as always-latest and the feature will be
in all future dev releases, at minimum the PR checklist should reflect that
`dev` is also targeted.
##########
docs/query-data/asof-join.md:
##########
@@ -0,0 +1,422 @@
+---
+{
+ "title": "ASOF JOIN",
+ "language": "en",
+ "description": "ASOF JOIN matches each row of the left table to the
nearest qualifying row in the right table based on a date/time condition,
commonly used in time-series analysis."
+}
+---
+
+## Overview
+
+ASOF JOIN is a special type of JOIN designed for time-series lookups on
date/time columns. Unlike regular equality JOIN, ASOF JOIN does not require an
exact match. Instead, for each left-table row, it finds the nearest right-table
row that satisfies the directional comparison in `MATCH_CONDITION`.
+
+ASOF JOIN does **not** mean "the absolutely closest row by time difference".
The returned row is the nearest row in the direction specified by
`MATCH_CONDITION`.
+
+A typical use case: given a table of stock trades and a table of stock quotes,
for each trade, find the most recent quote that was available at the time of
the trade. With regular JOIN, this requires complex subqueries and window
functions, while ASOF JOIN accomplishes it in a single, clear statement.
+
+ASOF JOIN supports two sub-types:
+
+- **ASOF JOIN** (ASOF LEFT JOIN): For each row in the left table, find the
nearest qualifying match in the right table according to `MATCH_CONDITION`. If
no match is found, the right-side columns are filled with NULL.
+- **ASOF INNER JOIN**: Same matching logic, but rows from the left table that
have no match are excluded from the result.
+
+## Syntax
+
+```sql
+SELECT <select_list>
+FROM <left_table>
+ASOF [LEFT] JOIN <right_table>
+ MATCH_CONDITION(<left_datetime_expr> <comparison_operator>
<right_datetime_expr>)
+ { ON <left_table.col> = <right_table.col> [AND ...]
+ | USING (<column_name> [, ...]) }
+```
+
+**Where:**
+
+- `ASOF JOIN` or `ASOF LEFT JOIN`: Left outer ASOF JOIN. Left table rows
without a match produce NULL on the right side.
+- `ASOF INNER JOIN`: Inner ASOF JOIN. Left table rows without a match are
discarded.
+- `MATCH_CONDITION(...)`: Specifies the nearest-match condition. Must
reference columns from both the left and right tables and evaluate to supported
date/time values (`DATEV2`, `DATETIMEV2`, or `TIMESTAMPTZ`).
+- `<comparison_operator>`: One of `>=`, `>`, `<=`, `<`.
+- `ON ...` or `USING (...)`: Defines one or more equality keys that partition
the matching space (equivalent to grouping). With `ON`, each condition must be
in the form `left_expr = right_expr`. With `USING`, each listed column name
must exist on both sides.
+
+## Parameters
+
+| Parameter | Required | Description |
+|-----------|----------|-------------|
+| `left_table` | Yes | The left (probe) table. All rows from this table are
evaluated. |
+| `right_table` | Yes | The right (build) table. Used to find the closest
match. |
+| `MATCH_CONDITION` | Yes | Defines the nearest-match rule. Both sides must
reference columns from both tables and evaluate to supported date/time values
(`DATEV2`, `DATETIMEV2`, or `TIMESTAMPTZ`). Expressions are allowed. Supported
operators: `>=`, `>`, `<=`, `<`. |
+| `ON` / `USING` clause | Yes | Defines one or more equality keys. Acts as the
grouping key — matching is only performed within the same group. `ON` supports
one or more equality (`=`) conditions and expressions (e.g., `SUBSTRING(l.code,
1, 3) = r.prefix`). `USING` supports one or more shared column names. |
+
+## How ASOF JOIN Matching Works
+
+The matching rule depends on the comparison operator in `MATCH_CONDITION`:
+
+| Operator | Matching Behavior | Typical Use Case |
+|----------|------------------|-----------------|
+| `>=` | For each left row, find the right row with the **largest** value that
is **less than or equal to** the left value. | Find the most recent
snapshot/quote before or at the event time. |
+| `>` | For each left row, find the right row with the **largest** value that
is **strictly less than** the left value. | Find the most recent snapshot/quote
strictly before the event time. |
+| `<=` | For each left row, find the right row with the **smallest** value
that is **greater than or equal to** the left value. | Find the next
event/snapshot at or after the current time. |
+| `<` | For each left row, find the right row with the **smallest** value that
is **strictly greater than** the left value. | Find the next event/snapshot
strictly after the current time. |
+
+**Key rules:**
+
+1. `MATCH_CONDITION` columns must be of type `DATEV2`, `DATETIMEV2`, or
`TIMESTAMPTZ`.
+2. Expressions are allowed in `MATCH_CONDITION`, for example:
`MATCH_CONDITION(l.ts >= r.ts + INTERVAL 1 HOUR)` or `MATCH_CONDITION(l.ts >=
DATE_ADD(r.ts, INTERVAL 3 HOUR))`.
+3. The equality key clause can be written with either `ON` or `USING`. In
`ON`, only equality (`=`) conjuncts are allowed. Non-equality conditions (such
as `>`, `OR`) or literal comparisons (such as `l.grp = 1`) are not allowed in
the `ON` clause.
+4. NULL values in the match column or the equality column never produce a
match. If the left row's match column is NULL, or if no matching right row
exists within the group, the right side is filled with NULL (for LEFT JOIN) or
the row is discarded (for INNER JOIN).
+5. When multiple right-side rows in the same group have the same match value
and satisfy the match condition, one of them is returned (non-deterministic).
+
+## Examples
+
+### Preparation
+
+Create a trades table and a quotes table:
+
+```sql
+CREATE TABLE trades (
+ trade_id INT,
+ symbol VARCHAR(10),
+ trade_time DATETIME,
+ price DECIMAL(10, 2),
+ quantity INT
+) DISTRIBUTED BY HASH(trade_id) BUCKETS 3
+PROPERTIES("replication_num" = "1");
+
+CREATE TABLE quotes (
+ quote_id INT,
+ symbol VARCHAR(10),
+ quote_time DATETIME,
+ bid_price DECIMAL(10, 2),
+ ask_price DECIMAL(10, 2)
+) DISTRIBUTED BY HASH(quote_id) BUCKETS 3
+PROPERTIES("replication_num" = "1");
+
+INSERT INTO trades VALUES
+(1, 'AAPL', '2024-01-01 10:00:05', 150.50, 100),
+(2, 'AAPL', '2024-01-01 10:00:15', 151.00, 200),
+(3, 'AAPL', '2024-01-01 10:00:25', 150.75, 150),
+(4, 'GOOG', '2024-01-01 10:00:10', 2800.00, 50),
+(5, 'GOOG', '2024-01-01 10:00:20', 2805.00, 75),
+(6, 'MSFT', '2024-01-01 10:00:08', 380.00, 120);
+
+INSERT INTO quotes VALUES
+(1, 'AAPL', '2024-01-01 10:00:00', 150.00, 150.10),
+(2, 'AAPL', '2024-01-01 10:00:10', 150.40, 150.60),
+(3, 'AAPL', '2024-01-01 10:00:20', 150.90, 151.10),
+(4, 'GOOG', '2024-01-01 10:00:05', 2795.00, 2800.00),
+(5, 'GOOG', '2024-01-01 10:00:15', 2802.00, 2808.00),
+(6, 'MSFT', '2024-01-01 10:00:00', 378.00, 380.00),
+(7, 'MSFT', '2024-01-01 10:00:10', 379.50, 381.00);
+```
+
+### Example 1: Find the Most Recent Quote for Each Trade (>=)
+
+For each trade, find the latest quote whose `quote_time` is less than or equal
to the trade's `trade_time`, within the same `symbol`.
+
+```sql
+SELECT t.trade_id, t.symbol, t.trade_time, t.price,
+ q.quote_id, q.quote_time, q.bid_price, q.ask_price
+FROM trades t
+ASOF LEFT JOIN quotes q
+ MATCH_CONDITION(t.trade_time >= q.quote_time)
+ ON t.symbol = q.symbol
+ORDER BY t.trade_id;
+```
+
+```text
++----------+--------+---------------------+--------+----------+---------------------+-----------+-----------+
+| trade_id | symbol | trade_time | price | quote_id | quote_time
| bid_price | ask_price |
++----------+--------+---------------------+--------+----------+---------------------+-----------+-----------+
+| 1 | AAPL | 2024-01-01 10:00:05 | 150.50 | 1 | 2024-01-01
10:00:00 | 150.00 | 150.10 |
+| 2 | AAPL | 2024-01-01 10:00:15 | 151.00 | 2 | 2024-01-01
10:00:10 | 150.40 | 150.60 |
+| 3 | AAPL | 2024-01-01 10:00:25 | 150.75 | 3 | 2024-01-01
10:00:20 | 150.90 | 151.10 |
+| 4 | GOOG | 2024-01-01 10:00:10 | 2800.0 | 4 | 2024-01-01
10:00:05 | 2795.00 | 2800.00 |
+| 5 | GOOG | 2024-01-01 10:00:20 | 2805.0 | 5 | 2024-01-01
10:00:15 | 2802.00 | 2808.00 |
Review Comment:
**Inconsistent decimal formatting in output:** The `price` column for GOOG
rows shows `2800.0` and `2805.0` (1 decimal place), while the column is defined
as `DECIMAL(10, 2)` and the INSERT uses `2800.00`. All other `price` values in
this result set show 2 decimal places (e.g., `150.50`). The output should show
`2800.00` and `2805.00` for consistency and accuracy.
##########
docs/query-data/asof-join.md:
##########
@@ -0,0 +1,422 @@
+---
+{
+ "title": "ASOF JOIN",
+ "language": "en",
+ "description": "ASOF JOIN matches each row of the left table to the
nearest qualifying row in the right table based on a date/time condition,
commonly used in time-series analysis."
+}
+---
+
+## Overview
+
+ASOF JOIN is a special type of JOIN designed for time-series lookups on
date/time columns. Unlike regular equality JOIN, ASOF JOIN does not require an
exact match. Instead, for each left-table row, it finds the nearest right-table
row that satisfies the directional comparison in `MATCH_CONDITION`.
+
+ASOF JOIN does **not** mean "the absolutely closest row by time difference".
The returned row is the nearest row in the direction specified by
`MATCH_CONDITION`.
+
+A typical use case: given a table of stock trades and a table of stock quotes,
for each trade, find the most recent quote that was available at the time of
the trade. With regular JOIN, this requires complex subqueries and window
functions, while ASOF JOIN accomplishes it in a single, clear statement.
+
+ASOF JOIN supports two sub-types:
+
+- **ASOF JOIN** (ASOF LEFT JOIN): For each row in the left table, find the
nearest qualifying match in the right table according to `MATCH_CONDITION`. If
no match is found, the right-side columns are filled with NULL.
+- **ASOF INNER JOIN**: Same matching logic, but rows from the left table that
have no match are excluded from the result.
+
+## Syntax
+
+```sql
+SELECT <select_list>
+FROM <left_table>
+ASOF [LEFT] JOIN <right_table>
+ MATCH_CONDITION(<left_datetime_expr> <comparison_operator>
<right_datetime_expr>)
+ { ON <left_table.col> = <right_table.col> [AND ...]
+ | USING (<column_name> [, ...]) }
+```
+
+**Where:**
+
+- `ASOF JOIN` or `ASOF LEFT JOIN`: Left outer ASOF JOIN. Left table rows
without a match produce NULL on the right side.
+- `ASOF INNER JOIN`: Inner ASOF JOIN. Left table rows without a match are
discarded.
+- `MATCH_CONDITION(...)`: Specifies the nearest-match condition. Must
reference columns from both the left and right tables and evaluate to supported
date/time values (`DATEV2`, `DATETIMEV2`, or `TIMESTAMPTZ`).
+- `<comparison_operator>`: One of `>=`, `>`, `<=`, `<`.
+- `ON ...` or `USING (...)`: Defines one or more equality keys that partition
the matching space (equivalent to grouping). With `ON`, each condition must be
in the form `left_expr = right_expr`. With `USING`, each listed column name
must exist on both sides.
+
+## Parameters
+
+| Parameter | Required | Description |
+|-----------|----------|-------------|
+| `left_table` | Yes | The left (probe) table. All rows from this table are
evaluated. |
+| `right_table` | Yes | The right (build) table. Used to find the closest
match. |
+| `MATCH_CONDITION` | Yes | Defines the nearest-match rule. Both sides must
reference columns from both tables and evaluate to supported date/time values
(`DATEV2`, `DATETIMEV2`, or `TIMESTAMPTZ`). Expressions are allowed. Supported
operators: `>=`, `>`, `<=`, `<`. |
+| `ON` / `USING` clause | Yes | Defines one or more equality keys. Acts as the
grouping key — matching is only performed within the same group. `ON` supports
one or more equality (`=`) conditions and expressions (e.g., `SUBSTRING(l.code,
1, 3) = r.prefix`). `USING` supports one or more shared column names. |
+
+## How ASOF JOIN Matching Works
+
+The matching rule depends on the comparison operator in `MATCH_CONDITION`:
+
+| Operator | Matching Behavior | Typical Use Case |
+|----------|------------------|-----------------|
+| `>=` | For each left row, find the right row with the **largest** value that
is **less than or equal to** the left value. | Find the most recent
snapshot/quote before or at the event time. |
+| `>` | For each left row, find the right row with the **largest** value that
is **strictly less than** the left value. | Find the most recent snapshot/quote
strictly before the event time. |
+| `<=` | For each left row, find the right row with the **smallest** value
that is **greater than or equal to** the left value. | Find the next
event/snapshot at or after the current time. |
+| `<` | For each left row, find the right row with the **smallest** value that
is **strictly greater than** the left value. | Find the next event/snapshot
strictly after the current time. |
+
+**Key rules:**
+
+1. `MATCH_CONDITION` columns must be of type `DATEV2`, `DATETIMEV2`, or
`TIMESTAMPTZ`.
+2. Expressions are allowed in `MATCH_CONDITION`, for example:
`MATCH_CONDITION(l.ts >= r.ts + INTERVAL 1 HOUR)` or `MATCH_CONDITION(l.ts >=
DATE_ADD(r.ts, INTERVAL 3 HOUR))`.
Review Comment:
**Type inconsistency:** Key Rule 1 states `MATCH_CONDITION` columns must be
of type `DATEV2`, `DATETIMEV2`, or `TIMESTAMPTZ`, but the `CREATE TABLE` DDL at
line 79 uses `DATETIME` (not `DATETIMEV2`), and the CTE examples at lines
302–333 use `CAST(... AS DATETIME)`. In Doris, `DATETIME` is an alias for
`DATETIMEV2`, so this works in practice, but the inconsistency may confuse
users. Consider either updating the DDL/CTE examples to use `DATETIMEV2`, or
adding a note that `DATETIME` is accepted as an alias.
##########
i18n/zh-CN/docusaurus-plugin-content-docs/current/query-data/asof-join.md:
##########
@@ -0,0 +1,422 @@
+---
+{
+ "title": "ASOF JOIN",
+ "language": "zh-CN",
+ "description": "ASOF JOIN 基于日期时间条件，将左表的每一行与右表中满足条件的最近一行进行匹配，常用于时序数据分析场景。"
+}
+---
+
+## 概述
+
+ASOF JOIN 是一种专为基于日期时间列的时序查询设计的特殊 JOIN 类型。与常规的等值 JOIN 不同，ASOF JOIN 不要求精确匹配，而是根据
`MATCH_CONDITION` 指定的方向，为左表中的每一行选择右表中满足条件的最近一行。
+
+ASOF JOIN 的“最近”并不是指按时间差绝对值查找最近一行，而是指在 `MATCH_CONDITION` 指定方向上满足条件的最近一行。
+
+一个典型的应用场景：给定一张股票交易表和一张行情报价表，需要为每笔交易找到交易发生时刻最近的报价。如果使用普通
JOIN，需要借助复杂的子查询和窗口函数来实现，而 ASOF JOIN 可以用一条简洁的语句完成。
+
+ASOF JOIN 支持两种子类型：
+
+- **ASOF JOIN**（ASOF LEFT JOIN）：对左表的每一行，根据 `MATCH_CONDITION`
的方向，在右表中查找满足条件的最近匹配行。如果未找到匹配，右侧列填充 NULL。
+- **ASOF INNER JOIN**：匹配逻辑相同，但左表中没有匹配的行会从结果中被排除。
+
+## 语法
+
+```sql
+SELECT <select_list>
+FROM <left_table>
+ASOF [LEFT] JOIN <right_table>
+ MATCH_CONDITION(<left_datetime_expr> <comparison_operator>
<right_datetime_expr>)
+ { ON <left_table.col> = <right_table.col> [AND ...]
+ | USING (<column_name> [, ...]) }
+```
+
+**说明：**
+
+- `ASOF JOIN` 或 `ASOF LEFT JOIN`：左外 ASOF JOIN。左表中没有匹配的行，右侧列填充 NULL。
+- `ASOF INNER JOIN`：内 ASOF JOIN。左表中没有匹配的行会被丢弃。
+-
`MATCH_CONDITION(...)`：指定最近匹配条件，必须同时引用左表和右表的列，且结果必须为受支持的日期时间值（`DATEV2`、`DATETIMEV2`
或 `TIMESTAMPTZ`）。
+- `<comparison_operator>`：`>=`、`>`、`<=`、`<` 四种比较运算符之一。
+- `ON ...` 或 `USING (...)`：定义一个或多个等值键，用于划分匹配空间（相当于分组）。使用 `ON` 时，每个条件的形式必须为
`left_expr = right_expr`；使用 `USING` 时，列出的列名必须同时存在于左右两侧。
+
+## 参数说明
+
+| 参数 | 是否必须 | 说明 |
+|------|----------|------|
+| `left_table` | 是 | 左表（探测表）。该表的所有行都会被评估。 |
+| `right_table` | 是 | 右表（构建表）。用于查找最接近的匹配。 |
+| `MATCH_CONDITION` | 是 |
定义最近匹配规则。两侧必须引用左右表的列，且结果必须为受支持的日期时间值（`DATEV2`、`DATETIMEV2` 或
`TIMESTAMPTZ`）；允许使用表达式。支持的运算符：`>=`、`>`、`<=`、`<`。 |
+| `ON` / `USING` 子句 | 是 | 定义一个或多个等值键。作为分组键——匹配仅在同一组内进行。`ON`
支持一个或多个等值（`=`）条件以及表达式（例如 `SUBSTRING(l.code, 1, 3) = r.prefix`）；`USING`
支持一个或多个同名列。 |
+
+## ASOF JOIN 匹配规则
+
+匹配规则取决于 `MATCH_CONDITION` 中的比较运算符：
+
+| 运算符 | 匹配行为 | 典型使用场景 |
+|--------|---------|-------------|
+| `>=` | 对左表的每一行，查找右表中**最大的**且**小于等于**左侧值的行。 | 查找事件发生时刻或之前的最新快照/报价。 |
+| `>` | 对左表的每一行，查找右表中**最大的**且**严格小于**左侧值的行。 | 查找严格早于事件时刻的最新快照/报价。 |
+| `<=` | 对左表的每一行，查找右表中**最小的**且**大于等于**左侧值的行。 | 查找当前时刻或之后的下一个事件/快照。 |
+| `<` | 对左表的每一行，查找右表中**最小的**且**严格大于**左侧值的行。 | 查找严格晚于当前时刻的下一个事件/快照。 |
+
+**关键规则：**
+
+1. `MATCH_CONDITION` 中的列必须为 `DATEV2`、`DATETIMEV2` 或 `TIMESTAMPTZ` 类型。
+2. `MATCH_CONDITION` 中允许使用表达式，例如：`MATCH_CONDITION(l.ts >= r.ts + INTERVAL 1
HOUR)` 或 `MATCH_CONDITION(l.ts >= DATE_ADD(r.ts, INTERVAL 3 HOUR))`。
+3. 等值键子句可以写成 `ON` 或 `USING`。使用 `ON` 时，只允许使用等值（`=`）条件并用 `AND` 连接；`ON`
子句中不允许使用不等式条件（如 `>`、`OR`）或字面量比较（如 `l.grp = 1`）。
+4. 匹配列或等值列中的 NULL 值不会产生匹配。如果左表行的匹配列为 NULL，或者在同组内没有符合条件的右表行，则右侧列填充 NULL（LEFT
JOIN）或该行被丢弃（INNER JOIN）。
+5. 当右表中多行具有相同的分组键且在匹配列上具有相同的值，并且都满足匹配条件时，返回其中一行（不确定性）。
+
+## 示例
+
+### 数据准备
+
+创建交易表和报价表：
+
+```sql
+CREATE TABLE trades (
+ trade_id INT,
+ symbol VARCHAR(10),
+ trade_time DATETIME,
+ price DECIMAL(10, 2),
+ quantity INT
+) DISTRIBUTED BY HASH(trade_id) BUCKETS 3
+PROPERTIES("replication_num" = "1");
+
+CREATE TABLE quotes (
+ quote_id INT,
+ symbol VARCHAR(10),
+ quote_time DATETIME,
+ bid_price DECIMAL(10, 2),
+ ask_price DECIMAL(10, 2)
+) DISTRIBUTED BY HASH(quote_id) BUCKETS 3
+PROPERTIES("replication_num" = "1");
+
+INSERT INTO trades VALUES
+(1, 'AAPL', '2024-01-01 10:00:05', 150.50, 100),
+(2, 'AAPL', '2024-01-01 10:00:15', 151.00, 200),
+(3, 'AAPL', '2024-01-01 10:00:25', 150.75, 150),
+(4, 'GOOG', '2024-01-01 10:00:10', 2800.00, 50),
+(5, 'GOOG', '2024-01-01 10:00:20', 2805.00, 75),
+(6, 'MSFT', '2024-01-01 10:00:08', 380.00, 120);
+
+INSERT INTO quotes VALUES
+(1, 'AAPL', '2024-01-01 10:00:00', 150.00, 150.10),
+(2, 'AAPL', '2024-01-01 10:00:10', 150.40, 150.60),
+(3, 'AAPL', '2024-01-01 10:00:20', 150.90, 151.10),
+(4, 'GOOG', '2024-01-01 10:00:05', 2795.00, 2800.00),
+(5, 'GOOG', '2024-01-01 10:00:15', 2802.00, 2808.00),
+(6, 'MSFT', '2024-01-01 10:00:00', 378.00, 380.00),
+(7, 'MSFT', '2024-01-01 10:00:10', 379.50, 381.00);
+```
+
+### 示例 1：为每笔交易找到最近的报价 (>=)
+
+对每笔交易，在相同 `symbol` 中查找 `quote_time` 小于等于 `trade_time` 的最新报价。
+
+```sql
+SELECT t.trade_id, t.symbol, t.trade_time, t.price,
+ q.quote_id, q.quote_time, q.bid_price, q.ask_price
+FROM trades t
+ASOF LEFT JOIN quotes q
+ MATCH_CONDITION(t.trade_time >= q.quote_time)
+ ON t.symbol = q.symbol
+ORDER BY t.trade_id;
+```
+
+```text
++----------+--------+---------------------+--------+----------+---------------------+-----------+-----------+
+| trade_id | symbol | trade_time | price | quote_id | quote_time
| bid_price | ask_price |
++----------+--------+---------------------+--------+----------+---------------------+-----------+-----------+
+| 1 | AAPL | 2024-01-01 10:00:05 | 150.50 | 1 | 2024-01-01
10:00:00 | 150.00 | 150.10 |
+| 2 | AAPL | 2024-01-01 10:00:15 | 151.00 | 2 | 2024-01-01
10:00:10 | 150.40 | 150.60 |
+| 3 | AAPL | 2024-01-01 10:00:25 | 150.75 | 3 | 2024-01-01
10:00:20 | 150.90 | 151.10 |
+| 4 | GOOG | 2024-01-01 10:00:10 | 2800.0 | 4 | 2024-01-01
10:00:05 | 2795.00 | 2800.00 |
+| 5 | GOOG | 2024-01-01 10:00:20 | 2805.0 | 5 | 2024-01-01
10:00:15 | 2802.00 | 2808.00 |
Review Comment:
**Inconsistent decimal formatting in output:** Same as the English file —
GOOG `price` values are shown as `2800.0` and `2805.0` instead of `2800.00` and
`2805.00`. The column is `DECIMAL(10, 2)` so it should always display 2 decimal
places.
##########
docs/query-data/asof-join.md:
##########
@@ -0,0 +1,422 @@
+---
+{
+ "title": "ASOF JOIN",
+ "language": "en",
+ "description": "ASOF JOIN matches each row of the left table to the
nearest qualifying row in the right table based on a date/time condition,
commonly used in time-series analysis."
+}
+---
+
+## Overview
+
+ASOF JOIN is a special type of JOIN designed for time-series lookups on
date/time columns. Unlike regular equality JOIN, ASOF JOIN does not require an
exact match. Instead, for each left-table row, it finds the nearest right-table
row that satisfies the directional comparison in `MATCH_CONDITION`.
+
+ASOF JOIN does **not** mean "the absolutely closest row by time difference".
The returned row is the nearest row in the direction specified by
`MATCH_CONDITION`.
+
+A typical use case: given a table of stock trades and a table of stock quotes,
for each trade, find the most recent quote that was available at the time of
the trade. With regular JOIN, this requires complex subqueries and window
functions, while ASOF JOIN accomplishes it in a single, clear statement.
+
+ASOF JOIN supports two sub-types:
+
+- **ASOF JOIN** (ASOF LEFT JOIN): For each row in the left table, find the
nearest qualifying match in the right table according to `MATCH_CONDITION`. If
no match is found, the right-side columns are filled with NULL.
+- **ASOF INNER JOIN**: Same matching logic, but rows from the left table that
have no match are excluded from the result.
+
+## Syntax
+
+```sql
+SELECT <select_list>
+FROM <left_table>
+ASOF [LEFT] JOIN <right_table>
+ MATCH_CONDITION(<left_datetime_expr> <comparison_operator>
<right_datetime_expr>)
+ { ON <left_table.col> = <right_table.col> [AND ...]
+ | USING (<column_name> [, ...]) }
+```
+
+**Where:**
+
+- `ASOF JOIN` or `ASOF LEFT JOIN`: Left outer ASOF JOIN. Left table rows
without a match produce NULL on the right side.
+- `ASOF INNER JOIN`: Inner ASOF JOIN. Left table rows without a match are
discarded.
+- `MATCH_CONDITION(...)`: Specifies the nearest-match condition. Must
reference columns from both the left and right tables and evaluate to supported
date/time values (`DATEV2`, `DATETIMEV2`, or `TIMESTAMPTZ`).
+- `<comparison_operator>`: One of `>=`, `>`, `<=`, `<`.
+- `ON ...` or `USING (...)`: Defines one or more equality keys that partition
the matching space (equivalent to grouping). With `ON`, each condition must be
in the form `left_expr = right_expr`. With `USING`, each listed column name
must exist on both sides.
+
+## Parameters
+
+| Parameter | Required | Description |
+|-----------|----------|-------------|
+| `left_table` | Yes | The left (probe) table. All rows from this table are
evaluated. |
+| `right_table` | Yes | The right (build) table. Used to find the closest
match. |
+| `MATCH_CONDITION` | Yes | Defines the nearest-match rule. Both sides must
reference columns from both tables and evaluate to supported date/time values
(`DATEV2`, `DATETIMEV2`, or `TIMESTAMPTZ`). Expressions are allowed. Supported
operators: `>=`, `>`, `<=`, `<`. |
+| `ON` / `USING` clause | Yes | Defines one or more equality keys. Acts as the
grouping key — matching is only performed within the same group. `ON` supports
one or more equality (`=`) conditions and expressions (e.g., `SUBSTRING(l.code,
1, 3) = r.prefix`). `USING` supports one or more shared column names. |
+
+## How ASOF JOIN Matching Works
+
+The matching rule depends on the comparison operator in `MATCH_CONDITION`:
+
+| Operator | Matching Behavior | Typical Use Case |
+|----------|------------------|-----------------|
+| `>=` | For each left row, find the right row with the **largest** value that
is **less than or equal to** the left value. | Find the most recent
snapshot/quote before or at the event time. |
+| `>` | For each left row, find the right row with the **largest** value that
is **strictly less than** the left value. | Find the most recent snapshot/quote
strictly before the event time. |
+| `<=` | For each left row, find the right row with the **smallest** value
that is **greater than or equal to** the left value. | Find the next
event/snapshot at or after the current time. |
+| `<` | For each left row, find the right row with the **smallest** value that
is **strictly greater than** the left value. | Find the next event/snapshot
strictly after the current time. |
+
+**Key rules:**
+
+1. `MATCH_CONDITION` columns must be of type `DATEV2`, `DATETIMEV2`, or
`TIMESTAMPTZ`.
+2. Expressions are allowed in `MATCH_CONDITION`, for example:
`MATCH_CONDITION(l.ts >= r.ts + INTERVAL 1 HOUR)` or `MATCH_CONDITION(l.ts >=
DATE_ADD(r.ts, INTERVAL 3 HOUR))`.
+3. The equality key clause can be written with either `ON` or `USING`. In
`ON`, only equality (`=`) conjuncts are allowed. Non-equality conditions (such
as `>`, `OR`) or literal comparisons (such as `l.grp = 1`) are not allowed in
the `ON` clause.
+4. NULL values in the match column or the equality column never produce a
match. If the left row's match column is NULL, or if no matching right row
exists within the group, the right side is filled with NULL (for LEFT JOIN) or
the row is discarded (for INNER JOIN).
+5. When multiple right-side rows in the same group have the same match value
and satisfy the match condition, one of them is returned (non-deterministic).
+
+## Examples
+
+### Preparation
+
+Create a trades table and a quotes table:
+
+```sql
+CREATE TABLE trades (
+ trade_id INT,
+ symbol VARCHAR(10),
+ trade_time DATETIME,
+ price DECIMAL(10, 2),
+ quantity INT
+) DISTRIBUTED BY HASH(trade_id) BUCKETS 3
+PROPERTIES("replication_num" = "1");
+
+CREATE TABLE quotes (
+ quote_id INT,
+ symbol VARCHAR(10),
+ quote_time DATETIME,
+ bid_price DECIMAL(10, 2),
+ ask_price DECIMAL(10, 2)
+) DISTRIBUTED BY HASH(quote_id) BUCKETS 3
+PROPERTIES("replication_num" = "1");
+
+INSERT INTO trades VALUES
+(1, 'AAPL', '2024-01-01 10:00:05', 150.50, 100),
+(2, 'AAPL', '2024-01-01 10:00:15', 151.00, 200),
+(3, 'AAPL', '2024-01-01 10:00:25', 150.75, 150),
+(4, 'GOOG', '2024-01-01 10:00:10', 2800.00, 50),
+(5, 'GOOG', '2024-01-01 10:00:20', 2805.00, 75),
+(6, 'MSFT', '2024-01-01 10:00:08', 380.00, 120);
+
+INSERT INTO quotes VALUES
+(1, 'AAPL', '2024-01-01 10:00:00', 150.00, 150.10),
+(2, 'AAPL', '2024-01-01 10:00:10', 150.40, 150.60),
+(3, 'AAPL', '2024-01-01 10:00:20', 150.90, 151.10),
+(4, 'GOOG', '2024-01-01 10:00:05', 2795.00, 2800.00),
+(5, 'GOOG', '2024-01-01 10:00:15', 2802.00, 2808.00),
+(6, 'MSFT', '2024-01-01 10:00:00', 378.00, 380.00),
+(7, 'MSFT', '2024-01-01 10:00:10', 379.50, 381.00);
+```
+
+### Example 1: Find the Most Recent Quote for Each Trade (>=)
+
+For each trade, find the latest quote whose `quote_time` is less than or equal
to the trade's `trade_time`, within the same `symbol`.
+
+```sql
+SELECT t.trade_id, t.symbol, t.trade_time, t.price,
+ q.quote_id, q.quote_time, q.bid_price, q.ask_price
+FROM trades t
+ASOF LEFT JOIN quotes q
+ MATCH_CONDITION(t.trade_time >= q.quote_time)
+ ON t.symbol = q.symbol
+ORDER BY t.trade_id;
+```
+
+```text
++----------+--------+---------------------+--------+----------+---------------------+-----------+-----------+
+| trade_id | symbol | trade_time | price | quote_id | quote_time
| bid_price | ask_price |
++----------+--------+---------------------+--------+----------+---------------------+-----------+-----------+
+| 1 | AAPL | 2024-01-01 10:00:05 | 150.50 | 1 | 2024-01-01
10:00:00 | 150.00 | 150.10 |
+| 2 | AAPL | 2024-01-01 10:00:15 | 151.00 | 2 | 2024-01-01
10:00:10 | 150.40 | 150.60 |
+| 3 | AAPL | 2024-01-01 10:00:25 | 150.75 | 3 | 2024-01-01
10:00:20 | 150.90 | 151.10 |
+| 4 | GOOG | 2024-01-01 10:00:10 | 2800.0 | 4 | 2024-01-01
10:00:05 | 2795.00 | 2800.00 |
+| 5 | GOOG | 2024-01-01 10:00:20 | 2805.0 | 5 | 2024-01-01
10:00:15 | 2802.00 | 2808.00 |
+| 6 | MSFT | 2024-01-01 10:00:08 | 380.00 | 6 | 2024-01-01
10:00:00 | 378.00 | 380.00 |
++----------+--------+---------------------+--------+----------+---------------------+-----------+-----------+
+```
+
+Trade #1 (AAPL, 10:00:05) is matched with quote #1 (AAPL, 10:00:00) because
that is the closest quote at or before the trade time for the same symbol.
+
+### Example 2: Find the Next Quote After Each Trade (<=)
+
+```sql
+SELECT t.trade_id, t.symbol, t.trade_time, t.price,
+ q.quote_id, q.quote_time, q.bid_price
+FROM trades t
+ASOF LEFT JOIN quotes q
+ MATCH_CONDITION(t.trade_time <= q.quote_time)
+ ON t.symbol = q.symbol
+ORDER BY t.trade_id;
+```
+
+```text
++----------+--------+---------------------+--------+----------+---------------------+-----------+
+| trade_id | symbol | trade_time | price | quote_id | quote_time
| bid_price |
++----------+--------+---------------------+--------+----------+---------------------+-----------+
+| 1 | AAPL | 2024-01-01 10:00:05 | 150.50 | 2 | 2024-01-01
10:00:10 | 150.40 |
+| 2 | AAPL | 2024-01-01 10:00:15 | 151.00 | 3 | 2024-01-01
10:00:20 | 150.90 |
+| 3 | AAPL | 2024-01-01 10:00:25 | 150.75 | NULL | NULL
| NULL |
+| 4 | GOOG | 2024-01-01 10:00:10 | 2800.0 | 5 | 2024-01-01
10:00:15 | 2802.00 |
+| 5 | GOOG | 2024-01-01 10:00:20 | 2805.0 | NULL | NULL
| NULL |
+| 6 | MSFT | 2024-01-01 10:00:08 | 380.00 | 7 | 2024-01-01
10:00:10 | 379.50 |
++----------+--------+---------------------+--------+----------+---------------------+-----------+
+```
+
+Trade #3 (AAPL, 10:00:25) has no subsequent quote, so the right side returns
NULL.
+
+### Example 3: ASOF INNER JOIN — Exclude Unmatched Rows
+
+```sql
+SELECT t.trade_id, t.symbol, t.trade_time, t.price,
+ q.quote_id, q.quote_time, q.bid_price
+FROM trades t
+ASOF INNER JOIN quotes q
+ MATCH_CONDITION(t.trade_time >= q.quote_time)
+ ON t.symbol = q.symbol
+ORDER BY t.trade_id;
+```
+
+```text
++----------+--------+---------------------+--------+----------+---------------------+-----------+
+| trade_id | symbol | trade_time | price | quote_id | quote_time
| bid_price |
++----------+--------+---------------------+--------+----------+---------------------+-----------+
+| 1 | AAPL | 2024-01-01 10:00:05 | 150.50 | 1 | 2024-01-01
10:00:00 | 150.00 |
+| 2 | AAPL | 2024-01-01 10:00:15 | 151.00 | 2 | 2024-01-01
10:00:10 | 150.40 |
+| 3 | AAPL | 2024-01-01 10:00:25 | 150.75 | 3 | 2024-01-01
10:00:20 | 150.90 |
+| 4 | GOOG | 2024-01-01 10:00:10 | 2800.0 | 4 | 2024-01-01
10:00:05 | 2795.00 |
+| 5 | GOOG | 2024-01-01 10:00:20 | 2805.0 | 5 | 2024-01-01
10:00:15 | 2802.00 |
+| 6 | MSFT | 2024-01-01 10:00:08 | 380.00 | 6 | 2024-01-01
10:00:00 | 378.00 |
++----------+--------+---------------------+--------+----------+---------------------+-----------+
+```
+
+All trades have a matching quote in this dataset, so the result is the same as
Example 1. If any trade had no matching quote, it would be excluded from the
result.
+
+### Example 4: Multiple Equality Conditions
+
+Match on multiple grouping keys (`product_id` and `region`) simultaneously:
+
+```sql
+SELECT o.order_id, o.product_id, o.region, o.order_time,
+ p.price, p.effective_time
+FROM orders o
+ASOF LEFT JOIN prices p
+ MATCH_CONDITION(o.order_time >= p.effective_time)
+ ON o.product_id = p.product_id AND o.region = p.region
+ORDER BY o.order_id;
+```
+
+This finds, for each order, the most recent price that was effective for the
same product in the same region.
+
+### Example 5: Expression in MATCH_CONDITION
+
+Find the matching right-side row whose timestamp is at least 1 hour before the
left row's timestamp:
+
+```sql
+SELECT l.id, l.ts, r.id AS rid, r.ts AS rts, r.data
+FROM left_table l
+ASOF LEFT JOIN right_table r
+ MATCH_CONDITION(l.ts >= r.ts + INTERVAL 1 HOUR)
+ ON l.grp = r.grp
+ORDER BY l.id;
+```
+
+Date/time functions are also supported:
+
+```sql
+MATCH_CONDITION(l.ts >= DATE_ADD(r.ts, INTERVAL 3 HOUR))
+MATCH_CONDITION(DATE_SUB(l.ts, INTERVAL 1 HOUR) >= r.ts)
+```
+
+### Example 6: Multi-level ASOF JOIN
+
+ASOF JOIN can be chained with other ASOF JOINs or regular JOINs:
+
+```sql
+SELECT o.order_id, o.order_time,
+ p.price, p.effective_time AS price_time,
+ i.stock_level, i.snapshot_time AS inv_time
+FROM orders o
+ASOF LEFT JOIN prices p
+ MATCH_CONDITION(o.order_time >= p.effective_time)
+ ON o.product_id = p.product_id AND o.region = p.region
+ASOF LEFT JOIN inventory i
+ MATCH_CONDITION(o.order_time >= i.snapshot_time)
+ ON o.product_id = i.product_id AND o.region = i.region
+ORDER BY o.order_id;
+```
+
+Mixing ASOF JOIN with regular JOIN is also supported:
+
+```sql
+SELECT o.order_id, prod.product_name,
+ o.order_time, p.price
+FROM orders o
+INNER JOIN products prod ON o.product_id = prod.product_id
+ASOF LEFT JOIN prices p
+ MATCH_CONDITION(o.order_time >= p.effective_time)
+ ON o.product_id = p.product_id AND o.region = p.region
+ORDER BY o.order_id;
+```
+
+### Example 7: ASOF JOIN with Aggregation
+
+```sql
+SELECT t.symbol,
+ COUNT(*) AS trade_count,
+ AVG(q.bid_price) AS avg_bid
+FROM trades t
+ASOF LEFT JOIN quotes q
+ MATCH_CONDITION(t.trade_time >= q.quote_time)
+ ON t.symbol = q.symbol
+GROUP BY t.symbol
+ORDER BY t.symbol;
+```
+
+### Example 8: Bidirectional ASOF JOIN — Finding Surrounding Records
+
+Find both the preceding and the following price for each order:
+
+```sql
+SELECT o.order_id, o.order_time,
+ p_before.price AS price_before,
+ p_before.effective_time AS time_before,
+ p_after.price AS price_after,
+ p_after.effective_time AS time_after
+FROM orders o
+ASOF LEFT JOIN prices p_before
+ MATCH_CONDITION(o.order_time >= p_before.effective_time)
+ ON o.product_id = p_before.product_id AND o.region = p_before.region
+ASOF LEFT JOIN prices p_after
+ MATCH_CONDITION(o.order_time <= p_after.effective_time)
+ ON o.product_id = p_after.product_id AND o.region = p_after.region
+ORDER BY o.order_id;
+```
+
+### Example 9: Directional Matching, Not Absolute Nearest
+
+ASOF JOIN only searches in the direction specified by `MATCH_CONDITION`. It
does not compare absolute time distance across both sides.
+
+```sql
+WITH left_events AS (
+ SELECT 1 AS event_id, 'AAPL' AS symbol, CAST('2024-01-01 10:00:06' AS
DATETIME) AS event_time
+),
+right_events AS (
+ SELECT 1 AS right_id, 'AAPL' AS symbol, CAST('2024-01-01 10:00:00' AS
DATETIME) AS ref_time
+ UNION ALL
+ SELECT 2 AS right_id, 'AAPL' AS symbol, CAST('2024-01-01 10:00:08' AS
DATETIME) AS ref_time
+)
+SELECT l.event_id, l.event_time, r.right_id, r.ref_time
+FROM left_events l
+ASOF LEFT JOIN right_events r
+ MATCH_CONDITION(l.event_time >= r.ref_time)
+ ON l.symbol = r.symbol;
+```
+
+```text
++----------+---------------------+----------+---------------------+
+| event_id | event_time | right_id | ref_time |
++----------+---------------------+----------+---------------------+
+| 1 | 2024-01-01 10:00:06 | 1 | 2024-01-01 10:00:00 |
++----------+---------------------+----------+---------------------+
+```
+
+Even though `10:00:08` is only 2 seconds away and `10:00:00` is 6 seconds
away, `MATCH_CONDITION(l.event_time >= r.ref_time)` only allows rows at or
before the left-side timestamp, so the result is `10:00:00`.
+
+```sql
+WITH left_events AS (
+ SELECT 1 AS event_id, 'AAPL' AS symbol, CAST('2024-01-01 10:00:06' AS
DATETIME) AS event_time
+),
+right_events AS (
+ SELECT 1 AS right_id, 'AAPL' AS symbol, CAST('2024-01-01 10:00:00' AS
DATETIME) AS ref_time
+ UNION ALL
+ SELECT 2 AS right_id, 'AAPL' AS symbol, CAST('2024-01-01 10:00:08' AS
DATETIME) AS ref_time
+)
+SELECT l.event_id, l.event_time, r.right_id, r.ref_time
+FROM left_events l
+ASOF LEFT JOIN right_events r
+ MATCH_CONDITION(l.event_time <= r.ref_time)
+ ON l.symbol = r.symbol;
+```
+
+```text
++----------+---------------------+----------+---------------------+
+| event_id | event_time | right_id | ref_time |
++----------+---------------------+----------+---------------------+
+| 1 | 2024-01-01 10:00:06 | 2 | 2024-01-01 10:00:08 |
++----------+---------------------+----------+---------------------+
+```
+
+### Example 10: Duplicate Match Values Can Be Non-deterministic
+
+When multiple right-side rows share the same grouping key and the same match
value, ASOF JOIN may return any one of them. This also applies to `TIMESTAMPTZ`.
+
+```sql
+WITH left_events AS (
+ SELECT 1 AS event_id, 'AAPL' AS symbol,
+ CAST('2024-01-01 10:00:05 +00:00' AS TIMESTAMPTZ) AS event_time
+),
+right_events AS (
+ SELECT 1 AS right_id, 'AAPL' AS symbol,
+ CAST('2024-01-01 10:00:00 +00:00' AS TIMESTAMPTZ) AS ref_time,
'snapshot_a' AS tag
+ UNION ALL
+ SELECT 2 AS right_id, 'AAPL' AS symbol,
+ CAST('2024-01-01 10:00:00 +00:00' AS TIMESTAMPTZ) AS ref_time,
'snapshot_b' AS tag
+)
+SELECT l.event_id, r.right_id, r.ref_time, r.tag
+FROM left_events l
+ASOF LEFT JOIN right_events r
+ MATCH_CONDITION(l.event_time >= r.ref_time)
+ ON l.symbol = r.symbol;
+```
+
+```text
++----------+----------+----------------------------+------------+
+| event_id | right_id | ref_time | tag |
++----------+----------+----------------------------+------------+
+| 1 | 1 | 2024-01-01 10:00:00 +00:00 | snapshot_a |
++----------+----------+----------------------------+------------+
+```
+
+The query may also return `right_id = 2` and `tag = snapshot_b`. If
deterministic output is required, deduplicate or pre-aggregate the right-side
rows before the ASOF JOIN.
+
+## Equivalent Rewrite
+
+ASOF JOIN is semantically equivalent to the following `LEFT JOIN` +
`ROW_NUMBER()` pattern, but with significantly better performance:
+
+```sql
+-- Equivalent to: ASOF LEFT JOIN ... MATCH_CONDITION(l.ts >= r.ts)
+SELECT id, rid, val FROM (
+ SELECT l.id, r.id AS rid, r.val,
Review Comment:
**Equivalent Rewrite uses undefined placeholder names:** The table names
(`left_table`, `right_table`) and column names (`id`, `rid`, `val`, `ts`,
`grp`) in this section are not defined in this document and differ from the
`trades`/`quotes` examples used throughout. Consider either:
1. Rewriting this example using the `trades`/`quotes` tables from the
earlier setup, or
2. Adding a comment like `-- Replace left_table, right_table, id, ts, grp,
val with your actual table and column names` to avoid confusion for users who
try to run this directly.
--
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: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
