Eric Richardson created HIVE-22285:
--------------------------------------
Summary: CAST function does not immediately return null if
conversion does not succeed
Key: HIVE-22285
URL: https://issues.apache.org/jira/browse/HIVE-22285
Project: Hive
Issue Type: Bug
Components: Hive
Affects Versions: 3.1.0
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
Reporter: Eric Richardson
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:
{code:sql}
cast('something that is not a date' as date){code}
will return null
{code:sql}
cast('something that is not a date' as date) is NOT null{code}
will return true (which is wrong)
{code:sql}
cast(null as date) is NOT null
{code}
will return false (which is correct)
full example to demonstrate problem
{code:sql}
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;
{code}
returns
{code:sql}
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
{code}
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.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
