[
https://issues.apache.org/jira/browse/FLINK-41280?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Ramin Gharib updated FLINK-41280:
---------------------------------
Description:
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.
was:
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.
Summary: CAST of a TIMESTAMP before 1970 to DATE, TIME or a lower
precision returns a wrong value (was: CAST of a TIMESTAMP before 1970 to DATE
or TIME returns a wrong value)
> 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
> Reporter: Ramin Gharib
> Assignee: Ramin Gharib
> Priority: Major
> Labels: pull-request-available
>
> 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)
