[I] `to_timestamp` with string input returns an array whose timezone does not match its declared return type [datafusion]

["adriangb (via GitHub)" <gi...@apache.org>]

adriangb opened a new issue, #24632:
URL: https://github.com/apache/datafusion/issues/24632

   ### Describe the bug
   
   When `datafusion.execution.time_zone` is set, `to_timestamp()` on a string input declares a
   return type of `Timestamp(ns, <tz>)` but produces an array of `Timestamp(ns, None)`. The
   declared type and the produced array disagree, so any plan that materializes the column
   fails at execution. Planning succeeds — `EXPLAIN` returns a plan for both queries below.
   
   ### To Reproduce
   
   datafusion-cli 55.0.0:
   
   ```sql
   SET datafusion.execution.time_zone = 'UTC';
   SELECT to_timestamp(s) AS v FROM (VALUES ('2026-01-01T00:00:00')) AS t(s);
   ```
   
   ```
   Error: Arrow error: Invalid argument error: column types must match schema types,
   expected Timestamp(ns, "UTC") but found Timestamp(ns) at column index 0
   ```
   
   Not specific to UTC:
   
   ```sql
   SET datafusion.execution.time_zone = 'America/New_York';
   SELECT to_timestamp(s) AS v FROM (VALUES ('2026-01-01T00:00:00')) AS t(s);
   -- expected Timestamp(ns, "America/New_York") but found Timestamp(ns) at column index 0
   ```
   
   A second symptom: because the planner trusts the declared type, it inserts no coercion
   before comparing against another timezone-aware value, so the comparison reaches the Arrow
   kernel with mismatched types:
   
   ```sql
   SET datafusion.execution.time_zone = 'UTC';
   SELECT to_timestamp(concat('2026-01-01T00:00:0', (value % 10)::text)) <= now() AS cmp
   FROM generate_series(1, 3) AS t(value);
   ```
   
   ```
   Error: Arrow error: Invalid argument error:
   Invalid comparison operation: Timestamp(ns) <= Timestamp(ns, "UTC")
   ```
   
   Unaffected, for contrast:
   
   - Numeric input: `to_timestamp(1767225600)` returns `Timestamp(ns, "UTC")`; declared and
     actual agree.
   - Default config (`datafusion.execution.time_zone` unset): declared is `Timestamp(ns, None)`
     and matches the array, so nothing fails.
   
   ### Expected behavior
   
   `to_timestamp` on a string input should produce an array annotated with the execution
   timezone, matching what `return_type` advertises.
   
   `return_type` returns `Timestamp(Nanosecond, self.timezone.clone())`, and the string branch
   does receive the configured timezone (it is passed into the parse helper). The gap appears
   to be that the resulting array is not annotated with it.
   
   ### Additional context
   
   Reproduced on datafusion-cli 54.0.0 and 55.0.0.
   
   Related:
   - #17998 / #19078 — made `to_timestamp` respect `datafusion.execution.time_zone`.
   - #23841 — a separate issue in the same family from #19078 (already-zoned inputs having
     their timezone discarded).
   - #17993 / #18017 — the equivalent change for `now()`, which reads the config at execution
     time rather than capturing it.
   - #13351 — umbrella issue for PostgreSQL-consistent `to_timestamp` semantics.
   - #12892 — the same config-awareness gap in `from_unixtime`.
   


-- 
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: github-unsubscribe@datafusion.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org

Re: [I] `to_timestamp` with string input returns an array whose timezone does not match its declared return type [datafusion]

["adriangb (via GitHub)" <gi...@apache.org>]

adriangb closed issue #24632: `to_timestamp` with string input returns an array whose timezone does not match its declared return type
URL: https://github.com/apache/datafusion/issues/24632


-- 
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: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org