CODEWITHSUNDEEP
sqlite
Splendor
Splendor

Reputation: 1396

SQLite returns incorrect arthimetic value

I'm working on a query that adds the following numbers and it's returning a puzzling result.

>SELECT ((36.7300 - 20.4300) - 16.3) AS Amount;
>-3.5527136788005e-15

I've tried everything I can think of like casting the values to different data types, but I'm still getting the same result.

When I separate out the steps it returns the right answer, but I can't get the two arthetic steps to work together for some reason.

>SELECT 36.7300 - 20.4300 AS Amount;
>16.3

>SELECT 16.3 - 16.3 AS Amount;
>0.0

Upvotes: 0

Views: 145

Answers (1)

antonio
antonio

Reputation: 18242

This happens because SQLite uses binary arithmetic instead of base-10 arithmetic.

From the SQLite FAQ:

(16) Why does ROUND(9.95,1) return 9.9 instead of 10.0? Shouldn't 9.95 round up?

SQLite uses binary arithmetic and in binary, there is no way to write 9.95 in a finite number of bits. The closest to you can get to 9.95 in a 64-bit IEEE float (which is what SQLite uses) is 9.949999999999999289457264239899814128875732421875. So when you type "9.95", SQLite really understands the number to be the much longer value shown above. And that value rounds down.

This kind of problem comes up all the time when dealing with floating point binary numbers. The general rule to remember is that most fractional numbers that have a finite representation in decimal (a.k.a "base-10") do not have a finite representation in binary (a.k.a "base-2"). And so they are approximated using the closest binary number available. That approximation is usually very close, but it will be slightly off and in some cases can cause your results to be a little different from what you might expect.

There is no direct solution for this, but there are several workarounds to face this problem:

  • Store your amount without decimals and compute INTEGER operations
  • Returning your REALs and do your math out of the database
  • Round your result out of the database

It depends on what are you going to do with your data. For example if you are managing currency you can store your amount in cents instead of dollars, but if you are storing scientific data this solution is far from valid and you may need to retrieve the operands and compute the results out of SQLite.

Upvotes: 1

Related Questions