sqlite: trouble selecting by real values

Question

Hello,

I am using an sqlite database to store log data.

My table has a lot of 'real' type columns. When I try to execute queries like:

SELECT * FROM log WHERE SomeNumber = 61;
SELECT * FROM log WHERE SomeNumber='61';

It will return nothing.

Queries like:

SELECT * FROM log WHERE SomeNumber < 10
SELECT * FROM log WHERE SomeNumber > 10

Will return incorrect ranges.

Is there something wrong with my syntax ?

Thank you,

Answer 1

It really depends on what you put into your table. Real numbers are almost always universally tricky. A "real" value of 61 might be stored as 61.0000000000001. The basic premise is, if you need to check for equality, prefer integers.

You might want to try:

SELECT * FROM log WHERE SomeNumber = 61.0;

It might also depend on how much precision you entered your original value with.

NB: I should point out that normally, an integer value like 61 is stored precisely even as a real. This is just a simplification.