You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@drill.apache.org by "Victoria Markman (JIRA)" <ji...@apache.org> on 2015/06/30 01:09:06 UTC
[jira] [Commented] (DRILL-3188) Restrict the types of window frames
that can be specified
[ https://issues.apache.org/jira/browse/DRILL-3188?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14606621#comment-14606621 ]
Victoria Markman commented on DRILL-3188:
-----------------------------------------
Verified fixed in 1.1
#Mon Jun 29 17:55:24 EDT 2015
git.commit.id.abbrev=e923ac5
Following bugs were opened as a follow up:
DRILL-3325 - Explicitly specified default window frame throws an error requiring order by
DRILL-3409 - Specifying default frame explicitly results in an error
DRILL-3359 - Drill should throw and error when window function defined using WINDOW AS uses ROWS UNBOUNDED PRECEDING
> Restrict the types of window frames that can be specified
> ---------------------------------------------------------
>
> Key: DRILL-3188
> URL: https://issues.apache.org/jira/browse/DRILL-3188
> Project: Apache Drill
> Issue Type: Bug
> Components: Query Planning & Optimization
> Affects Versions: 1.0.0
> Reporter: Victoria Markman
> Assignee: Sean Hsuan-Yi Chu
> Priority: Critical
> Labels: window_function
> Fix For: 1.1.0
>
>
> 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:
> {code}
> 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`]]])
> {code}
> Row range should be disabled as well:
> {code}
> 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)
> {code}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)