adriangb opened a new issue, #25095:
URL: https://github.com/apache/datafusion/issues/25095
### Describe the bug
Comparing a timezone-naive timestamp column against a `timestamptz` literal
returns the wrong rows whenever the session timezone is not UTC. The
optimizer's `unwrap_cast_in_comparison` rewrites `CAST(ts AS timestamptz) =
<literal>` into `ts = <literal re-labelled as naive>`, i.e. it treats the cast
as a pure re-labelling of the underlying integer. But casting `Timestamp(_,
None)` to `Timestamp(_, Some(tz))` *shifts* the instant by the timezone offset
(that is how `ts::timestamptz` correctly gives `2024-11-01T00:00:00+08:00` for
a naive `2024-11-01T00:00:00`), so the unwrapped predicate is off by exactly
the session offset.
The same rewrite fires for the implicit coercion `ts = <timestamptz
literal>`, so it is not limited to explicit casts. Column-vs-column
comparisons, where nothing gets unwrapped, are correct.
### To Reproduce
```sql
SET datafusion.execution.time_zone = 'Asia/Singapore'; -- +08:00
CREATE TABLE t AS SELECT TIMESTAMP '2024-11-01T00:00:00' AS ts;
CREATE TABLE u AS SELECT '2024-10-31T16:00:00Z'::timestamptz AS tstz;
-- (1) 2024-11-01 00:00 in Singapore *is* 2024-10-31 16:00 UTC, expect 1
SELECT count(*) AS q1 FROM t WHERE ts::timestamptz =
'2024-10-31T16:00:00Z'::timestamptz;
-- (2) wrong instant, expect 0
SELECT count(*) AS q2 FROM t WHERE ts::timestamptz =
'2024-11-01T00:00:00Z'::timestamptz;
-- (3) implicit coercion instead of an explicit cast, expect 1
SELECT count(*) AS q3 FROM t WHERE ts = '2024-10-31T16:00:00Z'::timestamptz;
-- (4) control: column vs column, nothing to unwrap, expect 1
SELECT count(*) AS q4 FROM t, u WHERE t.ts::timestamptz = u.tstz;
```
| | DataFusion 54.0.0 | PostgreSQL 17 | DuckDB 1.5.5 |
|---|---|---|---|
| q1 `ts::timestamptz = '…16:00Z'` | **0** | 1 | 1 |
| q2 `ts::timestamptz = '…00:00Z'` | **1** | 0 | 0 |
| q3 `ts = '…16:00Z'` (implicit) | **0** | 1 | 1 |
| q4 column vs column | 1 | 1 | 1 |
(PostgreSQL and DuckDB were run with `SET TimeZone = 'Asia/Singapore'`; the
rest of the script is identical.)
`EXPLAIN` shows the cast and the timezone are gone from the predicate; the
literal has been turned into a naive timestamp with the *same* integer value
the `timestamptz` literal had, `1730390400` = `2024-10-31T16:00:00Z`:
```
Filter: t.ts = TimestampNanosecond(1730390400000000000, None)
TableScan: t projection=[ts]
...
FilterExec: ts@0 = 1730390400000000000
```
whereas the cast that was unwrapped actually maps the column value to that
instant only after subtracting the offset:
```sql
SELECT to_unixtime(ts::timestamptz),
to_unixtime('2024-10-31T16:00:00Z'::timestamptz) FROM t;
-- 1730390400 | 1730390400 (equal, so q1 should match)
```
The unwrapping happens in `try_cast_literal_to_type`
(`datafusion/expr-common/src/casts.rs`), whose `cast_between_timestamp` only
rescales the time unit and ignores both sides' timezones; `unwrap_cast.rs` has
no timezone guard, and its only timezone test uses `UTC`, where the shift
happens to be zero.
### Expected behavior
`q1`/`q3` return 1 and `q2` returns 0, matching PostgreSQL and DuckDB and
matching DataFusion's own column-vs-column result. Either the unwrap should be
skipped when exactly one side of a `Timestamp -> Timestamp` cast carries a
(non-UTC) timezone, or the literal should be shifted the same way the cast
kernel would shift it.
### Additional context
Found while working on https://github.com/apache/datafusion/issues/13212.
Related: https://github.com/apache/datafusion/issues/25084 (the same naive →
named-timezone cast errors on DST boundaries).
Versions: `datafusion-cli` 54.0.0 (also reproduces on current `main`),
PostgreSQL 17, DuckDB 1.5.5.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
