Re: [sqlite] Bugs in foreign_key_list pragma

On May 24, 2019 2:22:46 p.m. EDT, "J. King" wrote: >First, a documentation bug: > > does not >indicate that a schema may in fact be prepended to the pragma the same >as its siblings. > >Second, the following is surprising: > >SQLite

Re: [sqlite] Have SQLite handle values of my own type

Hi, IP addresses are easy: convert to the form aaa.bbb.ccc.ddd where each of the four parts is always three digits long. IPv4 addresses are 32bit unsigned integers internally. The dotted -quad notation is 4 8bit unsigned integers that get concatenated together. If you store them as a 32bit

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] [SPAM?] Re: round function inconsistent

On 5/24/19 8:35 AM, Jose Isaias Cabrera wrote: > If IEEE754 can't figure out how to round 3.555 to 2 decimals, I don't know if > I'd trust it. ;-) thanks. Just to point out a simple fact that seems to have been glossed over a bit. In IEEE754 there is NO number with a value of exactly 3.555 (or

[sqlite] Bugs in foreign_key_list pragma

First, a documentation bug: does not indicate that a schema may in fact be prepended to the pragma the same as its siblings. Second, the following is surprising: SQLite version 3.28.0 2019-04-16 19:49:53 Enter ".help" for usage hints.

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

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

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

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

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 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] Unclear about some shell command differences

On 5/24/19, Tony Papadimitriou wrote: > Can someone explain the behavioral differences of the following shell > commands? > > .backup ?DB? FILEBackup DB (default "main") to FILE > .clone NEWDB Clone data into NEWDB from the existing database > .save FILE Write

Re: [sqlite] Bug: CTE name leaking through views

I tried it out in trunk and it works for me as well. Thanks for your help! Steve On Wed, May 22, 2019 at 3:50 PM Richard Hipp wrote: > Thanks for the test case. This problem should now be fixed on trunk. > Please try it out and let us know if you encounter any additional > probglems. > > -- >

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

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

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

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

> 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

[sqlite] Unclear about some shell command differences

Can someone explain the behavioral differences of the following shell commands? .backup ?DB? FILEBackup DB (default "main") to FILE .clone NEWDB Clone data into NEWDB from the existing database .save FILE Write in-memory database into FILE They all seem to

[sqlite] round function inconsistent

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

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

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

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

[sqlite] Seems I found 2 errors in .ar command of sqlite3 on Windows: patch included.

> Seems I found 2 errors in .ar command of sqlite3 on Windows: > First, in shell.c the code that reads lines from console as input uses > wrong codepage code CP_ACP or CP_OEMCP. > This doesn't allow to pass proper filenames to your func runOneSqlLine() , > as my Windows 10 console uses codepage

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

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

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

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

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

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

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] Have SQLite handle values of my own type

Windows TCP/IP userland used to be a port of the BSD networking tools (I think via Lachman Associates) so that's not surprising. Allegedly they reimplemented it at some point. On Thu, May 23, 2019 at 3:57 PM Keith Medcalf wrote: > On Thursday, 23 May, 2019 08:35, Dominique Devienne > wrote: >