Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

2011-06-07 Thread Sidney Cadot
Hi Jay >  No, this is basic SQL order of operations. You are right, that first approach I tried was definitely a brainfart. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

2011-06-07 Thread Nico Williams
On Tue, Jun 7, 2011 at 4:36 AM, Jean-Christophe Deschamps wrote: > Now, please try this: You miss the point. Not every app requires extended precision. But just because you don't require extended precision doesn't mean you can't use FP at all. It depends on the app. Nico -- __

Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

2011-06-07 Thread Jay A. Kreibich
On Tue, Jun 07, 2011 at 05:38:09PM +0200, Sidney Cadot scratched on the wall: > > Please someone show me what is the correct value of this avg() in practice. > SELECT AVG(data) FROM TryAvg ORDER BY ABS(data) DESC; > > But apparently, SQLite ignores the ORDER BY clause because of the > AVG() funct

Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

2011-06-07 Thread Sidney Cadot
> Please someone show me what is the correct value of this avg() in practice. There are a number of answers to this. To the level of precision that you specified, all answer are completely fine; the error is, in all cases, very small relative to the variance of your input data. It is an interest

Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

2011-06-07 Thread Sidney Cadot
Hi Jay, > One should never assume a database uses IEEE 754, so one should never > assume it uses similar semantics. One should not assume it unless it is documented, of course. Postgres, for example, half-heartedly embraces IEEE-754 'on platforms that use it' (see section 8.1.3 of its manual).

Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

2011-06-07 Thread Sidney Cadot
On Jun 6, 2011, at 21:55, Jean-Christophe Deschamps wrote: > You have a DOUBLE column where you need to store NaN? Go ahead and > store 'NaN' in offending rows. This cannot be done. They will be turned into NULL. Sidney ___ sqlite-users mailing lis

Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

2011-06-07 Thread Jean-Christophe Deschamps
>Sure, if you're just computing average() then you'll not get any NaNs. NaNs, NO (if we don't have NaNs in the set) but issues, YES. It all depends. No one knows. You don't even know what you're computing exactly. SQL interpret avg() as "take this data as a set, sum up these numerical value

Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

2011-06-06 Thread Jay A. Kreibich
On Mon, Jun 06, 2011 at 11:27:26PM +0200, Sidney Cadot scratched on the wall: > Hi Jay, > > > One should never assume a database uses IEEE 754, so one should never > > assume it uses similar semantics. > > One should not assume it unless it is documented, of course. Postgres, > for example, half-

Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

2011-06-06 Thread Nico Williams
On Mon, Jun 6, 2011 at 6:28 PM, Simon Slavin wrote: > I've rethought my earlier position.  This re-think is the result of the SQL > standard being incompatible with the IEEE standard.  If you want to do IEEE > arithmetic, do it in your own software, and use SQL just for retrieving the > numbers

Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

2011-06-06 Thread Simon Slavin
On 7 Jun 2011, at 12:01am, Nico Williams wrote: > On Mon, Jun 6, 2011 at 4:58 PM, Jean-Christophe Deschamps > wrote: >> Look at a FP-intensive product like Spatialite (SQLite-based). You'd >> probably agree it performs much more complex tasks than average, mean >> squares and such. >> I'd be ve

Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

2011-06-06 Thread Nico Williams
On Mon, Jun 6, 2011 at 4:58 PM, Jean-Christophe Deschamps wrote: > Look at a FP-intensive product like Spatialite (SQLite-based).  You'd > probably agree it performs much more complex tasks than average, mean > squares and such. > I'd be very surprised if it used NaN representations! Sure, if you

Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

2011-06-06 Thread Sidney Cadot
>> > You have a DOUBLE column where you need to store NaN? ?Go ahead and >> > store 'NaN' in offending rows. >> >> You mean, as a string? > >  No, by binding the raw value using the C interfaces as any >  respectable program would do. But then I'd lose the ability to actually use those values in c

Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

2011-06-06 Thread Jean-Christophe Deschamps
Nico, >There is one thing that makes numerical support in RDBMSes important: >aggregate functions. > >Aggregate functions are critical because they allow one to do much >analysis at the data source, instead of having to transport it >elsewhere for analysis. I agree with you, totally. Read me aga

Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

2011-06-06 Thread Jay A. Kreibich
On Mon, Jun 06, 2011 at 11:33:35PM +0200, Sidney Cadot scratched on the wall: > On Mon, Jun 6, 2011 at 9:55 PM, Jean-Christophe Deschamps > wrote: > > > You have a DOUBLE column where you need to store NaN? ?Go ahead and > > store 'NaN' in offending rows. > > You mean, as a string? No, by bin

Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

2011-06-06 Thread Sidney Cadot
> Given that there are > many, many SQLite3 applications, it is really not possible to say, > with a straight face anyways, that no applications would break. That is true. I would certainly not advocate changing the default behavior. However the 'once we make a mistake, we can't fix it' idea cann

Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

2011-06-06 Thread Sidney Cadot
On Mon, Jun 6, 2011 at 9:55 PM, Jean-Christophe Deschamps wrote: > You have a DOUBLE column where you need to store NaN?  Go ahead and > store 'NaN' in offending rows. You mean, as a string? That's rather a dirty hack. Also, it doesn't work as it should: sqlite> SELECT 1.0 + 'NaN'; 1.0 ___

Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

2011-06-06 Thread Nico Williams
On Mon, Jun 6, 2011 at 4:27 PM, Sidney Cadot wrote: >> If you want bare metal IEEE 754 for your scientific computing >> application, then you might want to rethink doing your math operations >> in a data storage system. > > You are making it sound as if proper support for IEEE-754 types would > op

Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

2011-06-06 Thread Sidney Cadot
Hi Jay, > One should never assume a database uses IEEE 754, so one should never > assume it uses similar semantics. One should not assume it unless it is documented, of course. Postgres, for example, half-heartedly embraces IEEE-754 'on platforms that use it' (see section 8.1.3 of its manual). It

Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

2011-06-06 Thread Scott Hess
On Mon, Jun 6, 2011 at 1:46 PM, Nico Williams wrote: > I can see two ways to add proper IEEE754 support in a > backwards-compatible way: a) via a pragma to enable raising exceptions > or returning +-inf/NaNs, b) by adding a callback via which to report > such exceptions, with exceptions raised onl

Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

2011-06-06 Thread Nico Williams
On Mon, Jun 6, 2011 at 2:55 PM, Jean-Christophe Deschamps wrote: > Allow me to add a humble bit to what Jay just posted. > > SQLite, as well as most other RDBMS around, allow you to perform FP > calculations in SQL statements.  I assume no-one imagines an extended > FP fine-grain support of hundre

Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

2011-06-06 Thread Jean-Christophe Deschamps
Allow me to add a humble bit to what Jay just posted. SQLite, as well as most other RDBMS around, allow you to perform FP calculations in SQL statements. I assume no-one imagines an extended FP fine-grain support of hundreds of computation options and status reporting be part of SQL or SQLite.

Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

2011-06-06 Thread Jay A. Kreibich
On Mon, Jun 06, 2011 at 05:36:03PM +0100, Simon Slavin scratched on the wall: > It's just a shame that the SQL and IEEE standards are mutually incompatible. Yes and no. First off, I would point out that the SQL standards are *significantly* older than the IEEE 754 standard. Languages that

Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

2011-06-06 Thread Black, Michael (IS)
behalf of Simon Slavin [slav...@bigfraud.org] Sent: Monday, June 06, 2011 11:36 AM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite? On 6 Jun 2011, at 5:30pm, Gabor Grothendieck wrote: > I and others use SQLite from R and R its

Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

2011-06-06 Thread Simon Slavin
On 6 Jun 2011, at 5:30pm, Gabor Grothendieck wrote: > I and others use SQLite from R and R itself gives Inf, -Inf and NaN > for 1/0, -1/0 and 0/0 respectively so it would reduce the differences > between the database and R if it worked in the same way. Perhaps an > option could control this beha

Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

2011-06-06 Thread Gabor Grothendieck
On Mon, Jun 6, 2011 at 11:54 AM, Richard Hipp wrote: > On Mon, Jun 6, 2011 at 11:44 AM, Sidney Cadot wrote: > >> >> Would it be useful to open a ticket on this issue, or will it never be >> changed e.g. for fear of breaking backward compatibility? >> > > There are approx 2 billion legacy apps in

Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

2011-06-06 Thread Richard Hipp
On Mon, Jun 6, 2011 at 11:44 AM, Sidney Cadot wrote: > > Would it be useful to open a ticket on this issue, or will it never be > changed e.g. for fear of breaking backward compatibility? > There are approx 2 billion legacy apps in the wild that use SQLite. Not breaking things is very important

Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

2011-06-06 Thread Sidney Cadot
> Ah.  In that case, I /would/ argue that this is bad, and that SQLite should > conform to the standard. That is true, although it is rather unfortunate that the standard makes this statement, IMHO. Unfortunately, this doesn't address the point of whether it should be possible to use NaNs as flo

Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

2011-06-06 Thread Sidney Cadot
> It'd be OK for NaN to map to NULL, but not for infinity, since there > is a distinction between positive and negative infinity, and that > distinction is valuable. The NaN value in IEEE-754 is also not unique. There is the distinction between signaling and quiet NaNs, and furthermore mantissa bi

Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

2011-06-06 Thread Simon Slavin
On 6 Jun 2011, at 1:49pm, Igor Tandetnik wrote: > Simon Slavin wrote: >> > >> http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt > > If we talking about the standard, note that both division by zero and numeric > overflow are supposed to trigger an error: > > 6.12-General Rules-4: If

Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

2011-06-06 Thread Igor Tandetnik
Simon Slavin wrote: > On 6 Jun 2011, at 4:39am, Sidney Cadot wrote: >> That's a bit of a philosophical point. In the context of SQLite, the >> expression 1.0 / 0.0 evaluates to something that is representable, >> namely NULL, that much is clear. Whether NULL is to be considered a >> value is up fo

Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

2011-06-06 Thread Nico Williams
On Mon, Jun 6, 2011 at 5:57 AM, Simon Slavin wrote: > No, it's in the standard.  Unfortunately you have to pay to receive the > standards document, but in the draft standard at > > http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt > > see the top of page 7: > > "null value (null): A speci

Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

2011-06-06 Thread Simon Slavin
On 6 Jun 2011, at 4:39am, Sidney Cadot wrote: > Hi Simon, > >> But you were using a SQL command to make the match. > > Well, I was using it to demonstrate some behavior I observed, yes. I > was not "matching values with NULL". But whatever. > >> You executed a SELECT command and got an answer

Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

2011-06-05 Thread Sidney Cadot
Hi Simon, > But you were using a SQL command to make the match. Well, I was using it to demonstrate some behavior I observed, yes. I was not "matching values with NULL". But whatever. > You executed a SELECT command and got an answer from SQL.  That answer does > not mean > "The result of the c

Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

2011-06-05 Thread Simon Slavin
On 6 Jun 2011, at 3:38am, Sidney Cadot wrote: >> And the OP was trying to match values with NULL. > > Actually, no, I was asking how SQlite behaves with respect to IEEE-754 > floating point. But you were using a SQL command to make the match. Here it one of them: On 5 Jun 2011, at 3:35pm, Sid

Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

2011-06-05 Thread Sidney Cadot
Hi Simon, > Jay is talking about SQL.  SQL /does/ use NULL for 'unknown'. Well yes, it does, but my entire point is that floating point NaN is quite different from "Unknown". SQLite sort-of unifies NaN and NULL (although this isn't documented). However, this is not an SQL choice -- it is an impl

Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

2011-06-05 Thread Simon Slavin
On 6 Jun 2011, at 3:02am, Sidney Cadot wrote: > Hi Jay, > >> As others have pointed out, one of the meanings of NULL is essentially >> "unknown." > > Yes, but in terms of IEEE-754, there exist no "unknown" results Jay is talking about SQL. SQL /does/ use NULL for 'unknown'. And the OP was

Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

2011-06-05 Thread Sidney Cadot
Hi Simon, > "Null is a special marker used in Structured Query Language (SQL) to indicate > that a data value does not exist in the database." To me, this statement does not apply to "NaN", which is a perfectly fine (albeit unusual) floating point value. > If you compare anything with NULL, you

Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

2011-06-05 Thread Sidney Cadot
Hi Jay, >  However, it is worth remembering that IEEE 754 is really about building >  processors, not about end-user interaction.  While it is a rigid, >  formal specification of a numeric environment, at its heart it is >  about mechanics, not about consistent mathematical systems built on >  the

Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

2011-06-05 Thread Jay A. Kreibich
On Sun, Jun 05, 2011 at 04:35:25PM +0200, Sidney Cadot scratched on the wall: > Is the way in which SQLite handlesNaN and Infinity values as defined > by IEEE-754 documented somewhere? I would also be interested to find a > discussion of the rationale behind the design decisions. Doubt it. I'm

Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

2011-06-05 Thread Simon Slavin
On 5 Jun 2011, at 6:10pm, Sidney Cadot wrote: >> Note that according to SQL semantics, 'NULL' means 'I don't know'. > > I am not quite sure what you are saying. I am pretty sure that NULL is > not defined so informally ... :) I expressed it in a short way, but I think it's a fair summary. Take

Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

2011-06-05 Thread Sidney Cadot
> Note that according to SQL semantics, 'NULL' means 'I don't know'. I am not quite sure what you are saying. I am pretty sure that NULL is not defined so informally ... :) > So every value of all types matches with it. I don't understand what "matches with" means in this context, sorry. ___

Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

2011-06-05 Thread Simon Slavin
On 5 Jun 2011, at 3:35pm, Sidney Cadot wrote: > After some experimenting, it appears that ... > > * SELECT 1.0 / 0.0 yields NULL (where I would expect to see Inf) > * SELECT 1e1 yields an actual IEEE-754 infinity, and it can be > stored in a table > * SELECT 1e1 + 1e1 yields Infinity

[sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

2011-06-05 Thread Sidney Cadot
Hi all, Is the way in which SQLite handlesNaN and Infinity values as defined by IEEE-754 documented somewhere? I would also be interested to find a discussion of the rationale behind the design decisions. After some experimenting, it appears that ... * SELECT 1.0 / 0.0 yields NULL (where I would