sepuri sai krishna created FLINK-40897:
------------------------------------------
Summary: 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
{{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)
