unwrap_cast_in_comparison drops the timezone shift when unwrapping CAST(timestamp AS timestamptz) = literal

[adriangb]

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

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:

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 #13212. Related: #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.