MariaDB SUM returns wrong value on column type float

version: 5.5.53-MariaDB

I added this table to my MariaDB:

    CREATE TABLE `myTable` (   `key` int(10) unsigned NOT NULL,
   `val_float` float unsigned NOT NULL,   `val_bigint` bigint(20) unsigned NOT NULL,   
   `val_int` int(10) unsigned NOT NULL,   PRIMARY KEY (`key`));

and add an entry:

     INSERT INTO myTable VALUES (1, 9600240000, 9600240000, 4294967295);

and when I do sum on all columns

  select `key`,sum(`val_float`),sum(`val_bigint`),sum(`val_int`) from myTable;

result:

  +------+------------------+-------------------+----------------+
  | key  | sum(`val_float`) | sum(`val_bigint`) | sum(`val_int`) |
  +------+------------------+-------------------+----------------+
  |    1 |       9600239616 |        9600240000 |     4294967295 |
  +------+------------------+-------------------+----------------+

How do I get the wrong value in sum(val_float) (9600239616 instead of 9600240000) and right values in the other columns

Thanks

Answer

9600240000 is a number which can’t be represented exactly by float datatype (IEEE 754 32-bit single precision)

Simple example in C:

#include <stdio.h>
void main()
{
  double double_value=9600240000;
  float float_value=9600240000;

  printf("float: %.14g double: %.14gn", float_value, double_value);
}

When running it, it will produce the following output:

float: 9600239616 double: 9600240000

The binary representation of 9600240000 is 1000111100001110000000100110000000 (34-bits) – even if you would try to store this value in a 32-bit integer, it wouldn’t work. If you need exact results, you should use the DECIMAL column type.