MySQL Bugs: #19746: SELECT FLOOR(1/3+2/3) returns zero. It should return 1

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

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.

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 =="

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.