CODEWITHSUNDEEP
sqlite
OrElse
OrElse

Reputation: 9959

Why my Sum result in SQLite is wrong?

Why my following SQL query 

Insert Into Test (ItemQty) VALUES (1.445);
Insert Into Test (ItemQty) VALUES (0.000000000000001);

Select Sum(ItemQty) from Test;

produces 1.445 as result and not 1.445000000000001 ? What am i missing here?

Upvotes: 0

Views: 193

Answers (1)

Bill Bell
Bill Bell

Reputation: 21643

SQLite Version 3 REAL values are stored as '8-byte IEEE floating point numbers' (see https://www.sqlite.org/datatype3.html). These values allow for just less than 16 decimal digits (see https://en.wikipedia.org/wiki/IEEE_floating_point#Basic_and_interchange_formats). However, the length of the constant 1.445000000000001 is 17, which is (just) beyond the capability of this format.

In fact, when I recreated your example but instead inserted the second value with one less zero after the decimal point I received the result 1.44500000000001.

Because that's only 16 digits long.

Upvotes: 1

Related Questions