[
https://issues.apache.org/jira/browse/HIVE-22285?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16943119#comment-16943119
]
Eric Richardson commented on HIVE-22285:
----------------------------------------
Same behavior with casting from string to int.
{code:sql}
CREATE TABLE IF NOT EXISTS castTestInt (rowId BIGINT, intString STRING,
expectedResult BOOLEAN) STORED AS ORC;
TRUNCATE TABLE castTestInt;
INSERT INTO TABLE castTestInt VALUES(0,'2019',true);
INSERT INTO TABLE castTestInt VALUES(1,null,false);
INSERT INTO TABLE castTestInt VALUES(2,' ',false);
INSERT INTO TABLE castTestInt VALUES(3,'',false);
INSERT INTO TABLE castTestInt VALUES(4,'not an int',false);
SELECT
rowId,
intString,
CAST(intString AS INT) as intInt,
CAST(intString AS INT) IS NOT NULL AS actualResult,
expectedResult,
nullIf(CAST(intString AS INT),null) IS NOT NULL AS workAroundResult
FROM castTestInt;
DROP TABLE IF EXISTS castTestInt;
{code}
{code:sql}
+--------+-------------+---------+---------------+-----------------+-------------------+
| rowid | intstring | intint | actualresult | expectedresult |
workaroundresult |
+--------+-------------+---------+---------------+-----------------+-------------------+
| 0 | 2019 | 2019 | true | true | true
|
| 1 | NULL | NULL | false | false | false
|
| 2 | | NULL | true | false | false
|
| 3 | | NULL | true | false | false
|
| 4 | not an int | NULL | true | false | false
|
+--------+-------------+---------+---------------+-----------------+-------------------+
5 rows selected (1.768 seconds)
{code}
> 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
> Priority: Major
>
> 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)
