[sqlite] [BUG?] BEFORE INSERT trigger has NEW.pk = -1 when NULL is passed for PK

of this feature in either https://www.sqlite.org/autoinc.html (section Background), https://www.sqlite.org/lang_createtable.html#rowid or https://www.sqlite.org/lang_createtrigger.html , so I consider this a bug (expect to see NULL in NEW.pk for the last two inserts). -- Tomash Brechko

[sqlite] [BUG?] BEFORE INSERT trigger has NEW.pk = -1 when NULL is passed for PK

2015-05-25 15:27 GMT+03:00 Richard Hipp : > > https://www.sqlite.org/mark/lang_createtrigger.html?Cautions+val*ger.#mark > Missed that. Thanks! -- Tomash Brechko

[sqlite] [BUG] 3.11.0: FTS3/4 index emptied by 'optimize' inside transaction

. which may or may not be related. Regards, -- Tomash Brechko

[sqlite] FTS tokenize=unicode61: "full" or "simple" case folding?

German speaker would expect in full-text search case? (Google gives different result counts for "Schlo?" and "Schloss", which actually surprises me a bit). -- Tomash Brechko

[sqlite] Amalgamation and CPP defines

, what may cause such a big difference. I could dig into this myself, but maybe someone has encountered the same problem before? Thanks! -- Tomash Brechko - To unsubscribe, send email to [EMAIL PROTECTED] -

Re: [sqlite] Makefile

E) -c sqlite3.c + alter.lo: $(TOP)/src/alter.c $(HDR) $(LTCOMPILE) -c $(TOP)/src/alter.c -- Tomash Brechko - To unsubscribe, send email to [EMAIL PROTECTED] -

[sqlite] [BUG or FR] OpenEphemeral invariant optimization (was [sqlite] Best way to optimize this query?)

you think? I can't call any of that a bug, because, as said in the docs, SQLite's primary goal is being robust and have a manageable code base, speed comes as a consequence. But, at least point 3 doesn't seem to be hard to do, and for a cert

Re: [sqlite] [BUG or FR] OpenEphemeral invariant optimization

dy to believe that constant repopulation is the cause. Now I know that it's the constance temp table re-creation that eats the time. Still, yes, my mistake, sorry. -- Tomash Brechko - To unsubscribe, send email to [EMAIL PROTECTED] -

Re: [sqlite] May I ask why the source distribution mechanism was changed starting with 3.3.14?

hey are in place, everything is perfectly manageable. -- Tomash Brechko - To unsubscribe, send email to [EMAIL PROTECTED] -

Re: [sqlite] how to add a new column quickly

ificiently? Thanks! If there is a typo in the last line, and it should have been 'newcolname=3' (not 2), then your operation is effectively UPDATE tablename SET newcolname = ROWID; -- Tomash Brechko - To unsubscribe, se

Re: [sqlite] how to add a new column quickly

On Sat, May 05, 2007 at 19:30:59 +0800, ronggui wong wrote: > Thanks. But there is no typo, what I want is a general solution. > > 2007/5/5, Tomash Brechko <[EMAIL PROTECTED]>: > >On Sat, May 05, 2007 at 14:01:56 +0800, ronggui wong wrote: > >> . update tablename

Re: [sqlite] Help wiith SQL - first row of each group

the plain SQL query should be fast enough :). -- Tomash Brechko - To unsubscribe, send email to [EMAIL PROTECTED] -

Re: [sqlite] Help wiith SQL - first row of each group

On Mon, May 07, 2007 at 18:10:29 +0400, Tomash Brechko wrote: > The solution with two table scans might be > > SELECT * FROM RESULT_TABLE a > WHERE P = (SELECT MIN(P) FROM RESULT_TABLE b WHERE b.G = a.G); > > You may also try to play with indexes G, P. > > It seem

Re: [sqlite] Help wiith SQL - first row of each group

On Mon, May 07, 2007 at 19:20:49 +0400, Tomash Brechko wrote: > But with SQLite 3.x there is only one user data pointer per user > defined aggregate function, so no parallel aggregate execution is > possible. Ah, I was too fast again :/. GROUP BY is really two-pass, but my understa

Re: [sqlite] Re: perfomance degradation for expr "foo = X or bar =X"

WHERE n2 = $I > ORDER BY id DESC; > > Timing: > 1: 0.080 s. > 2: 0.000 s. >From http://www.sqlite.org/optoverview.html section 6.0: Each table in the FROM clause of a query can use at most one index... So the first query can't benefit from both idx1 and idx2. Y

Re: [sqlite] perfomance degradation for expr "foo = X or bar =X"

On Wed, May 09, 2007 at 16:34:57 +0400, bash wrote: > On Wed, 9 May 2007 14:24:27 +0400 > Tomash Brechko <[EMAIL PROTECTED]> wrote: > > From http://www.sqlite.org/optoverview.html section 6.0: > > > > Each table in the FROM clause of a query can use at most one

Re: [sqlite] perfomance degradation for expr "foo = X or bar =X"

rows where rowid in (set1 union set2). You should read it the following way: "SQLite can't use two indexes per table, and using only one index is pointless, hence no index is used at all". So your question is actually "why SQLite uses at most one index per table?". My guess is

Re: [sqlite] perfomance degradation for expr "foo = X or bar =X"

ndow size and points distribution will give quite reasonable performance. -- Tomash Brechko - To unsubscribe, send email to [EMAIL PROTECTED] -

Re: [sqlite] DBD::SQLite with FTS

d library instead of the shipped code: $ ldd /usr/lib/perl5//DBD/SQLite/SQLite.so linux-gate.so.1 => (0xb7fc) (!)libsqlite3.so.0 => /usr/local/lib/libsqlite3.so.0 (0xb7f25000) libc.so.6 => /lib/tls/libc.so.6 (0xb7dfe000) /lib/ld-linux.so.2 (0x8000

Re: [sqlite] Odd results return by SELECT query WHERE word = "word"

yword" A keyword in double-quotes is interpreted as an identifier if it matches a known identifier. Otherwise it is interpreted as a string literal. -- Tomash Brechko - To unsubscribe, send email to [EMAIL PROTECTED] -

Re: [sqlite] default values at CREATE TABLE

o both test_num and test_name columns explicitly, since the next value of AUTOINCREMENT column is predictable (and the largest used value may be learned from sqlite_sequence table). -- Tomash Brechko - To unsubscribe, send email to [EMAIL PROTECTED] -

Re: [sqlite] What is the column value after ALTER TABLE ADD COLUMN?

other_column) WHERE newcol IS NULL; NULLs aren't equal to each other: sqlite> .nullvalue sqlite> select NULL = NULL; -- Tomash Brechko - To unsubscribe, send email to [EMAIL PROTECTED] -

Re: [sqlite] Unique Index not working properly

ould give a unique constraint error, but does not. > > testing with sql server and oracle shows that they *will* give an > error. in this case, NULL = NULL seems to be true > > can this be fixed ? it is quite a deviation from standard behavior. This page http://www.sqlite.org

[sqlite] [BUG] Adding an index changes query result

sqlite> select 11 > '2'; 0 It seems that last two cases should compare the same way, no matter what the actual affinity rules are. Regards, -- Tomash Brechko ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [BUG] Adding an index changes query result

2010/6/9 Pavel Ivanov > You can see that these 2 cases compare the same way. They both show > that string is always greater than number and thus '11' > 2 and '2' > > 11. And no affinity rules are applicable here because you use > constants which don't have any affinity.