LAST_VALUE/FIRST_VALUE/NTH_VALUE use inconsistent order for empty OVER()

Detail name	Value
Changelog Number	12225
Type	Bug
Status	Resolved
Affected Versions	Exasol 6.2.0, Exasol 7.0.0
Fix Versions	Exasol 6.2.16, Exasol 7.0.12, Exasol 7.1.1
Resolution Date	2021-08-05

Description

Although an empty OVER() clause does not define any specific order, the same order must be used for all analytic functions using an empty OVER() clause within the same query. Since the order is nondeterministic, values may differ between query executions.

If two or more analytic functions of type LAST_VALUE/FIRST_VALUE/NTH_VALUE appear in the same query and the corresponding functions use an empty OVER() clause, the query returns wrong results.
Example:

SELECT
  FIRST_VALUE(a) OVER (),
  LAST_VALUE(a) OVER ()
FROM (VALUES (1), (2), (3), (4)) AS t(a);

Workaround

Instead of an empty OVER(), it is possible to use this window clause:

OVER(ORDER BY (SELECT 1) ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

SELECT  FIRST_VALUE(a) OVER (ORDER BY (SELECT 1) ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),
  LAST_VALUE(a) OVER (ORDER BY (SELECT 1) ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
FROM (VALUES (1), (2), (3), (4)) AS t(a) ;

Fix

Exasol uses a consistent order for all analytic functions with an empty OVER() clause within a query.
Still, the results are nondeterministic and may vary between query executions.