Yaoxuan Wu created FLINK-41548:
----------------------------------

             Summary: Temporal join with an equality key joins a NULL probe key 
with the version of the NULL key (null keys are not filtered)
                 Key: FLINK-41548
                 URL: https://issues.apache.org/jira/browse/FLINK-41548
             Project: Flink
          Issue Type: Bug
          Components: Table SQL / Planner, Table SQL / Runtime
    Affects Versions: 2.3.0
            Reporter: Yaoxuan Wu


An event-time temporal join with an equality key joins a probe row whose key is 
NULL with the version of the NULL key, although NULL = NULL is not TRUE. A 
regular join over the same tables does not return that row. The same happens 
with a LEFT temporal join (the NULL-key row gets the NULL key's version instead 
of NULL padding) and with a temporal table function join. No error is raised.

{code:sql}
-- orders(k INT, v INT, ts BIGINT): (1, 1, 2000), (NULL, 2, 2000)
-- rates (k INT, v INT, ts BIGINT): (1, 10, 1000), (NULL, 20, 1000)
-- both: rt AS TO_TIMESTAMP_LTZ(ts, 3), WATERMARK FOR rt AS rt - INTERVAL 
'0.001' SECOND
-- (filesystem / json sources, streaming mode, parallelism 1)

CREATE TEMPORARY VIEW versioned_rates AS SELECT k, v, ts, rt FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY k ORDER BY rt DESC) AS rn FROM 
rates) WHERE rn = 1;

SELECT o.k, o.v, r.v FROM orders AS o
  JOIN versioned_rates FOR SYSTEM_TIME AS OF o.rt AS r ON o.k = r.k;
-- expected: (1, 1, 10)
-- actual:   (1, 1, 10), (NULL, 2, 20)

SELECT o.k, o.v, r.v FROM orders AS o
  LEFT JOIN versioned_rates FOR SYSTEM_TIME AS OF o.rt AS r ON o.k = r.k;
-- expected: (1, 1, 10), (NULL, 2, NULL)
-- actual:   (1, 1, 10), (NULL, 2, 20)

-- rates_fn = rates.createTemporalTableFunction($("rt"), $("k"))
SELECT o.k, o.v, r.v FROM orders AS o, LATERAL TABLE (rates_fn(o.rt)) AS r(k, 
v, ts, rt)
  WHERE o.k = r.k;
-- expected: (1, 1, 10)
-- actual:   (1, 1, 10), (NULL, 2, 20)

SELECT o.k, o.v, r.v FROM orders AS o JOIN rates AS r ON o.k = r.k;
-- (1, 1, 10)   regular join, for comparison
{code}

* Same on 2.0.1, 2.2.1, 2.3.0 and master (2.4-SNAPSHOT).
* The inputs are in time order and all rows are on time, so the result does not 
depend on arrival order.
* A versioned table with a declared primary key cannot hold a NULL key; 
versioned views built by deduplication on a nullable key, and temporal table 
functions over append-only tables, can.

Cause:
* {{StreamPhysicalTemporalJoin}} builds the exec node's {{JoinSpec}} with the 
equi keys, {{filterNulls}} (true for {{=}}, false for {{IS NOT DISTINCT FROM}}) 
and the non-equi remainder.
* {{StreamExecTemporalJoin.createJoinOperator}} generates the join condition 
from {{joinSpec.getNonEquiCondition()}} only and never reads 
{{joinSpec.getFilterNulls()}}. The equi keys are enforced only by the key 
selectors, and a NULL key is an ordinary key value for them.
* {{TemporalRowTimeJoinOperator}} (and {{TemporalProcessTimeJoinOperator}}) 
join a probe row with the latest version in the state of its key when the 
condition is true; nothing checks the key for NULL.

Regular streaming joins handle this: {{StreamExecJoin}} passes 
{{joinSpec.getFilterNulls()}} to the join operators, which wrap the condition 
in {{JoinConditionWithNullFilters}}. The temporal join could do the same, or 
drop probe rows and versions whose null-filtered key fields are NULL.




--
This message was sent by Atlassian Jira
(v10.3.26#10030026)

Reply via email to