Skip to content

Commit fca52aa

Browse files
namanjain24-sudoclaudegabotechs
authored
fix: truncate scalar timestamps in their own unit in date_trunc (apache#25498)
## Which issue does this PR close? - Closes apache#25432. ## Rationale for this change `process_scalar` always went through `general_date_trunc`, which converts the value to nanoseconds before truncating. A `Timestamp(Second)`, `Timestamp(Millisecond)` or `Timestamp(Microsecond)` value outside the nanosecond range (after 2262 or before 1677) therefore failed with `Timestamp ... out of range`. `process_array` does not convert for the fine granularities: when `granularity.is_fine_granularity()`, or the value has no timezone and the granularity is `hour` or `day`, it truncates in the input's own unit with plain arithmetic. So the same value succeeded in a column and failed as a scalar: ```sql SELECT date_trunc('second', to_timestamp_seconds(10000000000)); -- Execution error: Timestamp 10000000000 out of range SELECT date_trunc('second', to_timestamp_seconds(ts)) FROM (VALUES (10000000000)) AS t(ts); -- 2286-11-20T17:46:40 ``` ## What changes are included in this PR? The scalar path now takes the same route as the array path: - `truncates_in_input_unit` is the condition `process_array` already used, now shared by both paths. - `fine_granularity_unit` is the unit table from `general_date_trunc_array_fine_granularity`, moved out so both paths read it. - `date_trunc_fine_granularity` truncates one value in its own unit, with the checked subtraction and error message the array path already used for values near `i64::MIN`. The array path's slow branch now calls it too. For those granularities, a scalar and a one-row column now run the same function, so they accept the same values and give the same result. Everything else (`week`, `month`, `quarter`, `year`, and `hour`/`day` with a timezone) still goes through `general_date_trunc` on both paths, so it is unchanged and still limited to the nanosecond range for both scalars and columns. One existing test changes. `date_trunc('hour', arrow_cast(9223372036854775807, 'Timestamp(Second, None)'))` was expected to fail with `Timestamp 9223372036854775807 out of range`. That test comes from apache#22262, whose goal was an error instead of a panic, and a column holding that value already succeeds. As a scalar it now succeeds too, so the test uses `week`, which still converts to nanoseconds on both paths and so still covers the error. ## What is the testing strategy for this PR? - New unit test `scalar_and_array_accept_timestamps_beyond_nanosecond_range` evaluates `date_trunc` on a scalar and on a one-row column with the same value, for all three coarser-than-nanosecond units, with and without a timezone, and for values before 1677 and after 2262, and checks that both succeed with the same result. - New sqllogictest queries in `datetime/timestamps.slt` cover the issue's scalar and column queries, plus millisecond and microsecond scalars. - With the scalar change disabled, the new unit test fails, and the three new scalar queries fail with the error from the issue (`Timestamp 10000000000 out of range`, etc.), while the column query passes as it does on `main`. - `cargo test -p datafusion-functions` (384 passed), the full sqllogictest suite (520 files), `cargo fmt` and `cargo clippy -p datafusion-functions --all-targets --all-features -- -D warnings` pass. ## Are there any user-facing changes? `date_trunc` on a scalar timestamp outside the nanosecond range now returns the same result as on a column, instead of an out-of-range error, for `microsecond`, `millisecond`, `second` and `minute`, and for `hour` and `day` without a timezone. No API changes. --------- Co-authored-by: Claude Sonnet 5 <[email protected]> Co-authored-by: Gabriel <[email protected]>
1 parent 416002a commit fca52aa

2 files changed

Lines changed: 204 additions & 44 deletions

File tree

‎datafusion/functions/src/datetime/date_trunc.rs‎

Lines changed: 180 additions & 43 deletions
Original file line numberDiff line numberDiff line change
@@ -272,14 +272,7 @@ impl ScalarUDFImpl for DateTruncFunc {
272272
let parsed_tz = parse_tz(tz_opt)?;
273273
let array = as_primitive_array::<T>(array)?;
274274

275-
// fast path for fine granularity
276-
// For modern timezones, it's correct to truncate "minute" in this way.
277-
// Both datafusion and arrow are ignoring historical timezone's non-minute granularity
278-
// bias (e.g., Asia/Kathmandu before 1919 is UTC+05:41:16).
279-
// In UTC, "hour" and "day" have uniform durations and can be truncated with simple arithmetic
280-
if granularity.is_fine_granularity()
281-
|| (parsed_tz.is_none() && granularity.is_fine_granularity_utc())
282-
{
275+
if truncates_in_input_unit(granularity, parsed_tz.as_ref()) {
283276
let result = general_date_trunc_array_fine_granularity(
284277
T::UNIT,
285278
array,
@@ -301,10 +294,22 @@ impl ScalarUDFImpl for DateTruncFunc {
301294
tz_opt: Option<&Arc<str>>,
302295
) -> Result<ColumnarValue> {
303296
let parsed_tz = parse_tz(tz_opt)?;
304-
let value = if let Some(v) = v {
305-
Some(general_date_trunc(T::UNIT, *v, parsed_tz, granularity)?)
306-
} else {
307-
None
297+
let value = match v {
298+
// Truncate in the input's own unit, as `process_array` does, so a
299+
// scalar accepts every timestamp a column accepts.
300+
// `general_date_trunc` converts to nanoseconds first and so rejects
301+
// values outside the nanosecond range.
302+
Some(v) if truncates_in_input_unit(granularity, parsed_tz.as_ref()) => {
303+
match fine_granularity_unit(T::UNIT, granularity) {
304+
Some(unit) => {
305+
Some(truncate_to_unit(*v, unit.get(), granularity)?)
306+
}
307+
// `granularity` is no coarser than the input's unit
308+
None => Some(*v),
309+
}
310+
}
311+
Some(v) => Some(general_date_trunc(T::UNIT, *v, parsed_tz, granularity)?),
312+
None => None,
308313
};
309314
let value = ScalarValue::new_timestamp::<T>(value, tz_opt.cloned());
310315
Ok(ColumnarValue::Scalar(value))
@@ -756,30 +761,7 @@ fn general_date_trunc_array_fine_granularity<T: ArrowTimestampType>(
756761
granularity: DatePart,
757762
tz_opt: Option<Arc<str>>,
758763
) -> Result<ArrayRef> {
759-
let unit = match (tu, granularity) {
760-
(Second, DatePart::Minute) => NonZeroI64::new(60),
761-
(Second, DatePart::Hour) => NonZeroI64::new(3600),
762-
(Second, DatePart::Day) => NonZeroI64::new(86400),
763-
764-
(Millisecond, DatePart::Second) => NonZeroI64::new(1_000),
765-
(Millisecond, DatePart::Minute) => NonZeroI64::new(60_000),
766-
(Millisecond, DatePart::Hour) => NonZeroI64::new(3_600_000),
767-
(Millisecond, DatePart::Day) => NonZeroI64::new(86_400_000),
768-
769-
(Microsecond, DatePart::Millisecond) => NonZeroI64::new(1_000),
770-
(Microsecond, DatePart::Second) => NonZeroI64::new(1_000_000),
771-
(Microsecond, DatePart::Minute) => NonZeroI64::new(60_000_000),
772-
(Microsecond, DatePart::Hour) => NonZeroI64::new(3_600_000_000),
773-
(Microsecond, DatePart::Day) => NonZeroI64::new(86_400_000_000),
774-
775-
(Nanosecond, DatePart::Microsecond) => NonZeroI64::new(1_000),
776-
(Nanosecond, DatePart::Millisecond) => NonZeroI64::new(1_000_000),
777-
(Nanosecond, DatePart::Second) => NonZeroI64::new(1_000_000_000),
778-
(Nanosecond, DatePart::Minute) => NonZeroI64::new(60_000_000_000),
779-
(Nanosecond, DatePart::Hour) => NonZeroI64::new(3_600_000_000_000),
780-
(Nanosecond, DatePart::Day) => NonZeroI64::new(86_400_000_000_000),
781-
_ => None,
782-
};
764+
let unit = fine_granularity_unit(tu, granularity);
783765

784766
if let Some(unit) = unit {
785767
let unit = unit.get();
@@ -797,13 +779,7 @@ fn general_date_trunc_array_fine_granularity<T: ArrowTimestampType>(
797779
})
798780
.collect();
799781
let array: PrimitiveArray<T> = if maybe_underflow {
800-
array.try_unary(|value| {
801-
value.checked_sub(value.rem_euclid(unit)).ok_or_else(|| {
802-
exec_datafusion_err!(
803-
"Timestamp {value} out of range after truncating to {granularity}"
804-
)
805-
})
806-
})?
782+
array.try_unary(|value| truncate_to_unit(value, unit, granularity))?
807783
} else {
808784
PrimitiveArray::new(values.into(), array.nulls().cloned())
809785
}
@@ -815,6 +791,57 @@ fn general_date_trunc_array_fine_granularity<T: ArrowTimestampType>(
815791
}
816792
}
817793

794+
/// Whether truncating to `granularity` is plain arithmetic in the input's own
795+
/// time unit: rounding down to a multiple of [`fine_granularity_unit`].
796+
///
797+
/// For modern timezones, it's correct to truncate "minute" in this way.
798+
/// Both datafusion and arrow are ignoring historical timezone's non-minute granularity
799+
/// bias (e.g., Asia/Kathmandu before 1919 is UTC+05:41:16).
800+
/// In UTC, "hour" and "day" have uniform durations and can be truncated with simple arithmetic
801+
fn truncates_in_input_unit(granularity: DatePart, tz: Option<&Tz>) -> bool {
802+
granularity.is_fine_granularity()
803+
|| (tz.is_none() && granularity.is_fine_granularity_utc())
804+
}
805+
806+
/// The length of `granularity` in `tu`, or `None` when `granularity` is no
807+
/// coarser than `tu`, so a value in `tu` is already truncated to it.
808+
fn fine_granularity_unit(tu: TimeUnit, granularity: DatePart) -> Option<NonZeroI64> {
809+
match (tu, granularity) {
810+
(Second, DatePart::Minute) => NonZeroI64::new(60),
811+
(Second, DatePart::Hour) => NonZeroI64::new(3600),
812+
(Second, DatePart::Day) => NonZeroI64::new(86400),
813+
814+
(Millisecond, DatePart::Second) => NonZeroI64::new(1_000),
815+
(Millisecond, DatePart::Minute) => NonZeroI64::new(60_000),
816+
(Millisecond, DatePart::Hour) => NonZeroI64::new(3_600_000),
817+
(Millisecond, DatePart::Day) => NonZeroI64::new(86_400_000),
818+
819+
(Microsecond, DatePart::Millisecond) => NonZeroI64::new(1_000),
820+
(Microsecond, DatePart::Second) => NonZeroI64::new(1_000_000),
821+
(Microsecond, DatePart::Minute) => NonZeroI64::new(60_000_000),
822+
(Microsecond, DatePart::Hour) => NonZeroI64::new(3_600_000_000),
823+
(Microsecond, DatePart::Day) => NonZeroI64::new(86_400_000_000),
824+
825+
(Nanosecond, DatePart::Microsecond) => NonZeroI64::new(1_000),
826+
(Nanosecond, DatePart::Millisecond) => NonZeroI64::new(1_000_000),
827+
(Nanosecond, DatePart::Second) => NonZeroI64::new(1_000_000_000),
828+
(Nanosecond, DatePart::Minute) => NonZeroI64::new(60_000_000_000),
829+
(Nanosecond, DatePart::Hour) => NonZeroI64::new(3_600_000_000_000),
830+
(Nanosecond, DatePart::Day) => NonZeroI64::new(86_400_000_000_000),
831+
_ => None,
832+
}
833+
}
834+
835+
/// Rounds `value` down to a multiple of `unit`, the length of `granularity` in
836+
/// the value's time unit, or errors if that falls below `i64::MIN`.
837+
fn truncate_to_unit(value: i64, unit: i64, granularity: DatePart) -> Result<i64> {
838+
value.checked_sub(value.rem_euclid(unit)).ok_or_else(|| {
839+
exec_datafusion_err!(
840+
"Timestamp {value} out of range after truncating to {granularity}"
841+
)
842+
})
843+
}
844+
818845
// truncates a single value with the given timeunit to the specified granularity
819846
fn general_date_trunc(
820847
tu: TimeUnit,
@@ -1434,6 +1461,116 @@ mod tests {
14341461
}
14351462
}
14361463

1464+
/// Evaluates `date_trunc(granularity, value)` once on a scalar and once on a
1465+
/// one-row column holding the same value.
1466+
fn date_trunc_scalar_and_array(
1467+
granularity: &str,
1468+
value: ScalarValue,
1469+
) -> (
1470+
datafusion_common::Result<ScalarValue>,
1471+
datafusion_common::Result<ScalarValue>,
1472+
) {
1473+
let invoke = |arg: ColumnarValue| {
1474+
let data_type = value.data_type();
1475+
let args = ScalarFunctionArgs {
1476+
args: vec![ColumnarValue::Scalar(ScalarValue::from(granularity)), arg],
1477+
arg_fields: vec![
1478+
Field::new("a", DataType::Utf8, false).into(),
1479+
Field::new("b", data_type.clone(), true).into(),
1480+
],
1481+
number_rows: 1,
1482+
return_field: Field::new("f", data_type, true).into(),
1483+
config_options: Arc::new(ConfigOptions::default()),
1484+
};
1485+
match DateTruncFunc::new().invoke_with_args(args)? {
1486+
ColumnarValue::Scalar(result) => Ok(result),
1487+
ColumnarValue::Array(result) => ScalarValue::try_from_array(&result, 0),
1488+
}
1489+
};
1490+
(
1491+
invoke(ColumnarValue::Scalar(value.clone())),
1492+
invoke(ColumnarValue::Array(value.to_array().unwrap())),
1493+
)
1494+
}
1495+
1496+
/// A timestamp beyond the nanosecond range is truncated the same way as a
1497+
/// scalar and as a column: neither converts it to nanoseconds first.
1498+
#[test]
1499+
fn scalar_and_array_accept_timestamps_beyond_nanosecond_range() {
1500+
// 2286-11-20T17:46:40, after the last nanosecond timestamp in 2262
1501+
let seconds = 10_000_000_000;
1502+
let utc: Option<Arc<str>> = Some("UTC".into());
1503+
let cases = [
1504+
// Seconds, truncated to a minute, an hour and a day
1505+
(
1506+
ScalarValue::TimestampSecond(Some(seconds + 59), None),
1507+
"minute",
1508+
),
1509+
(
1510+
ScalarValue::TimestampSecond(Some(seconds + 1), None),
1511+
"hour",
1512+
),
1513+
(ScalarValue::TimestampSecond(Some(seconds + 1), None), "day"),
1514+
// The granularity is the input's own unit
1515+
(ScalarValue::TimestampSecond(Some(seconds), None), "second"),
1516+
// With a time zone
1517+
(
1518+
ScalarValue::TimestampSecond(Some(seconds + 59), utc.clone()),
1519+
"minute",
1520+
),
1521+
// Milliseconds and microseconds, truncated to a coarser unit
1522+
(
1523+
ScalarValue::TimestampMillisecond(Some(seconds * 1_000 + 999), None),
1524+
"second",
1525+
),
1526+
(
1527+
ScalarValue::TimestampMicrosecond(Some(seconds * 1_000_000 + 999), None),
1528+
"millisecond",
1529+
),
1530+
// Before the epoch, with a time zone
1531+
(
1532+
ScalarValue::TimestampMicrosecond(Some(-seconds * 1_000_000 - 1), utc),
1533+
"second",
1534+
),
1535+
];
1536+
for (value, granularity) in cases {
1537+
let (scalar, array) = date_trunc_scalar_and_array(granularity, value.clone());
1538+
let scalar = scalar.unwrap_or_else(|e| {
1539+
panic!("scalar date_trunc('{granularity}', {value:?}) failed: {e}")
1540+
});
1541+
assert_eq!(
1542+
scalar,
1543+
array.unwrap(),
1544+
"date_trunc('{granularity}', {value:?})"
1545+
);
1546+
}
1547+
1548+
// `date_trunc('second', to_timestamp_seconds(10000000000))` returns its input
1549+
// rather than an out of range error.
1550+
// See <https://github.com/apache/datafusion/issues/25432>.
1551+
let (scalar, _) = date_trunc_scalar_and_array(
1552+
"second",
1553+
ScalarValue::TimestampSecond(Some(seconds), None),
1554+
);
1555+
assert_eq!(
1556+
scalar.unwrap(),
1557+
ScalarValue::TimestampSecond(Some(seconds), None)
1558+
);
1559+
}
1560+
1561+
/// The scalar fast path shares the same underflow check as the array
1562+
/// path: truncating a value within one unit of `i64::MIN` to a coarser
1563+
/// granularity in its own unit must error rather than wrap around.
1564+
#[test]
1565+
fn scalar_and_array_reject_fine_granularity_underflow() {
1566+
let (scalar, array) = date_trunc_scalar_and_array(
1567+
"minute",
1568+
ScalarValue::TimestampSecond(Some(i64::MIN), None),
1569+
);
1570+
assert!(scalar.is_err(), "expected scalar path to reject underflow");
1571+
assert!(array.is_err(), "expected array path to reject underflow");
1572+
}
1573+
14371574
fn assert_fine_granularity_underflow<T: ArrowTimestampType>(
14381575
array: PrimitiveArray<T>,
14391576
granularity: DatePart,

‎datafusion/sqllogictest/test_files/datetime/timestamps.slt‎

Lines changed: 24 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -2740,9 +2740,32 @@ SELECT arrow_typeof(date_trunc('hour', TIME '14:30:45'));
27402740
----
27412741
Time64(ns)
27422742

2743+
# date_trunc accepts the same timestamps as a scalar as it does as a column,
2744+
# including ones beyond the range of a nanosecond timestamp
2745+
query P
2746+
SELECT date_trunc('second', to_timestamp_seconds(10000000000));
2747+
----
2748+
2286-11-20T17:46:40
2749+
2750+
query P
2751+
SELECT date_trunc('second', to_timestamp_seconds(ts))
2752+
FROM (VALUES (10000000000)) AS timestamps(ts);
2753+
----
2754+
2286-11-20T17:46:40
2755+
2756+
query P
2757+
SELECT date_trunc('minute', to_timestamp_millis(10000000019999));
2758+
----
2759+
2286-11-20T17:46:00
2760+
2761+
query P
2762+
SELECT date_trunc('millisecond', to_timestamp_micros(10000000000123456));
2763+
----
2764+
2286-11-20T17:46:40.123
2765+
27432766
query error DataFusion error: Execution error: Timestamp 9223372036854775807 out of range
27442767
SELECT date_trunc(
2745-
'hour',
2768+
'week',
27462769
arrow_cast(9223372036854775807, 'Timestamp(Second, None)')
27472770
);
27482771

0 commit comments

Comments
 (0)