You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@hive.apache.org by "Badger (JIRA)" <ji...@apache.org> on 2018/05/29 16:57:00 UTC
[jira] [Commented] (HIVE-16608) Incorrect results for INNER JOIN ON
clause / WHERE involving NVL / COALESCE
[ https://issues.apache.org/jira/browse/HIVE-16608?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16493847#comment-16493847 ]
Badger commented on HIVE-16608:
-------------------------------
We are also hitting this issue, but the proposed fix doesn't work either. Running the same query in spark sql works without issue. It is quite surprising this issue was created a year ago and noone else is encountering any issues.
> 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)