Repository navigation
RANGE window frame boundary overflow diverges from Spark for DATE and DECIMAL ORDER BY #5022
Description
Summary
Native RANGE window frames with an explicit PRECEDING/FOLLOWING offset diverge from Spark when the boundary arithmetic current +/- offset overflows. This affects both DATE ORDER BY (#4974) and DECIMAL ORDER BY (#4987).
Behavior
On boundary overflow:
- Spark computes the boundary through normal expression evaluation and produces a concrete value.
DATE: viaDateAdd, which is plainInt + Intwith silent wraparound (notMath.addExact, not ANSI-gated). It never throws and yields a wrapped date.DECIMAL: via normal decimal arithmetic, returningNULLunder non-ANSI and throwing under ANSI.
- Comet/DataFusion computes the boundary via
ScalarValue::add/sub. For bothDate32 + IntervalDayTimeandDecimal128near max precision, the arithmetic returnsErron overflow, and the window frame collapses the bound to the partition edge.
So the two engines produce different frames (and under ANSI, Spark throws where Comet does not).
Practical likelihood
DECIMAL: rare but plausible, needs values nearDecimal128max precision.DATE: extremely remote, overflowingDate32needs an offset of roughly 2.1 billion days (~5.88 million years) from a realistic date.
Options
- Make the native boundary overflow match Spark (best): wrap for
DATE,NULL/throw forDECIMALdepending on ANSI. This likely means upstream DataFusion work. - Mark the affected cases
Incompatible(Some(...))and gate the native path behindspark.comet.operator.<name>.allowIncompatible, falling back to Spark by default. Follows theCometDataWritingCommandprecedent.
Whichever route, a test at the type boundary should pin the chosen behavior rather than avoiding the edge.
Related
- feat: native RANGE window frames with explicit offset on DATE ORDER BY #4974 (DATE, currently runs native by default, undocumented)
- feat: native RANGE window frames with explicit offset on " DECIMAL " ORDER BY #4987 (DECIMAL, currently runs native by default, documented as a known difference)
- Both are part of Support RANGE window frames with an explicit offset on DATE or DECIMAL ORDER BY #4834.
Activity
0lai0 commentedon Jul 25, 2026
Thanks @andygrove. I think make it actually compatible would be better.
I'm happy to take the upstream DataFusion work for this.
0lai0 commentedon Jul 25, 2026
I looked into how Spark computes the RANGE frame bound, and I think it explains the divergence.
Spark builds the bound expression based on the ORDER BY column's data type, in
WindowEvaluatorFactoryBase.createBoundOrdering:DateAdd, which is a plainInt + Int(noaddExact, no ANSI check), so it silently wraps on overflow.DecimalAddNoOverflowCheck, which deliberately skips the overflow check, so it neither returns NULL nor throws.So Spark's bound compute is intentionally overflow-tolerant. The bound value is only used as a comparison key (not stored as a result), so wrapping / skipping the check is safe and keeps the frame boundary consistent with the actual row ordering.
DataFusion computes the frame bound in
window_state.rsusingadd_checked/sub_checked. On overflow these returnErr, and the bound collapses to the partition edge, that's where the result diverges from Spark.So making the DATE and DECIMAL cases actually compatible would mean changing this overflow behavior on the DataFusion side.
Note the collapse is a deliberate choice, so this would likely need to be an opt-in behavior rather than changing the default for all DataFusion users.
Is this the right direction?
If so, I'm happy to do the upstream DataFusion work.
docs: document known correctness issues in the compatibility guide (#…
andygrove commentedon Jul 29, 2026
I change priority from criticial to high given that this is fairly unlikely.
3 remaining items
docs: drop compatibility notes for bugs that are now fixed (#5154)
docs: document known correctness issues in the compatibility guide (a…
docs: drop compatibility notes for bugs that are now fixed (apache#5154)
kazuyukitanimura commentedon Sep 16, 2026
@comphead let's follow up.
0lai0 commentedon Oct 18, 2026
An update on where this stands, and a correction to the description.
Correction. The description says Spark returns
NULLunder non-ANSI and throws under ANSI when aDECIMALbound overflows. That is not what Spark does.WindowEvaluatorFactoryBasebuilds aDECIMALbound withDecimalAddNoOverflowCheck, which neither returnsNULLnor throws: it keeps the exact value and compares against it. So my earlier comment here, thatDECIMALneeds the ANSI-mode work in apache/datafusion#23929, was based on the wrong premise.The actual divergence is the same for both types: when the native bound overflows, DataFusion takes the partition edge as the bound, which also takes in the
NULLkeys sorted at that edge, while Spark's exact bound leaves them out. For example,DECIMAL(38,0)values[0, N, NULL]withN = 9e37andORDER BY d ASC NULLS LAST RANGE BETWEEN N PRECEDING AND N FOLLOWINGgive[2,2,1]in Spark and[2,3,1]natively.DECIMAL. #4987 falls back when the largest value of the
ORDER BYtype plus the offset can exceed2^127 - 1unscaled, which onlyDECIMAL(38, s)with a very large offset reaches. Everything else runs natively.DATE. #4974 (merged) falls back for offsets that can wrap Spark's
Intaddition, but the native bound already overflows once it leaves chrono's date range (about year 262142), and that pulls inNULLkeys the same way. # lowers the threshold to 92093340 days, the largest offset that keeps the bound inside chrono's range for every date in Spark's supported range.@andygrove I can't edit the issue body. Could you update it to the corrected Spark behavior? Once #4987 and # merge, the last one can close this issue.
andygrove commentedon Oct 19, 2026
Thanks for the update @0lai0 . Looks like one issue/PR number is missing from
and # merge?0lai0 commentedon Oct 20, 2026
Ohh sorry that it was a placeholder for the DATE follow-up PR, which isn't open yet. I've edited the comment, and I'll link the PR here once it's up. Thanks
docs: [branch-1.2] document known differences new in 1.2.0 in the upg…