Subject | Re: [firebird-support] Re: Bug: "-0" <> "0" in FB indexes !!! |
---|---|

Author | Ivan Prenosil |

Post date | 2004-03-26T11:50:10Z |

> > /* INSERT INTO FLOAT_ZERO_TEST (PK, DV) VALUES (-0.0, -0.0);It seems nobody read my previous post, so once again

> > causes duplicate exception */

>

> This *should* word. IEEE floats distiguish between -0 and +0.

> I DO consider this a BUG.

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 columnRight. It is just result of the way how indexes in Firebird internally work,

> definitely lists 0 first. This is wrong however you interpret -0 vs +0.

>

> Okay. This is also a BUG.

i.e. IEEE values is not treated as number, but just like byte stream.

Ivan

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