Analytical functions FIRST_VALUE, LAST_VALUE and NTH_VALUE do not consider IGNORE NULLS in combination with ORDER BY

Detail name	Value
Changelog Number	12113
Type	Bug
Status	Open
Affected Versions	Exasol 6.2.0, Exasol 7.0.0

Background

The FIRST_VALUE, LAST_VALUE and NTH_VALUE as analytical functions, allow defining IGNORE NULLS .

Description

These analytical functions do not consider IGNORE NULLS contained in the expression, when OVER clause contains an ORDER BY.

Examples

The following example shows this behavior of FIRST_VALUE, but this also applies to LAST_VALUE and NTH_VALUE.

Preparation:

create or replace table t (c1 int, c2 varchar(30), c3 int); 
insert into t 
    values (1, null,     1), 
           (1, 'text 1', 2), 
           (2, 'text 2', 1), 
           (2, null,     2);

Wrong behavior with ORDER BY

The following query contains NULLS in its result set even though IGNORE NULLS should have prevented that:

select c1, first_value(c2) ignore nulls over (partition by c1 order by c3) as c2_first_order, c3 from t;

This can be seen in the following result set:

c1	c2_first_order	c3
1	(null)	1
1	text 1	2
2	text 2	1
2	text 2	2

Correct behavior without ORDER BY

The following query does not contain NULLS in its result set, as expected:

select c1, first_value(c2) ignore nulls over (partition by c1) as c2_first_no_order, c3 from t;

This can be seen in the following result set:

c1	c2_first_no_order	c3
1	text 1	1
1	text 1	2
2	text 2	1
2	text 2	2

Workaround

There is no workaround.

Fix

The FIRST_VALUE, LAST_VALUE and NTH_VALUE as analytical function consider IGNORE NULLS.