Analytic function with two arguments crashes if one is an expression of non-numeric columns

Detail name	Value
Changelog Number	10496
Type	Bug
Status	Resolved
Affected Versions	Exasol 6.2.0, Exasol 7.0.0
Fix Versions	Exasol 7.0.3, Exasol 6.2.11
Resolution Date	2020-09-25

Description

If an analytic function contains more than one argument, and one of those arguments contains a non-numeric column in an expression, the query will result in an internal server error. The following call of the analytic function leads to an internal error:

create or replace table t(a int, b varchar(100));
insert into t values (1,1);
select covar_pop(a, b||b) over() from t;

Workaround

The affected analytic functions are mathematical functions that are composed of simpler mathematical functions, for example COVAR_POP(x,y) is

(SUM(x*y) - SUM(x) * SUM(y) / COUNT(*)) / COUNT(*)

This means the function can be rewritten manually:

-- select covar_pop(a, b||b) over() from t; is equal to
select (SUM(a*b||b) over () - SUM(a) over () * SUM(b||b) over () / COUNT(*) over ()) / COUNT(*) over () from t;

You can rewrite the analytic function using it's broken-down form, like shown above.

Fix

The above analytic functions will no longer return an internal server error.