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

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

Attach filesAttach ScreenshotAdd voteVotersWatch issueWatchersCreate sub-taskLinkCloneUpdate Comment AuthorReplace String in CommentUpdate Comment VisibilityDelete Comments
    XMLWordPrintableJSON

Details

    • Bug
    • Status: Open
    • Major
    • Resolution: Unresolved
    • 1.1.0
    • Future
    • Functions - Hive
    • None
    • 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

          This comment will be Viewable by All Users Viewable by All Users
          Cancel

          People

            Unassigned Unassigned
            haozhu Hao Zhu

            Dates

              Created:
              Updated:

              Slack

                Issue deployment