Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-3998

Bad datatype for sum(n), it should be BIGINT but it is sometimes INTEGER

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Blocker
    • Resolution: Not A Problem
    • Affects Version/s: 1.23.0
    • Fix Version/s: 1.22.0
    • Component/s: core
    • Labels:
      None

      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

        Attachments

        1. image-2020-05-14-15-37-14-571.png
          31 kB
          TANG Wen-hui
        2. image-2020-05-14-15-39-28-279.png
          84 kB
          TANG Wen-hui
        3. image-2020-05-14-16-15-59-907.png
          57 kB
          TANG Wen-hui
        4. image-2020-05-14-17-07-49-157.png
          57 kB
          TANG Wen-hui
        5. image-2020-05-14-17-12-11-277.png
          40 kB
          TANG Wen-hui

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              eolivelli Enrico Olivelli
            • Votes:
              0 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: