[ https://issues.apache.org/jira/browse/HIVE-1534?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12897587#action_12897587 ]
Amareshwari Sriramadasu commented on HIVE-1534: ----------------------------------------------- For a table input3 with following data: ||key || value || |NULL | 35 | |12 | NULL| |10 | 1000 | |10 | 100| |100| 100 | The queries {code} SELECT * FROM input3 a left outer JOIN input3 b ON (a.key=b.key AND a.key < 100); and SELECT * FROM input3 a right outer JOIN input3 b ON (a.key=b.key AND b.key < 100); {code} produce the output as {noformat} 10 1000 10 100 10 1000 10 1000 10 100 10 100 10 100 10 1000 12 NULL 12 NULL {noformat} Where as the expected output for "SELECT * FROM input3 a left outer JOIN input3 b ON (a.key=b.key AND a.key < 100);" is | NULL | 35 | NULL | NULL | | 10 | 1000 | 10 | 1000 | | 10 | 1000 | 10 | 100 | | 100 | 100 | NULL | NULL | | 12 | NULL | 12 | NULL | | 10 | 100 | 10 | 1000 | | 10 | 100 | 10 | 100 | and expected output for "SELECT * FROM input3 a right outer JOIN input3 b ON (a.key=b.key AND b.key < 100);" is | NULL | NULL | NULL | 35 | | 10 | 1000 | 10 | 1000 | | 10 | 100 | 10 | 1000 | | NULL | NULL | 100 | 100 | | 12 | NULL | 12 | NULL | | 10 | 1000 | 10 | 100 | | 10 | 100 | 10 | 100 | > predicate pushdown does not work correctly with outer joins > ----------------------------------------------------------- > > Key: HIVE-1534 > URL: https://issues.apache.org/jira/browse/HIVE-1534 > Project: Hadoop Hive > Issue Type: Bug > Components: Query Processor > Reporter: Amareshwari Sriramadasu > > The hive documentation for predicate pushdown says: > Left outer join: predicates on the left side aliases are pushed > Right outer join: predicates on the right side aliases are pushed > But, this pushdown should not happen for AND predicates in join queries: > ex: SELECT * FROM T1 JOIN T2 ON (T1.c1=T2.c2 AND T1.c1 < 10) -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.