> > /* INSERT INTO FLOAT_ZERO_TEST (PK, DV) VALUES (-0.0, -0.0);
> > causes duplicate exception */
>
> This *should* word. IEEE floats distiguish between -0 and +0.
> I DO consider this a BUG.

It seems nobody read my previous post, so once again
0 ... this is INTEGER where only one kind of zero exists, i.e. 0 = -0

0.0 ... this is DECIMAL/NUMERIC(x,1), which uses INTEGER as underlying
storage format, i.e. no negative zero, i.e. 0.0 = -0.0

0e0 ... this is DOUBLE PRECISION zero, that uses IEEE format where
negative zero exists, so 0e0 <> -0e0

The bug is that Firebird uses two different rules for comparing float values -
the result depends on (non)existence of index. E.g.


SQL> CREATE TABLE T(I INTEGER, D DOUBLE PRECISION);
SQL> INSERT INTO T VALUES (1,1);
SQL> INSERT INTO T VALUES (0,0);
SQL> INSERT INTO T VALUES (-1,-1);
SQL> INSERT INTO T VALUES (10,-0e0);
SQL>
SQL> SELECT * FROM T WHERE D=0;
I D
============ =======================
0 0.0000000000000000
10 0.0000000000000000

SQL> SELECT * FROM T WHERE D=-0e0;
I D
============ =======================
0 0.0000000000000000
10 0.0000000000000000 .... without index both kinds of zero are equal.

SQL> CREATE INDEX IX ON T(D);

SQL> SELECT * FROM T WHERE D=0;
I D
============ =======================
0 0.0000000000000000

SQL> SELECT * FROM T WHERE D=-0e0;
I D
============ =======================
10 0.0000000000000000 .... with index negative zero is not equal to positive zero

SQL> SELECT * FROM T ORDER BY D;
I D
============ =======================
10 0.0000000000000000 .... with index negative zero is not equal to positive
zero,
and is sorted like negative infinity, instead of right before positive zero
-1 -1.000000000000000
0 0.0000000000000000
1 1.000000000000000

> > The "order by DV" works as expected. "order by PK", the indexed column
> definitely lists 0 first. This is wrong however you interpret -0 vs +0.
>
> Okay. This is also a BUG.

Right. It is just result of the way how indexes in Firebird internally work,
i.e. IEEE values is not treated as number, but just like byte stream.

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