[
https://issues.apache.org/jira/browse/CALCITE-3402?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16957656#comment-16957656
]
benj edited comment on CALCITE-3402 at 10/23/19 3:26 PM:
---------------------------------------------------------
>From Apache Drill (1.16 + latest master), the only working ROWS clause is
>"ROWS BETWEEN UNBOUNDED PRECEDING AND current row", but yes with this clause
>it's possible to have compound ORDER BY
{code:sql}
/* ROWS is not possible with "BETWEEN x PRECEDING AND y FOLLOWING" (RANGE
either) */
apache drill> SELECT *, sum(a) OVER(ORDER BY b ROWS BETWEEN 1 PRECEDING AND 1
FOLLOWING) FROM (SELECT 1 a, 1 b, 1 c);
Error: UNSUPPORTED_OPERATION ERROR: This type of window frame is currently not
supported
See Apache Drill JIRA: DRILL-3188
/* ROWS is not possible with "BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED
FOLLOWING" (it's possible with RANGE but with single ORDER BY only ) */
apache drill> SELECT *, sum(a) OVER(ORDER BY b ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING) FROM (SELECT 1 a, 1 b, 1 c);
Error: UNSUPPORTED_OPERATION ERROR: This type of window frame is currently not
supported
See Apache Drill JIRA: DRILL-3188
/* ROWS is possible with "BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW" */
apache drill> SELECT *, sum(a) OVER(ORDER BY b ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW) FROM (SELECT 1 a, 1 b, 1 c);
+---+---+---+--------+
| a | b | c | EXPR$3 |
+---+---+---+--------+
| 1 | 1 | 1 | 1 |
+---+---+---+--------+
1 row selected (1.357 seconds)
/* ROWS followed by "BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW" is possible
with compound ORDER BY */
apache drill> SELECT *, sum(a) OVER(ORDER BY b, c ROWS BETWEEN UNBOUNDED
PRECEDING AND CURRENT ROW) FROM (SELECT 1 a, 1 b, 1 c);
+---+---+---+--------+
| a | b | c | EXPR$3 |
+---+---+---+--------+
| 1 | 1 | 1 | 1 |
+---+---+---+--------+
1 row selected (0.145 seconds)
{code}
And effectively it's not the same behaviour from Apache Calcite
{code:sql}
0: jdbc:calcite:model=target/test-classes/mod> SELECT *, sum(a) OVER(ORDER BY b
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) FROM (SELECT 1 a, 1 b, 1 c);
+---+---+---+--------+
| A | B | C | EXPR$3 |
+---+---+---+--------+
| 1 | 1 | 1 | 1 |
+---+---+---+--------+
1 row selected (0.053 seconds)
0: jdbc:calcite:model=target/test-classes/mod> SELECT *, sum(a) OVER(ORDER BY b
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM (SELECT 1 a, 1
b, 1 c);
+---+---+---+--------+
| A | B | C | EXPR$3 |
+---+---+---+--------+
| 1 | 1 | 1 | 1 |
+---+---+---+--------+
1 row selected (0.034 seconds)
{code}
So, the problem is not from Apache Calcite but only in Apache Drill side.
was (Author: benj641):
Only try from Apache Drill (1.16 + latest master), the only working ROWS clause
is "ROWS BETWEEN UNBOUNDED PRECEDING AND current row", but yes with this clause
it's possible to have compound ORDER BY
{code:sql}
/* ROWS is not possible with "BETWEEN x PRECEDING AND y FOLLOWING" (RANGE
either) */
apache drill> SELECT *, sum(a) OVER(ORDER BY b ROWS BETWEEN 1 PRECEDING AND 1
FOLLOWING) FROM (SELECT 1 a, 1 b, 1 c);
Error: UNSUPPORTED_OPERATION ERROR: This type of window frame is currently not
supported
See Apache Drill JIRA: DRILL-3188
/* ROWS is not possible with "BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED
FOLLOWING" (it's possible with RANGE but with single ORDER BY only ) */
apache drill> SELECT *, sum(a) OVER(ORDER BY b ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING) FROM (SELECT 1 a, 1 b, 1 c);
Error: UNSUPPORTED_OPERATION ERROR: This type of window frame is currently not
supported
See Apache Drill JIRA: DRILL-3188
/* ROWS is possible with "BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW" */
apache drill> SELECT *, sum(a) OVER(ORDER BY b ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW) FROM (SELECT 1 a, 1 b, 1 c);
+---+---+---+--------+
| a | b | c | EXPR$3 |
+---+---+---+--------+
| 1 | 1 | 1 | 1 |
+---+---+---+--------+
1 row selected (1.357 seconds)
/* ROWS followed by "BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW" is possible
with compound ORDER BY */
apache drill> SELECT *, sum(a) OVER(ORDER BY b, c ROWS BETWEEN UNBOUNDED
PRECEDING AND CURRENT ROW) FROM (SELECT 1 a, 1 b, 1 c);
+---+---+---+--------+
| a | b | c | EXPR$3 |
+---+---+---+--------+
| 1 | 1 | 1 | 1 |
+---+---+---+--------+
1 row selected (0.145 seconds)
{code}
But maybe the problem is not from Apache Calcite and only in Apache Drill side.
> Allow RANGE with compoud ORDER BY clause
> ----------------------------------------
>
> Key: CALCITE-3402
> URL: https://issues.apache.org/jira/browse/CALCITE-3402
> Project: Calcite
> Issue Type: Improvement
> Components: core
> Affects Versions: 1.18.0, 1.19.0
> Reporter: benj
> Priority: Major
>
> It will be very useful to have the capacity to use compound ORDER BY clause
> with RANGE
> {code:sql}
> apache drill (dfs.tmp)> SELECT a
> , last_value(c) OVER(PARTITION BY a ORDER BY c, b DESC RANGE BETWEEN
> UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
> FROM (SELECT 1 a, 'b' b, 3 c
> UNION SELECT 2, 'c', 4
> UNION SELECT 1, 'c', 4
> /* UNION ... */
> ) x;
> Error: VALIDATION ERROR: From line 2, column 56 to line 2, column 60: RANGE
> clause cannot be used with compound ORDER BY clause
> {code}
> I know it's possible (for last_value) to rewrite with first_value with an
> reverse ORDER BY and without RANGE to obtain correct result.
> But it will become sometimes less readable and request write from other SGBDR
> will not be compatible and should be rewrite, and for some other function
> than last_value, the problem will not be solved like that.
> compound ORDER BY clause with RANGE is possible with some SGBDR like
> Postgres:
> [https://www.postgresql.org/docs/9.3/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS]
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
