[HIVE-9412] Window clause ROW BETWEEN for PRECEDING does not work

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Duplicate
• Affects Version/s: 0.13.1, 0.14.0
• Fix Version/s: 2.0.0
• Component/s: Parser

Environment

CDH 5.3 or HDP 2.2

• Assignee: Navis Ryu
• Reporter: Will Du
• Created: 19/Jan/15 15:52
• Updated: 23/Mar/16 12:42
• Resolved: 23/Mar/16 12:42

Description

When window clause with ROWS is used between two proper PRECEDING boundaries, Hive reports syntax errors as following examples.

--For example 1
SELECT name, dept_num, salary,
MAX(salary) OVER (PARTITION BY dept_num ORDER BY
name ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING) win4_alter
FROM employee_contract;

Error: Error while compiling statement: FAILED: SemanticException Failed to breakup Windowing invocations into Groups. At least 1 group must only depend on input columns. Also check for circular dependencies.
Underlying error: Window range invalid, start boundary is greater than end boundary: window(start=range(2 PRECEDING), end=range(1 PRECEDING)) (state=42000,code=40000)

--For example 2
SELECT name, dept_num, salary,
MAX(salary) OVER (PARTITION BY dept_num ORDER BY
name ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) win1
FROM employee_contract;

Error: Error while compiling statement: FAILED: SemanticException End of a WindowFrame cannot be UNBOUNDED PRECEDING (state=42000,code=40000)

Issue Links

• duplicates: HIVE-10555 Improve windowing spec of range based windowing to support additional range formats
• links to: review board