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
|

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

Richard Hipp-3
IEEE754 floating point numbers have separate representations for +0.0
and -0.0.  As currently implemented, SQLite always display both
quantities as just "0.0".

Question:  Should SQLite be enhanced to show -0.0 as "-0.0"?  Or,
would that create unnecessary confusion?

--
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: Should SQLite distinguish between +0.0 and -0.0 on output?

Jose Isaias Cabrera-4

"...unnecessary confusion", IMHO.

________________________________
From: sqlite-users <[hidden email]> on behalf of Richard Hipp <[hidden email]>
Sent: Wednesday, June 12, 2019 09:35 AM
To: General Discussion of SQLite Database
Subject: [sqlite] Should SQLite distinguish between +0.0 and -0.0 on output?

IEEE754 floating point numbers have separate representations for +0.0
and -0.0.  As currently implemented, SQLite always display both
quantities as just "0.0".

Question:  Should SQLite be enhanced to show -0.0 as "-0.0"?  Or,
would that create unnecessary confusion?

--
D. Richard Hipp
[hidden email]
_______________________________________________
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: Should SQLite distinguish between +0.0 and -0.0 on output?

R Smith-2
In reply to this post by Richard Hipp-3
On 2019/06/12 3:35 PM, Richard Hipp wrote:
> IEEE754 floating point numbers have separate representations for +0.0
> and -0.0.  As currently implemented, SQLite always display both
> quantities as just "0.0".
>
> Question:  Should SQLite be enhanced to show -0.0 as "-0.0"?  Or,
> would that create unnecessary confusion?


1. Personally: I think all systems I'm involved in will be agnostic to
this change, and without confusion.

2. Generally: We see many questions on the forum about IEEE754, but
mostly it's to do with why X == (X-1).9999999nnnnn or why X ==
X.00000000001... etc.
I have seen people ask about the -0.0 vs 0.0 support, but have not seen
many people express confusion about it, or for the opposite in reply to it.

I will say that the go-to argument of people here (Devs included), when
confronted about the small epsilon offsets, is: "Because we IEEE754" - a
valid reason of course, but then it's probably best to adhere to it fully.

That said, it's been working like that flawlessly for years. Continuing
is not a horrible option.

I'd ask: Is it any faster or slower?  Does changing get rid of unneeded
code?  Is anything else gained from the change (other than proper
IEEE754 adherence)?

In my mind these matter more towards the decision than "will it confuse
some people" (at least with respect to this specific question).


Cheers,
Ryan

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

John McKown
In reply to this post by Richard Hipp-3
On Wed, Jun 12, 2019 at 8:35 AM Richard Hipp <[hidden email]> wrote:

> IEEE754 floating point numbers have separate representations for +0.0
> and -0.0.  As currently implemented, SQLite always display both
> quantities as just "0.0".
>
> Question:  Should SQLite be enhanced to show -0.0 as "-0.0"?  Or,
> would that create unnecessary confusion?
>

Is there any case where the display makes a difference? I cannot think of
any case where it is mathematically important. Actually the "0.0" is more
mathematically correct because zero is neither positive nor negative.

The IBM "mainframe" zSeries processors implement three floating points
formats: HFP (historic "hexadecimal Floating Point"), BFP (Binary Floating
Point -- IEEE754) and DFP (Decimal Floating Point -- IEEE754-2008). I am
not aware of any other architecture which does this.



>
> --
> D. Richard Hipp
> [hidden email]


--
This is clearly another case of too many mad scientists, and not enough
hunchbacks.


Maranatha! <><
John McKown
_______________________________________________
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?

Richard Hipp-3
In reply to this post by R Smith-2
On 6/12/19, R Smith <[hidden email]> wrote:
> Is it any faster or slower?

Adding the ability to show -0.0 slows down our floating-point
benchmark by 0.007% (74,800 CPU cycles out of 943,268,981).  For
comparison, the floating-point benchmark is about 17% faster than the
3.28.0 release, due to other unrelated enhancements.
--
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: Should SQLite distinguish between +0.0 and -0.0 on output?

Manuel Rigger
In reply to this post by John McKown
It could be surprising in the example below:

SELECT CAST('-0.0' AS NUMERIC); -- 0.0
SELECT CAST('0.0' AS NUMERIC); -- 0
SELECT CAST('+0.0' AS NUMERIC); -- 0

Best,
Manuel

On Wed, Jun 12, 2019 at 3:57 PM John McKown <[hidden email]>
wrote:

> On Wed, Jun 12, 2019 at 8:35 AM Richard Hipp <[hidden email]> wrote:
>
> > IEEE754 floating point numbers have separate representations for +0.0
> > and -0.0.  As currently implemented, SQLite always display both
> > quantities as just "0.0".
> >
> > Question:  Should SQLite be enhanced to show -0.0 as "-0.0"?  Or,
> > would that create unnecessary confusion?
> >
>
> Is there any case where the display makes a difference? I cannot think of
> any case where it is mathematically important. Actually the "0.0" is more
> mathematically correct because zero is neither positive nor negative.
>
> The IBM "mainframe" zSeries processors implement three floating points
> formats: HFP (historic "hexadecimal Floating Point"), BFP (Binary Floating
> Point -- IEEE754) and DFP (Decimal Floating Point -- IEEE754-2008). I am
> not aware of any other architecture which does this.
>
>
>
> >
> > --
> > D. Richard Hipp
> > [hidden email]
>
>
> --
> This is clearly another case of too many mad scientists, and not enough
> hunchbacks.
>
>
> Maranatha! <><
> John McKown
> _______________________________________________
> 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: Should SQLite distinguish between +0.0 and -0.0 on output?

Simon Slavin-3
In reply to this post by Richard Hipp-3
On 12 Jun 2019, at 2:35pm, Richard Hipp <[hidden email]> wrote:

> IEEE754 floating point numbers have separate representations for +0.0
> and -0.0.

Like other people I am concerned at confusion when various different parts of the engine try -0 == +0 or -0 < +0 .  I suspect that however you implement it it would lead to posts complaining that SQLite does it wrong.

Do you intend to model the other special values IEEE754 has ?  +ve and -ve infinity ?  Quiet and signalling Not A Numbers ?  If so, there might be some use to documenting that SQLite REALs are exactly IEEE754 and do everything they do.

If not, then the fact that IEEE754 is used internally to SQLite, as both a storage format and a math library, is an implementation detail and may change.  What might be useful instead is to define a new storage class called I754 which implements all the features of IEEE754 exactly, leaving the original REAL alone.

Just my 2c.
_______________________________________________
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?

Jonathan Brandmeyer
In reply to this post by Richard Hipp-3
IMO, when acting as a storage engine, SQLite should be good to the last
bit.  The sign of zero, the least significant bit of any mantissa, and the
payload bits of NaN should all be returned exactly as they were stored.

The history of almost-but-not-quite-IEEE754 arithmetic has been convergence
towards fully-IEEE754 pretty much everywhere.  People who are serious about
floating-point rely on its features.  Even though serious users are quite a
bit smaller than the community of casual floating-point users, the
maintenance burden of maintaining two sets of semantics means that you're
better off just supporting IEEE754.

For an SQL engine, the next-best-thing to strict binary IEEE754 is not
sloppy binary IEEE754, its probably strict decimal IEEE754.


On Wed, Jun 12, 2019 at 7:35 AM Richard Hipp <[hidden email]> wrote:

> IEEE754 floating point numbers have separate representations for +0.0
> and -0.0.  As currently implemented, SQLite always display both
> quantities as just "0.0".
>
> Question:  Should SQLite be enhanced to show -0.0 as "-0.0"?  Or,
> would that create unnecessary confusion?
>
> --
> D. Richard Hipp
> [hidden email]
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


--
Jonathan Brandmeyer
Vice President of Software Engineering
PlanetiQ
_______________________________________________
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?

Richard Hipp-3
On 6/12/19, Jonathan Brandmeyer <[hidden email]> wrote:
> IMO, when acting as a storage engine, SQLite should be good to the last
> bit.

That is already the case, and has been for 17 years.  The question at
hand is what should SQLite do when the application asks it to convert
a -0.0 value into text.  It is only the binary-to-text conversion
routine that is at question here.  If you are reading back your
database content using sqlite3_column_double(), you get back
bit-for-bit exactly what you put in.

--
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: Should SQLite distinguish between +0.0 and -0.0 on output?

R Smith-2
In reply to this post by Jonathan Brandmeyer
On 2019/06/12 4:39 PM, Jonathan Brandmeyer wrote:
> For an SQL engine, the next-best-thing to strict binary IEEE754 is not
> sloppy binary IEEE754, its probably strict decimal IEEE754.

I very much agree on this point, however, unlike the -0.0/+0.0 change,
changing to strict full IEEE754 conversion (with "-ve" etc.) WILL
actually break many things due to backward-incompatibility.

I tend to agree with Simon on making a new IEEE754 conversion for when
the need arise (maybe a sort of CAST target or collation rather than a
column "Type") and perhaps leaving REAL be (with simply clear docs on
how SQLIte's REAL works and diverges from IEEE754 in conversion). 
Especially since learning that, although the change is not slower by any
significant margin, it's certainly not faster.

To surmise the posts so far: If it doesn't give us full IEE754
conformance, it doesn't shorten the code-base, it's not mathematically
important, it's not faster, and it might be confusing...  Why change?.

(There may of course be reasons internal to the project or the devs
which I'm not privy to, the above is simply an opinion based on
currently publicly known details)

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

Keith Medcalf
In reply to this post by Richard Hipp-3
On Wednesday, 12 June, 2019 07:35, Richard Hipp <[hidden email]> wrote:

>IEEE754 floating point numbers have separate representations for +0.0
>and -0.0.  As currently implemented, SQLite always display both
>quantities as just "0.0".

>Question:  Should SQLite be enhanced to show -0.0 as "-0.0"?  Or,
>would that create unnecessary confusion?

By "on output" one assumes that you mean "when converted to text representation" and not that you are messing with the actual stored floating point number that is "input" via the sqlite3_bind_double and "output" via sqlite3_column_double.  

Therefore the answer should be dependant on whether or not TEXT input is parsed into the appropriate double format.  If the text input is converted into the corresponding IEEE754 format, then the output of the inverse conversion (double to text) should also present the same information notwithstanding the fact that +0.0 == -0.0.

For example, the statements:

select -0.0;
select cast(cast('-0.0' as real) as text);

both return a text representation '0.0'.  From this it is impossible to tell if the intermediate IEEE754 floating point value is -0.0 or +0.0.  

Testing reveals that the actual intermediate IEEE754 representation is in fact -0.0

Python 3.7.3 (v3.7.3:ef4ec6ed12, Mar 25 2019, 22:22:05) [MSC v.1916 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import apsw
>>> db = apsw.Connection('')
>>> for row in db.execute('select -0.0, 0.0, -0.0 == 0.0;'):
...  print(row, type(row._0), type(row._1), type(row._2))
...
Row(_0=-0.0, _1=0.0, _2=1) <class 'float'> <class 'float'> <class 'int'>

I would therefore submit that the conversion from double to text should also produce the same '-0.0' output for -0.0 IEEE754 floats.

---
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: 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 09:35:13 -0400
Richard Hipp <[hidden email]> wrote:

> Question:  Should SQLite be enhanced to show -0.0 as "-0.0"?  

No.  

1.  Prior art.  I can't think of a single programming language that
displays -0.0 without jumping through hoops.  

2.  Math.  Negative zero is not a mathematical concept.

3.  Utility.  There is none.  The user is only inconvenienced.  

Negative zero is an artifact of the IEEE 754, not a feature.  By
contrast, IEEE adopted 2s-complement signed integers specificially to
avoid negative zero.  That's evidently harder to do with floating
point. ;-)  

I see no advantage to exposing a technical mechanical artifact to the
user.

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

Simon Slavin-3
In reply to this post by R Smith-2
On 12 Jun 2019, at 4:35pm, R Smith <[hidden email]> wrote:

> (maybe a sort of CAST target or collation rather than a column "Type")

That's an interesting idea.  With a REAL value you get the same things when you print -0.0 and +0.0.  But if you CAST it to a new type of I754 then apply printf to that, you could get different text from the two values.
_______________________________________________
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?

Scott Robison-2
In reply to this post by James K. Lowden
On Wed, Jun 12, 2019, 10:02 AM James K. Lowden <[hidden email]>
wrote:

> On Wed, 12 Jun 2019 09:35:13 -0400
> Richard Hipp <[hidden email]> wrote:
>
> > Question:  Should SQLite be enhanced to show -0.0 as "-0.0"?
>
> No.
>
> 1.  Prior art.  I can't think of a single programming language that
> displays -0.0 without jumping through hoops.
>
> 2.  Math.  Negative zero is not a mathematical concept.
>
> 3.  Utility.  There is none.  The user is only inconvenienced.
>
> Negative zero is an artifact of the IEEE 754, not a feature.  By
> contrast, IEEE adopted 2s-complement signed integers specificially to
> avoid negative zero.  That's evidently harder to do with floating
> point. ;-)
>
> I see no advantage to exposing a technical mechanical artifact to the
> user.
>

Further, how much code might exist that depends on the current
functionality? Code that converts a real to a string then compares the
string value against 0.0.

If the decision is made to differentiate 0.0 with a prefixed sign, will all
positive values be likewise prefixed with + explicitly when converted to
string?

I think leaving it with the status quo is the best course of action.
_______________________________________________
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?

R Smith-2
In reply to this post by Simon Slavin-3

On 2019/06/12 6:04 PM, Simon Slavin wrote:
> On 12 Jun 2019, at 4:35pm, R Smith <[hidden email]> wrote:
>
>> (maybe a sort of CAST target or collation rather than a column "Type")
> That's an interesting idea.  With a REAL value you get the same things when you print -0.0 and +0.0.  But if you CAST it to a new type of I754 then apply printf to that, you could get different text from the two values.


Indeed - although James makes an important point, the -0.0 of IEEE754
isn't a useful feature, it's an artifact - a happenstance of an imposed
rule, not an answer to a mathematical concept needing to be
computerized. The CAST / Collation idea is still useful though for those
specifically interested in the real IEEE754 bit representation (as
Jonathan and Keith pointed out), which can even be improved to contain
the rest of the IEEE754 features as you've mentioned in the
conversion/text representation.


I now wonder if this is possible to implement as a simple User collation.


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

Keith Medcalf
In reply to this post by James K. Lowden

On Wednesday, 12 June, 2019 10:02, James K. Lowden <[hidden email]> wrote:

>1.  Prior art.  I can't think of a single programming language that
>displays -0.0 without jumping through hoops.

---//--- t.c ---//---
#include <stdio.h>

int main(int argc, char** argv)
{
    printf("%f %f\n", -0.0, 0.0);
}
---//--- t.c ---//---

Microsoft (R) C/C++ Optimizing Compiler Version 19.21.27702.2 for x64
>cl t.c
>t
-0.000000 0.000000

gcc version 8.1.0 (x86_64-win32-sjlj-rev0, Built by MinGW-W64 project
>gcc t.c
>a
-0.000000 0.000000

Python 3.7.3 (v3.7.3:ef4ec6ed12, Mar 25 2019, 22:22:05) [MSC v.1916 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> a = -0.0
>>> b = 0.0
>>> print(a,b)
-0.0 0.0

Really difficult hoops to jump through are they not?

---
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: Should SQLite distinguish between +0.0 and -0.0 on output?

Keith Medcalf

RUST appears to diddle, but then that is probably the result of the iron oxide coating:

>type src\main.rs
fn main() {
    println!("{} {}", -0.0, 0.0);
    println!("{:?} {:?}", -0.0, 0.0);
}
>cargo rr
   Compiling demo v0.1.0
   Finished release [optimized] target(s) in 0.44s
     Running `target\release\demo.exe`
0 0
-0.0 0.0

---
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 Keith Medcalf
>Sent: Wednesday, 12 June, 2019 10:28
>To: SQLite mailing list
>Subject: Re: [sqlite] Should SQLite distinguish between +0.0 and -0.0
>on output?
>
>
>On Wednesday, 12 June, 2019 10:02, James K. Lowden
><[hidden email]> wrote:
>
>>1.  Prior art.  I can't think of a single programming language that
>>displays -0.0 without jumping through hoops.
>
>---//--- t.c ---//---
>#include <stdio.h>
>
>int main(int argc, char** argv)
>{
>    printf("%f %f\n", -0.0, 0.0);
>}
>---//--- t.c ---//---
>
>Microsoft (R) C/C++ Optimizing Compiler Version 19.21.27702.2 for x64
>>cl t.c
>>t
>-0.000000 0.000000
>
>gcc version 8.1.0 (x86_64-win32-sjlj-rev0, Built by MinGW-W64 project
>>gcc t.c
>>a
>-0.000000 0.000000
>
>Python 3.7.3 (v3.7.3:ef4ec6ed12, Mar 25 2019, 22:22:05) [MSC v.1916
>64 bit (AMD64)] on win32
>Type "help", "copyright", "credits" or "license" for more
>information.
>>>> a = -0.0
>>>> b = 0.0
>>>> print(a,b)
>-0.0 0.0
>
>Really difficult hoops to jump through are they not?
>
>---
>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



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

Richard Hipp-3
In reply to this post by James K. Lowden
On 6/12/19, James K. Lowden <[hidden email]> 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

So in my tests, programming languages do distinguish between +0.0 and
-0.0, but database engines do not.
--
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: Should SQLite distinguish between +0.0 and -0.0 on output?

Thomas Kurz
In reply to this post by Richard Hipp-3
In the historical documents of Sqlite4, there has been a note about "distinguish whether a number is exact or approximate" (or similar). Imho this information would be more useful than distinguishing between +/- 0.0.

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

Thomas Kurz
In reply to this post by Jonathan Brandmeyer
> For an SQL engine, the next-best-thing to strict binary IEEE754 is not
sloppy binary IEEE754, its probably strict decimal IEEE754.

That would be a *really great* improvement!

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
1234