[jira] [Created] (CALCITE-5698) EXTRACT from INTERVAL partially does not follow the SQL standard

["Evgeny Stanilovsky (Jira)" <ji...@apache.org>]

Evgeny Stanilovsky created CALCITE-5698:
-------------------------------------------

             Summary: EXTRACT from INTERVAL partially does not follow the SQL standard
                 Key: CALCITE-5698
                 URL: https://issues.apache.org/jira/browse/CALCITE-5698
             Project: Calcite
          Issue Type: Bug
          Components: core
    Affects Versions: 1.34.0
            Reporter: Evgeny Stanilovsky
            Assignee: Evgeny Stanilovsky


From SQL standard we can found:
{noformat}
ISO/IEC 9075-2:1999 (E)
6.17 <numeric value function>:

If <extract field> is a <primary datetime field>, then the result is the value of the datetime
field identified by that <primary datetime field> and has the same sign as the <extract
source>.{noformat}
other data bases are follow this rule, i.e. :
{noformat}
SELECT
  EXTRACT (MONTH FROM INTERVAL '-1' MONTH)
FROM
  DUAL;{noformat}
returns: *-1*

and  **  the other one:
{noformat}
SELECT EXTRACT(MONTH FROM INTERVAL '-1 MONTHS'){noformat}
return the same result,

while calcite:
{noformat}
SELECT EXTRACT(MONTH FROM INTERVAL -1 MONTHS) AS extracted;{noformat}
{noformat}
< +---------+
< |      -1 |
< +---------+
---
> +-----------+
> |        11 |
> +-----------+{noformat}



--
This message was sent by Atlassian Jira
(v8.20.10#820010)