Ramin Gharib created FLINK-41280:
------------------------------------
Summary: CAST of a TIMESTAMP before 1970 to DATE or TIME 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
Reporter: Ramin Gharib
h3. Problem
{{TimestampToDateCastRule}} generates {{(int) (ts.getMillisecond() /
86400000L)}}, and {{DateTimeUtils.timestampWithoutLocalZoneToTime}} computes
{{ts.getMillisecond() % 86400000L}}. Java rounds both toward zero, so a
{{TIMESTAMP}} before 1970, which has negative epoch milliseconds, lands on the
wrong day and on a negative time of day.
||Input||{{CAST(... AS DATE)}} expected||actual||{{CAST(... AS TIME)}}
expected||actual||
|{{TIMESTAMP '1969-12-31
23:00:00'}}|{{1969-12-31}}|{{1970-01-01}}|{{23:00:00}}|{{-3600000}} ms of day,
not a valid time|
|{{TIMESTAMP '1969-12-31
12:00:00'}}|{{1969-12-31}}|{{1970-01-01}}|{{12:00:00}}|{{-43200000}} ms of day,
not a valid time|
The values come from evaluating the generated expression and the helper against
the 2.3.0 jars. {{TIMESTAMP_LTZ}} to {{DATE}} and {{TIME}} is correct, because
it converts through {{java.time}}.
h3. Proposal
Use {{Math.floorDiv}} in {{TimestampToDateCastRule}} and {{Math.floorMod}} in
{{DateTimeUtils.timestampWithoutLocalZoneToTime}}.
h3. Tests
* Cast rule cases for timestamps before 1970, including the last millisecond of
1969-12-31, for {{DATE}} and {{TIME(p)}}.
* A SQL case on a column value, so the generated code runs rather than a folded
literal.
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
