SQLite3 on Android not adding float numbers correctly

Question

I am having some trouble running a few simple statements on SQLite3 on Android.

For example:

SELECT 1234 + 0.001

That should return 1234.001, right? SQLite, however, returns 1234 both on the Emulator (v21) and on a real device (v19).

Considering 1234 is stored on a column called Field1, type REAL, on Table1, I have tried all the options below:

SELECT Field1 + 0.001 FROM Table1

SELECT (Field1 * 1.000)  + 0.001 FROM Table1

SELECT CAST(Field1 as FLOAT) + 0.001 FROM Table1

SELECT CAST(Field1 as REAL) + 0.001 FROM Table1

SELECT Field1 + 0.001 from Table1

SELECT CAST((Field1 + 0.001) as REAL) FROM Table1

Nothing seems to work, and in every single case I am getting 1234 instead of 1234.001. I need to get 1234.001 from 1234 in a query but SQLite3 isn't being helpful.

Another thing I just found out: if "Field1" <= 999, the SELECT works as expected and I get 999.001 in the result. Anything >= 1000 gives me an integer instead.

Can you please help me how to solve this?

"That should return 1234.001, right?" -- not necessarily. 1234.001 might not be representable as a float. — CommonsWare, Jul 18 '16 at 15:39
You are probably retrieving your datum as an **integer**. While you want a **float** or a **double**. — Phantômaxx, Jul 18 '16 at 16:14
@CommonsWare agreed, but none of the numbers >= 1000 are returned as x.001, like the screenshot above shows. On the other hand, any number < 1000 is returned as expected. — AlxDroidDev, Jul 18 '16 at 17:22
I wasn't formatting the number. I was reading them with `cursor.getString()`, and I guess that was the culprit. — AlxDroidDev, Jul 18 '16 at 21:35

score 0 · Answer 1 · answered Jul 18 '16 at 18:11

Actually, I found out that the problem is not what it seems. The numbers are being treated as x.001, but not shown as such. Internally, the numbers were being properly stored in binary, but not all of the decimal part was being shown. After just casting them as text, I was able to see that the decimal part was all there.

Anyhow, thank you for your time and answers.

score -1 · Answer 2 · edited May 23 '17 at 10:28

This is an issue of precision and how numbers are stored in a computer or for that matter in sqlite3. Numbers are stored in binary format, which means that by converting a decimal number to a binary number you loose some precision (in real numbers) read more here. Now, what are your options if you want the addition to yield 1234.001 as in your example?

A) Storing the number as a string.

You can always store the values '1234' and '0.001' as VARCHARs and in JAVA code parse this values to BigDecimals and perform your additions there. For more info on parsing, check out this link. The drawback of this method is that it will consume a lot of more storage space in your database and parsing operations aren't that fast either. Before using this method, think if this drawback will impact negatively the performance of your application.

B) Establish a MAXIMUM_PRECISION and store them as INTEGERs.

By default SQLITE3 stores INTEGERs using 8 bytes. This means that the largest integer that you can store is of the order of 10^19. Now, by storing integers you dont loose precision; so we can take advantage of this. Let's say that your MAXIMUM_PRECISION is 0.001 (as in your example) then you need to multiply this number by one thousand to get a 1. So what if instead of representing 1234.001 as a real, we represent it as 1234001 an int? that way we can store the int safely in sqlite3 and make sure that the operations work properly. In your code you can later on parse the number as a String and format it to display it to an user or you can parse it to a BigDecimal in order to keep precision. Of course, this will limit you to a maximum number of the order of 10^16; again check your requirements to see if this trick will work for your app. Please note that a similar trick is used to store currency without loosing precision in sqlit3, for more info see: this link

SQLite3 on Android not adding float numbers correctly

2 Answers2