Jun Di created HIVE-25861:
-----------------------------
Summary: When ConstantPropagate optimizer optimizes case when
equals case when twice, got wrong logical execution plan
Key: HIVE-25861
URL: https://issues.apache.org/jira/browse/HIVE-25861
Project: Hive
Issue Type: Bug
Components: Logical Optimizer
Reporter: Jun Di
Assignee: Jun Di
Attachments: 1.png
when run the following sql:
{code:sql}
select
t1.column_1,
t2.column_1,
t1.column_2,
t1.column_3,
case
when (
case
when t1.column_1 in (310000, 320000, 330000, 340000)
then 310000
else t1.column_1
end
) = (
case
when t2.column_1 in (310000, 320000, 330000, 340000)
then 310000
else t2.column_1
end
)
then t1.column_2
else t1.column_3
end as result
from
dim.dim_xmf_center t1
left join dim.dim_xmf_center t2
where
t1.mt = '202201';
{code}
t1.column_1 is 440000 and t2.column_1 is 440000 but the result is t1.column_3
Please see picture 1.png in the attachment for the result
I found that the case when part of the execution plan is wrong:
{code:sql}
CASE WHEN (CASE WHEN ((_col20) IN (310000, 320000, 330000, 340000)) THEN (CASE
WHEN ((_col46) IN (310000, 320000, 330000, 340000)) THEN ((true = _col20)) ELSE
(((_col46 = 310000) = _col20)) END) ELSE (CASE WHEN ((_col46) IN (310000,
320000, 330000, 340000)) THEN ((true = _col20)) ELSE (((_col46 = 310000) =
_col20)) END) END) THEN (_col12) ELSE (_col15) END
{code}
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
