Selecting a floating value yields incorrect results



  • Consider the following.

    CREATE TABLE IF NOT EXISTS `docs` (
      `id` int(6) unsigned NOT NULL,
      `qty` float NOT NULL,
      `price` float NOT NULL,
      `rebate` float not null,
      PRIMARY KEY (`id`)
    ) DEFAULT CHARSET=utf8;
    INSERT INTO `docs` VALUES
      (1,1,43616.7,0);
    

    SELECT SUM(qty * (price - price * rebate/100)) AS endval
    FROM docs

    Instead of returning 43616.7 as expected, this query gives back 43616.69921875 (on some machines 43616.671875). Demo fiddle can be https://www.db-fiddle.com/f/gUnwQaHBNTjXPrK4bnu65M/0 .

    Why is this happening? I would expect it to work like this (apologies for actually typing out something as basic as this):

    1 * (43616.7 - 43616.7 * 0 / 100) = 1 * (43616.7 - 0) = 43616.7
    

    I realize that I could overcome the issue by going with decimal (10,2) for my columns, I'm just wondering what's causing this unexpected behaviour.



    • Do not use FLOAT or DOUBLE for money; use DECIMAL(m,n)

    • It seems like qty should be integral?

    • The following, and most things like it are doomed to returning FALSE:

      WHERE float_col = 1.23
      

      This is due to FLOAT being stored in binary, but 1.23 is decimal.

    • 43616.7 is almost at the range limit of FLOAT, which is about 7 significant digits. (That constant is 6 "significant digits".)




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2