[
https://issues.apache.org/jira/browse/HIVE-9412?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14287027#comment-14287027
]
Navis commented on HIVE-9412:
-----------------------------
Verified with result from postgres
{noformat}
value | min | max | sum | first_value | last_value | min | max | sum |
first_value | last_value | min | max | sum | first_value | last_value | min |
max | sum | first_value | last_value
---------+-----+-----+-----+-------------+------------+-----+-----+------+-------------+------------+-----+-----+-----+-------------+------------+-----+-----+------+-------------+------------
val_484 | | | | | | | | |
| | 98 | 278 | 376 | 98 | 278 | 27 | 409 |
1867 | 98 | 238
val_98 | 484 | 484 | 484 | 484 | 484 | 484 | 484 | 484 |
484 | 484 | 255 | 278 | 533 | 278 | 255 | 27 | 409 |
1769 | 278 | 238
val_278 | 98 | 484 | 582 | 484 | 98 | 98 | 484 | 582 |
484 | 98 | 255 | 409 | 664 | 255 | 409 | 27 | 409 |
1491 | 255 | 238
val_255 | 98 | 278 | 376 | 98 | 278 | 98 | 484 | 860 |
484 | 278 | 165 | 409 | 574 | 409 | 165 | 27 | 409 |
1236 | 409 | 238
val_409 | 255 | 278 | 533 | 278 | 255 | 98 | 484 | 1115 |
484 | 255 | 27 | 165 | 192 | 165 | 27 | 27 | 311 |
827 | 165 | 238
val_165 | 255 | 409 | 664 | 255 | 409 | 98 | 484 | 1524 |
484 | 409 | 27 | 311 | 338 | 27 | 311 | 27 | 311 |
662 | 27 | 238
val_27 | 165 | 409 | 574 | 409 | 165 | 98 | 484 | 1689 |
484 | 165 | 86 | 311 | 397 | 311 | 86 | 86 | 311 |
635 | 311 | 238
val_311 | 27 | 165 | 192 | 165 | 27 | 27 | 484 | 1716 |
484 | 27 | 86 | 238 | 324 | 86 | 238 | 86 | 238 |
324 | 86 | 238
val_86 | 27 | 311 | 338 | 27 | 311 | 27 | 484 | 2027 |
484 | 311 | 238 | 238 | 238 | 238 | 238 | 238 | 238 |
238 | 238 | 238
val_238 | 86 | 311 | 397 | 311 | 86 | 27 | 484 | 2113 |
484 | 86 | | | | | | | |
| |
{noformat}
> Window clause ROW BETWEEN for PRECEDING does not work
> -----------------------------------------------------
>
> Key: HIVE-9412
> URL: https://issues.apache.org/jira/browse/HIVE-9412
> Project: Hive
> Issue Type: Bug
> Components: Parser
> Affects Versions: 0.14.0, 0.13.1
> Environment: CDH 5.3 or HDP 2.2
> Reporter: Will Du
> Assignee: Navis
> Attachments: HIVE-9412.1.patch.txt, HIVE-9412.2.patch.txt,
> HIVE-9412.3.patch.txt
>
>
> 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)
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
