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

[Hao Zhu (JIRA)]

Hao Zhu created DRILL-3727:
------------------------------

             Summary: Drill should return NULL instead of failure if cast 
column is empty
                 Key: DRILL-3727
                 URL: https://issues.apache.org/jira/browse/DRILL-3727
             Project: Apache Drill
          Issue Type: Bug
          Components: Functions - Hive
    Affects Versions: 1.1.0
         Environment: 1.1
            Reporter: Hao Zhu
            Assignee: Mehant Baid


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:
{code}
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)
{code}

Drill:
{code}
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]
{code}

Workaround:
{code}
> 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)
{code}



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)