[
https://issues.apache.org/jira/browse/HIVE-28880?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Soumyakanti Das reassigned HIVE-28880:
--------------------------------------
Assignee: Soumyakanti Das
> Query giving incorrect results when case is used along with UDFs upper & trim
> and a sub-query
> ---------------------------------------------------------------------------------------------
>
> Key: HIVE-28880
> URL: https://issues.apache.org/jira/browse/HIVE-28880
> Project: Hive
> Issue Type: Bug
> Components: HiveServer2
> Reporter: Taraka Rama Rao Lethavadla
> Assignee: Soumyakanti Das
> Priority: Major
> Attachments: test.q
>
>
> Steps to reproduce:
>
> {noformat}
> #Issue is not reproducible without below configs
> set hive.vectorized.execution.enabled=true;
> set hive.cbo.enable=true;
> set hive.auto.convert.join=true;
> {noformat}
> Create tables & insert few rows
> {noformat}
> CREATE EXTERNAL TABLE main_tbl(col1 string, col2 string) stored as orc;
> CREATE EXTERNAL TABLE sub_tbl(pdate date) stored as orc;
> insert into main_tbl values('20250331','BBB'),('20250331','AAAAAA');
> insert into sub_tbl values('2025-03-31');
> {noformat}
> The below query returns incorrect results
> {noformat}
> select case
> when upper(trim(col2)) = 'AAAAAA' then 'AAAA_BBBB_CCCC_DDDD'
> when upper(trim(col2)) = 'BBB' then 'WWWW_XXXX_YYYY_ZZZZ'
> else 'N/A'
> end as result
> from main_tbl
> where
> cast(concat(substr(trim(col1),1,4),'-',substr(trim(col1),5,2),'-',substr(trim(col1),7,2))
> as date) in (select pdate from sub_tbl);
> Output:
> --------------------
> WWWW_XXXX_YYYY_ZZZZ
> BBBWWWW_XXXX_YYYY_Z{noformat}
> While the expected result is
>
> {noformat}
> -------------------------
> WWWW_XXXX_YYYY_ZZZZ
> AAAA_BBBB_CCCC_DDDD
> {noformat}
> One workaround for this is rephrasing the query to
> {noformat}
> select case
> when upper(trim(col2)) = 'AAAAAA' then 'AAAA_BBBB_CCCC_DDDD'
> when upper(trim(col2)) = 'BBB' then 'WWWW_XXXX_YYYY_ZZZZ'
> else 'N/A'
> end as result
> from main_tbl
> where col1 in (select date_format(pdate, 'yyyyMMdd') from sub_tbl);{noformat}
> Attaching the qtest file here
>
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
