Bug #32095 data has different result for double type with/without ps-protocol
Submitted: 5 Nov 2007 7:26 Modified: 23 Jan 2008 16:28
Reporter: Guangbao Ni Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.1.22 OS:Any
Assigned to: CPU Architecture:Any
Tags: ps-protocol, server
Triage: D4 (Minor)

[5 Nov 2007 7:26] Guangbao Ni
Description:
When insert and update a double type in master, and slave shows different result with ps-protocol and without ps-protocol. the number of digitals is
greater than 15.

 

How to repeat:
for example:
--connection master
CREATE TABLE t1 (c1 INT KEY) ROW_FORMAT=DYNAMIC ENGINE=NDB;
ALTER ONLINE TABLE t1 ADD d DOUBLE UNSIGNED;
INSERT INTO t1 VALUES(13, 1.7976931348623157E+308);
--sync_slave_with_master
connection slave;
SELECT * FROM t1 ORDER BY c1;

Suggested fix:
The following is explanated by Alexey.

The diff is actually visible, note "1.7976931348623e+308" instead 
of  "1.79769313486232e+308".

The reason is in how the double-to-string conversion is performed. 

With ps protocol disabled the conversion is performed by the server in 
Field_double::val_str() which uses DBL_DIG = 15 as precision for "%g":

  if (dec >= NOT_FIXED_DEC)
  {
    sprintf(to,"%-*.*g",(int) field_length,DBL_DIG,nr);
    to=strcend(to,' ');
  }

With ps protocol enabled the conversion is performed by the client library in 
fetch_float_with_conversion() which uses 14 as precision for "%g":

      /*
        The 14 below is to ensure that the server and client has the same
        precisions. This will ensure that on the same machine you get the
        same value as a string independent of the protocol you use.
      */
      sprintf(buff, "%-*.*g", (int) min(sizeof(buff)-1,
                                        param->buffer_length),
        min(14,width), value);
      end= strcend(buff, ' ');
      *end= 0;

Hence the difference in rounding.

Thank you, Alexey for your explanation!
[5 Nov 2007 8:53] Guangbao Ni
It is a normal server bug, and not relate to replication and ndb storage engine.

the following is the simple example test case that you can reproduce it:
USE test;
CREATE TABLE t1 (c1 INT KEY,d DOUBLE UNSIGNED);
INSERT INTO t1 VALUES(13, 1.7976931348623157E+308);
SELECT * FROM t1 ORDER BY c1;
DROP TABLE t1;
[7 Nov 2007 4:00] 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/37236

ChangeSet@1.2669, 2007-11-07 11:48:11+08:00, gni@dev3-221.dev.cn.tlan +15 -0
  WL#3829 Extending testing of the online alter table
    Fix the problem caused by bug#32095  
    Here just shorten the double type and avoid the problem.  
    Because the bug#32095 has been comment in rpl_ndb_add_column.test,
    Don't want to be affected more than twice by the bug.
[14 Dec 2007 17:09] Bugs System
Pushed into 6.0.5-alpha
[23 Jan 2008 16:28] Paul Dubois
Noted in 6.0.5 changelog.

String-to-double conversion was performed differently when the
prepared-statement protocol was used from when it was not.