[HIVE-13803] More aggressive inference of transitive predicates for inner joins

• Type: Sub-task
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 2.1.0
• Fix Version/s: 2.1.1, 2.2.0
• Component/s: CBO
• Assignee: Jesús Camacho Rodríguez
• Reporter: Jesús Camacho Rodríguez
• Created: 20/May/16 14:58
• Updated: 27/Feb/24 22:24
• Resolved: 29/Jun/16 21:57

Description

Follow-up of HIVE-13068.

Currently for inner joins, we do not infer transitive predicates that do not reference any of the columns of the input. These predicates can be evaluated statically and can be useful to quickly discard intermediary results.

Ex. ql/src/test/results/clientpositive/constprog3.q.out

explain
select table1.id, table1.val, table1.val1
from table1 inner join table3
on table1.dimid = table3.id and table3.id = 1 where table1.dimid <> 1

Current plan:

STAGE DEPENDENCIES:
  Stage-1 is a root stage
  Stage-0 depends on stages: Stage-1

STAGE PLANS:
  Stage: Stage-1
    Map Reduce
      Map Operator Tree:
          TableScan
            alias: table1
            Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
            Filter Operator
              predicate: false (type: boolean)
              Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
              Select Operator
                expressions: id (type: int), val (type: int), val1 (type: int)
                outputColumnNames: _col0, _col1, _col2
                Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
                Reduce Output Operator
                  sort order: 
                  Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
                  value expressions: _col0 (type: int), _col1 (type: int), _col2 (type: int)
          TableScan
            alias: table3
            Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
            Filter Operator
              predicate: (id = 1) (type: boolean)
              Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
              Select Operator
                Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
                Reduce Output Operator
                  sort order: 
                  Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
      Reduce Operator Tree:
        Join Operator
          condition map:
               Inner Join 0 to 1
          keys:
            0 
            1 
          outputColumnNames: _col0, _col1, _col2
          Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
          File Output Operator
            compressed: false
            Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
            table:
                input format: org.apache.hadoop.mapred.SequenceFileInputFormat
                output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
                serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe

  Stage: Stage-0
    Fetch Operator
      limit: -1
      Processor Tree:
        ListSink

Issue Links

• is related to: HIVE-25953 Drop HiveRelMdPredicates::getPredicates(Join...) to use that of RelMdPredicates
• relates to: HIVE-13068 Disable Hive ConstantPropagate optimizer when CBO has optimized the plan II
• links to: RB link