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

["libo (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/HIVE-16608?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17440854#comment-17440854 ] 

libo commented on HIVE-16608:
-----------------------------

The issue [17148|https://issues.apache.org/jira/browse/HIVE-17148]  fixed this bug

> 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
(v8.20.1#820001)