Bug #15936 "round" differs on Windows to Unix
Submitted: 22 Dec 2005 12:15 Modified: 18 Mar 2009 14:22
Reporter: Joerg Bruehe Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:4.1.17 OS:Microsoft Windows (Windows (32 bit))
Assigned to: Alexey Kopytov CPU Architecture:Any
Triage: Triaged: D3 (Medium) / R3 (Medium) / E3 (Medium)

[22 Dec 2005 12:15] Joerg Bruehe
Description:
Detected in a custom build of 4.1.17, based on ChangeSet
  1.2463.1.1 05/12/20 12:47:44 paul@snake-hub.snake.net +1 -0
  mysql.cc:
    Typo in help message.

-------------------------------------------------------
*** r/func_math.result  Wed Dec 21 16:13:35 2005
--- r/func_math.reject  Thu Dec 22 14:33:45 2005
***************
*** 25,31 ****
  Note  1003  select truncate(52.64,1) AS `truncate(52.64,1)`,truncate(52.64,2) AS `truncate(52.64,2)`,truncate(52.64,-(1)) AS `truncate(52.64,-1)`,truncate(52.64,-(2)) AS `truncate(52.64,-2)`,truncate(-(52.64),1) AS `truncate(-52.64,1)`,truncate(-(52.64),-(1)) AS `truncate(-52.64,-1)`
  select round(5.5),round(-5.5);
  round(5.5)  round(-5.5)
! 6 -6
  explain extended select round(5.5),round(-5.5);
  id  select_type table type  possible_keys key key_len ref rows  Extra
  1 SIMPLE  NULL  NULL  NULL  NULL  NULL  NULL  NULL  No tables used
--- 25,31 ----
  Note  1003  select truncate(52.64,1) AS `truncate(52.64,1)`,truncate(52.64,2) AS `truncate(52.64,2)`,truncate(52.64,-(1)) AS `truncate(52.64,-1)`,truncate(52.64,-(2)) AS `truncate(52.64,-2)`,truncate(-(52.64),1) AS `truncate(-52.64,1)`,truncate(-(52.64),-(1)) AS `truncate(-52.64,-1)`
  select round(5.5),round(-5.5);
  round(5.5)  round(-5.5)
! 5 -6
  explain extended select round(5.5),round(-5.5);
  id  select_type table type  possible_keys key key_len ref rows  Extra
  1 SIMPLE  NULL  NULL  NULL  NULL  NULL  NULL  NULL  No tables used
-------------------------------------------------------

How to repeat:
Run the test suite on Windows.
[2 Jan 2006 3:42] Brian Aker
Fix the test.
[7 Apr 2006 15:24] Hartmut Holzgraefe
looking at this a little closer i see that what we state in our documentation
is not necessarily true. it says:

   The behavior of ROUND() when the argument is halfway between two 
   integers depends on the C library implementation. Different implementations 
   round to the nearest even number, always up, always down, or always toward zero.

It is true that rint() behavior is not fully specified, but most implementation 
seem to agree on rint() rounding to the nearest even number.

The only system were we seem to be getting different ROUND() results
is Windows, and up to now i thought because Windows libc does implement
the rounding function we are using in a different way. This is what i have heard
from various sources in the past but a closer look turned out that this is actually
urban legend ...

Actually there is no rint() function in the Windows C library at all so we are 
providing our own implementation in include/config_win.h:

  inline double rint(double nr)
  {
    double f = floor(nr);
    double c = ceil(nr);
    return (((c-nr) >= (nr-f)) ? f :c);
  }

and this implementation indeed always rounds x.5 to the next lowest integer
this does not match *any* of the possible behaviors of the text quoted above
as it rounds away from zero for negative values

at the same time we have this in include/my_global.h

  #ifndef HAVE_RINT 
  #define rint(A) floor((A)+(((A) < 0)? -0.5 : 0.5)) 
  #endif

this implementation always rounds away from zero on x.5
which is not like the typical implementation of libc  rint()
but at least it behaves more like one would expect.

To add to the confusion in 5.0 we not only have different
rounding behavior depending on what platform the server
runs on but also different behavior for FLOAT and DECIMAL
as our DECIMAL implementation is *not* platform specific ...

As the differences in rounding behavior are reported by users
every once in a while: why don't we stop to rely on the platform
specific behavior of rint() once and for all? We have our own
implementations for platforms where it isn't available anyway ...
[12 Apr 2006 10:20] Magnus Blåudd
===== config-win.h 1.85 vs edited =====
--- 1.85/include/config-win.h	2006-04-06 11:36:06 +02:00
+++ edited/config-win.h	2006-04-12 10:36:16 +02:00
@@ -29,7 +29,6 @@
 
 #include <sys/locking.h>
 #include <windows.h>
-#include <math.h>			/* Because of rint() */
 #include <fcntl.h>
 #include <io.h>
 #include <malloc.h>
@@ -222,17 +221,9 @@
 #define inline __inline
 #endif /* __cplusplus */
 
-inline double rint(double nr)
-{
-  double f = floor(nr);
-  double c = ceil(nr);
-  return (((c-nr) >= (nr-f)) ? f :c);
-}
-
 #ifdef _WIN64
 #define ulonglong2double(A) ((double) (ulonglong) (A))
 #define my_off_t2double(A)  ((double) (my_off_t) (A))
-
 #else
 inline double ulonglong2double(ulonglong value)
 {
@@ -338,7 +329,6 @@
 #define HAVE_FLOAT_H
 #define HAVE_LIMITS_H
 #define HAVE_STDDEF_H
-#define HAVE_RINT		/* defined in this file */
 #define NO_FCNTL_NONBLOCK	/* No FCNTL */
 #define HAVE_ALLOCA
 #define HAVE_STRPBRK
[21 Apr 2006 0:51] Jim Winstead
Patch looks okay to push to me.
[7 Jun 2006 10:40] Magnus Blåudd
Pushed to mysql-5.1-new-maint
[3 Aug 2006 8:59] Magnus Blåudd
If possible this should be fixed in 5.1. Need to be investigated further.
[22 Dec 2006 19:35] Kian Gould
has it been fixed yet? we are having problems on our page http://www.aoemedia.de with this. It worked just fine on a Windows Server, but has a few issues on Unix, looking forward to the fix.
[7 Apr 2008 15:51] 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/commits/45005

ChangeSet@1.2597, 2008-04-07 19:50:40+04:00, kaa@kaamos.(none) +6 -0
  Fix for bug #15936: "round" differs on Windows to Unix
  
  Both of our own implementations of rint(3) were inconsistent with the
  most common behavior of rint() on those platforms that have it: round
  to nearest, break ties by rounding to nearest even.
  
  Fixed by leaving just one implementation of rint() in our source tree,
  and changing its behavior to match the most common native
  implementations on other platforms.
[7 Apr 2008 18:25] 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/commits/45018

ChangeSet@1.2595, 2008-04-07 22:24:31+04:00, kaa@kaamos.(none) +1 -0
  Post-merge fix for bug #15936.
[28 May 2008 10:01] Bugs System
Pushed into 6.0.6-alpha
[7 Aug 2008 21:03] Sveta Smirnova
Bug #38635 was marked as duplicate of this one.
[9 Feb 2009 13:05] Miguel Solorzano
Bug: http://bugs.mysql.com/bug.php?id=42691 has been marked as duplicate of this one.
[23 Feb 2009 12:28] 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/commits/67178

2752 Alexey Kopytov	2009-02-23
      Fix for bug #15936: "round" differs on Windows to Unix
      
      Both of our own implementations of rint(3) were inconsistent with the
      most common behavior of rint() on those platforms that have it: round
      to nearest, break ties by rounding to nearest even.
      
      Fixed by leaving just one implementation of rint() in our source tree,
      and changing its behavior to match the most common native
      implementations on other platforms.
      modified:
        configure.in
        include/config-win.h
        include/my_global.h
        mysql-test/r/func_math.result
        mysql-test/t/func_math.test
        sql/mysqld.cc
[25 Feb 2009 8:36] 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/commits/67445

2753 Alexey Kopytov	2009-02-25
      Fixed a build failure on Ubuntu 8.10 introduced by the patch
      for bug #15936.
      
      On some platforms fenv.h may #undef the min/max macros
      defined in my_global.h.
      
      Fixed by moving the #include directive for fenv.h from
      mysqld.cc to my_global.h before definitions for min/max.
      modified:
        include/my_global.h
        sql/mysqld.cc
[9 Mar 2009 14:13] Bugs System
Pushed into 5.0.79 (revid:joro@sun.com-20090309135922-a0di9ebkxoj4d4wv) (version source revid:staale.smedseng@sun.com-20090227160758-td4jot2la75f9zy1) (merge vers: 5.0.79) (pib:6)
[11 Mar 2009 1:12] Paul Dubois
Noted in 5.0.79 changelog.

ROUND() sometimes returned different results on different platforms.

Setting report to NDI pending push into 5.1.x/6.0.x.
[13 Mar 2009 19:02] Bugs System
Pushed into 5.1.33 (revid:joro@sun.com-20090313111355-7bsi1hgkvrg8pdds) (version source revid:kgeorge@mysql.com-20090225093253-po02ul6h1ouzcp4i) (merge vers: 5.1.33) (pib:6)
[13 Mar 2009 20:03] Paul Dubois
Noted in 5.1.33.

Setting report to NDI pending push into 6.0.x.
[18 Mar 2009 13:17] Bugs System
Pushed into 6.0.11-alpha (revid:joro@sun.com-20090318122208-1b5kvg6zeb4hxwp9) (version source revid:alexey.kopytov@sun.com-20090225085554-34mubnd3qjh7abyh) (merge vers: 6.0.10-alpha) (pib:6)
[18 Mar 2009 14:22] Paul Dubois
Noted in 6.0.11 changelog.
[9 May 2009 16:40] Bugs System
Pushed into 5.1.34-ndb-6.2.18 (revid:jonas@mysql.com-20090508185236-p9b3as7qyauybefl) (version source revid:jonas@mysql.com-20090508100057-30ote4xggi4nq14v) (merge vers: 5.1.33-ndb-6.2.18) (pib:6)
[9 May 2009 17:37] Bugs System
Pushed into 5.1.34-ndb-6.3.25 (revid:jonas@mysql.com-20090509063138-1u3q3v09wnn2txyt) (version source revid:jonas@mysql.com-20090508175813-s6yele2z3oh6o99z) (merge vers: 5.1.33-ndb-6.3.25) (pib:6)
[9 May 2009 18:34] Bugs System
Pushed into 5.1.34-ndb-7.0.6 (revid:jonas@mysql.com-20090509154927-im9a7g846c6u1hzc) (version source revid:jonas@mysql.com-20090509073226-09bljakh9eppogec) (merge vers: 5.1.33-ndb-7.0.6) (pib:6)