[HIVE-9347] Bug with max() together with rank() and grouping sets

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 0.13.1, 0.14.0
• Fix Version/s: 1.1.0
• Component/s: Query Processor

Environment

Amazon Elastic Map Reduce, AMI 3.3.1, Hadoop Amazon 2.4.0, Hive 0.13.1

• Assignee: Navis Ryu
• Reporter: Michal Krawczyk
• Created: 12/Jan/15 12:13
• Updated: 12/Feb/15 23:41
• Resolved: 19/Jan/15 17:16

Description

It looks like the query below returns incorrect results on Hive 0.13.1, but it was working fine on Hive 0.11.

I have the following table:
CREATE TABLE `t`(
`category` int,
`live` int,
`comments` int)

with the following data:
hive> select * from t;
OK
3 0 2
2 0 2
8 0 2

The query:
hive> select category, max(live) live, max(comments) comments, rank() OVER (PARTITION BY category ORDER BY comments) rank1
FROM t
GROUP BY category
GROUPING SETS ((), (category))
HAVING max(comments) > 0;

return the following results:

NULL 1 48 1
2 1 49 1
3 1 49 1
8 1 49 1

When using grouping sets with the rank() function the max() function return incorrect results. Everything works fine if I remove grouping sets clause and split the query into two independent queries or remove the rank() function.

This looks like a bug to me but please review. That said, I'm not sure if it's just Amazon issue or general Hive issue.

Issue Links

• links to: review board