[
https://issues.apache.org/jira/browse/FLINK-41280?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Yuepeng Pan updated FLINK-41280:
--------------------------------
Affects Version/s: 2.4.0
> CAST of a TIMESTAMP before 1970 to DATE, TIME or a lower precision returns a
> wrong value
> ----------------------------------------------------------------------------------------
>
> Key: FLINK-41280
> URL: https://issues.apache.org/jira/browse/FLINK-41280
> Project: Flink
> Issue Type: Bug
> Components: Table SQL / Planner, Table SQL / Runtime
> Affects Versions: 2.3.0, 2.4.0
> Reporter: Ramin Gharib
> Assignee: Ramin Gharib
> Priority: Major
> Labels: pull-request-available
> Fix For: 2.5.0
>
>
> h3. Problem
> Three cast paths divide the epoch milliseconds of a {{TIMESTAMP}} with {{/}}
> or {{%}}, which Java rounds toward zero. A {{TIMESTAMP}} before 1970 has
> negative epoch milliseconds, so it moves toward 1970.
> * {{TimestampToDateCastRule}} generates {{(int) (ts.getMillisecond() /
> 86400000L)}}, so the cast to {{DATE}} lands on the next day.
> * {{DateTimeUtils.timestampWithoutLocalZoneToTime}} computes
> {{ts.getMillisecond() % 86400000L}}, so the cast to {{TIME}} gives a negative
> time of day.
> * {{DateTimeUtils.truncate}}, used by the cast to a lower {{TIMESTAMP(p)}} or
> {{TIMESTAMP_LTZ(p)}} precision, zeroes the last millisecond digits with
> {{/}}, so it rounds up instead of truncating.
> ||Input||Cast||Expected||Actual||
> |{{TIMESTAMP '1969-12-31 23:00:00'}}|{{AS
> DATE}}|{{1969-12-31}}|{{1970-01-01}}|
> |{{TIMESTAMP '1969-12-31 23:00:00'}}|{{AS TIME}}|{{23:00:00}}|{{-3600000}} ms
> of day, not a valid time|
> |{{TIMESTAMP(3) '1969-12-31 23:59:58.500'}}|{{AS TIMESTAMP(0)}}|{{1969-12-31
> 23:59:58}}|{{1969-12-31 23:59:59}}|
> |{{TIMESTAMP(3) '1969-12-31 23:59:59.250'}}|{{AS TIMESTAMP(1)}}|{{1969-12-31
> 23:59:59.2}}|{{1969-12-31 23:59:59.3}}|
> The values come from evaluating the generated expression and the helpers
> against the 2.3.0 jars. {{TIMESTAMP_LTZ}} to {{DATE}} and {{TIME}} is
> correct, because it converts through {{java.time}}.
> h3. Proposal
> Use floor division: {{Math.floorDiv}} in {{TimestampToDateCastRule}} and
> {{DateTimeUtils.zeroLastDigits}}, and {{Math.floorMod}} in
> {{DateTimeUtils.timestampWithoutLocalZoneToTime}}.
> h3. Tests
> * SQL cases on a column value for {{DATE}}, {{TIME}} and a lower
> {{TIMESTAMP}} precision before 1970, so the generated code runs rather than a
> folded literal.
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
