wrong Double value with SQLite REAL

Databases
#1

Xojo 2014r2.1

I’m getting wrong double values from SQLite REAL datatypes columns.
I have a complex database storing a lot of very small weights (up to 8 digit right of decimal point) defined as REAL in the SQLite DB.

When I read the DB the DatabaseField.DoubleValue are not always correct.

I created a test DB with a single file containing a single column:

CREATE TABLE "testDbl" ( "ID" INTEGER NOT NULL PRIMARY KEY , "RealValue" REAL);

I’ve inserted 10 rows as:

INSERT INTO "testDbl" VALUES (1, 0.84); INSERT INTO "testDbl" VALUES (2, 10.1129); INSERT INTO "testDbl" VALUES (3, -1.84); INSERT INTO "testDbl" VALUES (4, -0.53); INSERT INTO "testDbl" VALUES (5, 18.0); INSERT INTO "testDbl" VALUES (6, 10.0); INSERT INTO "testDbl" VALUES (7, 154.0); INSERT INTO "testDbl" VALUES (8, 33580.0); INSERT INTO "testDbl" VALUES (9, 1107.7); INSERT INTO "testDbl" VALUES (10, 0.4874);

If I read this table and move the “Value” column into a double variable I get wrong values for some (in bold), correct ones for others:
ID / RealValue / rs.field(“RealValue”).DoubleValue
1/ 0.84 / 0.84
2/ 10.1129 / 10.1128999999999998
3/ -1.84 / -1.8400000000000001
4/ -0.53 / -0.53
5/ 18.0 / 18.00
6/ 10.0 / 10.0
7/ 154.0 / 154.0
8/ 33580.0 / 33580.0
9/ 1107.7 / 1107.7000000000000455
10/ 0.4874 / 0.4874

Double values are checked using debug and using format(rs.Field(“RealValue”).DoubleValue, “#####0.00000000000000000”)
If I export the values to CSV or XLS I’m getting the correct values.

I’m experiencing the same problem using SQLitedatabase (Xojo) and cubeSQL database.

I can provide the testDB and a testApp

Any hint or workaround welcome !

Thanks and regards,

Olivier

#2

You’re actually getting the correct values
http://en.wikipedia.org/wiki/IEEE_floating_point

ALL values (including floating point) are base 2

The fractional portion is the sum of powers of 2
2^-1, 2^-2 etc (or 1/2 , 1/4 , 1/8 ) and so on
Trying to express numbers that are not exact powers of 2 sums will result in close approximations - which is what you’re seeing

#3

That’s just the nature of IEEE floating point storage. A Double cannot represent the number exactly. You get a very close approximation. It’s not a problem with sqlite or xojo; it’s just the way it is. Round it off to the number of decimals you want when you display it.

#4

OK ! Thanks Norman, Tim.
My issue is not really displaying numbers. I need to search weight values in other tables.

I need to have something like this: SELECT * FROM myTable mt WHERE mt.Tot_Weight = ?
If I bind ? with a double value I won’t find some records.
I’m not sure using a range would solve the problem because the range boundaries would be defined as double as well.

In Xojo code I can use equals, what’s the equivalent in SQL ?
What would be your solution ?

#5

comparing double always needs to include a delta for tolerance.

SELECT * FROM myTable mt WHERE mt.Tot_Weight >= ? AND mt.Tot_Weight <= ?

passing weight - delta and weight + delta.
And delta may be 0.00001 or so.

#6

Many thanks to all of you.
Sorry I can only flag a single answer as correct.

#7

SELECT * FROM myTable mt WHERE mt.Tot_Weight between ? and ?
and use value - delta and value + delta as the range

#8