Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-7195

AVG() loses precision in INT context

        Details

        • Type: Bug
        • Status: Open
        • Priority: Minor
        • Resolution: Unresolved
        • Affects Version/s: 10.0.14, 10.1.0
        • Fix Version/s: 10.1
        • Component/s: None
        • Labels:
          None

          Description

          AVG(bigint_expression) loses precision when used in integer context.
          This script demonstrates the problem using bit shift, to provide integer context:

          DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
  auto SERIAL,
  fld1 bigint unsigned NOT NULL,
  companynr tinyint(2) unsigned zerofill DEFAULT '00' NOT NULL,
  UNIQUE fld1 (fld1)
);
INSERT INTO t1 VALUES (1,0x7FFFFFFFFFFFFFFF,00);
INSERT INTO t1 VALUES (2,0x7FFFFFFFFFFFFFFE,37); 
INSERT INTO t1 VALUES (3,0x7FFFFFFFFFFFFFFC,37);  
SELECT companynr, AVG(fld1), AVG(fld1)<<0 AS avg1, CAST(AVG(fld1) AS UNSIGNED)<<0 AS avg2 FROM t1 GROUP BY companynr;

          The result is:

          +-----------+--------------------------+---------------------+---------------------+
| companynr | AVG(fld1)                | avg1                | avg2                |
+-----------+--------------------------+---------------------+---------------------+
|        00 | 9223372036854775807.0000 | 9223372036854775808 | 9223372036854775807 |
|        37 | 9223372036854775805.0000 | 9223372036854775808 | 9223372036854775805 |
+-----------+--------------------------+---------------------+---------------------+

          Notice, the values in the column avg1 lost precision.
          If I wrap AVG into CAST(AS UNSIGNED), like in the column avg2, then the result is Ok.

          The problem is in this piece of code in item_sum.cc, which uses double routines:

          longlong Item_avg_field::val_int()
{
  return (longlong) rint(val_real());
}

          It should use DECIMAL routines when working with DECIMAL or INTEGER arguments.

            Gliffy Diagrams

              Attachments

                Activity

                There are no comments yet on this issue.

                  People

                  • Assignee:
                    bar Alexander Barkov
                    Reporter:
                    bar Alexander Barkov
                  • Votes:
                    0 Vote for this issue
                    Watchers:
                    2 Start watching this issue

                    Dates

                    • Created:
                      Updated: