MySQL Bugs: #22129: A small double precision number becomes zero

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

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)

Thank you for the bug report. Verified as described.

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

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

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

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)

Available in 5.0.26.

Available in 5.1.12-beta.

Available in 4.1.22.

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.

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)

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.

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.

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.