Re: [sqlite] round function inconsistent

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

Re: [sqlite] round function inconsistent

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​

Re: [sqlite] round function inconsistent

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>

Re: [sqlite] round function inconsistent

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

Re: [sqlite] round function inconsistent

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

Re: [sqlite] round function inconsistent

> 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

Re: [sqlite] round function inconsistent

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

Re: [sqlite] round function inconsistent

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:

Re: [sqlite] round function inconsistent

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

Re: [sqlite] round function inconsistent

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.

Re: [sqlite] round function inconsistent

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

Re: [sqlite] round function inconsistent

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:

Re: [sqlite] round function inconsistent

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

Re: [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);

Re: [sqlite] round function inconsistent

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:

Re: [sqlite] round function inconsistent

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 *

Re: [sqlite] round function inconsistent

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

Re: [sqlite] round function inconsistent

> 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

Re: [sqlite] round function inconsistent

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

Re: [sqlite] round function inconsistent

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

Re: [sqlite] round function inconsistent

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

Re: [sqlite] round function inconsistent

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

Re: [sqlite] round function inconsistent

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

Re: [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

Re: [sqlite] round function inconsistent

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

Re: [sqlite] round function inconsistent

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

Re: [sqlite] round function inconsistent

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

Re: [sqlite] round function inconsistent

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

Re: [sqlite] round function inconsistent

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

Re: [sqlite] round function inconsistent

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 *

Re: [sqlite] round function inconsistent

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

.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:

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

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

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

> 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

Re: [sqlite] round documentation

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

Re: [sqlite] round documentation

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

Re: [sqlite] round documentation

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

Re: [sqlite] round documentation

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

Re: [sqlite] round documentation

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

Re: [sqlite] round documentation

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

Re: [sqlite] round documentation

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

Re: [sqlite] round problem?

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!

Re: [sqlite] round problem?

-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

Re: [sqlite] round problem?

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

Re: [sqlite] round problem?

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

Re: [sqlite] round problem?

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

Re: [sqlite] round problem?

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

Re: [sqlite] round problem?

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

Re: [sqlite] round problem?

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

Re: [sqlite] round problem?

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>

Re: [sqlite] round and storage class

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

Re: [sqlite] round ?

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

Re: [sqlite] round ?

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()

Re: [sqlite] round ?

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>

RE: [sqlite] round ?

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?