Re: [sqlite] german locale: queries calculated float fields deliver wrong dec.sep.

Lol, I used it everytime with a "," and wondering why my select always return an integer instead of a floating point value. Sometimes I should read documentation more ... lol. But the "." is mostly used as an thousand delimiter. But thanks that I know this now. Need to rewrite many things now.

Re: [sqlite] Slow query

> The query return the apropiate values as always the id -> id2 relation is > the same and id -> name and id2 -> name2. So your id maps uniquely to id2, name and name2. But what about year? What value of year do you want to be used in sorting? Anyway try to change query as "GROUP BY id, name2,

Re: [sqlite] Slow query

On 30 Jun 2010, at 12:32am, J. Rios wrote: > On Wed, Jun 30, 2010 at 12:13 AM, Simon Slavin wrote: >> >> A good index would be >> >> name2, year, id >> >> That's all three columns in the same index, not three separate indexes one >> on each column. > > I did the test

Re: [sqlite] Slow query

On Wed, Jun 30, 2010 at 12:13 AM, Simon Slavin wrote: > > None of your indexes are much use for this SELECT command. Imagine trying > to do the SELECT command yourself and you'll quickly see that whichever of > the supplied indexes you use you're left trying to sort a great

Re: [sqlite] Slow query

On 30 Jun 2010, at 12:05am, J. Rios wrote: > Sorry for posting on top of the message, No problem. Adding new text at the bottom of a post, and trimming what you quote just enough that people understand your new text, make your own message clear and encourage people to reply to it. Look at

Re: [sqlite] Slow query

On Wed, Jun 30, 2010 at 12:02 AM, Simon Slavin wrote: > > On 29 Jun 2010, at 11:57pm, J. Rios wrote: > > >>> I have created the next indexes : index1( name ), index2( id2 ), > index3( > >>> name2 ); > > Those are very unlikely to be of any use at all. They're probably a

Re: [sqlite] Slow query

The query return the apropiate values as always the id -> id2 relation is the same and id -> name and id2 -> name2. I keep them in the same table to speed up other queries that are now very quick as uses indexes for the ordering but in this SELECT the GROUP BY makes the difference and the SORT is

Re: [sqlite] Slow query

On 29 Jun 2010, at 11:57pm, J. Rios wrote: >>> I have created the next indexes : index1( name ), index2( id2 ), index3( >>> name2 ); Those are very unlikely to be of any use at all. They're probably a waste of space. > Its not the primary Key. There are more fields but the index on id is >

Re: [sqlite] Slow query

Its not the primary Key. There are more fields but the index on id is created also. Sorry I missed it in the post. If I do a EXPLAIN QUERY PLAN sqlite tells me its going to use the id INDEX. But the sorting is slow. Thanks in advance On Tue, Jun 29, 2010 at 5:32 PM, Jim Morris

Re: [sqlite] create table {table-name} as select.... table definition is imperfectly cloned

On Tue, Jun 29, 2010 at 09:51:42AM -0400, Pavel Ivanov scratched on the wall: > > ?...which actually surprises me, since I was under the impression > > ?CREATE TABLE ... AS SELECT always produced NONE affinities. ?Is this > > ?a semi-recent (last year) change? > > It looks like the only recent

Re: [sqlite] Slow query

You also haven't specified a primary key, i.e. on "id" On 6/28/2010 11:24 PM, J. Rios wrote: > I have the next table > > table1( id INTEGER, name VARCHAR, id2 INTEGER, name2 VARCHAR, year INTEGER > ); > > I have created the next indexes : index1( name ), index2( id2 ), index3( > name2 ); > >

Re: [sqlite] create table {table-name} as select.... table definition is imperfectly cloned

> PICK is simply an > example of a database that came in many slightly different versions all of > which adhered to a core definition,  and this compatibility was a good > thing. Then I reiterate my point using your wordings: SQL comes in different versions and flavors (SQLite, Oracle, MS SQL

Re: [sqlite] create table {table-name} as select.... table definition is imperfectly cloned

On Tue, Jun 29, 2010 at 11:46 AM, Tim Romano wrote: > > Here's a little story: The Therac-25. http://sunnyday.mit.edu/papers/therac.pdf -- - D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list

Re: [sqlite] create table {table-name} as select.... table definition is imperfectly cloned

On Tue, Jun 29, 2010 at 11:46:34AM -0400, Tim Romano scratched on the wall: > The core concern, at least as I see it, is the undesirable effects of > sharing data between implementations that do not handle INT and INTEGER > primary keys compatibly. The only known program that can read SQLite

Re: [sqlite] create table {table-name} as select.... table definition is imperfectly cloned

Pavel, As I said, I am biased in favor of the benefits to there being core-compatibility among the various implementations of SQLite but I am not dogmatic about it, and I have no say in the matter in any case ;-) But I think you may have misunderstood me: I am not arguing that SQlite should be

Re: [sqlite] create table {table-name} as select.... table definition is imperfectly cloned

On Tue, Jun 29, 2010 at 10:46 AM, Tim Romano wrote: > Puneet, > I am not trying to give Adobe any sort of primacy; but I wouldn't call them > unimportant either. > > The core concern, at least as I see it, is the undesirable effects of > sharing data between

Re: [sqlite] create table {table-name} as select.... table definition is imperfectly cloned

Puneet, I am not trying to give Adobe any sort of primacy; but I wouldn't call them unimportant either. The core concern, at least as I see it, is the undesirable effects of sharing data between implementations that do not handle INT and INTEGER primary keys compatibly. I don't use and won't

Re: [sqlite] create table {table-name} as select.... table definition is imperfectly cloned

> I think > SQLite implementations should probably adhere to a core spec but I recognize > this as my bias, not dogma. Probably this is my personal opinion but why should SQLite comply with specification of Pick Multi-dimensional databases if it never claimed to be multi-dimensional? SQLite is a

Re: [sqlite] create table {table-name} as select.... table definition is imperfectly cloned

On Tue, Jun 29, 2010 at 9:58 AM, Tim Romano wrote: > But there may be an argument for making the cloning more precise. The issue is that CREATE TABLE t AS SELECT... is not meant to clone a table. Not too long ago I encountered the same issue (search the mail archives).

Re: [sqlite] create table {table-name} as select.... table definition is imperfectly cloned

But there may be an argument for making the cloning more precise. It's a bit of a mess, or at least it seems so to me because my first ten years of database work was done with PICK, a database that was developed by PICK Systems but licensed to many companies and marketed under different brands

Re: [sqlite] create table {table-name} as select.... table definition is imperfectly cloned

>  ...which actually surprises me, since I was under the impression >  CREATE TABLE ... AS SELECT always produced NONE affinities.  Is this >  a semi-recent (last year) change? It looks like the only recent change was a year ago: http://www.sqlite.org/changes.html#version_3_6_15. But according to

Re: [sqlite] create table {table-name} as select.... table definition is imperfectly cloned

On Tue, Jun 29, 2010 at 06:59:18AM -0400, Tim Romano scratched on the wall: > CREATE TABLE "main"."proto" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT > NULL , "name" TEXT) > CREATE TABLE "main"."clone" as select * from PROTO > > The primary key of table CLONE is defined as "INT" not

[sqlite] create table {table-name} as select.... table definition is imperfectly cloned

CREATE TABLE "main"."proto" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL , "name" TEXT) CREATE TABLE "main"."clone" as select * from PROTO The primary key of table CLONE is defined as "INT" not "INTEGER". Regards Tim Romano Swarthmore PA ___

[sqlite] FTS and sorting

Hi What is the most efficient way to sort a result set from an FTS table? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] alternative to UNIQUE CONSTRAINT

Darren Duncan writes: [...] > > Try making all of your column definitions NOT NULL and see if that makes any > difference. That is, see if this works: > > CREATE TABLE wasdoesntwork( > idINTEGER PRIMARY KEY AUTOINCREMENT, > someint INTEGER NOT NULL,

Re: [sqlite] Slow query

> SELECT id, name, id2, name2 GROUP BY id ORDER BY name2, year DESC LIMIT 0, > 15 > > How can I make it faster? First of all your query should return nonsense in any field except id. I bet it will also return different results (for the same ids) depending on what LIMIT clause you add or don't add

[sqlite] sqlite3IsNaN with msvc6

MSVC6 doesn't have /fp: option so this piece of code in main.c always yields an assert: if ( rc==SQLITE_OK ){ u64 x = (((u64)1)<<63)-1; double y; assert(sizeof(x)==8); assert(sizeof(x)==sizeof(y)); memcpy(, , 8); assert( sqlite3IsNaN(y) ); } How can I compile sqlite in

[sqlite] Slow query

I have the next table table1( id INTEGER, name VARCHAR, id2 INTEGER, name2 VARCHAR, year INTEGER ); I have created the next indexes : index1( name ), index2( id2 ), index3( name2 ); The database have about 200,000 records. The next query takes about 2 seconds and I think its too much. SELECT