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

["Khurram Faraaz (JIRA)" <ji...@apache.org>]

Khurram Faraaz created DRILL-4431:
-------------------------------------

             Summary: NTILE function should NOT allow the use of frame clause in its window definition
                 Key: DRILL-4431
                 URL: https://issues.apache.org/jira/browse/DRILL-4431
             Project: Apache Drill
          Issue Type: Bug
          Components: Query Planning & Optimization
    Affects Versions: 1.6.0
            Reporter: Khurram Faraaz


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.
{noformat}
>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. 
{noformat}

{noformat}
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)
{noformat}



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)