Skip to content

Native string to timestamp cast returns NULL for zone offsets with a non-zero seconds part #7224

Closed

Description

@ninglinLiu

Describe the bug

The native CAST(string AS TIMESTAMP) returns NULL for a zone offset that has a non-zero seconds part, and raises CAST_INVALID_INPUT under ANSI mode. Spark accepts these offsets through ZoneId.of and returns a value.

The cause is that the native cast resolves offsets to an arrow Tz, which has minute precision, so an offset such as +08:00:30 cannot be represented. This was already the behavior before #7215, which added the +HH:MM:SS form for offsets whose seconds part is zero.

CometCast reports String -> Timestamp as Compatible(), and the compatibility guide says Comet supports all formats Spark accepts, so this is a gap in that claim.

Steps to reproduce

SELECT cast('2020-01-01 12:34:56 +08:00:30' AS timestamp);
SELECT cast('2020-01-01 12:34:56 UTC+08:00:30' AS timestamp);
SELECT cast('2020-01-01 12:34:56 -01:30:15' AS timestamp);

Spark returns 2020-01-01 04:34:26 (session time zone UTC) for the first query. Comet returns NULL, or raises CAST_INVALID_INPUT with spark.sql.ansi.enabled=true.

Expected behavior

Either the native cast handles second-precision offsets (for example by applying the offset in seconds instead of building a Tz), or Comet falls back to Spark for these inputs, and the compatibility guide documents the gap until then.

Additional context

Raised by @andygrove in the review of #7215.

This issue was written with AI assistance.

Metadata

Metadata

Assignees

Labels

area:expressionsExpression evaluationbugSomething isn't workingcorrectnesspriority:criticalData corruption, silent wrong results, security issues

Type

No type

Projects

No projects

    Milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions