EXTRACT built-in function

[alancai98]

What should be the behavior for when EXTRACT is called with

• a date keyword (year, month, day) with a time value?
• a time keyword (hour, minute, second) with a date value?
• a timezone part (timezone_hour, timezone_minute) with a datetime value without a timezone?

Should they emit some default value, MISSING (in permissive typing mode) / error (in strict), NULL?

I can't find anything in the PartiQL spec or SQL specs related to these cases.

[alancai98]

On a related topic, should PartiQL's spec define a return type for the EXTRACT builtin function? (copying a comment from partiql/partiql-tests#83)

SQL:2011 states:

The declared type of <extract expression> is an implementation-defined exact numeric type. If <primary
datetime field> specifies SECOND, then the scale is implementation-defined; otherwise, the scale is
0 (zero).

where an exact numeric type is defined as follows:

The data types NUMERIC, DECIMAL, SMALLINT, INTEGER, and BIGINT are collectively referred
to as exact numeric types.

So from SQL:2011, it appears to be implementation-defined.

---

SQL:1992 states:

<extract expression> (see Subclause 4.5.3, "Operations involving
            datetimes and intervals") operates on a datetime or interval
            argument and returns an integer.

but later states:

<extract expression> operates on a datetime or interval and returns
         an exact numeric value representing the value of one component of
         the datetime or interval.

and

If <extract expression> is specified, then

            Case:

            a) If <datetime field> does not specify SECOND, then the data
              type of the result is exact numeric with implementation-
              defined precision and scale 0.

            b) Otherwise, the data type of the result is exact numeric
              with implementation-defined precision and scale. The
              implementation-defined scale shall not be less than the spec-
              ified or implied <time fractional seconds precision> or <in-
              terval fractional seconds precision>, as appropriate, of the
              SECOND <datetime field> of the <extract source>.

---

I'm not sure if PartiQL should define the output type on its own or define it to be an implementation detail.