[ https://issues.apache.org/jira/browse/CALCITE-3998?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17107141#comment-17107141 ]
Enrico Olivelli edited comment on CALCITE-3998 at 5/14/20, 9:37 AM: -------------------------------------------------------------------- [~winipanda] thank you for debugging. Do you see differences between master and the branch with Calcite 1.23 ? in my calcite branch 1.23 the code of HerdDB is the same as it is on master branch, changing only calcite version I am very sorry but today I am super busy at work and I cannot debug was (Author: eolivelli): [~winipanda] thank you for debugging. Do you see differences between master and the branch with Calcite 1.23 ? in my calcite branch 1.23 the code of HerdDB is the same as it is on master branch, changing only calcite version > 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 > Attachments: image-2020-05-14-15-37-14-571.png, > image-2020-05-14-15-39-28-279.png, image-2020-05-14-16-15-59-907.png, > image-2020-05-14-17-07-49-157.png, image-2020-05-14-17-12-11-277.png > > > 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: > {noformat} > 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 > {noformat} > 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 > {noformat} > 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 > {noformat} > 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)