Analytic function NTH_VALUE returns internal server error

Detail name	Value
Changelog Number	19724
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

NTH_VALUE analytic function returns an internal server error when:

• provided nth_value is not a constant
• ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW window is specified
• Either IGNORE NULLS or FROM LAST RESPECT NULLS is specified

Preparation

create or replace table T(x int);
insert into T values (1);
insert into T values (2);
insert into T values (3);

create or replace table offsets(offset int);
insert into offsets values 1;

Examples

-- Expected: 1 for every x 
-- Observed: Internal Server Error
SELECT x, NTH_VALUE(x,offset IGNORE NULLS) OVER(ORDER BY x ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM T, offsets;

-- Expected: 1 for x=1, 2 for x=2, 3 for x=3
-- Observed: Internal Server Error
SELECT x, NTH_VALUE(x,offset FROM LAST RESPECT NULLS) OVER(ORDER BY x ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM T, offsets;

Workaround

If the query contains IGNORE_NULLS option, there is no workaround. If the query contains FROM LAST RESPECT NULLS option, the workaround is to use LAG instead.

SELECT x, LAG(x, offset - 1) over(ORDER BY x ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM T, offsets;

Fix

The queries run as expected.