[IGNITE-14681] Calcite engine. Extend return type of sum() aggregate function

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Component/s: sql
• Labels: calcite
• Assignee: Taras Ledkov
• Reporter: Aleksey Plekhanov
• Created: 04/May/21 19:45
• Updated: 17/Mar/22 07:40
• Resolved: 20/Oct/21 15:37

Description

Currently, sum() aggregate function returns the same type as an argument and there can be an overflow.

For example, query:

SELECT SUM(i::SMALLINT) FROM (SELECT 32000 as i UNION ALL SELECT 32000)

Returns -1536.

or

CREATE TABLE integers(i INTEGER);

INSERT INTO integers SELECT * FROM table(system_range(0, 999, 1));

SELECT SUM(b) FROM bigints

Returns 499500 instead of 4611686018427388403500.

Perhaps it would be better to return an extended type as some other vendors do.

For example, PostgreSQL returns bigint for smallint or int arguments, numeric for bigint arguments, double precision for floating-point arguments. MySQL returns a DECIMAL value for exact-value arguments (INTEGER or DECIMAL), and a DOUBLE value for approximate-value arguments (FLOAT or DOUBLE)

Affected tests:
modules/calcite/src/test/sql/aggregate/aggregates/test_sum.test_ignore

Result type of SUM:

Argument type	SUM type
TINYINT SMALLINT INTEGER	BIGINT
REAL FLOAT DOUBLE	DOUBLE
BIGINT DECIMAL	DECIMAL
other type	the same type

Issue Links

• is part of: IGNITE-14535 Caclite SQL engine capabilities
• is related to: IGNITE-15426 Caclite engine. Upgrade calcite libs to the version 1.28
• links to: GitHub Pull Request #9148