[DRILL-3188] Restrict the types of window frames that can be specified

• Type: Bug
• Status: Closed
• Priority: Critical
• Resolution: Fixed
• Affects Version/s: 1.0.0
• Fix Version/s: 1.1.0
• Component/s: Query Planning & Optimization
• Labels: window_function
• Assignee: Sean Hsuan-Yi Chu
• Reporter: Victoria Markman
• Created: 26/May/15 20:23
• Updated: 10/Sep/15 17:40
• Resolved: 16/Jun/15 00:47

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)