[
https://issues.apache.org/jira/browse/DRILL-3238?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Victoria Markman reopened DRILL-3238:
-------------------------------------
Sean,
I just realized that test failed for me and query now returns wrong result.
Please see below:
-- wrong result caused by a wrong plan (notice two window operators)
{code}
0: jdbc:drill:schema=dfs> select
. . . . . . . . . . . . > a2,
. . . . . . . . . . . . > sum(a2) over (w),
. . . . . . . . . . . . > count(*) over w
. . . . . . . . . . . . > from
. . . . . . . . . . . . > t2
. . . . . . . . . . . . > window w as (partition by a2 order by a2);
+-----+---------+---------+
| a2 | EXPR$1 | EXPR$2 |
+-----+---------+---------+
| 0 | 0 | 1 |
| 1 | 1 | 1 |
| 2 | 7 | 3 |
| 2 | 7 | 3 |
| 2 | 7 | 3 |
| 3 | 10 | 1 |
| 4 | 14 | 1 |
| 5 | 19 | 1 |
| 6 | 25 | 1 |
| 7 | 39 | 2 |
| 7 | 39 | 2 |
| 8 | 47 | 1 |
| 9 | 56 | 1 |
+-----+---------+---------+
13 rows selected (0.305 seconds)
0: jdbc:drill:schema=dfs> explain plan for select a2, sum(a2) over(w), count(*)
over w from t2 window w as (partition by a2 order by a2);
+------+------+
| text | json |
+------+------+
| 00-00 Screen
00-01 Project(a2=[$0], EXPR$1=[$1], EXPR$2=[$2])
00-02 Window(window#0=[window(partition {0} order by [0] range between
UNBOUNDED PRECEDING and CURRENT ROW aggs [COUNT()])])
00-03 SelectionVectorRemover
00-04 Sort(sort0=[$0], sort1=[$0], dir0=[ASC], dir1=[ASC])
00-05 Window(window#0=[window(partition {} order by [0] range
between UNBOUNDED PRECEDING and CURRENT ROW aggs [SUM($0)])])
00-06 SelectionVectorRemover
00-07 Sort(sort0=[$0], dir0=[ASC])
00-08 Scan(groupscan=[ParquetGroupScan
[entries=[ReadEntryWithPath [path=maprfs:///drill/testdata/subqueries/t2]],
selectionRoot=maprfs:/drill/testdata/subqueries/t2, numFiles=1,
columns=[`a2`]]])
{code}
-- correct
{code}
0: jdbc:drill:schema=dfs> select a2, sum(a2) over (partition by a2 order by a2)
from t2;
+-----+---------+
| 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.303 seconds)
{code}
--correct
{code}
0: jdbc:drill:schema=dfs> select
. . . . . . . . . . . . > a2,
. . . . . . . . . . . . > sum(a2) over (partition by a2 order by a2),
. . . . . . . . . . . . > count(*) over(partition by a2 order by a2)
. . . . . . . . . . . . > from
. . . . . . . . . . . . > t2;
+-----+---------+---------+
| a2 | EXPR$1 | EXPR$2 |
+-----+---------+---------+
| 0 | 0 | 1 |
| 1 | 1 | 1 |
| 2 | 6 | 3 |
| 2 | 6 | 3 |
| 2 | 6 | 3 |
| 3 | 3 | 1 |
| 4 | 4 | 1 |
| 5 | 5 | 1 |
| 6 | 6 | 1 |
| 7 | 14 | 2 |
| 7 | 14 | 2 |
| 8 | 8 | 1 |
| 9 | 9 | 1 |
+-----+---------+---------+
13 rows selected (0.324 seconds)
{code}
-- correct
{code}
0: jdbc:drill:schema=dfs> select
. . . . . . . . . . . . > a2,
. . . . . . . . . . . . > sum(a2) over w,
. . . . . . . . . . . . > count(*) over w
. . . . . . . . . . . . > from
. . . . . . . . . . . . > t2
. . . . . . . . . . . . > window w as (partition by a2 order by a2);
+-----+---------+---------+
| a2 | EXPR$1 | EXPR$2 |
+-----+---------+---------+
| 0 | 0 | 1 |
| 1 | 1 | 1 |
| 2 | 6 | 3 |
| 2 | 6 | 3 |
| 2 | 6 | 3 |
| 3 | 3 | 1 |
| 4 | 4 | 1 |
| 5 | 5 | 1 |
| 6 | 6 | 1 |
| 7 | 14 | 2 |
| 7 | 14 | 2 |
| 8 | 8 | 1 |
| 9 | 9 | 1 |
+-----+---------+---------+
13 rows selected (0.29 seconds)
{code}
> Cannot Plan Exception is raised when the same window partition is defined in
> select & window clauses
> ----------------------------------------------------------------------------------------------------
>
> Key: DRILL-3238
> URL: https://issues.apache.org/jira/browse/DRILL-3238
> Project: Apache Drill
> Issue Type: Bug
> Components: Query Planning & Optimization
> Reporter: Sean Hsuan-Yi Chu
> Assignee: Sean Hsuan-Yi Chu
> Labels: window_function
> Fix For: 1.2.0
>
>
> While this works:
> {code}
> select sum(a2) over(partition by a2 order by a2), count(*) over(partition by
> a2 order by a2)
> from t
> {code}
> , this fails
> {code}
> select sum(a2) over(w), count(*) over(partition by a2 order by a2)
> from t
> window w as (partition by a2 order by a2)
> {code}
> Notice these two queries are logically the same thing if we plug-in the
> window definition back into the SELECT-CLAUSE in the 2nd query.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
