Is the SQLite Real Datatype "aproximate"?

[FunkyDexter]

I've picked up a project that has an issue. It uses C# with a SQLiteCommand, SqLiteParameters etc. to write a set of floats (singles) to a SQLite database. Subsequently, it reads the values back but reads and exposes them as doubles. (I don't know why there's a discrepancy at this point).

What I'm seeing is that floats rounded to 2DPs in C# are coming back into the doubles with a load of extra high precision decimal place values. E.g. I save 5.20, what I get back is 5.1999998092651300. Or I save 4.90, I get back 4.9000000953674300.

Interestingly, I don't even need to wait until the value comes back into a double before I see the problem. I've been using a SQLite viewer to look at the data and it's had the extra DPs added.

I know SQL Server Reals and Floats are "approximate". I.e. they can get extra high precision DPs drifting like this. My hunch is that this is what's happening inside SQLite, too, but I can't find anything in the documentation to confirm either way.

Does anyone know for sure what SQLite's behaviour is on this?

[si_the_geek]

According to the documentation at https://www.sqlite.org/datatype3.html

REAL. The value is a floating point value, stored as an 8-byte IEEE floating point number.

That seems to be the same as Double in .Net, so would cause this kind of issue (because the conversion to Double effectively happens on the way in to the database).

[Zvoni]

This sounds like the same:
https://stackoverflow.com/questions/...ion-in-room-db

Have you tried typecasting to double before INSERT?

[FunkyDexter]

Yep, that's it. Looks like if I explicitly cast it to a double it stores the full "precise" value. I managed to talk to the original developer as well and it looks like maintaining them as doubles throughout should be OK

Thanks both.

[Schmidt]

Yep, that's it. Looks like if I explicitly cast it to a double it stores the full "precise" value.

IMO SQLite (when casting from a float- aka Single-value to a Double internally) does nothing wrong,
because when I do it "outside of SQLite" (in my case in the VB6-IDE Immediate-Window):
?CDbl(5.20!) ' <- the ! enforcing a Single-type on the Num-Literal
... it Prints out:
5.19999980926514

So not really sure what you mean (or did), when you say you can avoid this with "explicit casting" (on the outside).

Olaf