[ https://issues.apache.org/jira/browse/HIVE-5321?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Alexander Pivovarov updated HIVE-5321: -------------------------------------- Description: SELECT * FROM T1 LEFT OUTER JOIN T2 ON (T1.c1=T2.c2 AND T1.c1 < 10) and SELECT * FROM T1 RIGHT OUTER JOIN T2 ON (T1.c1=T2.c2 AND T2.c1 < 10) do not give correct results. to reproduce: hive> create table tt1 (c1 int); hive> create table tt2 (c1 int); $ vi tt1 1 2 3 4 $ vi tt2 1 2 8 9 $ hadoop fs -put tt1 /user/hive/warehouse/tt1/ $ hadoop fs -put tt2 /user/hive/warehouse/tt2/ wrong result: hive> select * from tt1 left outer join tt2 on (tt1.c1 = tt2.c1 and tt1.c1 <= 2); 1 1 2 2 3 NULL 4 NULL correct result: select * from tt1 left outer join tt2 on (tt1.c1 = tt2.c1) where tt1.c1 <= 2; 1 1 2 2 hive-0.11.0-bin$ head -1 RELEASE_NOTES.txt Release Notes - Hive - Version 0.11.0 was: SELECT * FROM T1 LEFT OUTER JOIN T2 ON (T1.c1=T2.c2 AND T1.c1 < 10) and SELECT * FROM T1 RIGHT OUTER JOIN T2 ON (T1.c1=T2.c2 AND T2.c1 < 10) do not give correct results. to reproduce: hive> create table tt1 (c1 int); hive> create table tt2 (c1 int); $ vi tt1 1 2 3 4 $ vi tt2 1 2 8 9 $ hadoop fs -put tt1 /user/hive/warehouse/tt1/ $ hadoop fs -put tt2 /user/hive/warehouse/tt2/ wrong result: hive> select * from tt1 left outer join tt2 on (tt1.c1 = tt2.c1 and tt1.c1 <= 2); 1 1 2 2 3 NULL 4 NULL correct result: select * from tt1 left outer join tt2 on (tt1.c1 = tt2.c1) where tt1.c1 <= 2; 1 1 2 2 alexp@t1:~/hive-0.11.0-bin$ head -1 RELEASE_NOTES.txt Release Notes - Hive - Version 0.11.0 > Join filters do not work correctly with outer joins again > --------------------------------------------------------- > > Key: HIVE-5321 > URL: https://issues.apache.org/jira/browse/HIVE-5321 > Project: Hive > Issue Type: Bug > Components: Query Processor > Affects Versions: 0.9.0, 0.11.0 > Reporter: Alexander Pivovarov > > SELECT * FROM T1 LEFT OUTER JOIN T2 ON (T1.c1=T2.c2 AND T1.c1 < 10) > and SELECT * FROM T1 RIGHT OUTER JOIN T2 ON (T1.c1=T2.c2 AND T2.c1 < 10) > do not give correct results. > to reproduce: > hive> create table tt1 (c1 int); > hive> create table tt2 (c1 int); > $ vi tt1 > 1 > 2 > 3 > 4 > $ vi tt2 > 1 > 2 > 8 > 9 > $ hadoop fs -put tt1 /user/hive/warehouse/tt1/ > $ hadoop fs -put tt2 /user/hive/warehouse/tt2/ > wrong result: > hive> select * from tt1 left outer join tt2 on (tt1.c1 = tt2.c1 and tt1.c1 <= > 2); > 1 1 > 2 2 > 3 NULL > 4 NULL > correct result: > select * from tt1 left outer join tt2 on (tt1.c1 = tt2.c1) where tt1.c1 <= 2; > 1 1 > 2 2 > hive-0.11.0-bin$ head -1 RELEASE_NOTES.txt > Release Notes - Hive - Version 0.11.0 -- This message is automatically generated by JIRA. If you think it was sent incorrectly, please contact your JIRA administrators For more information on JIRA, see: http://www.atlassian.com/software/jira