[jira] [Created] (CALCITE-5817) The lead field for the interval type should not be validated

["hongyu guo (Jira)" <ji...@apache.org>]

hongyu guo created CALCITE-5817:
-----------------------------------

             Summary: The lead field for the interval type should not be validated
                 Key: CALCITE-5817
                 URL: https://issues.apache.org/jira/browse/CALCITE-5817
             Project: Calcite
          Issue Type: Bug
          Components: core
    Affects Versions: 1.34.0
            Reporter: hongyu guo
            Assignee: hongyu guo


Calcite restricts the lead field of the interval type to a maximum of three digits through the [SqlIntervalQualifier#checkLeadFieldInRange|https://github.com/apache/calcite/blob/main/core/src/main/java/org/apache/calcite/sql/SqlIntervalQualifier.java#L472].

There are some example
{code:sql}
0: jdbc:calcite:model=model.json> select interval '100' day;
Error: Error while executing SQL "select interval '100' day": From line 1, column 8 to line 1, column 25: Interval field value 100 exceeds precision of DAY(2) field (state=,code=0)

0: jdbc:calcite:model=model.json> select INTERVAL '100:50' HOUR TO MINUTE;
Error: Error while executing SQL "select INTERVAL '100:50' HOUR TO MINUTE": From line 1, column 8 to line 1, column 39: Interval field value 100 exceeds precision of HOUR(2) field (state=,code=0)
{code}
In postgres and spark-sql, this restriction does not exist.
{code:sql}
-- postgres
postgres=# select INTERVAL '100' second;
 interval 
----------
 00:01:40
(1 row)

postgres=# select INTERVAL '100:5' HOUR TO MINUTE;
 interval  
-----------
 100:05:00
(1 row)

-- mysql (In mysql, directly using the INTERVAL keyword with a string value cannot be executed)
mysql> select current_date +  INTERVAL '100' HOUR;
+-------------------------------------+
| current_date +  INTERVAL '100' HOUR |
+-------------------------------------+
| 2023-07-08 04:00:00                 |
+-------------------------------------+
1 row in set (0.00 sec)

-- spark-sql
spark-sql> select INTERVAL '100:5' HOUR TO MINUTE;
INTERVAL '100:05' HOUR TO MINUTE
4 04:05:00.000000000
Time taken: 0.041 seconds, Fetched 1 row(s)
{code}



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