[CALCITE-878] Allow ORDER BY within COLLECT (an Oracle extension)

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Assignee: Unassigned
• Reporter: Julian Hyde
• Created: 07/Sep/15 18:34
• Updated: 01/Feb/21 18:45

Description

Oracle allows ORDER BY within the COLLECT aggregate function, e.g.

SELECT deptno, COLLECT(emp_type(ename, sal) ORDER BY sal DESC) emps
FROM emp
GROUP BY deptno

This is an Oracle-specific extension to SQL:2011 but nevertheless useful and worth considering.

Note that Calcite already supports DISTINCT within COLLECT.

Issue Links

• is related to: CALCITE-2224 WITHIN GROUP clause for aggregate functions
• relates to: CALCITE-1473 Ordered-set and hypothetical-set aggregate functions