The issue here is that you are "truncating" using Base10 (Decimal) but the data is stored in Base2 Floating Point. Therefore you will always get approximate results and never an exact one. On modern computers floating point arithmetic is defined in such a way as to represent approximations to fixed point numbers with a few ULP of the scaled binary representation. For magnitude 1 the epsilon (accuracy) of standard double-precision floating point is 2.220446049250313e-16 (which is why we say that double precision floating point is accurate to 15 to 17 decimal digits, depending on who you ask).
When you perform floating point arithmetic operations, a sequence of pathologic calculations will end up with a representation accuracy equal to the ULP of the largest number in the calculation sequence. There are various numerical calculation methods that avoid doing calculations in pathological order so that the representation is maintained to within a few ULP of the scale of the result. (that is, since the result of any operation between two floating point numbers x and y with result z has a precision of max(epsilon(x), epsilon(y), epsilon(z)), one can ensure maximum precision by re-ordering operations to ensure that epsilon(x) and epsilon(y) are as close to the same as possible, for each operation in the chain, such as by doing running averages rather than simple sum/division averages, etc.) However, when you take any floating point number and "truncate" it, it no longer has the precision defined by the machine representation epsilon. Using that truncated result in any future calculation will (likely depending on what you do with it) lead to massive precision loss. "Rounding" intermediates, by whatever method, also usually leads to massive precision loss. That is why one should always store and work with floating point data at its native precision and only truncate/round a result that will never participate in any further calculation. While cast(value * 1000 as integer) / 1000.0 is very fast, it will of course only work for numbers where the scaled number fits in a 64-bit signed integer. trunc(value, 3) which does the truncation (towards 0) and maintains precision means that the operation is performed by the math library or floating point unit in its usual extended precision and the result is then converted back to double precision using the IEEE-754 standard conversion, so the result is represented as an approximation to within a few ULP of the truncated value. However, the value of the input and the value of the output may be entirely different as they are still approximations (to different values also). > -----Original Message----- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On Behalf Of Jean-Marie CUAZ > Sent: Tuesday, 11 July, 2017 05:35 > To: sqlite-users@mailinglists.sqlite.org > Subject: Re: [sqlite] Example/recipe for truncating fp numbers > > To M. Slavin, Medcalf ans Hipp: > > Thanks for your attention. > > Yes, "cast(value * 1000 as integer) / 1000.0" is much better and I will > switch to your formula when possible, thanks very much. > > Thank you for your offer of C functions : a trunc() function is > certainely what is needed, but I don't understand the sentence > > "trunc(value, 3) which does the truncation (towards 0) and maintains > precision" > > What is needed is something like substr(value,1,Y) (as M. Slavin guessed > it), but on numbers rather than strings, without any alteration of the > subset returned compared to the orginal in the same range : just discard > everything above Y = nb of digits on the right side of the decimal > separator . So trunc(123.1246, 3) = trunc(123.1242, 3) = 123.124 (and > trunc(123.1, 3) = 123.1 greatly prefered to = 123.100) > > Precision 1 : the need of this truncation of fp numbers is only at > storage time, (the intermediate calculus in "/expression"/ in my example > is done (in SQL) at full system-available precision of course. > > Precision 2 : the number of digits kept on the right side of the decimal > separator results from mandatory external constraint, and is not a > developper's choice. With truncation, the loss of precision can, if > needed and with additional efforts, be (imperfectly) restored; I don't > know how to to do the same when rounding. > > To M. Hipp : > > round(123.45678, 3) returns 123.457 and what is needed is a "never > round" method : 123.456 > > Reason : the processing done at SQL level involves lot of > multiplications / divisions on real numbers and unfortunately "integer > arythmetics" is not an option (readability pb + error prone) . The > purpose of the truncation done here (wich I believe is a method allso > used in similar softwares) is an effort to reduce the loss of > associative property of addition over multiplication. If you send a > private mail to me I'll give more details. > > Thanks > > -jm > > > > > > > --- > L'absence de virus dans ce courrier électronique a été vérifiée par le > logiciel antivirus Avast. > https://www.avast.com/antivirus > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
