Re: [sqlite] Should SQLite distinguish between +0.0 and -0.0 on output?
> On Jun 12, 2019, at 8:28 AM, Richard Hipp wrote: > > 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. IMHO, any application code that outsources numeric/text conversions to SQLite must not be serious about numerics. :) Or in other words, anyone who's far enough down the numerics rabbit-hole to care about the difference between +0.0 and -0.0 is going to want to do the formatting themselves, to make sure that other details like significant figures and rounding are done to their standards. —Jens ___ 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?
Maybe the core pb under the question asked here is this prior question : from the standard, is it possible to consider 0.0 as an imprecise representation of 0 ? (if yes, signing 0.0 seems logical to me) Regards, -jm --- L'absence de virus dans ce courrier électronique a été vérifiée par le logiciel antivirus Avast. https://www.avast.com/antivirus ___ 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 2019-06-12 6:35 a.m., 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? I would say, either you support IEEE754 floats fully to the standard, or you don't pretend to support them and just say you have floats without mentioning IEEE754. Also I say that distinguishing -0.0 and 0.0 is good for those that need to know and harmless to those that don't. -- Darren Duncan ___ 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] 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
Re: [sqlite] Should SQLite distinguish between +0.0 and -0.0 on output?
Regarding additional uses for the sign of zero: Branch Cuts for Complex Elementary Functions, or Much Ado About Nothing's Sign Bit in The State of the Art in Numerical Analysis, (eds. Iserles and Powell), Clarendon Press, Oxford, 1987. https://people.freebsd.org/~das/kahan86branch.pdf On Wed, Jun 12, 2019 at 4:40 PM 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 > > -- > 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 -- Jonathan Brandmeyer Vice President of Software Engineering PlanetiQ ___ 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 12:02 PM David Raymond wrote: > https://www.sqlite.org/fileformat2.html#record_format > > The storage type of each record is given by an integer. And in the current > format, all non-negative integers are used. > Ouch. Yes, an additional data type was closer to what I had in mind. > -Original Message- > From: sqlite-users On > Behalf Of Thomas Kurz > Sent: Wednesday, June 12, 2019 1:05 PM > To: SQLite mailing list > Subject: Re: [sqlite] Should SQLite distinguish between +0.0 and -0.0 on > output? > > > It would also be a file format change, rendering about 1e12 existing > database files obsolete. > ___ 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 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 -- 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] Should SQLite distinguish between +0.0 and -0.0 on output?
On Thu, 13 Jun 2019 at 01:28, Richard Hipp wrote:
> On 6/12/19, Jonathan Brandmeyer 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.
>
Strictly speaking this isn't true - if using the C interface to pass in
-0.0 it used to get returned as 0.0 due to the optimization where
"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." (2015) That may
have changed in recent years as I haven't explicitly tested it once I
worked around this behaviour by storing some double values as BLOBs.
Similarly passing in a bit representation of NaNs will not get you
bit-for-bit exactly what you put in, as SQLite discards it on the return
trip (also from 2015, though this code is still present so I assume the
behaviour is the same):
sqlite3_bind_double calls sqlite3VdbeMemSetDouble which has a specific
check against NaN. My assumption is that this is what results in NaNs not
round tripping and instead coming back out as SQLITE_NULL:
SQLITE_PRIVATE void sqlite3VdbeMemSetDouble(Mem *pMem, double val){
sqlite3VdbeMemSetNull(pMem);
if( !sqlite3IsNaN(val) ){
pMem->u.r = val;
pMem->flags = MEM_Real;
}
}
___
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 12 Jun 2019, at 10:28pm, James K. Lowden wrote: > what kind of computation > would lead to a value in memory representing -0,0? Here's the classic answer. It's not very impressive or convincing because it just kicks the can further down the road. Suppose you have a system like IEEE754 which represents positive infinity and negative infinity as two different values. At some point you calculate a a <-- 20.0 / c where c is zero. The answer is positive or negative infinity depending on whether c is positive or negative zero. Now, you could answer that they're both infinity and the answer doesn't matter because you can't calculate with infinity. But suppose later you do IF a > 4 THEN … or z = a + [-ve infinity] Keeping different values for the two zeros may allow you to answer these questions correctly. Without that you cannot know the answer. This is one of the reasons I asked whether SQLite was going to distinguish between +inf and -inf, and have NaN values. So if SQLite already shows +inf and -inf differently as text, it should do the same thing for the two zeros. ___ 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 12:57:22 -0400 Richard Hipp wrote: > On 6/12/19, Thomas Kurz wrote: > >> 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! > > It would also be a file format change, rendering about 1e12 existing > database files obsolete. Is that necessarily the case? Could blob types be "overloaded" somehow, such that existing code sees a blob and new code recognizes said blob as decimal numeric? For example, if the first two bytes of the expansion area of the database header (at offset 72) were 0x0bcd (say), then record type 44 could represent a 128-bit decimal encoding [16 bytes = (44 -12)/2]. Existing programs wouldn't examine the expansion area, and would interpret the field as a blob. New programs would interpret type 44 as decimal or blob, depending on the CREATE TABLE definition. --jkl ___ 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 10:28:20 -0600
"Keith Medcalf" wrote:
> 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?
$ printf "%f\n" -0.0
-0.00
A poor choice of words. What I meant was, what kind of computation
would lead to a value in memory representing -0,0? I don't remember
ever coming across one.
Just because we can print -0.0 doesn't mean it's meaningful.
#include
int main() {
if( -0.0 == 0.0 ) {
printf("all zeros are zero\n");
return 0;
}
printf("minus zero is real\n");
return 1;
}
make negzero && ./negzero
cc negzero.c -o negzero
all zeros are zero
--jkl
___
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 12 Jun 2019, at 9:23pm, Jean-Marie CUAZ wrote: > A possible solution could be a new function, > - something like abszero() or trimzero() - acting only on +/- 0.0 Another would be a PRAGMA which sets whether the two values are converted to text iodentically or differently. ___ 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?
> 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. A possible solution could be a new function, - something like abszero() or trimzero() - acting only on +/- 0.0 and suppressing any sign before the value, combined with the new rule that binary-to-text conversion retrieve data exactly as it is stored (and maybe improving hosts langage interoperability). This could permit any desired behaviour. Personally, if I want to do binary-to-text conversion of numbers, it is my duty to consider there could be some "artifacts" to deal with, resulting from the representation standard. If binary-to-text conversion preserves any sign before 0.0 value, it might conciliate different intents to permit (for example) : CAST(trimzero(somecolumn) AS TEXT) if no sign is wanted for textual representation of 0.0 (don't kwow if it's a so good solution however) Best regards -jm --- L'absence de virus dans ce courrier électronique a été vérifiée par le logiciel antivirus Avast. https://www.avast.com/antivirus ___ 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?
https://www.sqlite.org/fileformat2.html#record_format The storage type of each record is given by an integer. And in the current format, all non-negative integers are used. To me of course that begs the question: Are negative serial types an option? That would of course mean a full 9 bytes per field just to hold the type (it's a varint) so it's an annoying overhead, but is it an option? Versions earlier than the one that implements this wouldn't be able to read it. But then we've already had additions like "without rowid" where any old version of the library isn't going be able to understand a new database with a without rowid table. And if a new database doesn't use the new negative serial types then the resulting file is still perfectly readable by older versions. I suppose the issue though is that rules are already in place for determining the affinity of a column and those rules currently give a value for , so if you create a new type of "decimal(a, b)" then an old version, instead of saying "I don't know what that is" will say "ok, that's numeric and I internally store it as a float" and give bad results instead of no results. I suppose then you could then expand on the "without rowid" model, and designate new features at the end of the create table text so that old versions don't they know what to do and would give an error... create table foo (field1 numeric(5, 2) primary key) without rowid with numeric with someNewFeature; An old database where someone had already declared something as "decimal(5,2)" wouldn't have the "with decimal" in the create table text, so you could still keep the historical affinity and not mix it up with something intended for the new "decimal" type. ...but that gets ugly and complicated quick. Anyway, sorry for my rambling, I'll go hide in a corner now. -Original Message- From: sqlite-users On Behalf Of Thomas Kurz Sent: Wednesday, June 12, 2019 1:05 PM To: SQLite mailing list Subject: Re: [sqlite] Should SQLite distinguish between +0.0 and -0.0 on output? > It would also be a file format change, rendering about 1e12 existing database files obsolete. Maybe, but maybe there could be some clever implementation which doesn't break compatibility. I don't know about the exact internals of how SQlite stores values in the file. But I think there must be some identifier that tells whether a value is binary, integer, or float. Wouldn't it be possible to store both values, binary float and decimal float, in such a way that older versions would just read the binary float and ignore the additional data? Then, newer versions could read either, according on whether PRAGMA DECIMAL_MATH=TRUE is set or not. Just an idea, don't know whether this would be feasible or not. ___ 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] Should SQLite distinguish between +0.0 and -0.0 on output?
James K. Lowden wrote: > On Wed, 12 Jun 2019 09:35:13 -0400 > Richard Hipp 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. I disagree. I think most languages distinguish between +0.0 and -0.0, +inf, -inf and Nan. At least c, c++, Java, Tcl and probably many others have +0.0 and -0.0. Even my text editor Vim knows about +0.0 and -0.0, +inf, -inf and NaN. In Vim: :echo 1.0 / -0.0 -inf :echo 1.0 / 0.0 inf In tclsh: % expr 1.0 / -0.0 -Inf % expr 1.0 / 0.0 Inf > 2. Math. Negative zero is not a mathematical concept. It's about IEEE 754 which is widely used, and not about math i.e. real numbers. > 3. Utility. There is none. The user is only inconvenienced. There can be uses for it. +0.0 or -0.0 can result in different outcomes when doing intermediate computations. If SQLite deviates from IEEE 754, it's likely to cause issues. I think that a database should be generic enough and not assume that users don't need -0.0. SQLite is written in C and all all C functions which use double already understand +0.0 and -0.0. So I assume that there is hardly any overhead in supporting -0.0 in SQLite. Regards Dominique ___ 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?
> It would also be a file format change, rendering about 1e12 existing database files obsolete. Maybe, but maybe there could be some clever implementation which doesn't break compatibility. I don't know about the exact internals of how SQlite stores values in the file. But I think there must be some identifier that tells whether a value is binary, integer, or float. Wouldn't it be possible to store both values, binary float and decimal float, in such a way that older versions would just read the binary float and ignore the additional data? Then, newer versions could read either, according on whether PRAGMA DECIMAL_MATH=TRUE is set or not. Just an idea, don't know whether this would be feasible or not. ___ 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 Jun 12, 2019, at 10:02 AM, James K. Lowden wrote: > > On Wed, 12 Jun 2019 09:35:13 -0400 > Richard Hipp wrote: > >> Question: Should SQLite be enhanced to show -0.0 as "-0.0"? > > 2. Math. Negative zero is not a mathematical concept. The best kind of correct: http://mathworld.wolfram.com/Zero.html ___ 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 Jun 12, 2019, at 10:45 AM, 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
Chrome’s JS engine (V8, shared with Node.js) does it both ways, depending on
context:
> a = -0.0;
-0
> console.log(a)
-0
> JSON.stringify(a)
"0”
> JSON.stringify( { a: a} )
"{"a":0}”
> alert(a)
Says “0”
Two different versions of Perl (5.16.3 on CentOS 7 and 5.18.4 on macOS 10.14.5)
prints “0” via all three paths I’ve tried: print(), say(), and Data::Dumper.
Clang on macOS agrees with your glibc result, for both printf() and cout.
___
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 6/12/19, Thomas Kurz wrote: >> 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! It would also be a file format change, rendering about 1e12 existing database files obsolete. -- 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] Should SQLite distinguish between +0.0 and -0.0 on output?
> 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 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?
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 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 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 So in my tests, programming languages do distinguish between +0.0 and -0.0, but database engines do not. -- 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] Should SQLite distinguish between +0.0 and -0.0 on output?
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-
>boun...@mailinglists.sqlite.org] 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
> wrote:
>
>>1. Prior art. I can't think of a single programming language that
>>displays -0.0 without jumping through hoops.
>
>---//--- t.c ---//---
>#include
>
>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.00 0.00
>
>gcc version 8.1.0 (x86_64-win32-sjlj-rev0, Built by MinGW-W64 project
>>gcc t.c
>>a
>-0.00 0.00
>
>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
>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] Should SQLite distinguish between +0.0 and -0.0 on output?
On Wednesday, 12 June, 2019 10:02, James K. Lowden
wrote:
>1. Prior art. I can't think of a single programming language that
>displays -0.0 without jumping through hoops.
---//--- t.c ---//---
#include
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.00 0.00
gcc version 8.1.0 (x86_64-win32-sjlj-rev0, Built by MinGW-W64 project
>gcc t.c
>a
-0.00 0.00
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
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 2019/06/12 6:04 PM, Simon Slavin wrote: On 12 Jun 2019, at 4:35pm, R Smith 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 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, 10:02 AM James K. Lowden wrote: > On Wed, 12 Jun 2019 09:35:13 -0400 > Richard Hipp 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 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 12 Jun 2019, at 4:35pm, R Smith 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 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 09:35:13 -0400 Richard Hipp 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 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 Wednesday, 12 June, 2019 07:35, 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?
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)
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
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 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 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 6/12/19, Jonathan Brandmeyer 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 d...@sqlite.org ___ 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?
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 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 > d...@sqlite.org > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- Jonathan Brandmeyer Vice President of Software Engineering PlanetiQ ___ 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 12 Jun 2019, at 2:35pm, Richard Hipp 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 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?
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
wrote:
> On Wed, Jun 12, 2019 at 8:35 AM 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?
> >
>
> 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
> > d...@sqlite.org
>
>
> --
> This is clearly another case of too many mad scientists, and not enough
> hunchbacks.
>
>
> Maranatha! <><
> John McKown
> ___
> 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] Should SQLite distinguish between +0.0 and -0.0 on output?
On 6/12/19, R Smith 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 d...@sqlite.org ___ 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 8:35 AM 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? > 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 > d...@sqlite.org -- This is clearly another case of too many mad scientists, and not enough hunchbacks. Maranatha! <>< John McKown ___ 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 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).999n or why X == X.001... 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 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?
"...unnecessary confusion", IMHO. From: sqlite-users on behalf of Richard Hipp 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 d...@sqlite.org ___ 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
[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 d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
