[sqlite] Rounding Error ,,, Last Patch was incorrect (Sorry)

[Keith Medcalf]

That last patch was a bad workaround -- please ignore it.  

The et_getdigit function does count the number of significant digits returned.  
However, it has a wrong boundary.  Limiting the number of significant digits is 
more correct than changing the output precision.  So, the following changes 
(against the amalgamation) will always use and round to the same number of 
significant digits specified by a preprocessor macro SQLITE_SIGNIFICANT_DIGITS, 
which defaults to 14.  The rounding function now works correctly again (I broke 
it under certain circumstances in the other patch).  Also, all output format 
functions use the default specified significant digits and round equally.  If 
you use a specific format in SQL printf function, it will behave properly, and 
if you do not specify a precision or exceed the number of significant digits, 
the results will be correct.  Output in the vdbe code is unchanged (only user 
value I/O is affected by removing the precision specifier from the printf's in 
vdbemem.c).

--- sqlite3.c
+++ sqlite3.c
@@ -19594,17 +19594,20 @@
 ** Example:
 ** input: *val = 3.14159
 ** output:*val = 1.4159function return = '3'
 **
 ** The counter *cnt is incremented each time.  After counter exceeds
-** 16 (the number of significant digits in a 64-bit float) '0' is
-** always returned.
+** SQLITE_SIGNIFICANT_DIGITS '0' is always returned.  Default to 14
+** digits for IEEE 754 Floating Point
 */
+#ifndef SQLITE_SIGNIFICANT_DIGITS
+#define SQLITE_SIGNIFICANT_DIGITS 14
+#endif
 static char et_getdigit(LONGDOUBLE_TYPE *val, int *cnt){
   int digit;
   LONGDOUBLE_TYPE d;
-  if( (*cnt)++ >= 16 ) return '0';
+  if( (*cnt)++ >= SQLITE_SIGNIFICANT_DIGITS ) return '0';
   digit = (int)*val;
   d = digit;
   digit += '0';
   *val = (*val - d)*10.0;
   return (char)digit;
@@ -59458,11 +59461,11 @@
   */
   if( fg & MEM_Int ){
 sqlite3_snprintf(nByte, pMem->z, "%lld", pMem->u.i);
   }else{
 assert( fg & MEM_Real );
-sqlite3_snprintf(nByte, pMem->z, "%!.15g", pMem->r);
+sqlite3_snprintf(nByte, pMem->z, "%!g", pMem->r);
   }
   pMem->n = sqlite3Strlen30(pMem->z);
   pMem->enc = SQLITE_UTF8;
   pMem->flags |= MEM_Str|MEM_Term;
   sqlite3VdbeChangeEncoding(pMem, enc);
@@ -65065,11 +65068,11 @@
   if( pVar->flags & MEM_Null ){
 sqlite3StrAccumAppend(, "NULL", 4);
   }else if( pVar->flags & MEM_Int ){
 sqlite3XPrintf(, "%lld", pVar->u.i);
   }else if( pVar->flags & MEM_Real ){
-sqlite3XPrintf(, "%!.15g", pVar->r);
+sqlite3XPrintf(, "%!g", pVar->r);
   }else if( pVar->flags & MEM_Str ){
 #ifndef SQLITE_OMIT_UTF16
 u8 enc = ENC(db);
 if( enc!=SQLITE_UTF8 ){
   Mem utf8;

I'll send the canonical source patches shortly.



---
()  ascii ribbon campaign against html e-mail
/\  www.asciiribbon.org




___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] VERY weird rounding error

[Keith Medcalf]

> Ideally, you write your software so that differences in the 15th decimal
> place don't matter to you.  If you want two platforms to give identical
> results you use integer arithmetic.  It really depends on what kind of
> application you're writing (scientific, financial) or what your numbers
> actually represent.

This is technically incorrect.  Floating Point accuracy is in Decimal Digits, 
not Decimal Places (unless, of course, the decimal formatted output is in 
"Engineering Notation" in which case Decimal Digits == Decimal Places -- 
Engineering Notation means that the number to the left of the decimal point is 
0).  

The "theoretical" accuracy (and the "reasonably expected" accuracy) is:

Half (2 Bytes)  ~ 3.32
Single (4 Bytes)~ 7.26
Double (8 bytes)~15.9   14
Double extended (10 Byte)   ~19.2   18
Quad (  16 Bytes)   ~34.0   32

The value 9990.1 has 5 significant digits.  
The output value 0.11 has 15 significant digits, of which the 
expected 14 are accurate.

Etienne, thanks for the thanks.

---
()  ascii ribbon campaign against html e-mail
/\  www.asciiribbon.org


> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Simon Slavin
> Sent: Saturday, 16 June, 2012 17:33
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] VERY weird rounding error
> 
> 
> On 17 Jun 2012, at 12:06am, Etienne  wrote:
> 
> > Is there a trick (in the sys. libraries mentioned above, or through the
> win32 C API) for changing the way the FPU handles (long) doubles?
> 
> You're at tricky low level detail now.  Rounding and truncation modes used by
> chips which implement IEEE754 are often carefully set by compiler writers to
> get the results they want.  If you mess with the settings they've chosen, you
> can get unexpected results in other apps which were previously working
> correctly.
> 
> 
> It's discovering and caring about this sort of thing that separates out the
> professional programmer from the someone who shouldn't be paid for
> programming.
> 
> I do appreciate your thanks, by the way.
> 
> Simon.
> ___
> 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

Re: [sqlite] VERY weird rounding error

[Simon Slavin]

On 17 Jun 2012, at 12:06am, Etienne  wrote:

> Is there a trick (in the sys. libraries mentioned above, or through the win32 
> C API) for changing the way the FPU handles (long) doubles?

You're at tricky low level detail now.  Rounding and truncation modes used by 
chips which implement IEEE754 are often carefully set by compiler writers to 
get the results they want.  If you mess with the settings they've chosen, you 
can get unexpected results in other apps which were previously working 
correctly.

Ideally, you write your software so that differences in the 15th decimal place 
don't matter to you.  If you want two platforms to give identical results you 
use integer arithmetic.  It really depends on what kind of application you're 
writing (scientific, financial) or what your numbers actually represent.

It's discovering and caring about this sort of thing that separates out the 
professional programmer from the someone who shouldn't be paid for programming.

I do appreciate your thanks, by the way.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] VERY weird rounding error

[Etienne]

If ever I forget later, I would like to thank you guys (esp. Simon, Dominique, 
Keith and Jay) for the time you spend on this issue.

Your idea of dumping the memory at  was a very good idea, Simon.

You were right: "realvalue" values are already slightly different (2 less 
significant bytes of the 10-bytes equ. string) after the first iteration (i.e. 
realvalue*=0.1) of the "while" loop (sqlite3.c:19911), while they are still 
byte-to-byte identical at sqlite3.c:19908.

I use exactly the same byte code (i.e. the same sqlite3.o module), Keith.

So I summarize the situation:
realvalue * 0.1 = res1 in sqlite3.o linked to shell.o
realvalue * 0.1 = res2 in sqlite3.o linked to JSDB  - with res1!=res2!!

The JSDB win32 executable is built like this:

gcc.exe -static-libgcc -o jsdb.exe -mconsole -mwindows -mole obj\*.o  
obj\jsdb.coff  -lole32 -lws2_32 -lodbc32 -loleaut32 -lmapi32 -lcomdlg32 -luuid 
-lwinmm -static -lstdc++ 

while I simply build the SQLite exec with "gcc -o SQLite.exe sqlite3.o shell.o".

Is there a trick (in the sys. libraries mentioned above, or through the win32 C 
API) for changing the way the FPU handles (long) doubles?


Etienne
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Patch Precision Specifier: (was: VERY weird rounding error)

[Keith Medcalf]

Format specifiers are forcing 15 digits of precision, so the default precision 
of 6 is being ignored.  Here is a patch that fixes the code in the amalgamation 
so the default precision of 6 will be used for floating point to text 
operations.  I don't know if both need to be patched, but this is the 
root-cause of the default precision in the conversion routines not having any 
effect.

Index: sqlite3.c
==
--- sqlite3.c
+++ sqlite3.c
@@ -59458,11 +59458,11 @@
   */
   if( fg & MEM_Int ){
 sqlite3_snprintf(nByte, pMem->z, "%lld", pMem->u.i);
   }else{
 assert( fg & MEM_Real );
-sqlite3_snprintf(nByte, pMem->z, "%!.15g", pMem->r);
+sqlite3_snprintf(nByte, pMem->z, "%!g", pMem->r);
   }
   pMem->n = sqlite3Strlen30(pMem->z);
   pMem->enc = SQLITE_UTF8;
   pMem->flags |= MEM_Str|MEM_Term;
   sqlite3VdbeChangeEncoding(pMem, enc);
@@ -65065,11 +65065,11 @@
   if( pVar->flags & MEM_Null ){
 sqlite3StrAccumAppend(, "NULL", 4);
   }else if( pVar->flags & MEM_Int ){
 sqlite3XPrintf(, "%lld", pVar->u.i);
   }else if( pVar->flags & MEM_Real ){
-sqlite3XPrintf(, "%!.15g", pVar->r);
+sqlite3XPrintf(, "%!g", pVar->r);
   }else if( pVar->flags & MEM_Str ){
 #ifndef SQLITE_OMIT_UTF16
 u8 enc = ENC(db);
 if( enc!=SQLITE_UTF8 ){
   Mem utf8;

Or in the fossil repository:

D:\Source\sqlite3\src>fossil diff
Index: src/vdbemem.c
==
--- src/vdbemem.c
+++ src/vdbemem.c
@@ -224,11 +224,11 @@
   */
   if( fg & MEM_Int ){
 sqlite3_snprintf(nByte, pMem->z, "%lld", pMem->u.i);
   }else{
 assert( fg & MEM_Real );
-sqlite3_snprintf(nByte, pMem->z, "%!.15g", pMem->r);
+sqlite3_snprintf(nByte, pMem->z, "%!g", pMem->r);
   }
   pMem->n = sqlite3Strlen30(pMem->z);
   pMem->enc = SQLITE_UTF8;
   pMem->flags |= MEM_Str|MEM_Term;
   sqlite3VdbeChangeEncoding(pMem, enc);

Index: src/vdbetrace.c
==
--- src/vdbetrace.c
+++ src/vdbetrace.c
@@ -119,11 +119,11 @@
   if( pVar->flags & MEM_Null ){
 sqlite3StrAccumAppend(, "NULL", 4);
   }else if( pVar->flags & MEM_Int ){
 sqlite3XPrintf(, "%lld", pVar->u.i);
   }else if( pVar->flags & MEM_Real ){
-sqlite3XPrintf(, "%!.15g", pVar->r);
+sqlite3XPrintf(, "%!g", pVar->r);
   }else if( pVar->flags & MEM_Str ){
 #ifndef SQLITE_OMIT_UTF16
 u8 enc = ENC(db);
 if( enc!=SQLITE_UTF8 ){
   Mem utf8;


If there was a reason for setting the precision to 15, then perhaps another 
SQLITE_DEFAULT_PRECISION could be defined with a default of 6, to permit the 
build to override the default in printf.c

--- printf.c
+++ printf.c
@@ -154,10 +154,17 @@
 #ifndef SQLITE_PRINT_BUF_SIZE
 # define SQLITE_PRINT_BUF_SIZE 70
 #endif
 #define etBUFSIZE SQLITE_PRINT_BUF_SIZE  /* Size of the output buffer */

+/*
+** Default Precision is 6 and may be overridden
+*/
+#ifndef SQLITE_DEFAULT_PRECISION
+#define SQLITE_DEFAULT_PRECISION 6
+#endif
+
 /*
 ** Render a string given by "fmt" into the StrAccum object.
 */
 void sqlite3VXPrintf(
   StrAccum *pAccum,  /* Accumulate results here */
@@ -400,11 +407,11 @@
   case etGENERIC:
 realvalue = va_arg(ap,double);
 #ifdef SQLITE_OMIT_FLOATING_POINT
 length = 0;
 #else
-if( precision<0 ) precision = 6; /* Set default precision */
+if( precision<0 ) precision = SQLITE_DEFAULT_PRECISION; /* Set 
default precision */
 if( realvalue<0.0 ){
   realvalue = -realvalue;
   prefix = '-';
 }else{
   if( flag_plussign )  prefix = '+';

---
()  ascii ribbon campaign against html e-mail
/\  www.asciiribbon.org


> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Keith Medcalf
> Sent: Saturday, 16 June, 2012 14:00
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] VERY weird rounding error
> 
> 
> Found the problem.
> 
> At sqlite3.c 19883 if( precision<0 ) precision = 6; /* Set default
> precision */
> 
> precision has a value of 15.  It should be 14 or less.  Don't know where it
> is getting this value from however.
> 
> 
> 
> ---
> ()  ascii ribbon campaign against html e-mail
> /\  www.asciiribbon.org
> 
> 
> > -Original Message-
> > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> > boun...@sqlite.org] On Behalf Of Keith Medcalf
> > Sent: Saturday, 16 June, 2012 13:28
> > To: General Discussion of SQLite Database
> > Subject: Re: [sqlite] VERY weird rounding error
> >
> >
> > Are you using the "same machine code" or merely "the same source code"?
> >
> > The sqlite3 shell.c always asks for the results back converted to text.
> >
> > Does your "other" shell ask for the results as "text" or as a double?
> >

Re: [sqlite] VERY weird rounding error

[Jay A. Kreibich]

On Sat, Jun 16, 2012 at 02:00:27PM -0600, Keith Medcalf scratched on the wall:
> 
> Found the problem.
> 
> At sqlite3.c 19883:
>   if( precision<0 ) precision = 6;  /* Set default precision */
> 
> precision has a value of 15.  It should be 14 or less.  Don't know
> where it is getting this value from however.

  If by "this value" you mean the 6, that's the default precision for
  "%f" conversion in the printf() family of functions.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] VERY weird rounding error

[Keith Medcalf]

Found the problem.

At sqlite3.c 19883 if( precision<0 ) precision = 6; /* Set default 
precision */

precision has a value of 15.  It should be 14 or less.  Don't know where it is 
getting this value from however.



---
()  ascii ribbon campaign against html e-mail
/\  www.asciiribbon.org


> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Keith Medcalf
> Sent: Saturday, 16 June, 2012 13:28
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] VERY weird rounding error
> 
> 
> Are you using the "same machine code" or merely "the same source code"?
> 
> The sqlite3 shell.c always asks for the results back converted to text.
> 
> Does your "other" shell ask for the results as "text" or as a double?
> 
> ...>python
> Python 2.7.3 (default, Apr 10 2012, 23:31:26) [MSC v.1500 32 bit (Intel)] on
> win32
> Type "help", "copyright", "credits" or "license" for more information.
> >>> import apsw
> >>> import apswrow
> >>> import math
> >>> db = apsw.Connection(':memory:')
> >>> cr = db.cursor()
> >>> r1 = cr.execute('select 0.1 as double;').next()
> >>> r2 = cr.execute('select cast(0.1 as text) as textval;').next()
> 
> >>> r1
> Row(double=0.1)
> 
> >>> type(r1[0])
> 
> 
> >>> math.frexp(r1[0])
> (0.7628639221191407, 17)
> 
> >>> math.frexp(float('0.1'))
> (0.7628639221191407, 17)
> 
> >>> r2
> Row(textval=u'0.11')
> >>> type(r2[0])
> 
> 
> >>> math.frexp(float(r2[0]))
> (0.7628639221191413, 17)
> 
> ...>sqlite3d
> SQLite version 3.7.13 2012-06-11 02:05:22
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> select 0.1;
> 0.11
> sqlite> select cast(0.1 as double);
> 0.11
> sqlite> select cast(0.1 as text);
> 0.11
> sqlite> select 0.11;
> 0.11
> 
> My conclusion is that the conversion from floating point to text in the
> SQLite database engine is inexact and designed as a convenience function
> only.  However, it is consistent. My recommendation would be that if you want
> "text" then use "text" everywhere.  If you want floating point, then use
> floating point everywhere.  You should not expect that wild conversions
> should produce the results you want.
> 
> ---
> ()  ascii ribbon campaign against html e-mail
> /\  www.asciiribbon.org
> 
> 
> > -Original Message-
> > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> > boun...@sqlite.org] On Behalf Of Etienne
> > Sent: Saturday, 16 June, 2012 11:32
> > To: General Discussion of SQLite Database
> > Subject: [sqlite] VERY weird rounding error
> >
> > Hi Simon,
> >
> > Once again, I really do not care of the accuracy.
> >
> > I KNOW 0.1 CAN NOT BE STORED EXACTLY IN A REAL VARIABLE.
> >
> > I just try to figure out why the SAME CODE (sqlite3 library) with the SAME
> > INPUT gives DIFFERENT RESULTS (no matter how accurate/exact they
> individually
> > are!) when linked to different shells.
> >
> >
> > Etienne
> >
> >
> > - Original message -
> > From: Simon Slavin 
> > To: General Discussion of SQLite Database 
> > Subject: Re: [sqlite] VERY weird rounding error: details
> > Date: Sat, 16 Jun 2012 18:13:29 +0100
> >
> >
> > On 16 Jun 2012, at 5:41pm, Etienne  wrote:
> >
> > > 19913:   realvalue *= 0.1;
> >
> > This operation cannot be correctly executed while holding the value in a
> real
> > variable.  You will get inconsistent results.  Please read the reference I
> > gave earlier for an explanation of why it is pointless trying to get
> perfect
> > accuracy while handling a value of 0.1 in a real variable.
> >
> > Simon.
> > ___
> > 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



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] VERY weird rounding error

[Keith Medcalf]

Are you using the "same machine code" or merely "the same source code"?

The sqlite3 shell.c always asks for the results back converted to text.

Does your "other" shell ask for the results as "text" or as a double?

...>python
Python 2.7.3 (default, Apr 10 2012, 23:31:26) [MSC v.1500 32 bit (Intel)] on 
win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import apsw
>>> import apswrow
>>> import math
>>> db = apsw.Connection(':memory:')
>>> cr = db.cursor()
>>> r1 = cr.execute('select 0.1 as double;').next()
>>> r2 = cr.execute('select cast(0.1 as text) as textval;').next()

>>> r1
Row(double=0.1)

>>> type(r1[0])


>>> math.frexp(r1[0])
(0.7628639221191407, 17)

>>> math.frexp(float('0.1'))
(0.7628639221191407, 17)

>>> r2
Row(textval=u'0.11')
>>> type(r2[0])


>>> math.frexp(float(r2[0]))
(0.7628639221191413, 17)

...>sqlite3d
SQLite version 3.7.13 2012-06-11 02:05:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select 0.1;
0.11
sqlite> select cast(0.1 as double);
0.11
sqlite> select cast(0.1 as text);
0.11
sqlite> select 0.11;
0.11

My conclusion is that the conversion from floating point to text in the SQLite 
database engine is inexact and designed as a convenience function only.  
However, it is consistent. My recommendation would be that if you want "text" 
then use "text" everywhere.  If you want floating point, then use floating 
point everywhere.  You should not expect that wild conversions should produce 
the results you want.

---
()  ascii ribbon campaign against html e-mail
/\  www.asciiribbon.org


> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Etienne
> Sent: Saturday, 16 June, 2012 11:32
> To: General Discussion of SQLite Database
> Subject: [sqlite] VERY weird rounding error
> 
> Hi Simon,
> 
> Once again, I really do not care of the accuracy.
> 
> I KNOW 0.1 CAN NOT BE STORED EXACTLY IN A REAL VARIABLE.
> 
> I just try to figure out why the SAME CODE (sqlite3 library) with the SAME
> INPUT gives DIFFERENT RESULTS (no matter how accurate/exact they individually
> are!) when linked to different shells.
> 
> 
> Etienne
> 
> 
> - Original message -
> From: Simon Slavin 
> To: General Discussion of SQLite Database 
> Subject: Re: [sqlite] VERY weird rounding error: details
> Date: Sat, 16 Jun 2012 18:13:29 +0100
> 
> 
> On 16 Jun 2012, at 5:41pm, Etienne  wrote:
> 
> > 19913:   realvalue *= 0.1;
> 
> This operation cannot be correctly executed while holding the value in a real
> variable.  You will get inconsistent results.  Please read the reference I
> gave earlier for an explanation of why it is pointless trying to get perfect
> accuracy while handling a value of 0.1 in a real variable.
> 
> Simon.
> ___
> 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

Re: [sqlite] VERY weird rounding error

[Simon Slavin]

On 16 Jun 2012, at 6:32pm, Etienne  wrote:

> Once again, I really do not care of the accuracy.
> 
> I KNOW 0.1 CAN NOT BE STORED EXACTLY IN A REAL VARIABLE.

I am unsurprised to find that your decimal strings are different after the 11th 
decimal place because your REAL values are not stored to 15 decimal places, and 
the calculations done with the numbers stored as REAL are not accurate to a 
precision of 10^-15.  Your number

0.901029468

involves a calculation involving the number

999.901029468

which has 20 decimal digits.  Calculations performed on Double-lenght REAL 
numbers are accurate only to 15 decimal digits.  Anything after that is just 
noise and can be ignored.  It doesn't matter what shows, because the programmer 
should never be showing the number to that much precision.

You posted

>>> Please note that the "realvalue" variable has identical values at the first 
>>> loop pass.


You have no way of knowing that.  You are seeing the value converted into a 
text string in an attempt to show it in decimal.  For all you know, the values 
are different, but they're being shown as the same text string because the 
difference is lost after the last digit.

There's no simple way to find out where the values are becoming different 
unless you dump the piece of memory the values are being stored in.  Do the 
calculation realvalue-999 in your code, store the resulting value in a 
variable, and dump the piece of memory that values is stored in (preferably as 
binary, but hexadecimal is acceptable).  Then look at the results from your two 
environments and see whether they're the same.

Your different maths libraries may do any of the following

1) turn your string '0.1' into different bit patterns, in order to store it as 
a binary value
2) do the same calculations but get different results because they round the 
right-hand bits differently
3) turn the same binary value into a different text string, when you ask to see 
the results in decimal.

This can happen
in different programming languages,
or in the same program compiled by two different compilers,
or in the same program compiled by the same compiler for two different 
platforms,
or in the same object code running on two identical platforms except for them 
having different CPUs,
or on identical platforms set to do rounding differently.

>From what I see, one of your programs is a C program and the other runs under 
>JSDB, whatever that is.  But you've now introduced another complication 
>because you're using the binary-to-decimal routines in gdb to show the results 
>as decimal, so instead of two environments you now have three.  Even if 
>they're both using the same IEEE754 algorithms You have no idea what rounding 
>mode each library uses.

For at least 50 years we've known that if you see a computer spit out a decimal 
number like

0.1 [lots of zeros here] 82734766

anything after the long line of zeros is rubbish.  It's perfectly predictable 
-- if you understand how your CPU does maths -- but it's not important.  
Because we know that storing values like 0.1 in a REAL variable will lead to 
that sort of thing because they cannot be stored accurately so it will happen 
every time.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] VERY weird rounding error

[Dominique Pellé]

Etienne  wrote:

> Hi Simon,
>
> Once again, I really do not care of the accuracy.
>
> I KNOW 0.1 CAN NOT BE STORED EXACTLY IN A REAL VARIABLE.
>
> I just try to figure out why the SAME CODE (sqlite3 library) with the SAME 
> INPUT gives DIFFERENT RESULTS (no matter how accurate/exact they individually 
> are!) when linked to different shells.
>
>
> Etienne


The compiler may not produce strict IEEE-754 floating point
computations: trade off between optimizations and strict
conformance. Floating registers registers have more bits
than double IEEE 754 (80 bit registers, i.e. more accuracy
than double 64 bits). So strict conformance to IEEE would be
not not only slower but can also be less accurate.

Now if you need strict conformance to IEEE-754, you
can give options to the compiler.  Try compiling with
gcc option -ffloat-store for example. There might be
other relevant options. Also turn off optimizations (-O0).

Some links:

http://stackoverflow.com/questions/7295861/enabling-strict-floating-point-mode-in-gcc
http://stackoverflow.com/questions/982421/how-to-write-portable-floating-point-arithmetic-in-c/

Regards
-- Dominique
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] VERY weird rounding error

[Etienne]

Hi Simon,

Once again, I really do not care of the accuracy.

I KNOW 0.1 CAN NOT BE STORED EXACTLY IN A REAL VARIABLE.

I just try to figure out why the SAME CODE (sqlite3 library) with the SAME 
INPUT gives DIFFERENT RESULTS (no matter how accurate/exact they individually 
are!) when linked to different shells.


Etienne


- Original message -
From: Simon Slavin 
To: General Discussion of SQLite Database 
Subject: Re: [sqlite] VERY weird rounding error: details
Date: Sat, 16 Jun 2012 18:13:29 +0100


On 16 Jun 2012, at 5:41pm, Etienne  wrote:

> 19913:   realvalue *= 0.1;

This operation cannot be correctly executed while holding the value in a real 
variable.  You will get inconsistent results.  Please read the reference I gave 
earlier for an explanation of why it is pointless trying to get perfect 
accuracy while handling a value of 0.1 in a real variable.

Simon.
___
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

Re: [sqlite] VERY weird rounding error: details

[Simon Slavin]

On 16 Jun 2012, at 5:41pm, Etienne  wrote:

> 19913:   realvalue *= 0.1;

This operation cannot be correctly executed while holding the value in a real 
variable.  You will get inconsistent results.  Please read the reference I gave 
earlier for an explanation of why it is pointless trying to get perfect 
accuracy while handling a value of 0.1 in a real variable.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] error group_concat

[Kees Nuyt]

On Sat, 16 Jun 2012 15:13:49 +, "Black, Michael (IS)"
 wrote:

>
>On my Windows 3.7.12 version it gets a segfault.
>On my Unix 3.7.13 version I get the misuse of aggregate

SunOS ozon 5.11 snv_130 i86pc i386 i86pc

3.7.12 2012-03-31 19:12:23 af602d87736b52802a4e760ffeeaa28112b99d9a
still works

3.7.12 2012-04-24 13:14:49 dfce8569765614462a3952d1761c10d579984665
3.7.12 2012-04-27 01:09:06 a49e909c8738317c8383ce93771c0a9c4cf270bc
3.7.12 2012-05-01 14:21:57 bfa61e781cb442be641486e7e55a1518e888d830
3.7.12 2012-05-12 22:36:03 af525b5d25b5dee20528acc8321b8c6ca9aa98ae
all report Memory fault(coredump)

3.7.12.1 2012-05-22 02:45:53 6d326d44fd1d626aae0e8456e5fa2049f1ce0789
Error: misuse of aggregate: GROUP_CONCAT()

As far as I recollect, indeed there was a bugfix for segfaults in that
timeframe.

I agree with Igor, it is not clear what the purpose of the query would
be.

-- 
Regards,

Kees Nuyt

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] VERY weird rounding error: details

[Etienne]

Hi all,

The "0.1 case" is still a mystery!

For clarity reasons, I have expanded line 19911 of sqlite3.c (v3.7.13)


19909: while( realvalue>=1e32 && exp<=350 ){ realvalue *= 1e-32; exp+=32; }
19910: while( realvalue>=1e8 && exp<=350 ){ realvalue *= 1e-8; exp+=8; }
19911: while( realvalue>=10.0 && exp<=350 ){ realvalue *= 0.1; exp++; }
19912: while( realvalue<1e-8 ){ realvalue *= 1e8; exp-=8; }
19913: while( realvalue<1.0 ){ realvalue *= 10.0; exp--; }

as:

19909: while( realvalue>=1e32 && exp<=350 ){ realvalue *= 1e-32; exp+=32; }
19910: while( realvalue>=1e8 && exp<=350 ){ realvalue *= 1e-8; exp+=8; }
19911: while( realvalue>=10.0 && exp<=350 )
19912:   {
19913:   realvalue *= 0.1;
19914:   exp++;
19915:   }
19916: while( realvalue<1e-8 ){ realvalue *= 1e8; exp-=8; }
19917: while( realvalue<1.0 ){ realvalue *= 10.0; exp--; }


No other modification.

SQLite3.c is compiled (gcc -c -g sqlite3.c) with default options ONE TIME ONLY.

(Env: Int. Core i5, Win7 Pro/32, gcc 4.5, gdb 7.2) 

The same object module is linked to shell.o (for SQLite) and to JSDB.

SQLite is then launched under GDB (run NUL "select 0.1;", breakpoint set up 
accordingly).

The debugging session looks like:


(...)
(gdb) print realvalue-0
$6 = 0.100582077
(gdb) n
19913   realvalue *= 0.1;
(gdb) print realvalue-0
$7 = 0.100582077
(gdb) n
19914   exp++;
(gdb) print realvalue-
$8 = 0.0102037268
(gdb) n
19913   realvalue *= 0.1;
(gdb) print realvalue-
$9 = 0.0102037268
(gdb) n
19914   exp++;
(gdb) print realvalue-999
$10 = 0.901018099  <--
(gdb)
(...)


JSDB run in the same environment with the matching input:

js>var db = new SQLite();
js>db.exec("select 0.1",function(r){writeln(r)});

Here is a piece of the debugging session (same breakpoint):


(...)
(gdb) print realvalue-0
$4 = 0.100582077
(gdb) n
19913   realvalue *= 0.1;
(gdb) print realvalue-0
$5 = 0.100582077
(gdb) n
19914   exp++;
(gdb) print realvalue-
$6 = 0.0102037268
(gdb) n
19913   realvalue *= 0.1;
(gdb) print realvalue-
$7 = 0.0102037268
(gdb) n
19914   exp++;
(gdb) print realvalue-999
$8 = 0.901029468   <--
(gdb)
(...)


Please note that the "realvalue" variable has identical values at the first 
loop pass.

They only start diverging (29468/18099 = +/-60%) from the second pass!

This divergence leads to a rounding error in the second case (JSDB), while 
SQLite(shell) properly displays the ("faked") result.

So same input, same byte code... and different results! Who the heck said IT is 
determinist?

Does anybody figure out the reason of the difference?

Thanks in advance.


Etienne
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] error group_concat

[Luuk]

On 16-06-2012 17:13, Black, Michael (IS) wrote:
> Works in 3.7.9
> 
> 
> On my Windows 3.7.12 version it gets a segfault.
> On my Unix 3.7.13 version I get the misuse of aggregate
> 
> 

works in 3.7.11 too  (Windows7):

SQLite version 3.7.11 2012-03-20 11:35:50
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TABLE t1 (A1 INTEGER NOT NULL,A2 INTEGER NOT NULL,A3
INTEGER NOT NULL,A4 INTEGER NOT NULL,PRIMARY KEY(A1));
sqlite> REPLACE INTO t1 VALUES(1,11,111,);
sqlite> REPLACE INTO t1 VALUES(2,22,222,);
sqlite> REPLACE INTO t1 VALUES(3,33,333,);
sqlite> CREATE TABLE t2 (B1 INTEGER NOT NULL,B2 INTEGER NOT NULL,B3
INTEGER NOT NULL,B4 INTEGER NOT NULL,PRIMARY KEY(B1));
sqlite> REPLACE INTO t2 VALUES(1,88,888,);
sqlite> REPLACE INTO t2 VALUES(2,99,999,);
sqlite> SELECT (SELECT GROUP_CONCAT(CASE WHEN a1=1 THEN'A' ELSE 'B' END)
FROM t2),t1.* FROM t1;
A,A|1|11|111|
B,B|2|22|222|
B,B|3|33|333|
sqlite>

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Strange behavior with fts4

[Yuriy Kaminskiy]

Philip Bennefall wrote:
> I hate to be cluttering up the list in this fashion, but I have come across 
> an issue that I cannot seem to find a solution for.
> 
> I am using two fts tables, one that uses the normal tokenizer and another 
> that uses the porter stemmer, so that I can search the same dataset with and 
> without porter. For the porter stemmer table, I have set the content option 
> to point to the other fts table. Like this:
> 
> CREATE VIRTUAL TABLE main_brain USING fts4(phrase, response, matchinfo=fts3, 
> tokenize=simple, order=desc);
> 
> CREATE VIRTUAL TABLE porter_brain USING fts4(content="main_brain", phrase, 
> response, matchinfo=fts3, tokenize=porter, order=desc);
> 
> Then I do the following:
> 
> INSERT INTO main_brain(phrase, response) VALUES('hello', 'Hi there!');
> 
> So you'd now think that main_brain should have this content in it, but the 
> porter_brain table should be empty. At least, that is what the documentation 
> on fts4 seems to indicate as it points out that it is my own responsibility 
> to make sure the tables are in sync. But:
> 
> SELECT * FROM porter_brain;
> 
> Produces:
> 
> hello|Hi there!

As porter_brain takes all content from main_brain, full scan result looks
"correct" ("SELECT FROM porter_brain" internally replaced with "SELECT FROM
main_brain"). But before you execute INSERT below, all fts indexes are missing,
so any request that utilize them, like

SELECT * FROM porter_brain WHERE phrase MATCH 'hello';

will fail.

> And:
> 
> SELECT * FROM main_brain;
> 
> Gives the same result. So it seems as though some internal synchronization 
> between these tables is going on. How should I handle this? The documentation
>  suggests a statement like the following in one of its examples:
> 
> INSERT INTO t3(docid, b, c) SELECT id, b, c FROM t2;

> Where I guess t3 would correspond to porter_brain and t2 would be main_brain 
> in my case. But I don't seem to need to do this at all.

INSERT INTO porter_brain (docid, phrase, response)
   SELECT docid, phrase, response FROM main_brain;

Before you execute this insert statement, porter_brain state is *inconsistent*
and it cannot be used properly.

[FWIW, most natural place for this insert would be AFTER INSERT trigger ... but
as main_brain is VIRTUAL TABLE, it is impossible; you can consider using
external content table for both virtual tables with trigger:

CREATE TABLE tblContent(phrase TEXT, response TEXT);
CREATE VIRTUAL TABLE main_brain USING fts4(content="tblContent", phrase,
response, matchinfo=fts3, tokenize=simple, order=desc);
CREATE VIRTUAL TABLE porter_brain USING fts4(content="tblContent", phrase,
response, matchinfo=fts3, tokenize=porter, order=desc);
CREATE TRIGGER trgSync AFTER INSERT ON tblContent FOR EACH ROW
 BEGIN
  INSERT INTO main_brain (docid, phrase, response)
   VALUES (NEW.rowid, NEW.phrase, NEW.response);
  INSERT INTO porter_brain (docid, phrase, response)
   VALUES (NEW.rowid, NEW.phrase, NEW.response);
   END;
].

> Can anyone throw some light on this?
> 
> Thanks in advance.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] error group_concat

[Black, Michael (IS)]

Works in 3.7.9

SQLite version 3.7.9 2011-11-01 00:52:41
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TABLE t1 (A1 INTEGER NOT NULL,A2 INTEGER NOT NULL,A3 INTEGER NOT 
NULL,A4 INTEGER NOT NULL,PRIMARY KEY(A1));
sqlite> REPLACE INTO t1 VALUES(1,11,111,);
sqlite> REPLACE INTO t1 VALUES(2,22,222,);
sqlite> REPLACE INTO t1 VALUES(3,33,333,);
sqlite> CREATE TABLE t2 (B1 INTEGER NOT NULL,B2 INTEGER NOT NULL,B3 INTEGER NOT 
NULL,B4 INTEGER NOT NULL,PRIMARY KEY(B1));
sqlite> REPLACE INTO t2 VALUES(1,88,888,);
sqlite> REPLACE INTO t2 VALUES(2,99,999,);
sqlite>
sqlite> SELECT (SELECT GROUP_CONCAT(CASE WHEN a1=1 THEN'A' ELSE 'B' END) FROM 
t2),t1.* FROM t1;
A,A|1|11|111|
B,B|2|22|222|
B,B|3|33|333|

On my Windows 3.7.12 version it gets a segfault.
On my Unix 3.7.13 version I get the misuse of aggregate



Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems



From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Luigi [lu...@emme4.it]
Sent: Friday, June 15, 2012 8:31 AM
To: sqlite-users@sqlite.org
Subject: EXT :[sqlite] error group_concat


CREATE TABLE t1 (A1 INTEGER NOT NULL,A2 INTEGER NOT NULL,A3 INTEGER NOT NULL,A4 
INTEGER NOT NULL,PRIMARY KEY(A1));
REPLACE INTO t1 VALUES(1,11,111,);
REPLACE INTO t1 VALUES(2,22,222,);
REPLACE INTO t1 VALUES(3,33,333,);
CREATE TABLE t2 (B1 INTEGER NOT NULL,B2 INTEGER NOT NULL,B3 INTEGER NOT NULL,B4 
INTEGER NOT NULL,PRIMARY KEY(B1));
REPLACE INTO t2 VALUES(1,88,888,);
REPLACE INTO t2 VALUES(2,99,999,);

SELECT (SELECT GROUP_CONCAT(CASE WHEN a1=1 THEN'A' ELSE 'B' END) FROM t2),t1.* 
FROM t1;

from Sqlite 3.7.12
return error misuse of aggregate

Thanks in advance.

Luigi
___
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

Re: [sqlite] error group_concat

[Igor Tandetnik]

Luigi  wrote:
> CREATE TABLE t1 (A1 INTEGER NOT NULL,A2 INTEGER NOT NULL,A3 INTEGER NOT
> NULL,A4 INTEGER NOT NULL,PRIMARY KEY(A1));
> REPLACE INTO t1 VALUES(1,11,111,);
> REPLACE INTO t1 VALUES(2,22,222,);
> REPLACE INTO t1 VALUES(3,33,333,);
> CREATE TABLE t2 (B1 INTEGER NOT NULL,B2 INTEGER NOT NULL,B3 INTEGER NOT
> NULL,B4 INTEGER NOT NULL,PRIMARY KEY(B1));
> REPLACE INTO t2 VALUES(1,88,888,);
> REPLACE INTO t2 VALUES(2,99,999,);
> 
> SELECT (SELECT GROUP_CONCAT(CASE WHEN a1=1 THEN'A' ELSE 'B' END) FROM t2),t1.*
> FROM t1;
> 
> from Sqlite 3.7.12
> return error misuse of aggregate

Looks like a bug. The query seems OK to me, formally.

However, practically speaking, it doesn't make much sense. What are you trying 
to achieve here? As far as I can tell, the query says: for every row in t1, 
generate a string consisting of as many 'A's or 'B's as there are rows in table 
t2. Why would you want such a thing?
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] error group_concat

[Luigi]

CREATE TABLE t1 (A1 INTEGER NOT NULL,A2 INTEGER NOT NULL,A3 INTEGER NOT NULL,A4 
INTEGER NOT NULL,PRIMARY KEY(A1));
REPLACE INTO t1 VALUES(1,11,111,);
REPLACE INTO t1 VALUES(2,22,222,);
REPLACE INTO t1 VALUES(3,33,333,);
CREATE TABLE t2 (B1 INTEGER NOT NULL,B2 INTEGER NOT NULL,B3 INTEGER NOT NULL,B4 
INTEGER NOT NULL,PRIMARY KEY(B1));
REPLACE INTO t2 VALUES(1,88,888,);
REPLACE INTO t2 VALUES(2,99,999,);

SELECT (SELECT GROUP_CONCAT(CASE WHEN a1=1 THEN'A' ELSE 'B' END) FROM t2),t1.* 
FROM t1;

from Sqlite 3.7.12
return error misuse of aggregate

Thanks in advance.

Luigi
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] error group_concat

[Luigi]

CREATE TABLE t1 (A1 INTEGER NOT NULL,A2 INTEGER NOT NULL,A3 INTEGER NOT 
NULL,A4 INTEGER NOT NULL,PRIMARY KEY(A1));
REPLACE INTO t1 VALUES(1,11,111,);
REPLACE INTO t1 VALUES(2,22,222,);
REPLACE INTO t1 VALUES(3,33,333,);
CREATE TABLE t2 (B1 INTEGER NOT NULL,B2 INTEGER NOT NULL,B3 INTEGER NOT 
NULL,B4 INTEGER NOT NULL,PRIMARY KEY(B1));
REPLACE INTO t2 VALUES(1,88,888,);
REPLACE INTO t2 VALUES(2,99,999,);

SELECT (SELECT GROUP_CONCAT(CASE WHEN a1=1 THEN'A' ELSE 'B' END) FROM t2),t1.* 
FROM t1;

from Sqlite 3.7.12
return error misuse of aggregate

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Strange behavior with fts4

[Philip Bennefall]

I hate to be cluttering up the list in this fashion, but I have come across an 
issue that I cannot seem to find a solution for.

I am using two fts tables, one that uses the normal tokenizer and another that 
uses the porter stemmer, so that I can search the same dataset with and without 
porter. For the porter stemmer table, I have set the content option to point to 
the other fts table. Like this:

CREATE VIRTUAL TABLE main_brain USING fts4(phrase, response, matchinfo=fts3, 
tokenize=simple, order=desc);

CREATE VIRTUAL TABLE porter_brain USING fts4(content="main_brain", phrase, 
response, matchinfo=fts3, tokenize=porter, order=desc);

 Then I do the following:

INSERT INTO main_brain(phrase, response) VALUES('hello', 'Hi there!');

So you'd now think that main_brain should have this content in it, but the 
porter_brain table should be empty. At least, that is what the documentation on 
fts4 seems to indicate as it points out that it is my own responsibility to 
make sure the tables are in sync. But:

SELECT * FROM porter_brain;

Produces:

hello|Hi there!

And:

SELECT * FROM main_brain;

Gives the same result. So it seems as though some internal synchronization 
between these tables is going on. How should I handle this? The documentation 
suggests a statement like the following in one of its examples:

INSERT INTO t3(docid, b, c) SELECT id, b, c FROM t2;

Where I guess t3 would correspond to porter_brain and t2 would be main_brain in 
my case. But I don't seem to need to do this at all.

Can anyone throw some light on this?

Thanks in advance.

Kind regards,

Philip Bennefall
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] read sql file in c++

[Stephan Beal]

On Sat, Jun 16, 2012 at 4:32 AM, Simon Slavin  wrote:

> There is no way to do it in one command.  You can write your own C++ code
> to read the text file one line at a time and call sqlite3_exec() for that
> line.  There is an example of a C++ program which calls that function here:
>

be careful with "lines" - SQL lines end in semicolons, lines read with
C++'s std::getline() and friends end on newlines:

SELECT
  foo FROM
bar...

To copy a whole C++ stream:

std::ostringstream os;
std::istream is("filename");
is >> std::noskipws;
std::istream_iterator begin(is);
std::istream_iterator end;
std::copy(  begin, end, std::ostream_iterator(os, "")

now the contents are in os.str() resp. os.str().c_str().


-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users