Bug #8431 Precision math: FLOOR returns incorrect result

• Submitted: 11 Feb 2005 1:04
• Modified: 4 Mar 2005 19:37
• Reporter: Trudy Pelzer
• Status: Closed
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0.3-alpha-debug
• OS: Linux (SuSE 9.1)
• Assigned to: Alexey Botchkov
• CPU Architecture: Any

[11 Feb 2005 1:04] Trudy Pelzer

Description:
The MySQL Reference Manual says that FLOOR should
return the largest integer value that is not greater than
its argument. This is not always the case.

How to repeat:
mysql> select floor(999999999999999999);
+---------------------------+
| floor(999999999999999999) |
+---------------------------+
|       1000000000000000000 |
+---------------------------+
1 row in set (0.00 sec)
-- This is the incorrect result. The function should return
999999999999999999

mysql> select floor(9.999999999999999999999);
+--------------------------------+
| floor(9.999999999999999999999) |
+--------------------------------+
|                             10 |
+--------------------------------+
1 row in set (0.00 sec)
-- This is the incorrect result. The function should return 9.

mysql> create table t (col1 int,col2 decimal(38,22);
mysql> insert into t values(3,9.9999999999999999999999);

mysql> select floor(col2) from t where col1=3;
+-------------+
| floor(col2) |
+-------------+
|          10 |
+-------------+
1 row in set (0.00 sec)
-- This is the incorrect result. The function should return 9.

[11 Feb 2005 1:10] MySQL Verification Team

Thank you for the bug report.
The test case should be fixed in the syntax of:

from:
create table t (col1 int,col2 decimal(38,22);
to:
create table t (col1 int,col2 decimal(38,22));

[4 Mar 2005 18:08] Bugs System

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/22673

[4 Mar 2005 19:37] Alexey Botchkov

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

[4 Apr 2005 22:24] Clint Byrum

I have seen this on MySQL 4.1.10 as well. This bug really should be reopened.