Subject Re: [firebird-support] Bug: "-0" <> "0" in FB indexes !!!
Author Helen Borrie
At 05:35 PM 25/03/2004 +0000, you wrote:
>A bug present in IB 5.6 & all FB versions:

Not a bug - it's inherent in float types. If you want exact numerics, use
DECIMAL, NUMERIC or one of the integer types.

>- A numeric field (happens both with FLOAT and DOUBLE PRECISION) can
>in fact hold both "zero" and "minus zero"
>- Normally, you won't get problems with this. But: When an index is
>used, the two zero "variants" are no longer equal.

Don't use indexes on float types. And certainly don't use them for keys -
you will never guarantee to match two "equal" values.

>- The error is encountered when issuing SQLs like these (and when an
>index is used):

Since zero is rarely stored as exact zero, where field = 0 is unlikely to
pick up many matches.

>A simple script to reproduce the error (remove comments and execute
>in order to see for yourself):
>--drop table test
>--create table test(val DOUBLE PRECISION)
>/*insert into test values (0);
>insert into test values (0);
>insert into test values (10000001);
>insert into test values (1.12);
>insert into test values (-1.1);
>insert into test values (-1.121);
>insert into test values (-1000000);
>--select * from test order by val
>--update test set val = -1*Val
>--select * from test order by val
>--create index val_ix on test(val)
>--select * from test order by val
>After the last select, you'll have the "0" values first, then the
>negative ones, then the positive ones...

That's as expected. The zero isn't zero, it's a very small positive
number, e.g. 0.00000000000001. When you negate it, it becomes a very small
negative number, e.g. -0.00000000000001 - so it's smaller than all of the
other negative numbers.

>...and one will experience one more strange thing that makes perfect
>sense: Every second time you do a
> update test set val = -1*Val
> will be OK again (minus * minus = plus) !!
>This is tested with Firebird 1.02, 1.03 & 1.5
>Anyone knowing this bug ?

You can have a lot of fun with floating point numbers on computers...
Don't use float types for storing money values.
"Use fixed numerics for things you count, float types for things you measure."