[jira] [Comment Edited] (CALCITE-4091) Extend INTERVAL literal syntax

["Julian Hyde (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-4091?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17145829#comment-17145829 ] 

Julian Hyde edited comment on CALCITE-4091 at 6/25/20, 9:00 PM:
----------------------------------------------------------------

Since {{DATE_ADD}} is a MySQL-specific function that is not supported by Calcite, can you remove it from your use cases, to keep things simple. (I got confused for a while because we do support {{DATEADD}}, which is PostgreSQL-specific and has strange syntax.)

In standard SQL, and Calcite, {{INTERVAL}} is a built-in data type and the {{INTERVAL}} keyword introduces a literal.

Calcite is, as far as I know, compliant with standard SQL.

You seem to be asking for 3 things:
* New unit WEEK (and others?).
* In "INTERVAL value unit [ TO unit ]", to allow "value" to be a numeric literal. The result would still be an interval literal.
* In "INTERVAL value unit [ TO unit ]", to allow "value" to be a numeric expression. The result would be an expression, short-hand for "value * INTERVAL '1' unit [ TO unit ]".

These changes should not be allowed in STRICT_92 or STRICT_99 conformance; only in MYSQL and perhaps other conformances TBD.

Question 1: Would the type of {{INTERVAL '1' WEEK}} be {{INTERVAL DAY}}? I really hope so. Adding new interval types would be painful.

Question 2. Would we support numeric values for begin-end intervals such as {{YEAR TO MONTH}} or {{HOUR TO MINUTE}}, or only in single unit intervals such as {{YEAR}} and {{HOUR}}?

We added extra time units to the {{EXTRACT}} function in CALCITE-2303.

In CALCITE-3383 we added plural time units (e.g. {{MINUTES}}). This is an extension to standard SQL. We should allow {{WEEKS}} etc. if and only if  {{SqlConformance.allowPluralTimeUnits()}} is true.


was (Author: julianhyde):
Since {{DATE_ADD}} is a MySQL-specific function that is not supported by Calcite, can you remove it from your use cases, to keep things simple. (I got confused for a while because we do support {{DATEADD}}, which is PostgreSQL-specific and has strange syntax.)

In standard SQL, and Calcite, {{INTERVAL}} is a built-in data type and the {{INTERVAL}} keyword introduces a literal.

Calcite is, as far as I know, compliant with standard SQL.

You seem to be asking for 3 things:
* New unit WEEK (and others?).
* In "INTERVAL value unit [ TO unit ]", to allow "value" to be a numeric literal. The result would still be an interval literal.
* In "INTERVAL value unit [ TO unit ]", to allow "value" to be a numeric expression. The result would be an expression, short-hand for "value * INTERVAL '1' unit [ TO unit ]".

Question 1: Would the type of {{INTERVAL '1' WEEK}} be {{INTERVAL DAY}}? I really hope so. Adding new interval types would be painful.

Question 2. Would we support numeric values for begin-end intervals such as {{YEAR TO MONTH}} or {{HOUR TO MINUTE}}, or only in single unit intervals such as {{YEAR}} and {{HOUR}}?

We added extra time units to the {{EXTRACT}} function in CALCITE-2303.

In CALCITE-3383 we added plural time units (e.g. {{MINUTES}}). This is an extension to standard SQL. We should allow {{WEEKS}} etc. if and only if  {{SqlConformance.allowPluralTimeUnits()}} is true.

> Extend INTERVAL literal syntax
> ------------------------------
>
>                 Key: CALCITE-4091
>                 URL: https://issues.apache.org/jira/browse/CALCITE-4091
>             Project: Calcite
>          Issue Type: Improvement
>          Components: core
>    Affects Versions: 1.23.0
>            Reporter: Dan Cojocaru
>            Priority: Major
>
> Using this parser configuration: 
> {code:java}
> final FrameworkConfig config = Frameworks.newConfigBuilder() .parserConfig(SqlParser.configBuilder()
> .setConformance(MYSQL_5).setLex(MYSQL).build())
> .programs(Programs.ofRules(RULE_SET))
> .build();
> {code}
> *Case 1.* When parsing : 
> {code:java}
> SELECT DATE_ADD('2008-01-02', INTERVAL '31' WEEK)
> {code}
> fails with :
> {code:java}
> Encountered "WEEK" at line 1, column 45.
> {code}
> *Case 2.* When parsing:
> {code:java}
> SELECT DATE_ADD('2008-01-02', INTERVAL 31 DAY)
> {code}
> fails with
> {code:java}
> Encountered "INTERVAL 31" at line 1, column 31.
> {code}
> *Case 3*. When parsing
> {code:java}
> SELECT DATE_ADD('1970-01-01 12:00:00', INTERVAL CAST(6/4 AS DECIMAL(3,1)) HOUR_MINUTE)
> {code}
> or
> {code:java}
> SELECT DATE_ADD('2008-01-02', INTERVAL ((select 1 from dual) - '31') DAY);
> {code}
> fails with:
> {code:java}
> Encountered "INTERVAL CAST".    
> {code}
> {code:java}
> Encountered "INTERVAL (
> {code}
> Conclusion: INTERVAL is not fully supported: 
>  Case 1 : WEEK time unit argument is not allowed, for other seems to work fine
>  Case 2: expressions(expr) that are nonStrings are not allowed
>  Case 3: expressions(expr) are not allowed at all, only string expr are allowed
> According to MySql documentation [https://dev.mysql.com/doc/refman/8.0/en/expressions.html#temporal-intervals]
> all the above cases should be parsed with success.



--
This message was sent by Atlassian Jira
(v8.3.4#803005)