You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@drill.apache.org by "Khurram Faraaz (JIRA)" <ji...@apache.org> on 2016/02/24 13:58:18 UTC
[jira] [Commented] (DRILL-3325) Explicitly specified default window
frame throws an error requiring order by
[ 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)