Wrong results for queries using Analytic Functions with RANGE based windows and descending order

Details

Detail name Value
Changelog Number 8380
Type Bug
Status Resolved
Affected Versions Exasol 6.2.0, Exasol 6.2.rc1
Fix Versions Exasol 6.2.1
Resolution Date 2019-07-25

Bug

Queries that satisfy following conditions, return wrong results for partitions with single rows. 

  • Use Analytic Functions
  • Use windows based on RANGE
  • Sorting order is descending
  • <value> preceding or <value> following is used in either or both of lower and upper bounds in the window frame clause.

This query returns NULLS instead of correct result for partitions with a single row.

How to reproduce
The following query produces NULLs instead of correct result. 

CREATE SCHEMA TEST;
CREATE TABLE t1 (p INT,  o INT, a INT) ;
INSERT INTO t1 VALUES (1, 1, 1), (2, 1, 2), (3, 1, 3);
SELECT  MAX(a) OVER ( PARTITION BY p ORDER BY o DESC RANGE BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING ) FROM t1;

Workaround
There is no workaround for this bug.