Dudu Markovitz created HIVE-16608:
-------------------------------------
Summary: 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
{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
(v6.3.15#6346)
