Datetime arithmetic with INTERVAL allows for values outside of permissible range #1786
Description
Description
- Performing datetime arithmetic with
INTERVALs outside of the permissible range of datetime values.
To Reproduce
One example. Date literals can't be specified with more than four digits for the year
partiql ▶ DATE '20251-01-01';
e: [syntax] Unexpected failure encountered. Caused by: org.partiql.parser.PartiQLLexerException: Expected DATE string to be of the format yyyy-MM-dd.
However, we currently allow datetime arithmetic outside of the allowable range:
partiql ▶ DATE '2025-01-01' + INTERVAL '10000' YEAR (5);
=== RESULT ===
DATE '+12025-01-01'
OK!
Expected Behavior
Per section 6.42 of the SQL2023 spec:
b) If, after the preceding step, any <primary datetime field> of the result is outside the permissible
range of values for the field or the result is invalid based on the natural rules for dates and
times, then an exception condition is raised: data exception — datetime field overflow (22008).
Valid values for datetime values:
Table 13 — Valid values for datetime fields
Keyword Valid values of datetime fields
YEAR 0001 to 9999
MONTH 01 to 12
Within the range 1 (one) to 31, but further constrained by the value of
MONTH and YEAR fields, according to the rules for the Gregorian calendar.
DAY
HOUR 00 to 23
MINUTE 00 to 59
00 to 61.9(N) where “9(N)”indicates a sequence of N instances of the digit
“9” and “N” indicates the number of digits specified by <time fractional
seconds precision>.
SECOND
Ranging from an implementation-defined (IL061) negative number not
greater than −12 to an implementation-defined (IL062) positive number
not less than +14
TIMEZONE_HOUR
TIMEZONE_MINUTE −59 to 59
If we're strictly following the SQL spec verbatim, we should expect an error here. Some other DBs like postgresql do allow for datetime literals with values outside of the SQL-defined ranges. We could opt to allow more datetime values, but it should be defined in an RFC.
Additional Context
- Java version: 17
- PartiQL version: 1.2.3-SNAPSHOT
- Add any other context about the problem here.