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):
> SELECT * FROM TBL ORDER BY [NUMERIC_FIELD]
> SELECT COUNT(*), [NUMERIC_FIELD] FROM TBL GROUP BY [NUMERIC_FIELD]
> SELECT * FROM TBL WHERE [NUMERIC_FIELD] = 0

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
>
>...it 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."

/heLen