Skip to content

Commit 4ec76ad

Browse files
authored
fix(spark): floor instead of truncate in unix_millis/unix_seconds for pre-epoch timestamp (apache#26129)
## Which issue does this PR close? - Closes apache#26128. ## Rationale for this change The Spark-compatible `unix_millis` and `unix_seconds` functions return different results from Spark for timestamps before 1970-01-01 that have a sub-unit component. Spark floors the result, but DataFusion truncates toward zero. For example, `unix_millis` of a timestamp 1 µs before the epoch is `-1` in Spark and `0` in DataFusion. Results for non-negative timestamps and exact multiples already match. The cause is that `SparkUnixTimestamp::simplify` rewrites the call as a cast to a coarser `Timestamp` unit, and Arrow's cast truncates toward zero. Spark computes these values with `Math.floorDiv` (`TimestampToLongBase`). ## What changes are included in this PR? In `SparkUnixTimestamp::simplify` (`datafusion/spark/src/function/datetime/unix.rs`): - When the input timestamp unit is finer than the target unit, the raw `Int64` ticks are divided by the unit ratio (1e3, 1e6 or 1e9). Then `1` is subtracted when the remainder is negative: `ticks / d - CAST(ticks % d < 0 AS BIGINT)`. This is an exact floor division over the full `i64` range, with no intermediate unit conversion, so it adds no new overflow cases. - When the input unit is the same as or coarser than the target, the existing direct cast is kept, so results for those inputs don't change. - NULL handling doesn't change; all operators involved propagate NULL. ## What is the testing strategy for this PR? New sqllogictest cases in `datafusion/sqllogictest/test_files/spark/datetime/unix.slt`: - **Runtime path:** a table column (not just literals, so constant folding isn't the only path tested) holding pre-epoch values with and without a remainder, a positive value and NULL. Each is checked for both `unix_millis` and `unix_seconds`. - **Constant-folded path:** the same cases written as literals. - **Other input units:** `Timestamp(Nanosecond)` to millis and micros, `Timestamp(Millisecond)` to seconds, and an unchanged conversion to a finer unit (`Millisecond` to micros). - **Extremes:** `i64::MIN` microseconds to seconds, and a large millisecond value to seconds, to guard against overflow. Expected values for microsecond inputs came from Spark 4.0.0 (pyspark with `spark.sql.session.timeZone=UTC`), not from this implementation. Spark has no nanosecond timestamps, so the nanosecond cases use the same floor rule. Local runs: all `spark/*` sqllogictests, `cargo test -p datafusion-spark`, `cargo fmt --all -- --check` and `cargo clippy -p datafusion-spark --all-targets -- -D warnings` pass ## Are there any user-facing changes? Yes, as a bug fix. `unix_millis` and `unix_seconds` now return the Spark-compatible (floored) value for pre-epoch timestamps with a sub-unit component. No public API changes, so no `api change` label is needed. --- AI assistance: I used an AI coding assistant to help find this divergence (by differential testing against Spark) and to draft the change. A separate AI review pass caught overflow edge cases, which the tests now cover. I reproduced the behavior against Spark 4.0.0 myself and reviewed the change line by line.
1 parent d75fd7b commit 4ec76ad

2 files changed

Lines changed: 192 additions & 4 deletions

File tree

  • datafusion
    • spark/src/function/datetime
    • sqllogictest/test_files/spark/datetime

‎datafusion/spark/src/function/datetime/unix.rs‎

Lines changed: 128 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -4,10 +4,11 @@
44

55
use std::sync::Arc;
66

7-
use arrow::datatypes::{DataType, Field, FieldRef, TimeUnit};
7+
use arrow::datatypes::{DataType, Field, FieldRef, Int64Type, TimeUnit};
8+
use datafusion_common::cast::as_int64_array;
89
use datafusion_common::types::logical_date;
910
use datafusion_common::utils::take_function_args;
10-
use datafusion_common::{Result, internal_err};
11+
use datafusion_common::{Result, ScalarValue, internal_err};
1112
use datafusion_expr::simplify::{ExprSimplifyResult, SimplifyContext};
1213
use datafusion_expr::{
1314
Coercion, ColumnarValue, Expr, ExprSchemable, ReturnFieldArgs, ScalarFunctionArgs,
@@ -111,6 +112,24 @@ impl SparkUnixTimestamp {
111112
pub fn seconds() -> Self {
112113
Self::new("unix_seconds", TimeUnit::Second)
113114
}
115+
116+
fn downscale_divisor(&self, input_type: &DataType) -> Option<i64> {
117+
match (input_type, self.time_unit) {
118+
(DataType::Timestamp(TimeUnit::Millisecond, _), TimeUnit::Second)
119+
| (DataType::Timestamp(TimeUnit::Microsecond, _), TimeUnit::Millisecond)
120+
| (DataType::Timestamp(TimeUnit::Nanosecond, _), TimeUnit::Microsecond) => {
121+
Some(1_000)
122+
}
123+
(DataType::Timestamp(TimeUnit::Microsecond, _), TimeUnit::Second)
124+
| (DataType::Timestamp(TimeUnit::Nanosecond, _), TimeUnit::Millisecond) => {
125+
Some(1_000_000)
126+
}
127+
(DataType::Timestamp(TimeUnit::Nanosecond, _), TimeUnit::Second) => {
128+
Some(1_000_000_000)
129+
}
130+
_ => None,
131+
}
132+
}
114133
}
115134

116135
impl ScalarUDFImpl for SparkUnixTimestamp {
@@ -131,8 +150,30 @@ impl ScalarUDFImpl for SparkUnixTimestamp {
131150
Ok(Arc::new(Field::new(self.name(), DataType::Int64, nullable)))
132151
}
133152

134-
fn invoke_with_args(&self, _args: ScalarFunctionArgs) -> Result<ColumnarValue> {
135-
internal_err!("invoke_with_args should not be called on `{}`", self.name())
153+
fn invoke_with_args(&self, args: ScalarFunctionArgs) -> Result<ColumnarValue> {
154+
let [ts] = take_function_args(self.name(), args.args)?;
155+
let Some(divisor) = self.downscale_divisor(&ts.data_type()) else {
156+
return ts
157+
.cast_to(
158+
&DataType::Timestamp(self.time_unit, Some("UTC".into())),
159+
None,
160+
)?
161+
.cast_to(&DataType::Int64, None);
162+
};
163+
// Spark floors negative fractional timestamps, unlike Arrow's
164+
// truncating timestamp casts. The argument has already been evaluated once.
165+
match ts.cast_to(&DataType::Int64, None)? {
166+
ColumnarValue::Scalar(ScalarValue::Int64(ticks)) => {
167+
Ok(ColumnarValue::Scalar(ScalarValue::Int64(
168+
ticks.map(|ticks| ticks.div_euclid(divisor)),
169+
)))
170+
}
171+
ColumnarValue::Array(ticks) => Ok(ColumnarValue::Array(Arc::new(
172+
as_int64_array(&ticks)?
173+
.unary::<_, Int64Type>(|ticks| ticks.div_euclid(divisor)),
174+
))),
175+
other => internal_err!("Expected Int64 ticks, got {other:?}"),
176+
}
136177
}
137178

138179
fn simplify(
@@ -141,6 +182,12 @@ impl ScalarUDFImpl for SparkUnixTimestamp {
141182
info: &SimplifyContext,
142183
) -> Result<ExprSimplifyResult> {
143184
let [ts] = take_function_args(self.name(), args)?;
185+
if self
186+
.downscale_divisor(&ts.get_type(info.schema())?)
187+
.is_some()
188+
{
189+
return Ok(ExprSimplifyResult::Original(vec![ts]));
190+
}
144191
Ok(ExprSimplifyResult::Simplified(
145192
ts.cast_to(
146193
&DataType::Timestamp(self.time_unit, Some("UTC".into())),
@@ -150,3 +197,80 @@ impl ScalarUDFImpl for SparkUnixTimestamp {
150197
))
151198
}
152199
}
200+
201+
#[cfg(test)]
202+
mod tests {
203+
use super::*;
204+
use arrow::array::Int64Array;
205+
use datafusion::prelude::SessionContext;
206+
use datafusion_expr::{ScalarUDF, create_udf};
207+
use std::sync::atomic::{AtomicUsize, Ordering};
208+
209+
#[tokio::test]
210+
async fn test_unix_timestamp_evaluates_volatile_input_once() -> Result<()> {
211+
for (function, unit, divisor) in [
212+
(SparkUnixTimestamp::seconds(), "Millisecond", 1_000),
213+
(SparkUnixTimestamp::milliseconds(), "Microsecond", 1_000),
214+
(SparkUnixTimestamp::microseconds(), "Nanosecond", 1_000),
215+
(SparkUnixTimestamp::seconds(), "Microsecond", 1_000_000),
216+
(SparkUnixTimestamp::milliseconds(), "Nanosecond", 1_000_000),
217+
(SparkUnixTimestamp::seconds(), "Nanosecond", 1_000_000_000),
218+
] {
219+
let function = ScalarUDF::from(function);
220+
for array_input in [false, true] {
221+
let ctx = SessionContext::new();
222+
let calls = Arc::new(AtomicUsize::new(0));
223+
let udf_calls = Arc::clone(&calls);
224+
ctx.register_udf(create_udf(
225+
"volatile_ticks",
226+
vec![DataType::Int64],
227+
DataType::Int64,
228+
Volatility::Volatile,
229+
Arc::new(move |args| {
230+
let ticks =
231+
if udf_calls.fetch_add(1, Ordering::SeqCst).is_multiple_of(2)
232+
{
233+
-1
234+
} else {
235+
divisor
236+
};
237+
Ok(match &args[0] {
238+
ColumnarValue::Scalar(_) => {
239+
ColumnarValue::Scalar(ScalarValue::Int64(Some(ticks)))
240+
}
241+
ColumnarValue::Array(array) => ColumnarValue::Array(
242+
Arc::new(Int64Array::from(vec![ticks; array.len()])),
243+
),
244+
})
245+
}),
246+
));
247+
ctx.register_udf(function.clone());
248+
let (input, from, expected) = if array_input {
249+
(
250+
"column1",
251+
" FROM (VALUES (0), (1))",
252+
vec![Some(-1), Some(-1)],
253+
)
254+
} else {
255+
("0", "", vec![Some(-1)])
256+
};
257+
let query = format!(
258+
"SELECT {}(arrow_cast(volatile_ticks({input}), \
259+
'Timestamp({unit}, None)')) AS result{from}",
260+
function.name()
261+
);
262+
let batches = ctx.sql(&query).await?.collect().await?;
263+
assert_eq!(calls.load(Ordering::SeqCst), 1, "{query}");
264+
let actual = batches
265+
.iter()
266+
.map(|batch| as_int64_array(batch.column(0)))
267+
.collect::<Result<Vec<_>>>()?
268+
.into_iter()
269+
.flat_map(|array| array.iter())
270+
.collect::<Vec<_>>();
271+
assert_eq!(actual, expected, "{query}");
272+
}
273+
}
274+
Ok(())
275+
}
276+
}

‎datafusion/sqllogictest/test_files/spark/datetime/unix.slt‎

Lines changed: 64 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -119,3 +119,67 @@ NULL
119119

120120
query error Function 'unix_seconds' requires Timestamp, but received String \(DataType: Utf8View\)
121121
SELECT unix_seconds('1970-01-01 00:00:01Z'::string);
122+
123+
# Spark floors (Math.floorDiv) when converting to a coarser unit, so pre-epoch
124+
# timestamps with a sub-unit remainder round toward negative infinity.
125+
statement ok
126+
CREATE TABLE unix_floor_us AS SELECT arrow_cast(column1, 'Timestamp(Microsecond, None)') AS ts FROM (VALUES
127+
(-1), (-1000), (-1001), (-1000000), (-1000001), (1500), (NULL)
128+
);
129+
130+
query III
131+
SELECT arrow_cast(ts, 'Int64'), unix_millis(ts), unix_seconds(ts) FROM unix_floor_us;
132+
----
133+
-1 -1 -1
134+
-1000 -1 -1
135+
-1001 -2 -1
136+
-1000000 -1000 -1
137+
-1000001 -1001 -2
138+
1500 1 0
139+
NULL NULL NULL
140+
141+
statement ok
142+
DROP TABLE unix_floor_us;
143+
144+
# Same results when the expressions are constant-folded
145+
query II
146+
SELECT
147+
unix_millis(arrow_cast(-1001, 'Timestamp(Microsecond, None)')),
148+
unix_seconds(arrow_cast(-1000001, 'Timestamp(Microsecond, None)'));
149+
----
150+
-2 -2
151+
152+
# Other input units: nanoseconds floor too, milliseconds to seconds floors,
153+
# and conversions to a finer unit are unchanged
154+
query IIII
155+
SELECT
156+
unix_millis(arrow_cast(-1, 'Timestamp(Nanosecond, None)')),
157+
unix_micros(arrow_cast(-1, 'Timestamp(Nanosecond, None)')),
158+
unix_seconds(arrow_cast(-1, 'Timestamp(Millisecond, None)')),
159+
unix_micros(arrow_cast(-1, 'Timestamp(Millisecond, None)'));
160+
----
161+
-1 -1 -1 -1000
162+
163+
# Nanoseconds to seconds floor negative remainders but preserve exact multiples.
164+
query II
165+
SELECT
166+
unix_seconds(arrow_cast(-1000000001, 'Timestamp(Nanosecond, None)')),
167+
unix_seconds(arrow_cast(-1000000000, 'Timestamp(Nanosecond, None)'));
168+
----
169+
-2 -1
170+
171+
# Timezone metadata does not change the epoch-based result.
172+
query II
173+
SELECT
174+
unix_millis(arrow_cast(-1, 'Timestamp(Microsecond, Some("+02:00"))')),
175+
unix_seconds(arrow_cast(-1, 'Timestamp(Microsecond, Some("+02:00"))'));
176+
----
177+
-1 -1
178+
179+
# Extreme values do not overflow
180+
query II
181+
SELECT
182+
unix_seconds(arrow_cast(-9223372036854775808, 'Timestamp(Microsecond, None)')),
183+
unix_seconds(arrow_cast(9223372036854775, 'Timestamp(Millisecond, None)'));
184+
----
185+
-9223372036855 9223372036854

0 commit comments

Comments
 (0)