MySQL Bugs: #35071: DOUBLE to DECIMAL conversion gives different results on Windows and Linux

Bug #35071	DOUBLE to DECIMAL conversion gives different results on Windows and Linux
Submitted:	5 Mar 2008 9:38	Modified:	20 Jul 2017 8:25
Reporter:	Stefan Hinz	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	5.0,5.1	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
"SELECT PI()+0.000000000000000000" gives 3.141592653589793*100* on Windows but 3.141592653589793*116* on Linux (and in the Reference Manual).

Alexey explained:

Note that "SELECT PI()+0.000000000000000000" produces a DECIMAL result. So what you see is a result of DOUBLE-to-DECIMAL conversion, and thus one should not expect a "precise" result (to be more specific, errors in least significant digits depend on the algorithm used for the conversion).

This was improved in 6.0 by the patch for WL#2934 (dtoa) where we use a more precise algorithm when converting DOUBLE to DECIMAL.

6.0-BK, Windows:

mysql> SELECT PI()+0.000000000000000000\G
*************************** 1. row ***************************
PI()+0.000000000000000000: 3.141592653589793000

6.0-BK, Linux:

mysql> SELECT PI()+0.000000000000000000\G
*************************** 1. row ***************************
PI()+0.000000000000000000: 3.141592653589793000 

How to repeat:
Issue "SELECT PI()+0.000000000000000000" statement on Windows and on Linux.

Suggested fix:
Make behavior consistent across platforms.

Alexey explained:

I suspect the inconsistency is a result of the difference in floating point code generation between gcc and Visual Studio. Could you give more  details on your Windows build (compiler version and CPU)?

I think I should backport some compiler tweaks I did for the dtoa patch in 6.0 to 5.x, so that results would be at least consistent across platforms. Could you please file a bug?

The user who initially reported the inconsistency provided this information about his platform:

Cpu is AMD Athlon K7 SSE, official compilation. (got from mysql.com)

Alexey, shouldn't this be closed now?

As the original report says, this bug does not exist in 6.0, but it is still present in 5.0/5.1.

see this bug i found and recently fixed in drizzle, same problem applies here and likely is the cause of the differences being seen:
    https://bugs.launchpad.net/drizzle/+bug/408694

it's a mis-use of the new dtoa function, passing in the buffer size as ndigits rather than the maximum number of digits to 'print' (e.g. precision.)

dtoa.c:224 is
  res= dtoa(x, 4, type == MY_GCVT_ARG_DOUBLE ? width : min(width, FLT_DIG),
            &decpt, &sign, &end, buf, sizeof(buf));
and should be:
  res= dtoa(x, 4, type == MY_GCVT_ARG_DOUBLE ? min(width, DBL_DIG) :
            min(width, FLT_DIG), &decpt, &sign, &end, buf, sizeof(buf));

patch is the first piece of the following, the test problems may not apply.
    https://code.launchpad.net/~rwmcfa1/drizzle/fix-bug408694

-rm

re-read comments and see that this particular bug doesn't apply to 6.0, regardless the problem i pointed out does. i'll create a new bug for it...

I believe this bug has been fixed by https://github.com/mysql/mysql-server/commit/f02525b and thus, does not exist as of MySQL 5.5.

Posted by developer:
 
Per the following comment, this bug seems to have been fixed. Closing this bug report.
[20 Jul 7:58] Alexey Kopytov <aleksei.kopytov@caviumnetworks.com>
I believe this bug has been fixed by
https://github.com/mysql/mysql-server/commit/f02525b and thus, does not
exist as of MySQL 5.5.