Bug #3051 FLOOR returns invalid

• Submitted: 3 Mar 2004 1:07
• Modified: 19 Mar 2004 7:00
• Reporter: Jon Witte
• Status: Closed
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 4.0.14-standard-log
• OS: Linux (Mandrake 9.1)
• Assigned to: Bugs System
• CPU Architecture: Any

[3 Mar 2004 1:07] Jon Witte

Description:
FLOOR misbehaves when used in association with LOG2 (See Below)

Interestingly, FLOOR works on win32 4.0.12-nt-log

How to repeat:
mysql> select floor(log2
+----------------+
| floor(log2(4)) |
+----------------+
|              2 |
+----------------+
1 row in set (0.00 sec)

mysql> select floor(log2
+----------------+
| floor(log2(8)) |
+----------------+
|              2 |
+----------------+
1 row in set (0.00 sec)

Suggested fix:
Dunno, using ROUND instead tidied up the results for me.

[3 Mar 2004 17:56] Dean Ellis

Verified against 4.0.19/Linux.  Another gcc optimization bug.

===== item_func.cc 1.106 vs edited =====
*** /tmp/item_func.cc-1.106-1388        Fri Feb  6 06:15:52 2004
--- edited/item_func.cc Wed Mar  3 19:55:28 2004
***************
*** 667,673 ****
                                                                             
  longlong Item_func_floor::val_int()
  {
!   double value=args[0]->val();
    null_value=args[0]->null_value;
    return (longlong) floor(value);
  }
--- 667,673 ----
   
  longlong Item_func_floor::val_int()
  {
!   volatile double value=args[0]->val();
    null_value=args[0]->null_value;
    return (longlong) floor(value);
  }

[4 Mar 2004 6:22] Victor Vagin

I've repeated the bug on mysql-323:

mysql> select floor(log(8)/log(2));
+----------------------+
| floor(log(8)/log(2)) |
+----------------------+
|                    2 |
+----------------------+
1 row in set (0.00 sec)

So, I'm going to fix it in mysql-3.23 and after merge it'll appear in mysql-4.0

[11 Mar 2004 2:12] Victor Vagin

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

Additional info:

Fix will be in mysql-3.23.59, mysql-4.0.19, mysql-4.1.2

[19 Mar 2004 7:00] Michael Widenius

Note that the fix we added will only work for some compilers.

When you are working with floating point math, there is no gurantees that
floor(log2(8)) will return 2 or 3 becasue log2() is not an exact function and floor is defined to cut out any decimals.