[DRILL-4431] NTILE function should NOT allow the use of frame clause in its window definition

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 1.6.0
• Fix Version/s: Future
• Component/s: Query Planning & Optimization
• Labels: window_function
• Assignee: Unassigned
• Reporter: Khurram Faraaz
• Created: 24/Feb/16 17:03
• Updated: 24/Jun/16 21:50

Description

NTILE function should not allow the use of frame clause in its window definition, the below query should return and error and say the operation is not supported.

Drill 1.6.0, commit ID: 6d5f4983

The SQL spec says NTILE should not allow use of frame clause.

From the SQL SPEC on page 220
ISO/IEC 9075-2:2011(E)
6.10

7)

<window function>

If <ntile function>, <lead or lag function>, <rank function type> or ROW_NUMBER is specified, then:

a)  If <ntile function>, <lead or lag function>, RANK or DENSE_RANK is specified, then the window

ordering clause WOC of WDX shall be present.

b)  The window framing clause of WDX shall not be present. 

0: jdbc:drill:schema=dfs.tmp> select NTILE(3) OVER(PARTITION BY CAST(columns[0] as integer) ORDER BY cast(columns[0] as integer) ROWS UNBOUNDED PRECEDING) from dfs.tmp.`t_alltype.csv`;
+---------+
| EXPR$0  |
+---------+
| 1       |
| 1       |
| 1       |
...
...
| 1      |
| 1      |
| 1      |
+--------+
145 rows selected (0.322 seconds)