[DRILL-3727] Drill should return NULL instead of failure if cast column is empty

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 1.1.0
• Fix Version/s: Future
• Component/s: Functions - Hive

Environment

1.1

• Assignee: Unassigned
• Reporter: Hao Zhu
• Created: 31/Aug/15 20:50
• Updated: 17/May/19 13:31

Description

If Drill is casting an empty string to date, it will fail with error:
Error: SYSTEM ERROR: IllegalFieldValueException: Value 0 for monthOfYear must be in the range [1,12]
However Hive can just return a NULL instead.
I think it makes sense for Drill to have the same behavior as Hive in this case.

Repro:
Hive:

create table h1db.testempty(col0 string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '|'
STORED AS TEXTFILE
;

hive> select * from h1db.testempty ;
OK

2015-01-01
Time taken: 0.28 seconds, Fetched: 2 row(s)

hive> select cast(col0 as date) from  h1db.testempty;
OK
NULL
2015-01-01
Time taken: 0.078 seconds, Fetched: 2 row(s)

Drill:

use hive;
> select * from h1db.testempty ;
+-------------+
|    col0     |
+-------------+
|             |
| 2015-01-01  |
+-------------+
2 rows selected (0.232 seconds)

> select cast(col0 as date) from  h1db.testempty;
Error: SYSTEM ERROR: IllegalFieldValueException: Value 0 for monthOfYear must be in the range [1,12]

Workaround:

> select case when col0='' then null else cast(col0 as date) end from  h1db.testempty;
+-------------+
|   EXPR$0    |
+-------------+
| null        |
| 2015-01-01  |
+-------------+
2 rows selected (0.287 seconds)