Adriano created HIVE-14704:
------------------------------
Summary: make partition pruning with outer joins possible
Key: HIVE-14704
URL: https://issues.apache.org/jira/browse/HIVE-14704
Project: Hive
Issue Type: New Feature
Components: Query Planning
Affects Versions: 1.1.0
Reporter: Adriano
Indeed by SQL semantics, the predicate specified in the ON clause should be
applied on the Join. However, the predicate in the WHERE clause is applied
after the Join is made. Thus, the condition in the WHERE are not pushed into
the Join for outer joins.
set hive.mapred.mode=strict;
create table table1 (s1 string , s2 string) partitioned by (dt string, time
string);
alter table table1 add partition (dt='1',time='2');
create table table2 (s1 string , s2 string) partitioned by (dt string, time
string);
alter table table2 add partition (dt='1',time='2');
right outer join: failed
0: jdbc:hive2://host-10-17-80-30.coe.cloudera> explain select a.* from table1 a
right outer join table2 b on (a.s1 = b.s1) where a.dt='1' and a.time='2' and
b.dt='1';
Error: Error while compiling statement: FAILED: SemanticException [Error
10041]: No partition predicate found for Alias "TS" Table "table1"
(state=42000,code=10041)
left outer join: failed
0: jdbc:hive2://host-10-17-80-30.coe.cloudera> explain select a.* from table1 a
left outer join table2 b on (a.s1 = b.s1) where a.dt='1' and a.time='2' and
b.dt='1';
Error: Error while compiling statement: FAILED: SemanticException [Error
10041]: No partition predicate found for Alias "TS" Table "table2"
(state=42000,code=10041)
This doesn't happen if the scrict mode is disabled
set hive.mapred.mode=nonstrict;
or if we move the where clause of the right table to the left join as below:
explain select a.* from table1 a left outer join table2 b on (a.s1 = b.s1) and
b.dt='1' where a.dt='1' and a.time='2';
or if the where clause of the left table to the right join as below:
explain select a.* from table1 a right outer join table2 b on (a.s1 = b.s1) and
a.dt='1' and a.time='2' where b.dt='1'
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
