Analytic function NTH_VALUE produces wrong results with exclude current row and ignore nulls

Detail name	Value
Changelog Number	19723
Type	Bug
Status	Resolved
Affected Versions	Exasol 7.1.0, Exasol 8.0.0
Fix Versions	Exasol 7.1.26, Exasol 8.26.0
Resolution Date	2024-03-15

Description

If the NTH_VALUE function is used with EXCLUDE CURRENT ROW and IGNORE NULLS options, it always returns NULL instead of the expected value.

Preparation

create or replace table t(x int, y int);
insert into t values (1, 1);
insert into t values (2, 2);
insert into t values (3, 3);

Example

-- Expected: 2 for x=1 and x=3, 1 for x=2
-- Observed: NULL for every x
select x, nth_value(y, 1 ignore nulls) over(order by x rows between 1 preceding and 1 following exclude current row) from t;

Workaround

There is no workaround.

Fix

The query produces correct results.