Jungtaek Lim created SPARK-62044:
------------------------------------

             Summary: Stream-stream join evicts state bounded through a CAST to 
TIMESTAMP by the event time, losing matches
                 Key: SPARK-62044
                 URL: https://issues.apache.org/jira/browse/SPARK-62044
             Project: Spark
          Issue Type: Bug
          Components: Structured Streaming
    Affects Versions: 5.0.0
            Reporter: Jungtaek Lim


A stream-stream join can bound one input's state through a column that is cast 
to TIMESTAMP,
such as a DATE, TIMESTAMP_NTZ or numeric column. Spark solves the cutoff for 
the cast column, but
then evicts rows by the input's event time with that cutoff. So it drops rows 
that later rows of
the other input can still match. An inner join loses those matches. A left 
outer join emits the
row with nulls too early and also loses the match.

Example, with 1-hour watermarks on {{leftTime}} and {{rightTime}} and session 
time zone UTC:

{code:sql}
SELECT * FROM l JOIN r
  ON leftKey = rightKey
  AND rightTime BETWEEN leftTime AND CAST(leftDate AS TIMESTAMP)
{code}

||Step||Input||Spark today||Expected||
|1|left (1, leftTime = day 1, leftDate = day 5); right (9, day 1)|-|-|
|2|left (8, day 4 01:00, day 9); right (9, day 4 01:00)|-|-|
|3|right (9, day 4 01:00) (watermark now day 4)|left row 1 is evicted; a left 
outer join emits {{(1, day 1, null)}}|- (the row can still match until day 5)|
|4|right (1, day 4 12:00)|-|{{(1, day 1, day 4 12:00)}}|

The eviction predicate in step 3 is {{leftTime <= day 4}}, a cutoff solved from 
{{leftDate}} but
applied to {{leftTime}}. Same result with state format versions 2 and 4, inner 
and left outer.

A second symptom: an outer join whose preserved input has no watermark and is 
bounded only through
such a cast column never emits that input's unmatched rows. Example: a left 
outer join with a
watermark only on {{rightTime}} and
{{leftKey = rightKey AND CAST(leftDate AS TIMESTAMP) BETWEEN rightTime - 
INTERVAL 1 DAY AND rightTime}}.
Left {{(1, day 1)}} is never emitted, and its row stays in state, however far 
the watermark moves.

h2. Fix outline

Evict by the cast expression that the condition bounds, not by the event time. 
The cutoff is
already in the cast's units (microseconds of a TIMESTAMP), so the eviction 
predicate becomes
{{CAST(leftDate AS TIMESTAMP) <= cutoff}}, and a NULL cast value is evicted 
too. State format 4
would order its index by the cast value. With this, the input without a 
watermark in the second
example is also evicted, and its unmatched rows are emitted. Note that DATE and 
TIMESTAMP_NTZ
casts depend on the session time zone.




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

---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]

Reply via email to