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)