Details
-
Bug
-
Status: Open
-
Major
-
Resolution: Unresolved
-
3.1.0
-
None
-
None
-
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.