subject:"\[sqlite\] round \?"

Re: [sqlite] round function inconsistent

2019-05-28 Thread 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

2019-05-27 Thread 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

2019-05-27 Thread 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

2019-05-27 Thread 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

2019-05-26 Thread 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

2019-05-25 Thread 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

2019-05-25 Thread 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

2019-05-24 Thread 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, );
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

2019-05-24 Thread 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

2019-05-24 Thread 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

2019-05-24 Thread 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

2019-05-24 Thread 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

2019-05-24 Thread 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

2019-05-24 Thread 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

2019-05-24 Thread 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

2019-05-24 Thread 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

2019-05-24 Thread 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

2019-05-24 Thread 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

2019-05-24 Thread 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

2019-05-24 Thread 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

2019-05-24 Thread 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

2019-05-24 Thread 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

2019-05-24 Thread 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

2019-05-24 Thread 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

2019-05-24 Thread 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

2019-05-24 Thread 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

2019-05-24 Thread 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

2019-05-24 Thread 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

2019-05-24 Thread 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

2019-05-24 Thread 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

2019-05-24 Thread 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

2019-05-24 Thread 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

Re: [sqlite] Round-tripping SQLite back and forth between text representation.

2018-07-12 Thread Richard Hipp

On 7/12/18, dmp  wrote:
>
> I use a dump
> in my interface which I used with diff to compare changes in my
> personal expense database. This was to insure changes introduced in work
> on the interface were not screwing things up. Very helpful to insure
> your not introducing bugs.

I am glad that has been working for you.  But there is a caveat:  The
".dump" format can (and does) change slightly from one release of
SQLite to the next.  So you are welcomed to continue using ".dump"
this way, but just be careful that you do not compare the .dump output
from two different versions of SQLite.

-- 
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-tripping SQLite back and forth between text representation.

2018-07-12 Thread dmp

Randall wrote:

> My wishlist is:
> (o) Allow humans to view the contents of a DB without custom tools.

If what is meant here is a generic tool that opens/views any particular
file format, db context here, then there are tools including
the generic db gui that I have been working on for years.

> (o) Have a way to see what has changed between V1 and V2 of a database,
> e.g., for a "change review."
> (o) Have a way to merge two independent sets of database changes into
> a single result in an understandable way.

This has already been answered, .dump diff and sqldiff. I use a dump
in my interface which I used with diff to compare changes in my
personal expense database. This was to insure changes introduced in work
on the interface were not screwing things up. Very helpful to insure
your not introducing bugs.

> (o) Have a way to make changes (update, insert, delete) to the DB data
  in a  pinch without specialized tools.

My generic db gui will do all these. Once more it really is a plugin
framework so it is very easy to introduce your own code to extend its
behavior.

Seems parts of the wishlist could be provided more effectly by client/server
db rather than SQLite. My personal general expenses database is not
SQLite, but a client/server db. Just an old box back in the corner. Why,
because I use the same db server with my dad's, in his 90s, expenses which
we both can add, edit, search, review, and aggregate accounts at the
end of the year.

danap.

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

Re: [sqlite] Round-tripping SQLite back and forth between text representation.

2018-07-11 Thread Richard Hipp

On 7/11/18, Randall Smith  wrote:
>
> My wishlist is:
>
> (o) Allow humans to view the contents of a DB without custom tools.

SQLite database file are binary.  That is a necessity in any format
that needs to store binary data.  On the other hand, the SQLite
database file format is carefully and fully documented
(https://www.sqlite.org/fileformat2.html) and there have been
multiple, independent implementations of readers and writers for that
file format.  SQLite databases are one of only three formats (the
others being JSON and CSV) recommended by the US Library of Congress
for archival storage of datasets.
(https://www.sqlite.org/locrsf.html).  The SQLite database library is
the second mostly widely deployed bit of software in the world -
second only to zlib - so the tools needed to read SQLite are probably
already available on your system.  SQLite is baked into every Mac and
Windows machine.  SQLite is not a thoroughly baked into Linux
machines, but it is still pretty common.

Text files are also opaque binaries in the sense that they are stored
using a binary encoding on a disk drive or SSD.  They seem less opaque
because you have tools easily at hand (a filesystem and "cat") to
access them.  The point is this: Tools to access SQLite are also
widely available.  Perhaps not quite as widely as "cat", but nearly
so.

"Opaque" vs. "non-opaque" is not a binary property of data files.  It
is a question of degree.  A text file might seem less opaque than a
database, but that depends to some extent on the text that it
contains.  Try reading the HTML for a typical website.  Or trying
reading the XML that is at the core of a Word document or Power-Point
presentation.  Those files are all text, but they seem pretty opaque
to me.

> (o) Have a way to see what has changed between V1 and V2 of a database,
> e.g., for a "change review."

The "sqldiff" utility program will do this for you.  Just as with the
unix "diff" command, the "sqldiff" shows you (in human-readable form)
the difference between two SQLite database files.  The output takes
the form of SQL statements that will transform the first file into the
second.

> (o) Have a way to merge two independent sets of database changes into a
> single result in an understandable way.

The sqldiff command will do this.  If you have a baseline database B,
and two separate derivative databases D1 and D2, you can merge those
changes together by computing the differences in B->D1 and applying
those changes to D2.  Or compute the differences from B->D2 and apply
those changes to D1.  As with "patch" or "diff3", there is the
possibility of merge conflicts, but you a clean merge surprisingly
often.

> (o) Have a way to make changes (update, insert, delete) to the DB data in a
> pinch without specialized tools.

I guess it all comes down to how you define "specialized".  At some
point, tools become sufficiently ubiquitous and common-place that they
cease to be specialized.  The SQLite command-line shell may have
reached that threshold.  If not, it is certainly close.  SQLite is
certainly not obscure or esoteric.  It comes installed by default on
just about every computer you can purchase today.

-- 
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-tripping SQLite back and forth between text representation.

2018-07-11 Thread Simon Slavin

On 11 Jul 2018, at 6:01pm, Randall Smith  wrote:

> (o) Allow humans to view the contents of a DB without custom tools.
> (o) Have a way to see what has changed between V1 and V2 of a database, e.g., 
> for a "change review."

SQL is based around Ted Codd's view of relational databases.  One of the 
fundamentals of this view is that a table of rows has no inherent order.  Rows 
of data in one table are like pebbles in a bag, not dots on a line.  Or, if you 
prefer computing terms, they're a set, not an array.

However a text file does have an inherent order.  So any tool that coverts a 
SQL database to a text file must do something arbitrary: pick an order.  The 
conclusion is that if you're comparing two databases you need to compare them 
while they're databases, not when you're looking at them as text files.

So if your first point above comes down to "view the database as text" then the 
above two points conflict with one-another.  You need to do that first point 
and the second point as two separate procedures, not do step 1 then use the 
output of that for step 2.

As a more direct answer to an earlier question, the ".dump" command of the CLI 
does dump the data in a predictable and consistent order.  But it is not 
documented to do so.  So a future version can change that.

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

Re: [sqlite] Round-tripping SQLite back and forth between text representation.

2018-07-11 Thread Randall Smith

On 2018/07/10 8:27 PM, Randall Smith wrote:

> One follow-up: Do you know if the dump output is "deterministic" over

> time? That is, if I diff two dumps taken at different times, will the

> unchanged material be in the same order and so on? Or is the ordering

> effectively random?

> My underlying question is "can text-comparing two DB dumps be used to 
> determine what has changed?"

On 2018/07/11 Simon wrote:

I am not sure if it is 100% deterministic - it probably is, however, I would 
like to point out that while parsing a dump (supposing it IS deterministic) is 
possible, writing some code to check congruence between two DBs at the business 
end of the SQLite API is significantly better, much easier and always 100% 
deterministic.

Not only that, but the sqldiff command-line utility (download page) does it 
already (though you may require a more specific result, but at a minimum its a 
good start).

Is there perhaps a specific difficulty which makes you think that parsing the 
dump would provide a better/easier insight into which data changed?

Fundamental problems with SQLite or other binary representations are (a) the 
information represented is opaque unless one spends time and money creating 
bespoke tools to allow viewing and technical reviews of the content, and (b) 
there is no simple way to allow concurrent development of info by several 
people and to reconcile independent changes into a coherent whole ("merging").  
These are both mission critical for a team effort of any size (even size=2!). 
The software industry has historically avoided these problems by storing 
everything in the form of text files, and has developed elaborate tools and 
procedures for viewing, reviewing, storing, and merging information in this 
form and as a result large teams can collaborate on a rapidly evolving body of 
digital information easily and well.

Binary file formats like SQLite, while having many compelling advantages, have 
a hard time penetrating into areas where multiple people need to collaborate on 
an evolving body of information because of the limitations described above.  
IMO this is an urgent problem and one that has not been solved very well for 
SQLite.  I don't have the wherewithal to solve it generally, but I am trying to 
see if there are ways to bridge the gap between SQLite DBs and existing 
team-capable workflows built around text files.

My wishlist is:

(o) Allow humans to view the contents of a DB without custom tools.
(o) Have a way to see what has changed between V1 and V2 of a database, e.g., 
for a "change review."
(o) Have a way to merge two independent sets of database changes into a single 
result in an understandable way.
(o) Have a way to make changes (update, insert, delete) to the DB data in a 
pinch without specialized tools.

I'm thinking the dump approach you described previously has promise provided 
certain criteria are met.  Interestingly, the text representation produced by 
dump is about the same size as the "normal" binary form, and it will compress 
to about 1/8 the size of the binary form.  So it's not a bad archival format.

Randall.

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

Re: [sqlite] Round-tripping SQLite back and forth between text representation.

2018-07-11 Thread Will Parsons

On Tuesday, 10 Jul 2018  2:27 PM -0400, Randall Smith wrote:


> My underlying question is "can text-comparing two DB dumps be used
> to determine what has changed?"

I don't know if it will meet your needs, but I've written a script for
my own purposes to compare DB changes.  Since it's fairly short, I
include it here.

--8<---cut here---start->8---
#!/bin/sh
# Compare two SQLite3 databases.
# If invoked under the name "tksql3diff", the diff is displayed graphically
# using tkdiff to display the differences.
#
# Last modified:  10-Jul-2018  Wm. Parsons

if [ $# -ne 2 ]
then
   echo "Usage: `basename $0`  "
   exit
fi

if [ `basename $0` = "tksql3diff" ]
then
   diff=tkdiff
else
   diff=diff
fi

file1=$1
file2=$2

dump()
{
   file=$1

   # check that the file is readable
   if [ ! -r $file ]
   then
  echo "cannot read $file" >&2
  exit 1
   fi

   sql="select 'Application ID:'; pragma application_id;
select 'User version:'; pragma user_version;"
   tables=`sqlite3 $file < $file.dump
}

dump $file1
dump $file2

$diff $file1.dump $file2.dump

rm $file1.dump $file2.dump
--8<---cut here---end--->8---

Note:  This *probably* doesn't play well if you have BLOB fields in
your database.

-- 
Will

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

Re: [sqlite] Round-tripping SQLite back and forth between text representation.

2018-07-10 Thread R Smith


On 2018/07/10 8:27 PM, Randall Smith wrote:
One follow-up: Do you know if the dump output is "deterministic" over 
time? That is, if I diff two dumps taken at different times, will the 
unchanged material be in the same order and so on? Or is the ordering 
effectively random?

My underlying question is "can text-comparing two DB dumps be used to determine what 
has changed?"


I am not sure if it is 100% deterministic - it probably is, however, I 
would like to point out that while parsing a dump (supposing it IS 
deterministic) is possible, writing some code to check congruence 
between two DBs at the business end of the SQLite API is significantly 
better, much easier and always 100% deterministic.


Not only that, but the sqldiff command-line utility (download page) does 
it already (though you may require a more specific result, but at a 
minimum its a good start).


Is there perhaps a specific difficulty which makes you think that 
parsing the dump would provide a better/easier insight into which data 
changed?



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

[sqlite] Round-tripping SQLite back and forth between text representation.

2018-07-10 Thread Randall Smith

From: Simon Slavin 



> I'm curious if there is some standard or normal way to convert a SQLite DB to 
> a text representation, and then recreate the DB content from the text.  
> Naively, this seems hard or impossible as a general problem, but perhaps I am 
> missing something.



Yep.  It's done a lot, to convert a database to a different SQL engine, or 
rescue data from a corrupt database.  See section 10 of







For more information read the documentation about the '.dump' and '.read' 
commands on the same page.  Or ask here.



Simon.



Thanks, Simon, for the info.



One follow-up:  Do you know if the dump output is "deterministic" over time?  
That is, if I diff two dumps taken at different times, will the unchanged 
material be in the same order and so on?  Or is the ordering effectively random?



My underlying question is "can text-comparing two DB dumps be used to determine 
what has changed?"



Thanks again for any insights.



Randall Smith








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

Re: [sqlite] Round-tripping SQLite back and forth between text representation.

2018-07-09 Thread Simon Slavin

On 10 Jul 2018, at 1:52am, Randall Smith  wrote:

> I'm curious if there is some standard or normal way to convert a SQLite DB to 
> a text representation, and then recreate the DB content from the text.  
> Naively, this seems hard or impossible as a general problem, but perhaps I am 
> missing something.

Yep.  It's done a lot, to convert a database to a different SQL engine, or 
rescue data from a corrupt database.  See section 10 of

For more information read the documentation about the '.dump' and '.read' 
commands on the same page.  Or ask here.

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

Re: [sqlite] Round-tripping SQLite back and forth between text representation.

2018-07-09 Thread Keith Medcalf


.dump in the command line shell?


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


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Randall Smith
>Sent: Monday, 9 July, 2018 18:53
>To: sqlite-users@mailinglists.sqlite.org
>Subject: [sqlite] Round-tripping SQLite back and forth between text
>representation.
>
>I'm curious if there is some standard or normal way to convert a
>SQLite DB to a text representation, and then recreate the DB content
>from the text.  Naively, this seems hard or impossible as a general
>problem, but perhaps I am missing something.
>
>Thanks in advance for any advice or suggestions.
>
>Randall.
>___
>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-tripping SQLite back and forth between text representation.

2018-07-09 Thread J Decker

does it have to be text?  There was serialization added to sqlite
https://www.sqlite.org/c3ref/serialize.html

On Mon, Jul 9, 2018 at 5:52 PM Randall Smith 
wrote:

> I'm curious if there is some standard or normal way to convert a SQLite DB
> to a text representation, and then recreate the DB content from the text.
> Naively, this seems hard or impossible as a general problem, but perhaps I
> am missing something.
>
> Thanks in advance for any advice or suggestions.
>
> Randall.
> ___
> 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] Round-tripping SQLite back and forth between text representation.

2018-07-09 Thread Randall Smith

I'm curious if there is some standard or normal way to convert a SQLite DB to a 
text representation, and then recreate the DB content from the text.  Naively, 
this seems hard or impossible as a general problem, but perhaps I am missing 
something.

Thanks in advance for any advice or suggestions.

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

Re: [sqlite] Round was Mistake in documentation and question

2010-08-09 Thread Roger Andersson

> In addition a quick question.
> Is there a way to perform a division of two columns (real 
> type) and force the result to be presented/rounded in 2 
> decimal places ?
> 
ROUND should do the trick ;-)
http://www.sqlite.org/lang_corefunc.html#round
Please note
http://www.sqlite.org/faq.html#q16

/Roger

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

Re: [sqlite] round documentation

2010-05-28 Thread Matt Young

Bingo, I live and learn

On 5/28/10, Igor Tandetnik  wrote:
> Igor Tandetnik wrote:
>> Matt Young  wrote:
>>> I second that documentation confusion.  There is no truncate to
>>> integer, though I wish it would.
>>
>> Somewhat off-topic, but if you want truncation, this would do it: round(x
>> - 0.5) .
>
> Actually, cast(x as integer) works better. It follows the usual C rules,
> like (int)x for x declared as double.
> --
> Igor Tandetnik
>
> ___
> 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] round documentation

2010-05-28 Thread Igor Tandetnik

Igor Tandetnik wrote:
> Matt Young  wrote:
>> I second that documentation confusion.  There is no truncate to
>> integer, though I wish it would.
> 
> Somewhat off-topic, but if you want truncation, this would do it: round(x - 
> 0.5) .

Actually, cast(x as integer) works better. It follows the usual C rules, like 
(int)x for x declared as double.
-- 
Igor Tandetnik

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

Re: [sqlite] round documentation

2010-05-27 Thread Matt Young

OK, got it.  I was referring to the number of decimal points, but yes
round(x,0) does do something

On 5/27/10, Igor Tandetnik  wrote:
> Matt Young wrote:
>> Round(x,0) really doesn't exist, it simply does  round(x,1)
>
> select round(4.1, 0), round(4.1, 1);
> 4.04.1
>
> --
> Igor Tandetnik
>
> ___
> 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] round documentation

2010-05-27 Thread Igor Tandetnik

Matt Young wrote:
> Round(x,0) really doesn't exist, it simply does  round(x,1)

select round(4.1, 0), round(4.1, 1);
4.04.1

-- 
Igor Tandetnik

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

Re: [sqlite] round documentation

2010-05-27 Thread Matt Young

sqlite> select round(4.-.5);
4.0
sqlite> select round(4.-0);
4.0
sqlite> select round(4);
4.0
sqlite> select round(4,0);
4.0
sqlite> select round(4,1);
4.0
sqlite> select round(4,2);
4.0
sqlite> select round(4.666,2);
4.67
sqlite>


Round(x,0) really doesn't exist, it simply does  round(x,1)

On 5/27/10, Igor Tandetnik <itandet...@mvps.org> wrote:
> Matt Young <youngsan...@gmail.com> wrote:
>> I second that documentation confusion.  There is no truncate to
>> integer, though I wish it would.
>
> Somewhat off-topic, but if you want truncation, this would do it: round(x -
> 0.5) . Well, it's more like floor(), it goes down rather than towards zero
> (which makes a difference when x is negative).
> --
> Igor Tandetnik
>
>
> ___
> 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] round documentation

2010-05-27 Thread Igor Tandetnik

Matt Young  wrote:
> I second that documentation confusion.  There is no truncate to
> integer, though I wish it would.

Somewhat off-topic, but if you want truncation, this would do it: round(x - 
0.5) . Well, it's more like floor(), it goes down rather than towards zero 
(which makes a difference when x is negative).
-- 
Igor Tandetnik


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

Re: [sqlite] round documentation

2010-05-27 Thread Matt Young

I second that documentation confusion.  There is no truncate to
integer, though I wish it would.

On 5/27/10, Wilson, Ronald  wrote:
> From http://www.sqlite.org/lang_corefunc.html
>
> "The round(X,Y) function returns a string representation of the
> floating-point value X rounded to Y digits to the right of the decimal
> point. If the Y argument is omitted, the X value is truncated to an
> integer."
>
> The documentation above is incorrect in the last clause.  The X value is not
> truncated it is rounded to an integer.  Also, it returns a real, not an
> integer or a string.
>
> SQLite version 3.6.22
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> select round(1.6);
> 2.0
> sqlite> select a, typeof(a) from (select round(1.6) as a);
> 2.0|real
> sqlite> select a, typeof(a) from (select round("1.6") as a);
> 2.0|real
> sqlite> select a, typeof(a) from (select round('1.6') as a);
> 2.0|real
> sqlite>
>
> Ron Wilson, Engineering Project Lead
> (o) 434.455.6453, (m) 434.851.1612, www.harris.com
>
> ___
> 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] round documentation

2010-05-27 Thread Wilson, Ronald

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

"The round(X,Y) function returns a string representation of the floating-point 
value X rounded to Y digits to the right of the decimal point. If the Y 
argument is omitted, the X value is truncated to an integer."

The documentation above is incorrect in the last clause.  The X value is not 
truncated it is rounded to an integer.  Also, it returns a real, not an integer 
or a string.

SQLite version 3.6.22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select round(1.6);
2.0
sqlite> select a, typeof(a) from (select round(1.6) as a);
2.0|real
sqlite> select a, typeof(a) from (select round("1.6") as a);
2.0|real
sqlite> select a, typeof(a) from (select round('1.6') as a);
2.0|real
sqlite>

Ron Wilson, Engineering Project Lead
(o) 434.455.6453, (m) 434.851.1612, www.harris.com

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

Re: [sqlite] round problem?

2010-02-15 Thread Doug Currie

On Feb 15, 2010, at 1:43 PM, Roger Binns wrote:

> Shane Harrelson wrote:
>> I'm looking at how this can be improved.
> 
> It seems that everyone else is converging on using David Gay's dtoa.c 

We've been "converging" for a few years!

http://www.mail-archive.com/sqlite-users@sqlite.org/msg09529.html

e


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

Re: [sqlite] round problem?

2010-02-15 Thread Roger Binns

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Shane Harrelson wrote:
> I'm looking at how this can be improved.

It seems that everyone else is converging on using David Gay's dtoa.c whose
algorithm is based on the paper "How to Print Floating-Point Numbers
Accurately" by  Guy L. Steele, Jr. and Jon L. White [Proc. ACM SIGPLAN '90,
pp. 92-101].   David's paper is at
http://cm.bell-labs.com/cm/cs/doc/90/4-10.ps.gz

Python has just switched to this and gcc uses it internally too now.  The
latest version is at
http://gcc.gnu.org/viewcvs/trunk/libjava/classpath/native/fdlibm/dtoa.c?view=markup

(Be wary of older versions as they get miscompiled due to aliasing issues.)

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAkt5lcUACgkQmOOfHg372QS2AQCfVHu8c82fz9h51SuYMnQIEpJV
txcAoIhNgdXI6Qwsw2XaDrj1g2wmX0se
=Zi+T
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] round problem?

2010-02-15 Thread Shane Harrelson

On Mon, Feb 15, 2010 at 12:52 PM, Phil Hibbs  wrote:

> Shane Harelson:
> > Under the covers, when the second term to ROUND(X,y) is omitted, SQLite
> adds
> > 0.5 and then truncates.Because of floating point precision, some
> numbers
> > can not be represented exactly... causing the odd rounding you saw in
> your
> > examples.
>
> I've just had a look "under the covers", and indeed it seems that
> SQLite has its own printf implementation. Makes sense, as I think the
> C standard itself is also implementation-defined in this case, and
> this might be the kind of thing that SQLite coders want control over.
>
> It was hasty to conclude that round-to-even is the rule, as one more
> example shows:
>
> sqlite> select round(40226.5);
> 40227.0
>
> I think the 40223.5 case is just an example of binary/decimal floating
> point incompatibility. As Kernighan and Plauger put it:
> "Floating-point numbers are a lot like sandpiles: Every time you move
> one you lose a little sand and pick up a little dirt."
>
> I'm a little surprised that it's going wrong with a number ending in
> 0.5 though, I'd have thought that that would be expressible perfectly
> in binary without loss. I don't know enough about it though.
>
> Phil Hibbs.
>
>

As you and Igor point out, the numbers being tested can be accurately
represented.   However, as part of the SQLite's internal printf()
implementation, the floating point value is shifted to (loosely) scientific
form and manipulated for formatting.   So 40224.5 is converted to something
like 4.02245e4 before printing.   The errors get introduced as part of this
manipulation.  I'm looking at how this can be improved.

HTH.
-Shane

PS.  Sorry if this got posted multiple times.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] round problem?

2010-02-15 Thread Shane Harrelson

On Mon, Feb 15, 2010 at 12:52 PM, Phil Hibbs  wrote:

> Shane Harelson:
> > Under the covers, when the second term to ROUND(X,y) is omitted, SQLite
> adds
> > 0.5 and then truncates.Because of floating point precision, some
> numbers
> > can not be represented exactly... causing the odd rounding you saw in
> your
> > examples.
>
> I've just had a look "under the covers", and indeed it seems that
> SQLite has its own printf implementation. Makes sense, as I think the
> C standard itself is also implementation-defined in this case, and
> this might be the kind of thing that SQLite coders want control over.
>
> It was hasty to conclude that round-to-even is the rule, as one more
> example shows:
>
> sqlite> select round(40226.5);
> 40227.0
>
> I think the 40223.5 case is just an example of binary/decimal floating
> point incompatibility. As Kernighan and Plauger put it:
> "Floating-point numbers are a lot like sandpiles: Every time you move
> one you lose a little sand and pick up a little dirt."
>
> I'm a little surprised that it's going wrong with a number ending in
> 0.5 though, I'd have thought that that would be expressible perfectly
> in binary without loss. I don't know enough about it though.
>
> Phil Hibbs.
>
>

As you and Igor point out, the numbers being tested can be accurately
represented.   However, as part of the SQLite's internal printf()
implementation, the floating point value is shifted to (loosely) scientific
form and manipulated for formatting.   So 40224.5 is converted to something
like 4.02245e4 before printing.   The errors get introduced as part of this
manipulation.  I'm looking at how this can be improved.

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

Re: [sqlite] round problem?

2010-02-15 Thread Phil Hibbs

Shane Harelson:
> Under the covers, when the second term to ROUND(X,y) is omitted, SQLite adds
> 0.5 and then truncates.Because of floating point precision, some numbers
> can not be represented exactly... causing the odd rounding you saw in your
> examples.

I've just had a look "under the covers", and indeed it seems that
SQLite has its own printf implementation. Makes sense, as I think the
C standard itself is also implementation-defined in this case, and
this might be the kind of thing that SQLite coders want control over.

It was hasty to conclude that round-to-even is the rule, as one more
example shows:

sqlite> select round(40226.5);
40227.0

I think the 40223.5 case is just an example of binary/decimal floating
point incompatibility. As Kernighan and Plauger put it:
"Floating-point numbers are a lot like sandpiles: Every time you move
one you lose a little sand and pick up a little dirt."

I'm a little surprised that it's going wrong with a number ending in
0.5 though, I'd have thought that that would be expressible perfectly
in binary without loss. I don't know enough about it though.

Phil Hibbs.
-- 
Don't you just hate self-referential sigs?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] round problem?

2010-02-15 Thread Igor Tandetnik

Shane Harrelson wrote:
> http://www.sqlite.org/lang_corefunc.html#round
> 
> Under the covers, when the second term to ROUND(X,y) is omitted, SQLite adds
> 0.5 and then truncates.Because of floating point precision, some numbers
> can not be represented exactly... causing the odd rounding you saw in your
> examples.

While it's true that some numbers cannot be represented exactly in floating 
point, numbers like 40223, 0.5 and 40223.5 are in fact represented exactly. I 
don't think this can explain the behavior observed.

Igor Tandetnik

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

Re: [sqlite] round problem?

2010-02-15 Thread Shane Harrelson

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

Under the covers, when the second term to ROUND(X,y) is omitted, SQLite adds
0.5 and then truncates.Because of floating point precision, some numbers
can not be represented exactly... causing the odd rounding you saw in your
examples.

I'll see what I can do to update the documentation and/or make the rounding
more consistent.

HTH.
-Shane


On Mon, Feb 15, 2010 at 12:03 PM, Phil Hibbs  wrote:

> Igor:
> > http://en.wikipedia.org/wiki/Rounding#Round_half_to_even
>
> So, are you saying round-half-to-even is the SQLite behaviour? I would
> have expected it to have used the "normal" mathematical convention of
> round-half-away-from-zero. The reason this is "normal" mathematical
> behaviour is that any decimal result is likely to be a truncation of
> the real result, e.g. "pi = 3.14195". The SQL standard leaves it up to
> the implementation:
>
> sec 4.4.1:
> An approximation obtained by rounding of a numerical value N for
> an  T is a value V representable in T such
> that the absolute value of the difference between N and the nu-
> merical value of V is not greater than half the absolute value
> of the difference between two successive numerical values repre-
> sentable in T. If there are more than one such values V, then it is
> implementation-defined which one is taken.
>
> So I guess "round half to even" is a reasonable interpretation, but
> I'd like to see this, and all implementation-defined behaviour,
> documented.
>
> Phil Hibbs.
> --
> Don't you just hate self-referential sigs?
> ___
> 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] round problem?

2010-02-15 Thread Phil Hibbs

Igor:
> http://en.wikipedia.org/wiki/Rounding#Round_half_to_even

So, are you saying round-half-to-even is the SQLite behaviour? I would
have expected it to have used the "normal" mathematical convention of
round-half-away-from-zero. The reason this is "normal" mathematical
behaviour is that any decimal result is likely to be a truncation of
the real result, e.g. "pi = 3.14195". The SQL standard leaves it up to
the implementation:

sec 4.4.1:
 An approximation obtained by rounding of a numerical value N for
 an  T is a value V representable in T such
 that the absolute value of the difference between N and the nu-
 merical value of V is not greater than half the absolute value
 of the difference between two successive numerical values repre-
 sentable in T. If there are more than one such values V, then it is
 implementation-defined which one is taken.

So I guess "round half to even" is a reasonable interpretation, but
I'd like to see this, and all implementation-defined behaviour,
documented.

Phil Hibbs.
-- 
Don't you just hate self-referential sigs?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] round problem?

2010-02-15 Thread Igor Tandetnik

zabusovm...@mail.ru wrote:
> SQLite version 3.6.22
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> select round(40223+0.5);
> 40224.0
> sqlite> select round(40224+0.5);
> 40224.0
> sqlite> select round(40225+0.5);
> 40226.0
> sqlite>

http://en.wikipedia.org/wiki/Rounding#Round_half_to_even

-- 
Igor Tandetnik

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

Re: [sqlite] round and storage class

2007-03-26 Thread Dennis Cote


Iulian Musat wrote:

Hello everybody !

First of all I have to say that I'm a fairly new user of SQLite, so be 
kind :-)


The problem is with the "round" function: the result have a decimal 
point even if the second argument is zero (the default):

sqlite> select round(1.234);
1.0
sqlite> select round(1.234,0);
1.0

I'm not sure if this shouldn't produce '1' instead of '1.0'.

The reason for bringing this up is the resulted storage class in a 
statement like this:

INSERT INTO tableint SELECT ROUND(some_expression) FROM other_table;

I know that in theory this shouldn't matter for SQLite, but I suppose 
that some queries would be more efficient if the storage class is 
INTEGER for the columns involved (please tell me if I'm wrong here).



So, here are the questions:
1. If you feel that round should behave this way, how do I submit a 
patch. Not a lot of modifications nor terrible smart (basically some 
work done at the end of roundFunc from func.c), but I'm also curios 
how one can submit patches (sorry if I missed this info from sqlite.org).
See section Contributed Code at http://www.sqlite.org/copyright.html for 
a start.


2. Is there a way to find the storage class for a particular field 
from a particular row of a table?
I'm not sure for example if the next statements will produce the same 
storage class:

create tableint (i integer);
insert into tableint values(1);
insert into tableint values(1.0);
insert into tableint values(round(1));

You can use the typeof() function to get the type (storage class) of a 
field.


   select typeof(round(1.234));

You can use the cast(x as type) syntax to change the type of the round 
result to integer.


   select cast(round(1.234) as integer);

HTH
Dennis Cote

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] round and storage class

2007-03-26 Thread Iulian Musat


Hello everybody !

First of all I have to say that I'm a fairly new user of SQLite, so be 
kind :-)


The problem is with the "round" function: the result have a decimal 
point even if the second argument is zero (the default):

sqlite> select round(1.234);
1.0
sqlite> select round(1.234,0);
1.0

I'm not sure if this shouldn't produce '1' instead of '1.0'.

The reason for bringing this up is the resulted storage class in a 
statement like this:

INSERT INTO tableint SELECT ROUND(some_expression) FROM other_table;

I know that in theory this shouldn't matter for SQLite, but I suppose 
that some queries would be more efficient if the storage class is 
INTEGER for the columns involved (please tell me if I'm wrong here).



So, here are the questions:
1. If you feel that round should behave this way, how do I submit a 
patch. Not a lot of modifications nor terrible smart (basically some 
work done at the end of roundFunc from func.c), but I'm also curios how 
one can submit patches (sorry if I missed this info from sqlite.org).


2. Is there a way to find the storage class for a particular field from 
a particular row of a table?
I'm not sure for example if the next statements will produce the same 
storage class:

create tableint (i integer);
insert into tableint values(1);
insert into tableint values(1.0);
insert into tableint values(round(1));

I'm just getting used with VDBE, but from running these with "explain" I 
think everything is clear until reaching "MakeRecord". For example, the 
first statement would produce something like:

 cut -
5|Integer|1|0|
6|MakeRecord|1|0|d
 cut -
And the second:
 cut -
5|Real|0|0|1.0
6|MakeRecord|1|0|d
 cut -

The 'd' from MakeRecord is just the column affinity, doesn't specify how 
the actual record was stored:

sqlite> explain insert into tableint values(1.5);
...
5|Real|0|0|1.5
6|MakeRecord|1|0|d
...
Same code, but obviously 1.5 is stored as real.


Regards,
-Iulian


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] round ?

2005-07-28 Thread Nicolas Martin


Oops,
i didn't check cvstrack
Many thanks !
Nicolas
D. Richard Hipp wrote:


On Thu, 2005-07-28 at 08:37 +0200, Nicolas Martin wrote:
 


Some strange effect of the round expression :

sqlite> select round(1-0.5);
0
sqlite> select round(2-0.5);
2
   



See http://www.sqlite.org/cvstrac/tktview?tn=1316.  The
problem results from inconsistent behavior in platform
printf() functions.  The fix was for SQLite to always use
it's own internal printf() function.

Re: [sqlite] round ?

2005-07-28 Thread D. Richard Hipp

On Thu, 2005-07-28 at 08:37 +0200, Nicolas Martin wrote:
> Some strange effect of the round expression :
> 
> sqlite> select round(1-0.5);
> 0
> sqlite> select round(2-0.5);
> 2

See http://www.sqlite.org/cvstrac/tktview?tn=1316.  The
problem results from inconsistent behavior in platform
printf() functions.  The fix was for SQLite to always use
it's own internal printf() function.
-- 
D. Richard Hipp <[EMAIL PROTECTED]>

Re: [sqlite] round ?

2005-07-28 Thread Nicolas Martin


My version in sqlite3 v3.22 on freebsd5.4
I will check on Windows on the same computer.


Damian Slee wrote:


I tried it on windows for you.   sqlite3.exe v3.21

sqlite> select round(1-0.5);
1
sqlite> select round(2-0.5);
2
sqlite> select round(3-0.5);
3
sqlite> select round(4-0.5);
4
sqlite> select round(5-0.5);
5
sqlite> select round(6-0.5);
6

Maybe the math libarary on the C compiler you are using?


-Original Message-
From: Nicolas Martin [mailto:[EMAIL PROTECTED] 
Sent: Thursday, July 28, 2005 2:37 PM

To: sqlite-users@sqlite.org
Subject: [sqlite] round ?

Some strange effect of the round expression :

sqlite> select round(1-0.5);
0
sqlite> select round(2-0.5);
2
sqlite> select round(3-0.5);
2
sqlite> select round(4-0.5);
4
sqlite> select round(5-0.5);
4
sqlite> select round(6-0.5);
6
sqlite>

RE: [sqlite] round ?

2005-07-28 Thread Damian Slee

I tried it on windows for you.   sqlite3.exe v3.21

sqlite> select round(1-0.5);
1
sqlite> select round(2-0.5);
2
sqlite> select round(3-0.5);
3
sqlite> select round(4-0.5);
4
sqlite> select round(5-0.5);
5
sqlite> select round(6-0.5);
6

Maybe the math libarary on the C compiler you are using?
 

-Original Message-
From: Nicolas Martin [mailto:[EMAIL PROTECTED] 
Sent: Thursday, July 28, 2005 2:37 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] round ?

Some strange effect of the round expression :

sqlite> select round(1-0.5);
0
sqlite> select round(2-0.5);
2
sqlite> select round(3-0.5);
2
sqlite> select round(4-0.5);
4
sqlite> select round(5-0.5);
4
sqlite> select round(6-0.5);
6
sqlite>






--
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.338 / Virus Database: 267.9.6/59 - Release Date: 27/07/2005
 

-- 
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.338 / Virus Database: 267.9.6/59 - Release Date: 27/07/2005

[sqlite] round ?

2005-07-28 Thread Nicolas Martin


Some strange effect of the round expression :

sqlite> select round(1-0.5);
0
sqlite> select round(2-0.5);
2
sqlite> select round(3-0.5);
2
sqlite> select round(4-0.5);
4
sqlite> select round(5-0.5);
4
sqlite> select round(6-0.5);
6
sqlite>

69 matches

Mail list logo