Subject Bug: "-0" <> "0" in FB indexes !!!
Author bjorgeitteno
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 ?