Blocker Description
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
