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

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,

Re: [sqlite] .import error: cannot open large file

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 06/06/2011 04:47 PM, Rense Corten wrote: > Just to report back on this issue: recompiling as per Nuno's > instructions indeed solved the problem. Still, if anyone can explain > to me why the original executable would work without problems on a >

Re: [sqlite] .import error: cannot open large file

Just to report back on this issue: recompiling as per Nuno's instructions indeed solved the problem. Still, if anyone can explain to me why the original executable would work without problems on a different machine, I would be grateful. > > On Fri, Jun 3, 2011 at 10:28 AM, Rense Corten

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

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

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

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

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

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

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

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

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

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

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

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

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

> 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

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

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

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

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

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

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?

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,

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

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

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

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

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

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

Re: [sqlite] sqlite-users Digest, Vol 42, Issue 6

Hear, hear! We're all capable of making our own decisions. Last time I looked, the title of this forum was General Discussion of SQLIte database, hopefully we can get back on topic. Pete > Message: 26 > Date: Mon, 6 Jun 2011 01:26:54 +0100 > From: Simon Slavin >

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

That's the nice thing about standards...there's so many to choose from... :-) Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of

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

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

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

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

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

[sqlite] Speed comparison

I saw that there is the need for a speed comparison. I have MySQL (5.1.53) installed (and when necessary I could install PostgreSQL). Would it be interesting if I made those tests? If yes, what is the correct way to do it? I am not using the most recent version (3.7.5), but I suppose that this

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

> 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

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

> 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

Re: [sqlite] Howto pivot in SQLite

Tom, Stop and think about what you just suggested: The invoice would look something like this: |Qty | Desc | Price | Total -++---+ +| 2 | 5x7 | 9.95 | 18.90 +| 1 | 5x7 | 9.95 | 18.90 +| 1 | 8x10 | 19.95 | 19.95 +| 2 | 16x20 | 49.00 | 98.00 +| 1 |

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

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

Re: [sqlite] Bug in .indices

2011/6/6 Igor Tandetnik : >>> If you are talking about INTEGER PRIMARY KEY column, then no index is shown >>> for it because none is created. See also >>> http://sqlite.org/lang_createtable.html#rowid >> >> That is what I am talking about. Something went wrong with my

Re: [sqlite] Bug in .indices

Cecil Westerhof wrote: > 2011/6/6 Igor Tandetnik : >> If you are talking about INTEGER PRIMARY KEY column, then no index is shown >> for it because none is created. See also >> http://sqlite.org/lang_createtable.html#rowid > > That is what I am

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

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

Re: [sqlite] Bug in .indices

2011/6/6 Igor Tandetnik : > Cecil Westerhof wrote: >> The help says that .indices shows all indices. But it shows at least >> not the PRIMARY KEY indices. >> When using: >>    .indices >> I get nothing. > > Works for me: > > sqlite> create table t(x

Re: [sqlite] Problem with create table and strftime

2011/6/6 Igor Tandetnik : >> In a create table I have: >>      measureDate            DATE           UNIQUE      DEFAULT >> (strftime('%Y-%m-%d', 'now')), >> but when I look with .schema, I get: >>      measureDate            DATE           DEFAULT (Datetime('now')), >> >> The

[sqlite] Bug in .indices

Something went wrong, so again. The help says that .indices shows all indices. But it shows at least not the PRIMARY KEY indices. When using: .indices I get nothing. When using: .indices weights I get: sqlite_autoindex_weights_1 -- Cecil Westerhof

Re: [sqlite] Problem with create table and strftime

Cecil Westerhof wrote: > In a create table I have: > measureDateDATE UNIQUE DEFAULT > (strftime('%Y-%m-%d', 'now')), > but when I look with .schema, I get: > measureDateDATE DEFAULT (Datetime('now')), > > The

Re: [sqlite] Bug in .indices

Cecil Westerhof wrote: > The help says that .indices shows all indices. But it shows at least > not the PRIMARY KEY indices. > When using: >.indices > I get nothing. Works for me: sqlite> create table t(x text primary key); sqlite> .indices t sqlite_autoindex_t_1 If

[sqlite] Bug in .indices

The help says that .indices shows all indices. But it shows at least not the PRIMARY KEY indices. When using: .indices I get nothing. When using: -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] Spatialite in Xcode

On 06/06/2011, at 9:32 PM, Jan wrote: > did you already check the SpatiaLite website. There is a whole chapter > about integrating SpatiaLite in different ways (OSes). I took a look at > it myself yesterday (though Xcode was not the reason) - very good website. Thanks for the reply. Yes,

Re: [sqlite] Spatialite in Xcode

Hi, did you already check the SpatiaLite website. There is a whole chapter about integrating SpatiaLite in different ways (OSes). I took a look at it myself yesterday (though Xcode was not the reason) - very good website. Jan Am 06.06.2011 13:15, schrieb BareFeetWare: > Hi all, > > I'm

[sqlite] Spatialite in Xcode

Hi all, I'm trying to add spatialite, a library of geographic/spatial/geometric extensions for SQLite, to my existing SQLite project in Xcode. Has anyone here done this successfully? If so, how, with step by step instructions please :-) Any help appreciated. Thanks, Tom BareFeetWare --

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

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

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

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] Proper way to escape table name and column name

On Mon, Jun 6, 2011 at 6:14 AM, Jean-Christophe Deschamps wrote: > > >What is the official way to escape table name that contains a space > >and column name that contain a spaces? > > You can use square brakets or double-quotes: > > [This is a long name for a small table] >

Re: [sqlite] Proper way to escape table name and column name

>What is the official way to escape table name that contains a space >and column name that contain a spaces? You can use square brakets or double-quotes: [This is a long name for a small table] "This is a long name for a small table as well" ___

[sqlite] Problem with create table and strftime

In a create table I have: measureDateDATE UNIQUE DEFAULT (strftime('%Y-%m-%d', 'now')), but when I look with .schema, I get: measureDateDATE DEFAULT (Datetime('now')), The UNIQUE constraint is disappeared and the DEFAULT is changed. I

[sqlite] Proper way to escape table name and column name

What is the official way to escape table name that contains a space and column name that contain a spaces? Thanks a lot. -- Marco Bambini http://www.sqlabs.com ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] Big difference in performance between Python and gcc

2011/6/1 Dominique Pellé : > Alessandro Marzocchi wrote: > >> Hello, >>    I made some preliminary tests for an application storing big >> chunks of data in a sqlite database. I did firsts tests with python >> and they gave me quite impressive results. I then tried to

Re: [sqlite] Big difference in performance between Python and gcc

On the weekend I had way to test the same code on a linux box... performance there are as expected (with C performing slightly better than python with a ~20% difference in execution times between the two). I'll try disabling thread, as they could give a big performance hit on win system.

Re: [sqlite] HELP: sqlite queries execute faster in sqlite manager addon but take a lot of time to execute using a java program

Changing the driver helped in improving the performance drastically. Sridhar On 03-06-2011 20:05, Sridhar Polavarapu wrote: > This is not any update or insert statement. This is just a select > statement. Will that help if i change the driver ? I am currently > using sqlitejdbc-v056.jar > >

Re: [sqlite] HELP : how to use datetime('column', 'localtime') as a part of sql string

Thanks Pavel for your replies. The reason looks to be the driver I am using to connect sqlite. I have changed it to use the driver from xerial and it is working fine now. Sridhar On 03-06-2011 12:00, Sridhar Polavarapu wrote: > Here is the code of my TestStatusDate > > public static void

Re: [sqlite] Do I need to migrate to MySQL?

Darren Duncan писал(а) в своём письме Mon, 06 Jun 2011 05:43:47 +0600: > What I'm saying is analogous to saying people should default to wearing > helmets > when riding bikes and only not wear helmets on bikes when they can justify it. > You'll still get where you're