Skip to content

Commit b7ccbbd

Browse files
adriangbclaude
andauthored
fix: coerce timezone-naive minus timezone-aware timestamps at equal time units (#25240)
## Which issue does this PR close? - Part of #13212. It does not close it: the session time zone is not consulted yet, this only makes `-` agree with `=` and with itself. ## Rationale for this change Subtracting a timezone-naive timestamp from a timezone-aware one gives a different answer depending on the *time unit* of the aware operand, and disagrees with `=` on the same pair of values. No session time zone is involved. On `main`: ```sql CREATE TABLE t AS SELECT arrow_cast('2024-11-01T00:00:00-04:00', 'Timestamp(Nanosecond, Some("America/New_York"))') AS ts_tz_ns, arrow_cast('2024-11-01T00:00:00-04:00', 'Timestamp(Millisecond, Some("America/New_York"))') AS ts_tz_ms, '2024-11-01T00:00:00'::timestamp AS ts; SELECT ts_tz_ns - ts, ts_tz_ms - ts, ts_tz_ns = ts FROM t; -- main: 4 hours | 0 hours | true ``` The two values compare equal yet are four hours apart, and the answer flips with the storage unit. PostgreSQL 17 and DuckDB 1.5.2, with the session time zone set to `America/New_York` (the only zone in play here, since DataFusion has no session zone set), both return `00:00:00` and `true`. The cause is in `BinaryTypeCoercer::signature_inner`. For `Minus`, the first thing the arithmetic arm does is ask arrow for a result type. `Timestamp(u, Some(tz)) - Timestamp(u, None)` at *equal* units is the one mixed pair arrow can subtract directly, so no cast is inserted and arrow subtracts the raw values, reading the naive operand as UTC. Every other unit pairing fails that probe and falls through to `temporal_coercion_strict_timezone`, which casts the naive operand to the aware operand's zone, which is also what `=` and the other comparisons do. ## What changes are included in this PR? For `Minus` on a mixed timezone-aware / timezone-naive pair, coerce through `temporal_coercion_strict_timezone` before the arrow probe, so every unit pairing inserts the same cast. The naive operand is then read in the aware operand's time zone at every unit, as `=` already reads it. Nothing else changes: pairs that are both aware or both naive, and pairs of two different aware zones, are untouched. ## What is the testing strategy for this PR? - `test_timestamp_minus_mixed_timezone_awareness` in `datafusion/expr-common/src/type_coercion/binary/tests/arithmetic.rs` pins the coerced types for both operand orders at equal and differing units, and the untouched cases. - An slt block in `datafusion/sqllogictest/test_files/datetime/timestamps.slt` with the reproduction above, both operand orders, and the plan showing the inserted cast. Each expectation is the value PostgreSQL and DuckDB return. - The full sqllogictest suite passes; no other expectation changes. ## Are there any user-facing changes? Yes, documented in the 56.0.0 upgrade guide: `Timestamp(u, Some(tz)) - Timestamp(u, None)` at equal units now reads the naive operand in `tz` rather than as UTC. With the values above, `ts_tz - ts` changes from 4 hours to 0 hours, matching `ts_tz = ts` being true. 🤖 Generated with [Claude Code](https://claude.com/claude-code) --------- Co-authored-by: Claude Fable 5.1 <[email protected]>
1 parent 33574a1 commit b7ccbbd

4 files changed

Lines changed: 143 additions & 0 deletions

File tree

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

Lines changed: 28 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -285,6 +285,34 @@ impl<'a> BinaryTypeCoercer<'a> {
285285
return Ok(Signature { lhs, rhs, ret });
286286
}
287287
Plus | Minus | Multiply | Divide | Modulo => {
288+
// `Timestamp(u, Some(tz)) - Timestamp(u, None)` at *equal* units is
289+
// the one mixed timezone-aware/naive pair arrow can subtract
290+
// directly, so the `self.get_result` probe below would accept it
291+
// and no cast would be inserted: arrow then subtracts the raw
292+
// values, i.e. reads the naive operand as UTC. Every other unit
293+
// pairing falls through to `temporal_coercion_strict_timezone`
294+
// below and reads the naive operand in the aware operand's zone,
295+
// as comparisons already do. Coerce here so that all unit pairings
296+
// agree.
297+
if self.op == &Minus
298+
&& matches!(
299+
(lhs, rhs),
300+
(Timestamp(_, Some(_)), Timestamp(_, None))
301+
| (Timestamp(_, None), Timestamp(_, Some(_)))
302+
)
303+
&& let Some(coerced) = temporal_coercion_strict_timezone(lhs, rhs)
304+
{
305+
let ret = self.get_result(&coerced, &coerced).map_err(|e| {
306+
plan_datafusion_err!(
307+
"Cannot get result type for temporal operation {coerced} {} {coerced}: {e}", self.op
308+
)
309+
})?;
310+
return Ok(Signature {
311+
lhs: coerced.clone(),
312+
rhs: coerced,
313+
ret,
314+
});
315+
}
288316
if let Ok(ret) = self.get_result(lhs, rhs) {
289317

290318
// Temporal arithmetic, e.g. Date32 + Interval

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

Lines changed: 29 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -475,3 +475,32 @@ fn test_decimal_precision_overflow_cross_variant() -> Result<()> {
475475

476476
Ok(())
477477
}
478+
479+
#[test]
480+
fn test_timestamp_minus_mixed_timezone_awareness() -> Result<()> {
481+
let aware_tz: Arc<str> = Arc::from("America/New_York");
482+
let aware_ms = DataType::Timestamp(Millisecond, Some(Arc::clone(&aware_tz)));
483+
let aware_ns = DataType::Timestamp(Nanosecond, Some(Arc::clone(&aware_tz)));
484+
let naive_ns = DataType::Timestamp(Nanosecond, None);
485+
486+
// Differing units already coerced to the aware zone; equal units did not.
487+
// Both must now coerce both operands to the aware zone at the finer unit,
488+
// in either operand order.
489+
test_coercion_binary_rule!(aware_ms, naive_ns, Operator::Minus, aware_ns);
490+
test_coercion_binary_rule!(naive_ns, aware_ms, Operator::Minus, aware_ns);
491+
test_coercion_binary_rule!(aware_ns, naive_ns, Operator::Minus, aware_ns);
492+
test_coercion_binary_rule!(naive_ns, aware_ns, Operator::Minus, aware_ns);
493+
494+
// Pairs that are both aware or both naive are untouched.
495+
test_coercion_binary_rule!(aware_ns, aware_ns, Operator::Minus, aware_ns);
496+
test_coercion_binary_rule!(naive_ns, naive_ns, Operator::Minus, naive_ns);
497+
// Two different aware zones keep their own types (arrow subtracts the
498+
// instants directly); unchanged by this rule.
499+
let other_tz = DataType::Timestamp(Nanosecond, Some(Arc::from("+08:00")));
500+
test_coercion_binary_rule!(aware_ns, other_tz, Operator::Minus, aware_ns, other_tz);
501+
502+
// For contrast, comparisons already coerced the mixed pair this way.
503+
test_coercion_binary_rule!(aware_ms, naive_ns, Operator::Eq, aware_ns);
504+
505+
Ok(())
506+
}

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

Lines changed: 70 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2079,6 +2079,76 @@ SELECT '2000-01-01T00:00:00'::timestamp - '2010-01-01T00:00:00'::timestamp;
20792079
----
20802080
-3653 days 0 hours 0 mins 0.000000000 secs
20812081

2082+
# Subtracting a timezone-naive timestamp from a timezone-aware one reads the
2083+
# naive operand in the aware operand's time zone at every time unit, as `=`
2084+
# does. At equal units arrow can subtract the raw values directly, which used to
2085+
# skip coercion and read the naive operand as UTC, so the answer depended on the
2086+
# time unit of the aware operand.
2087+
statement ok
2088+
CREATE TABLE mixed_tz_minus AS SELECT
2089+
arrow_cast('2024-11-01T00:00:00-04:00', 'Timestamp(Nanosecond, Some("America/New_York"))') AS ts_tz_ns,
2090+
arrow_cast('2024-11-01T00:00:00-04:00', 'Timestamp(Millisecond, Some("America/New_York"))') AS ts_tz_ms,
2091+
'2024-11-01T00:00:00'::timestamp AS ts;
2092+
2093+
# postgresql / duckdb (session time zone America/New_York): 00:00:00, 00:00:00, true
2094+
query ??B
2095+
SELECT ts_tz_ns - ts, ts_tz_ms - ts, ts_tz_ns = ts FROM mixed_tz_minus;
2096+
----
2097+
0 days 0 hours 0 mins 0.000000000 secs 0 days 0 hours 0 mins 0.000000000 secs true
2098+
2099+
query ??
2100+
SELECT ts - ts_tz_ns, ts - ts_tz_ms FROM mixed_tz_minus;
2101+
----
2102+
0 days 0 hours 0 mins 0.000000000 secs 0 days 0 hours 0 mins 0.000000000 secs
2103+
2104+
# The same at every time unit, with the aware and naive operands at equal units:
2105+
# seconds, milliseconds, microseconds and nanoseconds.
2106+
statement ok
2107+
CREATE TABLE mixed_tz_minus_units AS SELECT
2108+
arrow_cast('2024-11-01T00:00:00-04:00', 'Timestamp(Second, Some("America/New_York"))') AS ts_tz_s,
2109+
arrow_cast('2024-11-01T00:00:00-04:00', 'Timestamp(Millisecond, Some("America/New_York"))') AS ts_tz_ms,
2110+
arrow_cast('2024-11-01T00:00:00-04:00', 'Timestamp(Microsecond, Some("America/New_York"))') AS ts_tz_us,
2111+
arrow_cast('2024-11-01T00:00:00-04:00', 'Timestamp(Nanosecond, Some("America/New_York"))') AS ts_tz_ns,
2112+
arrow_cast('2024-11-01T00:00:00', 'Timestamp(Second, None)') AS ts_s,
2113+
arrow_cast('2024-11-01T00:00:00', 'Timestamp(Millisecond, None)') AS ts_ms,
2114+
arrow_cast('2024-11-01T00:00:00', 'Timestamp(Microsecond, None)') AS ts_us,
2115+
arrow_cast('2024-11-01T00:00:00', 'Timestamp(Nanosecond, None)') AS ts_ns;
2116+
2117+
# postgresql / duckdb (session time zone America/New_York): 00:00:00 for every unit
2118+
query ????
2119+
SELECT ts_tz_s - ts_s, ts_tz_ms - ts_ms, ts_tz_us - ts_us, ts_tz_ns - ts_ns FROM mixed_tz_minus_units;
2120+
----
2121+
0 days 0 hours 0 mins 0 secs 0 days 0 hours 0 mins 0.000 secs 0 days 0 hours 0 mins 0.000000 secs 0 days 0 hours 0 mins 0.000000000 secs
2122+
2123+
query ????
2124+
SELECT ts_s - ts_tz_s, ts_ms - ts_tz_ms, ts_us - ts_tz_us, ts_ns - ts_tz_ns FROM mixed_tz_minus_units;
2125+
----
2126+
0 days 0 hours 0 mins 0 secs 0 days 0 hours 0 mins 0.000 secs 0 days 0 hours 0 mins 0.000000 secs 0 days 0 hours 0 mins 0.000000000 secs
2127+
2128+
query BBBB
2129+
SELECT ts_tz_s = ts_s, ts_tz_ms = ts_ms, ts_tz_us = ts_us, ts_tz_ns = ts_ns FROM mixed_tz_minus_units;
2130+
----
2131+
true true true true
2132+
2133+
statement ok
2134+
DROP TABLE mixed_tz_minus_units
2135+
2136+
statement ok
2137+
SET datafusion.explain.logical_plan_only = true
2138+
2139+
query TT
2140+
EXPLAIN SELECT ts_tz_ns - ts FROM mixed_tz_minus;
2141+
----
2142+
logical_plan
2143+
01)Projection: mixed_tz_minus.ts_tz_ns - CAST(mixed_tz_minus.ts AS Timestamp(ns, "America/New_York"))
2144+
02)--TableScan: mixed_tz_minus projection=[ts_tz_ns, ts]
2145+
2146+
statement ok
2147+
SET datafusion.explain.logical_plan_only = false
2148+
2149+
statement ok
2150+
DROP TABLE mixed_tz_minus
2151+
20822152
# Interval - Timestamp => error
20832153
query error Cannot coerce arithmetic expression Interval\(MonthDayNano\) - Timestamp\(ns\) to valid types
20842154
SELECT i - ts1 from FOO;

‎docs/source/library-user-guide/upgrading/56.0.0.md‎

Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -122,6 +122,22 @@ Rewriting affected files with a current DataFusion version, for example using
122122
restores optimizations that depend on them. Preserve the required data ordering
123123
and ordering metadata when rewriting sorted files.
124124

125+
### `Timestamp(u, Some(tz)) - Timestamp(u, None)` reads the naive operand in `tz`
126+
127+
Subtracting a timezone-naive timestamp from a timezone-aware one _at the same
128+
time unit_ did not coerce at all in `55.0.0`: the naive operand was read as UTC,
129+
which differed both from `=` on the same pair of values and from every other
130+
pairing of time units. It now coerces like the rest, so the naive operand is
131+
read in the aware operand's time zone:
132+
133+
```sql
134+
-- ts_tz is 2024-11-01T00:00:00-04:00 (America/New_York), ts is the naive
135+
-- 2024-11-01T00:00:00; ts_tz = ts is true in both versions
136+
-- 55.0.0: 0 days 4 hours 0 mins 0.000000000 secs
137+
-- 56.0.0: 0 days 0 hours 0 mins 0.000000000 secs
138+
SELECT ts_tz - ts FROM timestamps;
139+
```
140+
125141
### `ForeignSession::create_physical_plan` is unsupported
126142

127143
`ForeignSession::create_physical_plan` no longer forwards to the library that

0 commit comments

Comments
 (0)