Casting invalid dates

Casting of an invalid date differs from Hive 1 in CDH 5 to Hive 3 in CDP. Hive 3 uses a different parser formatter from the one used in Hive 1, which affects semantics. Hive 1 considers 00 invalid for date fields. Hive 3 considers 00 valid for date fields. Neither Hive 1 nor Hive 3 correctly handles invalid dates, and Hive-25056 addresses this issue.

Before Upgrade to CDP

Casting of invalid date (zero value in one or more of the 3 fields of date, month, year) returns a NULL value:

> SELECT CAST ('0000-00-00' as date) , CAST ('000-00-00 00:00:00' AS TIMESTAMP) ;
...
------------
_c0_c1
------------
NULLNULL
------------
1 row selected (0.154 seconds)

After Upgrade to CDP

Casting of an invalid date returns a result.

> SELECT CAST ('0000-00-00' as date) , CAST ('000-00-00 00:00:00' AS TIMESTAMP) ;
...
-----------------------------------+
_c0_c1
-----------------------------------+
00002-11-30 00:00:00.0
-----------------------------------+
1 row selected (5.291 seconds)

Action Required

Do not cast invalid dates in Hive 3.