[
https://issues.apache.org/jira/browse/HIVE-741?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12899753#action_12899753
]
Ning Zhang commented on HIVE-741:
---------------------------------
@Amareshwari, sorry the syntax was wrong for the 3 table joins. Below is the
correct syntax and plan.
{code}
explain select * from src a left outer join src b on (a.value=b.value) right
outer join src c on (b.value=c.value);
OK
ABSTRACT SYNTAX TREE:
(TOK_QUERY (TOK_FROM (TOK_RIGHTOUTERJOIN (TOK_LEFTOUTERJOIN (TOK_TABREF src
a) (TOK_TABREF src b) (= (. (TOK_TABLE_OR_COL a) value) (. (TOK_TABLE_OR_COL b)
value))) (TOK_TABREF src c) (= (. (TOK_TABLE_OR_COL b) value) (.
(TOK_TABLE_OR_COL c) value)))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR
TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR TOK_ALLCOLREF))))
STAGE DEPENDENCIES:
Stage-1 is a root stage
Stage-0 is a root stage
STAGE PLANS:
Stage: Stage-1
Map Reduce
Alias -> Map Operator Tree:
a
TableScan
alias: a
Reduce Output Operator
key expressions:
expr: value
type: string
sort order: +
Map-reduce partition columns:
expr: value
type: string
tag: 0
value expressions:
expr: key
type: string
expr: value
type: string
b
TableScan
alias: b
Reduce Output Operator
key expressions:
expr: value
type: string
sort order: +
Map-reduce partition columns:
expr: value
type: string
tag: 1
value expressions:
expr: key
type: string
expr: value
type: string
c
TableScan
alias: c
Reduce Output Operator
key expressions:
expr: value
type: string
sort order: +
Map-reduce partition columns:
expr: value
type: string
tag: 2
value expressions:
expr: key
type: string
expr: value
type: string
Reduce Operator Tree:
Join Operator
condition map:
Left Outer Join0 to 1
Right Outer Join1 to 2
condition expressions:
0 {VALUE._col0} {VALUE._col1}
1 {VALUE._col0} {VALUE._col1}
2 {VALUE._col0} {VALUE._col1}
handleSkewJoin: false
outputColumnNames: _col0, _col1, _col4, _col5, _col8, _col9
Select Operator
expressions:
expr: _col0
type: string
expr: _col1
type: string
expr: _col4
type: string
expr: _col5
type: string
expr: _col8
type: string
expr: _col9
type: string
outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5
File Output Operator
compressed: true
GlobalTableId: 0
table:
input format: org.apache.hadoop.mapred.TextInputFormat
output format:
org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
Stage: Stage-0
Fetch Operator
limit: -1
{code}
> NULL is not handled correctly in join
> -------------------------------------
>
> Key: HIVE-741
> URL: https://issues.apache.org/jira/browse/HIVE-741
> Project: Hadoop Hive
> Issue Type: Bug
> Reporter: Ning Zhang
> Assignee: Amareshwari Sriramadasu
> Attachments: patch-741.txt
>
>
> With the following data in table input4_cb:
> Key Value
> ------ --------
> NULL 325
> 18 NULL
> The following query:
> {code}
> select * from input4_cb a join input4_cb b on a.key = b.value;
> {code}
> returns the following result:
> NULL 325 18 NULL
> The correct result should be empty set.
> When 'null' is replaced by '' it works.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
