Uploaded image for project: 'Ignite'
  1. Ignite
  2. IGNITE-14681

Calcite engine. Extend return type of sum() aggregate function

    XMLWordPrintableJSON

Details

    Description

      Currently, sum() aggregate function returns the same type as an argument and there can be an overflow.

      For example, query:

      SELECT SUM(i::SMALLINT) FROM (SELECT 32000 as i UNION ALL SELECT 32000)

      Returns -1536.

      or

      CREATE TABLE integers(i INTEGER);
      
      INSERT INTO integers SELECT * FROM table(system_range(0, 999, 1));
      
      SELECT SUM(b) FROM bigints
      

      Returns 499500 instead of 4611686018427388403500.

      Perhaps it would be better to return an extended type as some other vendors do.

      For example, PostgreSQL returns bigint for smallint or int arguments, numeric for bigint arguments, double precision for floating-point arguments. MySQL returns a DECIMAL value for exact-value arguments (INTEGER or DECIMAL), and a DOUBLE value for approximate-value arguments (FLOAT or DOUBLE)

      Affected tests:
      modules/calcite/src/test/sql/aggregate/aggregates/test_sum.test_ignore

      Result type of SUM:

      Argument type SUM type
      TINYINT
      SMALLINT
      INTEGER
      BIGINT
      REAL
      FLOAT
      DOUBLE
      DOUBLE
      BIGINT
      DECIMAL
      DECIMAL
      other type the same type

      Attachments

        Issue Links

          Activity

            People

              tledkov-gridgain Taras Ledkov
              alex_pl Aleksey Plekhanov
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0h
                  0h
                  Logged:
                  Time Spent - 2h 10m
                  2h 10m