Named window references allow duplicate ORDER BY, PARTITION BY, or window frame clauses

Detail name	Value
Changelog Number	10618
Type	Bug
Status	Resolved
Affected Versions	Exasol 6.2.8
Fix Versions	Exasol 7.0.0
Resolution Date	2020-09-11

Description

It is currently possible to reference a named window and add ORDER BY, PARTITION BY, or window frame clauses even if the referenced named window already contains the specified clauses. 

Fix

In accordance with the SQL standard, named windows allow only one partition_clause, one order_clause, and one window_frame_clause, as described in our documentation. How this can be used is shown below:

SELECT
  id, department, hire_date, starting_salary,
  AVG(starting_salary) OVER w2 AVG,
  MIN(starting_salary) OVER w2 MIN_STARTING_SALARY,
  MAX(starting_salary) OVER (w1 ORDER BY hire_date)
FROM employee_table
WINDOW w1 as (PARTITION BY department), w2 as (w1 ORDER BY hire_date)
ORDER BY department, hire_date;

Changed behavior

Named window references are now resolved in accordance with the SQL standard, preventing duplicate ORDER BY, PARTITION BY, or window frame clauses.