Re: [sqlite] Feature request: extend the IN operator

2011/8/8 Simon Slavin : > You're quite right.  If someone was going to write that functionality into a > SQLite app in real life they'd probably use 'LIKE' or 'GLOB' and store all > the possibilities in one row. FTS3/FTS4 is better as index for lists. See my test script for Igor. We can fast sea

Re: [sqlite] Feature request: extend the IN operator

On 7-aug-2011, om 13:17, Alexey Pechnikov wrote: > Is it possible to support construction like to > > where x IN "1 2 3" > > There are a lot of situations when is very useful to store list of > identifiers in table field. > I know about the intarray virtual table but it's need > constructor/destru

Re: [sqlite] Feature request: extend the IN operator

On 7 Aug 2011, at 10:07pm, P Kishor wrote: > I will let you heavyweights duke it out, but re. the above point, > SQLite (and more databases) have a per row system overhead that can > very quickly overweight the actual data if the data are too granular > with each row storing just a tiny amount. F

Re: [sqlite] Feature request: extend the IN operator

On Mon, Aug 8, 2011 at 2:19 AM, Igor Tandetnik wrote: > Alexey Pechnikov wrote: >> 2011/8/7 Simon Slavin : >>> You don't need to. The SQLite expressions I listed tell you how to achieve >>> the result without doing that. >> >> Really? And how can you perform the query like to: >> >> sqlite> crea

Re: [sqlite] Feature request: extend the IN operator

Igor, how about simple test? Normalized database is big and slow. $ time ./test_norm_idx.tcl real32m54.978s user32m14.885s sys 0m39.842s $ time ./test_idx.tcl real7m19.005s user6m55.226s sys 0m11.717s $ ls -lh *db 2,1G test_idx.db 7,1G test_norm_idx.db test_idx.tcl

Re: [sqlite] Feature request: extend the IN operator

Alexey Pechnikov wrote: > 2011/8/7 Simon Slavin : >> You don't need to. The SQLite expressions I listed tell you how to achieve >> the result without doing that. > > Really? And how can you perform the query like to: > > sqlite> create table t1(ids text); > sqlite> insert into t1 (ids) values (

Re: [sqlite] Feature request: extend the IN operator

2011/8/7 Simon Slavin : > You don't need to.  The SQLite expressions I listed tell you how to achieve > the result without doing that. Really? And how can you perform the query like to: sqlite> create table t1(ids text); sqlite> insert into t1 (ids) values ('1 2 3'); sqlite> insert into t1 (ids)

Re: [sqlite] Feature request: extend the IN operator

On 7 Aug 2011, at 3:32pm, Alexey Pechnikov wrote: > 2011/8/7 Stephan Beal : > >> Also keep in mind that sqlite3 tries, to a large degree, to be compatible >> with ANSI SQL, and (IN "A B C"), in the form you describe, is not >> ANSI-specified. > > Yes, but in PostgreSQL (as example) we can creat

Re: [sqlite] Feature request: extend the IN operator

2011/8/7 Stephan Beal : > Also keep in mind that sqlite3 tries, to a large degree, to be compatible > with ANSI SQL, and (IN "A B C"), in the form you describe, is not > ANSI-specified. Yes, but in PostgreSQL (as example) we can create user-defined function returns table from the list. SQLite can

Re: [sqlite] Feature request: extend the IN operator

2011/8/7 Simon Slavin : > For example, > > SELECT * FROM cars WHERE cars.name IN ('Corolla', 'Fiesta') create table t (names TEXT); insert into t(name) values ('Corolla Fiesta'); SELECT * FROM cars WHERE cars.name IN (select names from t where rowid=1); -- Best regards, Alexey Pechnikov. http:/

Re: [sqlite] Feature request: extend the IN operator

On 7 Aug 2011, at 12:17pm, Alexey Pechnikov wrote: > Is it possible to support construction like to > > where x IN "1 2 3" How does this differ on the IN operator ? For example, SELECT * FROM cars WHERE cars.name IN ('Corolla', 'Fiesta') > There

Re: [sqlite] Feature request: extend the IN operator

On Sun, Aug 7, 2011 at 1:17 PM, Alexey Pechnikov wrote: > There are a lot of situations when is very useful to store list of > identifiers in table field. > The problem with the concept of "list as a string" is that it implies a specific token separator, and there is no single universal solution

Re: [sqlite] Feature request: extend the IN operator

On Sun, Aug 7, 2011 at 3:17 PM, Alexey Pechnikov wrote: > Is it possible to support construction like to > > where x IN "1 2 3" > ... > > Does somebody interesting in this functionality too? > > I needed one, I just implemented mysql find_in_set as a user function. there it was comma-delimited, b

[sqlite] Feature request: extend the IN operator

Is it possible to support construction like to where x IN "1 2 3" There are a lot of situations when is very useful to store list of identifiers in table field. I know about the intarray virtual table but it's need constructor/destructor calls (and produce segfaults with the autorizer function).