[
https://issues.apache.org/jira/browse/CALCITE-5817?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
hongyu guo updated CALCITE-5817:
--------------------------------
Description:
Calcite restricts the lead field of the interval type to a maximum of 2 digits
as default 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}
So we must explicitly specify the precision of the lead field.
{code:sql}
0: jdbc:calcite:model=model.json> select interval '100' day(3);
+--------+
| EXPR$0 |
+--------+
| +100 |
+--------+
1 row selected (0.016 seconds)
0: jdbc:calcite:model=model.json> select INTERVAL '100:50' HOUR(3) TO MINUTE;
+---------+
| EXPR$0 |
+---------+
| +100:50 |
+---------+
1 row selected (0.015 seconds)
{code}
In mysql, 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}
was:
Calcite restricts the lead field of the interval type to a maximum of two
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}
> 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
> Priority: Minor
>
> Calcite restricts the lead field of the interval type to a maximum of 2
> digits as default 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}
>
> So we must explicitly specify the precision of the lead field.
> {code:sql}
> 0: jdbc:calcite:model=model.json> select interval '100' day(3);
> +--------+
> | EXPR$0 |
> +--------+
> | +100 |
> +--------+
> 1 row selected (0.016 seconds)
> 0: jdbc:calcite:model=model.json> select INTERVAL '100:50' HOUR(3) TO MINUTE;
> +---------+
> | EXPR$0 |
> +---------+
> | +100:50 |
> +---------+
> 1 row selected (0.015 seconds)
> {code}
> In mysql, 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)
