You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@hive.apache.org by "Chaoyu Tang (JIRA)" <ji...@apache.org> on 2015/12/02 16:55:11 UTC

[jira] [Created] (HIVE-12566) Incorrect result returns when using COALESCE in WHERE condition with LEFT JOIN

Chaoyu Tang created HIVE-12566:
----------------------------------

             Summary: Incorrect result returns when using COALESCE in WHERE condition with LEFT JOIN
                 Key: HIVE-12566
                 URL: https://issues.apache.org/jira/browse/HIVE-12566
             Project: Hive
          Issue Type: Bug
          Components: Query Planning
    Affects Versions: 0.13.0
            Reporter: Chaoyu Tang
            Priority: Critical


The left join query with on/where clause returns incorrect result (more rows are returned). See the reproducible sample below.
Left table with data:
{code}
CREATE TABLE ltable (i int, la int, lk1 string, lk2 string) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
---
1,\N,CD5415192314304,00071
2,\N,CD5415192225530,00071
{code}
Right  table with data:
{code}
CREATE TABLE rtable (ra int, rk1 string, rk2 string) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
---
1,CD5415192314304,00071
45,CD5415192314304,00072
{code}
Query:
{code}
SELECT * FROM ltable l LEFT OUTER JOIN rtable r on (l.lk1 = r.rk1 AND l.lk2 = r.rk2) WHERE COALESCE(l.la,'EMPTY')=COALESCE(r.ra,'EMPTY');
{code}
Result returns:
{code}
1	NULL	CD5415192314304	00071	NULL	NULL	NULL
2	NULL	CD5415192225530	00071	NULL	NULL	NULL
{code}
The correct result should be
{code}
2	NULL	CD5415192225530	00071	NULL	NULL	NULL
{code}



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)