[
https://issues.apache.org/jira/browse/FLINK-41596?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18148548#comment-18148548
]
sepuri sai krishna commented on FLINK-41596:
--------------------------------------------
Found this while working on the review comments of
https://github.com/apache/flink/pull/29366 (FLINK-40897). One of its TIME tests
expected a -1 that comes from this, so I am taking that test out of that PR. I
would like to work on this. Could it be assigned to me?
> 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
> Priority: Major
>
> 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)