Yaoxuan Wu created FLINK-41583:
----------------------------------
Summary: Temporal table function join: a WHERE predicate on the
function's columns is pushed into the history table and an outdated version is
joined (FLINK-28988 guard misses TEMPORAL_JOIN_CONDITION)
Key: FLINK-41583
URL: https://issues.apache.org/jira/browse/FLINK-41583
Project: Flink
Issue Type: Bug
Components: Table SQL / Planner
Affects Versions: 2.2.1, 2.3.0, 2.0.1
Reporter: Yaoxuan Wu
In an event-time temporal table function join, a WHERE predicate that refers
only to the function's columns is pushed into the history table, below the
choice of the version. The join then picks the latest version that passes the
filter instead of the version valid at the probe time, and returns a row from
an outdated version. No error is raised.
{code:sql}
-- orders(k INT, ot TIMESTAMP(3), WATERMARK FOR ot AS ot): (1, 2026-01-01
00:00:10)
-- rates (k INT, v INT, rt TIMESTAMP(3), WATERMARK FOR rt AS rt): (1, 5,
2026-01-01 00:00:01), (1, -5, 2026-01-01 00:00:05)
-- filesystem / csv sources, streaming mode, parallelism 1
-- rates_fn = tEnv.from("rates").createTemporalTableFunction($("rt"), $("k"))
SELECT o.k, r.v FROM orders AS o, LATERAL TABLE (rates_fn(o.ot)) AS r
WHERE o.k = r.k;
-- (1, -5) correct: the version valid at 00:00:10
SELECT o.k, r.v FROM orders AS o, LATERAL TABLE (rates_fn(o.ot)) AS r
WHERE o.k = r.k AND r.v > 0;
-- expected: [] (the version valid at 00:00:10 has v = -5)
-- actual: (1, 5)
-- the same with a versioned view is correct:
CREATE TEMPORARY VIEW rates_v AS SELECT k, v, rt FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY k ORDER BY rt DESC) AS rn FROM
rates) WHERE rn = 1;
SELECT o.k, r.v FROM orders AS o JOIN rates_v FOR SYSTEM_TIME AS OF o.ot AS r
ON o.k = r.k AND r.v > 0;
-- []
{code}
Optimized plan of the wrong query: the filter is below the TemporalJoin on the
rates side.
{noformat}
Calc(select=[k, v])
+- TemporalJoin(joinType=[InnerJoin], where=[(__TEMPORAL_JOIN_CONDITION(ot, rt,
__TEMPORAL_JOIN_CONDITION_PRIMARY_KEY(k0)) AND (k = k0))], select=[k, ot, k0,
v, rt])
:- Exchange(distribution=[hash[k]])
: +- WatermarkAssigner(rowtime=[ot], watermark=[ot])
: +- TableSourceScan(table=[[default_catalog, default_database,
orders]], fields=[k, ot])
+- Exchange(distribution=[hash[k]])
+- Calc(select=[k, v, rt], where=[(v > 0)])
+- WatermarkAssigner(rowtime=[rt], watermark=[rt])
+- TableSourceScan(table=[[default_catalog, default_database,
rates]], fields=[k, v, rt])
{noformat}
* Same on 2.0.1, 2.2.1, 2.3.0 and master (2.4-SNAPSHOT).
* The inputs are in time order and every row is on time, so the result does not
depend on arrival order.
* A predicate that also refers to the probe side stays in the join condition
and gives the right result, e.g. {{r.v > 0 + 0 * EXTRACT(SECOND FROM o.ot)}}.
Cause:
* FLINK-28988 fixed this for {{FOR SYSTEM_TIME AS OF}}: {{FlinkFilterJoinRule}}
({{FlinkFilterIntoJoinRule}} / {{FlinkJoinConditionPushRule}}) does not push
filters into an event-time temporal join, using {{isEventTimeTemporalJoin}}.
* {{isEventTimeTemporalJoin}} only recognizes
{{TemporalJoinUtil.INITIAL_TEMPORAL_JOIN_CONDITION}} (4 operands), which the
{{FOR SYSTEM_TIME AS OF}} form produces.
* {{LogicalCorrelateToJoinFromTemporalTableFunctionRule}} rewrites the temporal
table function join with {{TEMPORAL_JOIN_CONDITION}}
({{TemporalJoinUtil.makeRowTimeTemporalFunctionJoinConCall}}), so the guard
does not apply and the right-only conjunct is pushed into the history table.
A possible fix: make {{isEventTimeTemporalJoin}} also match a row-time
{{TEMPORAL_JOIN_CONDITION}} (e.g. via
{{TemporalJoinUtil.isRowTimeTemporalFunctionJoinCon}}).
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)