Skip to content

Commit 51673d0

Browse files
authored
fix: preserve timestamp precision when coercing mixed time units (#22759)
## Which issue does this PR close? <!-- We generally require a GitHub issue to be filed for all bug fixes and enhancements and this helps us generate change logs for our releases. You can link an issue to this PR using the GitHub syntax. For example `Closes #123` indicates that this PR will close issue #123. --> - Closes #22756. ## Rationale for this change DataFusion coerced Timestamp values with different TimeUnits to the coarser unit which result in a loss of precision and lead to incorrect comparison semantics <!-- Why are you proposing this change? If this is already explained clearly in the issue then this section is not needed. Explaining clearly why changes are proposed helps reviewers understand your changes and offer better suggestions for fixes. --> ## What changes are included in this PR? - Change timestamp time unit coercion to prefer the finer unit. - Add overflow checks for timestamp-to-timestamp casts when converting to a finer unit. <!-- There is no need to duplicate the description in the issue here but it is sometimes worth providing a summary of the individual changes in this PR. --> ## Are these changes tested? Yes. Added unit tests for timestamp coercion and overflow checks, plus sqllogictest coverage for comparison, subtraction, UNION, and COALESCE. <!-- We typically require tests for all PRs in order to: 1. Prevent the code from being accidentally broken by subsequent changes 2. Serve as another way to document the expected behavior of the code If tests are not included in your PR, please explain why (for example, are they covered by existing tests)? --> ## Are there any user-facing changes? Yes. Timestamp operations with mixed time units now coerce to the finer unit. If that conversion would overflow i64, the query returns an overflow error. <!-- If there are user-facing changes then we may require documentation to be updated before approving the PR. --> <!-- If there are any breaking changes to public APIs, please add the `api change` label. -->
1 parent 3c4034c commit 51673d0

6 files changed

Lines changed: 214 additions & 31 deletions

File tree

‎datafusion/common/src/scalar/mod.rs‎

Lines changed: 44 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -148,6 +148,30 @@ pub fn date_to_timestamp_multiplier(
148148
}
149149
}
150150

151+
/// Returns the multiplier that converts the input timestamp representation into
152+
/// the desired timestamp unit, if the conversion requires a multiplication that
153+
/// can overflow an `i64`.
154+
pub fn timestamp_to_timestamp_multiplier(
155+
source_type: &DataType,
156+
target_type: &DataType,
157+
) -> Option<i64> {
158+
let (DataType::Timestamp(source_unit, _), DataType::Timestamp(target_unit, _)) =
159+
(source_type, target_type)
160+
else {
161+
return None;
162+
};
163+
164+
match (source_unit, target_unit) {
165+
(TimeUnit::Second, TimeUnit::Millisecond) => Some(1_000),
166+
(TimeUnit::Second, TimeUnit::Microsecond) => Some(1_000_000),
167+
(TimeUnit::Second, TimeUnit::Nanosecond) => Some(1_000_000_000),
168+
(TimeUnit::Millisecond, TimeUnit::Microsecond) => Some(1_000),
169+
(TimeUnit::Millisecond, TimeUnit::Nanosecond) => Some(1_000_000),
170+
(TimeUnit::Microsecond, TimeUnit::Nanosecond) => Some(1_000),
171+
_ => None,
172+
}
173+
}
174+
151175
/// Ensures the provided value can be represented as a timestamp with the given
152176
/// multiplier. Returns an [`DataFusionError::Execution`] when the converted
153177
/// value would overflow the timestamp range.
@@ -4265,7 +4289,8 @@ impl ScalarValue {
42654289
}
42664290

42674291
if let Some(multiplier) = date_to_timestamp_multiplier(&source_type, target_type)
4268-
&& let Some(value) = self.date_scalar_value_as_i64()
4292+
.or_else(|| timestamp_to_timestamp_multiplier(&source_type, target_type))
4293+
&& let Some(value) = self.temporal_scalar_value_as_i64()
42694294
{
42704295
ensure_timestamp_in_bounds(value, multiplier, &source_type, target_type)?;
42714296
}
@@ -4287,10 +4312,14 @@ impl ScalarValue {
42874312
ScalarValue::try_from_array(&cast_arr, 0)
42884313
}
42894314

4290-
fn date_scalar_value_as_i64(&self) -> Option<i64> {
4315+
fn temporal_scalar_value_as_i64(&self) -> Option<i64> {
42914316
match self {
42924317
ScalarValue::Date32(Some(value)) => Some(i64::from(*value)),
42934318
ScalarValue::Date64(Some(value)) => Some(*value),
4319+
ScalarValue::TimestampSecond(Some(value), _)
4320+
| ScalarValue::TimestampMillisecond(Some(value), _)
4321+
| ScalarValue::TimestampMicrosecond(Some(value), _)
4322+
| ScalarValue::TimestampNanosecond(Some(value), _) => Some(*value),
42944323
_ => None,
42954324
}
42964325
}
@@ -10161,6 +10190,19 @@ mod tests {
1016110190
);
1016210191
}
1016310192

10193+
#[test]
10194+
fn cast_timestamp_to_timestamp_overflow_returns_error() {
10195+
let scalar = ScalarValue::TimestampSecond(Some(i64::MAX), None);
10196+
let err = scalar
10197+
.cast_to(&DataType::Timestamp(TimeUnit::Nanosecond, None))
10198+
.expect_err("expected cast to fail");
10199+
assert!(
10200+
err.to_string()
10201+
.contains("converted value exceeds the representable i64 range"),
10202+
"unexpected error: {err}"
10203+
);
10204+
}
10205+
1016410206
#[test]
1016510207
fn null_dictionary_scalar_produces_null_dictionary_array() {
1016610208
let dictionary_scalar = ScalarValue::Dictionary(

‎datafusion/expr-common/src/columnar_value.rs‎

Lines changed: 79 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -18,17 +18,23 @@
1818
//! [`ColumnarValue`] represents the result of evaluating an expression.
1919
2020
use arrow::{
21-
array::{Array, ArrayRef, Date32Array, Date64Array, NullArray},
21+
array::{
22+
Array, ArrayRef, Date32Array, Date64Array, NullArray, TimestampMicrosecondArray,
23+
TimestampMillisecondArray, TimestampNanosecondArray, TimestampSecondArray,
24+
},
2225
compute::{CastOptions, kernels, max, min},
23-
datatypes::DataType,
26+
datatypes::{DataType, TimeUnit},
2427
util::pretty::pretty_format_columns,
2528
};
2629
use datafusion_common::internal_datafusion_err;
2730
use datafusion_common::{
2831
Result, ScalarValue,
2932
format::DEFAULT_CAST_OPTIONS,
3033
internal_err,
31-
scalar::{date_to_timestamp_multiplier, ensure_timestamp_in_bounds},
34+
scalar::{
35+
date_to_timestamp_multiplier, ensure_timestamp_in_bounds,
36+
timestamp_to_timestamp_multiplier,
37+
},
3238
};
3339
use std::fmt;
3440
use std::sync::Arc;
@@ -319,7 +325,7 @@ fn cast_array_by_name(
319325
) {
320326
datafusion_common::nested_struct::cast_column(array, cast_type, cast_options)
321327
} else {
322-
ensure_date_array_timestamp_bounds(array, cast_type)?;
328+
ensure_temporal_array_timestamp_bounds(array, cast_type)?;
323329
Ok(kernels::cast::cast_with_options(
324330
array,
325331
cast_type,
@@ -328,12 +334,14 @@ fn cast_array_by_name(
328334
}
329335
}
330336

331-
fn ensure_date_array_timestamp_bounds(
337+
fn ensure_temporal_array_timestamp_bounds(
332338
array: &ArrayRef,
333339
cast_type: &DataType,
334340
) -> Result<()> {
335341
let source_type = array.data_type().clone();
336-
let Some(multiplier) = date_to_timestamp_multiplier(&source_type, cast_type) else {
342+
let Some(multiplier) = date_to_timestamp_multiplier(&source_type, cast_type)
343+
.or_else(|| timestamp_to_timestamp_multiplier(&source_type, cast_type))
344+
else {
337345
return Ok(());
338346
};
339347

@@ -367,7 +375,55 @@ fn ensure_date_array_timestamp_bounds(
367375
})?;
368376
(min(arr), max(arr))
369377
}
370-
_ => return Ok(()), // Not a date type, nothing to do
378+
DataType::Timestamp(TimeUnit::Second, _) => {
379+
let arr = array
380+
.as_any()
381+
.downcast_ref::<TimestampSecondArray>()
382+
.ok_or_else(|| {
383+
internal_datafusion_err!(
384+
"Expected TimestampSecondArray but found {}",
385+
array.data_type()
386+
)
387+
})?;
388+
(min(arr), max(arr))
389+
}
390+
DataType::Timestamp(TimeUnit::Millisecond, _) => {
391+
let arr = array
392+
.as_any()
393+
.downcast_ref::<TimestampMillisecondArray>()
394+
.ok_or_else(|| {
395+
internal_datafusion_err!(
396+
"Expected TimestampMillisecondArray but found {}",
397+
array.data_type()
398+
)
399+
})?;
400+
(min(arr), max(arr))
401+
}
402+
DataType::Timestamp(TimeUnit::Microsecond, _) => {
403+
let arr = array
404+
.as_any()
405+
.downcast_ref::<TimestampMicrosecondArray>()
406+
.ok_or_else(|| {
407+
internal_datafusion_err!(
408+
"Expected TimestampMicrosecondArray but found {}",
409+
array.data_type()
410+
)
411+
})?;
412+
(min(arr), max(arr))
413+
}
414+
DataType::Timestamp(TimeUnit::Nanosecond, _) => {
415+
let arr = array
416+
.as_any()
417+
.downcast_ref::<TimestampNanosecondArray>()
418+
.ok_or_else(|| {
419+
internal_datafusion_err!(
420+
"Expected TimestampNanosecondArray but found {}",
421+
array.data_type()
422+
)
423+
})?;
424+
(min(arr), max(arr))
425+
}
426+
_ => return Ok(()), // Not a temporal type that needs checking.
371427
};
372428

373429
// Only validate the min and max values instead of all elements
@@ -694,4 +750,20 @@ mod tests {
694750
"unexpected error: {err}"
695751
);
696752
}
753+
754+
#[test]
755+
fn cast_timestamp_array_to_timestamp_overflow() {
756+
let overflow_value = i64::MAX / 1_000_000_000 + 1;
757+
let array: ArrayRef =
758+
Arc::new(TimestampSecondArray::from(vec![Some(overflow_value)]));
759+
let value = ColumnarValue::Array(array);
760+
let result =
761+
value.cast_to(&DataType::Timestamp(TimeUnit::Nanosecond, None), None);
762+
let err = result.expect_err("expected overflow to be detected");
763+
assert!(
764+
err.to_string()
765+
.contains("converted value exceeds the representable i64 range"),
766+
"unexpected error: {err}"
767+
);
768+
}
697769
}

‎datafusion/expr-common/src/type_coercion/binary.rs‎

Lines changed: 4 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -2048,22 +2048,10 @@ fn temporal_coercion(lhs_type: &DataType, rhs_type: &DataType) -> Option<DataTyp
20482048
fn timeunit_coercion(lhs_unit: &TimeUnit, rhs_unit: &TimeUnit) -> TimeUnit {
20492049
use arrow::datatypes::TimeUnit::*;
20502050
match (lhs_unit, rhs_unit) {
2051-
(Second, Millisecond) => Second,
2052-
(Second, Microsecond) => Second,
2053-
(Second, Nanosecond) => Second,
2054-
(Millisecond, Second) => Second,
2055-
(Millisecond, Microsecond) => Millisecond,
2056-
(Millisecond, Nanosecond) => Millisecond,
2057-
(Microsecond, Second) => Second,
2058-
(Microsecond, Millisecond) => Millisecond,
2059-
(Microsecond, Nanosecond) => Microsecond,
2060-
(Nanosecond, Second) => Second,
2061-
(Nanosecond, Millisecond) => Millisecond,
2062-
(Nanosecond, Microsecond) => Microsecond,
2063-
(l, r) => {
2064-
assert_eq!(l, r);
2065-
*l
2066-
}
2051+
(Second, Second) => Second,
2052+
(Nanosecond, _) | (_, Nanosecond) => Nanosecond,
2053+
(Microsecond, _) | (_, Microsecond) => Microsecond,
2054+
(Millisecond, _) | (_, Millisecond) => Millisecond,
20672055
}
20682056
}
20692057

‎datafusion/expr-common/src/type_coercion/binary/tests/arithmetic.rs‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -40,8 +40,8 @@ fn test_date_timestamp_arithmetic_error() -> Result<()> {
4040
&DataType::Timestamp(Millisecond, None),
4141
)
4242
.get_input_types()?;
43-
assert_eq!(lhs, DataType::Timestamp(Millisecond, None));
44-
assert_eq!(rhs, DataType::Timestamp(Millisecond, None));
43+
assert_eq!(lhs, DataType::Timestamp(Nanosecond, None));
44+
assert_eq!(rhs, DataType::Timestamp(Nanosecond, None));
4545

4646
let err =
4747
BinaryTypeCoercer::new(&DataType::Date32, &Operator::Plus, &DataType::Date64)

‎datafusion/expr-common/src/type_coercion/binary/tests/comparison.rs‎

Lines changed: 36 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -575,6 +575,24 @@ fn test_type_coercion_compare() -> Result<()> {
575575
Operator::Eq,
576576
DataType::Timestamp(Second, Some("Europe/Brussels".into()))
577577
);
578+
test_coercion_binary_rule!(
579+
DataType::Timestamp(Second, None),
580+
DataType::Timestamp(Millisecond, None),
581+
Operator::Eq,
582+
DataType::Timestamp(Millisecond, None)
583+
);
584+
test_coercion_binary_rule!(
585+
DataType::Timestamp(Second, Some("America/New_York".into())),
586+
DataType::Timestamp(Nanosecond, Some("Europe/Brussels".into())),
587+
Operator::Lt,
588+
DataType::Timestamp(Nanosecond, Some("America/New_York".into()))
589+
);
590+
test_coercion_binary_rule!(
591+
DataType::Timestamp(Microsecond, None),
592+
DataType::Timestamp(Nanosecond, None),
593+
Operator::GtEq,
594+
DataType::Timestamp(Nanosecond, None)
595+
);
578596

579597
// list
580598
let inner_field = Arc::new(Field::new_list_field(DataType::Int64, true));
@@ -872,6 +890,24 @@ fn test_type_union_coercion_prefers_string() {
872890
);
873891
}
874892

893+
#[test]
894+
fn test_type_union_coercion_prefers_finer_timestamp_unit() {
895+
assert_eq!(
896+
type_union_coercion(
897+
&DataType::Timestamp(Second, None),
898+
&DataType::Timestamp(Millisecond, None),
899+
),
900+
Some(DataType::Timestamp(Millisecond, None))
901+
);
902+
assert_eq!(
903+
type_union_resolution(&[
904+
DataType::Timestamp(Second, None),
905+
DataType::Timestamp(Nanosecond, None),
906+
]),
907+
Some(DataType::Timestamp(Nanosecond, None))
908+
);
909+
}
910+
875911
/// Tests that comparison operators coerce to numeric when comparing
876912
/// numeric and string types.
877913
#[test]

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

Lines changed: 49 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -2480,6 +2480,51 @@ SELECT TIMESTAMPTZ '2020-01-01 00:00:00Z' = TIMESTAMP '2020-01-01'
24802480
----
24812481
true
24822482

2483+
query BBB
2484+
SELECT
2485+
arrow_cast(TIMESTAMP '2024-01-01 00:00:00', 'Timestamp(Second, None)') =
2486+
arrow_cast(TIMESTAMP '2024-01-01 00:00:00.123', 'Timestamp(Millisecond, None)'),
2487+
arrow_cast(TIMESTAMP '2024-01-01 00:00:00', 'Timestamp(Second, None)') =
2488+
arrow_cast(TIMESTAMP '2024-01-01 00:00:00.000', 'Timestamp(Millisecond, None)'),
2489+
arrow_cast(TIMESTAMP '2024-01-01 00:00:00', 'Timestamp(Second, None)') <
2490+
arrow_cast(TIMESTAMP '2024-01-01 00:00:00.123', 'Timestamp(Millisecond, None)')
2491+
----
2492+
false true true
2493+
2494+
query ?
2495+
SELECT
2496+
arrow_cast(TIMESTAMP '2024-01-01 00:00:00.123', 'Timestamp(Millisecond, None)') -
2497+
arrow_cast(TIMESTAMP '2024-01-01 00:00:00', 'Timestamp(Second, None)')
2498+
----
2499+
0 days 0 hours 0 mins 0.123 secs
2500+
2501+
query TP
2502+
SELECT arrow_typeof(ts), ts
2503+
FROM (
2504+
SELECT arrow_cast(TIMESTAMP '2024-01-01 00:00:00', 'Timestamp(Second, None)') AS ts
2505+
UNION ALL
2506+
SELECT arrow_cast(TIMESTAMP '2024-01-01 00:00:00.123', 'Timestamp(Millisecond, None)') AS ts
2507+
)
2508+
ORDER BY ts
2509+
----
2510+
Timestamp(ms) 2024-01-01T00:00:00
2511+
Timestamp(ms) 2024-01-01T00:00:00.123
2512+
2513+
query TP
2514+
SELECT
2515+
arrow_typeof(
2516+
coalesce(
2517+
arrow_cast(NULL, 'Timestamp(Second, None)'),
2518+
arrow_cast(TIMESTAMP '2024-01-01 00:00:00.123', 'Timestamp(Millisecond, None)')
2519+
)
2520+
),
2521+
coalesce(
2522+
arrow_cast(NULL, 'Timestamp(Second, None)'),
2523+
arrow_cast(TIMESTAMP '2024-01-01 00:00:00.123', 'Timestamp(Millisecond, None)')
2524+
)
2525+
----
2526+
Timestamp(ms) 2024-01-01T00:00:00.123
2527+
24832528
# verify timestamp cast with integer input
24842529
query PPPPPP
24852530
SELECT to_timestamp(null), to_timestamp(0), to_timestamp(1926632005), to_timestamp(1), to_timestamp(-1), to_timestamp(0-1)
@@ -3959,17 +4004,17 @@ true
39594004
query ?
39604005
select arrow_cast('2024-06-17T11:00:00', 'Timestamp(Nanosecond, Some("UTC"))') - arrow_cast('2024-06-17T12:00:00', 'Timestamp(Microsecond, Some("UTC"))');
39614006
----
3962-
0 days -1 hours 0 mins 0.000000 secs
4007+
0 days -1 hours 0 mins 0.000000000 secs
39634008

39644009
query ?
39654010
select arrow_cast('2024-06-17T13:00:00', 'Timestamp(Nanosecond, Some("+00:00"))') - arrow_cast('2024-06-17T12:00:00', 'Timestamp(Microsecond, Some("UTC"))');
39664011
----
3967-
0 days 1 hours 0 mins 0.000000 secs
4012+
0 days 1 hours 0 mins 0.000000000 secs
39684013

39694014
query ?
39704015
select arrow_cast('2024-06-17T13:00:00', 'Timestamp(Nanosecond, Some("UTC"))') - arrow_cast('2024-06-17T12:00:00', 'Timestamp(Microsecond, Some("+00:00"))');
39714016
----
3972-
0 days 1 hours 0 mins 0.000000 secs
4017+
0 days 1 hours 0 mins 0.000000000 secs
39734018

39744019
# not supported: coercion across timezones
39754020
query error
@@ -5331,7 +5376,7 @@ SELECT to_timestamp(arrow_cast(-9223372036, 'Int64'));
53315376
1677-09-21T00:12:44
53325377

53335378
# Overflow error when value exceeds valid range
5334-
query error Arithmetic overflow
5379+
query error converted value exceeds the representable i64 range
53355380
SELECT to_timestamp(arrow_cast(9223372037, 'Int64'));
53365381

53375382
# Float truncation behavior

0 commit comments

Comments
 (0)