> 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.
Thanks, this was exactly the info I was looking for. -Jon ______________________________________ Jon Polfer Project Engineer - High Level Software Engineering Office Phone: 262-832-0049 (Ext. 5 for Jon Polfer) Fax: E-mail: jpol...@forceamerica.com FORCE America Inc. W229 N1433 Westwood Drive, Suite 200 Waukesha, WI 53186 www.forceamerica.com The Leading Innovator in Mobile Hydraulic Solutions -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Scott Hess Sent: Tuesday, January 04, 2011 5:27 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Rounding Strategy 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
