Skip to content

Commit 9fd232a

Browse files
adriangbclaude
andcommitted
fix: choose the AT TIME ZONE branch after type coercion
The SQL planner chose between the naive and the aware lowering from `Expr::get_type`, which runs before type coercion. So a `CASE` or `UNION` whose first arm is naive, but whose coerced type is aware, kept the old aware-in, aware-out result. The same early type also set the `TimeUnit`, so a first arm with a coarser unit truncated the other arms. The planner now plans `AT TIME ZONE` as `timezone(zone, expr)` through `ExprPlanner::plan_at_time_zone`, which now receives the raw operands. The function decides once the coerced type is known. Co-Authored-By: Claude Opus 5.5 <[email protected]>
1 parent e0c60b5 commit 9fd232a

6 files changed

Lines changed: 173 additions & 91 deletions

File tree

‎datafusion/expr/src/planner.rs‎

Lines changed: 7 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -218,20 +218,15 @@ pub trait ExprPlanner: Debug + Send + Sync {
218218
Ok(PlannerResult::Original(args))
219219
}
220220

221-
/// Plan `<expr> AT TIME ZONE '<tz>'` when `<expr>` is **already
222-
/// timezone-aware**.
221+
/// Plan `<expr> AT TIME ZONE '<tz>'`.
223222
///
224-
/// Following PostgreSQL, `<tz-aware timestamp> AT TIME ZONE zone` returns
225-
/// the wall clock the instant has in `zone`, as a timezone-*naive*
226-
/// timestamp. (The timezone-*naive* case is the plain
227-
/// `CAST(<expr> AS Timestamp(unit, Some(tz)))` that the SQL planner
228-
/// handles on its own and never reaches this method.)
223+
/// `args` is `[Utf8 literal tz, expr]`, the argument order of the
224+
/// PostgreSQL function form `timezone(zone, expr)`.
229225
///
230-
/// `args` holds a single expression: the input already cast to
231-
/// `Timestamp(unit, Some(tz))`. That cast preserves the instant and only
232-
/// relabels the timezone, so all an implementation has to do is drop the
233-
/// timezone while keeping the displayed value — exactly what
234-
/// `to_local_time` does.
226+
/// The result type depends on whether the type of `expr` *after type
227+
/// coercion* has a timezone, and the SQL planner runs before type
228+
/// coercion. So an implementation must return an expression that decides
229+
/// later, such as the `timezone` function of `datafusion-functions`.
235230
///
236231
/// Returns original expression arguments if not possible
237232
fn plan_at_time_zone(&self, args: Vec<Expr>) -> Result<PlannerResult<Vec<Expr>>> {

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

Lines changed: 2 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -33,19 +33,13 @@ impl ExprPlanner for DatetimeFunctionPlanner {
3333
)))
3434
}
3535

36-
/// `<tz-aware timestamp> AT TIME ZONE 'tz'` returns the wall clock in `tz`
37-
/// as a timezone-naive timestamp, matching PostgreSQL.
38-
///
39-
/// The SQL planner hands us the input already cast to
40-
/// `Timestamp(unit, Some(tz))` — an instant-preserving relabel — so the
41-
/// remaining step is to strip the timezone while keeping the displayed
42-
/// value, which is `to_local_time`.
36+
/// `<expr> AT TIME ZONE 'tz'` is `timezone('tz', <expr>)`.
4337
fn plan_at_time_zone(
4438
&self,
4539
args: Vec<Expr>,
4640
) -> datafusion_common::Result<PlannerResult<Vec<Expr>>> {
4741
Ok(PlannerResult::Planned(Expr::ScalarFunction(
48-
ScalarFunction::new_udf(crate::datetime::to_local_time(), args),
42+
ScalarFunction::new_udf(crate::datetime::timezone(), args),
4943
)))
5044
}
5145
}

‎datafusion/sql/src/expr/mod.rs‎

Lines changed: 23 additions & 45 deletions
Original file line numberDiff line numberDiff line change
@@ -896,26 +896,12 @@ impl<S: ContextProvider> SqlToRel<'_, S> {
896896

897897
/// Plan `<timestamp> AT TIME ZONE '<tz>'`.
898898
///
899-
/// The meaning of `AT TIME ZONE` depends on whether its input carries a
900-
/// timezone, and it always returns the *other* kind of timestamp. This
901-
/// follows PostgreSQL (and DuckDB):
902-
///
903-
/// * a timezone-**naive** input is read as a wall clock in `tz`, and the
904-
/// result is the corresponding timezone-**aware** instant. That is a
905-
/// plain `CAST(expr AS Timestamp(unit, Some(tz)))`, because arrow's
906-
/// `Timestamp(_, None) -> Timestamp(_, Some(tz))` cast interprets the
907-
/// naive value as local time in `tz`.
908-
/// * a timezone-**aware** input is an instant, and the result is the wall
909-
/// clock that instant has in `tz`, as a timezone-**naive** timestamp.
910-
/// The same cast is still the first half of that (casting between two
911-
/// aware types preserves the instant and only relabels the zone); the
912-
/// second half — dropping the zone while keeping the displayed value —
913-
/// is delegated to [`ExprPlanner::plan_at_time_zone`], which
914-
/// `datafusion-functions` implements with `to_local_time`.
915-
///
916-
/// Anything that is not a timestamp (a string literal, for instance) takes
917-
/// the naive path, since a `CAST` to a timezone-aware timestamp is the
918-
/// natural reading of `AT TIME ZONE` for it.
899+
/// The result type depends on whether the input has a timezone *after
900+
/// type coercion*, and type coercion runs after this planner. So this
901+
/// method only builds `timezone('<tz>', <timestamp>)` through
902+
/// [`ExprPlanner::plan_at_time_zone`], and that function decides once the
903+
/// type is known. See `TimezoneFunc` in `datafusion-functions` for the
904+
/// semantics.
919905
///
920906
/// [`ExprPlanner::plan_at_time_zone`]: datafusion_expr::planner::ExprPlanner::plan_at_time_zone
921907
fn sql_at_time_zone_to_expr(
@@ -938,25 +924,7 @@ impl<S: ContextProvider> SqlToRel<'_, S> {
938924
let expr =
939925
self.sql_expr_to_logical_expr_internal(timestamp, schema, planner_context)?;
940926

941-
// `AT TIME ZONE` does not change the precision of its input, so keep
942-
// the input's `TimeUnit` when it has one.
943-
let (unit, input_is_tz_aware) = match expr.get_type(schema)? {
944-
DataType::Timestamp(unit, tz) => (unit, tz.is_some()),
945-
_ => (TimeUnit::Nanosecond, false),
946-
};
947-
948-
// Instant-preserving relabel into `tz` for an aware input; local-time
949-
// interpretation for a naive one.
950-
let relabeled = Expr::Cast(Cast::new(
951-
Box::new(expr),
952-
DataType::Timestamp(unit, Some(tz)),
953-
));
954-
955-
if !input_is_tz_aware {
956-
return Ok(relabeled);
957-
}
958-
959-
let mut args = vec![relabeled];
927+
let mut args = vec![lit(tz.as_ref()), expr];
960928
for planner in self.context_provider.get_expr_planners() {
961929
match planner.plan_at_time_zone(args)? {
962930
PlannerResult::Planned(expr) => return Ok(expr),
@@ -966,12 +934,22 @@ impl<S: ContextProvider> SqlToRel<'_, S> {
966934
}
967935
}
968936

969-
plan_err!(
970-
"AT TIME ZONE on a timezone-aware timestamp is not supported by any \
971-
ExprPlanner. It needs the `to_local_time` function; register \
972-
`datafusion_functions::datetime` (or its `DatetimeFunctionPlanner`) \
973-
with the session"
974-
)
937+
// No planner handles it, for example when `datafusion-sql` is used
938+
// without `datafusion-functions`. Keep the historical lowering for an
939+
// input that is not visibly timezone-aware, and refuse one that is,
940+
// rather than return the wrong type for it.
941+
let expr = args.swap_remove(1);
942+
if let DataType::Timestamp(_, Some(_)) = expr.get_type(schema)? {
943+
return not_impl_err!(
944+
"AT TIME ZONE on a timezone-aware timestamp needs an ExprPlanner \
945+
that implements `plan_at_time_zone`, such as the \
946+
`DatetimeFunctionPlanner` of `datafusion-functions`"
947+
);
948+
}
949+
Ok(Expr::Cast(Cast::new(
950+
Box::new(expr),
951+
DataType::Timestamp(TimeUnit::Nanosecond, Some(tz)),
952+
)))
975953
}
976954

977955
fn sql_position_to_expr(

‎datafusion/sql/tests/sql_integration.rs‎

Lines changed: 30 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -46,6 +46,7 @@ use datafusion_sql::{
4646

4747
use crate::common::{CustomExprPlanner, CustomTypePlanner, MockSessionState};
4848
use datafusion_functions::core::planner::CoreFunctionPlanner;
49+
use datafusion_functions::datetime::{self, planner::DatetimeFunctionPlanner};
4950
use datafusion_functions_aggregate::{
5051
approx_median::approx_median_udaf,
5152
average::avg_udaf,
@@ -4045,10 +4046,34 @@ fn plan_merge_into_rejects_invalid_actions_and_structure(
40454046
);
40464047
}
40474048

4048-
/// A timezone-naive input is read as a wall clock in the target timezone, so it
4049-
/// lowers to a plain `CAST` and preserves the input's `TimeUnit`.
4049+
/// With the `DatetimeFunctionPlanner`, `AT TIME ZONE` plans as the `timezone`
4050+
/// function for every input type. The function picks the result type after
4051+
/// type coercion, so the SQL planner does not need the input type.
40504052
#[test]
4051-
fn plan_at_time_zone_on_naive_timestamp() {
4053+
fn plan_at_time_zone_with_datetime_planner() {
4054+
let state = MockSessionState::default()
4055+
.with_expr_planner(Arc::new(DatetimeFunctionPlanner))
4056+
.with_scalar_function(datetime::timezone());
4057+
let plan = logical_plan_from_state(
4058+
"SELECT birth_date AT TIME ZONE 'America/Denver' FROM person",
4059+
&GenericDialect {},
4060+
ParserOptions::default(),
4061+
state,
4062+
)
4063+
.unwrap();
4064+
assert_snapshot!(
4065+
plan,
4066+
@r#"
4067+
Projection: timezone(Utf8("America/Denver"), person.birth_date)
4068+
TableScan: person
4069+
"#
4070+
);
4071+
}
4072+
4073+
/// Without an `ExprPlanner` for `AT TIME ZONE`, an input that is not visibly
4074+
/// timezone-aware keeps the historical lowering to a plain `CAST`.
4075+
#[test]
4076+
fn plan_at_time_zone_on_naive_timestamp_without_planner() {
40524077
let plan =
40534078
logical_plan("SELECT birth_date AT TIME ZONE 'America/Denver' FROM person")
40544079
.unwrap();
@@ -4061,10 +4086,8 @@ fn plan_at_time_zone_on_naive_timestamp() {
40614086
);
40624087
}
40634088

4064-
/// A timezone-aware input needs `to_local_time`, which is provided by
4065-
/// `datafusion-functions`' `DatetimeFunctionPlanner`. The mock context provider
4066-
/// used by these tests does not register it, so planning must fail with a clear
4067-
/// message rather than silently falling back to the naive lowering.
4089+
/// Without an `ExprPlanner` for `AT TIME ZONE`, a visibly timezone-aware input
4090+
/// fails with a clear message, rather than silently taking the naive lowering.
40684091
#[test]
40694092
fn plan_at_time_zone_on_tz_aware_timestamp_without_planner() {
40704093
let err = logical_plan(

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

Lines changed: 103 additions & 13 deletions
Original file line numberDiff line numberDiff line change
@@ -5877,21 +5877,18 @@ SELECT date_bin(NULL, TIMESTAMP '2023-01-01 12:30:00', TIMESTAMP '2023-01-01 12:
58775877
----
58785878
NULL
58795879

5880-
# KNOWN LIMITATION: `AT TIME ZONE` chooses its behaviour from the type that
5881-
# `Expr::get_type` reports in the SQL planner, which runs BEFORE the type
5882-
# coercion analyzer. For a `CASE` expression, `get_type` reports the type of the
5883-
# first non-null `THEN` arm and ignores coercion. So the two queries below have
5884-
# the same coerced input type, `Timestamp(ns, "UTC")`, but give different
5885-
# results: the first still takes the timezone-naive path and keeps the old
5886-
# aware-in aware-out shape.
5887-
#
5888-
# PostgreSQL gives `2024-01-01 05:00:00` as a naive timestamp for both.
5889-
# A correct fix has to dispatch after coercion. Tracked here so the behaviour is
5890-
# visible rather than silent.
5880+
# `AT TIME ZONE` chooses its behaviour from the input type AFTER type
5881+
# coercion. Each input below is `Timestamp(ns, "UTC")` once coerced, even when
5882+
# the first CASE arm or the first UNION branch is naive. PostgreSQL 17 gives the
5883+
# naive `2024-01-01 05:00:00` for all of them.
58915884
statement ok
58925885
CREATE TABLE at_tz_case AS
58935886
SELECT arrow_cast('2024-01-01T12:00:00Z', 'Timestamp(Nanosecond, Some("UTC"))') AS aware,
58945887
arrow_cast('2024-01-01T12:00:00', 'Timestamp(Nanosecond, None)') AS naive,
5888+
arrow_cast('2024-01-01T12:00:00.123456789Z', 'Timestamp(Nanosecond, Some("UTC"))') AS aware_frac,
5889+
arrow_cast('2024-01-01T12:00:00.123456789', 'Timestamp(Nanosecond, None)') AS naive_frac,
5890+
arrow_cast('2024-01-01T12:00:00Z', 'Timestamp(Second, Some("UTC"))') AS aware_s,
5891+
arrow_cast('2024-01-01T12:00:00', 'Timestamp(Second, None)') AS naive_s,
58955892
true AS b
58965893

58975894
# both CASE expressions have the same type
@@ -5902,15 +5899,108 @@ FROM at_tz_case
59025899
----
59035900
Timestamp(ns, "UTC") Timestamp(ns, "UTC")
59045901

5905-
# but AT TIME ZONE does not agree on them
5902+
# so AT TIME ZONE agrees on them, whatever the order of the arms
59065903
query TPTP
59075904
SELECT arrow_typeof((CASE WHEN b THEN naive ELSE aware END) AT TIME ZONE 'America/Denver'),
59085905
(CASE WHEN b THEN naive ELSE aware END) AT TIME ZONE 'America/Denver',
59095906
arrow_typeof((CASE WHEN b THEN aware ELSE naive END) AT TIME ZONE 'America/Denver'),
59105907
(CASE WHEN b THEN aware ELSE naive END) AT TIME ZONE 'America/Denver'
59115908
FROM at_tz_case
59125909
----
5913-
Timestamp(ns, "America/Denver") 2024-01-01T05:00:00-07:00 Timestamp(ns) 2024-01-01T05:00:00
5910+
Timestamp(ns) 2024-01-01T05:00:00 Timestamp(ns) 2024-01-01T05:00:00
5911+
5912+
# CTE / derived-table column over such a CASE
5913+
query TP
5914+
WITH c AS (SELECT CASE WHEN b THEN naive ELSE aware END AS x FROM at_tz_case)
5915+
SELECT arrow_typeof(x AT TIME ZONE 'America/Denver'), x AT TIME ZONE 'America/Denver' FROM c
5916+
----
5917+
Timestamp(ns) 2024-01-01T05:00:00
5918+
5919+
# UNION ALL with the naive branch first
5920+
query TP
5921+
SELECT arrow_typeof(x AT TIME ZONE 'America/Denver'), x AT TIME ZONE 'America/Denver'
5922+
FROM (SELECT naive AS x FROM at_tz_case UNION ALL SELECT aware FROM at_tz_case)
5923+
----
5924+
Timestamp(ns) 2024-01-01T05:00:00
5925+
Timestamp(ns) 2024-01-01T05:00:00
5926+
5927+
# aggregate over such a CASE
5928+
query TP
5929+
SELECT arrow_typeof(max(CASE WHEN b THEN naive ELSE aware END) AT TIME ZONE 'America/Denver'),
5930+
max(CASE WHEN b THEN naive ELSE aware END) AT TIME ZONE 'America/Denver'
5931+
FROM at_tz_case
5932+
----
5933+
Timestamp(ns) 2024-01-01T05:00:00
5934+
5935+
# The time unit also comes from the coerced type. Here the first arm or branch
5936+
# has second precision, but the coerced type has nanosecond precision, so the
5937+
# fraction of a second is kept.
5938+
query TPTP
5939+
SELECT arrow_typeof((CASE WHEN NOT b THEN naive_s ELSE aware_frac END) AT TIME ZONE 'UTC'),
5940+
(CASE WHEN NOT b THEN naive_s ELSE aware_frac END) AT TIME ZONE 'UTC',
5941+
arrow_typeof((CASE WHEN NOT b THEN aware_s ELSE naive_frac END) AT TIME ZONE 'UTC'),
5942+
(CASE WHEN NOT b THEN aware_s ELSE naive_frac END) AT TIME ZONE 'UTC'
5943+
FROM at_tz_case
5944+
----
5945+
Timestamp(ns) 2024-01-01T12:00:00.123456789 Timestamp(ns) 2024-01-01T12:00:00.123456789
5946+
5947+
query TP rowsort
5948+
SELECT arrow_typeof(x AT TIME ZONE 'UTC'), x AT TIME ZONE 'UTC'
5949+
FROM (SELECT naive_s AS x FROM at_tz_case UNION ALL SELECT naive_frac FROM at_tz_case)
5950+
----
5951+
Timestamp(ns, "UTC") 2024-01-01T12:00:00.123456789Z
5952+
Timestamp(ns, "UTC") 2024-01-01T12:00:00Z
5953+
5954+
# A dictionary-encoded timestamp follows its value type
5955+
query TP
5956+
SELECT arrow_typeof(d AT TIME ZONE 'America/Denver'), d AT TIME ZONE 'America/Denver'
5957+
FROM (SELECT arrow_cast(aware, 'Dictionary(Int32, Timestamp(Nanosecond, Some("UTC")))') AS d FROM at_tz_case)
5958+
----
5959+
Timestamp(ns) 2024-01-01T05:00:00
5960+
5961+
query TP
5962+
SELECT arrow_typeof(d AT TIME ZONE 'America/Denver'), d AT TIME ZONE 'America/Denver'
5963+
FROM (SELECT arrow_cast(naive, 'Dictionary(Int32, Timestamp(Nanosecond, None))') AS d FROM at_tz_case)
5964+
----
5965+
Timestamp(ns, "America/Denver") 2024-01-01T12:00:00-07:00
5966+
5967+
# An untyped placeholder has no type when the statement is prepared, so it
5968+
# takes the naive branch, as it did before `AT TIME ZONE` looked at the input
5969+
# type.
5970+
statement ok
5971+
PREPARE at_tz_p AS SELECT arrow_typeof($1 AT TIME ZONE 'America/Denver'), $1 AT TIME ZONE 'America/Denver'
5972+
5973+
query TP
5974+
EXECUTE at_tz_p(arrow_cast('2024-01-01T12:00:00Z', 'Timestamp(Nanosecond, Some("UTC"))'))
5975+
----
5976+
Timestamp(ns, "America/Denver") 2024-01-01T05:00:00-07:00
5977+
5978+
statement ok
5979+
DEALLOCATE at_tz_p
5980+
5981+
# The plan lowers to CAST / to_local_time after simplification. The column is
5982+
# named with the operator syntax.
5983+
query TT
5984+
EXPLAIN SELECT (CASE WHEN b THEN naive ELSE aware END) AT TIME ZONE 'America/Denver', naive AT TIME ZONE 'America/Denver' FROM at_tz_case
5985+
----
5986+
logical_plan
5987+
01)Projection: to_local_time(CAST(CASE WHEN at_tz_case.b THEN CAST(at_tz_case.naive AS Timestamp(ns, "UTC")) ELSE at_tz_case.aware END AS Timestamp(ns, "America/Denver"))) AS CASE WHEN at_tz_case.b THEN at_tz_case.naive ELSE at_tz_case.aware END AT TIME ZONE 'America/Denver', CAST(at_tz_case.naive AS Timestamp(ns, "America/Denver")) AS at_tz_case.naive AT TIME ZONE 'America/Denver'
5988+
02)--TableScan: at_tz_case projection=[aware, naive, b]
5989+
physical_plan
5990+
01)ProjectionExec: expr=[to_local_time(CAST(CASE WHEN b@2 THEN CAST(naive@1 AS Timestamp(ns, "UTC")) ELSE aware@0 END AS Timestamp(ns, "America/Denver"))) as CASE WHEN at_tz_case.b THEN at_tz_case.naive ELSE at_tz_case.aware END AT TIME ZONE 'America/Denver', CAST(naive@1 AS Timestamp(ns, "America/Denver")) as at_tz_case.naive AT TIME ZONE 'America/Denver']
5991+
02)--DataSourceExec: partitions=1, partition_sizes=[1]
5992+
5993+
# `timezone(zone, expression)` is the function form, as in PostgreSQL and DuckDB
5994+
query TPTP
5995+
SELECT arrow_typeof(timezone('America/Denver', aware)), timezone('America/Denver', aware),
5996+
arrow_typeof(timezone('America/Denver', naive)), timezone('America/Denver', naive)
5997+
FROM at_tz_case
5998+
----
5999+
Timestamp(ns) 2024-01-01T05:00:00 Timestamp(ns, "America/Denver") 2024-01-01T12:00:00-07:00
6000+
6001+
# The zone decides the result type, so it must be a constant
6002+
statement error timezone requires its first argument \(the timezone\) to be a constant, non-empty string
6003+
SELECT timezone(arrow_cast(b, 'Utf8'), aware) FROM at_tz_case
59146004

59156005
statement ok
59166006
DROP TABLE at_tz_case

‎docs/source/user-guide/sql/operators.md‎

Lines changed: 8 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -597,23 +597,25 @@ into account:
597597
+---------------------+---------------+
598598
```
599599

600-
The second form is equivalent to [`to_local_time`] applied to the timestamp
601-
after it has been relabelled into `tz`, and requires `to_local_time` to be
602-
registered with the session.
600+
`expression AT TIME ZONE tz` is the same as the function call
601+
[`timezone(tz, expression)`], as in PostgreSQL and DuckDB. The planner uses the
602+
input type _after_ type coercion. For example, `CASE WHEN c THEN naive ELSE aware END` has a timezone-aware type, so `AT TIME ZONE` returns a
603+
timezone-naive timestamp for it, whatever the order of the arms.
603604

604605
Because the two forms return different types, applying `AT TIME ZONE` twice
605606
returns a timezone-aware timestamp again: the wall clock produced by the first
606607
application is re-read as a local time in the second timezone.
607608

608-
`AT TIME ZONE` never changes the precision (`TimeUnit`) of its input. Inputs
609-
that are not timestamps at all (a string literal, for example) are cast to
609+
`AT TIME ZONE` never changes the precision (`TimeUnit`) of its input. A
610+
dictionary-encoded timestamp follows its value type. Inputs that are not
611+
timestamps at all (a string literal, for example) are cast to
610612
`Timestamp(Nanosecond, Some(tz))`.
611613

612614
Note that a timezone written as a fixed offset string follows the ISO 8601
613615
convention, so `'+05:30'` is 5 hours 30 minutes _east_ of UTC. PostgreSQL
614616
applies the opposite (POSIX) convention to offsets spelled this way.
615617

616-
[`to_local_time`]: scalar_functions.md#to_local_time
618+
[`timezone(tz, expression)`]: scalar_functions.md#timezone
617619

618620
## Literals
619621

0 commit comments

Comments
 (0)