MySQL Bugs: #60876: A Floating Point Issue

Bug #60876	A Floating Point Issue
Submitted:	15 Apr 2011 6:20	Modified:	23 Apr 2011 16:47
Reporter:	Devang Modi	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	mysql Ver 14.12 Distrib 5.0.45	OS:	Linux (Red Hat Enterprise Linux Server release 5.3 )
Assigned to:		CPU Architecture:	Any
Tags:	and, between, difference, field, FLOAT, IN, inserted, selected, value

Description:
Dear Sir;

We are using MySQL on RHEL server since last 7 years and we have very big database with MySQL.
Since last few year we were a strange issue particularly with one table.
In this issue when we inserted value in said table and when we try to fetch same record value gets change in between.
The table name is "productinvoice" and value is getting change in field name called "totalamount" that is having type "float".
For your reference I am putting table structure below.

CREATE TABLE `productinvoice` (
  `recid` bigint(20) NOT NULL auto_increment,
  `clientlocid` bigint(20) NOT NULL default '0',
  `customerlocid` bigint(20) NOT NULL default '0',
  `productid` bigint(20) default NULL,
  `vendorlocid` bigint(20) default NULL,
  `type` char(1) NOT NULL default '',
  `source` char(1) NOT NULL default 'T',
  `invdate` date NOT NULL default '0000-00-00',
  `periodenddate` date NOT NULL default '0000-00-00',
  `totalamount` float default NULL,
  `salestax` char(1) NOT NULL default 'N',
  `billingflag` char(1) NOT NULL default 'N',
  `comment` varchar(250) default NULL,
  `piainvoice` mediumtext,
  `specificroutesid` bigint(20) default NULL,
  `pmtcode` char(1) NOT NULL default 'N',
  `archupdt` char(1) default 'N',
  PRIMARY KEY  (`recid`),
  KEY `vendorlocid` (`vendorlocid`),
  KEY `productid` (`productid`),
  KEY `type` (`type`),
  KEY `source` (`source`),
  KEY `invdate` (`invdate`),
  KEY `periodenddate` (`periodenddate`),
  KEY `billingflag` (`billingflag`),
  KEY `clientlocid` (`clientlocid`),
  KEY `customerlocid` (`customerlocid`),
  KEY `specificroutesid` (`specificroutesid`),
  KEY `archupdt` (`archupdt`)
) ENGINE=InnoDB AUTO_INCREMENT=3347548 DEFAULT CHARSET=latin1;

I describing steps in "How to repeat:" column so kindly refer it.

Queries/Questions:
1. Why value is not same at Step-3 ? I expected value should be 24.900001
2. Why value is not "24.900001" at step-4?
3. Why value is "24.9000015" and not "24.900001" a step-5, how digit "5" added to value?

I also tried it on more than server with different processor.
On live server we have 4Xeon processors with 32GB RAM, I tried it on AMD Phenome Black Edition with 16 GB.
On live we are using RHEL-5.3 while on test server we are using RHEL-5.6 with MySQL - mysql  Ver 14.12 Distrib 5.0.77 .

But every time I am receiving same result.

I wish your appreciable and valuable help by providing exact reason and remedies for this issue.

thanks a lot

Devang
 

How to repeat:
All under mysql command prompt (cli client):

Step-1 : 
CREATE TABLE `productinvoice` (
  `recid` bigint(20) NOT NULL auto_increment,
  `clientlocid` bigint(20) NOT NULL default '0',
  `customerlocid` bigint(20) NOT NULL default '0',
  `productid` bigint(20) default NULL,
  `vendorlocid` bigint(20) default NULL,
  `type` char(1) NOT NULL default '',
  `source` char(1) NOT NULL default 'T',
  `invdate` date NOT NULL default '0000-00-00',
  `periodenddate` date NOT NULL default '0000-00-00',
  `totalamount` float default NULL,
  `salestax` char(1) NOT NULL default 'N',
  `billingflag` char(1) NOT NULL default 'N',
  `comment` varchar(250) default NULL,
  `piainvoice` mediumtext,
  `specificroutesid` bigint(20) default NULL,
  `pmtcode` char(1) NOT NULL default 'N',
  `archupdt` char(1) default 'N',
  PRIMARY KEY  (`recid`),
  KEY `vendorlocid` (`vendorlocid`),
  KEY `productid` (`productid`),
  KEY `type` (`type`),
  KEY `source` (`source`),
  KEY `invdate` (`invdate`),
  KEY `periodenddate` (`periodenddate`),
  KEY `billingflag` (`billingflag`),
  KEY `clientlocid` (`clientlocid`),
  KEY `customerlocid` (`customerlocid`),
  KEY `specificroutesid` (`specificroutesid`),
  KEY `archupdt` (`archupdt`)
) ENGINE=InnoDB AUTO_INCREMENT=3347548 DEFAULT CHARSET=latin1

Step-2:

INSERT INTO productinvoice(clientlocid, customerlocid, type, invdate, periodenddate, totalamount, billingflag, source) 
values('64601', '66238', 'I', '2011-03-31', '2011-03-27', '24.900001', 'N', 'T');

Step-3:

 select * from productinvoice where recid = <Newly Generated Recid> \G

My Result is:

*************************** 1. row ***************************
           recid: 3347547
     clientlocid: 64601
   customerlocid: 66238
       productid: NULL
     vendorlocid: NULL
            type: I
          source: T
         invdate: 2011-03-31
   periodenddate: 2011-03-27
     totalamount: 24.9
        salestax: N
     billingflag: N
         comment: NULL
      piainvoice: NULL
specificroutesid: NULL
         pmtcode: N
        archupdt: N

Step-4:

select round(totalamount , 6 )  from productinvoice where recid = 3347547;

My result is:

+-------------------------+
| round(totalamount , 6 ) |
+-------------------------+
|               24.900002 | 
+-------------------------+

Step-5:

select round(totalamount , 7 )  from productinvoice where recid = 3347547;

+-------------------------+
| round(totalamount , 7 ) |
+-------------------------+
|              24.9000015 | 
+-------------------------+

Devang

The results you presented are easily repeatable on, say, 5.0.91 on 32-bit Windows XP on Intel CPU:

mysql> select round(totalamount , 7 )  from productinvoice where recid = 3347548
;
+-------------------------+
| round(totalamount , 7 ) |
+-------------------------+
|              24.9000015 |
+-------------------------+
1 row in set (0.00 sec)

mysql> select totalamount * 1000000000  from productinvoice where recid = 334754
8;
+--------------------------+
| totalamount * 1000000000 |
+--------------------------+
|         24900001525.8789 |
+--------------------------+
1 row in set (0.05 sec)

But I think they are expected to some extent and explained in the manual, http://dev.mysql.com/doc/refman/5.0/en/problems-with-float.html. 

Use DECIMAL maybe?

Dear Sir;

Yes, I read that document and I understood it very well but here my worry is "We already have many records in said table, if we will update now data type , can we loose some data ?"

thanks

Devang

You can try to create table with the same column and structure, but with DECIMAL type for the column in question, then INSERT INTO new_table SELECT * FROM old_table and check if data are the same.

In any case, this is not a bug. FLOAT type just works that way, approximate values are stored.