Selecting a floating value yields incorrect results

[kalena]

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.

[Pearlaqua]

• 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".)