davidradl commented on code in PR #28737:
URL: https://github.com/apache/flink/pull/28737#discussion_r3585815582
##########
docs/content.zh/docs/sql/reference/queries/joins.md:
##########
@@ -295,6 +295,74 @@ WHERE
- SQL 中可以定义 temporal table DDL，但不能定义 temporal table 函数;
- temporal table DDL 和 temporal table function 都支持 temporal join 版本表，但只有
temporal table function 可以 temporal join 任何表/视图的最新版本（即"处理时间 Temporal Join"）。
+LATERAL SNAPSHOT Join
+--------------
+
+{{< label Streaming >}}
+
+A `LATERAL SNAPSHOT` join is a *stream enrichment* join that augments an
append-only table (the *probe* side) with the current state of an updating
table (the *build* side).
+Unlike an [event-time temporal join](#event-time-temporal-join), it does not
correlate each probe-side row with a specific historical version of the build
side.
+Instead, every probe-side row is joined with the build-side state that is
current at the time the row is processed, similar to a [processing-time
temporal join](#processing-time-temporal-join), but with well-defined behavior
at query start-up.
+
+The `LATERAL SNAPSHOT` join is designed for scenarios where the other temporal
joins are a poor fit:
+
+- The build side updates **infrequently**. An event-time temporal join relies
on continuous build-side watermarks to emit results, so a build side that
rarely advances its watermark stalls the join and lets probe-side state
accumulate. A `LATERAL SNAPSHOT` join does not stall when the build side goes
idle.
+- The application has **low-latency** requirements that are incompatible with
the watermark-induced delay of an event-time temporal join.
+- The build side has **no primary key**. Event-time and processing-time
temporal joins require the build-side primary key to appear in the equi-join
condition; a `LATERAL SNAPSHOT` join does not.
+
+The build side is wrapped in the `SNAPSHOT` table function inside a `LATERAL
TABLE` clause. Both `INNER JOIN` and `LEFT [OUTER] JOIN` are supported. The
join requires at least one conjunctive equality predicate; additional non-equi
predicates are allowed in the `ON` clause.
+
+```sql
+SELECT [column_list]
+FROM probe_table
+[LEFT] JOIN LATERAL TABLE(
+ SNAPSHOT(
+ input => TABLE build_table,
+ [ load_completed_condition => <'compile_time' | 'user_time'>, ]
+ [ load_completed_time => <timestamp_ltz>, ]
+ [ load_completed_idle_timeout => <interval>, ]
+ [ state_ttl => <interval> ])) AS s
+ON probe_table.key = s.key
+```
+
+The `SNAPSHOT` function accepts the following arguments:
+
+| Argument | Type | Required | Description |
+| --- | --- | --- | --- |
+| `input` | TABLE | yes | The build-side table. It may use any [changelog
mode]({{< ref "docs/sql/reference/queries/changelog" >}}) (inserts, updates,
and deletes) and must declare a [watermark]({{< ref
"docs/concepts/sql-table-concepts/time_attributes" >}}#event-time). |
+| `load_completed_condition` | STRING | no | Determines when the initial load
phase completes. One of `'compile_time'` (default) or `'user_time'`. With
`'compile_time'`, the load phase completes once the build-side watermark
reaches the wall-clock time at which the query was compiled. With
`'user_time'`, it completes once the build-side watermark reaches the explicit
`load_completed_time`. |
+| `load_completed_time` | TIMESTAMP_LTZ(3) | no | The build-side event time
that completes the load phase. Required when `load_completed_condition` is
`'user_time'` and must not be set otherwise. |
+| `load_completed_idle_timeout` | INTERVAL | no | A processing-time fallback
that completes the load phase if the build-side watermark stalls before
reaching the configured time. |
+| `state_ttl` | INTERVAL | no | Retention time for build-side state. Keys that
are not accessed within this duration become eligible for eviction. Only
applied during the join phase. Defaults to the pipeline's [state TTL]({{< ref
"docs/dev/table/config" >}}#table-exec-state-ttl). |
+
+The operator processes its inputs in two sequential phases. During the **load
phase**, the operator builds up the build-side table by accumulating its
changes into state until the load completion condition is met. This allows the
build side to load its initial state (for example the current contents of a
compacted topic or a change-data-capture snapshot) before any probe-side row is
joined, ensuring that early probe-side rows are not joined against an
incomplete build side. While the build side is not yet ready, incoming
probe-side rows are buffered. The load phase completes when the build-side
watermark reaches the wall-clock time at which the query was compiled
(`compile_time`, the default), when the build-side watermark reaches
`load_completed_time` (`user_time`), or, as a fallback, when the
`load_completed_idle_timeout` elapses in processing time without the build-side
watermark advancing (which handles build sides that become idle during
start-up).
+
+When transitioning from load to **join phase**, all buffered probe-side rows
are joined against the current build-side state and emitted. From this point
onwards, each probe-side row is joined and emitted immediately against the
build-side state that is current at that moment. Build-side updates continue to
be applied to the state and are made visible to later probe-side rows.
+
+Reusing the tables from the [event-time temporal
join](#event-time-temporal-join) example, the following query enriches each
order with the conversion rate that is current when the order is processed. In
contrast to the temporal join, the join keeps making progress even if the rates
are updated only rarely.
+
+```sql
+SELECT
+ order_id,
+ price,
+ orders.currency,
+ conversion_rate,
+ order_time
+FROM orders
+JOIN LATERAL TABLE(
+ SNAPSHOT(input => TABLE currency_rates)) AS rates
+ON orders.currency = rates.currency;
+```
+
+The probe side must be an append-only table, and its time attributes are
preserved in the output. The build side may be an updating table and must
declare a watermark. The result is append-only. A build-side rowtime attribute
that is projected into the output is materialized as a regular `TIMESTAMP` and
is no longer a time attribute. Probe-side watermarks are forwarded downstream
in join phase; build-side watermarks are consumed internally and are not
propagated.
Review Comment:
I suggest a picture )sequence diagram or the like) showing the time line
including
- the build side and probe side
- watermarks
- how the select is triggered including the load phase
- maybe an example input and output
--
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]
