MySQL Bugs: #26788: mysqld (debug) aborts when inserting specific numbers into char fields

Bug #26788	mysqld (debug) aborts when inserting specific numbers into char fields
Submitted:	2 Mar 2007 8:21	Modified:	28 Apr 2010 1:54
Reporter:	Daniel Fischer	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	all current	OS:	Any (all)
Assigned to:	Alexey Kopytov	CPU Architecture:	Any

Description:
Mysqld sometimes calculates a wrong number of digits for the result of pretty-printing a floating point number when storing it in a char field.

Debug-mode mysqld aborts because of a failed assertion, non-debug mysqld truncates the result.

This appears to happen on all platforms, however the debug mysqld doesn't seem to abort on this on Linux like it does on almost all our UNIX hosts. Non-debug mysqld does show the wrong results on Linux.

How to repeat:
mysql> create table t1 (a char(20));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t1 values (1.225e-05);

Debug mysqld (configure --with-debug) aborts at this point, non-debug continues as follows:

Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select a+0 from t1;
+-------+
| a+0   |
+-------+
| 1.225 | 
+-------+
1 row in set (0.00 sec)

Suggested fix:
Fix pretty-printing in sql/field.cc Field_str::store(double). It doesn't quite use

My suggested fix got truncated. It was meant to say that Field_str::store(double) doesn't quite use the same rules for deciding when scientific notation is going to be used. When sprintf uses scientific notation while store() thinks it won't, store() is going to underestimate the number of characters in the result.

Maybe related to Bug #24657 ?

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

ChangeSet@1.2623, 2007-03-30 00:53:01-07:00, igor@olga.mysql.com +3 -0
  Fixed bug #26788: an assertion abort the function  Field_str::store
  that converts double values into strings for a character field.
  It chooses the most precise and most compact the representation
  for any double value. If however the the field is not long enough
  to hold any meaningful representation the function truncates it
  and issues a warning message.
  In some cases the string representations were incorrect. This is
  fixed by the patch.

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

ChangeSet@1.2623, 2007-04-01 01:40:33-07:00, igor@olga.mysql.com +4 -0
  Fixed bug #26788: an assertion abort the function  Field_str::store
  that converts double values into strings for a character field.
  It chooses the most precise and most compact the representation
  for any double value. If however the the field is not long enough
  to hold any meaningful representation the function truncates it
  and issues a warning message.
  In some cases the string representations were incorrect. This is
  fixed by the patch.

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

ChangeSet@1.2623, 2007-04-02 01:42:14-07:00, igor@olga.mysql.com +7 -0
  Fixed bug #26788: an assertion abort the function  Field_str::store
  that converts double values into strings for a character field.
  It chooses the most precise and most compact the representation
  for any double value. If however the the field is not long enough
  to hold any meaningful representation the function truncates it
  and issues a warning message.
  In some cases the string representations were incorrect. This is
  fixed by the patch.

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

ChangeSet@1.2527, 2007-10-08 12:12:16+04:00, kaa@polly.(none) +3 -0
  Fix for bug #26788 "mysqld (debug) aborts when inserting specific numbers into char fields".
  
  Rewrote the code that determines what 'precision' argument should be passed to sprintf() to fit the string representation of the input number into the field.
  We get finer control over conversion by pre-calculating the exponent, so we are able to determine which conversion format, 'e' or 'f', will be used by sprintf().

Another testcase. Causes assertion in debug build:
-------------------------------------------------

drop table if exists `t1`;
create table `t1` (a int primary key auto_increment,`col001` varchar (10))engine=innodb;
insert into `t1` set `col001` = ' ';
insert into t1(`col001`) select (cot(`a`)) from `t1`;

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

ChangeSet@1.2527, 2007-11-21 09:44:35+03:00, kaa@polly.(none) +3 -0
  Fix for bug #26788 "mysqld (debug) aborts when inserting specific
  numbers into char fields".
  
  Rewrote the code that determines what 'precision' argument should be
  passed to sprintf() to fit the string representation of the input number
  into the field.
  We get finer control over conversion by pre-calculating the exponent, so
  we are able to determine which conversion format, 'e' or 'f', will be
  used by sprintf().

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

ChangeSet@1.2527, 2007-11-29 17:26:34+03:00, kaa@polly.(none) +3 -0
  Fix for bug #26788 "mysqld (debug) aborts when inserting specific
  numbers into char fields" and bug #12860 "Difference in zero padding of
  exponent between Unix and Windows"
  
  Rewrote the code that determines what 'precision' argument should be
  passed to sprintf() to fit the string representation of the input number
  into the field.
  We get finer control over conversion by pre-calculating the exponent, so
  we are able to determine which conversion format, 'e' or 'f', will be
  used by sprintf().
  We also remove the leading zero from the exponent on Windows to make it
  compatible with the sprintf() output on other platforms.

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

ChangeSet@1.2527, 2007-11-29 17:31:29+03:00, kaa@polly.(none) +6 -0
  Fix for bug #26788 "mysqld (debug) aborts when inserting specific
  numbers into char fields" and bug #12860 "Difference in zero padding of
  exponent between Unix and Windows"
  
  Rewrote the code that determines what 'precision' argument should be
  passed to sprintf() to fit the string representation of the input number
  into the field.
  We get finer control over conversion by pre-calculating the exponent, so
  we are able to determine which conversion format, 'e' or 'f', will be
  used by sprintf().
  We also remove the leading zero from the exponent on Windows to make it
  compatible with the sprintf() output on other platforms.

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

ChangeSet@1.2527, 2007-12-01 10:05:59+03:00, kaa@polly.(none) +6 -0
  Fix for bug #26788 "mysqld (debug) aborts when inserting specific
  numbers into char fields" and bug #12860 "Difference in zero padding of
  exponent between Unix and Windows"
  
  Rewrote the code that determines what 'precision' argument should be
  passed to sprintf() to fit the string representation of the input number
  into the field.
  We get finer control over conversion by pre-calculating the exponent, so
  we are able to determine which conversion format, 'e' or 'f', will be
  used by sprintf().
  We also remove the leading zero from the exponent on Windows to make it
  compatible with the sprintf() output on other platforms.

Pushed into 6.0.5-alpha

Pushed into 5.1.23-rc

Pushed into 5.0.54

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 bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented bugfix in the 5.0.54, 5.1.23, and 6.0.5 changelogs as follows:

        mysqld sometimes miscalculated the number of
        digits required when storing a floating-point number in a
        CHAR column. This caused the value to be
        truncated, or (when using a debug build) caused the server to
        crash.

Pushed into 6.0.5-alpha

Setting report to NDI pending push to Celosia.

Setting report to Need Merge pending push of Celosia to release tree.

Pushed into 5.5.3-m3 (revid:alik@sun.com-20100306103849-hha31z2enhh7jwt3) (version source revid:vvaintroub@fedora12-20091225154921-x25a5pyw1pxiwobv) (merge vers: 5.5.99) (pib:16)

Noted in 5.5.3 changelog.

Pushed into 5.5.5-m3 (revid:alik@sun.com-20100427093804-a2k3rrjpwu5jegu8) (version source revid:alik@sun.com-20100427093804-a2k3rrjpwu5jegu8) (merge vers: 5.5.5-m3) (pib:16)

Pushed into mysql-next-mr (revid:alik@sun.com-20100427094036-38frbg3famdlvjup) (version source revid:alik@sun.com-20100427093825-92wc8b22d4yg34ju) (pib:16)

Already fixed in 5.5.x.