hive-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Gaurav Kumar (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (HIVE-4905) In strict mode, predicate pushdown does not work on partition columns with statements using left/right join
Date Thu, 31 Dec 2015 07:47:49 GMT

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

Mime
View raw message