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)