You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@ignite.apache.org by "Taras Ledkov (Jira)" <ji...@apache.org> on 2021/10/04 09:01:00 UTC
[jira] [Updated] (IGNITE-14681) Calcite engine. Extend return type
of sum() aggregate function
[ https://issues.apache.org/jira/browse/IGNITE-14681?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Taras Ledkov updated IGNITE-14681:
----------------------------------
Description:
Currently, {{sum()}} aggregate function returns the same type as an argument and there can be an overflow.
For example, query:
{noformat}
SELECT SUM(i::SMALLINT) FROM (SELECT 32000 as i UNION ALL SELECT 32000){noformat}
Returns {{-1536}}.
or
{noformat}
CREATE TABLE integers(i INTEGER);
INSERT INTO integers SELECT * FROM table(system_range(0, 999, 1));
SELECT SUM(b) FROM bigints
{noformat}
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* |
was:
Currently, {{sum()}} aggregate function returns the same type as an argument and there can be an overflow.
For example, query:
{noformat}
SELECT SUM(i::SMALLINT) FROM (SELECT 32000 as i UNION ALL SELECT 32000){noformat}
Returns {{-1536}}.
or
{noformat}
CREATE TABLE integers(i INTEGER);
INSERT INTO integers SELECT * FROM table(system_range(0, 999, 1));
SELECT SUM(b) FROM bigints
{noformat}
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}}
> Calcite engine. Extend return type of sum() aggregate function
> --------------------------------------------------------------
>
> Key: IGNITE-14681
> URL: https://issues.apache.org/jira/browse/IGNITE-14681
> Project: Ignite
> Issue Type: Bug
> Reporter: Aleksey Plekhanov
> Assignee: Taras Ledkov
> Priority: Major
> Labels: calcite2-required, calcite3-required
> Time Spent: 2h
> Remaining Estimate: 0h
>
> Currently, {{sum()}} aggregate function returns the same type as an argument and there can be an overflow.
> For example, query:
> {noformat}
> SELECT SUM(i::SMALLINT) FROM (SELECT 32000 as i UNION ALL SELECT 32000){noformat}
> Returns {{-1536}}.
> or
> {noformat}
> CREATE TABLE integers(i INTEGER);
> INSERT INTO integers SELECT * FROM table(system_range(0, 999, 1));
> SELECT SUM(b) FROM bigints
> {noformat}
> 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* |
--
This message was sent by Atlassian Jira
(v8.3.4#803005)