[
https://issues.apache.org/jira/browse/DRILL-4463?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Deneche A. Hakim updated DRILL-4463:
------------------------------------
Component/s: (was: SQL Parser)
Query Planning & Optimization
> Frame clause in window definition without PARTITION BY and ORDER BY results
> in validation error
> -----------------------------------------------------------------------------------------------
>
> Key: DRILL-4463
> URL: https://issues.apache.org/jira/browse/DRILL-4463
> Project: Apache Drill
> Issue Type: Bug
> Components: Query Planning & Optimization
> Affects Versions: 1.6.0
> Environment: 4 node cluster
> Reporter: Khurram Faraaz
> Labels: window_function
> Fix For: Future
>
>
> Use of frame clause in window definition without PARTITION BY and ORDER BY
> results in validation error, this should be supported as per SQL spec.
> Drill 1.6.0, commit ID : 6d5f4983, CentOS
> {noformat}
> 0: jdbc:drill:schema=dfs.tmp> SELECT LAST_VALUE(c1) OVER(RANGE BETWEEN
> UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM `t_alltype`;
> Error: VALIDATION ERROR: From line 1, column 27 to line 1, column 85: Window
> specification must contain an ORDER BY clause
> [Error Id: 4ff9916f-f122-46ae-81f1-8cd1d90fa331 on centos-02.qa.lab:31010]
> (state=,code=0)
> {noformat}
> Same query as above with an EMPTY window definition returns results. Note
> that there is no ORDER BY clause in the below query. Error message in the
> above query says "Window specification must contain an ORDER BY clause",
> which is not correct.
> {noformat}
> 0: jdbc:drill:schema=dfs.tmp> SELECT LAST_VALUE(c1) OVER( ) FROM `t_alltype`;
> +---------+
> | EXPR$0 |
> +---------+
> | 145 |
> | 145 |
> | 145 |
> | 145 |
> | 145 |
> | 145 |
> | 145 |
> | 145 |
> ...
> | 145 |
> | 145 |
> | 145 |
> | 145 |
> | 145 |
> | 145 |
> | 145 |
> | 145 |
> +--------+
> 145 rows selected (0.249 seconds)
> {noformat}
> Postgres returns results for the query that uses frame clause within window
> definition, without an ORDER BY clause.
> {noformat}
> postgres=# SELECT LAST_VALUE(c1) OVER(RANGE BETWEEN UNBOUNDED PRECEDING AND
> UNBOUNDED FOLLOWING) FROM t_alltype;;
> last_value
> ------------
> 145
> 145
> 145
> 145
> 145
> 145
> 145
> ...
> 145
> 145
> 145
> 145
> 145
> (145 rows)
> {noformat}
> Also another point to note, a similar window function query with a different
> frame clause, works and Drill returns results.
> {noformat}
> 0: jdbc:drill:schema=dfs.tmp> SELECT LAST_VALUE(c1) OVER(ROWS BETWEEN
> UNBOUNDED PRECEDING AND CURRENT ROW) FROM `t_alltype`;
> +---------+
> | EXPR$0 |
> +---------+
> | 1 |
> | 2 |
> | 3 |
> | 4 |
> | 5 |
> | 6 |
> | 7 |
> | 8 |
> | 9 |
> | 10 |
> | 11 |
> | 12 |
> | 13 |
> | 14 |
> | 15 |
> | 16 |
> | 17 |
> | 18 |
> ...
> | 140 |
> | 141 |
> | 142 |
> | 143 |
> | 144 |
> | 145 |
> +--------+
> 145 rows selected (0.254 seconds)
> {noformat}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
