Re: [sqlite] round function inconsistent

[Jose Isaias Cabrera]

Lifepillar, on Friday, May 24, 2019 02:48 PM, wrote...
>On 24 May 2019, at 19:53, Warren Young  wrote:
>>
>>https://chiselapp.com/user/lifepillar/repository/sqlite3decimal/index
>
>sqlite3decimal’s author here: be warned that the extension is still in
> development. In particular, it’s far from being optimized and the syntax
> is cumbersome. But the computations should give correct results wrt to
> decimal arithmetic (and you may choose the rounding algorithm)! I am
> currently working on different projects that are consuming all my time,
> but I plan to bring the extension to production level. Eventually :)
> Knowing that there is a need out there for this type of calculations is a
> strongly motivating factor.

I would be very interested when it has been completed.  However, I will 
download it and test it now. Maybe it will save me lots of programming outside 
sqlite.  :-)  Thanks.

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

Re: [sqlite] round function inconsistent

[Jose Isaias Cabrera]

Rowan Worth, on Monday, May 27, 2019 11:07 PM, wrote...​
>On Mon, 27 May 2019 at 23:36, Jose Isaias Cabrera ​
>wrote:​
>​
>> Ok, I think it happens even before the casting.  This should be,​
>> 3.2598, and yet, it's 3.26.​
>>​
>> sqlite> SELECT 0.005 + 3.2548;​
>> 3.26​
>>​
>​
>Note that no arithmetic is required to see these symptoms:​
​
Yes.  It has definitely been a learning experience.  I actually thought that 
using floating point was more accurate in every way possible. :-) I am also 
understood why some of the dumps that I get from a reporting system have 
numbers such as 3.2548, etc., when someone actually entered a 
different and shorter number all together. I am the least in knowledge of this 
group, and I appreciate the lovely responses from everyone. But, I have been 
wondering in the past few days: why haven't more people complaint about it? 
Math is math and decimal number is what most business run?  Yes, there are many 
other floating point application for engineers and such, but the amount of work 
in daily business activity relies on decimal numbering system.  Anyway, I 
understand it now.​
​
>This is all very subtle which is why some languages/software offer actual​
>decimal arithmetic. sqlite does not, but there's also nothing to stop you​
>from storing eg. strings in the DB and converting to/from decimal​
>representations in your application.​
​
Thanks Rowan for the reply. Yes, I will have to manage it this way from now on. 
 Back to creating a few arithmetic functions to manage these types of decimal 
processing.  It is sad, though, because it would be easier to be able to do 
this right from SQLite, instead of outside.  Now, I will have to bring the 
records out, do the sum of the records, round, etc., etc.  Thanks.​
​
josé
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] round function inconsistent

[Rowan Worth]

On Mon, 27 May 2019 at 23:36, Jose Isaias Cabrera 
wrote:

> Ok, I think it happens even before the casting.  This should be,
> 3.2598, and yet, it's 3.26.
>
> sqlite> SELECT 0.005 + 3.2548;
> 3.26
>

Note that no arithmetic is required to see these symptoms:

sqlite> SELECT 3.2598;
3.26

But also note that when floating point numbers are displayed, they are
_almost always_ simplified for ease of reading. Try this C code:

#include 

int
main(int argc, char** argv)
{
double d = 3.2598;
printf("%f\n", d);
return 0;
}

It prints 3.26. There is also a kind of rounding that happens at the
display level, which can make it tricky to appreciate what is going on
behind the scenes. You can adjust that behaviour in C; eg. changing %f to
%.72f gives you "more precision":
3.2597868371792719699442386627197265625000

Also note that in practice 3.2598 _is_ 3.26. You can check this
in sqlite:

sqlite> SELECT 3.2598 = 3.26;
1

64-bit floating point just doesn't have the accuracy to represent the
difference. Here's what changing the lowest bit looks like around 3.26:

0x400a147ae147ae13 =~ 3.2593
0x400a147ae147ae14 =~ 3.26
0x400a147ae147ae15 =~ 3.2602

One way to think of floating point is that each 64-bit value represents a
"bin" of closely related numbers - the decimal value I've written on the
right hand side here represents the (approximate) bin centre.
3.2598 is closer to 3.26 than 3.2593 so it gets
lumped into 0x400a147ae147ae14

This is all very subtle which is why some languages/software offer actual
decimal arithmetic. sqlite does not, but there's also nothing to stop you
from storing eg. strings in the DB and converting to/from decimal
representations in your application.
-Rowan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] round function inconsistent

[Jose Isaias Cabrera]

James K. Lowden, on Sunday, May 26, 2019 04:51 PM, wrote...​
>On Fri, 24 May 2019 13:10:49 +​
>Jose Isaias Cabrera  wrote:​
>​
>> >Consider these two queries:​
>> >​
>> >   SELECT round(3.255,2);​
>> >   SELECT round(3.2548,2);​
>> >​
>> >Do you expect them to give different answers?​
>> ​
>> 3.26​
>> 3.25​
>​
>sqlite> SELECT cast(100 * (0.005 + 3.2548) as​
>   ...> integer)/100.0;​
>3.26​
​
Ok, I think it happens even before the casting.  This should be, 
3.2598, and yet, it's 3.26.​
​
11:23:07.55>sqlite3​
SQLite version 3.28.0 2019-04-16 19:49:53​
Enter ".help" for usage hints.​
Connected to a transient in-memory database.​
Use ".open FILENAME" to reopen on a persistent database.​
sqlite> SELECT 0.005 + 3.2548;​
3.26​
​
I even tried casting it as real,​
​
sqlite> SELECT cast(0.005 + 3.2548 as real);​
3.26​
sqlite> SELECT cast(3.2548 + 0.005 as real);​
3.26​
sqlite> SELECT cast((3.2548 + 0.005) as real);​
3.26​
sqlite>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] round function inconsistent

[James K. Lowden]

On Fri, 24 May 2019 13:10:49 +
Jose Isaias Cabrera  wrote:

> >Consider these two queries:
> >
> >   SELECT round(3.255,2);
> >   SELECT round(3.2548,2);
> >
> >Do you expect them to give different answers?
> 
> 3.26
> 3.25

sqlite> SELECT cast(100 * (0.005 + 3.2548) as
   ...> integer)/100.0;
3.26

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

Re: [sqlite] round function inconsistent

[Thomas Kurz]

>  INSERT INTO t1(a,b) VALUES(2,3.254893418589635);

But this is a different scenario. The value is already stored in the database 
as 3.255.


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

Re: [sqlite] round function inconsistent

[Tim Streater]

On 24 May 2019, at 13:35, Jose Isaias Cabrera  wrote:

> Alessandro Merolli, on Friday, May 24, 2019 07:39 AM, wrote...
>
>> Great! Now use SQLite API and add a new user defined function for
>> your used case.
>> I suppose that SQlite should always follow a well-defined pattern: in
>> this case as Mr. Hipp said, it is IEEE754.
>
> If IEEE754 can't figure out how to round 3.555 to 2 decimals, I don't know if
> I'd trust it. ;-) thanks.

IEEE754 knows nothing about 3.555. If you input that, it gets changed to a 
binary floating point number with a different actual value. Which is then 
rounded. No point in complaining about that.


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

Re: [sqlite] round function inconsistent

[Keith Medcalf]

sqlite> select roundhe(3.255, 2);
3.26
sqlite> select printf('%!.18f', 3.255);
3.254893
sqlite> select printf('%!.18f', roundhe(3.255, 2));
3.259787
sqlite> select printf('%!.18f', pow(10.0, 2));
100.0
sqlite> select printf('%!.18f', pow(10.0, 2) * 3.255);
325.5

The good:  proper rounding (half-even) within the limits of the floating-point 
math processor/library of your computer.
The bad:   drags in your platform math library and requires that it work 
according to specifications.
Requires:  IEEE-754 representation is "round nearest" (which it usually is).

and never round intermediates.  Using intermediates to which rounding has been 
applied invalidates the precision guarantees (such as they are) of IEEE-754.

SQLITE_PRIVATE void _heroundingFunc(sqlite3_context *context, int argc, 
sqlite3_value **argv)
{
int p = 0;
double x, scale, xval, ipart, fpart, sgn;

if ((argc == 0) || (argc > 2))
return;
if (sqlite3_value_type(argv[0]) == SQLITE_NULL)
return;
x = sqlite3_value_double(argv[0]);
if (argc == 2)
{
if (sqlite3_value_type(argv[1]) == SQLITE_NULL)
return;
p = sqlite3_value_int(argv[1]);
p = p > 15 ? 15 : (p < 0 ? 0 : p);
}
scale = pow(10.0, p);
sgn = 1.0;
if (x < 0)
sgn = -1.0;
xval = sgn * x * scale;
if (log10(xval) > 16.0)
{
sqlite3_result_double(context, x);
return;
}
fpart = modf(xval, &ipart);
if ((fpart > 0.5) || ((fpart == 0.5) && (fmod(ipart, 2.0) == 1.0)))
ipart += 1.0;
xval = sgn * ipart / scale;
sqlite3_result_double(context, xval);
}

Note that you can implement "grade school" rounding (round half away from zero) 
by changing:

if ((fpart > 0.5) || ((fpart == 0.5) && (fmod(ipart, 2.0) == 1.0)))
ipart += 1.0;

to

if (fpart => 0.5)
ipart += 1.0;

This is the part that implements the rounding since you know the sign (sgn), 
the integer part (ipart), and the remainder (fpart) and unless you change the 
ipart then the result is merely the truncation of the value (towards zero).

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.




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

Re: [sqlite] round function inconsistent

[Lifepillar]

On 24 May 2019, at 19:53, Warren Young  wrote:
> 
> On May 24, 2019, at 7:10 AM, Jose Isaias Cabrera  wrote:
>> 
>> Dr. Richard Hipp, on Friday, May 24, 2019 08:44 AM, wrote...
 Dr. Hipp, how many more scenarios, where round gives the wrong answer,
 exist?  Thanks.
>>> 
>>> Consider these two queries:
>>> 
>>> SELECT round(3.255,2);
>>> SELECT round(3.2548,2);
>>> 
>>> Do you expect them to give different answers?
>> 
>> 3.26
>> 3.25
> 
> Go read this: https://www.floating-point-gui.de/
> 
> Then install this extension: 
> 
>https://chiselapp.com/user/lifepillar/repository/sqlite3decimal/index

sqlite3decimal’s author here: be warned that the extension is still in 
development. In particular, it’s far from being optimized and the syntax is 
cumbersome. But the computations should give correct results wrt to decimal 
arithmetic (and you may choose the rounding algorithm)! I am currently working 
on different projects that are consuming all my time, but I plan to bring the 
extension to production level. Eventually :) Knowing that there is a need out 
there for this type of calculations is a strongly motivating factor. 

> Having done both, you’ll know why SQLite does what it does, and will have a 
> solution for the problem where it matters.
> 
> And it doesn’t always matter!  Anything dealing in real-world measurements, 
> for example, generally doesn’t need more than 9 decimal digits of precision, 
> depending on the measuring instrument, which is more than covered by an 
> IEEE-754 double.  Measurement instruments are not absolutely precise: they 
> have inherent inaccuracies, and electronic instruments have drift and other 
> characteristics that can prevent them from giving the same answer given the 
> same task.
> 
> Since the measurement itself cannot be more precise than an IEEE-754 double, 
> any “error” in an answer computed from it is down in the noise floor of the 
> measurement.  That is to say, two measurements taken back-to-back might give 
> the same or greater error.
> 
> This is no accident.  IEEE-754 was designed by people working in the physical 
> sciences, and they knew all of this when they designed it.  It is what it was 
> designed *for*.
> 
> What IEEE-754 was *not* designed for was discrete math, such as monetary 
> matters on scales smaller than a national economy.

That is correct as far as IEEE 754-1985 is concerned. But the latest revision 
(IEEE 754-2008) standardises floating-point decimal arithmetic, too. So, “IEEE 
754” does not imply binary floating-point any longer. It is better to be 
explicit whether you are talking about “binary” or “decimal” IEEE 754, nowadays.

Re the different DBMSs’ specifications, PostgreSQL’s does the right thing, IMO. 
Unfortunately, SQLite does not have exact numeric types, so the choice is 
either to implement round() on binary floating-point numbers or not to 
implement round() at all.

Life.


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

Re: [sqlite] round function inconsistent

[sky5walk]

Yes, I fear users are attempting simplistic banking apps with floating
point rounds. :(
May explain where millions of my pennies went in my last android stock sale!

On Fri, May 24, 2019 at 1:55 PM Warren Young  wrote:

> On May 24, 2019, at 7:10 AM, Jose Isaias Cabrera 
> wrote:
> >
> > Dr. Richard Hipp, on Friday, May 24, 2019 08:44 AM, wrote...
> >>> Dr. Hipp, how many more scenarios, where round gives the wrong answer,
> >>> exist?  Thanks.
> >>
> >> Consider these two queries:
> >>
> >>  SELECT round(3.255,2);
> >>  SELECT round(3.2548,2);
> >>
> >> Do you expect them to give different answers?
> >
> > 3.26
> > 3.25
>
> Go read this: https://www.floating-point-gui.de/
>
> Then install this extension:
>
> https://chiselapp.com/user/lifepillar/repository/sqlite3decimal/index
>
> Having done both, you’ll know why SQLite does what it does, and will have
> a solution for the problem where it matters.
>
> And it doesn’t always matter!  Anything dealing in real-world
> measurements, for example, generally doesn’t need more than 9 decimal
> digits of precision, depending on the measuring instrument, which is more
> than covered by an IEEE-754 double.  Measurement instruments are not
> absolutely precise: they have inherent inaccuracies, and electronic
> instruments have drift and other characteristics that can prevent them from
> giving the same answer given the same task.
>
> Since the measurement itself cannot be more precise than an IEEE-754
> double, any “error” in an answer computed from it is down in the noise
> floor of the measurement.  That is to say, two measurements taken
> back-to-back might give the same or greater error.
>
> This is no accident.  IEEE-754 was designed by people working in the
> physical sciences, and they knew all of this when they designed it.  It is
> what it was designed *for*.
>
> What IEEE-754 was *not* designed for was discrete math, such as monetary
> matters on scales smaller than a national economy.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] round function inconsistent

[Warren Young]

On May 24, 2019, at 7:10 AM, Jose Isaias Cabrera  wrote:
> 
> Dr. Richard Hipp, on Friday, May 24, 2019 08:44 AM, wrote...
>>> Dr. Hipp, how many more scenarios, where round gives the wrong answer,
>>> exist?  Thanks.
>> 
>> Consider these two queries:
>> 
>>  SELECT round(3.255,2);
>>  SELECT round(3.2548,2);
>> 
>> Do you expect them to give different answers?
> 
> 3.26
> 3.25

Go read this: https://www.floating-point-gui.de/

Then install this extension: 

https://chiselapp.com/user/lifepillar/repository/sqlite3decimal/index

Having done both, you’ll know why SQLite does what it does, and will have a 
solution for the problem where it matters.

And it doesn’t always matter!  Anything dealing in real-world measurements, for 
example, generally doesn’t need more than 9 decimal digits of precision, 
depending on the measuring instrument, which is more than covered by an 
IEEE-754 double.  Measurement instruments are not absolutely precise: they have 
inherent inaccuracies, and electronic instruments have drift and other 
characteristics that can prevent them from giving the same answer given the 
same task.

Since the measurement itself cannot be more precise than an IEEE-754 double, 
any “error” in an answer computed from it is down in the noise floor of the 
measurement.  That is to say, two measurements taken back-to-back might give 
the same or greater error.

This is no accident.  IEEE-754 was designed by people working in the physical 
sciences, and they knew all of this when they designed it.  It is what it was 
designed *for*.

What IEEE-754 was *not* designed for was discrete math, such as monetary 
matters on scales smaller than a national economy.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] round function inconsistent

[Thomas Kurz]

Sorry, I was too fast with sending.

With the three values mentioned before:

a) 3.255
b) 3.254999
c) 3.254893418589635

Both SQLite and MySQL (however, I used MariaDB) return these values on a simple 
SELECT b:

a) 3.255
b) 3.254999
c) 3.255


And ROUND(b,2) returns:

a) 3.26 for MariaDB, 3.25 for SQLite
b) 3.25 for both
c) 3.26 for MariaDB, 3.25 for SQLite

For MariaDB, this is consistent, for SQlite, it ain't.


- Original Message - 
From: Richard Hipp 
To: SQLite mailing list 
Sent: Friday, May 24, 2019, 19:06:00
Subject: [sqlite] round function inconsistent

On 5/24/19, Jose Isaias Cabrera  wrote:


> FWIW, I went to sqlfiddle [1] and these are the answers for this SQL
> command:

> SELECT round(3.255,2), round(3.2548,2);

I also went to sqlfiddle and did a slightly more realistic scenario:

  CREATE TABLE t1(a INT, b DOUBLE PRECISION);
  INSERT INTO t1(a,b) VALUES(1,3.255);
  INSERT INTO t1(a,b) VALUES(2,3.254893418589635);
  SELECT a, b FROM t1;

In other words, I made the value actually pass through the database.

For MySQL I got:

1 3.26
2 3.26

For SQL Server I got:

1 3.25
2 3.25

The query does not work on PostgreSQL, because PG wisely prohibits
using the two-argument around() function on binary floating-point
values, perhaps to prevent discussions such as this one.  In order to
get this to work on PG I had to modify the query as follows:

   SELECT a, round(CAST(b AS NUMERIC),2) from t1

And the result is then:

1 3.26
2 3.26

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] round function inconsistent

[Jose Isaias Cabrera]

Dr. Richard Hipp, on Friday, May 24, 2019 01:06 PM, wrote...
>
>I also went to sqlfiddle and did a slightly more realistic scenario:
>
>  CREATE TABLE t1(a INT, b DOUBLE PRECISION);
>  INSERT INTO t1(a,b) VALUES(1,3.255);
>  INSERT INTO t1(a,b) VALUES(2,3.254893418589635);
>  SELECT a, b FROM t1;
>
>In other words, I made the value actually pass through the database.
Good call...

>For MySQL I got:
>
>1 3.26
>2 3.26
>
>For SQL Server I got:
>
>1 3.25
>2 3.25
>
>The query does not work on PostgreSQL, because PG wisely prohibits
>using the two-argument around() function on binary floating-point
>values, perhaps to prevent discussions such as this one.  In order to
>get this to work on PG I had to modify the query as follows:
>
>   SELECT a, round(CAST(b AS NUMERIC),2) from t1
>
>And the result is then:
>
>1 3.26
>2 3.26

Touché!

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

Re: [sqlite] round function inconsistent

[Richard Hipp]

On 5/24/19, Jose Isaias Cabrera  wrote:

>
> FWIW, I went to sqlfiddle [1] and these are the answers for this SQL
> command:
>
> SELECT round(3.255,2), round(3.2548,2);

I also went to sqlfiddle and did a slightly more realistic scenario:

  CREATE TABLE t1(a INT, b DOUBLE PRECISION);
  INSERT INTO t1(a,b) VALUES(1,3.255);
  INSERT INTO t1(a,b) VALUES(2,3.254893418589635);
  SELECT a, b FROM t1;

In other words, I made the value actually pass through the database.

For MySQL I got:

1 3.26
2 3.26

For SQL Server I got:

1 3.25
2 3.25

The query does not work on PostgreSQL, because PG wisely prohibits
using the two-argument around() function on binary floating-point
values, perhaps to prevent discussions such as this one.  In order to
get this to work on PG I had to modify the query as follows:

   SELECT a, round(CAST(b AS NUMERIC),2) from t1

And the result is then:

1 3.26
2 3.26

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] round function inconsistent

[Jose Isaias Cabrera]

Thomas Kurz, on Friday, May 24, 2019 09:13 AM, wrote...
>Sorry, but even Excel (which usually isn't very good at decimal math) gives 
>correct results:
>
>ROUND(3.255;2) --> 3.26
>ROUND(3.254999;2) --> 3.25

FWIW, I went to sqlfiddle [1] and these are the answers for this SQL command:

SELECT round(3.255,2), round(3.2548,2);

for MySQL 5.6, answer...
round(3.255,2) | round(3.2548,2)
3.26 | 3.25

PostgreSQL 9.6, answer...
round | round
3.26 | 3.25

SQLite (SQL.js), answer...
round(3.255,2) | round(3.2548,2)
3.25 | 3.25

MS SQL Server 2017, answer...
 |
3.26 | 3.25

I could not get Oracle to work. But, it looks like MySQL, PostgreSQL and MSSQL 
provide correct output. By the way, the SQLite version on this site is old 
(3.15.1). Thanks.

josé

[1] http://sqlfiddle.com/#!9/a0753b/3
[http://sqlfiddle.com/images/fiddle_transparent.png]
SQL Fiddle | A tool for easy online testing and sharing of database problems 
and their solutions.
Application for testing and sharing SQL queries.
sqlfiddle.com

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

Re: [sqlite] round function inconsistent

[Radovan Antloga]

In Delphi I have 2 functions that works (I override default sqlite round):

const
  ExtEps = 1.0842021725E-19;
  DblEps = 2.2204460493E-16;
  KnownErrorLimit = 1.234375;
  SafetyFactor = 2;
  MaxRelErrDbl = DblEps * KnownErrorLimit * SafetyFactor;
  MaxRelErrExt = ExtEps * KnownErrorLimit * SafetyFactor;

function RoundExt(const AValue: Extended; const ADigit: Integer = -2): 
Extended;

var
  E: Extended;
begin
  E := IntPower(10, -ADigit);
  Result := Round(AValue * (1 + MaxRelErrExt) * E) / E;
end;

function RoundDbl(const AValue: Double; const ADigit: Integer = -2): Double;
var
  E: Double;
begin
  E := IntPower(10, -ADigit);
  Result := Round(AValue * (1 + MaxRelErrDbl) * E) / E;
end;

You could implement it in sqlite.

Regards Radovan



On 24.05.2019 13:13, Richard Hipp wrote:

On 5/24/19, Hajo Bruns  wrote:

Hi,
the round function seems to round inconsistently:

ivesselect round(5.485,2), round (3.555,2),round (3.255,2)
gives
5,49  3,56  3,25

Last result should be 3.26

3.255 cannot be exactly represented as an IEEE754 double-precision
binary floating point number.  So the system has to use an
approximation.  The closest approximation is
3.25489341858963598497211933135986328125 and that value
rounds to 3.25.



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

Re: [sqlite] round function inconsistent

[Ling, Andy]

> I do not know what the underlying representation for floating point
> numbers is in Excel, but as your experiment shows, it is probably not
> IEEE754 double-precision binary.
> 

Well according to this is does...

https://support.microsoft.com/en-us/help/78113/floating-point-arithmetic-may-give-inaccurate-results-in-excel

But that seems to be lying. If I enter the two number into Excel, they behave 
differently.
So something in Excel "knows"

There are some exceptions listed in that document, but they don't seem to 
explain
the difference for these two numbers.



**
DISCLAIMER:
Privileged and/or Confidential information may be contained in this message. If 
you are not the addressee of this message, you may not copy, use or deliver 
this message to anyone. In such event, you should destroy the message and 
kindly notify the sender by reply e-mail. It is understood that opinions or 
conclusions that do not relate to the official business of the company are 
neither given nor endorsed by the company. Thank You.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] round function inconsistent

[Chris Locke]

>  Yours is clearly incorrect

lol.  "Your software gives a different result to the one I expect,
therefore its wrong."

You are aware that your first example (3.255) probably isn't being stored
internally as a single.
Just because computers work outside your understanding doesn't make them
'incorrect'.

On Fri, May 24, 2019 at 2:15 PM Thomas Kurz  wrote:

> Sorry, but even Excel (which usually isn't very good at decimal math)
> gives correct results:
>
> ROUND(3.255;2) --> 3.26
> ROUND(3.254999;2) --> 3.25
>
> Yours is clearly incorrect.
>
>
> - Original Message -
> From: Richard Hipp 
> To: SQLite mailing list 
> Sent: Friday, May 24, 2019, 14:44:52
> Subject: [sqlite] round function inconsistent
>
> On 5/24/19, Jose Isaias Cabrera  wrote:
>
> > Dr. Hipp, how many more scenarios, where round gives the wrong answer,
> > exist?  Thanks.
>
>
> Consider these two queries:
>
>SELECT round(3.255,2);
>SELECT round(3.2548,2);
>
> Do you expect them to give different answers?
>
> If so, do you realize that 3.255 and 3.48 are in fact the
> exact same floating point number?  That number in (unambiguous) hex
> notation is 0x1.a0a3d70a3d70ap+1. So how is it that you would expect
> the round() function to return different answers for two cases where
> it is given bit-for-bit identical inputs?  How does it know which
> answer to give?
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] round function inconsistent

[Richard Hipp]

On 5/24/19, Thomas Kurz  wrote:
> ... decimal math 

Therein lies your misunderstanding.  SQLite does not do decimal math.
It does binary math, and in particular IEEE754 double-precision binary
math.  And in that numeric system, 3.255 and 3.2548 are
the exact same number, and hence always round the same.

I do not know what the underlying representation for floating point
numbers is in Excel, but as your experiment shows, it is probably not
IEEE754 double-precision binary.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] round function inconsistent

[Thomas Kurz]

Sorry, but even Excel (which usually isn't very good at decimal math) gives 
correct results:

ROUND(3.255;2) --> 3.26
ROUND(3.254999;2) --> 3.25

Yours is clearly incorrect.


- Original Message - 
From: Richard Hipp 
To: SQLite mailing list 
Sent: Friday, May 24, 2019, 14:44:52
Subject: [sqlite] round function inconsistent

On 5/24/19, Jose Isaias Cabrera  wrote:

> Dr. Hipp, how many more scenarios, where round gives the wrong answer,
> exist?  Thanks.


Consider these two queries:

   SELECT round(3.255,2);
   SELECT round(3.2548,2);

Do you expect them to give different answers?

If so, do you realize that 3.255 and 3.48 are in fact the
exact same floating point number?  That number in (unambiguous) hex
notation is 0x1.a0a3d70a3d70ap+1. So how is it that you would expect
the round() function to return different answers for two cases where
it is given bit-for-bit identical inputs?  How does it know which
answer to give?

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] round function inconsistent

[Jose Isaias Cabrera]

Dr. Richard Hipp, on Friday, May 24, 2019 08:44 AM, wrote...
>> Dr. Hipp, how many more scenarios, where round gives the wrong answer,
>> exist?  Thanks.
>>
>
>Consider these two queries:
>
>   SELECT round(3.255,2);
>   SELECT round(3.2548,2);
>
>Do you expect them to give different answers?

3.26
3.25

>If so, do you realize that 3.255 and 3.48 are in fact the
>exact same floating point number?  That number in (unambiguous) hex
>notation is 0x1.a0a3d70a3d70ap+1. So how is it that you would expect
>the round() function to return different answers for two cases where
>it is given bit-for-bit identical inputs?  How does it know which
>answer to give?

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

Re: [sqlite] round function inconsistent

[radovan5]

Developers have problems using default round function so it is somethink 
we need.

So I have:

 ROUTINES FOR ROUNDING IEEE-754 FLOATS TO SPECIFIED NUMBER OF DECIMAL 
FRACTIONS


  These routines round input values to fit as closely as possible to an
  output number with desired number of decimal fraction digits.

  Because, in general, numbers with decimal fractions cannot be exactly
  represented in IEEE-754 floating binary point variables, error limits
  are used to determine if the input numbers are intended to represent an
  exact decimal fraction rather than a nearby value.   Thus an error limit
  will be taken into account when deciding that a number input such as
  1.295, which internally is represented 1.29499 9 …, really should
  be considered exactly 1.295 and that 0.2 9 ... really should
  be interpreted as 0.3 when applying the rounding rules.

I just provided 2 functions. But I have many options like (copy from code):

    (Abbr: 'HalfEven'; Dscr: 'Round to nearest or to even whole number '
 '(a.k.a Bankers) '),
    (Abbr: 'HalfPos' ; Dscr: 'Round to nearest or toward positive'),
    (Abbr: 'HalfNeg' ; Dscr: 'Round to nearest or toward negative'),
    (Abbr: 'HalfDown'; Dscr: 'Round to nearest or toward zero'),
    (Abbr: 'HalfUp'  ; Dscr: 'Round to nearest or away from zero'),
    (Abbr: 'RndNeg'  ; Dscr: 'Round toward negative. (a.k.a. Floor) '),
    (Abbr: 'RndPos'  ; Dscr: 'Round toward positive. (a.k.a. Ceil ) '),
    (Abbr: 'RndDown' ; Dscr: 'Round toward zero. (a.k.a. Trunc) '),
    (Abbr: 'RndUp'   ; Dscr: 'Round away from zero.') );


Radovan

On 24.05.2019 14:34, Simon Slavin wrote:

On 24 May 2019, at 1:30pm, Jose Isaias Cabrera  wrote:


Dr. Hipp, how many more scenarios, where round gives the wrong answer, exist?  
Thanks.

As Dr. Hipp wrote, round was giving the right answer.  All you need to do is 
pass the number 3.255 as the parameter.  If you're passing the wrong number, 
don't blame SQLite for getting the wrong result.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


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

Re: [sqlite] round function inconsistent

[radovan5]

Well I do. But when I use sqlite in PHP I have default round.

Radovan

On 24.05.2019 13:39, Alessandro Merolli wrote:

Great! Now use SQLite API and add a new user defined function for your used 
case.
I suppose that SQlite should always follow a well-defined pattern: in this case 
as Mr. Hipp said, it is IEEE754.
Cheers!


On 24 May 2019, at 08:27, radovan5  wrote:

In Delphi I have 2 functions that works (I override default sqlite round):

const
   ExtEps = 1.0842021725E-19;
   DblEps = 2.2204460493E-16;
   KnownErrorLimit = 1.234375;
   SafetyFactor = 2;
   MaxRelErrDbl = DblEps * KnownErrorLimit * SafetyFactor;
   MaxRelErrExt = ExtEps * KnownErrorLimit * SafetyFactor;

function RoundExt(const AValue: Extended; const ADigit: Integer = -2): Extended;
var
   E: Extended;
begin
   E := IntPower(10, -ADigit);
   Result := Round(AValue * (1 + MaxRelErrExt) * E) / E;
end;

function RoundDbl(const AValue: Double; const ADigit: Integer = -2): Double;
var
   E: Double;
begin
   E := IntPower(10, -ADigit);
   Result := Round(AValue * (1 + MaxRelErrDbl) * E) / E;
end;

You could implement it in sqlite.

Regards Radovan


On 24.05.2019 13:13, Richard Hipp wrote:

On 5/24/19, Hajo Bruns  wrote:

Hi,
the round function seems to round inconsistently:

ivesselect round(5.485,2), round (3.555,2),round (3.255,2)
gives
5,49  3,56  3,25

Last result should be 3.26

3.255 cannot be exactly represented as an IEEE754 double-precision
binary floating point number.  So the system has to use an
approximation.  The closest approximation is
3.25489341858963598497211933135986328125 and that value
rounds to 3.25.


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

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


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

Re: [sqlite] round function inconsistent

[Richard Hipp]

On 5/24/19, Jose Isaias Cabrera  wrote:
>
> Dr. Hipp, how many more scenarios, where round gives the wrong answer,
> exist?  Thanks.
>

Consider these two queries:

   SELECT round(3.255,2);
   SELECT round(3.2548,2);

Do you expect them to give different answers?

If so, do you realize that 3.255 and 3.48 are in fact the
exact same floating point number?  That number in (unambiguous) hex
notation is 0x1.a0a3d70a3d70ap+1. So how is it that you would expect
the round() function to return different answers for two cases where
it is given bit-for-bit identical inputs?  How does it know which
answer to give?

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] round function inconsistent

[Jose Isaias Cabrera]

Simon Slavin, on Friday, May 24, 2019 08:34 AM, wrote...
>On 24 May 2019, at 1:30pm, Jose Isaias Cabrera  wrote:
>
>> Dr. Hipp, how many more scenarios, where round gives the wrong answer,
> exist?  Thanks.
>
>As Dr. Hipp wrote, round was giving the right answer.  All you need to do
> is pass the number 3.255 as the parameter.  If you're passing the wrong
> number, don't blame SQLite for getting the wrong result.

Ok, maybe I am wrong, but to round 3.255 to two decimal points, should be 3.26, 
correct?  If SQLite result is 3.25, then it's wrong.  Am I wrong on this?  
Sorry, I know a little math, but I don't get as deep as you guys do.  Please 
correct my lack of knowledge.  I am serious.  I don't understand the reasoning 
behind it.  Thanks.

josé

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

Re: [sqlite] round function inconsistent

[Jose Isaias Cabrera]

Alessandro Merolli, on Friday, May 24, 2019 07:39 AM, wrote...

> Great! Now use SQLite API and add a new user defined function for
> your used case.
> I suppose that SQlite should always follow a well-defined pattern: in
> this case as Mr. Hipp said, it is IEEE754.

If IEEE754 can't figure out how to round 3.555 to 2 decimals, I don't know if 
I'd trust it. ;-) thanks.

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

Re: [sqlite] round function inconsistent

[Simon Slavin]

On 24 May 2019, at 1:30pm, Jose Isaias Cabrera  wrote:

> Dr. Hipp, how many more scenarios, where round gives the wrong answer, exist? 
>  Thanks.

As Dr. Hipp wrote, round was giving the right answer.  All you need to do is 
pass the number 3.255 as the parameter.  If you're passing the wrong number, 
don't blame SQLite for getting the wrong result.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] round function inconsistent

[Jose Isaias Cabrera]

Dr. Richard Hipp, on Friday, May 24, 2019 07:13 AM, wrote...

>> Last result should be 3.26
>
>3.255 cannot be exactly represented as an IEEE754 double-precision
>binary floating point number.  So the system has to use an
>approximation.  The closest approximation is
>3.25489341858963598497211933135986328125 and that value
>rounds to 3.25.

I actually use lots of rounding in SQLite such as this one (please ignore the 
extra code--not enough time to clean it up),

  char[] cmd;
  cmd ~= "\nBEGIN;";
  cmd ~= "\nUPDATE LSOpenJobs SET ProjFund = " ~
"\n(" ~
"\n  SELECT " ~
"\n  ( " ~
"\nCASE " ~
"\nWHEN instr(LSOpenJobs.XtraD,'%') > 0 THEN " ~
"\n  round(sum(t2.ProjFund) * cast(LSOpenJobs.XtraD as integer)/100.0, 
2) " ~
"\nELSE " ~
"\n  LSOpenJobs.ProjFund " ~
"\nEND " ~
"\n  ) " ~
"\n  FROM LSOpenJobs t2 " ~
"\nWHERE LSOpenJobs.ProjID = t2.ProjID AND LSOpenJobs.lang = t2.lang " ~
"\nAND t2.PSubClass != 'Portal-Fee' " ~
"\nAND t2.PSubClass != 'Rush-Job' " ~
"\n) " ~
"\nWHERE ProjID = " ~ pid ~ " AND PSubClass = 'Rush-Job';";
  cmd ~= "\nUPDATE LSOpenJobs SET Xtra8 = " ~
"\n(" ~
"\n  SELECT " ~
"\n  ( " ~
"\nCASE " ~
"\nWHEN instr(LSOpenJobs.XtraD,'%') > 0 THEN " ~
"\n  round(sum(t2.Xtra8) * cast(LSOpenJobs.XtraD as integer)/100.0, 2) 
" ~
"\nELSE " ~
"\n  LSOpenJobs.Xtra8 " ~
"\nEND " ~
"\n  ) " ~
"\n  FROM LSOpenJobs t2 " ~
"\nWHERE LSOpenJobs.ProjID = t2.ProjID AND LSOpenJobs.lang = t2.lang " ~
"\nAND t2.PSubClass != 'Portal-Fee' " ~
"\nAND t2.PSubClass != 'Rush-Job' " ~
"\n) " ~
"\nWHERE ProjID = " ~ pid ~ " AND PSubClass = 'Rush-Job';";
  cmd ~= "\nEND;";

and I was told by some of the users that our tool calculations was missing 
cents here and there in the quotes.  I said, "Blasphemy! SQLite does not makes 
mistakes!" and now, that I see this, I have to apologize and ask them for their 
forgiveness. :-)  I was blaming Excel and whatever else they used before 
inserting data into SQLite.  But now, I see that I was wrong.

Dr. Hipp, how many more scenarios, where round gives the wrong answer, exist?  
Thanks.

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

Re: [sqlite] round function inconsistent

[Alessandro Merolli]

Great! Now use SQLite API and add a new user defined function for your used 
case.
I suppose that SQlite should always follow a well-defined pattern: in this case 
as Mr. Hipp said, it is IEEE754.
Cheers!

> On 24 May 2019, at 08:27, radovan5  wrote:
> 
> In Delphi I have 2 functions that works (I override default sqlite round):
> 
> const
>   ExtEps = 1.0842021725E-19;
>   DblEps = 2.2204460493E-16;
>   KnownErrorLimit = 1.234375;
>   SafetyFactor = 2;
>   MaxRelErrDbl = DblEps * KnownErrorLimit * SafetyFactor;
>   MaxRelErrExt = ExtEps * KnownErrorLimit * SafetyFactor;
> 
> function RoundExt(const AValue: Extended; const ADigit: Integer = -2): 
> Extended;
> var
>   E: Extended;
> begin
>   E := IntPower(10, -ADigit);
>   Result := Round(AValue * (1 + MaxRelErrExt) * E) / E;
> end;
> 
> function RoundDbl(const AValue: Double; const ADigit: Integer = -2): Double;
> var
>   E: Double;
> begin
>   E := IntPower(10, -ADigit);
>   Result := Round(AValue * (1 + MaxRelErrDbl) * E) / E;
> end;
> 
> You could implement it in sqlite.
> 
> Regards Radovan
> 
> 
> On 24.05.2019 13:13, Richard Hipp wrote:
>> On 5/24/19, Hajo Bruns  wrote:
>>> Hi,
>>> the round function seems to round inconsistently:
>>> 
>>> ivesselect round(5.485,2), round (3.555,2),round (3.255,2)
>>> gives
>>> 5,49  3,56  3,25
>>> 
>>> Last result should be 3.26
>> 3.255 cannot be exactly represented as an IEEE754 double-precision
>> binary floating point number.  So the system has to use an
>> approximation.  The closest approximation is
>> 3.25489341858963598497211933135986328125 and that value
>> rounds to 3.25.
>> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] round function inconsistent

[radovan5]

In Delphi I have 2 functions that works (I override default sqlite round):

const
  ExtEps = 1.0842021725E-19;
  DblEps = 2.2204460493E-16;
  KnownErrorLimit = 1.234375;
  SafetyFactor = 2;
  MaxRelErrDbl = DblEps * KnownErrorLimit * SafetyFactor;
  MaxRelErrExt = ExtEps * KnownErrorLimit * SafetyFactor;

function RoundExt(const AValue: Extended; const ADigit: Integer = -2): 
Extended;

var
  E: Extended;
begin
  E := IntPower(10, -ADigit);
  Result := Round(AValue * (1 + MaxRelErrExt) * E) / E;
end;

function RoundDbl(const AValue: Double; const ADigit: Integer = -2): 
Double;

var
  E: Double;
begin
  E := IntPower(10, -ADigit);
  Result := Round(AValue * (1 + MaxRelErrDbl) * E) / E;
end;

You could implement it in sqlite.

Regards Radovan


On 24.05.2019 13:13, Richard Hipp wrote:

On 5/24/19, Hajo Bruns  wrote:

Hi,
the round function seems to round inconsistently:

ivesselect round(5.485,2), round (3.555,2),round (3.255,2)
gives
5,49  3,56  3,25

Last result should be 3.26

3.255 cannot be exactly represented as an IEEE754 double-precision
binary floating point number.  So the system has to use an
approximation.  The closest approximation is
3.25489341858963598497211933135986328125 and that value
rounds to 3.25.



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

Re: [sqlite] round function inconsistent

[Richard Hipp]

On 5/24/19, Hajo Bruns  wrote:
> Hi,
> the round function seems to round inconsistently:
>
> ivesselect round(5.485,2), round (3.555,2),round (3.255,2)
> gives
> 5,49  3,56  3,25
>
> Last result should be 3.26

3.255 cannot be exactly represented as an IEEE754 double-precision
binary floating point number.  So the system has to use an
approximation.  The closest approximation is
3.25489341858963598497211933135986328125 and that value
rounds to 3.25.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] round function inconsistent

[Hajo Bruns]

Hi,
the round function seems to round inconsistently:

ivesselect round(5.485,2), round (3.555,2),round (3.255,2)
gives
5,49  3,56  3,25

Last result should be 3.26
Used Version
3.24.0
(sqlite interop.dll)


Plan Software GmbH
Martin-Luther Str. 20 * D-66111 Saarbrücken * Germany
phone +49 (0) 681 / 37927-248
fax +49 (0) 681 / 37927-29
mailto:hbr...@plansoft.de
Geschäftsführer: Axel Biewer, Mesut Cengiz, Kevin Dewi, Michael Wille
Amtsgericht Saarbrücken, HRB 9989




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