[ https://issues.apache.org/jira/browse/HIVE-741?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12896789#action_12896789 ]
Amareshwari Sriramadasu commented on HIVE-741: ---------------------------------------------- bq. When 'null' is replaced by '' it works. I see the same result even if 'null' is replaced with ''. To reproduce the above, I created a table input1 with {code} hive> create table input1 (key int, value int); {code} Loaded the following input using Load data command {noformat} in.txt ^A35 12^A 10^A1000 10^A100 100^A100 {noformat} I see the following output for join queries executed. {noformat} hive> select * from input1 a join input1 b on a.key=b.value; Output: NULL 35 12 NULL 100 100 10 100 100 100 100 100 Expected Output: 100 100 10 100 100 100 100 100 hive> select * from input1 a left outer join input1 b on a.key=b.value; OUTPUT: NULL 35 12 NULL 10 1000 NULL NULL 10 100 NULL NULL 12 NULL NULL NULL 100 100 10 100 100 100 100 100 Expected Output: NULL 35 NULL NULL 10 1000 NULL NULL 10 100 NULL NULL 12 NULL NULL NULL 100 100 10 100 100 100 100 100 hive> select * from input1 a right outer join input1 b on a.key=b.value; OUTPUT: NULL 35 12 NULL NULL NULL NULL 35 100 100 10 100 100 100 100 100 NULL NULL 10 1000 ExpectedOutput: NULL NULL NULL 35 NULL NULL 12 NULL 100 100 10 100 100 100 100 100 NULL NULL 10 1000 {noformat} Expected output is obtained from mysql db for a similar query. Ning, if you are not working on the fix for this, I would like to contribute. Would need your help understanding join code also, as I'm a new to hive. > 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: Ning Zhang > > 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.