[DRILL-3760] Casting interval to string and back to interval fails

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Fix Version/s: Future
• Component/s: Functions - Drill
• Labels: interval
• Assignee: Unassigned
• Reporter: Daniel Barclay
• Created: 10/Sep/15 21:13
• Updated: 10/Nov/15 18:26

Description

Casting from an interval type to VARCHAR(...) and then casting back to the same interval type yields data format errors, for example:

0: jdbc:drill:drillbit=localhost> VALUES CAST( CAST( INTERVAL '1' MONTH AS VARCHAR(99) ) AS INTERVAL MONTH );
Error: SYSTEM ERROR: IllegalArgumentException: Invalid format: "0 years 1 month "


[Error Id: 339d28df-b687-47f0-b6ce-1f7732e41660 on dev-linux2:31010] (state=,code=0)
0: jdbc:drill:drillbit=localhost> 

The problem seems to be in casting from interval types to strings. The SQL standard specifies that the result string has the syntax of a SQL literal, but Drill currently uses some other syntax:

0: jdbc:drill:drillbit=localhost> VALUES CAST( INTERVAL '1' YEAR AS VARCHAR(99) );
+-------------------+
|      EXPR$0       |
+-------------------+
| 1 year 0 months   |
+-------------------+
1 row selected (0.27 seconds)
0: jdbc:drill:drillbit=localhost>