[DRILL-3651] Window function should not be allowed in order by clause of over clause of window function

• Type: Bug
• Status: Open
• Priority: Minor
• Resolution: Unresolved
• Fix Version/s: Future
• Component/s: Query Planning & Optimization
• Labels: window_function
• Assignee: Unassigned
• Reporter: Victoria Markman
• Created: 14/Aug/15 19:31
• Updated: 01/Oct/15 16:36

Description

Should not parse and throw an error according to SQL standard.
"ISO/IEC 9075-2:2011(E) 7.11 <window clause>"
d) If WDX has a window ordering clause, then WDEF shall not specify <window order clause> (hope I'm reading it correctly)

SELECT rank() OVER (ORDER BY rank() OVER (ORDER BY c1)) from t1;

Instead, drill returns result:

0: jdbc:drill:schema=dfs> SELECT rank() OVER (ORDER BY rank() OVER (ORDER BY c1)) from t1;
+---------+
| EXPR$0  |
+---------+
| 1       |
| 2       |
| 3       |
| 4       |
| 5       |
| 6       |
| 7       |
| 8       |
| 9       |
| 10      |
+---------+
10 rows selected (0.336 seconds)

Postgres throws an error in this case:

postgres=# SELECT rank() OVER (ORDER BY rank() OVER (ORDER BY c1)) from t1;
ERROR:  window functions are not allowed in window definitions
LINE 1: SELECT rank() OVER (ORDER BY rank() OVER (ORDER BY c1)) from...

Courtesy of postgres test suite.