Uploaded image for project: 'Hive'
  1. Hive
  2. HIVE-19433

HiveJoinPushTransitivePredicatesRule hangs

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 3.0.0, 3.1.0
    • Component/s: Logical Optimizer
    • Labels:
      None
    • Target Version/s:

      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 );
      

        Attachments

        1. HIVE-19433.2.patch
          39 kB
          Vineet Garg
        2. HIVE-19433.1.patch
          39 kB
          Vineet Garg

          Issue Links

            Activity

              People

              • Assignee:
                vgarg Vineet Garg
                Reporter:
                vgarg Vineet Garg
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: