[ https://issues.apache.org/jira/browse/HIVE-3381?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Phabricator updated HIVE-3381: ------------------------------ Attachment: HIVE-3381.D5565.4.patch navis updated the revision "HIVE-3381 [jira] Result of outer join is not valid". Reviewers: JIRA 1. Simplified code a little 2. Added comments including example 3. Rebased on trunk & passed all tests REVISION DETAIL https://reviews.facebook.net/D5565 AFFECTED FILES ql/src/java/org/apache/hadoop/hive/ql/exec/CommonJoinOperator.java ql/src/java/org/apache/hadoop/hive/ql/exec/HashTableSinkOperator.java ql/src/java/org/apache/hadoop/hive/ql/exec/JoinOperator.java ql/src/java/org/apache/hadoop/hive/ql/exec/JoinUtil.java ql/src/java/org/apache/hadoop/hive/ql/exec/MapJoinOperator.java ql/src/java/org/apache/hadoop/hive/ql/exec/SMBMapJoinOperator.java ql/src/java/org/apache/hadoop/hive/ql/exec/persistence/MapJoinObjectValue.java ql/src/test/results/clientpositive/auto_join21.q.out ql/src/test/results/clientpositive/auto_join29.q.out ql/src/test/results/clientpositive/auto_join7.q.out ql/src/test/results/clientpositive/auto_join_filters.q.out ql/src/test/results/clientpositive/join21.q.out ql/src/test/results/clientpositive/join7.q.out ql/src/test/results/clientpositive/join_1to1.q.out ql/src/test/results/clientpositive/join_filters.q.out ql/src/test/results/clientpositive/join_filters_overlap.q.out To: JIRA, navis Cc: njain > Result of outer join is not valid > --------------------------------- > > Key: HIVE-3381 > URL: https://issues.apache.org/jira/browse/HIVE-3381 > Project: Hive > Issue Type: Bug > Components: Query Processor > Affects Versions: 0.10.0 > Reporter: Navis > Assignee: Navis > Priority: Critical > Attachments: HIVE-3381.D5565.3.patch, HIVE-3381.D5565.4.patch > > > Outer joins, especially full outer joins or outer join with filter on 'ON > clause' is not showing proper results. For example, query in test join_1to1.q > {code} > SELECT * FROM join_1to1_1 a full outer join join_1to1_2 b on a.key1 = b.key1 > and a.value = 66 and b.value = 66 ORDER BY a.key1 ASC, a.key2 ASC, a.value > ASC, b.key1 ASC, b.key2 ASC, b.value ASC; > {code} > results > {code} > NULL NULL NULL NULL NULL 66 > NULL NULL NULL NULL 10050 66 > NULL NULL NULL 10 10010 66 > NULL NULL NULL 30 10030 88 > NULL NULL NULL 35 10035 88 > NULL NULL NULL 40 10040 88 > NULL NULL NULL 40 10040 88 > NULL NULL NULL 50 10050 88 > NULL NULL NULL 50 10050 88 > NULL NULL NULL 50 10050 88 > NULL NULL NULL 70 10040 88 > NULL NULL NULL 70 10040 88 > NULL NULL NULL 70 10040 88 > NULL NULL NULL 70 10040 88 > NULL NULL 66 NULL NULL NULL > NULL 10050 66 NULL NULL NULL > 5 10005 66 5 10005 66 > 15 10015 66 NULL NULL NULL > 20 10020 66 20 10020 66 > 25 10025 88 NULL NULL NULL > 30 10030 66 NULL NULL NULL > 35 10035 88 NULL NULL NULL > 40 10040 66 NULL NULL NULL > 40 10040 66 40 10040 66 > 40 10040 88 NULL NULL NULL > 40 10040 88 NULL NULL NULL > 50 10050 66 NULL NULL NULL > 50 10050 66 50 10050 66 > 50 10050 66 50 10050 66 > 50 10050 88 NULL NULL NULL > 50 10050 88 NULL NULL NULL > 50 10050 88 NULL NULL NULL > 50 10050 88 NULL NULL NULL > 50 10050 88 NULL NULL NULL > 50 10050 88 NULL NULL NULL > 60 10040 66 60 10040 66 > 60 10040 66 60 10040 66 > 60 10040 66 60 10040 66 > 60 10040 66 60 10040 66 > 70 10040 66 NULL NULL NULL > 70 10040 66 NULL NULL NULL > 70 10040 66 NULL NULL NULL > 70 10040 66 NULL NULL NULL > 80 10040 88 NULL NULL NULL > 80 10040 88 NULL NULL NULL > 80 10040 88 NULL NULL NULL > 80 10040 88 NULL NULL NULL > {code} > but it seemed not right. This should be > {code} > NULL NULL NULL NULL NULL 66 > NULL NULL NULL NULL 10050 66 > NULL NULL NULL 10 10010 66 > NULL NULL NULL 25 10025 66 > NULL NULL NULL 30 10030 88 > NULL NULL NULL 35 10035 88 > NULL NULL NULL 40 10040 88 > NULL NULL NULL 50 10050 88 > NULL NULL NULL 70 10040 88 > NULL NULL NULL 70 10040 88 > NULL NULL NULL 80 10040 66 > NULL NULL NULL 80 10040 66 > NULL NULL 66 NULL NULL NULL > NULL 10050 66 NULL NULL NULL > 5 10005 66 5 10005 66 > 15 10015 66 NULL NULL NULL > 20 10020 66 20 10020 66 > 25 10025 88 NULL NULL NULL > 30 10030 66 NULL NULL NULL > 35 10035 88 NULL NULL NULL > 40 10040 66 40 10040 66 > 40 10040 88 NULL NULL NULL > 50 10050 66 50 10050 66 > 50 10050 66 50 10050 66 > 50 10050 88 NULL NULL NULL > 50 10050 88 NULL NULL NULL > 60 10040 66 60 10040 66 > 60 10040 66 60 10040 66 > 60 10040 66 60 10040 66 > 60 10040 66 60 10040 66 > 70 10040 66 NULL NULL NULL > 70 10040 66 NULL NULL NULL > 80 10040 88 NULL NULL NULL > 80 10040 88 NULL NULL NULL > {code} -- 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