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

EXTRACT function: HOUR, MINUTE and SECOND parts of a DATE must be zero

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 1.16.0
    • Fix Version/s: 1.17.0
    • Component/s: core
    • Labels:
      None

      Description

      While working on tests for CALCITE-2303 faced with the next issue (do not fix within 2303 as the fix does not need avatica update while 2303 does)

      select extract(second from date '2008-02-23');

      returns 13
      I guess the issue is known because of the test is present in org.apache.calcite.sql.test.SqlOperatorBaseTest#testExtractDate within TODO block and with the comment

        if (TODO) {
            // Looks like there is a bug in current execution code which returns 13
            // instead of 0
            tester.checkScalar(
                "extract(second from date '2008-2-23')",
                "0",
                "BIGINT NOT NULL");
          }

      I deep dive into it and realized that the problem is that in case of DATE extract works with days + in case of seconds, minute, hours there will be used org.apache.calcite.adapter.enumerable.RexImpTable#getFactor. And finally the result is

      number_of_days % 60000 / 1000L //for extract seconds
      number_of_days % 360000 / 1000L //for extract minutes

      and yes

      select extract(minute from date '1700-01-01');

      returns -1

        Attachments

          Activity

            People

            • Assignee:
              julianhyde Julian Hyde
              Reporter:
              Sergey Nuyanzin Sergey Nuyanzin
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: