[CALCITE-3881] SqlFunctions#addMonths yields incorrect results in some corner case

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: avatica-1.16.0
• Fix Version/s: 1.23.0, avatica-1.18.0
• Component/s: avatica, core
• Labels: pull-request-available
• Assignee: Danny Chen
• Reporter: Zhenghua Gao
• Created: 27/Mar/20 12:06
• Updated: 18/May/21 11:23
• Resolved: 07/Apr/21 22:53

Description

SqlFunctions#addMonths use DateTimeUtils#ymdToUnixDate to calculate the JDN(julian day number). But in some corner cases it yields incorrent results. The root cause is: the algorithm of DateTimeUtils#ymdToUnixDate requires reasonable month(1 to 12)[1], but SqlFunctions#addMonths may pass in a month out of the reasonable range.

BTW: I didn't find the reference of the original paper of the algorithm, but an jdn explanation. Please correct me if anyone can find the original paper.

 

The following case can reproduce the bug:

addMonth('2019-09-01', 6) should yield '2020-03-01'

@Test public void testAddMonths() { 
  checkAddMonths(2019, 9, 1, 2020, 3, 1, 6); 
} 

 

[1] http://www.cs.utsa.edu/~cs1063/projects/Spring2011/Project1/jdn-explanation.html

Issue Links

• is related to: CALCITE-4476 DateTimeUtils.timeStringToUnixDate may produce wrong time
• is related to: FLINK-16823 The functioin TIMESTAMPDIFF doesn't perform expected result
• links to: GitHub Pull Request #121
• links to: GitHub Pull Request #1890