[DRILL-3431] CHAR not handled same as VARCHAR for cast to INTERVAL

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

Description

Expressions of type VARCHAR can be used in casting to an interval type. (The expression itself is not rejected, even if a value is invalid and causes an error in execution.)

However, expressions of type CHAR are rejected.

Note that since character string literals are of type CHAR, that means that character string literals cannot be cast to interval types.
 

In the following, note how the expression of type VARCHAR (the cast specification casting to VARCHAR(10)) is accepted (execution gets to the conversion's format syntax check), but how the expressions of type CHAR (the cast to CHAR(10) and the direct character string literal) are rejected:

0: jdbc:drill:zk=local> SELECT CAST( CAST( 'xxx' AS VARCHAR(10) ) AS INTERVAL HOUR ) FROM INFORMATION_SCHEMA.CATALOGS;
Error: SYSTEM ERROR: IllegalArgumentException: Invalid format: "xxx"

Fragment 0:0

[Error Id: 237092b2-d203-4126-9a6b-e036bc229cd4 on dev-linux2:31010] (state=,code=0)
0: jdbc:drill:zk=local> SELECT CAST( CAST( 'xxx' AS CHAR(10) ) AS INTERVAL HOUR ) FROM INFORMATION_SCHEMA.CATALOGS;
Jun 30, 2015 11:39:55 AM org.apache.calcite.sql.validate.SqlValidatorException <init>
SEVERE: org.apache.calcite.sql.validate.SqlValidatorException: Cast function cannot convert value of type CHAR(10) to type INTERVAL HOUR
Jun 30, 2015 11:39:55 AM org.apache.calcite.runtime.CalciteException <init>
SEVERE: org.apache.calcite.runtime.CalciteContextException: From line 1, column 8 to line 1, column 57: Cast function cannot convert value of type CHAR(10) to type INTERVAL HOUR
Error: PARSE ERROR: From line 1, column 8 to line 1, column 57: Cast function cannot convert value of type CHAR(10) to type INTERVAL HOUR


[Error Id: c50b7497-d85f-42af-bcee-512b26363a66 on dev-linux2:31010] (state=,code=0)
0: jdbc:drill:zk=local> SELECT CAST( 'xxx' AS INTERVAL HOUR ) FROM INFORMATION_SCHEMA.CATALOGS;
Jun 30, 2015 11:40:02 AM org.apache.calcite.sql.validate.SqlValidatorException <init>
SEVERE: org.apache.calcite.sql.validate.SqlValidatorException: Cast function cannot convert value of type CHAR(3) to type INTERVAL HOUR
Jun 30, 2015 11:40:02 AM org.apache.calcite.runtime.CalciteException <init>
SEVERE: org.apache.calcite.runtime.CalciteContextException: From line 1, column 8 to line 1, column 37: Cast function cannot convert value of type CHAR(3) to type INTERVAL HOUR
Error: PARSE ERROR: From line 1, column 8 to line 1, column 37: Cast function cannot convert value of type CHAR(3) to type INTERVAL HOUR


[Error Id: 16bda2bf-a907-4c1d-92e5-0344e4b8e286 on dev-linux2:31010] (state=,code=0)
0: jdbc:drill:zk=local> 

Is Drill missing an implicit cast (to convert CHAR to VARCHAR before checking types for the case specification)? Or is it that the cast specification code should accept type CHAR but does not? (Or is it something else?)

Issue Links

• supercedes: DRILL-2864 Unable to cast string literal with the valid value in ISO 8601 format to interval