[
https://issues.apache.org/jira/browse/FLINK-40897?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18121974#comment-18121974
]
sepuri sai krishna commented on FLINK-40897:
--------------------------------------------
I would like to work on this. I have a patch and regression tests ready, with
red-green verification against the existing TemporalTypesTest suite. Could
someone assign the ticket to me?
> TIMESTAMPDIFF returns wrong results and fails with scala.MatchError for TIME
> operands
> -------------------------------------------------------------------------------------
>
> Key: FLINK-40897
> URL: https://issues.apache.org/jira/browse/FLINK-40897
> Project: Flink
> Issue Type: Bug
> Components: Table SQL / Planner
> Affects Versions: 2.3.0, 2.4.0
> Reporter: sepuri sai krishna
> Priority: Major
>
> {{TIMESTAMPDIFF}} misbehaves in two ways when one or both operands are
> {{TIME}}. Both are reproducible on {{master}} and on released 2.3.0.
> h3. 1. Wrong results for year-month units between two TIME values
> {code:sql}
> SELECT TIMESTAMPDIFF(YEAR, TIME '12:00:00', TIME '13:00:00'); -- returns
> 9856
> SELECT TIMESTAMPDIFF(QUARTER, TIME '12:00:00', TIME '13:00:00'); -- returns
> 39425
> SELECT TIMESTAMPDIFF(MONTH, TIME '12:00:00', TIME '13:00:00'); -- returns
> 118277
> {code}
> All three should be {{0}}: the two values are one hour apart.
> Flink contradicts itself here. Casting the second operand to {{TIMESTAMP}}
> does
> not change the meaning of the query, but it does change the answer:
> || Expression || Returns ||
> | {{TIMESTAMPDIFF(MONTH, TIME '12:00:00', TIME '13:00:00')}} | {{118277}} |
> | {{TIMESTAMPDIFF(MONTH, TIME '12:00:00', CAST(TIME '13:00:00' AS
> TIMESTAMP))}} | {{0}} |
> | {{TIMESTAMPDIFF(YEAR, TIME '12:00:00', CAST(TIME '13:00:00' AS
> TIMESTAMP))}} | {{0}} |
> The day-time units on the same pair are correct
> ({{TIMESTAMPDIFF(SECOND, TIME '12:00:00', TIME '13:00:00')}} returns
> {{3600}}),
> so on {{master}} only the {{TIME}}/{{TIME}} year-month combination is
> affected.
> On 2.3.0 the {{TIME}}/{{DATE}} pairs are wrong as well:
> || Expression || 2.3.0 || master || Expected ||
> | {{TIMESTAMPDIFF(MONTH, TIME '12:00:00', DATE '2021-03-31')}} | {{-1418716}}
> | {{614}} | {{614}} |
> | {{TIMESTAMPDIFF(MONTH, DATE '2021-03-31', TIME '12:00:00')}} | {{1418716}}
> | {{-614}} | {{-614}} |
> h3. 2. scala.MatchError for day-time units mixing TIME with DATE or TIMESTAMP
> {code:sql}
> SELECT TIMESTAMPDIFF(DAY, TIME '12:00:00', TIMESTAMP '2021-03-31 02:00:00');
> SELECT TIMESTAMPDIFF(DAY, TIMESTAMP '2021-03-31 02:00:00', TIME '12:00:00');
> SELECT TIMESTAMPDIFF(HOUR, TIME '12:00:00', DATE '2021-03-31');
> SELECT TIMESTAMPDIFF(DAY, DATE '2021-03-31', TIME '12:00:00');
> {code}
> {noformat}
> scala.MatchError: (TIMESTAMP_WITHOUT_TIME_ZONE,TIME_WITHOUT_TIME_ZONE) (of
> class scala.Tuple2)
> at
> org.apache.flink.table.planner.codegen.calls.ScalarOperatorGens$.$anonfun$generateTemporalPlusMinus$9(ScalarOperatorGens.scala:261)
> at
> org.apache.flink.table.planner.codegen.calls.ScalarOperatorGens$.$anonfun$generateOperatorIfNotNull$1(ScalarOperatorGens.scala:2053)
> at
> org.apache.flink.table.planner.codegen.GenerateUtils$.generateCallIfArgsNotNull(GenerateUtils.scala:59)
> at
> org.apache.flink.table.planner.codegen.calls.ScalarOperatorGens$.generateTemporalPlusMinus(ScalarOperatorGens.scala:260)
> {noformat}
> Every day-time unit ({{DAY}}, {{HOUR}}, {{MINUTE}}, {{SECOND}}, {{WEEK}})
> fails
> for these operand pairs, in both orders. On {{master}} the year-month units on
> the same pairs succeed and return correct values; on 2.3.0 see the table
> above.
> h3. Impact
> Symptom 1 is silent: a wrong number is returned with nothing logged, so a
> query
> that groups or filters on the result produces incorrect output with no
> indication of a problem.
> Symptom 2 is a plan-time failure, so the query cannot run at all.
> h3. Scope
> Verified against constant-folded literals and against real table columns, on
> {{master}} and on the released 2.3.0 jars.
> Related: FLINK-39385 fixed the same {{MatchError}} for the {{TIME}}/{{TIME}}
> pair in 2.3.0. The pairs above were not covered.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
