[ https://issues.apache.org/jira/browse/CALCITE-3402?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16956606#comment-16956606 ]
Julian Hyde commented on CALCITE-3402: -------------------------------------- Only allowing a single key makes a lot of sense. If {{i}} is an INTEGER column, you can have {{ORDER BY i RANGE BETWEEN 3 PRECEDING and 5 FOLLOWING}}. The range is an INTEGER expression. Why? Because when you subtract an INTEGER from an INTEGER, the result is an INTEGER. If {{t}} is a TIMESTAMP column, you can have {{ORDER BY t RANGE BETWEEN INTERVAL '3' DAY PRECEDING and INTERVAL '5' DAY FOLLOWING}}. Why? Because when you subtract a TIMESTAMP from a TIMESTAMP, the result is an INTERVAL. If you have {{x}} and {{y}} are columns of any type, you can't have {{ORDER BY x, y RANGE BETWEEN anything AND anything}}, because you can't subtract (x1, y1) from (x2, y2). (By the way, I'm pretty sure that PostgreSQL does allow {{ORDER BY t RANGE BETWEEN INTERVAL '3' DAY PRECEDING and INTERVAL '5' DAY FOLLOWING}}, despite your assertion that it does not.) > 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)