Enrico Olivelli created CALCITE-3998:
----------------------------------------
Summary: 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
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)
