Comparing a timestamp cast to a time zone against a literal returns wrong results #25941

Activity

1. neilconway

   added

   bugSomething isn't workingSomething isn't working

   on Oct 1, 2026
2. DHRUV6029 commented on Oct 5, 2026

   

   DHRUV6029

   on Oct 5, 2026

   More actions

   take
3. github-actions

   assigned

   DHRUV6029

   on Oct 5, 2026
4. DHRUV6029 commented on Oct 5, 2026

   

   DHRUV6029

   on Oct 5, 2026

   More actions

   Hi @Ruchirtripathi, thanks for working on #25099!

   While digging into this issue I realized it's the same bug as #25095, just triggered a bit differently. It uses a fixed-offset time zone like +05:00 or -03:00 instead of a named zone like Asia/Singapore. For example:

   CREATE TABLE t AS VALUES (TIMESTAMP '2024-01-01 10:00:00');
   
   SELECT count(*) FROM t
   WHERE arrow_cast(column1, 'Timestamp(Nanosecond, Some("+05:00"))')
       = arrow_cast('2024-01-01T10:00:00+05:00', 'Timestamp(Nanosecond, Some("+05:00"))');
   -- returns 0 on main, should be 1

   The root cause is the same: the unwrap_cast rewrite copies the literal's value without applying the timezone shift.

   I applied the change from #25099 on top of current main (6e319bddd) and ran all the queries from this issue. They return the right results now.

   Since #25099 fixes both issues, could you add Closes #25941 to its description? That way both get closed when it merges.

   Thanks!
5. AdamGS commented on Nov 2, 2026

   

   AdamGS

   on Nov 2, 2026

   Contributor

   More actions

   On current main (9411463), this issue seems to be solved, the following SLT tests passes:

   statement ok
   CREATE TABLE t AS VALUES (TIMESTAMP '2024-01-01 10:00:00');
   
   statement ok
   CREATE TABLE l AS SELECT arrow_cast('2024-01-01T10:00:00+05:00', 'Timestamp(Nanosecond, Some("+05:00"))') AS v;
   
   # Both sides are 2024-01-01T10:00:00+05:00
   query PP
   SELECT arrow_cast(column1, 'Timestamp(Nanosecond, Some("+05:00"))') AS lhs, v AS rhs FROM t, l;
   ----
   2024-01-01T10:00:00+05:00 2024-01-01T10:00:00+05:00
   
   # Compare against a column: 1 row (correct)
   query I
   SELECT count(*) FROM t, l
   WHERE arrow_cast(t.column1, 'Timestamp(Nanosecond, Some("+05:00"))') = l.v;
   ----
   1
   
   query I
   SELECT count(*) FROM t
   WHERE arrow_cast(column1, 'Timestamp(Nanosecond, Some("+05:00"))')
       = arrow_cast('2024-01-01T10:00:00+05:00', 'Timestamp(Nanosecond, Some("+05:00"))');
   ----
   1
   
   
   statement ok
   SET datafusion.execution.time_zone = '+05:00';
   
   statement ok
   CREATE TABLE t2 AS VALUES (TIMESTAMP '2024-01-01 10:00:00');
   
   
   # 2024-01-01T10:00:00+05:00
   query P
   SELECT column1::timestamptz FROM t2;
   ----
   2024-01-01T10:00:00+05:00
   
   # 0
   query I
   SELECT count(*) FROM t2 WHERE column1::timestamptz = TIMESTAMPTZ '2024-01-01 10:00:00+05:00';
   ----
   1
   
   statement ok
   RESET datafusion.execution.time_zone;
   
   statement ok
   DROP TABLE t;
   
   statement ok
   DROP TABLE l;
   
   statement ok
   DROP TABLE t2;
   
6. alamb

   closed this as completedin #27018on Nov 2, 2026
7. alamb

   added a commit that references this issue on Nov 2, 2026

   test: Add regression tests for some `unwrap_cast` related bugs that d…

   ...

   This issue will close once commit 17401bb is merged into the 'main' branch.

   17401bb