Vineet Garg created HIVE-23066:
----------------------------------
Summary: [Subqueries] Throw an error if COALESCE/NVL is used in
correlated condition
Key: HIVE-23066
URL: https://issues.apache.org/jira/browse/HIVE-23066
Project: Hive
Issue Type: Bug
Components: Query Planning
Reporter: Vineet Garg
Assignee: Vineet Garg
NVL could lead to wrong results
{code:sql}
create table TABLEA (id int, lib string);
insert into TABLEA values (1, 'a'),(2, 'b'),(3, null),(5,'zx');
create table TABLEB (id int, lib string);
insert into TABLEB values (1, 'a'),(4, 'c'),(3, null),(5,'zy');
select *
from TABLEA a
where exists (
select 1
from TABLEB b
where nvl(a.lib,0) = nvl(b.lib,0)
);
{code}
***OUTPUT***
{noformat}
+-------+--------+
| a.id | a.lib |
+-------+--------+
| 1 | a |
+-------+--------+
{noformat}
***EXPECTED***
{noformat}
+-------+--------+
| a.id | a.lib |
+-------+--------+
| 1 | a |
| 3 | NULL |
+-------+--------+
{noformat}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
