[jira] [Comment Edited] (CALCITE-3998) Bad datatype for sum(n), it should be BIGINT but it is sometimes INTEGER

["Julian Hyde (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-3998?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17106576#comment-17106576 ] 

Julian Hyde edited comment on CALCITE-3998 at 5/13/20, 6:51 PM:
----------------------------------------------------------------

Can you use \{code\} or \{noformat\} markup and fix the emojis in your description.

As of CALCITE-1945 the type of SUM can be controlled via {{RelDataTypeSystem.deriveSumType}}. I don't know whether anything has changed recently, or whether you could use this method to get the results you want.


was (Author: julianhyde):
Can you use {code} or {format} markup and fix the emojis in your description.

As of CALCITE-1945 the type of SUM can be controlled via {{RelDataTypeSystem.deriveSumType}}. I don't know whether anything has changed recently, or whether you could use this method to get the results you want.

> Bad datatype for sum(n), it should be BIGINT but it is sometimes INTEGER
> ------------------------------------------------------------------------
>
>                 Key: CALCITE-3998
>                 URL: https://issues.apache.org/jira/browse/CALCITE-3998
>             Project: Calcite
>          Issue Type: Bug
>          Components: core
>    Affects Versions: 1.23.0
>            Reporter: Enrico Olivelli
>            Priority: Blocker
>
> I also noted that sometimes the type of sum(N) where N is an INTEGER column sometimes it is now reported by Calcite as INTEGER and sometimes as a BIGINT. In 1.22 every time is reported as BIGINT.
> So we have another test failing.
> SELECT sum(n1), count(*) as cc, k1
> FROM tblspace1.tsql
> GROUP by k1
> ORDER BY sum(n1)
> Here sum(n1) is reported now a INTEGER, previously it was a BIGINT. I would prefer to see it as a BIGINT in order to prevent overflows
> Here are the plans:
> INFO: Query: SELECT sum(n1), count(*) as cc, k1  FROM tblspace1.tsql GROUP by k1 ORDER BY sum(n1) -- Logical Plan
> LogicalSort(sort0=[$0], dir0=[ASC]): rowcount = 2.0, cumulative cost = {10.525000095367432 rows, 37.0 cpu, 0.0 io}, id = 1038
>   LogicalProject(EXPR$0=[$1], CC=[$2], K1=[$0]): rowcount = 2.0, cumulative cost = {8.525000095367432 rows, 13.0 cpu, 0.0 io}, id = 1037
>     LogicalAggregate(group=[{0}], EXPR$0=[SUM($1)], CC=[COUNT()]): rowcount = 2.0, cumulative cost = {6.525000095367432 rows, 7.0 cpu, 0.0 io}, id = 1035
>       LogicalProject(K1=[$0], n1=[$1]): rowcount = 2.0, cumulative cost = {4.0 rows, 7.0 cpu, 0.0 io}, id = 1034
>         LogicalTableScan(table=[[tblspace1, tsql]]): rowcount = 2.0, cumulative cost = {2.0 rows, 3.0 cpu, 0.0 io}, id = 1032
> May 12, 2020 11:07:37 AM herddb.sql.CalcitePlanner runPlanner
> INFO: Query: SELECT sum(n1), count(*) as cc, k1  FROM tblspace1.tsql GROUP by k1 ORDER BY sum(n1) -- Best  Plan
> EnumerableSort(sort0=[$0], dir0=[ASC]): rowcount = 2.0, cumulative cost = {5.0 rows, 31.0 cpu, 0.0 io}, id = 1245
>   EnumerableProject(EXPR$0=[$1], CC=[1:BIGINT], K1=[$0]): rowcount = 2.0, cumulative cost = {3.0 rows, 7.0 cpu, 0.0 io}, id = 1244
>     EnumerableInterpreter: rowcount = 2.0, cumulative cost = {1.0 rows, 1.0 cpu, 0.0 io}, id = 1243
>       BindableTableScan(table=[[tblspace1, tsql]], projects=[[0, 1]]): rowcount = 2.0, cumulative cost = {0.016 rows, 0.024 cpu, 0.0 io}, id = 1055
> Within the same test case with the same tables the result of this query is not changed
> SELECT sum(n1) as ss, min(n1) as mi, max(n1) as ma FROM tblspace1.tsql
> INFO: Query: SELECT sum(n1) as ss, min(n1) as mi, max(n1) as ma FROM tblspace1.tsql -- Logical Plan
> LogicalAggregate(group=[{}], SS=[SUM($0)], MI=[MIN($0)], MA=[MAX($0)]): rowcount = 1.0, cumulative cost = {5.387500047683716 rows, 5.0 cpu, 0.0 io}, id = 1253
>   LogicalProject(n1=[$1]): rowcount = 2.0, cumulative cost = {4.0 rows, 5.0 cpu, 0.0 io}, id = 1252
>     LogicalTableScan(table=[[tblspace1, tsql]]): rowcount = 2.0, cumulative cost = {2.0 rows, 3.0 cpu, 0.0 io}, id = 1250
> May 12, 2020 11:08:48 AM herddb.sql.CalcitePlanner runPlanner
> INFO: Query: SELECT sum(n1) as ss, min(n1) as mi, max(n1) as ma FROM tblspace1.tsql -- Best  Plan
> EnumerableAggregate(group=[{}], SS=[SUM($0)], MI=[MIN($0)], MA=[MAX($0)]): rowcount = 1.0, cumulative cost = {2.387500047683716 rows, 1.0 cpu, 0.0 io}, id = 1295
>   EnumerableInterpreter: rowcount = 2.0, cumulative cost = {1.0 rows, 1.0 cpu, 0.0 io}, id = 1294
>     BindableTableScan(table=[[tblspace1, tsql]], projects=[[1]]): rowcount = 2.0, cumulative cost = {0.012 rows, 0.018000000000000002 cpu, 0.0 io}, id = 1265
> This is the test on HerdDB
> https://github.com/diennea/herddb/blob/vote-calcite-123/herddb-core/src/test/java/herddb/sql/SimplerPlannerTest.java#L237



--
This message was sent by Atlassian Jira
(v8.3.4#803005)