[
https://issues.apache.org/jira/browse/HIVE-16608?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
libo reassigned HIVE-16608:
---------------------------
Assignee: libo
> 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
> Assignee: libo
> 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
(v8.20.1#820001)
