[
https://issues.apache.org/jira/browse/DRILL-3279?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14601636#comment-14601636
]
Deneche A. Hakim commented on DRILL-3279:
-----------------------------------------
Postgres seem to allow the following window functions without an order-by
clause: row_number, rank, dense_rank, percent_rank and cume_dist.
The SQL Standard actually states the following:
{noformat}
If <ntile function>, <lead or lag function>, RANK or DENSE_RANK is specified,
then the window ordering clause WOC of WDX shall be present.
{noformat}
In Postgres, when we don't specify an order-by clause in the query, each
partition will contain one single window frame and the following functions will
return the same value for all rows of each partition:
- rank returns, dense_rank and cume_dist return 1
- percent_rank returns 0
[~amansinha100], [~vicky] and [~jni] what do you think we should support in
Drill ?
> Window functions without ORDER BY - Error message needs to be fixed
> -------------------------------------------------------------------
>
> Key: DRILL-3279
> URL: https://issues.apache.org/jira/browse/DRILL-3279
> Project: Apache Drill
> Issue Type: Bug
> Components: Execution - Flow
> Affects Versions: 1.0.0
> Reporter: Khurram Faraaz
> Assignee: Deneche A. Hakim
> Priority: Minor
> Labels: window_function
> Fix For: 1.1.0
>
>
> We need to add these two window function names CUME_DIST and PERCENT_RANK to
> the error message, when user issues window function query without an order by
> in window definition.
> {code}
> 0: jdbc:drill:schema=dfs.tmp> select cume_dist() over (partition by col_chr)
> from `allDataInPrq/0_0_0.parquet`;
> Error: PARSE ERROR: From line 1, column 25 to line 1, column 46: RANK or
> DENSE_RANK functions require ORDER BY clause in window specification
> [Error Id: 330100e4-d90e-43db-8893-8e9ad3783874 on centos-03.qa.lab:31010]
> (state=,code=0)
> {code}
> {code}
> 0: jdbc:drill:schema=dfs.tmp> select row_number() over (partition by col_chr)
> from `allDataInPrq/0_0_0.parquet`;
> Error: PARSE ERROR: From line 1, column 26 to line 1, column 47: RANK or
> DENSE_RANK functions require ORDER BY clause in window specification
> [Error Id: 4a95813f-592c-45e4-a74c-1cd34a9067c9 on centos-03.qa.lab:31010]
> (state=,code=0)
> {code}
> {code}
> 0: jdbc:drill:schema=dfs.tmp> select rank() over (partition by col_chr) from
> `allDataInPrq/0_0_0.parquet`;
> Error: PARSE ERROR: From line 1, column 20 to line 1, column 41: RANK or
> DENSE_RANK functions require ORDER BY clause in window specification
> [Error Id: 19bbd577-b653-4ebb-8f62-b9aee2bd6be5 on centos-03.qa.lab:31010]
> (state=,code=0)
> {code}
> {code}
> 0: jdbc:drill:schema=dfs.tmp> select dense_rank() over (partition by col_chr)
> from `allDataInPrq/0_0_0.parquet`;
> Error: PARSE ERROR: From line 1, column 26 to line 1, column 47: RANK or
> DENSE_RANK functions require ORDER BY clause in window specification
> [Error Id: f9a438db-097c-441e-94a4-1d07835fcca7 on centos-03.qa.lab:31010]
> (state=,code=0)
> {code}
> {code}
> 0: jdbc:drill:schema=dfs.tmp> select percent_rank() over (partition by
> col_chr) from `allDataInPrq/0_0_0.parquet`;
> Error: PARSE ERROR: From line 1, column 28 to line 1, column 49: RANK or
> DENSE_RANK functions require ORDER BY clause in window specification
> [Error Id: 897f32ea-4da5-4b1d-8864-f504bfe5ab6f on centos-03.qa.lab:31010]
> (state=,code=0)
> {code}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
