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

TIMESTAMPDIFF function returns incorrect result

    XMLWordPrintableJSON

Details

    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

       

      Attachments

        Issue Links

          Activity

            People

              mbudiu Mihai Budiu
              mbudiu Mihai Budiu
              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 - 10m
                  10m