[jira] [Created] (HIVE-16608) Incorrect results for INNER JOIN ON clause / WHERE involving NVL / COALESCE

["Dudu Markovitz (JIRA)" <ji...@apache.org>]

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)