Re: [sqlite] BUG : round(x,y) not consistent

[Luuk]

On 30-04-2011 20:48, Simon Slavin wrote:
> 
> On 29 Apr 2011, at 9:09pm, Jean-Marie CUAZ wrote:
> 
>> Win XP + SQLite 3.7.4
>>
>> SELECT   round(8.875,2)->  8.88
>> SELECT round(16.875,2)   -> 16.87
>> SELECT round(32.875,2)   -> 32.88
>> SELECT round(64.875,2)  ->  64.87
> 
> The documentation for SQLite does not mention what rounding algorithm is used:
> 
> http://www.sqlite.org/lang_corefunc.html
> 
> I suspect it's using banker's rounding.  That's a reasonably good rounding 
> method intended to round without disturbing the mean.  If you think it 
> shouldn't, what else, and why ?
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

If thats true, than the description here is wrong:
http://en.wikipedia.org/wiki/Rounding#Round_half_to_even
Round half to even

A tie-breaking rule that is even less biased is round half to even, namely

* If the fraction of y is 0.5, then q is the even integer nearest to y.

Thus, for example, +23.5 becomes +24, +22.5 becomes +22, −22.5 becomes
−22, and −23.5 becomes −24.

This method also treats positive and negative values symmetrically, and
therefore is free of overall bias if the original numbers are positive
or negative with equal probability. In addition, for most reasonable
distributions of y values, the expected (average) value of the rounded
numbers is essentially the same as that of the original numbers, even if
the latter are all positive (or all negative). However, this rule will
still introduce a positive bias for even numbers (including zero), and a
negative bias for the odd ones.

This variant of the round-to-nearest method is also called unbiased
rounding (ambiguously, and a bit abusively), convergent rounding,
statistician's rounding, Dutch rounding, Gaussian rounding, or *bankers'
rounding*. This is widely used in bookkeeping.

This is the default rounding mode used in IEEE 754 computing functions
and operators

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

Re: [sqlite] BUG : round(x,y) not consistent

[Simon Slavin]

On 29 Apr 2011, at 9:09pm, Jean-Marie CUAZ wrote:

> Win XP + SQLite 3.7.4
> 
> SELECT   round(8.875,2)->  8.88
> SELECT round(16.875,2)   -> 16.87
> SELECT round(32.875,2)   -> 32.88
> SELECT round(64.875,2)  ->  64.87

The documentation for SQLite does not mention what rounding algorithm is used:

http://www.sqlite.org/lang_corefunc.html

I suspect it's using banker's rounding.  That's a reasonably good rounding 
method intended to round without disturbing the mean.  If you think it 
shouldn't, what else, and why ?

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

Re: [sqlite] BUG : round(x,y) not consistent

[Pete Attkins]

On 2011-04-30, at 13:36, Jos Groot Lipman wrote:

> The rounding problem is documented in the FAQ:
> http://www.sqlite.org/faq.html#q16

That reference does not address the issue of the four rounding modes  
of the Intel FPUs. SQLite being a library rather than a process, does  
SQLite control the FPU rounding mode or does it rely on the calling  
thread's FPU rounding mode?

This is very important to anyone intending to use the REAL datatype  
for a financial transaction database. They would be well advised to  
get local government approval for the system design because not all  
countries may yet have relaxed their taxation laws that previously  
insisted on a CURRENCY datatype which uses "round half to even" aka  
"Bankers' Rounding".



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

Re: [sqlite] BUG : round(x,y) not consistent

[Luuk]

On 30-04-2011 14:04, Luuk wrote:
> On 29-04-2011 22:09, Jean-Marie CUAZ wrote:
>> Win XP + SQLite 3.7.4
>>
>> SELECT   round(8.875,2)->  8.88
>> SELECT round(16.875,2)   -> 16.87
>> SELECT round(32.875,2)   -> 32.88
>> SELECT round(64.875,2)  ->  64.87
>>
>> I'm not sure SQLite is the actual culprit (and I'm aware the subject is 
>> complicated) but it's annoying.
>>
>> Jean-MarieSQLite version 3.7.2
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> .mode line
> sqlite> select round(2.875,2),round(4.875,2),round(8.875,2),
> round(16.875,2), round(32.875,2), round(64.875,2),round(128.875,2);
>   round(2.875,2) = 2.87
>   round(4.875,2) = 4.87
>   round(8.875,2) = 8.88
>  round(16.875,2) = 16.87
>  round(32.875,2) = 32.88
>  round(64.875,2) = 64.87
> round(128.875,2) = 128.88
> sqlite> select round(2.8755,3),round(4.8755,3),round(8.8755,3),
> round(16.8755,3), round(32.8755,3), round(64.8755,3),round(128.8755,3);
>   round(2.8755,3) = 2.876
>   round(4.8755,3) = 4.875
>   round(8.8755,3) = 8.876
>  round(16.8755,3) = 16.875
>  round(32.8755,3) = 32.876
>  round(64.8755,3) = 64.876
> round(128.8755,3) = 128.875
> sqlite>
>
> sqlite 3.7.2 in Windows7 and same result on openSUSE ;)
sqlite> select
round(1000*2.875,2),round(1000*4.875,2),round(1000*8.875,2),
round(1000*16.875,2), round(1000*32.875,2),
round(1000*64.875,2),round(1000*128.875,
2);
  round(1000*2.875,2) = 2875.0
  round(1000*4.875,2) = 4875.0
  round(1000*8.875,2) = 8875.0
 round(1000*16.875,2) = 16875.0
 round(1000*32.875,2) = 32875.0
 round(1000*64.875,2) = 64875.0
round(1000*128.875,2) = 128875.0
sqlite> select
round(1000*2.875,2)/1000,round(1000*4.875,2)/1000,round(1000*8.875,2)/1000,
round(1000*16.875,2)/1000, round(1000*32.875,2)/1000, round(1000*64.8
75,2)/1000,round(1000*128.875,2)/1000;
  round(1000*2.875,2)/1000 = 2.875
  round(1000*4.875,2)/1000 = 4.875
  round(1000*8.875,2)/1000 = 8.875
 round(1000*16.875,2)/1000 = 16.875
 round(1000*32.875,2)/1000 = 32.875
 round(1000*64.875,2)/1000 = 64.875
round(1000*128.875,2)/1000 = 128.875
sqlite>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BUG : round(x,y) not consistent

[Jos Groot Lipman]

The rounding problem is documented in the FAQ:
http://www.sqlite.org/faq.html#q16 

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

Re: [sqlite] BUG : round(x,y) not consistent

[Luuk]

On 29-04-2011 22:09, Jean-Marie CUAZ wrote:
> Win XP + SQLite 3.7.4
>
> SELECT   round(8.875,2)->  8.88
> SELECT round(16.875,2)   -> 16.87
> SELECT round(32.875,2)   -> 32.88
> SELECT round(64.875,2)  ->  64.87
>
> I'm not sure SQLite is the actual culprit (and I'm aware the subject is 
> complicated) but it's annoying.
>
> Jean-MarieSQLite version 3.7.2
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .mode line
sqlite> select round(2.875,2),round(4.875,2),round(8.875,2),
round(16.875,2), round(32.875,2), round(64.875,2),round(128.875,2);
  round(2.875,2) = 2.87
  round(4.875,2) = 4.87
  round(8.875,2) = 8.88
 round(16.875,2) = 16.87
 round(32.875,2) = 32.88
 round(64.875,2) = 64.87
round(128.875,2) = 128.88
sqlite> select round(2.8755,3),round(4.8755,3),round(8.8755,3),
round(16.8755,3), round(32.8755,3), round(64.8755,3),round(128.8755,3);
  round(2.8755,3) = 2.876
  round(4.8755,3) = 4.875
  round(8.8755,3) = 8.876
 round(16.8755,3) = 16.875
 round(32.8755,3) = 32.876
 round(64.8755,3) = 64.876
round(128.8755,3) = 128.875
sqlite>

sqlite 3.7.2 in Windows7 and same result on openSUSE ;)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] BUG : round(x,y) not consistent

[Jean-Marie CUAZ]

Win XP + SQLite 3.7.4

SELECT   round(8.875,2)->  8.88
SELECT round(16.875,2)   -> 16.87
SELECT round(32.875,2)   -> 32.88
SELECT round(64.875,2)  ->  64.87

I'm not sure SQLite is the actual culprit (and I'm aware the subject is 
complicated) but it's annoying.

Jean-Marie

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