From: Dan Nelson
Date: November 17 2006 10:03pm
Subject: Re: float numbers
List-Archive: http://lists.mysql.com/mysql/203419
Message-Id: <20061117220333.GC7333@dan.emsphone.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

In the last episode (Nov 18), Ahmad Al-Twaijiry said:
> I have a column in a table defined as float
> 
> mynumber float(20,2)
> 
> if we say mynumber column in a row is 1000000 ,  when I run this SQL :
> 
> UPDATE Table SET mynumber=mynumber-100.15
> 
> the mynumber column will be 999900 not 999899.85
> 
> what is the problem ?

It works for me (approximately) in mysql 5.1:

mysql> create table blah ( mynumber float(20,2) );
mysql> insert into blah values ( 1000000 );
mysql> update blah SET mynumber=mynumber-100.15;
mysql> select * from blah;
+-----------+
| mynumber  |
+-----------+
| 999899.88 |
+-----------+
1 row in set (0.02 sec)

Since base-10 fractions can't be represented exactly by a base-2
floating-point number, any fractional value you store will be
imprecisely stored and will cuase rounding errors.  You probably want
to use the DECIMAL type instead of FLOAT.

http://dev.mysql.com/doc/refman/5.0/en/numeric-type-overview.html

-- 
	Dan Nelson
	dnelson@stripped