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)
