Nikolina Vraneš created SPARK-58237:
---------------------------------------
Summary: Fix BIN BY multi-day civil-time bin boundaries to use the
absolute grid
Key: SPARK-58237
URL: https://issues.apache.org/jira/browse/SPARK-58237
Project: Spark
Issue Type: Sub-task
Components: SQL
Affects Versions: 5.0.0
Reporter: Nikolina Vraneš
h2. Summary
For {{TIMESTAMP}} (LTZ) inputs in a non-UTC session, {{BinByExec}} placed
multi-day civil-time bin boundaries by forward-walking (each bin's end reused
as the next bin's start) instead of computing each boundary independently from
the origin on the absolute grid {{bin_start(k) = timestampAddDayTime(origin, k
* width)}} that {{time_bucket}} uses. Because {{timestampAddDayTime}} splits
the width into a calendar-day part (which absorbs DST: a calendar day is 23h or
25h of real time across a transition) plus a real-microsecond remainder, the
operation is not associative across a DST or offset transition, so the walk
drifts off the grid.
h2. Symptoms
For a width with a non-whole-day part (for example {{INTERVAL '36' HOUR}})
whose bins span a DST transition, boundaries are off by the transition hour
from the second emitted bin onward. Two consequences:
* The same calendar bin is reported with different {{bin_start}} / {{bin_end}}
depending on where a row's range began (a row that walked into bin N versus a
row that started in bin N), so {{GROUP BY bin_start}} fragments one bin into
two groups an hour apart.
* At a whole-day zone skip (for example Pacific/Apia on 2011-12-30, which does
not exist in that zone), two consecutive grid boundaries collapse to the same
instant.
Whole-day widths ({{INTERVAL '1' DAY}}, {{'2' DAY}}) are unaffected because the
walk and the grid agree when the width has no sub-day remainder. Sub-day widths
use the UTC fast path and are already grid-aligned. {{TIMESTAMP_NTZ}} inputs
use UTC arithmetic and are unaffected.
The defect was latent in a code path exercised only by the emission of the
second and later bins of a row, which {{time_bucket}} does not have (it returns
one bucket per row), so the shared {{time_bucket}} tests could not surface it,
and the existing {{BinBySuite}} DST tests all used whole-day widths.
h2. Fix
Compute every bin boundary on the absolute grid, index-driven, matching the
grid {{time_bucket}} uses:
* {{DateTimeUtils.timeBucketDTInterval}} is factored into
{{timeBucketIndexDTInterval}} (the bucket index containing a timestamp) and
{{timeBucketBoundaryDTInterval}} (the grid boundary of a given index), with
{{timeBucketDTInterval}} now defined as {{boundary(index(ts))}}. This is
behavior-preserving for {{time_bucket}}; the existing {{DateTimeUtilsSuite}}
cases are the regression guard.
* {{BinByExec}} advances the bucket index and reads each boundary off the grid
via {{timeBucketBoundaryDTInterval}}, rather than walking with
{{timestampAddDayTime}}. The zero-length-range path computes {{bin_end}} as the
next grid boundary for the same reason.
The collapsed zero-width bin at a whole-day zone skip is emitted as a ratio-0
row; the real bins still tile the range so ratios sum to 1.0.
h2. Tests
* {{DateTimeUtilsSuite}}: a round-trip test that {{boundary(index(ts)) ==
timeBucketDTInterval(ts)}} across sub-day, 36h spring-forward, and fall-back
cases, plus a whole-day Apia skip where two consecutive boundaries collapse.
* {{BinBySuite}}: multi-bin 36h boundaries across spring-forward land on the
grid; two rows reaching the same bin from different starts report the identical
boundary; a whole-day Apia skip emits the zero-width bin; a zero-length range
under a 36h width places {{bin_end}} on the grid.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
