[
https://issues.apache.org/jira/browse/CALCITE-3402?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
benj updated CALCITE-3402:
--------------------------
Description:
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]
was:
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}
This is possible with some SGBDR like Postgres:
[https://www.postgresql.org/docs/9.3/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS]
> 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)
