Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Double precision 'minus zero' anomaly in indices [CORE1813] #2243

Open
firebird-automations opened this issue Apr 1, 2008 · 2 comments
Open

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Sergei Rubanik (sergeir)

Attachments:
create_db.sql

The issues is related to comparison of -0E0 and +0E0 using indices and their processing in unique indices.
In live cases -0E0 can be got, for instance, as result of cast(0 as double precision)/cast(-1 as double precision) or just 0/(-1) in SQL Dialect 1.

When indices are not used, this works correctly and -0E0=0E0, -1<-0E0, -1<0E0.

When indices are used, there are following anomalies are observed (can be reproduced on the database prepared with create_db.sql):

A) Incorrect comparison results.

SELECT * FROM RDB$DATABASE WHERE 0E0=-0E0; -- returns a record

-- Selects without indices

SELECT * FROM A WHERE PZ = 0E0 -- returns a record
SELECT * FROM A WHERE PZ = -0E0 -- returns a record
SELECT * FROM A WHERE NZ = 0E0 -- returns a record
SELECT * FROM A WHERE NZ = -0E0 -- returns a record
SELECT * FROM A WHERE PZ > -1E0 -- returns a record
SELECT * FROM A WHERE NZ > -1E0 -- returns a record

-- Selects using indices

SELECT * FROM A WHERE PZI = 0E0 -- returns a record
SELECT * FROM A WHERE PZI = -0E0 -- empty return dataset, which seems to be incorrect
SELECT * FROM A WHERE NZI = 0E0 -- empty return dataset, which seems to be incorrect
SELECT * FROM A WHERE NZI = -0E0 -- returns a record
SELECT * FROM A WHERE PZI > -1E0 -- returns a record
SELECT * FROM A WHERE NZI > -1E0 -- empty return dataset, which seems to be incorrect

however

SELECT * FROM A WHERE NZI < 1E0 -- returns a record

B) Unique index allows coexistence of 0E0 and -0E0.

INSERT INTO B (F1) VALUES (0E0); -- this works
INSERT INTO B (F1) VALUES (-0E0); -- this works too, which seems to be incorrect

And again, comparisons cannot find -0E0 using index:

SELECT * FROM B WHERE F1 > -1E0 -- returns only one record instead of expected two

however

SELECT * FROM B WHERE F1 < 1E0 -- returns two records
SELECT * FROM B WHERE F1 <= 0E0 -- returns two records

but

SELECT * FROM B WHERE F1 < 0E0 -- empty return dataset

We consider index results as incorrect on the basis that IEEE standard defines comparison so that +0=-0.

@firebird-automations
Copy link
Collaborator Author

Modified by: Sergei Rubanik (sergeir)

Attachment: create_db.sql [ 10820 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: Sergei Rubanik (sergeir)

summary: 'Minus zero' anomaly in indices => Double precision 'minus zero' anomaly in indices

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

1 participant