Re: [sqlite] [SPAM?] Re: [EXTERNAL] Re: Should SQLite distinguish between +0.0 and -0.0 on output?
On 6/13/19 10:51 AM, R Smith wrote: > On 2019/06/13 4:44 PM, Doug Currie wrote: >>> >>> Except by the rules of IEEE (as I understand them) >>> >>> -0.0 < 0.0 is FALSE, so -0.0 is NOT "definitely left of true zero" >>> >> Except that 0.0 is also an approximation to zero, not "true zero." >> >> Consider that 1/-0.0 is -inf whereas 1/0.0 is +int > > > I do not know if this is the result case in any of the programming > languages, but in Mathematical terms that is just not true. > > 1/0.0 --> Undefined, doesn't exist, cannot be computed, Should error > out. Anything returning +Inf or -Inf is plain wrong. > I posit the same holds true for 1/-0.0 Yes, 1.0/0.0 is undefined in the Field of Real numbers, but IEEE isn't the field of Real Numbers. First, as pointed out, it has limited precision, but secondly it have values that are not in the field of Real Numbers, namely NaN and +/-Inf. Note, that with a computer, you need to do SOMETHING when asked for 1.0/0.0, it isn't good to just stop (and traps/exceptions are hard to define for general compution systems), so defining the result is much better than just defining that anything could happen. It could have been defined as just a NaN, but having a special 'error' value for +Inf or -Inf turns out to be very useful in some fields. -- Richard Damon ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [SPAM?] Re: [EXTERNAL] Re: Should SQLite distinguish between +0.0 and -0.0 on output?
> Le 13 juin 2019 à 23:29, Richard Hipp a écrit : > > You are right. I had forgotten about that point. This has not been > changed even now, and so -0.0 does get truncated to +0.0 when it is > stored in the database file. Would it cost much, performance-wise, to treat double(-0.0) apart and not convert it to integer 0 during storage binary format optimization? [This, independently of the discussion about text conversion, or not, from double(-0.0) to '-0.0' (proposal) or '0.0' (current behaviour)]. — Best Regards, Meilleures salutations, Met vriendelijke groeten, Mit besten Grüßen, Olivier Mascia ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [SPAM?] Re: [EXTERNAL] Re: Should SQLite distinguish between +0.0 and -0.0 on output?
On 13 Jun 2019, at 11:08pm, Keith Medcalf wrote: > Note that even when the column is declared with no affinity that NaNs are > stored as null. Other than NaN, all other IEEE754 double values (including > subnormals and +/- Inf) seem to round-trip correctly through the bind_double > and column_double interface ... Just to round this off, IEEE754 has two different NaN values: signalling and non-signalling. SQLite doesn't distinguish between them. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [SPAM?] Re: [EXTERNAL] Re: Should SQLite distinguish between +0.0 and -0.0 on output?
Note that even when the column is declared with no affinity that NaNs are stored as null. Other than NaN, all other IEEE754 double values (including subnormals and +/- Inf) seem to round-trip correctly through the bind_double and column_double interface ... --- 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 Donald Shepherd >Sent: Thursday, 13 June, 2019 15:59 >To: General Discussion of SQLite Database >Subject: Re: [sqlite] [SPAM?] Re: [EXTERNAL] Re: Should SQLite >distinguish between +0.0 and -0.0 on output? > >On Fri., 14 Jun. 2019, 7:43 am Keith Medcalf, >wrote: > >> On Thursday, 13 June, 2019 15:21, Donald Shepherd < >> donald.sheph...@gmail.com> wrote: >> >> >On Fri, 14 Jun 2019 at 7:11 am, Richard Hipp >wrote: >> >> >> On 6/13/19, Donald Shepherd wrote: >> >> >>> Given there's been numerous comments to the effect that SQLite >> >>> now supports -0.0 storing and retrieval other than printing, >> >>> I'm curious which version this was implemented in as I wouldn't >> >>> mind removing my custom code when we move to a SQLite version >> >>> with this improvement. >> >> >> SQLite version 3.0.0 from 2004-06-17. >> >> >When I reported that testing on 3.8.7.2 stored -0.0 but retrieved >> >0.0, your comment on this list was that "SQLite converts integer >> >floating point values to actual integers for storage (because that >> >takes up less space on disk) and then converts back to double upon >> >retrieval. That round-trip would change -0.0 into +0.0." >> >> >I was wondering when this changed? >> >> >That doesn't seem like "supporting -0.0 storing and retrieval" >since >> >3.0.0.0 if it was previously being modified to another value >during >> >storage, losing the sign in the process. >> >> Actually, it depends on whether the database column has real >affinity or >> not. If it has no affinity then the value is not "converted". >> >> If you declare the database column thusly: >> >> create table x(x); -- column x has no affinity >> >> then storing a double -0.0 in the column either via the statement >INSERT >> INTO X VALUES (-0.0); or through binding a double containing -0.0 >as a >> parameter to INSERT INTO X VALUES (?) results in the double -0.0 >being >> stored in the database and retrieved with column_double against the >query >> SELECT X FROM X; >> >> However, if you declare the database column thusly: >> >> create table x(x real); -- column x has real affinity >> >> then all manner of attempting to store -0.0, 0, 0.0 results in >> column_double retrieving 0.0 (stripping the sign) since what is >actually >> stored is the two's complement integer 0 which does not have a sign >(or >> rather, the sign is +ve). >> > >Thanks, that's an interesting wrinkle I might be able to use. > >Regards, >Donald Shepherd. > >> >___ >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] [SPAM?] Re: [EXTERNAL] Re: Should SQLite distinguish between +0.0 and -0.0 on output?
On Fri., 14 Jun. 2019, 7:43 am Keith Medcalf, wrote: > On Thursday, 13 June, 2019 15:21, Donald Shepherd < > donald.sheph...@gmail.com> wrote: > > >On Fri, 14 Jun 2019 at 7:11 am, Richard Hipp wrote: > > >> On 6/13/19, Donald Shepherd wrote: > > >>> Given there's been numerous comments to the effect that SQLite > >>> now supports -0.0 storing and retrieval other than printing, > >>> I'm curious which version this was implemented in as I wouldn't > >>> mind removing my custom code when we move to a SQLite version > >>> with this improvement. > > >> SQLite version 3.0.0 from 2004-06-17. > > >When I reported that testing on 3.8.7.2 stored -0.0 but retrieved > >0.0, your comment on this list was that "SQLite converts integer > >floating point values to actual integers for storage (because that > >takes up less space on disk) and then converts back to double upon > >retrieval. That round-trip would change -0.0 into +0.0." > > >I was wondering when this changed? > > >That doesn't seem like "supporting -0.0 storing and retrieval" since > >3.0.0.0 if it was previously being modified to another value during > >storage, losing the sign in the process. > > Actually, it depends on whether the database column has real affinity or > not. If it has no affinity then the value is not "converted". > > If you declare the database column thusly: > > create table x(x); -- column x has no affinity > > then storing a double -0.0 in the column either via the statement INSERT > INTO X VALUES (-0.0); or through binding a double containing -0.0 as a > parameter to INSERT INTO X VALUES (?) results in the double -0.0 being > stored in the database and retrieved with column_double against the query > SELECT X FROM X; > > However, if you declare the database column thusly: > > create table x(x real); -- column x has real affinity > > then all manner of attempting to store -0.0, 0, 0.0 results in > column_double retrieving 0.0 (stripping the sign) since what is actually > stored is the two's complement integer 0 which does not have a sign (or > rather, the sign is +ve). > Thanks, that's an interesting wrinkle I might be able to use. Regards, Donald Shepherd. > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [SPAM?] Re: [EXTERNAL] Re: Should SQLite distinguish between +0.0 and -0.0 on output?
On Thursday, 13 June, 2019 15:21, Donald Shepherd wrote: >On Fri, 14 Jun 2019 at 7:11 am, Richard Hipp wrote: >> On 6/13/19, Donald Shepherd wrote: >>> Given there's been numerous comments to the effect that SQLite >>> now supports -0.0 storing and retrieval other than printing, >>> I'm curious which version this was implemented in as I wouldn't >>> mind removing my custom code when we move to a SQLite version >>> with this improvement. >> SQLite version 3.0.0 from 2004-06-17. >When I reported that testing on 3.8.7.2 stored -0.0 but retrieved >0.0, your comment on this list was that "SQLite converts integer >floating point values to actual integers for storage (because that >takes up less space on disk) and then converts back to double upon >retrieval. That round-trip would change -0.0 into +0.0." >I was wondering when this changed? >That doesn't seem like "supporting -0.0 storing and retrieval" since >3.0.0.0 if it was previously being modified to another value during >storage, losing the sign in the process. Actually, it depends on whether the database column has real affinity or not. If it has no affinity then the value is not "converted". If you declare the database column thusly: create table x(x); -- column x has no affinity then storing a double -0.0 in the column either via the statement INSERT INTO X VALUES (-0.0); or through binding a double containing -0.0 as a parameter to INSERT INTO X VALUES (?) results in the double -0.0 being stored in the database and retrieved with column_double against the query SELECT X FROM X; However, if you declare the database column thusly: create table x(x real); -- column x has real affinity then all manner of attempting to store -0.0, 0, 0.0 results in column_double retrieving 0.0 (stripping the sign) since what is actually stored is the two's complement integer 0 which does not have a sign (or rather, the sign is +ve). -- 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] [SPAM?] Re: [EXTERNAL] Re: Should SQLite distinguish between +0.0 and -0.0 on output?
On 6/13/19, Donald Shepherd wrote: > > When I reported that testing on 3.8.7.2 stored -0.0 but retrieved 0.0, your > comment on this list was that "SQLite converts integer floating point > values to actual integers for storage (because that takes up less space on > disk) and then converts back to double upon retrieval. That round-trip > would change -0.0 into +0.0." I was wondering when this changed? You are right. I had forgotten about that point. This has not been changed even now, and so -0.0 does get truncated to +0.0 when it is stored in the database file. -- 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] [SPAM?] Re: [EXTERNAL] Re: Should SQLite distinguish between +0.0 and -0.0 on output?
On Fri, 14 Jun 2019 at 07:13, Simon Slavin wrote: > On 13 Jun 2019, at 10:01pm, Donald Shepherd > wrote: > > > Given there's been numerous comments to the effect that SQLite now > supports > > -0.0 storing and retrieval other than printing, I'm curious which version > > this was implemented in as I wouldn't mind removing my custom code when > we > > move to a SQLite version with this improvement. > > For auditing and probity reasons, I would store the values twice: once in > a BLOB and another time in a REAL. When you need to look at the value you > can choose which one to look at depending on why you're looking at it. > Thanks to the very useful data affinity rules and flexibility in SQLite we store a REAL if SQLite supports the value and a BLOB if it doesn't (off the top of my head, this is -0.0 and the NaN values - based off testing on 3.8.7.2 which we haven't revisited, hence my current curiousity), and the data type informs us as to whether we need to do a memory conversion from binary data to floating point on the way back out. Regards, Donald Shepherd. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [SPAM?] Re: [EXTERNAL] Re: Should SQLite distinguish between +0.0 and -0.0 on output?
On Fri, 14 Jun 2019 at 7:11 am, Richard Hipp wrote: > On 6/13/19, Donald Shepherd wrote: > > > > Given there's been numerous comments to the effect that SQLite now > supports > > -0.0 storing and retrieval other than printing, I'm curious which version > > this was implemented in as I wouldn't mind removing my custom code when > we > > move to a SQLite version with this improvement. > > SQLite version 3.0.0 from 2004-06-17. When I reported that testing on 3.8.7.2 stored -0.0 but retrieved 0.0, your comment on this list was that "SQLite converts integer floating point values to actual integers for storage (because that takes up less space on disk) and then converts back to double upon retrieval. That round-trip would change -0.0 into +0.0." I was wondering when this changed? That doesn't seem like "supporting -0.0 storing and retrieval" since 3.0.0.0 if it was previously being modified to another value during storage, losing the sign in the process. Regards, Donald Shepherd. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [SPAM?] Re: [EXTERNAL] Re: Should SQLite distinguish between +0.0 and -0.0 on output?
On 13 Jun 2019, at 10:01pm, Donald Shepherd wrote: > Given there's been numerous comments to the effect that SQLite now supports > -0.0 storing and retrieval other than printing, I'm curious which version > this was implemented in as I wouldn't mind removing my custom code when we > move to a SQLite version with this improvement. For auditing and probity reasons, I would store the values twice: once in a BLOB and another time in a REAL. When you need to look at the value you can choose which one to look at depending on why you're looking at it. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [SPAM?] Re: [EXTERNAL] Re: Should SQLite distinguish between +0.0 and -0.0 on output?
On Thursday, 13 June, 2019 15:01, Donald Shepherd wrote: >Given there's been numerous comments to the effect that SQLite now >supports -0.0 storing and retrieval other than printing, I'm curious >which version this was implemented in as I wouldn't mind removing my >custom code when we move to a SQLite version with this improvement. Which version of SQLite3 does NOT work properly in this respect? -- 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] [SPAM?] Re: [EXTERNAL] Re: Should SQLite distinguish between +0.0 and -0.0 on output?
On 6/13/19, Donald Shepherd wrote: > > Given there's been numerous comments to the effect that SQLite now supports > -0.0 storing and retrieval other than printing, I'm curious which version > this was implemented in as I wouldn't mind removing my custom code when we > move to a SQLite version with this improvement. SQLite version 3.0.0 from 2004-06-17. -- 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] [SPAM?] Re: [EXTERNAL] Re: Should SQLite distinguish between +0.0 and -0.0 on output?
On Fri, 14 Jun 2019 at 3:28 am, Doug wrote: > It occurs to me that if there are sqlite applications today requiring the > functionality of -0.0, they have implemented it so that it works for their > application. Perhaps, they accomplished that by adding a flag bit or by > some other means. > > So if you do nothing about -0.0, you will break no existing applications. > Granted, you are not adhering to IEEE-745. > > However, ... > > If you can find anyone who has implemented such an application (using > -0.0), you could find out how they implemented it. To make sqlite adhere to > IEEE-745, generate an sqlite application note suggesting possible ways an > application might implement the functionality. My application uses it as we are required to maintain a bit perfect copy of customer data. As such we had to store it as a BLOB with extra logic around converting it back to a C double on retrieval. Given there's been numerous comments to the effect that SQLite now supports -0.0 storing and retrieval other than printing, I'm curious which version this was implemented in as I wouldn't mind removing my custom code when we move to a SQLite version with this improvement. Regards, Donald Shepherd. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [SPAM?] Re: [EXTERNAL] Re: Should SQLite distinguish between +0.0 and -0.0 on output?
On Thursday, 13 June, 2019 11:28, Doug wrote: >It occurs to me that if there are sqlite applications today requiring >the functionality of -0.0, they have implemented it so that it works >for their application. Perhaps, they accomplished that by adding a >flag bit or by some other means. >So if you do nothing about -0.0, you will break no existing >applications. Granted, you are not adhering to IEEE-745. >However, ... >If you can find anyone who has implemented such an application (using >-0.0), you could find out how they implemented it. To make sqlite >adhere to IEEE-745, generate an sqlite application note suggesting >possible ways an application might implement the functionality. -0.0 is implemented and supported flawlessly except for TWO failings: - "printing" (and all conversions to text) does not output the sign - .dump processing does not preserve -0.0 on output The question is whether or not CONVERSION TO TEXT should preserve the sign. Since absolutely no one on the face of the planet who uses SQLite3 where a value of -0.0 is of any consequence would be converting floating point values yither and from TEXT representation (they would be using them directly via the sqlite3_bind_double and sqlite3_column_double interfaces) the question is really moot as far as users of SQLite3 depending on the value of -0.0 being preserved, because it is being preserved -- except across database .dump operations. The .dump issue needs to be addressed since the purpose of .dump is to dump SQL that will re-create the database. -inf and inf have already been handled, SQLite3 does not "do" NaN's, the only other thing that the .dump does not preserve is -0.0 simply because .dump does not output -0.0 as -0.0 (though it parses -0.0 on input just peachy). It might be nice for the CLI to output the sign bit as it would make debugging simpler (the standard SQLite3 CLI could be used to inspect tables). The drawback is that it will confuse people who do not understand floating point anyway. For those who depend on -0.0, they would not be using the CLI and conversion to text anyway. --- 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] [SPAM?] Re: [EXTERNAL] Re: Should SQLite distinguish between +0.0 and -0.0 on output?
It occurs to me that if there are sqlite applications today requiring the functionality of -0.0, they have implemented it so that it works for their application. Perhaps, they accomplished that by adding a flag bit or by some other means. So if you do nothing about -0.0, you will break no existing applications. Granted, you are not adhering to IEEE-745. However, ... If you can find anyone who has implemented such an application (using -0.0), you could find out how they implemented it. To make sqlite adhere to IEEE-745, generate an sqlite application note suggesting possible ways an application might implement the functionality. --Doug ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] json path escaping with double quote
Hello,
With the json1 extension, we can escape special characters like '['
from being interpreted as an array index by wrapping the path in
double quotes. But sometimes, it does not work:
sqlite> CREATE TABLE test (data TEXT);
sqlite> INSERT INTO test (data) VALUES ('{}');
sqlite> UPDATE test SET data = json_set(data, '$."equity_spot[at_andr]"', json(3
2));
sqlite> SELECT json_each.key, json_each.value FROM test, json_each(test.data);
"equity_spot[at_andr]"|32
sqlite> -- KO: expected equity_spot[at_andr]|32 but got
"equity_spot[at_andr]"|32
sqlite> DELETE FROM test;
sqlite> INSERT INTO test (data) VALUES ('{"equity_spot[at_andr]":34.3}');
sqlite> UPDATE test SET data = json_set(data, '$."equity_spot[at_andr]"', json(3
2));
sqlite> SELECT json_each.key, json_each.value FROM test, json_each(test.data);
equity_spot[at_andr]|32
sqlite> -- OK: no double quote
I use json_patch as a workaround.
Is this the expected behaviour ?
Thanks.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] Re: Should SQLite distinguish between +0.0 and -0.0 on output?
Perhaps this should be handled the same way as Inf and -Inf. For the purposes of conversion to text the value should be 0.0. However, for the purposes of .dump the actual -0.0 should be output just as Inf and -Inf are output as 1e999 and -1e999 respectively, since the purpose of .dump is to be able to re-create the database contents -- and this would require the least amount of changes ... sqlite> create table x(x); sqlite> insert into x values (-1e999),(-0.0),(0.0),(1e999); sqlite> select * from x; -Inf 0.0 0.0 Inf sqlite> .dump PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE x(x); INSERT INTO x VALUES(-1e999); INSERT INTO x VALUES(0.0); INSERT INTO x VALUES(0.0); INSERT INTO x VALUES(1e999); COMMIT; sqlite> --- 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 Doug Currie >Sent: Thursday, 13 June, 2019 09:36 >To: SQLite mailing list >Subject: Re: [sqlite] [SPAM?] Re: [EXTERNAL] Re: Should SQLite >distinguish between +0.0 and -0.0 on output? > >> >> I do not know if this is the result case in any of the programming >> languages, but in Mathematical terms that is just not true. >> > >The related IEEE 754 rules are described here: >https://en.wikipedia.org/wiki/Signed_zero > >e >___ >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] endianess/signed issue on OpenBSD/sparc64 ?
On Thu, Jun 13 2019, Richard Hipp wrote: > On 6/9/19, Landry Breuil wrote: >> >> this broke on sparc64 >>>select 298.2564151; >> -298.2564151 >> > > I cannot reproduce this (perhaps because I do not have access to a > sparc64 platform running OpenBSD) and do not have any good ideas about > what might be causing it. If you can get me a temporary login on such > a system, however, I will debug it for you. Send private email to the > address below. Thanks for the proposal, I have sent you a private mail. For the record: - I have built a bunch of older sqlite3 releases: 3.8.2, 3.16.2, 3.24.0, 3.25.1, 3.25.3 and 3.27.2. All of them give the same wrong result for "select 298.2564151;" - on OpenBSD/sparc64 both the base system and sqlite3 are built using gcc-4.2.1, an old gcc release licensed under GPLv2. Using gcc-8.3.0 to build sqlite3 doesn't magically fix the problem. -- jca | PGP : 0x1524E7EE / 5135 92C1 AD36 5293 2BDF DDCC 0DFA 74AE 1524 E7EE signature.asc Description: PGP signature ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Should SQLite distinguish between +0.0 and -0.0 on output?
On Wed, 12 Jun 2019 18:40:19 -0400 Richard Hipp wrote: > On 6/12/19, James K. Lowden wrote: > > what kind of computation > > would lead to a value in memory representing -0,0? > > 0.0/-1.0 Fine. I suspect the reason -0.0 has never cropped up as an issue in my experience is that -0.0 == 0.0. The existence of -0.0 never mattered because it was computationally irrelevant. I couldn't tell from your reference to Wolfram whether or not you considered the "negative zero is not math" to be dispositive (so to speak). If you're still considering rendering "-0.0" in the even the floating point unit happened to end up with "negative zero", are you also going to provide a way for users to detect the sign bit and "positivize" zero, such as through a SQLite function for signbit(3)? If so, to what end? IMO this whole discussion is a tempest in a teapot about angels dancing on the head of a pin. I have yet to see anyone offer any advantage of treating -.0.0 as anything other than 0. Far more important is integer division by zero. SQLite disguises it as NULL, making it undetectable and indistinguishable from genuinely missing information. --jkl ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [SPAM?] Re: [EXTERNAL] Re: Should SQLite distinguish between +0.0 and -0.0 on output?
On Thu, 13 Jun 2019 10:44:52 -0400 Doug Currie wrote: > > Except by the rules of IEEE (as I understand them) > > > > -0.0 < 0.0 is FALSE, so -0.0 is NOT "definitely left of true zero" > > > > Except that 0.0 is also an approximation to zero, not "true zero." Sure, because floating point numbers are approximations of real numbers. > Consider that 1/-0.0 is -inf whereas 1/0.0 is +int Consider too that both are mathematical nonsense. Just reading over the documentation, it seems it's also detectable nonsense: fenv(3) describes FE_DIVBYZERO. --jkl ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [SPAM?] Re: [EXTERNAL] Re: Should SQLite distinguish between +0.0 and -0.0 on output?
> > I do not know if this is the result case in any of the programming > languages, but in Mathematical terms that is just not true. > The related IEEE 754 rules are described here: https://en.wikipedia.org/wiki/Signed_zero e ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [SPAM?] Re: [EXTERNAL] Re: Should SQLite distinguish between +0.0 and -0.0 on output?
On Thu, Jun 13, 2019, 8:51 AM R Smith wrote: > On 2019/06/13 4:44 PM, Doug Currie wrote: > >> > >> Except by the rules of IEEE (as I understand them) > >> > >> -0.0 < 0.0 is FALSE, so -0.0 is NOT "definitely left of true zero" > >> > > Except that 0.0 is also an approximation to zero, not "true zero." > > > > Consider that 1/-0.0 is -inf whereas 1/0.0 is +int > > > I do not know if this is the result case in any of the programming > languages, but in Mathematical terms that is just not true. > > 1/0.0 --> Undefined, doesn't exist, cannot be computed, Should error > out. Anything returning +Inf or -Inf is plain wrong. > I posit the same holds true for 1/-0.0 > One of the features of the standard is "gradual underflow". I think of it as a limit like concept. Hence why the storage of negative zero has a place, why the computation of x/+-0.0 yields +-inf. It is more than just an artifact of the standard intended to solve certain computational problems. All of that is irrelevant, though. The standard works the way the standard works. SQLite has the option of either rendering -0.0 with or without a sign. Should the system be changed? I think that the possibility of breaking existing SQLite usage by changing the long established text output makes it ill advised to make such a change. Combined with the idea that sometimes SQLite will convert between fp & integer zero on storage, thus losing the sign, illustrates that there is too much history of how SQLite handles numbers already. Such a change would not harm me in any way I can perceive. I have not written any code that depends on -0.0, nor have I written code that depends on the textual conversion thereof. I just think that SQLite's history of maintaining backward compatibility by not gratuitously changing established functionality that some people might depend on, even when it seems like an improvement, should result in no change in the cast of -0.0 to text. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [SPAM?] Re: [EXTERNAL] Re: Should SQLite distinguish between +0.0 and -0.0 on output?
On 2019/06/13 4:44 PM, Doug Currie wrote: Except by the rules of IEEE (as I understand them) -0.0 < 0.0 is FALSE, so -0.0 is NOT "definitely left of true zero" Except that 0.0 is also an approximation to zero, not "true zero." Consider that 1/-0.0 is -inf whereas 1/0.0 is +int I do not know if this is the result case in any of the programming languages, but in Mathematical terms that is just not true. 1/0.0 --> Undefined, doesn't exist, cannot be computed, Should error out. Anything returning +Inf or -Inf is plain wrong. I posit the same holds true for 1/-0.0 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [SPAM?] Re: [EXTERNAL] Re: Should SQLite distinguish between +0.0 and -0.0 on output?
> > > Except by the rules of IEEE (as I understand them) > > -0.0 < 0.0 is FALSE, so -0.0 is NOT "definitely left of true zero" > Except that 0.0 is also an approximation to zero, not "true zero." Consider that 1/-0.0 is -inf whereas 1/0.0 is +int e ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] endianess/signed issue on OpenBSD/sparc64 ?
On 6/9/19, Landry Breuil wrote: > > this broke on sparc64 >>select 298.2564151; > -298.2564151 > I cannot reproduce this (perhaps because I do not have access to a sparc64 platform running OpenBSD) and do not have any good ideas about what might be causing it. If you can get me a temporary login on such a system, however, I will debug it for you. Send private email to the address below. -- 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] [SPAM?] Re: [EXTERNAL] Re: Should SQLite distinguish between +0.0 and -0.0 on output?
On 6/13/19 1:29 AM, Hick Gunter wrote: >> 2. Math. Negative zero is not a mathematical concept. > -0.0 is "too close to distinguish from, but definitely left of true zero", > which may be significant in some context > Except by the rules of IEEE (as I understand them) -0.0 < 0.0 is FALSE, so -0.0 is NOT "definitely left of true zero" -- Richard Damon ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Should SQLite distinguish between +0.0 and -0.0 on output?
On Wed, Jun 12, 2019 at 6:45 PM Richard Hipp wrote: > On 6/12/19, James K. Lowden wrote: > > 1. Prior art. I can't think of a single programming language that > > displays -0.0 without jumping through hoops. > > Prints -0.0 as "-0.0" or just "-0": glibc, Tcl, Python, Javascript > > Prints -0.0 as "0.0" or "0": PostgreSQL, MySQL, Oracle, SqlServer > Since I didn't know if Richard knew about NUMBER vs BINARY_DOUBLE in Oracle, the latter being supposed to store IEEE doubles, I tried, and I can't see to input a -0.0 in either using SQL*PLUS. I guess I would have to write a small test with OCI to know if at least like SQLite either is able to preserve the sign. But in terms of to-text rendering, I'm not getting a -0.0 back for sure, like Richard. And the SIGN() function does not show the numbers as negative, although there is some weirdness is that it returns a different result for NUMBER and BINARY_DOUBLE which I didn't investigate. FWIW :). --DD Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> create table testfp (fpnum number, fpieee binary_double); Table created. SQL> insert into testfp values (0.0, 0.0); 1 row created. SQL> insert into testfp values (-0.0, -0.0); 1 row created. SQL> select * from testfp; FPNUM FPIEEE -- -- 0 0 0 0 SQL> insert into testfp values (-0.0, cast(-0.0 as binary_double)); 1 row created. SQL> select * from testfp; FPNUM FPIEEE -- -- 0 0 0 0 0 0 SQL> select sign(fpnum), sign(fpieee) from testfp; SIGN(FPNUM) SIGN(FPIEEE) --- 01 01 01 SQL> insert into testfp values (-1.0, -1.0); 1 row created. SQL> select sign(fpnum), sign(fpieee) from testfp; SIGN(FPNUM) SIGN(FPIEEE) --- 01 01 01 -1 -1 SQL> insert into testfp values (-1.0*0, -1.0*0); 1 row created. SQL> select sign(fpnum), sign(fpieee) from testfp; SIGN(FPNUM) SIGN(FPIEEE) --- 01 01 01 -1 -1 01 SQL> select * from testfp 2 ; FPNUM FPIEEE -- -- 0 0 0 0 0 0 -1 -1.0E+000 0 0 SQL> ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
