[ https://issues.apache.org/jira/browse/DRILL-3279?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14603165#comment-14603165 ]
Victoria Markman edited comment on DRILL-3279 at 6/26/15 4:52 PM: ------------------------------------------------------------------ Hakim and I investigated this morning and we see that Calcite is already following SQL standard: {code} 0: jdbc:drill:schema=dfs> select rank() over(partition by b1 order by c1 RANGE UNBOUNDED PRECEDING) from t1; Error: PARSE ERROR: From line 1, column 48 to line 1, column 52: ROW/RANGE not allowed with RANK or DENSE_RANK functions [Error Id: c9ce36b7-db45-4503-b936-c1ce633fe40b on atsqa4-133.qa.lab:31010] (state=,code=0) 0: jdbc:drill:schema=dfs> select row_number() over(partition by b1 order by c1 RANGE UNBOUNDED PRECEDING) from t1; Error: PARSE ERROR: From line 1, column 54 to line 1, column 58: ROW/RANGE not allowed with RANK or DENSE_RANK functions [Error Id: 20924dea-8b2e-43e1-8f88-87675016e905 on atsqa4-133.qa.lab:31010] (state=,code=0) {code} We just need to adjust error message, to mention rest of the functions. I will file a separate Jira for that. was (Author: vicky): Hakim and I investigated this morning and we see that Calcite is already following SQL standard: {code} 0: jdbc:drill:schema=dfs> select rank() over(partition by b1 order by c1 RANGE UNBOUNDED PRECEDING) from t1; Error: PARSE ERROR: From line 1, column 48 to line 1, column 52: ROW/RANGE not allowed with RANK or DENSE_RANK functions [Error Id: c9ce36b7-db45-4503-b936-c1ce633fe40b on atsqa4-133.qa.lab:31010] (state=,code=0) {code} We just need to adjust error message, to mention rest of the functions. I will file a separate Jira for that. > 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.2.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)