sepuri sai krishna created FLINK-41596:
------------------------------------------
Summary: TIMESTAMPDIFF with MONTH, QUARTER or YEAR is off by one
when the second argument is earlier
Key: FLINK-41596
URL: https://issues.apache.org/jira/browse/FLINK-41596
Project: Flink
Issue Type: Bug
Components: Table SQL / Runtime
Affects Versions: 2.3.0, 2.4.0
Reporter: sepuri sai krishna
When the second argument of TIMESTAMPDIFF is earlier than the first, MONTH,
QUARTER and YEAR results can be one too far from zero. Swapping the arguments
changes more than the sign:
{code:sql}
SELECT TIMESTAMPDIFF(MONTH, TIMESTAMP '2021-01-04 00:00:00', TIMESTAMP
'2021-02-04 00:00:01'); -- 1
SELECT TIMESTAMPDIFF(MONTH, TIMESTAMP '2021-02-04 00:00:01', TIMESTAMP
'2021-01-04 00:00:00'); -- -2
SELECT TIMESTAMPDIFF(MONTH, TIMESTAMP '1970-01-01 00:00:01', TIMESTAMP
'1970-01-01 23:59:59'); -- 0
SELECT TIMESTAMPDIFF(MONTH, TIMESTAMP '1970-01-01 23:59:59', TIMESTAMP
'1970-01-01 00:00:01'); -- -1
{code}
The second and fourth should be -1 and 0, which is what MySQL returns.
The same happens with TIMESTAMP_LTZ, with a DATE and a TIMESTAMP, and with
timestampDiff in the Table API.
Checked on 2.3.0 and master, JDK 17.
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)