Before Hive 0.8.0, the query
set hive.mapred.mode=strict; create table invites (foo int, bar string) partitioned by (ds string); create table invites2 (foo int, bar string) partitioned by (ds string); select count(*) from invites join invites2 on invites.ds=invites2.ds where invites.ds='2011-01-01';
would give the error
Error in semantic analysis: No Partition Predicate Found for Alias "invites2" Table "invites2"
Here, the filter is applied to the table invites as invites.ds='2011-01-01' but not invites2.ds='2011-01-01'. This causes Hive to reject the query in strict mode to prevent scanning all the partitions of invites2. This can be seen by using explain plan on the query without strict mode on:
STAGE DEPENDENCIES: Stage-1 is a root stage Stage-2 depends on stages: Stage-1 Stage-0 is a root stage STAGE PLANS: Stage: Stage-1 Map Reduce Alias -> Map Operator Tree: invites TableScan alias: invites Filter Operator predicate: expr: (ds = '2011-01-01') type: boolean Reduce Output Operator key expressions: expr: ds type: string sort order: + Map-reduce partition columns: expr: ds type: string tag: 0 value expressions: expr: ds type: string invites2 TableScan alias: invites2 Reduce Output Operator key expressions: expr: ds type: string sort order: + Map-reduce partition columns: expr: ds type: string tag: 1 Reduce Operator Tree: Join Operator condition map: Inner Join 0 to 1 condition expressions: 0 {VALUE._col2} 1 handleSkewJoin: false outputColumnNames: _col2 Select Operator Group By Operator aggregations: expr: count() bucketGroup: false mode: hash outputColumnNames: _col0 File Output Operator compressed: false GlobalTableId: 0 table: input format: org.apache.hadoop.mapred.SequenceFileInputFormat output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat Stage: Stage-2 Map Reduce Alias -> Map Operator Tree: file:/var/folders/nt/ng21tg0n1jl4547lw0k8lg6hq_nw87/T/charleschen/hive_2011-08-04_10-59-05_697_8934329734633337337/-mr-10002 Reduce Output Operator sort order: tag: -1 value expressions: expr: _col0 type: bigint Reduce Operator Tree: Group By Operator aggregations: expr: count(VALUE._col0) bucketGroup: false mode: mergepartial outputColumnNames: _col0 Select Operator expressions: expr: _col0 type: bigint outputColumnNames: _col0 File Output Operator compressed: false GlobalTableId: 0 table: input format: org.apache.hadoop.mapred.TextInputFormat output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat Stage: Stage-0 Fetch Operator limit: -1
Note that there is no filter on the tablescan operation for invites2.
In Hive 0.8.0, support will be added for recognizing transitivity on join conditions during predicate pushdown with [HIVE-1989|https://issues.apache.org/jira/browse/HIVE-1989]. With the above example, Hive will now infer the filter invites2.ds='2011-01-01' from the filter invites.ds='2011-01-01' and the join condition invites.ds=invites2.ds. The explain plan now gives:
STAGE DEPENDENCIES: Stage-1 is a root stage Stage-2 depends on stages: Stage-1 Stage-0 is a root stage STAGE PLANS: Stage: Stage-1 Map Reduce Alias -> Map Operator Tree: invites TableScan alias: invites Filter Operator predicate: expr: (ds = '2011-01-01') type: boolean Reduce Output Operator key expressions: expr: ds type: string sort order: + Map-reduce partition columns: expr: ds type: string tag: 0 value expressions: expr: ds type: string invites2 TableScan alias: invites2 Filter Operator predicate: expr: (ds = '2011-01-01') type: boolean Reduce Output Operator key expressions: expr: ds type: string sort order: + Map-reduce partition columns: expr: ds type: string tag: 1 Reduce Operator Tree: Join Operator condition map: Inner Join 0 to 1 condition expressions: 0 {VALUE._col2} 1 handleSkewJoin: false outputColumnNames: _col2 Select Operator Group By Operator aggregations: expr: count() bucketGroup: false mode: hash outputColumnNames: _col0 File Output Operator compressed: false GlobalTableId: 0 table: input format: org.apache.hadoop.mapred.SequenceFileInputFormat output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat Stage: Stage-2 Map Reduce Alias -> Map Operator Tree: file:/var/folders/nt/ng21tg0n1jl4547lw0k8lg6hq_nw87/T/charleschen/hive_2011-08-04_10-56-09_896_8195257719501884918/-mr-10002 Reduce Output Operator sort order: tag: -1 value expressions: expr: _col0 type: bigint Reduce Operator Tree: Group By Operator aggregations: expr: count(VALUE._col0) bucketGroup: false mode: mergepartial outputColumnNames: _col0 Select Operator expressions: expr: _col0 type: bigint outputColumnNames: _col0 File Output Operator compressed: false GlobalTableId: 0 table: input format: org.apache.hadoop.mapred.TextInputFormat output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat Stage: Stage-0 Fetch Operator limit: -1