[CALCITE-4091] Extend INTERVAL literal syntax

• Type: Improvement
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 1.23.0
• Component/s: core
• Assignee: Julian Hyde
• Reporter: Dan Cojocaru
• Created: 25/Jun/20 09:23
• Updated: 20/Jul/20 21:26

Description

Using this parser configuration: 

final FrameworkConfig config = Frameworks.newConfigBuilder() .parserConfig(SqlParser.configBuilder()
.setConformance(MYSQL_5).setLex(MYSQL).build())
.programs(Programs.ofRules(RULE_SET))
.build();

Case 1. When parsing : 

SELECT DATE_ADD('2008-01-02', INTERVAL '31' WEEK)

fails with :

Encountered "WEEK" at line 1, column 45.

Case 2. When parsing:

SELECT DATE_ADD('2008-01-02', INTERVAL 31 DAY)

fails with

Encountered "INTERVAL 31" at line 1, column 31.

Case 3. When parsing

SELECT DATE_ADD('1970-01-01 12:00:00', INTERVAL CAST(6/4 AS DECIMAL(3,1)) HOUR_MINUTE)

or

SELECT DATE_ADD('2008-01-02', INTERVAL ((select 1 from dual) - '31') DAY);

fails with:

Encountered "INTERVAL CAST".    

Encountered "INTERVAL (

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.

Sub-Tasks

• Interval expressions: Closed