MySQL Bugs: #21205: Different number of digits for float/doble/real in --ps-protocol

Bug #21205	Different number of digits for float/doble/real in --ps-protocol
Submitted:	21 Jul 2006 8:12	Modified:	11 May 2009 14:56
Reporter:	Magnus Blåudd	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Prepared statements	Severity:	S3 (Non-critical)
Version:	5.0	OS:	Any
Assigned to:	Alexey Kopytov	CPU Architecture:	Any

Description:
Splitting bug#11589 into to parts.

[8 Jul 2005 10:36] Matthias Leich
The problem with the differing output does also appear with non
zerofill float/double/real values.
Without --ps-protocol :
CREATE TABLE t1 (f1 DOUBLE);
INSERT INTO t1 SET f1 = 
-1.79769313486231e+308;;
SELECT f1 AS f1_direct FROM t1;
f1_direct
-1.79769313486231e+308
SELECT f1 AS f1_eval FROM t1;
f1_eval          (Executed via eval)
-1.79769313486231e+308
PREPARE my_sel FROM 'SELECT f1 AS f1_prepared FROM t1';
EXECUTE my_sel;
f1_prepared
-1.79769313486231e+308
       All SELECT variants give the same result 
       -1.79769313486231e+308 (= mantissa 15 digits).
With --ps-protocol :
... 
SELECT f1 AS f1_direct FROM t1;
f1_direct
-1.7976931348623e+308  ( = mantissa 14 digits)
                         ###
      All other SELECT variants give the same result 
      -1.79769313486231e+308.

When I shorten the mantissa to 14 digits
(INSERT of -1.7976931348623e+308)
the problem disappears.
 
The file ml033.test contains this testcase.

How to repeat:
--disable_abort_on_error

--disable_warnings
drop table if exists t1 ;
--enable_warnings

# let $engine= innodb;
CREATE TABLE t1 (f1 DOUBLE);

INSERT INTO t1 SET f1 = 
-1.7976931348623e+308;
# The value above 14 digit mantissa is harmless.
# -1.79769313486231e+308; would cause a problem.
SELECT f1 AS f1_direct FROM t1;
let $my_stmt1= SELECT f1 AS f1_eval FROM t1;
let $my_stmt2= SELECT f1 AS f1_prepared FROM t1;
eval $my_stmt1;
eval PREPARE my_sel FROM '$my_stmt2';
EXECUTE my_sel;

Suggested fix:
Same number of digits independent of wheter ps-protocol or not and also wheter this is returned directly from a field or an item.

[13 Jun 18:10] Magnus Svensson

Looks like the number of digits depend on wheter we are sending a
double/float/real field directly or if it's a Item_* that is sending the value.
Not so nice as it's not possible in libmysql to see any difference. :)

Per AlexeyK in Dec/2007:
Fixed in 6.0 by the patch for WL #2934.

Setting E/R trusting AlexeyK's suggestion.

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/64372

2728 Alexey Kopytov	2009-01-28
      Fix for bug #21205: Different number of digits for float/double/real in --ps-protocol
      
      Various parts of code used different 'precision' arguments for sprintf("%g") when converting
      floating point numbers to a string. This led to differences in results in some cases 
      depending on whether the text-based or prepared statements protocol is used for a query.
      
      Fixed by changing arguments to sprintf("%g") to always be 15 (DBL_DIG) so that results are
      consistent regardless of the protocol.
      
      This patch will be null-merged to 6.0 as the problem does not exists there (fixed by the
      patch for WL#2934).

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/65759

2984 Alexey Kopytov	2009-02-10 [merge]
      Manual merge of the patch for bug #21205 to 6.0.

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/66336

2745 Alexey Kopytov	2009-02-14
      Fixed bdb_gis and ndb_gis test failures in PB introduced by the patch for bug #21205.

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/66337

2746 Alexey Kopytov	2009-02-14
      Fixed memory_func_view test failure introduced by the patch for bug #21205.

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/66339

2746 Alexey Kopytov	2009-02-14
      Fixed several test failures in the funcs_1 suite introduced by the patch for bug #21205.

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/66342

2799 Alexey Kopytov	2009-02-14
      Fixed parser test failure introduced by the patch for bug #21205.

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/66388

2748 Alexey Kopytov	2009-02-15
      Fixed PB failures on IA64 hosts introduced by the patch for bug #21205.

Pushed into 6.0.10-alpha (revid:sergey.glukhov@sun.com-20090218125737-5y5b2xo3duo1wlvo) (version source revid:sergey.glukhov@sun.com-20090216145310-eo8xpvq2je0m3jkv) (merge vers: 6.0.10-alpha) (pib:6)

No changelog entry needed for 6.0 because the problem does not exist there. (Correct?)

Setting report to NDI pending push into 5.0.x/5.1.x.

Pushed into 5.0.79 (revid:joro@sun.com-20090309135922-a0di9ebkxoj4d4wv) (version source revid:alexey.kopytov@sun.com-20090215092608-oft43gyp1vihsyle) (merge vers: 5.0.79) (pib:6)

Noted in 5.0.79 changelog.

Floating-point numbers could be handled with different numbers of
digits depending on whether the text or prepared-statement protocol
was used.

Setting report to NDI pending push into 5.1.x.

Pushed into 5.1.33 (revid:joro@sun.com-20090313111355-7bsi1hgkvrg8pdds) (version source revid:vvaintroub@mysql.com-20090218093153-sjzxf01i4ezte0xp) (merge vers: 5.1.33) (pib:6)

Noted in 5.1.33 changelog.

Setting report to NDI pending push into 6.0.x.

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)

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)

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)

Closing. No changelog entry needed for 6.0 because the problem does not exist there.