hive-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Navis (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (HIVE-9412) Window clause ROW BETWEEN for PRECEDING does not work
Date Thu, 22 Jan 2015 06:55:34 GMT

    [ 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)

Mime
View raw message