float numbers

Ho everyone

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 ?



--
echo "Hello World "

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/...-overview.html

--
Dan Nelson
dnelson@allantgroup.com

At 03:29 PM 11/17/2006, Ahmad Al-Twaijiry wrote:
>Ho everyone
>
>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 ?

Ho Ho Ho,

Float only uses 4 bytes and does not have much precision so you'll get
rounding. Try DOUBLE instead.

Mike

Thanks Dan & Mos

I have many tables that use float (in production database), if I
convert all of the float column to DOUBLE or DECIMAL (using alter ),
is there any impact or anything I should know that could happen to me
?

is there any page that describe the differences between FLOAT, DECIMAL
and DOUBLE in MySQL 5.0.* ?

Thanks

On 11/18/06, mos <mos99@fastmail.fm> wrote:
> At 03:29 PM 11/17/2006, Ahmad Al-Twaijiry wrote:
> >Ho everyone
> >
> >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 ?
>
> Ho Ho Ho,
>
> Float only uses 4 bytes and does not have much precision so you'll get
> rounding. Try DOUBLE instead.
>
> Mike
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=ahmadt@gmail.com
>
>


--
echo "Hello World "

In the last episode (Nov 18), Ahmad Al-Twaijiry said:
> Thanks Dan & Mos
>
> I have many tables that use float (in production database), if I
> convert all of the float column to DOUBLE or DECIMAL (using alter ),
> is there any impact or anything I should know that could happen to
> me?
>
> is there any page that describe the differences between FLOAT, DECIMAL
> and DOUBLE in MySQL 5.0.* ?

In addition to the url I listed in my first post

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

there's also:

http://dev.mysql.com/doc/refman/5.0/...ith-float.html
http://dev.mysql.com/doc/refman/5.0/...ric-types.html
http://dev.mysql.com/doc/refman/5.0/...sion-math.html

--
Dan Nelson
dnelson@allantgroup.com

Dan Nelson wrote:
> 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.
>
That isn't "exactly" true. If you were to add the word "always" between
can't and be, it would have been true.

There are some base 10 fractions that can't be expressed exactly in base
2, and some base 2 fractions that can't be expressed exactly in base
10. However, there are also fractions that can be stored exactly in
both base 10 and base 2, such as .5, .25, .125, .75, .625. You get the
idea. I don't remember any examples of the first two cases, and am too
lazy to figure any out, but they do exist.




--
Chris W
KE5GIX

"Protect your digital freedom and privacy, eliminate DRM,
learn more at http://www.defectivebydesign.org/what_is_drm"

Gift Giving Made Easy
Get the gifts you want &
give the gifts they want
One stop wish list for any gift,
from anywhere, for any occasion!
http://thewishzone.com

Hi Ahmad,

On 11/18/06, Ahmad Al-Twaijiry <ahmadt@gmail.com> wrote:
> I have many tables that use float (in production database), if I
> convert all of the float column to DOUBLE or DECIMAL (using alter ),
> is there any impact or anything I should know that could happen to me
> ?

You'll get a slight slow-down in performance - a trade-off you will
make if you choose DECIMAL instead of FLOAT. Even if you choose
DOUBLE, there is a risk of rounding errors. Try converting/casting
the integer '1234567890' to float/double and assign the resulting
float to an integer variable - then see what difference it makes.
With DECIMAL you get accurate monetary calculations but slightly
slower speed.

--
Asif

Thanks all

I already switched all my FLOAT columns to DECIMAL and everything is
working fine

Thanks Again.

On 11/20/06, Asif Lodhi <itmailster@gmail.com> wrote:
> Hi Ahmad,
>
> On 11/18/06, Ahmad Al-Twaijiry <ahmadt@gmail.com> wrote:
> > I have many tables that use float (in production database), if I
> > convert all of the float column to DOUBLE or DECIMAL (using alter ),
> > is there any impact or anything I should know that could happen to me
> > ?
>
> You'll get a slight slow-down in performance - a trade-off you will
> make if you choose DECIMAL instead of FLOAT. Even if you choose
> DOUBLE, there is a risk of rounding errors. Try converting/casting
> the integer '1234567890' to float/double and assign the resulting
> float to an integer variable - then see what difference it makes.
> With DECIMAL you get accurate monetary calculations but slightly
> slower speed.
>
> --
> Asif
>


--
echo "Hello World "

You will see the same rounding problems if you alter a FLOAT to either a
DOUBLE or a DECIMAL. Unless all of your original data had integer values,
it's too late unless you force rounding yourself.

For example, if you alter the FLOAT field to DECIMAL(23,3), add .005 to each
number, and then alter the field to DECIMAL(22,2) you will wind up with a
value that is exactly what you want. (This applies only to positive numbers,
for negative numbers you need to decide if you want to round up towards 0 or
down away from 0.)

Using DOUBLE will not solve the problem, it will merely make it less likely
to cause trouble.

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341


> -----Original Message-----
> From: Ahmad Al-Twaijiry [mailto:ahmadt@gmail.com]
> Sent: Friday, November 17, 2006 5:34 PM
> To: mos; Dan Nelson
> Cc: mysql@lists.mysql.com
> Subject: Re: float numbers
>
> Thanks Dan & Mos
>
> I have many tables that use float (in production database), if I
> convert all of the float column to DOUBLE or DECIMAL (using alter ),
> is there any impact or anything I should know that could happen to me
> ?
>
> is there any page that describe the differences between FLOAT, DECIMAL
> and DOUBLE in MySQL 5.0.* ?
>
> Thanks
>
> On 11/18/06, mos <mos99@fastmail.fm> wrote:
> > At 03:29 PM 11/17/2006, Ahmad Al-Twaijiry wrote:
> > >Ho everyone
> > >
> > >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 ?
> >
> > Ho Ho Ho,
> >
> > Float only uses 4 bytes and does not have much precision so
> you'll get
> > rounding. Try DOUBLE instead.
> >
> > Mike
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> http://lists.mysql.com/mysql?unsub=ahmadt@gmail.com
> >
> >
>
>
> --
> echo "Hello World "
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=j...e-infoshop.com
>
>

Hi!

SQL Maestro Group is happy to announce a new version of our powerful GUI
solution for MySQL server administration and database development.
http://www.sqlmaestro.com/products/m...stro/download/

Also we're happy to grant you a time-limited 25% discount on all our
products and bundles. Don't miss this unique chance to get our software at a
special Christmas price!
http://www.sqlmaestro.com/purchase/

New version features:

1. The main feature of the new version is the tabbed MDI interface
implementation (like Mozilla Firefox). Here are some its advantages:
- you can drag-n-drop tabs to change their order;
- you can close the current tab pressing the mouse wheel (or the middle
mouse button);
- you can drag-n-drop database objects or selected text between tabs;
- each tab has a popup menu, which allows you to close current tab, close
all tabs or close all the tab except selected one (close other tabs).

2. SQL Script Editor: if a user opens a file larger than 100K, SQL Maestro
will suggest him to execute the script file without opening it in the
editor. Of course it is also possible to execute any script file using this
way: just press the "Execute script from file" link on the Script Editor
navigation bar and specify the filename.

3. SQL Maestro now stores parameters history for procedure/function
execution so you can choose a set of already used values instead of entering
them manually.

4. Database profiles are now grouped by host+port+login combination rather
than host+port in the previous versions
// ....
10. Data grid: the speed of data loading was significantly increased.

Full press release is available at:
http://www.sqlmaestro.com/news/company/3374/

Background information:
SQL Maestro Group is engaged in developing database administration and
management tools for MySQL, SQL Server, PostgreSQL, Oracle, SQLite, Firebird
and MaxDB providing the highest performance, scalability and reliability to
meet the requirements of today's database applications.

Thank you for your attention.

Sincerely yours,
SQL Maestro Group
http://www.sqlmaestro.com