Issue Details (XML | Word | Printable)

Key: CORE-1813
Type: Bug Bug
Status: Open Open
Priority: Major Major
Assignee: Unassigned
Reporter: Sergei Rubanik
Votes: 0
Watchers: 0
Operations

If you were logged in you would be able to see more operations.
Firebird Core

Double precision 'minus zero' anomaly in indices

Created: 01/Apr/08 06:05 AM   Updated: 01/Apr/08 06:13 AM
Component/s: Engine
Affects Version/s: 2.0.3
Fix Version/s: None

File Attachments: 1. Text File create_db.sql (0.6 kB)

Environment:
OS: Windows Server 2003 Standard Edition SP 2, Windows XP Professional SP2
Server Version: WI-V2.0.3.12981 Firebird 2.0
Server Implementation: Firebird/x86/Windows NT
Both SuperServer and Classic mode are affected.
Client Library Version: 2.0.3.12981
ODS Version: 11.0
SQL Dialect 1 or 3.


 Description  « Hide
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.


 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
There are no comments yet on this issue.