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

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

VotersWatch issueWatchersLinkCloneUpdate Comment AuthorReplace String in CommentUpdate Comment VisibilityDelete Comments
    XMLWordPrintableJSON

Details

    • Bug
    • Status: Closed
    • Major
    • Resolution: Fixed
    • 1.16.0
    • 1.17.0
    • core
    • 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

        Issue Links

        Activity

          This comment will be Viewable by All Users Viewable by All Users
          Cancel

          People

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

            Dates

              Created:
              Updated:
              Resolved:

              Slack

                Issue deployment