Skip to content

Commit ce7957b

Browse files
Toby1009Omega359
authored andcommitted
fix: preserve date_trunc sorting for timezone-aware timestamps (apache#25409)
## Which issue does this PR close? - Closes apache#25353. ## Rationale for this change `ORDER BY date_trunc(...)` can return incorrectly ordered results across named timezone transitions because the optimizer incorrectly assumes that truncation preserves ordering and removes a required sort. ## What changes are included in this PR? Only propagate input ordering when the precision is constant and the input range type is a timezone-free timestamp, `Time32`, or `Time64`. Return `SortProperties::Unordered` for timezone-aware timestamps and unknown range types. An unknown range can hide a timezone-aware result, such as `from_unixtime(c, 'America/Goose_Bay')`. Follow-up optimizations are tracked separately: - apache#25464: recognize safe timezone-aware cases, such as UTC and fixed offsets. - apache#25465: provide typed bounds for `date_bin` and `from_unixtime`. ## What is the testing strategy for this PR? - A unit test covers timezone-free, timezone-aware, and unknown range types. - SQL regression tests cover the original `America/Goose_Bay` reproducer and the `from_unixtime` case identified during review. - The new unknown-range assertion and `from_unixtime` regression fail with the previous ordering check and pass with the allowlist. - Formatting, full Clippy, and extended workspace tests pass locally. ## Are there any user-facing changes? Affected queries return correctly ordered results. Some queries may retain additional sorts, including UTC and fixed-offset timestamps, and timezone-free expressions whose bounds analysis does not provide their output type.
1 parent c1571dc commit ce7957b

2 files changed

Lines changed: 96 additions & 4 deletions

File tree

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

Lines changed: 49 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -431,11 +431,15 @@ impl ScalarUDFImpl for DateTruncFunc {
431431
}
432432

433433
fn output_ordering(&self, input: &[ExprProperties]) -> Result<SortProperties> {
434-
// The DATE_TRUNC function preserves the order of its second argument.
435434
let precision = &input[0];
436435
let date_value = &input[1];
437436

438-
if precision.sort_properties.eq(&SortProperties::Singleton) {
437+
let order_safe_input = matches!(
438+
date_value.range.data_type(),
439+
Timestamp(_, None) | Time32(_) | Time64(_)
440+
);
441+
442+
if precision.sort_properties == SortProperties::Singleton && order_safe_input {
439443
Ok(date_value.sort_properties)
440444
} else {
441445
Ok(SortProperties::Unordered)
@@ -889,13 +893,55 @@ mod tests {
889893
TimestampNanosecondArray, TimestampSecondArray,
890894
};
891895
use arrow::buffer::NullBuffer;
892-
use arrow::compute::DatePart;
893896
use arrow::compute::kernels::cast_utils::string_to_timestamp_nanos;
897+
use arrow::compute::{DatePart, SortOptions};
894898
use arrow::datatypes::{DataType, Field, TimeUnit};
895899
use datafusion_common::ScalarValue;
896900
use datafusion_common::config::ConfigOptions;
901+
use datafusion_expr::interval_arithmetic::Interval;
902+
use datafusion_expr::sort_properties::{ExprProperties, SortProperties};
897903
use datafusion_expr::{ColumnarValue, ScalarFunctionArgs, ScalarUDFImpl};
898904

905+
#[test]
906+
fn output_ordering_respects_timestamp_timezone() {
907+
let precision_value = ScalarValue::Utf8(Some("hour".into()));
908+
let precision = ExprProperties::new_unknown()
909+
.with_order(SortProperties::Singleton)
910+
.with_range(
911+
Interval::try_new(precision_value.clone(), precision_value).unwrap(),
912+
);
913+
let ordered = SortProperties::Ordered(SortOptions::default());
914+
let date_value = |data_type| {
915+
ExprProperties::new_unknown()
916+
.with_order(ordered)
917+
.with_range(Interval::make_unbounded(&data_type).unwrap())
918+
};
919+
let function = DateTruncFunc::new();
920+
921+
let timestamp = date_value(DataType::Timestamp(TimeUnit::Second, None));
922+
assert_eq!(
923+
function
924+
.output_ordering(&[precision.clone(), timestamp])
925+
.unwrap(),
926+
ordered
927+
);
928+
let timestamp_with_timezone = date_value(DataType::Timestamp(
929+
TimeUnit::Second,
930+
Some("America/Goose_Bay".into()),
931+
));
932+
assert_eq!(
933+
function
934+
.output_ordering(&[precision.clone(), timestamp_with_timezone])
935+
.unwrap(),
936+
SortProperties::Unordered
937+
);
938+
let unknown = ExprProperties::new_unknown().with_order(ordered);
939+
assert_eq!(
940+
function.output_ordering(&[precision, unknown]).unwrap(),
941+
SortProperties::Unordered
942+
);
943+
}
944+
899945
#[test]
900946
fn date_trunc_test() {
901947
let cases = vec![

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

Lines changed: 47 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1655,7 +1655,53 @@ SELECT DATE_TRUNC('day', arrow_cast(TIMESTAMP '2023-08-03 14:38:50Z', 'Timestamp
16551655
2023-08-03T14:38:50
16561656
2023-08-03T14:38:50
16571657

1658-
# date_trunc with data with timezones
1658+
# date_trunc must not propagate ordering across named timezone transitions
1659+
query II
1660+
SELECT
1661+
arrow_cast(ts, 'Int64') AS input_epoch,
1662+
arrow_cast(date_trunc('hour', ts), 'Int64') AS truncated
1663+
FROM (
1664+
SELECT
1665+
arrow_cast(
1666+
column1,
1667+
'Timestamp(Second, Some("America/Goose_Bay"))'
1668+
) AS ts
1669+
FROM (
1670+
VALUES
1671+
(562129260::bigint),
1672+
(562129259::bigint)
1673+
)
1674+
ORDER BY ts
1675+
LIMIT 2
1676+
)
1677+
ORDER BY date_trunc('hour', ts);
1678+
----
1679+
562129260 562125600
1680+
562129259 562129200
1681+
1682+
# An unknown range from a child function may hide a timezone-aware timestamp
1683+
query II
1684+
SELECT
1685+
c,
1686+
arrow_cast(
1687+
date_trunc('hour', from_unixtime(c, 'America/Goose_Bay')),
1688+
'Int64'
1689+
) AS truncated
1690+
FROM (
1691+
SELECT column1 AS c
1692+
FROM (
1693+
VALUES
1694+
(562129260::bigint),
1695+
(562129259::bigint)
1696+
)
1697+
ORDER BY c
1698+
LIMIT 2
1699+
)
1700+
ORDER BY date_trunc('hour', from_unixtime(c, 'America/Goose_Bay'));
1701+
----
1702+
562129260 562125600
1703+
562129259 562129200
1704+
16591705
statement ok
16601706
CREATE TABLE timestamp_strings(ts varchar)
16611707
AS VALUES

0 commit comments

Comments
 (0)