Bug #22129 A small double precision number becomes zero
Submitted: 8 Sep 2006 20:45 Modified: 16 Apr 2007 11:40
Reporter: Peter Gulutzan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.1/5.0/4.1BK OS:Linux (SUSE 10.0 / 64-bit)
Assigned to: Alexey Kopytov CPU Architecture:Any

[8 Sep 2006 20:45] Peter Gulutzan
Description:
I insert a small number, with 8 or more digits
after the decimal point, with exponent -300,
in a DOUBLE PRECISION column. It becomes 0.
But the number is greater than other numbers
which MySQL can store and which are greater
than zero.

Perhaps this could be solved along with
#21497 DOUBLE truncated to unusable value
which is for large numbers.

How to repeat:
mysql> create table tf (s1 double precision);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into tf values (1e-308),(1.00000001e-300);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from tf;
+--------+
| s1     |
+--------+
| 1e-308 |
|      0 |
+--------+
2 rows in set (0.00 sec)
[8 Sep 2006 23:34] MySQL Verification Team
Thank you for the bug report. Verified as described.
[21 Sep 2006 7:54] 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/12315

ChangeSet@1.2541, 2006-09-21 11:50:03+04:00, kaa@polly.local +3 -0
  Fixed bug #22129: A small double precision number becomes zero
  Better checks for underflow/overflow
[21 Sep 2006 11: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/12329

ChangeSet@1.2541, 2006-09-21 15:00:37+04:00, kaa@polly.local +3 -0
  Fixed bug #22129: A small double precision number becomes zero
  Better checks for underflow/overflow
[22 Sep 2006 15:24] 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/12413

ChangeSet@1.2541, 2006-09-22 19:23:58+04:00, kaa@polly.local +3 -0
  Fixed bug #22129: A small double precision number becomes zero
  Better checks for underflow/overflow
[25 Sep 2006 8:53] 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/12460

ChangeSet@1.2236, 2006-09-25 12:50:32+04:00, kaa@polly.local +1 -0
  Fixed broken 'strict' test which relied on incorrect behaviour of my_strtod() (fixed in bug #22129)
[3 Oct 2006 20:01] Chad MILLER
Available in 5.0.26.
[3 Oct 2006 20:15] Chad MILLER
Available in 5.1.12-beta.
[4 Oct 2006 13:56] Chad MILLER
Available in 4.1.22.
[4 Oct 2006 17:20] Peter Gulutzan
Now the test case looks like this:

mysql> create table tf (s1 double precision);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into tf values (1e-308),(1.00000001e-300);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from tf;
+-----------------+
| s1              |
+-----------------+
|               0 |
| 1.00000001e-300 |
+-----------------+
2 rows in set (0.01 sec)

Thus, 1.00000001e-300 is now okay, but 1e-308
-- which was accepted before -- is not. This
looks like a change which was not the subject
of the bug. I am aware that 1e-308 is below
the recommended limit, and below the limit that
the MySQL Reference Manual says:
http://dev.mysql.com/doc/refman/5.0/en/numeric-type-overview.html
2.2250738585072014E-308. But 1e-308 was possible
before in my environment.
[8 Nov 2006 16:07] 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/15028

ChangeSet@1.2611, 2006-11-08 19:07:21+03:00, kaa@polly.local +2 -0
  Removed the underflow check (bug #22129)
[11 Dec 2006 4:06] Paul DuBois
Noted in 4.1.23, 5.0.32, 5.1.15 changelogs.

Some small double precision numbers (such as 1.00000001e-300) that
should have been accepted were truncated to zero.
[21 Dec 2006 18:30] Daniel Fischer
I'm re-opening this bug because with 5.0.32, on some platforms the type_float test case (which tests this bug) fails like so:

type_float                     [ fail ]

Errors are (from /PATH/mysqltest-time) :
mysqltest: Result length mismatch
(the last lines may be the most important ones)
Below are the diffs between actual and expected results:
-------------------------------------------------------
*** r/type_float.result
--- r/type_float.reject
***************
*** 264,270
  drop table t1,t2,t3;
  select 1e-308, 1.00000001e-300, 100000000e-300;
  1e-308        1.00000001e-300 100000000e-300
! 1e-308        1.00000001e-300 1e-292
  select 10e307;
  10e307
  1e+308
--- 264,270
  drop table t1,t2,t3;
  select 1e-308, 1.00000001e-300, 100000000e-300;
  1e-308        1.00000001e-300 100000000e-300
! 0     1.00000001e-300 1e-292
  select 10e307;
  10e307
  1e+308
-------------------------------------------------------

Encountered with 5.0.32 on RHEL3/4 and SLES 9 on ia64.
[16 Apr 2007 11:40] Alexey Kopytov
Closing this bug as discussed with PeterG. Denormalized floating point numbers are not supported or handled correctly on, which is the subject of bug #26914.