Wrong results for multiple sums involving Interval Day To Second type

Detail name	Value
Changelog Number	17801
Type	Bug
Status	Resolved
Affected Versions	Exasol 7.1.0, Exasol 8.0.0
Fix Versions	Exasol 7.1.23, Exasol 8.22.0
Resolution Date	2023-08-29

Description

A query may produce wrong results if the following conditions are met:

• There is more then one SUM set function in the select list.
• At least one of these SUM set functions uses an expression of type Day To Second.
• At least one of these SUM set functions uses an expression of type DECIMAL(x, y) where x <= 8.

Preparation

CREATE TABLE t1(a DECIMAL(4), b INTERVAL DAY (9) TO SECOND (9), c SMALLINT);
INSERT INTO t1 VALUES(3, cast('-1 00:00:00.000' as INTERVAL DAY (9) TO SECOND (9)), 1);
INSERT INTO t1 VALUES(4, cast('1  10:00:00.000' as INTERVAL DAY (9) TO SECOND (9)), 1);

Example

-- Expected result: 7 | +000000000 10:00:00.000000000
SELECT SUM(a), SUM(b) FROM t1 GROUP BY c;
-- Expected result: +000000000 10:00:00.000000000 | 7 
SELECT SUM(b), SUM(a) FROM t1 GROUP BY c;

Workaround

Cast the DECIMAL(x,y) to a DECIMAL (z,y) where z >8:

-- Expected result: 7 | +000000000 10:00:00.000000000
SELECT SUM(cast(a AS DECIMAL(18,0))), SUM(b) FROM t1 GROUP BY c;
-- Expected result: +000000000 10:00:00.000000000 | 7 
SELECT SUM(b), SUM(cast(a AS DECIMAL(18,0))) FROM t1 GROUP BY c;

Fix

The queries return the correct results.