Uploaded image for project: 'Apache Drill'
  1. Apache Drill
  2. DRILL-3727

Drill should return NULL instead of failure if cast column is empty

    XMLWordPrintableJSON

    Details

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

      1.1

      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)
      

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              haozhu Hao Zhu
            • Votes:
              2 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated: