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)

Reply via email to