[HIVE-19433] HiveJoinPushTransitivePredicatesRule hangs

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 3.1.0, 3.0.0
• Component/s: Logical Optimizer
• Target Version/s: 3.0.0
• Assignee: Vineet Garg
• Reporter: Vineet Garg
• Created: 07/May/18 00:03
• Updated: 27/Feb/24 22:23
• Resolved: 14/May/18 18:40

Description

Reproducer

    CREATE TABLE `table1`(
       `idp_warehouse_id` bigint,
       `idp_audit_id` bigint,
       `idp_effective_date` date,
       `idp_end_date` date,
       `idp_delete_date` date,
       `pruid` varchar(32),
       `prid` bigint,
       `prtimesheetid` bigint,
       `prassignmentid` bigint,
       `prchargecodeid` bigint,
       `prtypecodeid` bigint,
       `prsequence` bigint,
       `prmodby` varchar(96),
       `prmodtime` timestamp,
       `prrmexported` bigint,
       `prrmckdel` bigint,
       `slice_status` int,
       `role_id` bigint,
       `user_lov1` varchar(30),
       `user_lov2` varchar(30),
       `incident_id` bigint,
       `incident_investment_id` bigint,
       `odf_ss_actuals` bigint,
       `practsum` decimal(38,20));

    CREATE TABLE `table2`(
       `idp_warehouse_id` bigint,
       `idp_audit_id` bigint,
       `idp_effective_date` date,
       `idp_end_date` date,
       `idp_delete_date` date,
       `pruid` varchar(32),
       `prid` bigint,
       `prtimesheetid` bigint,
       `prassignmentid` bigint,
       `prchargecodeid` bigint,
       `prtypecodeid` bigint,
       `prsequence` bigint,
       `prmodby` varchar(96),
       `prmodtime` timestamp,
       `prrmexported` bigint,
       `prrmckdel` bigint,
       `slice_status` int,
       `role_id` bigint,
       `user_lov1` varchar(30),
       `user_lov2` varchar(30),
       `incident_id` bigint,
       `incident_investment_id` bigint,
       `odf_ss_actuals` bigint,
       `practsum` decimal(38,20));

    explain SELECT          s.idp_warehouse_id AS source_warehouse_id
    FROM            table1 s
    JOIN

                           table2 d
    ON              (
                                    s.prid = d.prid )
    JOIN
                             table2 e
    ON
                                    s.prid = e.prid
    WHERE
    concat(
                    CASE
                                    WHEN s.prid IS NULL THEN 1
                                    ELSE s.prid
                    END,',',
                    CASE
                                    WHEN s.prtimesheetid IS NULL THEN 1
                                    ELSE s.prtimesheetid
                    END,',',
                    CASE
                                    WHEN s.prassignmentid IS NULL THEN 1
                                    ELSE s.prassignmentid
                    END,',',
                    CASE
                                    WHEN s.prchargecodeid IS NULL THEN 1
                                    ELSE s.prchargecodeid
                    END,',',
                    CASE
                                    WHEN (s.prtypecodeid) IS NULL THEN ''
                                    ELSE s.prtypecodeid
                    END,',',
                    CASE
                                    WHEN s.practsum IS NULL THEN 1
                                    ELSE s.practsum
                    END,',',
                    CASE
                                    WHEN s.prsequence IS NULL THEN 1
                                    ELSE s.prsequence
                    END,',',
                    CASE
                                    WHEN length(s.prmodby) IS NULL THEN ''
                                    ELSE s.prmodby
                    END,',',
                    CASE
                                    WHEN s.prmodtime IS NULL THEN cast(from_unixtime(unix_timestamp('2017-12-08','yyyy-MM-dd') ) AS timestamp)
                                    ELSE s.prmodtime
                    END,',',
                    CASE
                                    WHEN s.prrmexported IS NULL THEN 1
                                    ELSE s.prrmexported
                    END,',',
                    CASE
                                    WHEN s.prrmckdel IS NULL THEN 1
                                    ELSE s.prrmckdel
                    END,',',
                    CASE
                                    WHEN s.slice_status IS NULL THEN 1
                                    ELSE s.slice_status
                    END,',',
                    CASE
                                    WHEN s.role_id IS NULL THEN 1
                                    ELSE s.role_id
                    END,',',
                    CASE
                                    WHEN length(s.user_lov1) IS NULL THEN ''
                                    ELSE s.user_lov1
                    END,',',
                    CASE
                                    WHEN length(s.user_lov2) IS NULL THEN ''
                                    ELSE s.user_lov2
                    END,',',
                    CASE
                                    WHEN s.incident_id IS NULL THEN 1
                                    ELSE s.incident_id
                    END,',',
                    CASE
                                    WHEN s.incident_investment_id IS NULL THEN 1
                                    ELSE s.incident_investment_id
                    END,',',
                    CASE
                                    WHEN s.odf_ss_actuals IS NULL THEN 1
                                    ELSE s.odf_ss_actuals
                    END ) != concat(
                    CASE
                                    WHEN length(d.pruid) IS NULL THEN ''
                                    ELSE d.pruid
                    END,',',
                    CASE
                                    WHEN d.prid IS NULL THEN 1
                                    ELSE d.prid
                    END,',',
                    CASE
                                    WHEN d.prtimesheetid IS NULL THEN 1
                                    ELSE d.prtimesheetid
                    END,',',
                    CASE
                                    WHEN d.prassignmentid IS NULL THEN 1
                                    ELSE d.prassignmentid
                    END,',',
                    CASE
                                    WHEN d.prchargecodeid IS NULL THEN 1
                                    ELSE d.prchargecodeid
                    END,',',
                    CASE
                                    WHEN (d.prtypecodeid) IS NULL THEN ''
                                    ELSE d.prtypecodeid
                    END,',',
                    CASE
                                    WHEN d.practsum IS NULL THEN 1
                                    ELSE d.practsum
                    END,',',
                    CASE
                                    WHEN d.prsequence IS NULL THEN 1
                                    ELSE d.prsequence
                    END,',',
                    CASE
                                    WHEN length(d.prmodby) IS NULL THEN ''
                                    ELSE d.prmodby
                    END,',',
                    CASE
                                    WHEN d.prmodtime IS NULL THEN cast(from_unixtime(unix_timestamp('2017-12-08','yyyy-MM-dd') ) AS timestamp)
                                    ELSE d.prmodtime
                    END,',',
                    CASE
                                    WHEN d.prrmexported IS NULL THEN 1
                                    ELSE d.prrmexported
                    END,',',
                    CASE
                                    WHEN d.prrmckdel IS NULL THEN 1
                                    ELSE d.prrmckdel
                    END,',',
                    CASE
                                    WHEN d.slice_status IS NULL THEN 1
                                    ELSE d.slice_status
                    END,',',
                    CASE
                                    WHEN d.role_id IS NULL THEN 1
                                    ELSE d.role_id
                    END,',',
                    CASE
                                    WHEN length(d.user_lov1) IS NULL THEN ''
                                    ELSE d.user_lov1
                    END,',',
                    CASE
                                    WHEN length(d.user_lov2) IS NULL THEN ''
                                    ELSE d.user_lov2
                    END,',',
                    CASE
                                    WHEN d.incident_id IS NULL THEN 1
                                    ELSE d.incident_id
                    END,',',
                    CASE
                                    WHEN d.incident_investment_id IS NULL THEN 1
                                    ELSE d.incident_investment_id
                    END,',',
                    CASE
                                    WHEN d.odf_ss_actuals IS NULL THEN 1
                                    ELSE d.odf_ss_actuals
                    END );

Issue Links

• is related to: HIVE-19101 Apply rule [HiveJoinPushTransitivePredicatesRule] is getting stuck when there are huge number of predicates