drill-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Deneche A. Hakim (JIRA)" <j...@apache.org>
Subject [jira] [Updated] (DRILL-4463) Frame clause in window definition without PARTITION BY and ORDER BY results in validation error
Date Mon, 07 Mar 2016 16:36:41 GMT

     [ 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: Execution - Flow)
                 SQL Parser

> 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: SQL Parser
>    Affects Versions: 1.6.0
>         Environment: 4 node cluster
>            Reporter: Khurram Faraaz
>              Labels: window_function
>
> 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)

Mime
View raw message