Re: [sqlite] Rounding Strategy
On Jan 5, 2011, at 4:09 AM, Jean-Christophe Deschamps wrote: > >> I can't help but wonder how decimal math is supposed to make 1/3 + 1/3 >> + 1/3 better. > > Sorry it was almost a private joke here. It's a very common maths > question / challenge about what infinite decimal expansion means. > > If you add 0.... (where ellipsis means infinite number of digit > 3), do you get 0.9... or 1 > > Other forms of the question: > is 0.999... = 1 true? > is 0.999... an actual real? > > Note: 0.3... is noted 0.3 with a dot above the 3 Hmm I learned it with a horiz bar over the 3. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Rounding Strategy
Scott, >SQLite handles rounding by running the value through the internal >printf with the appropriate precision. As best I can tell the >internal printf adds half a unit at the appropriate position, then >truncates. Yep, truncation is a way to do it but doesn't meet layman expectations in many (most?) cases. Rounding is another beast entirely. > Since the 3.05 isn't precisely represented (with format >%.16f, I get "3.0498"), adding .05 doesn't do much to >improve the situation. Due to truncation, OK. If round() were using some "round to rearest, ties away from zero" setting (IEEE754r) it would always work. But that isn't easy to implement portably AFAICT. >Definitely be careful when using floating-point. This kind of thing >is why there are so many number types in different SQL >implementations. ... and so many settings in IEE hardware/software. I really look forward Intel decimal FP being mainstream, specially in business-likely code like a DB engine. But until such baby finds its way in hardware, I guess we're stuck with this pesky binary thing and slow / imprecise conversions back and forth and 1/3 + 1/3 + 1/3 != 1. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Rounding Strategy
> > select round(3.05, 1) >3.0 > >Is this expected behavior for SQLite? >The documentation isn't specific on the rounding strategy that is used. > >My personal expectation was that this would round to 3.1. You _expect_ that 3.05 will represent exactly as 3.05 in IEEE, but it that the case? Should the nearest FP representation be 3.048 then the round() function would work fine, which is likely. In such case, add half a unit at the position below rounding position. Half a unit is guaranteed to have an exact representation under IEEE (at least in my old time), being a power of 2. [Binary] FP can be utterly misleading. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
