[HIVE-404] Problems in "SELECT * FROM t SORT BY col1 LIMIT 100"

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 0.3.0
• Component/s: Query Processor
• Hadoop Flags: Reviewed

Release Note

HIVE-404. Fix ordering in "SELECT * FROM t SORT BY col1 LIMIT 100" when query is a outer-most query. (Namit Jain via zshao)

• Assignee: Namit Jain
• Reporter: Zheng Shao
• Created: 10/Apr/09 07:51
• Updated: 17/Dec/11 00:08
• Resolved: 18/Apr/09 01:25

Description

Unless the user specify "set mapred.reduce.tasks=1;", he will see unexpected results with the query of "SELECT * FROM t SORT BY col1 LIMIT 100"

Basically, in the first map-reduce job, each reducer will get sorted data and only keep the first 100. In the second map-reduce job, we will distribute and sort the data randomly, before feeding into a single reducer that outputs the first 100.

In short, the query will output 100 random records in N * 100 top records from each of the reducer in the first map-reduce job.

This is contradicting to what people expects.

We should propagate the SORT BY columns to the second map-reduce job.