[
https://issues.apache.org/jira/browse/CALCITE-5981?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17762847#comment-17762847
]
Mihai Budiu commented on CALCITE-5981:
--------------------------------------
The TimestampDiffConvertlet seems correct, it converts the call of
timestampdiff(unit, end, start) to CAST(Reinterpret(Minus(end, start, month))):
The actual bug is in Avatica, in DateTimeUtils.subtractMonths, which doesn't
handle correctly the leap years.
subtractMonths(date0, date1) tries to find a number of months m such that
date1 + m - 1 <= date0 and date1 + m > date0
In our case date0 = "2005-02-28" and date1 = "2004-02-29".
date1 + 11 = "2005-01-29"
date1 + 12 = "2005-02-28" (because 2005-02-29 does not exist).
The correct m is 11, but this code chooses 12.
I think this code could be much simpler and does not need to iterate or guess m.
I will try to find the Avatica repository and submit a fix there.
> TIMESTAMPDIFF function returns incorrect result
> ------------------------------------------------
>
> Key: CALCITE-5981
> URL: https://issues.apache.org/jira/browse/CALCITE-5981
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.35.0
> Reporter: Mihai Budiu
> Priority: Minor
>
> The following test fails, when added to SqlOperatorTest:
> {code:java}
> f.checkScalar("timestampdiff(month, DATE '2004-02-29', DATE '2005-02-28')",
> "11", "INTEGER NOT NULL");
> {code}
> The result returned by the expression is 12. However, MySQL returns 11.
> The semantics of this function is not described clearly in the documentation,
> but according to several prior issues [1] [2] [3] the intended semantics
> should be the same as in MySQL.
> A corresponding MySQL test: [4]
> The implementation seems to be in
> StandardConvertletTable.TimestampDiffConvertlet.
> [1] https://issues.apache.org/jira/browse/CALCITE-1827,
> [2] https://issues.apache.org/jira/browse/CALCITE-3529,
> [3] https://issues.apache.org/jira/browse/CALCITE-1124.
> [4]
> [https://github.com/mysql/mysql-server/blob/ea1efa9822d81044b726aab20c857d5e1b7e046a/mysql-test/r/func_time.result#L1151]
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
