You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@hive.apache.org by "Eric Richardson (Jira)" <ji...@apache.org> on 2019/10/02 19:58:00 UTC
[jira] [Commented] (HIVE-22285) CAST function does not immediately
return null if conversion does not succeed
[ 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)