Bug #2663 FORMAT() function rounding inconsistently

• Submitted: 6 Feb 2004 8:08
• Modified: 6 Feb 2004 9:11
• Reporter: Nathan Cassano
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: MySQL-3.23.56-1
• OS: Linux (Linux 2.4.20-20.7)
• Assigned to: Sergei Golubchik
• CPU Architecture: Any

[6 Feb 2004 8:08] Nathan Cassano

Description:

The MySQL FORMAT function (or one of it's utilized functions) is inconsistently rounding decimal values ending in 5 up and down. This appears to be triggered by the value of the whole number portion of the decimal (sic).

How to repeat:
mysql> select FORMAT(.005, 2);
+-----------------+
| FORMAT(.005, 2) |
+-----------------+
| 0.01            |
+-----------------+
mysql> select FORMAT(1.005, 2);
+------------------+
| FORMAT(1.005, 2) |
+------------------+
| 1.00             |
+------------------+
mysql> select FORMAT(7.005, 2);
+------------------+
| FORMAT(7.005, 2) |
+------------------+
| 7.00             |
+------------------+
mysql> select FORMAT(8.005, 2);
+------------------+
| FORMAT(8.005, 2) |
+------------------+
| 8.01             |
+------------------+

Suggested fix:

Well this is a band-aid that was discovered but it gives an idea of how you might fix the problem internally.

FORMAT(Dollars >= 0, Dollars + 0.00000000001, Dollars + -0.00000000001), 2)

[6 Feb 2004 9:11] Sergei Golubchik

it's not a bug, but an intrinsic property of a limited-precision representation of a real numbers. See, for example:

% perl -e 'printf "%.2f %.2f %.2f %.2f\n", 0.005, 1.005, 7.005, 8.005'
0.01 1.00 7.00 8.01