[
https://issues.apache.org/jira/browse/CALCITE-5817?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17741497#comment-17741497
]
hongyu guo commented on CALCITE-5817:
-------------------------------------
I change interval type's lead field default to maximum allowed precision in
[SqlIntervalQualifier#checkLeadFieldInRange|https://github.com/apache/calcite/blob/main/core/src/main/java/org/apache/calcite/sql/SqlIntervalQualifier.java#L472]
, This change will not affect other functions and will *only* take effect
during validation
> 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
> Labels: pull-request-available
>
> 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)
