You are right.
When multiplying zero and -1 in floating poing, i.e. something like

-1 * float_field
or
-1 * 0e0
or
-1e0 * 0

the result has not all bytes zero (like when you store regular 0),
but its highest bit is set to one. That "thing" than causes problems
both with sorting (like in your example), but also with searching
(simple "WHERE val=0" will also not find the record using index).

In fact, IEEE standard allows both positive and negative zeroes
(and some other exotic values, like positive/negative infinity
or NaN=not-a-number) !
The problem is that Firebird should treat these values consistently
(which it currently does not). Negative-zero values should be either
forbidden (converted to normal zero internally), or fully supported
(i.e. printed as "-0", correctly sorted, correctly treated in searching)

Note that in Firebird you can directly write negative-zero literal.
-0 ... this is normal zero (because it is integer literal),
-0e0 ... this is negative-zero (because it is floating point value) !!

Ivan
http://www.volny.cz/iprenosil/interbase/

----- Original Message -----
From: "bjorgeitteno" <bjorge@...>
To: <firebird-support@yahoogroups.com>
Sent: Thursday, March 25, 2004 6:35 PM
Subject: [firebird-support] Bug: "-0" <> "0" in FB indexes !!!


> A bug present in IB 5.6 & all FB versions:
>
> - 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.
> - 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
>
> 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...
>
> ...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 ?