Re: [sqlite] (shell dependent) library rounding error

[Dominique Pellé]

Etienne  wrote:

> R:\>sqlite NUL "select 0.1;"
> 0.1
>
>
> JSDB (www.jsdb.org) output:
>
> js>var db = new SQLite();
> js>db.exec("select 0.1", function(r){writeln(r)});
> 0.1=0.11
> true
>
>> You are not doing the same thing both times.  One time you are writing the 
>> result using SQLite.  The other time you are writing the result using a 
>> 'writeln()' command.  There is no 'writeln()' command in SQLite.
>
> THE PROBLEM OCCURS AT SQLITE LEVEL! The caller does not matter.
>
> Behaviors diverge from sqlite3(071300).c: line 19911 (var "realvalue") very 
> precisely.
>
>
> Regards,
> Etienne

More likely, the printf(...) (or the equivalent function) is done with
different precision in both case.  So it prints the same number
differently.

Anyway, you should not rely on 2 floating points being equal or not.
Floating points should be compared with a small margin.

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

[sqlite] (shell dependent) library rounding error

[Etienne]

> > The problem is: the "real" rounding error depends here on the shell (SQLite 
> > or JSDB) calling the SQLite library.

> Yes.  Different languages use different ways to hide the fact that they can't 
> hide the 0.1.

The difference of languages is irrelevant here.

Both shells call "SQLITE_API const unsigned char *sqlite3_column_text()". The 
returned string is not modified nor (re)converted by JSDB in any way.

Inputs are proven to be identical, so are compiler options.



> There's nothing you can do about this.

I have already fixed this issue - without figuring out what is exactly the 
problem. 

I just do not want to patch every future SQLite release.



 R:\>sqlite NUL "select 0.1;"
 0.1
 
 
 JSDB (www.jsdb.org) output:
 
 js>var db = new SQLite();
 js>db.exec("select 0.1", function(r){writeln(r)});
 0.1=0.11
 true

> You are not doing the same thing both times.  One time you are writing the 
> result using SQLite.  The other time you are writing the result using a 
> 'writeln()' command.  There is no 'writeln()' command in SQLite.

THE PROBLEM OCCURS AT SQLITE LEVEL! The caller does not matter.

Behaviors diverge from sqlite3(071300).c: line 19911 (var "realvalue") very 
precisely.


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

Re: [sqlite] (shell dependent) library rounding error

[Jay A. Kreibich]

On Fri, Jun 15, 2012 at 06:32:51AM +0200, Etienne scratched on the wall:

> The problem is: the "real" rounding error depends here on the shell
> (SQLite or JSDB) calling the SQLite library.

  This shouldn't be a surprise.  The core SQLite API is expecting a
  64-bit binary number.  That's what goes in and what comes out.  I
  have great confidence that the bits you put in (assuming they're a
  legal floating point number) are going to be the bits you get back
  out.

  Otherwise, you're not putting a number in, you're putting a string
  representation of a number in.  In your case, that's also what you're
  asking for back out.  If you actually store the string as a sequence
  of characters and nothing else, again: what you put in is going to be
  what you get back out.

  But when you start to covert things between strings and binary
  numbers, especially complex floating point representations, it
  shouldn't be a big surprise that different code out there does
  it differently.


  As for output, I'm pretty sure SQLite, when asked to do a floating
  point to string conversion, uses the standard "%f" printf conversion.
  By default, it is limited to 6 decimal places.  If it only sees
  zeros, it will roll the number back up.  The number isn't "wrong", it
  is just being displayed in a shortened notation.

   -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] (shell dependent) library rounding error

[Simon Slavin]

On 15 Jun 2012, at 5:32am, Etienne  wrote:

> That said... the rounding error ITSELF is not the point.
> 
> The problem is: the "real" rounding error depends here on the shell (SQLite 
> or JSDB) calling the SQLite library.

Yes.  Different languages use different ways to hide the fact that they can't 
hide the 0.1.  There's nothing you can do about this.

> If I submit twice the same input (e.g. 0.1) to the same "black box" (e.g. 
> SQLite library - same compiler options etc.), I am just waiting for the same 
> (fake) result. IT was determinist... so far.

Here is your original text:

 R:\>sqlite NUL "select 0.1;"
 0.1
 
 
 JSDB (www.jsdb.org) output:
 
 js>var db = new SQLite();
 js>db.exec("select 0.1", function(r){writeln(r)});
 0.1=0.11
 true

You are not doing the same thing both times.  One time you are writing the 
result using SQLite.  The other time you are writing the result using a 
'writeln()' command.  There is no 'writeln()' command in SQLite.

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

[sqlite] (shell dependent) library rounding error

[Etienne]

Hi Simon,

Thanks for your answer.

That said... the rounding error ITSELF is not the point.

The problem is: the "real" rounding error depends here on the shell (SQLite or 
JSDB) calling the SQLite library.

If I submit twice the same input (e.g. 0.1) to the same "black box" (e.g. 
SQLite library - same compiler options etc.), I am just waiting for the same 
(fake) result. IT was determinist... so far.

Why is SQLite able to "hide" the error in one case and not in the other case? 
Because they are slightly DIFFERENT! How is it possible? 

Regards,
Etienne



- Original message -
From: Simon Slavin <slav...@bigfraud.org>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Subject: Re: [sqlite] (shell dependent) library rounding error
Date: Thu, 14 Jun 2012 22:13:18 +0100


On 14 Jun 2012, at 9:30pm, Etienne <ejlist-sql...@yahoo.fr> wrote:

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

There's no way to store the fraction 0.1 as a binary value.  Read this:

<http://revjim.net/2003/05/07/funny-math/>

or go read any beginner's book on computer science.  SQLite, just like every 
other programming language, goes some way to fake its results but you can 
usually find some very simple operation which will make it expose the fact that 
it's all faked.  This isn't a bug in SQLite, it's a problem with pretending you 
can do the same things with binary and decimal numbers.

If you know you're going to need to store fractional values exactly, multiply 
all your numbers up until you can store integers.  For instance, if you need to 
store integer amounts of money precisely, multiply all the values by 100, store 
cents instead of Euros, and store them as INTEGER rather than REAL/FLOAT.

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] (shell dependent) library rounding error

[Simon Slavin]

On 14 Jun 2012, at 9:30pm, Etienne  wrote:

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

There's no way to store the fraction 0.1 as a binary value.  Read this:



or go read any beginner's book on computer science.  SQLite, just like every 
other programming language, goes some way to fake its results but you can 
usually find some very simple operation which will make it expose the fact that 
it's all faked.  This isn't a bug in SQLite, it's a problem with pretending you 
can do the same things with binary and decimal numbers.

If you know you're going to need to store fractional values exactly, multiply 
all your numbers up until you can store integers.  For instance, if you need to 
store integer amounts of money precisely, multiply all the values by 100, store 
cents instead of Euros, and store them as INTEGER rather than REAL/FLOAT.

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

[sqlite] (shell dependent) library rounding error

[Etienne]

Hi all,

This is not really a SQLite issue, but the only solution I found out so far 
consists in modifying SQLite sources.

Maybe someone here is able to clarify the following mystery... 

---
SQLite shell output:

R:\>sqlite NUL "select 0.1;"
0.1


JSDB (www.jsdb.org) output:

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

[Same rounding error with a couple of other "short" reals]

AFAIK, JSDB does use the SQLite library properly.

Technically, I can not figure out why sqlite3:sqlite3VXPrintf() behaves 
differently if embedded in the SQLite shell or in JSDB.

In this particular case, "realvalue" values start to diverge slightly from the 
"10.0" loop:

---
if( realvalue>0.0 ){
  while( realvalue>=1e32 && exp<=350 ){ realvalue *= 1e-32; exp+=32; }
  while( realvalue>=1e8 && exp<=350 ){ realvalue *= 1e-8; exp+=8; }
  
  while( realvalue>=10.0 && exp<=350 ){ realvalue *= 0.1; exp++; }  <--
  
  while( realvalue<1e-8 ){ realvalue *= 1e8; exp-=8; }
  while( realvalue<1.0 ){ realvalue *= 10.0; exp--; }
---

Adding intermediate exp power(s) of 2 (between 8 and 1) in the lines above does 
fix the problem, e.g.:

(...)
if( realvalue>=1e4 && exp<=350 ){ realvalue *= 1e-4; exp+=4; }
(...)

However I still do not know why the same code/input/compiler options etc. lead 
to slightly different results.

Any hint welcome!

Thanks in advance.

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