andygrove opened a new issue, #7209:
URL: https://github.com/apache/datafusion-comet/issues/7209
### Describe the bug
The native string-to-timestamp cast rejects several zone suffixes that Spark
accepts. Spark parses the zone with `ZoneId.of` (after its own `getZoneId`
rewrites), so any id Java knows works. For these inputs Comet returns NULL with
ANSI off and from `try_cast`. Under ANSI, the default on Spark 4, it raises
`CAST_INVALID_INPUT`. Spark returns a value:
| Input | Spark (UTC session) |
|---|---|
| `2020-01-01 12:34:56 PST` | `2020-01-01 20:34:56` |
| `2020-01-01 12:34:56 CET` | `2020-01-01 11:34:56` |
| `2020-01-01 12:34:56Zulu` | `2020-01-01 12:34:56` |
| `2020-01-01 12:34:56America/Los_Angeles` (no space) | `2020-01-01
20:34:56` |
| `2020-01-01 12:34:56 +08:00:00` | `2020-01-01 04:34:56` |
| `2020-01-01 12:34:56 Etc/GMT+8` | `2020-01-01 20:34:56` |
The same happens for `TIMESTAMP_NTZ` and in an `America/Los_Angeles`
session. `Etc/GMT+8` appears to be caught by the `GMT` prefix rule in
`extract_offset_suffix`. Spark resolves `PST` through `ZoneId.SHORT_IDS`, which
the native parser does not consult. The other forms (`Z`, `UTC`, `UTC+8`,
`GMT-08:00`, `+08`, `-0800`, `EST`, IANA names after a space) match Spark.
The 1.1.0 release has the same gaps by code: `extract_offset_suffix` and
chrono-tz 0.10.4 are unchanged since then.
### Steps to reproduce
Spark 4.1, default configs, UTC session:
```scala
Seq("2020-01-01 12:34:56 PST", "2020-01-01
12:34:56Zulu").toDF("s").write.parquet("/tmp/tz")
spark.read.parquet("/tmp/tz").selectExpr("s", "CAST(s AS TIMESTAMP)
t").show()
```
- Spark: both rows parse.
- Comet: the query fails with `[CAST_INVALID_INPUT] The value '2020-01-01
12:34:56 PST' of the type "STRING" cannot be cast to "TIMESTAMP"`. With
`spark.sql.ansi.enabled=false`, or with `try_cast`, `t` is NULL.
Note that `unix_micros(CAST(s AS TIMESTAMP))` matches Spark, because
`unix_micros` goes through the codegen dispatcher, which evaluates the cast in
Spark's code.
### Expected behavior
The same timestamps as Spark, or a fallback to Spark for zone suffixes the
native parser does not resolve.
### Additional context
Found in the branch-1.2 release audit (#7204). It is not a 1.2 regression.
Related but different: #5674 and #6329 (both closed).
--
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]
