[CALCITE-5981] TIMESTAMPDIFF function returns incorrect result

• Type: Bug
• Status: Closed
• Priority: Minor
• Resolution: Fixed
• Affects Version/s: 1.23.0
• Fix Version/s: avatica-1.24.0
• Component/s: avatica
• Labels: pull-request-available
• Assignee: Mihai Budiu
• Reporter: Mihai Budiu
• Created: 07/Sep/23 01:26
• Updated: 03/Dec/23 21:18
• Resolved: 14/Sep/23 15:31

Description

The following test fails, when added to SqlOperatorTest:

f.checkScalar("timestampdiff(month, DATE '2004-02-29', DATE '2005-02-28')",
        "11", "INTEGER NOT NULL");

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

 

Issue Links

• links to: GitHub Pull Request #226