[ 
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)

Reply via email to