[HIVE-17726] Using exists may lead to incorrect results

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 3.0.0
• Fix Version/s: 3.0.0
• Assignee: Vineet Garg
• Reporter: Zoltan Haindrich
• Created: 06/Oct/17 14:03
• Updated: 22/May/18 23:15
• Resolved: 12/Oct/17 21:10

Description

drop table if exists tx1;
create table tx1 (a integer,b integer);
insert into tx1	values  (1, 1),
                        (1, 2),
                        (1, 3);

select count(*) as result,3 as expected from tx1 u
where exists (select * from tx1 v where u.a=v.a and u.b <> v.b);

select count(*) as result,3 as expected from tx1 u
where exists (select * from tx1 v where u.a=v.a and u.b <> v.b limit 1);

current results are 6 and 2.

Issue Links

• is related to: HIVE-15456 Umbrella JIRA for all subquery redesign related tasks and issues
• relates to: HIVE-17823 Fix subquery Qtest of Hive on Spark