MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:April 18 1999 8:42pm
Subject:Re: Serious sum() bug in MySQL 3.22.21
View as plain text  

>>>>> "Michael" == Michael R Gile <gilem@stripped> writes:

>> Hi!
>> This is probably a bug in the FreeBSD math library.

Michael> as you can see, monty doesn't like freebsd (and is very quick to blame it)

As I at the same time got a mail that stated 'Works find on intel
linux 2.0.36', I was inclined to think this was a precision bug in the atof()
function.  I don't have anything against FreeBsd.

Michael> however, i get the same (-0.00) result on the following systems
Michael> yes, i know they are not the most recent version, but should at least indicate
> that it isn't just a "freebsd" bug

Michael> freebsd 2.2.7 Mysql 3.22.14b-gamma
Michael> RedHat Linux 5.2 Mysql 3.22.10-beta
Michael> solaris 2.5 Mysql 3.21.33b

Michael> (note that the `dc' application produces 0 in all cases, not -0.00)

>> Note that its common practice in most languages to NEVER compare a
>> floating point value that may have fractions with '=', but instead
>> always use:
>> total between -0.5 and 0.5

Michael> this is good advice. why do you not use it in Mysql (i take it this is the
> question you answer next)?

>> Note that in MySQL 3.23 you will have not fixed point float fields, so
>> in this case the sum above will be a very small value <> 0
>> I think that if I change the '=' compare in MySQL to be 'smarter' it
>> will brake more applications than it will fix :(

Michael> don't you think that *NOT* having fixed point float fields will break more
> applications than it will fix?  it is my understanding that all many SQL applications
> depend on the fact that the values are fixed point.

The problem is not that easy to solve genreally within MySQL.  If
MySQL did some rounding when doing comparisons you will get problems
with queries like:

SELECT * from table_name WHERE floating_point_value > 0


SELECT * from table_name WHERE floating_point_value = 0

The same row will be found in both of the above queries !
(This is even more serious than the original problem)

Note that if you declare your column as:

amount decimal(10,2) 

This means that the stored value will be exact.

This doesn't guarantee that the sum() of the values will be exact or
even that a comparison of type:

select * from table where amount = 10.95

will find any rows.  This is because 10.50 is here given as an
floating point value, which is not an exact value for a computer.

(MySQL for example calculates sum() with double precision)

I shall add to crash-me a test how different databases solves this,
but I fear the result is as it's in other languages:

You should never compare a floating point value with =


PS: If anyone can give me a set of rules that solves this and will
    ALWAYS work, I am more than happy to implement these.

    I have thought about defining the comparison for fixed floating
    point values to:

    floor(value1 * 10^decimals) op floor(value2 * 10^decimals)

    but this will only shift the precision and give other problems
    with larger decimal values.
Serious sum() bug in MySQL 3.22.21tcobb18 Apr
  • Re: Serious sum() bug in MySQL 3.22.21Mike Machado18 Apr
  • Serious sum() bug in MySQL 3.22.21Michael Widenius18 Apr
    • Re: Serious sum() bug in MySQL 3.22.21Michael R. Gile18 Apr
      • Re: Serious sum() bug in MySQL 3.22.21Michael Widenius19 Apr
        • Re: Serious sum() bug in MySQL 3.22.21Michael R. Gile19 Apr
  • Re: Serious sum() bug in MySQL 3.22.21Barry18 Apr
  • Re: Seriously Disruptive DBA Jonathan G. Lampe24 Aug
    • Re: Seriously Disruptive DBAMark Schoonover24 Aug