[
https://issues.apache.org/jira/browse/HIVE-24094?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17190506#comment-17190506
]
zhaolong commented on HIVE-24094:
---------------------------------
select SEARCHWORD,cast(searchword as bigint) as castresult from testa;
when cbo is false，cast result is null if string that not a number cast bigint,
is not null if string is a number.
!image-2020-09-04-10-54-43-141.png!
when cbo is true, the result is same as cbo is false.
!image-2020-09-04-10-56-00-764.png!
!image-2020-09-04-10-56-07-286.png!
select SEARCHWORD, cast(searchword as bigint) is null as nullresult from testa;
cbo is true and false both return correct。
!image-2020-09-04-10-59-36-780.png!
select SEARCHWORD, cast(searchword as bigint) is not null as notnullresult from
testa;
when cbo is false:
!image-2020-09-04-11-02-07-917.png!
when cbo is true:
!image-2020-09-04-11-02-18-008.png!
we can see the first value is not correct, should bu false
> cast type mismatch and use is not null, the results are error if cbo is true
> ----------------------------------------------------------------------------
>
> Key: HIVE-24094
> URL: https://issues.apache.org/jira/browse/HIVE-24094
> Project: Hive
> Issue Type: Bug
> Components: CBO
> Affects Versions: 3.1.0
> Reporter: zhaolong
> Priority: Major
> Attachments: image-2020-08-31-10-01-26-250.png,
> image-2020-08-31-10-02-39-154.png, image-2020-09-04-10-54-43-141.png,
> image-2020-09-04-10-56-00-764.png, image-2020-09-04-10-56-07-286.png,
> image-2020-09-04-10-59-36-780.png, image-2020-09-04-11-02-07-917.png,
> image-2020-09-04-11-02-18-008.png
>
>
> 1.CREATE TABLE IF NOT EXISTS testa
> (
> SEARCHWORD STRING,
> COUNT_NUM BIGINT,
> WORDS STRING
> )
> ROW FORMAT DELIMITED FIELDS TERMINATED BY '\27'
> STORED AS TEXTFILE;
> 2.insert into testa values('searchword', 1, 'a');
> 3.set hive.cbo.enable=false;
> 4.SELECT
> CASE
> WHEN CAST(searchword as bigint) IS NOT NULL THEN CAST(CAST(searchword as
> bigint) as String)
> ELSE searchword
> END AS WORDS,
> searchword FROM testa;
> !image-2020-08-31-10-01-26-250.png!
> 5.set hive.cbo.enable=true;
> 6.SELECT
> CASE
> WHEN CAST(searchword as bigint) IS NOT NULL THEN CAST(CAST(searchword as
> bigint) as String)
> ELSE searchword
> END AS WORDS,
> searchword FROM testa;
> !image-2020-08-31-10-02-39-154.png!
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
