Bug #19746 SELECT FLOOR(1/3+2/3) returns zero. It should return 1
Submitted: 12 May 2006 0:24 Modified: 17 May 2006 23:39
Reporter: David Chatel Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.21 OS:Windows (Windows XP Pro SP2)
Assigned to: CPU Architecture:Any

[12 May 2006 0:24] David Chatel
Description:
Floating calculations inaccuracy leads to miscalculations.

How to repeat:
SELECT FLOOR(1/3+2/3) should return 1 but it returns 0.

The problem also affect the function SUM :

CREATE TABLE `test` (
`id` BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`value` DOUBLE NOT NULL
) ENGINE = innodb;
INSERT INTO `test` (`value`) VALUES (1/3),(2/3);
SELECT FLOOR(SUM(value)) FROM test;

This also returns 0 while it should return 1.

Suggested fix:
Increase the accuracy of floating data.
[12 May 2006 11:22] Hartmut Holzgraefe
Welcome to the wonderful world of float rounding issues.

There is no way to express *exactly* 1/3 or 2/3 in binary floating point formats,
it also isn't possible in decimal either.

Lets take a decimal example, and lets take only 3 significant digits:

1/3 = 0.333
2/3 = 0.666

0.333 + 0.666 = 0.999

floor(0.999) = 0

floor() should never start any rounding of its own here,
it is defined to return the highest integer value that is
less or equal its argument, not less or about equal
but less or *exactly* equal.

So it behaves as defined here.

PS: for similar reason the first rule of floats is
      "never compare floats with =="
[17 May 2006 23:39] David Chatel
I agree, except on one thing :
2/3 should be represented, if represented as decimal(4,3), as 0.667 ; not 0.666.

It would easily solve the 0.999999.... != 1 issue.
I agree that 0.99999999999999999999 doesnt equal 1, but the limit tends to be equal to 1.

Floating calculations should reflect this, by using a round instead of a truncature.