Dr. Richard Hipp, on Friday, May 24, 2019 07:13 AM, wrote...
>> Last result should be 3.26
>
>3.255 cannot be exactly represented as an IEEE754 double-precision
>binary floating point number. So the system has to use an
>approximation. The closest approximation is
>3.25499999999999989341858963598497211933135986328125 and that value
>rounds to 3.25.
I actually use lots of rounding in SQLite such as this one (please ignore the
extra code--not enough time to clean it up),
char[] cmd;
cmd ~= "\nBEGIN;";
cmd ~= "\nUPDATE LSOpenJobs SET ProjFund = " ~
"\n(" ~
"\n SELECT " ~
"\n ( " ~
"\n CASE " ~
"\n WHEN instr(LSOpenJobs.XtraD,'%') > 0 THEN " ~
"\n round(sum(t2.ProjFund) * cast(LSOpenJobs.XtraD as integer)/100.0,
2) " ~
"\n ELSE " ~
"\n LSOpenJobs.ProjFund " ~
"\n END " ~
"\n ) " ~
"\n FROM LSOpenJobs t2 " ~
"\n WHERE LSOpenJobs.ProjID = t2.ProjID AND LSOpenJobs.lang = t2.lang " ~
"\n AND t2.PSubClass != 'Portal-Fee' " ~
"\n AND t2.PSubClass != 'Rush-Job' " ~
"\n) " ~
"\nWHERE ProjID = " ~ pid ~ " AND PSubClass = 'Rush-Job';";
cmd ~= "\nUPDATE LSOpenJobs SET Xtra8 = " ~
"\n(" ~
"\n SELECT " ~
"\n ( " ~
"\n CASE " ~
"\n WHEN instr(LSOpenJobs.XtraD,'%') > 0 THEN " ~
"\n round(sum(t2.Xtra8) * cast(LSOpenJobs.XtraD as integer)/100.0, 2)
" ~
"\n ELSE " ~
"\n LSOpenJobs.Xtra8 " ~
"\n END " ~
"\n ) " ~
"\n FROM LSOpenJobs t2 " ~
"\n WHERE LSOpenJobs.ProjID = t2.ProjID AND LSOpenJobs.lang = t2.lang " ~
"\n AND t2.PSubClass != 'Portal-Fee' " ~
"\n AND t2.PSubClass != 'Rush-Job' " ~
"\n) " ~
"\nWHERE ProjID = " ~ pid ~ " AND PSubClass = 'Rush-Job';";
cmd ~= "\nEND;";
and I was told by some of the users that our tool calculations was missing
cents here and there in the quotes. I said, "Blasphemy! SQLite does not makes
mistakes!" and now, that I see this, I have to apologize and ask them for their
forgiveness. :-) I was blaming Excel and whatever else they used before
inserting data into SQLite. But now, I see that I was wrong.
Dr. Hipp, how many more scenarios, where round gives the wrong answer, exist?
Thanks.
josé
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
