On Mon, 27 May 2019 at 23:36, Jose Isaias Cabrera <jic...@outlook.com>
wrote:
> Ok, I think it happens even before the casting. This should be,
> 3.2599999999999998, and yet, it's 3.26.
>
> sqlite> SELECT 0.005 + 3.2549999999999998;
> 3.26
>
Note that no arithmetic is required to see these symptoms:
sqlite> SELECT 3.2599999999999998;
3.26
But also note that when floating point numbers are displayed, they are
_almost always_ simplified for ease of reading. Try this C code:
#include <stdio.h>
int
main(int argc, char** argv)
{
double d = 3.2599999999999998;
printf("%f\n", d);
return 0;
}
It prints 3.260000. There is also a kind of rounding that happens at the
display level, which can make it tricky to appreciate what is going on
behind the scenes. You can adjust that behaviour in C; eg. changing %f to
%.72f gives you "more precision":
3.259999999999999786837179271969944238662719726562500000000000000000000000
Also note that in practice 3.2599999999999998 _is_ 3.26. You can check this
in sqlite:
sqlite> SELECT 3.2599999999999998 = 3.26;
1
64-bit floating point just doesn't have the accuracy to represent the
difference. Here's what changing the lowest bit looks like around 3.26:
0x400a147ae147ae13 =~ 3.2599999999999993
0x400a147ae147ae14 =~ 3.26
0x400a147ae147ae15 =~ 3.2600000000000002
One way to think of floating point is that each 64-bit value represents a
"bin" of closely related numbers - the decimal value I've written on the
right hand side here represents the (approximate) bin centre.
3.2599999999999998 is closer to 3.26 than 3.2599999999999993 so it gets
lumped into 0x400a147ae147ae14
This is all very subtle which is why some languages/software offer actual
decimal arithmetic. sqlite does not, but there's also nothing to stop you
from storing eg. strings in the DB and converting to/from decimal
representations in your application.
-Rowan
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
