>>>>> "Michael" == Michael R Gile <gilem@stripped> writes:
>> 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:
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.