[
https://issues.apache.org/jira/browse/FLINK-40731?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18117803#comment-18117803
]
sepuri sai krishna commented on FLINK-40731:
--------------------------------------------
To help pin the behaviour down, a few things I checked.
I attached `ConvertTzRepro.java`. It calls `DateTimeUtils.convertTz` directly
and
needs only released jars, no checkout or build. Same output on 1.20.4 and 2.2.1:
```
from to actual expected
'UTC' 'America/New_York' 2024-01-01 07:00:00 2024-01-01 07:00:00
'UTC' 'america/new_york' 2024-01-01 12:00:00 2024-01-01 07:00:00 <==
WRONG
'UTC' 'America/New York' 2024-01-01 12:00:00 2024-01-01 07:00:00 <==
WRONG
'UTC' 'Not/AZone' 2024-01-01 12:00:00 NULL <==
WRONG
'UTC' '+08:00' 2024-01-01 12:00:00 2024-01-01 20:00:00 <==
WRONG
'UTC' 'GMT+08:00' 2024-01-01 20:00:00 2024-01-01 20:00:00
```
Spark 3.5.3, run locally, raises on all three of its timezone functions:
```
convert_timezone('UTC', 'america/new_york', timestamp'2024-01-01 12:00:00')
from_utc_timestamp(timestamp'2024-01-01 12:00:00', 'america/new_york')
to_utc_timestamp(timestamp'2024-01-01 12:00:00', 'Not/AZone')
-> java.time.zone.ZoneRulesException: Unknown time-zone ID
```
Flink rejects too, everywhere except `CONVERT_TZ`. `TO_TIMESTAMP_LTZ` fails
validation for a literal and returns NULL when the zone comes from an
expression, and `table.local-time-zone` goes through
`TableConfigValidation.validateTimeZone`.
Nothing depends on the current behaviour: every `CONVERT_TZ` occurrence in the
test tree uses `Asia/Shanghai`, `PST` or `GMT`, so no test asserts the fallback.
It also does not look like a decision that was taken. The cache came over with
`SqlDateTimeUtils` when that moved into table-common in FLINK-24500, and I could
not find a ticket discussing unrecognized zones.
That leaves NULL, matching `TO_TIMESTAMP_LTZ` at runtime, or an error, matching
Spark. Do you have a preference?
> CONVERT_TZ silently returns a wrong timestamp for a time zone it does not
> recognize
> -----------------------------------------------------------------------------------
>
> Key: FLINK-40731
> URL: https://issues.apache.org/jira/browse/FLINK-40731
> Project: Flink
> Issue Type: Bug
> Components: Table SQL / Runtime
> Affects Versions: 2.4.0
> Reporter: sepuri sai krishna
> Priority: Major
> Labels: pull-request-available
> Attachments: ConvertTzRepro.java
>
>
> When {{CONVERT_TZ}} does not recognize a time zone it returns a
> converted-looking
> timestamp that is wrong, with no error and nothing logged. Zone ids are case
> sensitive, so an ordinary typo is enough:
> {code:sql}
> CONVERT_TZ('2024-01-01 12:00:00', 'UTC', 'America/New_York') -- 2024-01-01
> 07:00:00
> CONVERT_TZ('2024-01-01 12:00:00', 'UTC', 'america/new_york') -- 2024-01-01
> 12:00:00
> {code}
> Other bad input is already reported as NULL:
> {code:sql}
> CONVERT_TZ('not a timestamp', 'UTC', 'America/New_York') -- NULL
> {code}
> Reproduced on master. I have a fix and tests ready, could someone assign this
> to
> me?
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
