first_value/last_value may cause internal server error

Detail name	Value
Changelog Number	15702
Type	Bug
Status	Resolved
Affected Versions	Exasol 7.1.0
Fix Versions	Exasol 7.1.16, Exasol 8.9.0
Resolution Date	2022-11-24

Description

An internal server error may be thrown if the following conditions are met

• aggregation over large numeric data type (e.g. expression type is larger as DECIMAL(18,0) )
• affected functions: first_value, last_value
• join condition is on a string data type (CHAR, VARCHAR)
• not all nodes have data for aggregation (e.g. due application of a filter condition)
• left join is affected if table on left side doesn't have the data on all nodes, (same rule applies also to right join and right table)

Preparation

create table a(x char(1), y decimal(19,0));
insert into a values ('a',10);

create table b(z char(1));
insert into b values ('a');
insert into b values ('a');

Example

select first_value(y)  
from a
left join b on a.x=b.z;

 

Workaround

Enforce materialization before aggregation with "order by false"

select first_value(y)  
from 
   (select * 
    from a 
    left join b on a.x=b.z 
    order by false);

Fix

Those queries succeed without error.