Identical analytic functions are computed multiple times

Detail name	Value
Changelog Number	10606
Type	Bug
Status	Resolved
Affected Versions	Exasol 6.2.8, Exasol 7.0.rc2
Fix Versions	Exasol 7.0.0, Exasol 6.2.9
Resolution Date	2020-08-19

Description

Exasol 6.2 introduced a refactoring of analytic functions.  Currently, Identical analytic functions that have a lower or upper bound with FOLLOWING or PRECEDING are computed multiple times, which can lead to worse performance. The following example illustrates this case:

create table ten as select * from (values 1,2,3,4,5,6,7,8,9,10) as t(x);
create or replace table T (a int, b int); insert into T select a.x + 10 * b.x + 100 * c.x + 1000 * d.x, MOD(a.x, 2) from ten a, ten b, ten c, ten d, ten, ten; 

select sum(a) over (partition by b order by a desc rows between 100000 preceding and 100000 following) c from t; -- ~27 seconds

select sum(a) over (partition by b order by a desc rows between 100000 preceding and 100000 following),
       sum(a) over (partition by b order by a desc rows between 100000 preceding and 100000 following)  from t; -- ~50 seconds 

Fix

Multiple analytic function calls that share a common window clause are now always computed only once. This means the above two queries will have approximately the same duration. 

Changed behavior

Identical analytic functions are now always computed only once.