[
https://issues.apache.org/jira/browse/HIVE-16608?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16589476#comment-16589476
]
Sathyaprakash Govindasamy commented on HIVE-16608:
--------------------------------------------------
Another work around is to write query like this
where nvl(TABLEA.attr, 0)= CASE WHEN TABLEB.attr IS NOT NULL THEN TABLEB.attr
ELSE 0 END
Converting one of the NVL statement to CASE statement is enough
> Incorrect results for INNER JOIN ON clause / WHERE involving NVL / COALESCE
> ---------------------------------------------------------------------------
>
> Key: HIVE-16608
> URL: https://issues.apache.org/jira/browse/HIVE-16608
> Project: Hive
> Issue Type: Bug
> Components: Logical Optimizer
> Affects Versions: 2.1.1
> Reporter: Dudu Markovitz
> Priority: Major
>
> {code}
> create table TABLEA (key int,attr int);
> create table TABLEB (key int,attr int);
> insert into TABLEA values (1,null),(2,0),(3,null);
> insert into TABLEB values (1,null),(2,null),(3,0);
> {code}
> The following SELECT statements should return 3 rows but return none:
> {code}
> select TABLEA.attr
> from TABLEA join TABLEB
> on TABLEA.key=TABLEB.key
> and nvl(TABLEA.attr, 0)=nvl(TABLEB.attr, 0)
> {code}
> {code}
> select TABLEA.attr
> from TABLEA join TABLEB
> on TABLEA.key=TABLEB.key
> where nvl(TABLEA.attr, 0)=nvl(TABLEB.attr, 0)
> {code}
> The same results returned for COALESCE
> In the execution plan we can notice the wrong predicate *attr is not null*,
> twice
> {code}
> predicate: (key is not null and attr is not null) (type: boolean)
> {code}
> Selecting columns from both tables seems to prevent the issue.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
