LAG() in combination with IGNORE NULLS may return incorrect results

Detail name	Value
Changelog Number	14568
Type	Bug
Status	Resolved
Affected Versions	Exasol 7.0.0, Exasol 7.1.0
Fix Versions	Exasol 8.0.0, Exasol 7.1.10, Exasol 7.0.19
Resolution Date	2022-04-20

Description

Queries using the analytic function LAG() in combination with IGNORE NULLS may return incorrect results.

Preparation

create table emp (empno decimal,empname varchar(20),job varchar(20),sal decimal);

insert into emp values(
(   1  ,       'JOHN',       'CLERK',      null ),       
(   2  ,       'ERIC',       'CLERK',      950  ),     
(   3  ,       'KURT',       'CLERK',      null ),   
(   6  ,       'JULIE',      'CLERK',      1300 )
);

Example

SELECT empno, empname, job, sal, 
       LAG(sal, 1,0) IGNORE NULLS 
       OVER (ORDER BY empno) AS sal_prev 
FROM   emp
ORDER BY job, empno;

Workaround

Rewrite the query by using LEAD() with reverted order instead

SELECT empno, empname, job, sal, 
       LEAD(sal, 1, 0) IGNORE NULLS 
       OVER (ORDER BY empno desc) AS sal_prev
FROM   emp
ORDER BY job, empno;

Fix

In those scenarios the correct results are returned.