Uploaded image for project: 'Apache Drill'
  1. Apache Drill
  2. DRILL-4431

NTILE function should NOT allow the use of frame clause in its window definition

    XMLWordPrintableJSON

Details

    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)
      

      Attachments

        Activity

          People

            Unassigned Unassigned
            khfaraaz Khurram Faraaz
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

              Created:
              Updated: