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

Restrict the types of window frames that can be specified

    XMLWordPrintableJSON

    Details

      Description

      We don't support row range with window functions. So we should disable this functionality, because currently we return default frame result.
      The only frame we currently support is BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

      If you don't specify frame in Calcite, this is exactly what you get:

      0: jdbc:drill:schema=dfs> explain plan for select a2,b2,c2, cast(count(*) over(partition by a2, substr(b2,1,2),c2 order by cast(a2 as double) + 100 ) as bigint) from t2 order by a2;
      +------+------+
      | text | json |
      +------+------+
      | 00-00    Screen
      00-01      Project(a2=[$0], b2=[$1], c2=[$2], EXPR$3=[$3])
      00-02        SelectionVectorRemover
      00-03          Sort(sort0=[$0], dir0=[ASC])
      00-04            Project(a2=[$0], b2=[$1], c2=[$2], EXPR$3=[$5])
      00-05              Window(window#0=[window(partition {0, 2, 4} order by [3] range between UNBOUNDED PRECEDING and CURRENT ROW aggs [COUNT()])])
      00-06                SelectionVectorRemover
      00-07                  Sort(sort0=[$0], sort1=[$2], sort2=[$4], sort3=[$3], dir0=[ASC], dir1=[ASC], dir2=[ASC], dir3=[ASC])
      00-08                    Project(a2=[$1], b2=[$0], c2=[$2], $3=[+(CAST($1):DOUBLE, 100)], $4=[SUBSTR($0, 1, 2)])
      00-09                      Scan(groupscan=[ParquetGroupScan [entries=[ReadEntryWithPath [path=maprfs:///drill/testdata/aggregation/t2]], selectionRoot=/drill/testdata/aggregation/t2, numFiles=1, columns=[`a2`, `b2`, `c2`]]])
      

      Row range should be disabled as well:

      0: jdbc:drill:schema=dfs> select * from t2;
      +-----+--------+-------------+
      | a2  |   b2   |     c2      |
      +-----+--------+-------------+
      | 0   | zzz    | 2014-12-31  |
      | 1   | aaaaa  | 2015-01-01  |
      | 2   | bbbbb  | 2015-01-02  |
      | 2   | bbbbb  | 2015-01-02  |
      | 2   | bbbbb  | 2015-01-02  |
      | 3   | ccccc  | 2015-01-03  |
      | 4   | ddddd  | 2015-01-04  |
      | 5   | eeeee  | 2015-01-05  |
      | 6   | fffff  | 2015-01-06  |
      | 7   | ggggg  | 2015-01-07  |
      | 7   | ggggg  | 2015-01-07  |
      | 8   | hhhhh  | 2015-01-08  |
      | 9   | iiiii  | 2015-01-09  |
      +-----+--------+-------------+
      13 rows selected (0.123 seconds)
      
      0: jdbc:drill:schema=dfs> select a2, sum(a2) over(partition by a2 order by a2 rows between 1 preceding and 1 following ) from t2 order by a2;
      +-----+---------+
      | a2  | EXPR$1  |
      +-----+---------+
      | 0   | 0       |
      | 1   | 1       |
      | 2   | 6       |
      | 2   | 6       |
      | 2   | 6       |
      | 3   | 3       |
      | 4   | 4       |
      | 5   | 5       |
      | 6   | 6       |
      | 7   | 14      |
      | 7   | 14      |
      | 8   | 8       |
      | 9   | 9       |
      +-----+---------+
      13 rows selected (0.2 seconds)
      

        Attachments

          Activity

            People

            • Assignee:
              seanhychu Sean Hsuan-Yi Chu
              Reporter:
              vicky Victoria Markman
            • Votes:
              0 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: