Skip to content

Commit 1b27fc1

Browse files
adriangbclaude
andcommitted
fix: never fix the AT TIME ZONE branch before the input type is final
`timezone` lowered itself to `CAST` / `to_local_time(CAST)` in `simplify`. That fixed the branch from the type the simplifier saw, and that type is not always final: - `PREPARE` runs the optimizer without the analyzer, so a `CASE` of a naive and an aware column is still uncoerced there. - An untyped placeholder has no type until `EXECUTE`. Remove the lowering. The branch is now chosen only from the argument type, in `return_field_from_args` and `invoke_with_args`. The SQL planner no longer falls back to reading the input type when no `ExprPlanner` handles `AT TIME ZONE`. It returns an error, as for `EXTRACT`. Without the `CAST`, `unwrap_cast_in_comparison` no longer relabels a literal compared with `naive AT TIME ZONE 'zone'` (#25095), so one existing expectation loses a row that never matched. Co-Authored-By: Claude Opus 5.5 <[email protected]>
1 parent 9fd232a commit 1b27fc1

4 files changed

Lines changed: 70 additions & 90 deletions

File tree

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

Lines changed: 14 additions & 40 deletions
Original file line numberDiff line numberDiff line change
@@ -21,11 +21,9 @@ use arrow::datatypes::{DataType, Field, FieldRef, TimeUnit};
2121
use datafusion_common::types::logical_string;
2222
use datafusion_common::utils::take_function_args;
2323
use datafusion_common::{Result, ScalarValue, exec_err, internal_err};
24-
use datafusion_expr::expr::ScalarFunction;
25-
use datafusion_expr::simplify::{ExprSimplifyResult, SimplifyContext};
2624
use datafusion_expr::{
27-
Cast, Coercion, ColumnarValue, Documentation, Expr, ReturnFieldArgs,
28-
ScalarFunctionArgs, ScalarUDFImpl, Signature, TypeSignatureClass, Volatility,
25+
Coercion, ColumnarValue, Documentation, Expr, ReturnFieldArgs, ScalarFunctionArgs,
26+
ScalarUDFImpl, Signature, TypeSignatureClass, Volatility,
2927
};
3028
use datafusion_macros::user_doc;
3129

@@ -34,17 +32,20 @@ use datafusion_macros::user_doc;
3432
///
3533
/// The operator always returns the *other* kind of timestamp:
3634
///
37-
/// | Input type (after coercion) | Result | Plan after simplification |
35+
/// | Input type | Result | Computed as |
3836
/// | --- | --- | --- |
3937
/// | `Timestamp(unit, Some(_))` (aware) | the wall clock of the instant in `zone`, as `Timestamp(unit, None)` | `to_local_time(CAST(expression AS Timestamp(unit, Some(zone))))` |
4038
/// | `Timestamp(unit, None)` (naive) | the value read as a wall clock in `zone`, as `Timestamp(unit, Some(zone))` | `CAST(expression AS Timestamp(unit, Some(zone)))` |
4139
/// | any other type | as for a naive input | `CAST(expression AS Timestamp(ns, Some(zone)))` |
4240
///
43-
/// The choice needs the input type *after* type coercion. For example, a
44-
/// `CASE` with a naive arm and an aware arm is aware only after coercion. The
45-
/// SQL planner runs before coercion, so it plans `AT TIME ZONE` as a call to
46-
/// this function (see `DatetimeFunctionPlanner`), and [`Self::simplify`]
47-
/// lowers the call once the type is known.
41+
/// The choice depends only on the type of `expression`, and it is made every
42+
/// time that type is read: [`ScalarUDFImpl::return_field_from_args`] for the
43+
/// plan, and [`ScalarUDFImpl::invoke_with_args`] for the data. The function is
44+
/// deliberately never rewritten into the `CAST` form during planning. A rewrite
45+
/// would fix the choice from the type at that moment, and that type is not
46+
/// always final: the SQL planner runs before type coercion, `PREPARE` runs the
47+
/// optimizer without the analyzer, and an untyped placeholder has no type
48+
/// until `EXECUTE`.
4849
///
4950
/// `zone` must be a constant, because it determines the result type.
5051
#[user_doc(
@@ -107,8 +108,8 @@ impl TimezoneFunc {
107108
}
108109
}
109110

110-
/// The two steps that `timezone(zone, <input_type>)` lowers to: the target
111-
/// type of the `CAST`, and whether `to_local_time` follows it.
111+
/// The two steps that `timezone(zone, <input_type>)` does: the target type of
112+
/// the `CAST`, and whether `to_local_time` follows it.
112113
fn lowering(input_type: &DataType, zone: &str) -> (DataType, bool) {
113114
let (unit, input_is_aware) = match input_type {
114115
DataType::Timestamp(unit, tz) => (*unit, tz.is_some()),
@@ -174,31 +175,6 @@ impl ScalarUDFImpl for TimezoneFunc {
174175
)))
175176
}
176177

177-
/// Lower to `CAST`, or to `to_local_time(CAST)`, now that the input type
178-
/// is the coerced one.
179-
fn simplify(
180-
&self,
181-
args: Vec<Expr>,
182-
info: &SimplifyContext,
183-
) -> Result<ExprSimplifyResult> {
184-
let [zone_arg, input] = take_function_args(self.name(), args)?;
185-
let Expr::Literal(zone, _) = &zone_arg else {
186-
return Ok(ExprSimplifyResult::Original(vec![zone_arg, input]));
187-
};
188-
let zone = zone_from_scalar(self.name(), Some(zone))?;
189-
let (cast_type, strip_timezone) = lowering(&info.get_data_type(&input)?, &zone);
190-
let cast = Expr::Cast(Cast::new(Box::new(input), cast_type));
191-
Ok(ExprSimplifyResult::Simplified(if strip_timezone {
192-
Expr::ScalarFunction(ScalarFunction::new_udf(
193-
super::to_local_time(),
194-
vec![cast],
195-
))
196-
} else {
197-
cast
198-
}))
199-
}
200-
201-
/// Reached only when the plan was not simplified.
202178
fn invoke_with_args(&self, args: ScalarFunctionArgs) -> Result<ColumnarValue> {
203179
let [zone, input] = take_function_args(self.name(), &args.args)?;
204180
let zone = match zone {
@@ -231,10 +207,8 @@ mod tests {
231207

232208
use super::TimezoneFunc;
233209

234-
/// `invoke_with_args` must agree with the lowering that `simplify` does,
235-
/// for a plan that the simplifier did not visit.
236210
#[test]
237-
fn invoke_without_simplify() {
211+
fn invoke() {
238212
// 2024-01-01T12:00:00Z
239213
let micros = 1_704_110_400_000_000;
240214
let cases = [

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

Lines changed: 4 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -934,22 +934,10 @@ impl<S: ContextProvider> SqlToRel<'_, S> {
934934
}
935935
}
936936

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-
)))
937+
not_impl_err!(
938+
"AT TIME ZONE needs an ExprPlanner that implements `plan_at_time_zone`, \
939+
such as the `DatetimeFunctionPlanner` of `datafusion-functions`"
940+
)
953941
}
954942

955943
fn sql_position_to_expr(

‎datafusion/sql/tests/sql_integration.rs‎

Lines changed: 7 additions & 24 deletions
Original file line numberDiff line numberDiff line change
@@ -4070,33 +4070,16 @@ fn plan_at_time_zone_with_datetime_planner() {
40704070
);
40714071
}
40724072

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`.
4073+
/// Without an `ExprPlanner` for `AT TIME ZONE`, planning fails for every input,
4074+
/// as for `EXTRACT`. The SQL planner does not know the input type after type
4075+
/// coercion, so it cannot choose a lowering on its own.
40754076
#[test]
4076-
fn plan_at_time_zone_on_naive_timestamp_without_planner() {
4077-
let plan =
4078-
logical_plan("SELECT birth_date AT TIME ZONE 'America/Denver' FROM person")
4079-
.unwrap();
4080-
assert_snapshot!(
4081-
plan,
4082-
@r#"
4083-
Projection: CAST(person.birth_date AS Timestamp(ns, "America/Denver"))
4084-
TableScan: person
4085-
"#
4086-
);
4087-
}
4088-
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.
4091-
#[test]
4092-
fn plan_at_time_zone_on_tz_aware_timestamp_without_planner() {
4093-
let err = logical_plan(
4094-
"SELECT (birth_date AT TIME ZONE 'UTC') AT TIME ZONE 'America/Denver' FROM person",
4095-
)
4096-
.unwrap_err();
4077+
fn plan_at_time_zone_without_planner() {
4078+
let err = logical_plan("SELECT birth_date AT TIME ZONE 'America/Denver' FROM person")
4079+
.unwrap_err();
40974080
assert!(
40984081
err.strip_backtrace()
4099-
.contains("AT TIME ZONE on a timezone-aware timestamp"),
4082+
.contains("AT TIME ZONE needs an ExprPlanner"),
41004083
"unexpected error: {err}"
41014084
);
41024085
}

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

Lines changed: 45 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -4442,7 +4442,7 @@ statement error DataFusion error: Error during planning: Function 'to_local_time
44424442
select to_local_time('2024-04-01T00:00:20Z');
44434443

44444444
# invalid timezone
4445-
statement error DataFusion error: Optimizer rule 'simplify_expressions' failed[\s\S]*Arrow error: Parser error: Invalid timezone "Europe/timezone": failed to parse timezone
4445+
statement error DataFusion error: Arrow error: Parser error: Invalid timezone "Europe/timezone": failed to parse timezone
44464446
select to_local_time('2024-04-01T00:00:20Z'::timestamp AT TIME ZONE 'Europe/timezone');
44474447

44484448
# valid query
@@ -4645,10 +4645,14 @@ SELECT column1 FROM t_utc WHERE column1 < '2024-02-01T00:00:00' AT TIME ZONE 'Am
46454645
2024-01-01T00:00:01Z
46464646
2024-02-01T00:00:01Z
46474647

4648+
# No row matches: the row `2024-02-01T00:00:01+01:00` is 2024-01-31T23:00:01Z,
4649+
# and the literal is 2024-02-01T00:00:01Z. DuckDB also returns no row. Before
4650+
# `AT TIME ZONE` became the `timezone` function, it was a `CAST`, and
4651+
# `unwrap_cast_in_comparison` relabelled the literal instead of converting it
4652+
# (https://github.com/apache/datafusion/issues/25095), so this row matched.
46484653
query P
46494654
SELECT column1 FROM t_europe WHERE column1 = '2024-01-31T16:00:01' AT TIME ZONE 'America/Los_Angeles';
46504655
----
4651-
2024-02-01T00:00:01+01:00
46524656

46534657
query P
46544658
SELECT column1 FROM t_europe WHERE column1 BETWEEN '2020-01-01T00:00:00' AT TIME ZONE 'Australia/Brisbane' AND '2024-02-01T00:00:00' AT TIME ZONE 'America/Los_Angeles';
@@ -5964,30 +5968,61 @@ FROM (SELECT arrow_cast(naive, 'Dictionary(Int32, Timestamp(Nanosecond, None))')
59645968
----
59655969
Timestamp(ns, "America/Denver") 2024-01-01T12:00:00-07:00
59665970

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.
5971+
# `PREPARE` runs the optimizer before type coercion, and an untyped placeholder
5972+
# has no type until `EXECUTE`. The branch is still chosen from the final type.
59705973
statement ok
59715974
PREPARE at_tz_p AS SELECT arrow_typeof($1 AT TIME ZONE 'America/Denver'), $1 AT TIME ZONE 'America/Denver'
59725975

59735976
query TP
59745977
EXECUTE at_tz_p(arrow_cast('2024-01-01T12:00:00Z', 'Timestamp(Nanosecond, Some("UTC"))'))
59755978
----
5976-
Timestamp(ns, "America/Denver") 2024-01-01T05:00:00-07:00
5979+
Timestamp(ns) 2024-01-01T05:00:00
5980+
5981+
query TP
5982+
EXECUTE at_tz_p(arrow_cast('2024-01-01T12:00:00', 'Timestamp(Nanosecond, None)'))
5983+
----
5984+
Timestamp(ns, "America/Denver") 2024-01-01T12:00:00-07:00
59775985

59785986
statement ok
59795987
DEALLOCATE at_tz_p
59805988

5981-
# The plan lowers to CAST / to_local_time after simplification. The column is
5982-
# named with the operator syntax.
5989+
statement ok
5990+
PREPARE at_tz_case_p(boolean) AS
5991+
SELECT arrow_typeof((CASE WHEN b THEN naive ELSE aware END) AT TIME ZONE 'America/Denver'),
5992+
(CASE WHEN b THEN naive ELSE aware END) AT TIME ZONE 'America/Denver'
5993+
FROM at_tz_case WHERE b = $1
5994+
5995+
query TP
5996+
EXECUTE at_tz_case_p(true)
5997+
----
5998+
Timestamp(ns) 2024-01-01T05:00:00
5999+
6000+
statement ok
6001+
DEALLOCATE at_tz_case_p
6002+
6003+
# A view over such a CASE
6004+
statement ok
6005+
CREATE VIEW at_tz_case_v AS SELECT CASE WHEN b THEN naive ELSE aware END AS x FROM at_tz_case
6006+
6007+
query TP
6008+
SELECT arrow_typeof(x AT TIME ZONE 'America/Denver'), x AT TIME ZONE 'America/Denver' FROM at_tz_case_v
6009+
----
6010+
Timestamp(ns) 2024-01-01T05:00:00
6011+
6012+
statement ok
6013+
DROP VIEW at_tz_case_v
6014+
6015+
# `AT TIME ZONE` stays a `timezone` call in the plan. It is not rewritten into
6016+
# a `CAST` during planning, because the type at that point is not always final.
6017+
# The column is named with the operator syntax.
59836018
query TT
59846019
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
59856020
----
59866021
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'
6022+
01)Projection: timezone(Utf8("America/Denver"), CASE WHEN at_tz_case.b THEN CAST(at_tz_case.naive AS Timestamp(ns, "UTC")) ELSE at_tz_case.aware END), timezone(Utf8("America/Denver"), at_tz_case.naive)
59886023
02)--TableScan: at_tz_case projection=[aware, naive, b]
59896024
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']
6025+
01)ProjectionExec: expr=[timezone(America/Denver, CASE WHEN b@2 THEN CAST(naive@1 AS Timestamp(ns, "UTC")) ELSE aware@0 END) as CASE WHEN at_tz_case.b THEN at_tz_case.naive ELSE at_tz_case.aware END AT TIME ZONE 'America/Denver', timezone(America/Denver, naive@1) as at_tz_case.naive AT TIME ZONE 'America/Denver']
59916026
02)--DataSourceExec: partitions=1, partition_sizes=[1]
59926027

59936028
# `timezone(zone, expression)` is the function form, as in PostgreSQL and DuckDB

0 commit comments

Comments
 (0)