RANGE window frame boundary overflow diverges from Spark for DATE and DECIMAL ORDER BY

[andygrove]

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: via DateAdd, which is plain Int + Int with silent wraparound (not Math.addExact, not ANSI-gated). It never throws and yields a wrapped date.
  • DECIMAL: via normal decimal arithmetic, returning NULL under non-ANSI and throwing under ANSI.
• Comet/DataFusion computes the boundary via ScalarValue::add/sub. For both Date32 + IntervalDayTime and Decimal128 near max precision, the arithmetic returns Err on 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 near Decimal128 max precision.
• DATE: extremely remote, overflowing Date32 needs an offset of roughly 2.1 billion days (~5.88 million years) from a realistic date.

Options

1. Make the native boundary overflow match Spark (best): wrap for DATE, NULL/throw for DECIMAL depending on ANSI. This likely means upstream DataFusion work.
2. Mark the affected cases Incompatible(Some(...)) and gate the native path behind spark.comet.operator.<name>.allowIncompatible, falling back to Spark by default. Follows the CometDataWritingCommand precedent.

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.

[0lai0]

Thanks @andygrove. I think make it actually compatible would be better.
I'm happy to take the upstream DataFusion work for this.

[0lai0]

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:

1. DATE → DateAdd, which is a plain Int + Int (no addExact, no ANSI check), so it silently wraps on overflow.
2. DECIMAL → 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.rs using add_checked / sub_checked. On overflow these return Err, 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.