You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
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.
The text was updated successfully, but these errors were encountered:
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.
The text was updated successfully, but these errors were encountered: