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. Since the 3.05 isn't precisely represented (with format %.16f, I get "3.0499999999999998"), adding .05 doesn't do much to improve the situation.
Put another way, when you type: select 3.05 + 0.05 < 3.1; the result is "1", whereas select 305 + 5 < 310; gives the result "0". Definitely be careful when using floating-point. This kind of thing is why there are so many number types in different SQL implementations. -scott On Tue, Jan 4, 2011 at 2:58 PM, Jean-Christophe Deschamps <j...@q-e-d.org> wrote: > >> > 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.049999999999998 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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
