[HIVE-27876] Incorrect query results on tables with ClusterBy & SortBy

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 4.0.0
• Labels: pull-request-available
• Assignee: Ramesh Kumar Thangarajan
• Reporter: Naresh P R
• Created: 15/Nov/23 22:43
• Updated: 29/Mar/24 17:49
• Resolved: 20/Dec/23 09:04

Description

Repro:

 

create external table test_bucket(age int, name string, dept string) clustered by (age, name) sorted by (age asc, name asc) into 2 buckets stored as orc;
insert into test_bucket values (1, 'user1', 'dept1'), ( 2, 'user2' , 'dept2');
insert into test_bucket values (1, 'user1', 'dept1'), ( 2, 'user2' , 'dept2');

//empty wrong results
select age, name, count(*) from test_bucket group by  age, name having count(*) > 1; 
+------+-------+------+
| age  | name  | _c2  |
+------+-------+------+
+------+-------+------+

// Workaround
set hive.map.aggr=false;
select age, name, count(*) from test_bucket group by  age, name having count(*) > 1; 
+------+--------+------+
| age  |  name  | _c2  |
+------+--------+------+
| 1    | user1  | 2    |
| 2    | user2  | 2    |
+------+--------+------+ 

 

 

Issue Links

• links to: GitHub Pull Request #4889