[IMPALA-2734] Bad result for subquery with having

• Type: Bug
• Status: Closed
• Priority: Blocker
• Resolution: Fixed
• Affects Version/s: Impala 2.2, Impala 2.3.0
• Fix Version/s: Impala 2.5.0, Impala 2.3.4
• Component/s: Frontend
• Labels: correctness planner
• Target Version: Impala 2.5.0, Impala 2.3.4
• Assignee: Dimitris Tsirogiannis
• Reporter: Peter Brejcak
• Created: 03/Dec/15 07:26
• Updated: 09/Sep/16 09:03
• Resolved: 04/Feb/16 04:17

Description

There is problem for combination of subquery with having clause.
First query should return 1 row (and result is 8 rows), second 7 rows (and result is 0 rows).
Steps to reproduce:

create table table_subquery_having_problem (id int, value int);
insert into table table_subquery_having_problem values (1,1);
insert into table table_subquery_having_problem values (1,2);
insert into table table_subquery_having_problem values (1,3);
insert into table table_subquery_having_problem values (1,4);
insert into table table_subquery_having_problem values (1,5);
insert into table table_subquery_having_problem values (1,6);
insert into table table_subquery_having_problem values (1,7);
insert into table table_subquery_having_problem values (1,8);
insert into table table_subquery_having_problem values (1,9);

select x.value from table_subquery_having_problem x
where exists 
(select 1 from table_subquery_having_problem y 
where x.value>y.value
 and x.id=y.id
group by y.id
having count(1)=1);

select x.value from table_subquery_having_problem x
where exists 
(select 1 from table_subquery_having_problem y 
where x.value>y.value
 and x.id=y.id
group by y.id
having count(1)>1)