[CALCITE-3402] Allow RANGE with compoud ORDER BY clause

• Type: Improvement
• Status: Closed
• Priority: Major
• Resolution: Not A Problem
• Affects Version/s: 1.18.0, 1.19.0
• Component/s: core
• Assignee: Danny Chen
• Reporter: benj
• Created: 11/Oct/19 15:16
• Updated: 25/Oct/19 08:24
• Resolved: 25/Oct/19 08:24

Description

It will be very useful to have the capacity to use compound ORDER BY clause with RANGE

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

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