Bug #6792 IF statement doesn't work correctly

• Submitted: 24 Nov 2004 9:14
• Modified: 19 Dec 2004 13:30
• Reporter: Paul Menheere
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.1.7-Max
• OS: Linux (Linux)
• Assigned to: Sergei Golubchik
• CPU Architecture: Any

[24 Nov 2004 9:14] Paul Menheere

Description:
Currently on MySQL 4.1.7-Max the following query goes bad.

SELECT 39.95, (39.95*100 + IF( (39.95*100)%Floor(100*0.05) = 0, 0, (FLOOR(100*0.05) - (39.95*100)%Floor(100*0.05))))/100,(39.95*100)%Floor(100*0.05),IF( (39.95*100)%Floor(100*0.05) = 0, 0, (FLOOR(100*0.05) - (39.95*100)%Floor(100*0.05)))

Normally 39.95 is a Price in a table, but this should show it too.

what I am doing here is to round the price, always up to 0.05. As you can see when you execute the query you can see that the `(39.95*100)%Floor(100*0.05)` equals `0` but in the IF statement, it still False en picks the third argument. so that 39.95 becomes 40.
It's only with this number AFAIK. Other numbers are working correctly

On MySQL 4.0.16-Max it works correctly. 

How to repeat:
SELECT 39.95, (39.95*100 + IF( (39.95*100)%Floor(100*0.05) = 0, 0, (FLOOR(100*0.05) - (39.95*100)%Floor(100*0.05))))/100,(39.95*100)%Floor(100*0.05),IF( (39.95*100)%Floor(100*0.05) = 0, 0, (FLOOR(100*0.05) - (39.95*100)%Floor(100*0.05)))

[24 Nov 2004 9:16] Paul Menheere

higher priority than low, it involves some boolean logic.

[24 Nov 2004 12:31] Joerg Bruehe

a) Floating point arithmetic is always a problem, because of the decimal-to-binary conversion.
See the article by David Goldberg in "ACM Computing Surveys", March 1991.
b) As of 4.1.7, the MOD operator was expanded in MySQL to also accept non-integral operands. Using it might be an easier way for your rounding.

[30 Nov 2004 9:38] Paul Menheere

Well MySQL Server 4.1.7Max doesn't think that the (39.95*100)%Floor(100*0.05) and 0 are equal that's all.

Earlier versions think it is

[19 Dec 2004 11:43] Hartmut Holzgraefe

4.1.5:    (39.95*100) % 5 = 0
4.1.7:    (39.95*100) % 5 = 5  (actually 4.9999...)

[19 Dec 2004 13:30] Sergei Golubchik

As Joerg explained the reason is the fix for http://bugs.mysql.com/bug.php?id=6138

As documented in the manual:

   * `MOD()' no longer rounds arguments with a fractional part to
     integers.  Now it returns exact remainder after division. (Bug
     #6138)

Though I agree that this should've been done in 5.0, not in 4.1.7 - but it's too late to change it.

39.95 is inherently inexact. It's represented internally as a floating-point number 39.94999999999999.

Quite natural, the remainder is 4.999999999999.

To get pre-4.1.7 behaviour use

ROUND(39.95*100)%5