HDP 3.1.0.0 Hive 3.1.0.3.1.0.0-78
Query via DAS-Lite 1.3.0.1.3.0.0-89
When casting a string to a date and checking to see if the result was actually legal, HIVE does not return the correct information.
DROP TABLE IF EXISTS castTest;
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;
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
The boolean actualResult should indicate if the date string was a valid date.
There is a workaround, the function nullIf(a,b) returns null if a=b evaluates the result of the CAST correctly when b=null.
... View more
