Should SQLite distinguish between +0.0 and -0.0 on output?

classic Classic list List threaded Threaded
67 messages Options
1234
Reply | Threaded
Open this post in threaded view
|

Re: [SPAM?] Re: [EXTERNAL] Re: Should SQLite distinguish between +0.0 and -0.0 on output?

Scott Robison-2
On Thu, Jun 13, 2019, 8:51 AM R Smith <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [SPAM?] Re: [EXTERNAL] Re: Should SQLite distinguish between +0.0 and -0.0 on output?

Doug Currie-2
In reply to this post by R Smith-2
>
> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [SPAM?] Re: [EXTERNAL] Re: Should SQLite distinguish between +0.0 and -0.0 on output?

James K. Lowden
In reply to this post by Doug Currie-2
On Thu, 13 Jun 2019 10:44:52 -0400
Doug Currie <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Should SQLite distinguish between +0.0 and -0.0 on output?

James K. Lowden
In reply to this post by Richard Hipp-3
On Wed, 12 Jun 2019 18:40:19 -0400
Richard Hipp <[hidden email]> wrote:

> On 6/12/19, James K. Lowden <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Re: Should SQLite distinguish between +0.0 and -0.0 on output?

Keith Medcalf
In reply to this post by Richard Hipp-3

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-
>[hidden email]] 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
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [SPAM?] Re: [EXTERNAL] Re: Should SQLite distinguish between +0.0 and -0.0 on output?

Doug
In reply to this post by James K. Lowden
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [SPAM?] Re: [EXTERNAL] Re: Should SQLite distinguish between +0.0 and -0.0 on output?

Keith Medcalf

On Thursday, 13 June, 2019 11:28, Doug <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [SPAM?] Re: [EXTERNAL] Re: Should SQLite distinguish between +0.0 and -0.0 on output?

Donald Shepherd
In reply to this post by Doug
On Fri, 14 Jun 2019 at 3:28 am, Doug <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [SPAM?] Re: [EXTERNAL] Re: Should SQLite distinguish between +0.0 and -0.0 on output?

Richard Hipp-3
On 6/13/19, Donald Shepherd <[hidden email]> 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
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [SPAM?] Re: [EXTERNAL] Re: Should SQLite distinguish between +0.0 and -0.0 on output?

Keith Medcalf
In reply to this post by Donald Shepherd
On Thursday, 13 June, 2019 15:01, Donald Shepherd <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [SPAM?] Re: [EXTERNAL] Re: Should SQLite distinguish between +0.0 and -0.0 on output?

Simon Slavin-3
In reply to this post by Donald Shepherd
On 13 Jun 2019, at 10:01pm, Donald Shepherd <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [SPAM?] Re: [EXTERNAL] Re: Should SQLite distinguish between +0.0 and -0.0 on output?

Donald Shepherd
In reply to this post by Richard Hipp-3
On Fri, 14 Jun 2019 at 7:11 am, Richard Hipp <[hidden email]> wrote:

> On 6/13/19, Donald Shepherd <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [SPAM?] Re: [EXTERNAL] Re: Should SQLite distinguish between +0.0 and -0.0 on output?

Donald Shepherd
In reply to this post by Simon Slavin-3
On Fri, 14 Jun 2019 at 07:13, Simon Slavin <[hidden email]> wrote:

> On 13 Jun 2019, at 10:01pm, Donald Shepherd <[hidden email]>
> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [SPAM?] Re: [EXTERNAL] Re: Should SQLite distinguish between +0.0 and -0.0 on output?

Richard Hipp-3
In reply to this post by Donald Shepherd
On 6/13/19, Donald Shepherd <[hidden email]> 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
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [SPAM?] Re: [EXTERNAL] Re: Should SQLite distinguish between +0.0 and -0.0 on output?

Keith Medcalf
In reply to this post by Donald Shepherd
On Thursday, 13 June, 2019 15:21, Donald Shepherd <[hidden email]> wrote:

>On Fri, 14 Jun 2019 at 7:11 am, Richard Hipp <[hidden email]> wrote:

>> On 6/13/19, Donald Shepherd <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [SPAM?] Re: [EXTERNAL] Re: Should SQLite distinguish between +0.0 and -0.0 on output?

Donald Shepherd
On Fri., 14 Jun. 2019, 7:43 am Keith Medcalf, <[hidden email]> wrote:

> On Thursday, 13 June, 2019 15:21, Donald Shepherd <
> [hidden email]> wrote:
>
> >On Fri, 14 Jun 2019 at 7:11 am, Richard Hipp <[hidden email]> wrote:
>
> >> On 6/13/19, Donald Shepherd <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [SPAM?] Re: [EXTERNAL] Re: Should SQLite distinguish between +0.0 and -0.0 on output?

Keith Medcalf

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-
>[hidden email]] 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, <[hidden email]>
>wrote:
>
>> On Thursday, 13 June, 2019 15:21, Donald Shepherd <
>> [hidden email]> wrote:
>>
>> >On Fri, 14 Jun 2019 at 7:11 am, Richard Hipp <[hidden email]>
>wrote:
>>
>> >> On 6/13/19, Donald Shepherd <[hidden email]> 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
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [SPAM?] Re: [EXTERNAL] Re: Should SQLite distinguish between +0.0 and -0.0 on output?

Simon Slavin-3
On 13 Jun 2019, at 11:08pm, Keith Medcalf <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [SPAM?] Re: [EXTERNAL] Re: Should SQLite distinguish between +0.0 and -0.0 on output?

Olivier Mascia
In reply to this post by Richard Hipp-3
> Le 13 juin 2019 à 23:29, Richard Hipp <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [SPAM?] Re: [EXTERNAL] Re: Should SQLite distinguish between +0.0 and -0.0 on output?

Richard Damon
In reply to this post by R Smith-2
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
1234