[ https://issues.apache.org/jira/browse/DRILL-3325?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15162937#comment-15162937 ]
Khurram Faraaz commented on DRILL-3325: --------------------------------------- This case (RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) is fixed on Drill 1.6.0 commit ID: 6d5f4983 {noformat} 0: jdbc:drill:schema=dfs.tmp> select count(*) OVER(PARTITION BY CAST(columns[0] as integer) ORDER BY cast(columns[0] as integer) RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) from dfs.tmp.`t_alltype.csv`; +---------+ | EXPR$0 | +---------+ | 1 | | 1 | | 1 | | 1 | | 1 | | 1 | | 1 | ... | 1 | | 1 | | 1 | +--------+ 145 rows selected (0.299 seconds) {noformat} > 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 > Labels: window_function > Fix For: Future > > > 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)