Re: [sqlite] Re: select round(98926650.50001, 1) ?
On Wednesday, September 05, 2007 Simon Davies wrote:
> [...]
> This resolves down to calling a function vxprintf, which has the following
> line:
>while( realvalue>=10.0 && exp<=350 ){ realvalue *= 0.1; exp++; }
> When this line is reached, realvalue has value 98926650.5 and exp value 0.
> realvalue then goes the following values during the loop:
> 9892665.050007
> 989266.5050012
> 98926.65050018
> 9892.665050032
> 989.2665050039
> 98.92665050051
> 9.892665050051
> exp finishes at 7
> Note the 'error' added in by the successive multiplications is 0.00051
> [...]
> One wonders why XP/VC++ produces such a large error in its floating
> point manipulations that appears to be avoided by other compilers.
...probably because VC++ uses 64 bits for long double (53 bit
mantissa) whereas gcc uses 96 bits (64 bit mantissa). Note that
realvalue above is declared as LONGDOUBLE_TYPE (long double).
http://msdn2.microsoft.com/en-us/library/9c3yd98k(VS.80).aspx
http://msdn2.microsoft.com/en-us/library/9cx8xs15(vs.80).aspx
e
--
Doug Currie
Londonderry, NH, USA
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Re: select round(98926650.50001, 1) ?
On 9/5/07, Cory Nelson <[EMAIL PROTECTED]> wrote: > On 9/5/07, Nuno Lucas <[EMAIL PROTECTED]> wrote: > > What about defining __STD_IEC_559 before the compilation? > > Acording to this: > > > >http://david.tribble.com/text/cdiffs.htm#C99-iec60559 > > > > C99 allows one to force the compiler to follow the standard, so maybe > > libc does it by default, but the official sqlite compiled version > > (which IIRC is linked with the old Microsoft C runtime DLL) doesn't. > > it is defined by the compiler to indicate that it conforms. it is not > something that you yourself define. ugh! Don't know how I got it wrong! Seems like I read it in a wishful thinking way. Sorry for the noise. Regards, ~Nuno Lucas > -- > Cory Nelson - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: select round(98926650.50001, 1) ?
On 9/5/07, Nuno Lucas <[EMAIL PROTECTED]> wrote: > What about defining __STD_IEC_559 before the compilation? > Acording to this: > >http://david.tribble.com/text/cdiffs.htm#C99-iec60559 > > C99 allows one to force the compiler to follow the standard, so maybe > libc does it by default, but the official sqlite compiled version > (which IIRC is linked with the old Microsoft C runtime DLL) doesn't. it is defined by the compiler to indicate that it conforms. it is not something that you yourself define. -- Cory Nelson - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: select round(98926650.50001, 1) ?
Hi Nuno,
Have tried your suggestion; added __STD_IEC_559 to preprocessor
definitions for the sqlite project in Visual Studio. Same result.
I noted that the preprocessor macro quoted is applicable to C99 and
not C++. So I then tried specifying that Visual Studio compile the
sqlite code as C rather than C++ (it probably was doing that anyway),
but again with the same result.
Rgds,
Simon
On 05/09/07, Nuno Lucas <[EMAIL PROTECTED]> wrote:
> On 9/5/07, Simon Davies <[EMAIL PROTECTED]> wrote:
> [...]
> > in sqlite3VdbeMemStringify.
> > This resolves down to calling a function vxprintf, which has the following
> > line:
> >
> >while( realvalue>=10.0 && exp<=350 ){ realvalue *= 0.1; exp++; }
> >
> [...]
> > Interestingly, if the line is changed to
> >while( realvalue>=10.0 && exp<=350 ){ realvalue /= 10; exp++; }
> > then the accumulated error is less, and the correct value is printed.
> > Of course, this change may break other builds.
> >
> > One wonders why XP/VC++ produces such a large error in its floating
> > point manipulations that appears to be avoided by other compilers.
>
> What about defining __STD_IEC_559 before the compilation?
> Acording to this:
>
> http://david.tribble.com/text/cdiffs.htm#C99-iec60559
>
> C99 allows one to force the compiler to follow the standard, so maybe
> libc does it by default, but the official sqlite compiled version
> (which IIRC is linked with the old Microsoft C runtime DLL) doesn't.
>
>
> Regards,
> ~Nuno Lucas
>
>
> >
> > Rgds,
> > Simon
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Re: select round(98926650.50001, 1) ?
On 9/5/07, Simon Davies <[EMAIL PROTECTED]> wrote:
[...]
> in sqlite3VdbeMemStringify.
> This resolves down to calling a function vxprintf, which has the following
> line:
>
>while( realvalue>=10.0 && exp<=350 ){ realvalue *= 0.1; exp++; }
>
[...]
> Interestingly, if the line is changed to
>while( realvalue>=10.0 && exp<=350 ){ realvalue /= 10; exp++; }
> then the accumulated error is less, and the correct value is printed.
> Of course, this change may break other builds.
>
> One wonders why XP/VC++ produces such a large error in its floating
> point manipulations that appears to be avoided by other compilers.
What about defining __STD_IEC_559 before the compilation?
Acording to this:
http://david.tribble.com/text/cdiffs.htm#C99-iec60559
C99 allows one to force the compiler to follow the standard, so maybe
libc does it by default, but the official sqlite compiled version
(which IIRC is linked with the old Microsoft C runtime DLL) doesn't.
Regards,
~Nuno Lucas
>
> Rgds,
> Simon
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Re: select round(98926650.50001, 1) ?
Hi All,
Here's my 2p...
on XP and VC++ (for my sins!)
The round function works.
sqlite_column_double() returns 98926650.5
The '01' on the end is introduced by calling sqlite_column_text
(in shell.c).
This results in reaching the line
sqlite3_snprintf(NBFS, z, "%!.15g", pMem->r);
in sqlite3VdbeMemStringify.
This resolves down to calling a function vxprintf, which has the following line:
while( realvalue>=10.0 && exp<=350 ){ realvalue *= 0.1; exp++; }
When this line is reached, realvalue has value 98926650.5 and exp value 0.
realvalue then goes the following values during the loop:
9892665.050007
989266.5050012
98926.65050018
9892.665050032
989.2665050039
98.92665050051
9.892665050051
exp finishes at 7
Note the 'error' added in by the successive multiplications is 0.00051
When the rounding value (calculated earlier) is added to realvalue,
the 15th digit is now 1, and therefore within the requested precision.
The call to sqlite3_snprintf() asks for 15 digits precision, hence the
'01' gets displayed in the result to the shell.
To summarize, the successive multiplication in vxprintf introduces a
large enough error that it is representable by the precision requested
by sqlite_column_text().
Interestingly, if the line is changed to
while( realvalue>=10.0 && exp<=350 ){ realvalue /= 10; exp++; }
then the accumulated error is less, and the correct value is printed.
Of course, this change may break other builds.
One wonders why XP/VC++ produces such a large error in its floating
point manipulations that appears to be avoided by other compilers.
Rgds,
Simon
On 05/09/07, Doug Currie <[EMAIL PROTECTED]> wrote:
> On Wednesday, September 05, 2007 Arjen Markus wrote:
>
> > Doug Currie wrote:
>
> >>I suspect the bug is in the functions that convert between string and
> >>double; that's why I keep harping on Steele and White's (and
> >>Clinger's) PLDI 1990 papers. What I don't know is why this bug appears
> >>in the binary from sqlite.org but not in the version I build myself
> >>with gcc 3.4.5 from SQLite version 3.4.2 source.
> >>
> >>
> >>
> > The implementation of that algorithm is far from trivial. It actually
> > requires the
> > use of an arbitrary-precision library (or so at least is my understanding).
>
> Yes, in some cases big integers are needed. In extreme cases, e.g.,
> denormalized numbers, up to 160 bytes may be required.
>
> I have implemented these algorithms, twice, but for employers with
> proprietary programs. The algorithms are also implemented in glibc
> based on high quality code from David Gay (Bell Labs, Lucent, Sandia,
> Netlib), available with a BSD license at
> http://netlib.sandia.gov/fp/index.html
>
> e
>
> --
> Doug Currie
> Londonderry, NH, USA
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Re: select round(98926650.50001, 1) ?
On Wednesday, September 05, 2007 Arjen Markus wrote: > Doug Currie wrote: >>I suspect the bug is in the functions that convert between string and >>double; that's why I keep harping on Steele and White's (and >>Clinger's) PLDI 1990 papers. What I don't know is why this bug appears >>in the binary from sqlite.org but not in the version I build myself >>with gcc 3.4.5 from SQLite version 3.4.2 source. >> >> >> > The implementation of that algorithm is far from trivial. It actually > requires the > use of an arbitrary-precision library (or so at least is my understanding). Yes, in some cases big integers are needed. In extreme cases, e.g., denormalized numbers, up to 160 bytes may be required. I have implemented these algorithms, twice, but for employers with proprietary programs. The algorithms are also implemented in glibc based on high quality code from David Gay (Bell Labs, Lucent, Sandia, Netlib), available with a BSD license at http://netlib.sandia.gov/fp/index.html e -- Doug Currie Londonderry, NH, USA - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: select round(98926650.50001, 1) ?
John Machin wrote: On 5/09/2007 10:13 AM, John Stanton wrote: John Machin wrote: On 5/09/2007 6:18 AM, John Stanton wrote: These are regular floating point numbers, and behave accordingly. Utter nonsense. round(98926650.5, 1) -> 98926650.501 is a BUG. Precisely, As I said, regular floating point. "regular floating point" does not cause such bugs; read the remainder of my post. The bug is in sqlite or in some library routine that it is using. Precisely. To stay out of trouble treat floating point implementations as platform specific. 98926650.5 is representable EXACTLY in "regular" floating point. The round function should calculate a scale factor (10.0) in this case, and then: shifted = 98926650.5 * 10.0 // i.e. 989266505.0, with no loss of precision floored = floor(shifted) // result: 989266505.0 (no change) answer = floored / 10.0 // result: 98926650.5, exactly And here, using Python as a calculator and exemplar, is what should happen with Selena's second case: | >>> original = 85227887.01 | >>> original | 85227887.01005 | >>> shifted = original * 10.0 | >>> shifted | 852278870.1002 | >>> floored = float(int(shifted)) | >>> floored | 852278870.0 | >>> import math | >>> math.floor(shifted) | 852278870.0 | >>> answer = floored / 10.0 | >>> answer | 85227887.0 | >>> Serena Lien wrote: I have read some of the postings/faq about the difficulties with the round function, when certain numbers do not have a finite representation in binary, which SQLite uses. eg 9.95 is actually 9.9499...etc so that round( 9.95, 1) rounds down. But, I have found several numbers which don't get rounded at all, and in fact return more decimal places! round(98926650.5, 1) -> 98926650.501 - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: select round(98926650.50001, 1) ?
Doug Currie wrote: I suspect the bug is in the functions that convert between string and double; that's why I keep harping on Steele and White's (and Clinger's) PLDI 1990 papers. What I don't know is why this bug appears in the binary from sqlite.org but not in the version I build myself with gcc 3.4.5 from SQLite version 3.4.2 source. The implementation of that algorithm is far from trivial. It actually requires the use of an arbitrary-precision library (or so at least is my understanding). Regards, Arjen - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: select round(98926650.50001, 1) ?
On 9/5/07, Doug Currie <[EMAIL PROTECTED]> wrote: > > > Clinger's) PLDI 1990 papers. What I don't know is why this bug appears > in the binary from sqlite.org but not in the version I build myself > with gcc 3.4.5 from SQLite version 3.4.2 source. > > Note it also appears when I compile SQLite from 3.4.2 source code using Microsoft Visual C++ (2003). I have not looked at using fp:precise and _controlfp: as Kees suggested because it's only a feature in VC 2005.. Serena
Re: [sqlite] Re: select round(98926650.50001, 1) ?
On Tuesday, September 04, 2007 John Machin wrote: > On 5/09/2007 6:18 AM, John Stanton wrote: >> These are regular floating point numbers, and behave accordingly. > Utter nonsense. round(98926650.5, 1) -> 98926650.501 is a BUG. I agree with you that there is a bug here somewhere. But it is not in the round function, regardless of the merits of its implementation... C:\pvm3>sqlite3 SQLite version 3.4.2 Enter ".help" for instructions sqlite> select 98926650.5 * 1.0; 98926650.501 I suspect the bug is in the functions that convert between string and double; that's why I keep harping on Steele and White's (and Clinger's) PLDI 1990 papers. What I don't know is why this bug appears in the binary from sqlite.org but not in the version I build myself with gcc 3.4.5 from SQLite version 3.4.2 source. e -- Doug Currie Londonderry, NH, USA - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: select round(98926650.50001, 1) ?
On 5/09/2007 10:13 AM, John Stanton wrote: John Machin wrote: On 5/09/2007 6:18 AM, John Stanton wrote: These are regular floating point numbers, and behave accordingly. Utter nonsense. round(98926650.5, 1) -> 98926650.501 is a BUG. Precisely, As I said, regular floating point. "regular floating point" does not cause such bugs; read the remainder of my post. The bug is in sqlite or in some library routine that it is using. 98926650.5 is representable EXACTLY in "regular" floating point. The round function should calculate a scale factor (10.0) in this case, and then: shifted = 98926650.5 * 10.0 // i.e. 989266505.0, with no loss of precision floored = floor(shifted) // result: 989266505.0 (no change) answer = floored / 10.0 // result: 98926650.5, exactly And here, using Python as a calculator and exemplar, is what should happen with Selena's second case: | >>> original = 85227887.01 | >>> original | 85227887.01005 | >>> shifted = original * 10.0 | >>> shifted | 852278870.1002 | >>> floored = float(int(shifted)) | >>> floored | 852278870.0 | >>> import math | >>> math.floor(shifted) | 852278870.0 | >>> answer = floored / 10.0 | >>> answer | 85227887.0 | >>> Serena Lien wrote: I have read some of the postings/faq about the difficulties with the round function, when certain numbers do not have a finite representation in binary, which SQLite uses. eg 9.95 is actually 9.9499...etc so that round( 9.95, 1) rounds down. But, I have found several numbers which don't get rounded at all, and in fact return more decimal places! round(98926650.5, 1) -> 98926650.501 - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: select round(98926650.50001, 1) ?
John Machin wrote: On 5/09/2007 6:18 AM, John Stanton wrote: These are regular floating point numbers, and behave accordingly. Utter nonsense. round(98926650.5, 1) -> 98926650.501 is a BUG. Precisely, As I said, regular floating point. 98926650.5 is representable EXACTLY in "regular" floating point. The round function should calculate a scale factor (10.0) in this case, and then: shifted = 98926650.5 * 10.0 // i.e. 989266505.0, with no loss of precision floored = floor(shifted) // result: 989266505.0 (no change) answer = floored / 10.0 // result: 98926650.5, exactly And here, using Python as a calculator and exemplar, is what should happen with Selena's second case: | >>> original = 85227887.01 | >>> original | 85227887.01005 | >>> shifted = original * 10.0 | >>> shifted | 852278870.1002 | >>> floored = float(int(shifted)) | >>> floored | 852278870.0 | >>> import math | >>> math.floor(shifted) | 852278870.0 | >>> answer = floored / 10.0 | >>> answer | 85227887.0 | >>> Serena Lien wrote: I have read some of the postings/faq about the difficulties with the round function, when certain numbers do not have a finite representation in binary, which SQLite uses. eg 9.95 is actually 9.9499...etc so that round( 9.95, 1) rounds down. But, I have found several numbers which don't get rounded at all, and in fact return more decimal places! round(98926650.5, 1) -> 98926650.501 - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: select round(98926650.50001, 1) ?
On 5/09/2007 6:18 AM, John Stanton wrote: These are regular floating point numbers, and behave accordingly. Utter nonsense. round(98926650.5, 1) -> 98926650.501 is a BUG. 98926650.5 is representable EXACTLY in "regular" floating point. The round function should calculate a scale factor (10.0) in this case, and then: shifted = 98926650.5 * 10.0 // i.e. 989266505.0, with no loss of precision floored = floor(shifted) // result: 989266505.0 (no change) answer = floored / 10.0 // result: 98926650.5, exactly And here, using Python as a calculator and exemplar, is what should happen with Selena's second case: | >>> original = 85227887.01 | >>> original | 85227887.01005 | >>> shifted = original * 10.0 | >>> shifted | 852278870.1002 | >>> floored = float(int(shifted)) | >>> floored | 852278870.0 | >>> import math | >>> math.floor(shifted) | 852278870.0 | >>> answer = floored / 10.0 | >>> answer | 85227887.0 | >>> Serena Lien wrote: I have read some of the postings/faq about the difficulties with the round function, when certain numbers do not have a finite representation in binary, which SQLite uses. eg 9.95 is actually 9.9499...etc so that round( 9.95, 1) rounds down. But, I have found several numbers which don't get rounded at all, and in fact return more decimal places! round(98926650.5, 1) -> 98926650.501 - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: select round(98926650.50001, 1) ?
These are regular floating point numbers, and behave accordingly. Serena Lien wrote: I have read some of the postings/faq about the difficulties with the round function, when certain numbers do not have a finite representation in binary, which SQLite uses. eg 9.95 is actually 9.9499...etc so that round( 9.95, 1) rounds down. But, I have found several numbers which don't get rounded at all, and in fact return more decimal places! round(98926650.5, 1) -> 98926650.501 round(85227887.01, 1) -> 85227887.001 Even if these numbers cannot be represented properly in binary, why is it they aren't rounded? thanks for any assistance, Serena On 8/31/07, Serena Lien <[EMAIL PROTECTED]> wrote: Hello, With SQLite 3.3.13, this returns 98926650.501 Can you explain how I can get the expected rounding/truncation? thanks. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: select round(98926650.50001, 1) ?
On 4/09/2007 7:13 AM, Doug Currie wrote: On Monday, September 03, 2007 Nuno Lucas wrote: Maybe some OS specific error? Wasn't there some discussion earlier about the Microsoft compiler not using the full double precision by default? Microsoft C compilers store long doubles in 64 bits, just like doubles http://msdn2.microsoft.com/en-us/library/9c3yd98k(VS.80).aspx whereas gcc stores long doubles in 96 bits and uses 64 bit mantissa (80x87 "extended" type) versus Microsoft 53 bit mantissa. http://msdn2.microsoft.com/en-us/library/9cx8xs15(vs.80).aspx Sqlite3 uses long doubles in round, and other functions, and so results between Microsoft Visual C++ compiled and gcc compiled versions of sqlite3 are bound to produce different results. "different" means wrong, even if only by 1 ulp. A correct answer should be achievable with 64-bit FP. The Windows distribution of Python is compiled with an MS C compiler; it manages to get the correct answer for Selena's test cases. The source for round() is trivially small, platform-independent, and just uses doubles. It relies on the C library floor() function (ceil() if negative). Rounding a positive number f to 2 decimal places is effectively as simple as floor(f * 100.0 + 0.5) / 100.0 ... worth borrowing, perhaps. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: select round(98926650.50001, 1) ?
On Monday, September 03, 2007 Nuno Lucas wrote: > Maybe some OS specific error? Wasn't there some discussion earlier > about the Microsoft compiler not using the full double precision by > default? Microsoft C compilers store long doubles in 64 bits, just like doubles http://msdn2.microsoft.com/en-us/library/9c3yd98k(VS.80).aspx whereas gcc stores long doubles in 96 bits and uses 64 bit mantissa (80x87 "extended" type) versus Microsoft 53 bit mantissa. http://msdn2.microsoft.com/en-us/library/9cx8xs15(vs.80).aspx Sqlite3 uses long doubles in round, and other functions, and so results between Microsoft Visual C++ compiled and gcc compiled versions of sqlite3 are bound to produce different results. Why my gcc 3.4.5 compiled sqlite3.exe and the one from the sqlite.org downloads page produce different results is still a mystery to me. e -- Doug Currie Londonderry, NH, USA - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: select round(98926650.50001, 1) ?
On Mon, 3 Sep 2007 18:29:55 +0100, you wrote: >This made me to remember there was a bug some time ago about the >rounding algorithm (but can't remember at what version it was fixed), >so I just tested it. > >"official" amalgamated sqlite 3.4.0 downloaded from the site some time ago: > >SQLite version 3.4.0 >Enter ".help" for instructions >sqlite> select round(98926650.5, 1) ; >98926650.5 >sqlite> select round(85227887.01, 1) ; >85227887.0 >sqlite> select round(85227887.01, 2) ; >85227887.01 >sqlite> select round(98926650.50001, 1) ; >98926650.5 > >Linux [K]Ubuntu 7.04 (feisty) sqlite3 package 3.3.13-0ubuntu1: > >SQLite version 3.3.13 >Enter ".help" for instructions >sqlite> select round(98926650.5, 1); >98926650.5 >sqlite> select round(85227887.01, 1); >85227887.0 >sqlite> select round(85227887.01, 2); >85227887.01 >sqlite> select round(98926650.50001, 1) ; >98926650.5 > >$ uname -a >Linux ubuno 2.6.20-16-generic #2 SMP Thu Jun 7 20:19:32 UTC 2007 i686 GNU/Linux > > >So it seems SQLite is already doing the right job. >Maybe some OS specific error? Wasn't there some discussion earlier >about the Microsoft compiler not using the full double precision by >default? > > >Regards, >~Nuno Lucas I found something in a post by Joe Wilson. Message-ID: <[EMAIL PROTECTED]> Date: Sun, 10 Jun 2007 15:55:32 -0700 (PDT) From: Joe Wilson <[EMAIL PROTECTED]> He found: http://support.microsoft.com/kb/102555 Microsoft Visual C++ runtime library provides default floating-point exception handling and includes functions such as _controlfp for determining and adjusting the floating-point hardware's rounding, precision control, and exception handling behavior. More info on fp:precise and _controlfp: http://msdn2.microsoft.com/en-us/library/aa289157(vs.71).aspx I'm not sure this is related. Out of curiousness I tried it myself on Microsoft Windows [Version 6.0.6000, aka vista] SQLite version 3.4.2 sqlite> select round(98926650.50001, 1); 98926650.501 sqlite> select round(98926650.50001 -0.1, 1); 98926650.501 sqlite> select round(98926650.50001 -0.0001, 1); 98926650.501 sqlite> select round(98926650.50001 -0.001, 1); 98926650.501 sqlite> select round(98926650.50001 -0.01, 1); 98926650.501 sqlite> select round(98926650.50001 -0.1, 1); 98926650.4 sqlite> Oh, well, 9 digits of accuracy is way more than most measurements we can do in daily life. For money, use integers and express in cents / centimes or something. -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: select round(98926650.50001, 1) ?
On Monday, September 03, 2007 Nuno Lucas wrote: > This made me to remember there was a bug some time ago about the > rounding algorithm (but can't remember at what version it was fixed), > so I just tested it. > "official" amalgamated sqlite 3.4.0 downloaded from the site some time ago: > SQLite version 3.4.0 > Enter ".help" for instructions > sqlite> select round(98926650.5, 1) ; > 98926650.5 > So it seems SQLite is already doing the right job. > Maybe some OS specific error? Wasn't there some discussion earlier > about the Microsoft compiler not using the full double precision by > default? On WinXP SQLite version 3.4.2 Enter ".help" for instructions sqlite> select round(98926650.5, 1); 98926650.501 My results above are using the sqlite3.exe from http://www.sqlite.org. I belive this is compiled with gcc, but I don't know what version. However, if I compile from sources, I get C:\Dev\sqlite\sqlite-3.4.2\bld>.\sqlite3 SQLite version 3.4.2 Enter ".help" for instructions sqlite> select round(98926650.5, 1); 98926650.5 I am using: $ gcc --version gcc.exe (GCC) 3.4.5 (mingw special) e -- Doug Currie Londonderry, NH, USA - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: select round(98926650.50001, 1) ?
On 9/3/07, Doug Currie <[EMAIL PROTECTED]> wrote: > On Monday, September 03, 2007 Arjen Markus wrote: > > > Serena Lien wrote: > > >>round(98926650.5, 1) -> 98926650.501 > >>round(85227887.01, 1) -> 85227887.001 > > > They are in fact rounded, but the internal binary representation can > > not be turned into the appropriate decimal (and human readable) > > representation due to the finite precision. > > 98926650.5 is represented exactly in IEEE double; something else is > mucking up the round or the display of the result, maybe both. > > 85227887.0 is represented exactly in IEEE double, though 85227887.01 > is not. Nevertheless, there are ways to print floating point numbers > readably. http://portal.acm.org/citation.cfm?id=93559 This made me to remember there was a bug some time ago about the rounding algorithm (but can't remember at what version it was fixed), so I just tested it. "official" amalgamated sqlite 3.4.0 downloaded from the site some time ago: SQLite version 3.4.0 Enter ".help" for instructions sqlite> select round(98926650.5, 1) ; 98926650.5 sqlite> select round(85227887.01, 1) ; 85227887.0 sqlite> select round(85227887.01, 2) ; 85227887.01 sqlite> select round(98926650.50001, 1) ; 98926650.5 Linux [K]Ubuntu 7.04 (feisty) sqlite3 package 3.3.13-0ubuntu1: SQLite version 3.3.13 Enter ".help" for instructions sqlite> select round(98926650.5, 1); 98926650.5 sqlite> select round(85227887.01, 1); 85227887.0 sqlite> select round(85227887.01, 2); 85227887.01 sqlite> select round(98926650.50001, 1) ; 98926650.5 $ uname -a Linux ubuno 2.6.20-16-generic #2 SMP Thu Jun 7 20:19:32 UTC 2007 i686 GNU/Linux So it seems SQLite is already doing the right job. Maybe some OS specific error? Wasn't there some discussion earlier about the Microsoft compiler not using the full double precision by default? Regards, ~Nuno Lucas > e > > -- > Doug Currie > Londonderry, NH, USA - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: select round(98926650.50001, 1) ?
On Monday, September 03, 2007 Arjen Markus wrote: > Serena Lien wrote: >>Okay, but even if the rounded result does not have a finite binary >>representation, shouldn't the displayed (human readable) representation be >>at least truncated to the number of decimal places that were requested in >>the round function? Not that I am confusing round with truncate, but surely >>it is a more acceptable result? >> >> > Hm, that is a completely different question. It would mean that more > information is > associated with the rounded result than merely the number (in internal > representation). No. See the paper cited in my earlier email: http://portal.acm.org/citation.cfm?id=93559 Once the number is rounded (correctly), there is enough information in the number itself. It can be displayed with exactly the smallest number of digits necessary to reconstruct the number. This will be the number of digits that Serena expects. e -- Doug Currie Londonderry, NH, USA - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: select round(98926650.50001, 1) ?
On Monday, September 03, 2007 Arjen Markus wrote: > Serena Lien wrote: >>round(98926650.5, 1) -> 98926650.501 >>round(85227887.01, 1) -> 85227887.001 > They are in fact rounded, but the internal binary representation can > not be turned into the appropriate decimal (and human readable) > representation due to the finite precision. 98926650.5 is represented exactly in IEEE double; something else is mucking up the round or the display of the result, maybe both. 85227887.0 is represented exactly in IEEE double, though 85227887.01 is not. Nevertheless, there are ways to print floating point numbers readably. http://portal.acm.org/citation.cfm?id=93559 e -- Doug Currie Londonderry, NH, USA - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: select round(98926650.50001, 1) ?
On 9/3/07, Serena Lien <[EMAIL PROTECTED]> wrote: > Okay, but even if the rounded result does not have a finite binary > representation, shouldn't the displayed (human readable) representation be > at least truncated to the number of decimal places that were requested in > the round function? Not that I am confusing round with truncate, but surely > it is a more acceptable result? One way you could do this would be to implement your own round() function to return a string instead of a number. That way it would display the right result. Look at func.c [1] to see how round() is implemented. Regards, ~Nuno Lucas [1] http://www.sqlite.org/cvstrac/fileview?f=sqlite/src/func.c=1.174 > > thanks, Serena. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: select round(98926650.50001, 1) ?
Thanks for pointing out the implementation issues. I was thinking only in the case where I am asking for a string result, not all the time, ie sqlite3_column_text, where I would want the string representation of the rounded number in this format, and did not realize this would require storing all the string results.. I suppose this conversion should really be done in a wrapper function instead. thanks for your help, Serena. On 9/3/07, Arjen Markus <[EMAIL PROTECTED]> wrote: > > > Hm, that is a completely different question. It would mean that more > information is > associated with the rounded result than merely the number (in internal > representation). > I do not know enough about the way SQLite organises these things to > speculate about > that, but it seems like a computational burden to me: > > Suppose you have one million records to search for numbers that are then > rounded. Not > only would you need to compute and store the rounded numbers but also > the string > representation (or something to effect that you can get a proper decimal > presentation). > > Regards, > > Arjen > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > > - > >
Re: [sqlite] Re: select round(98926650.50001, 1) ?
Serena Lien wrote: Okay, but even if the rounded result does not have a finite binary representation, shouldn't the displayed (human readable) representation be at least truncated to the number of decimal places that were requested in the round function? Not that I am confusing round with truncate, but surely it is a more acceptable result? Hm, that is a completely different question. It would mean that more information is associated with the rounded result than merely the number (in internal representation). I do not know enough about the way SQLite organises these things to speculate about that, but it seems like a computational burden to me: Suppose you have one million records to search for numbers that are then rounded. Not only would you need to compute and store the rounded numbers but also the string representation (or something to effect that you can get a proper decimal presentation). Regards, Arjen - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: select round(98926650.50001, 1) ?
Okay, but even if the rounded result does not have a finite binary representation, shouldn't the displayed (human readable) representation be at least truncated to the number of decimal places that were requested in the round function? Not that I am confusing round with truncate, but surely it is a more acceptable result? thanks, Serena. On 9/3/07, Arjen Markus <[EMAIL PROTECTED]> wrote: > > Serena Lien wrote: > > >I have read some of the postings/faq about the difficulties with the > round > >function, when certain numbers do not have a finite representation in > >binary, which SQLite uses. eg 9.95 is actually 9.9499...etc so that > round( > >9.95, 1) rounds down. > > > >But, I have found several numbers which don't get rounded at all, and in > >fact return more decimal places! > > > >round(98926650.5, 1) -> 98926650.501 > >round(85227887.01, 1) -> 85227887.001 > > > >Even if these numbers cannot be represented properly in binary, why is it > >they aren't rounded? > > > >thanks for any assistance, > >Serena > > > > > They are in fact rounded, but the internal binary representation can not > be turned > into the appropriate decimal (and human readable) representation due to > the > finite precision. > > A number like 1.511 can be rounded to 1.5 and that is _exactly_ > representable > as binary number. But if, as in your examples, the number requires more > precision > than is available (remember: only a finite number of digits/bits in > total, no matter > what the position of the decimal point/comma), the old problem of > binary-to- > decimal conversion kicks in again. > > Regards, > > Arjen > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > > - > >
Re: [sqlite] Re: select round(98926650.50001, 1) ?
Serena Lien wrote: I have read some of the postings/faq about the difficulties with the round function, when certain numbers do not have a finite representation in binary, which SQLite uses. eg 9.95 is actually 9.9499...etc so that round( 9.95, 1) rounds down. But, I have found several numbers which don't get rounded at all, and in fact return more decimal places! round(98926650.5, 1) -> 98926650.501 round(85227887.01, 1) -> 85227887.001 Even if these numbers cannot be represented properly in binary, why is it they aren't rounded? thanks for any assistance, Serena They are in fact rounded, but the internal binary representation can not be turned into the appropriate decimal (and human readable) representation due to the finite precision. A number like 1.511 can be rounded to 1.5 and that is _exactly_ representable as binary number. But if, as in your examples, the number requires more precision than is available (remember: only a finite number of digits/bits in total, no matter what the position of the decimal point/comma), the old problem of binary-to- decimal conversion kicks in again. Regards, Arjen - To unsubscribe, send email to [EMAIL PROTECTED] -
