[HIVE-3381] Result of outer join is not valid

• Type: Bug
• Status: Closed
• Priority: Critical
• Resolution: Fixed
• Affects Version/s: 0.10.0
• Fix Version/s: 0.11.0
• Component/s: Query Processor
• Assignee: Navis Ryu
• Reporter: Navis Ryu
• Created: 14/Aug/12 07:10
• Updated: 16/May/13 21:09
• Resolved: 26/Mar/13 17:32

Description

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

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;

results

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

but it seemed not right. This should be

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

Issue Links

• is blocked by: HIVE-3411 Filter predicates on outer join overlapped on single alias is not handled properly