Wrong results for 4 SUM functions of type DOUBLE in select list

Detail name	Value
Changelog Number	9150
Type	Bug
Status	Resolved
Affected Versions	Exasol 6.2.0
Fix Versions	Exasol 6.2.3
Resolution Date	2019-11-27

Problem

Exasol optimizes queries that contain several set functions. Under certain conditions this leads to wrong results
Conditions:

• There are exactly 4 SUM set functions in a single select list.
• All 4 SUM set functions have arguments of type DOUBLE.
• The 4th set function contains NULL values.

Example:

CREATE SCHEMA TEST;
CREATE OR REPLACE TABLE t1(groupkey int, col1 double precision, col2 double precision, col3 double precision, col4 double precision);
INSERT INTO t1 VALUES (1,1,1,1,1);
INSERT INTO t1 VALUES (1,2,2,2,2);
INSERT INTO t1 VALUES (1,3,3,3,NULL);

SELECT SUM(col1), SUM(col2), SUM(col3), SUM(col4) FROM t1 group by groupkey;

Workaround

Depends on the query. Some possibilities:

• use 3 or 5 SUM functions
• cast at least one argument to DECIMAL(x, y)
• split the SELECT (see example below)

Example:

WITH
    tmp1 AS
    (
        SELECT groupkey, SUM(col1) col1, SUM(col2) col2 FROM t1 GROUP BY groupkey
    ),
    tmp2 AS
    (
        SELECT groupkey, SUM(col3) col3, SUM(col4) col4 FROM t1 GROUP BY groupkey
    )
SELECT tmp1.col1, tmp1.col2, tmp2.col3, tmp2.col4 FROM tmp1 JOIN tmp2 ON tmp1.groupkey=tmp2.groupkey;