Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-3881

SqlFunctions#addMonths yields incorrect results in some corner case

    XMLWordPrintableJSON

    Details

      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

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                danny0405 Danny Chen
                Reporter:
                docete Zhenghua Gao
              • Votes:
                0 Vote for this issue
                Watchers:
                4 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:

                  Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0h
                  0h
                  Logged:
                  Time Spent - 1h 10m
                  1h 10m