[jira] [Updated] (DRILL-4463) Frame clause in window definition without PARTITION BY and ORDER BY results in validation error

2016-03-07 Thread Deneche A. Hakim (JIRA) 

 [ 
https://issues.apache.org/jira/browse/DRILL-4463?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Deneche A. Hakim updated DRILL-4463:

Fix Version/s: Future

> 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)

[jira] [Updated] (DRILL-4463) Frame clause in window definition without PARTITION BY and ORDER BY results in validation error

2016-03-07 Thread Deneche A. Hakim (JIRA) 

 [ 
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)

[jira] [Updated] (DRILL-4463) Frame clause in window definition without PARTITION BY and ORDER BY results in validation error

2016-03-07 Thread Deneche A. Hakim (JIRA) 

 [ 
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)