[DRILL-3360] Window function defined within another window function

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.1.0
• Fix Version/s: 1.2.0
• Component/s: Query Planning & Optimization
• Labels: window_function

Environment

CentOS 4 node cluster

• Assignee: Sean Hsuan-Yi Chu
• Reporter: Khurram Faraaz
• Created: 24/Jun/15 20:43
• Updated: 11/Sep/15 18:45
• Resolved: 11/Sep/15 16:25

Description

Window function defined within another window function, Postgres 9.3 does not support this, Drill supports it and we see results being returned. We should not support this kind of query.

From Postgres 9.3

postgres=# select rank() over(order by row_number() over(order by col_int)) from vwOnParq_wCst;
ERROR:  window functions are not allowed in window definitions
LINE 1: select rank() over(order by row_number() over(order by col_i...

From execution on Drill

0: jdbc:drill:schema=dfs.tmp> select rank() over(order by row_number() over(order by col_int)) from vwOnParq_wCst;
+---------+
| EXPR$0  |
+---------+
| 1       |
| 2       |
| 3       |
| 4       |
| 5       |
| 6       |
| 7       |
| 8       |
| 9       |
| 10      |
| 11      |
| 12      |
| 13      |
| 14      |
| 15      |
| 16      |
| 17      |
| 18      |
| 19      |
| 20      |
| 21      |
| 22      |
| 23      |
| 24      |
| 25      |
| 26      |
| 27      |
| 28      |
| 29      |
| 30      |
+---------+
30 rows selected (0.377 seconds)