[
https://issues.apache.org/jira/browse/HIVE-4905?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15075812#comment-15075812
]
Gaurav Kumar commented on HIVE-4905:
------------------------------------
*For left/right outer joins:*
{code:sql}
select * from tbl1 left outer join tbl2
on tbl1.col1 = tbl2.col2 and tbl2.month=20 and tbl2.day=20
where tbl1.month=20 and tbl1.day=20
{code}
The key is to put the partitions of tbl2 in the ‘on’ condition and the
partitions of tbl1 in the ‘where’ condition.
*For full outer joins:*
The only way is to wrap both the tables in a subquery.
{code:sql}
select * from
(select * from tbl1 where month=20 and day=20) tbl1_x
full outer join
(select * from tbl2 where month=20 and day=20) tbl2_x
on tbl1_x.col1 = tbl2_x.col2
{code}
> In strict mode, predicate pushdown does not work on partition columns with
> statements using left/right join
> ------------------------------------------------------------------------------------------------------------
>
> Key: HIVE-4905
> URL: https://issues.apache.org/jira/browse/HIVE-4905
> Project: Hive
> Issue Type: Bug
> Components: Query Processor
> Affects Versions: 0.12.0
> Reporter: caofangkun
> Assignee: caofangkun
> Priority: Minor
>
> set hive.mapred.mode=strict;
> drop table mpt3;
> create table mpt3 (s1 string , s2 string) partitioned by (dt string, time
> string);
> alter table mpt3 add partition (dt='1',time='2');
> drop table mpt4;
> create table mpt4 (s1 string , s2 string) partitioned by (dt string, time
> string);
> alter table mpt4 add partition (dt='1',time='2');
> Query One: works well
> explain
> select * from mpt3 a join mpt4 b on (a.s1 = b.s1) where a.dt='1' and
> a.time='2' and b.dt='1';
> Query Two :failed
> hive (default)> explain select a.* from mpt3 a right outer join mpt4 b on
> (a.s1 = b.s1) where a.dt='1' and a.time='2' and b.dt='1';
> FAILED: SemanticException [Error 10041]: No partition predicate found for
> Alias "a" Table "mpt3"
> Query Three: failed
> hive (default)> explain select a.* from mpt3 a left outer join mpt4 b on
> (a.s1 = b.s1) where a.dt='1' and a.time='2' and b.dt='1';
> FAILED: SemanticException [Error 10041]: No partition predicate found for
> Alias "b" Table "mpt4"
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
