[jira] [Created] (DRILL-3325) Explicitly specified default window frame throws an error requiring order by

["Victoria Markman (JIRA)" <ji...@apache.org>]

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)