On Mon, Feb 15, 2010 at 12:52 PM, Phil Hibbs <sna...@gmail.com> wrote:
> Shane Harelson: > > Under the covers, when the second term to ROUND(X,y) is omitted, SQLite > adds > > 0.5 and then truncates. Because of floating point precision, some > numbers > > can not be represented exactly... causing the odd rounding you saw in > your > > examples. > > I've just had a look "under the covers", and indeed it seems that > SQLite has its own printf implementation. Makes sense, as I think the > C standard itself is also implementation-defined in this case, and > this might be the kind of thing that SQLite coders want control over. > > It was hasty to conclude that round-to-even is the rule, as one more > example shows: > > sqlite> select round(40226.5); > 40227.0 > > I think the 40223.5 case is just an example of binary/decimal floating > point incompatibility. As Kernighan and Plauger put it: > "Floating-point numbers are a lot like sandpiles: Every time you move > one you lose a little sand and pick up a little dirt." > > I'm a little surprised that it's going wrong with a number ending in > 0.5 though, I'd have thought that that would be expressible perfectly > in binary without loss. I don't know enough about it though. > > Phil Hibbs. > > As you and Igor point out, the numbers being tested can be accurately represented. However, as part of the SQLite's internal printf() implementation, the floating point value is shifted to (loosely) scientific form and manipulated for formatting. So 40224.5 is converted to something like 4.02245e4 before printing. The errors get introduced as part of this manipulation. I'm looking at how this can be improved. HTH. -Shane _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
