[yunqa.de] Re: Unique Index failing?
- From: Delphi Inspiration <delphi@xxxxxxxx>
- To: yunqa@xxxxxxxxxxxxx
- Date: Sat, 31 Oct 2009 15:24:16 +0100
At 13:31 31.10.2009, Jamie wrote:
>I have a problem where I am seeing duplicate values in a table, which
>seem to contradict a unique index I created at the time the table was
>created. I am seeing non-unique combinations of a real date/time value
>and an integer value which make up the index.
[ snip ]
>I am ending up with apparently identical date/time stamps for a given
>integer value, such as two occurrences of "4, 2455104.1667". The
>duplicate times all seem to be at 16:00, 17:30, 19:00 or 22:00.
>
>I assume that either the raw floating point values are fractionally
>different and I am failing to find a way of displaying them with
>sufficient accuracy to display the difference
You can never count on double types to determine equality. This is true for
expressions (xDobule = yDouble) as well as for UNIQUE INDEXes. Reason is that
SQLite uses regular floating point comparisons. Database comparison often
suffer from the fact that floating point numbers are just an approximate
representation of their actual value. This results in accuracy problems as
described here: http://en.wikipedia.org/wiki/Floating_point#Accuracy_problems.
>(is there a way to see the raw value stored?),
Double type values are stored and retrieved to and from SQLite unchanged. You
can of access any double's memory by dereferencing a pointer to its variable
like this: PDouble(d)^.
>or that I am managing to write some data in a way the circumvents the index?
No, this is not possible.
>Any suggestions would be appreciated.
I suggest you add a floating point collation sequence which compares up to a
certain number of digits precision only. Here is a the function from my
DISQLite3 test suite which does just that:
function FloatsEqual(const x, y, Delta: Extended): Boolean;
begin
Result := x = y;
if not Result then
try
if y = 0 then
Result := Abs(1 - y / x) <= Delta
else
Result := Abs(1 - x / y) <= Delta;
except
Result := False; // catch real rare overflow e.g. 1.0e3000/1.0e-3000
end
end;
The above function is inspired by the one below, taken from the Jedi JCL
jclMath.pas:
function FloatsEqual(const X, Y: Float): Boolean;
begin
try
if Y = 0 then
// catch exact equality
Result := (X = Y) or (Abs(1 - Y/X ) <= PrecisionTolerance)
else
// catch exact equality
Result := (X = Y) or (Abs(1 - X/Y ) <= PrecisionTolerance);
except
Result := False; // catch real rare overflow e.g. 1.0e3000/1.0e-3000
end
end;
For your date comparisons, you need to find the proper Delta /
PrecisionTolerance based on your DateTime precision requirements. Then register
the collation sequence with sqlite3_create_collation(), where you can also pass
the Precision Tolerance if your callback function supports it. At last,
reference the registered collation sequence when you create your table field
and / or index.
Ralf
_______________________________________________
Delphi Inspiration mailing list
yunqa@xxxxxxxxxxxxx
//www.freelists.org/list/yunqa
Other related posts:
