[HIVE-25861] When ConstantPropagate optimizer optimizes case when equals case when twice, got wrong logical execution plan

• Type: Bug
• Status: Resolved
• Priority: Critical
• Resolution: Duplicate
• Component/s: Logical Optimizer
• Labels: pull-request-available
• Assignee: Jun Di
• Reporter: Jun Di
• Due: 28/Feb/22
• Created: 12/Jan/22 06:32
• Updated: 14/Jan/22 14:45
• Resolved: 14/Jan/22 14:45

Description

when run the following 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';

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:

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

Issue Links

• duplicates: HIVE-12735 Constant folding for WHEN/CASE expression does not set return type correctly
• links to: GitHub Pull Request #2942
• links to: GitHub Pull Request #2942