[CALCITE-2324] EXTRACT function: HOUR, MINUTE and SECOND parts of a DATE must be zero

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.16.0
• Fix Version/s: 1.17.0
• Component/s: core
• Assignee: Julian Hyde
• Reporter: Sergey Nuyanzin
• Created: 24/May/18 01:04
• Updated: 06/Feb/19 19:30
• Resolved: 12/Jun/18 20:39

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

Issue Links

• is duplicated by: CALCITE-2831 Fix EXTRACT(SECOND FROM <DATE>)