Uploaded image for project: 'Hive'
  1. Hive
  2. HIVE-22285

CAST function does not immediately return null if conversion does not succeed

    XMLWordPrintableJSON

    Details

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

      HDP 3.1.0
      Hive 3.1.0.3.1.0.0-78
      Query via DAS 1.3.0 or beeline 3.1.0.3.1.0.0-78

      Description

      When cast is called on a column (or derived column) and the cast does not succeed it is supposed to return null.  Which is what it returns at the end of the query, but some evaluations within the query do not evaluate to null, unless the predicate of the cast is null.

      ex:

      cast('something that is not a date' as date)

      will return null

       

      cast('something that is not a date' as date) is NOT null

      will return true (which is wrong)

       

      cast(null as date) is NOT null
      

      will return false (which is correct)

       

      full example to demonstrate problem

      CREATE TABLE IF NOT EXISTS castTest (rowId BIGINT, dateString STRING, expectedResult BOOLEAN) STORED AS ORC;
      
      TRUNCATE TABLE castTest;
      
      INSERT INTO TABLE castTest VALUES(0,'2019-10-02',true);
      INSERT INTO TABLE castTest VALUES(1,null,false);
      INSERT INTO TABLE castTest VALUES(2,'          ',false);
      INSERT INTO TABLE castTest VALUES(3,'',false);
      INSERT INTO TABLE castTest VALUES(4,'not a date',false);
      
      SELECT
        rowId,
        dateString,
        CAST(dateString AS DATE) as dateDate,
        CAST(dateString AS DATE) IS NOT NULL AS actualResult,
        expectedResult,
        nullIf(CAST(dateString AS DATE),null) IS NOT NULL AS workAroundResult
      FROM castTest;
      
      DROP TABLE IF EXISTS castTest;
      
      

       returns

      ROWID  DATESTRING  DATEDATE    ACTUALRESULT  EXPECTEDRESULT  WORKAROUNDRESULT
      0      2019-10-02  2019-10-02  true          true            true
      1      null        null        false         false           false
      2                  null        true          false           false
      3      ""          null        true          false           false
      4      not a date  null        true          false           false
      

      Expected result is true if the date string was a valid convertible date string, false otherwise.

       Query (incorrectly) returns true for actual result when dateString is empty, whitespace, or characters.

      Query only returns correct result when dateString is convertible or null.

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              Eric Richardson Eric Richardson
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated: