Khurram Faraaz created DRILL-4463:
-------------------------------------

             Summary: 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: Execution - Flow
    Affects Versions: 1.6.0
         Environment: 4 node cluster
            Reporter: Khurram Faraaz


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)

Reply via email to