Paul Jackson created HIVE-18334:
-----------------------------------
Summary: Cannot JOIN ON result of COALESCE
Key: HIVE-18334
URL: https://issues.apache.org/jira/browse/HIVE-18334
Project: Hive
Issue Type: Bug
Affects Versions: 2.3.2
Environment: Amazon AWS
Release label:emr-5.11.0
Hadoop distribution:Amazon 2.7.3
Applications:Hive 2.3.2, Pig 0.17.0, Hue 4.0.1
classification=hive-site,properties=[hive.strict.checks.cartesian.product=false,hive.mapred.mode=nonstrict]
Reporter: Paul Jackson
Priority: Minor
A join is returning no results when the ON clause is equating the results of
two COALESCE functions. To reproduce:
{code:SQL}
CREATE TABLE t5 (
dno INTEGER,
dname VARCHAR(30),
eno INTEGER,
ename VARCHAR(30));
CREATE TABLE t6 (
dno INTEGER,
dname VARCHAR(30),
eno INTEGER,
ename VARCHAR(30));
INSERT INTO t5 VALUES
(10, 'FOO', NULL, NULL),
(20, 'BAR', NULL, NULL),
(NULL, NULL, 7300, 'LARRY'),
(NULL, NULL, 7400, 'MOE'),
(NULL, NULL, 7500, 'CURLY');
INSERT INTO t6 VALUES
(10, 'LENNON', NULL, NULL),
(20, 'MCCARTNEY', NULL, NULL),
(NULL, NULL, 7300, 'READY'),
(NULL, NULL, 7400, 'WILLING'),
(NULL, NULL, 7500, 'ABLE');
-- Fails with 0 results
SELECT *
FROM t5
INNER JOIN t6
ON COALESCE(`t5`.`eno`, `t5`.`dno`) = COALESCE(`t6`.`eno`, `t6`.`dno`)
-- Full cross with where clause works, returning 5 results
SELECT *
FROM t5
JOIN t6
WHERE `t5`.`eno` = `t6`.`eno` OR `t5`.`dno` = `t6`.`dno`
-- Strange that coalescing the same field returns 2 results...
SELECT *
FROM t5
INNER JOIN t6
ON COALESCE(`t5`.`dno`, `t5`.`dno`) = COALESCE(`t6`.`dno`, `t6`.`dno`)
-- ...and coalescing the other field returns 3 results
SELECT *
FROM t5
INNER JOIN t6
ON COALESCE(`t5`.`eno`, `t5`.`eno`) = COALESCE(`t6`.`eno`, `t6`.`eno`)
{code}
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
