Victoria Markman created DRILL-3325: ---------------------------------------
Summary: Explicitly specified default window frame throws an error requiring order by Key: DRILL-3325 URL: https://issues.apache.org/jira/browse/DRILL-3325 Project: Apache Drill Issue Type: Bug Components: Query Planning & Optimization Affects Versions: 1.0.0 Reporter: Victoria Markman Assignee: Jinfeng Ni Calcite requires explicit ORDER BY clause when "default" frame ("UNBOUNDED PRECEDING AND CURRENT ROW") is specified: {code} 0: jdbc:drill:schema=dfs> select sum(a1) over(partition by b1 range between unbounded preceding and unbounded following ) from t1; Error: PARSE ERROR: From line 1, column 20 to line 1, column 95: Window specification must contain an ORDER BY clause [Error Id: 446c7fd3-f588-4832-b79d-08cec610ff24 on atsqa4-133.qa.lab:31010] (state=,code=0) {code} I thought that we decided to make query above to be equivalent to: {code} 0: jdbc:drill:schema=dfs> explain plan for select sum(a1) over(partition by b1) from t1; {code} Explain plan (notice frame and an empty "order by"): {code} | 00-00 Screen 00-01 Project(EXPR$0=[CASE(>($2, 0), CAST($3):ANY, null)]) 00-02 Window(window#0=[window(partition {1} order by [] range between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING aggs [COUNT($0), $SUM0($0)])]) 00-03 SelectionVectorRemover 00-04 Sort(sort0=[$1], dir0=[ASC]) 00-05 Project(a1=[$1], b1=[$0]) 00-06 Scan(groupscan=[ParquetGroupScan [entries=[ReadEntryWithPath [path=maprfs:///drill/testdata/subqueries/t1]], selectionRoot=/drill/testdata/subqueries/t1, numFiles=1, columns=[`a1`, `b1`]]]) {code} If I add order by, I get an error. Because based on our design, see DRILL-3188, order by only allows frames: "RANGE UNBOUNDED PRECEDING" and "RANGE BETEWEEN UNBOUNDED PRECEDING AND CURRENT ROW" {code} 0: jdbc:drill:schema=dfs> select sum(a1) over(partition by b1 order by c1 range between unbounded preceding and unbounded following ) from t1; Error: UNSUPPORTED_OPERATION ERROR: This type of window frame is currently not supported See Apache Drill JIRA: DRILL-3188 [Error Id: 7b2f1e39-0ad2-4584-aa4c-bdace84adfe4 on atsqa4-133.qa.lab:31010] (state=,code=0) {code} "{color:red}ROWS{color} BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING" works: {code} 0: jdbc:drill:schema=dfs> select sum(a1) over(partition by b1 rows between unbounded preceding and unbounded following ) from t1; +---------+ | EXPR$0 | +---------+ | 1 | | 2 | | 3 | | 5 | | 6 | | 7 | | null | | 9 | | 10 | | 4 | +---------+ 10 rows selected (0.312 seconds) {code} explain plan: notice empty "order by" as well. {code} 00-01 Project(EXPR$0=[CASE(>($2, 0), CAST($3):ANY, null)]) 00-02 Window(window#0=[window(partition {1} order by [] rows between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING aggs [COUNT($0), $SUM0($0)])]) 00-03 SelectionVectorRemover 00-04 Sort(sort0=[$1], dir0=[ASC]) 00-05 Project(a1=[$1], b1=[$0]) 00-06 Scan(groupscan=[ParquetGroupScan [entries=[ReadEntryWithPath [path=maprfs:///drill/testdata/subqueries/t1]], selectionRoot=/drill/testdata/subqueries/t1, numFiles=1, columns=[`a1`, `b1`]]]) {code} -- This message was sent by Atlassian JIRA (v6.3.4#6332)