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: | |
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
[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.