SQLite query where clause with floating point numbers fails?

Question

I'm putting a float in an Android based SQLite database, like so:

private static final String DATABASE_CREATE = 
    "create table " + DATABASE_TABLE + " ("  
                    + KEY_ID + " integer primary key autoincrement, "
                    + KEY_FLOAT  + " REAL, " + ...
...

content.put(KEY_FLOAT, 37.3f);
db.insert(DATABASE_TABLE, null, content);

When I query the table:

Cursor cursor = db.query(false, DATABASE_TABLE, 
      new String[] { KEY_ID, KEY_FLOAT, ... },
      KEY_LATITUDE + "=37.3",
      null, null, null, null, null);

the cursor comes back empty. If I change the value of the float to 37.0 it works properly, returning the record in the cursor.

I've tested this at some length, changing the database column spec from "REAL" to "float", etc. AS long as I have any fractional portion after the decimal point, the cursor returns empty. What is going on?

Thanks in advance!

Answer 1

37.0 is exactly representable in binary floating point, so you don't have any of the normal issues. 37.3 is not exactly representable - your query isn't matching because the value in the database isn't exactly 37.3.

Options:

• Use a decimal-based numeric type, if such a thing exists in SQLite (I'll check in a minute)
• Make your query use a tolerance, e.g. "LATITUDE > 37.29 AND LATITUDE < 37.31". Comparing binary floating point values for exact equality is just asking for trouble, I'm afraid.

EDIT: I've just checked the docs, and it looks like SQLite doesn't support decimal non-integer types. I'll leave that suggestion above as it would be relevant for the same issue in other databases though.

EDIT: Pascal's solution of effectively rolling your own numeric type is a good one in many cases. Work out what level of precision you want, and multiply/divide accordingly... so if you want 2 decimal places of precision, multiply by 100 when you store a value and then divide by 100 after fetching it. Your queries will need to be in the "multiplied" form, of course.

Answer 2

Granted, it's a bad idea to compare float values for equality.

Howver, I see that SQLite uses 8-byte floating point values (which is like a DOUBLE), so it's odd that 37.0 is considered equal to 37.3. Unless you modified for your example the values used in the actual code?

You could store your LATITUDE as integers, in tenth of degree, applying the precision yourself, and converting the value on read/write...

Answer 3

I tried with <column> BETWEEN <min> AND <max> and it worked, tolerance LATITUDE > 37.29 AND LATITUDE < 37.31 didn't work for me. But I tried BETWEEN with this hint.

My observations:

1. When I have a single record, greater than (>) didn't work.
2. If there is more than one record, greater than (>) worked perfectly.