[
https://issues.apache.org/jira/browse/CALCITE-3402?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16954409#comment-16954409
]
benj commented on CALCITE-3402:
-------------------------------
"SQL03 7.10 Rule 11a" is probably a reference of the SQL 2003 standard §7.10
please find a link to a sql 2003 standard here:
[http://grigoras.perso.enseeiht.fr/ens/bd/5WD-02-Foundation-2003-09.pdf]
Note that I think in this version it's more probably § *7.11* Rulle 11a
(because 7.10 is for HAVING clause and 7.11 for WINDOW clause)
{noformat}
11)If WDEF's <window frame clause> specifies <window frame preceding> or
<window frame following>,then let UVS be the <unsigned value specification>
simply contained in the <window frame preceding> or<window frame following>.
Case:
a)If RANGE is specified, then WDEF's <window order clause> shall contain a
single <sort key>SK.The declared type of SK shall be numeric, datetime, or
interval. The declared type of UVS shall benumeric if the declared type of SK
is numeric; otherwise, it shall be an interval type that may be addedto or
subtracted from the declared type of SK according to the Syntax Rules of
Subclause 6.30,“<datetime value expression>”, and Subclause 6.32, “<interval
value expression>”, in this part ofISO/IEC 9075.
b)If ROWS is specified, then the declared type of UVS shall be exact numeric
with scale 0 (zero).
{noformat}
> 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)
