[sqlite] Version 3.25.1 coming soon....
Thanks for the bug reports. Two new bugs were added in the 3.25.0 release, so we will shortly have a 3.25.1 release to fix them. A draft change-log for 3.25.1 can be seen here: https://www.sqlite.org/draft/releaselog/3_25_1.html We *really* appreciate your bug reports. But we appreciate them even more when they arrive before a release rather than the day after. :-) In the future, please consider testing the latest SQLite in your applications while it is in beta. Now that 3.25.0 is already out, please continue testing it. We will hold off on the 3.25.1 patch for about 24 hours in case something else comes up. If you need the patches right away, or if you are just curious, the patches are on a branch: https://www.sqlite.org/src/timeline?r=branch-3.25 A hint about using the previous page: If you click on two of the circles in the graph at the left, it will take you to a diff between the two check-ins you selected. So if you want to see comprehensive diff of all changes in the branch, first click on the purple circle (notice how it turns red, indicating that it is now the baseline) then click on the leaf circle - the one with the black dot in the middle. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE touches unchanged expression indexes on update
This seems to work perfectly for expression indexes. Thanks, it makes a HUGE difference for us! Would you perhaps be able to make a similar fix for partial indexes? i.e. this scenario: CREATE TABLE Foo(x, y, z); CREATE INDEX FooX on Foo(x); CREATE INDEX FooZ on Foo(z); CREATE INDEX FooPartialZ on Foo(z) WHERE z > 42; explain UPDATE foo SET x=1 WHERE rowid=1; addr opcode p1p2p3p4 p5 comment - - -- - 0 Init 0 36000 Start at 36 1 Null 0 7 800 r[7..8]=NULL 2 OpenWrite 0 2 0 3 00 root=2 iDb=0; Foo 3 Integer1 12000 r[12]=1 4 SeekRowid 0 6 12 00 intkey=r[12] 5 Rowid 0 8 000 r[8]=rowid 6 OpenWrite 1 5 0 k(2,,) 00 root=5 iDb=0; FooPartialZ <<<=== 7 OpenWrite 3 3 0 k(2,,) 00 root=3 iDb=0; FooX 8 IsNull 8 35000 if r[8]==NULL goto 35 9 Integer1 9 000 r[9]=1 10Column 0 1 10 00 r[10]=Foo.y 11Column 0 2 11 00 r[11]=Foo.z 12Noop 0 0 000 uniqueness check for FooPartialZ <<<=== 13Null 0 1 000 r[1]=NULL 14Le 141811(BINARY) 51 if r[11]<=r[14] goto 18 15SCopy 112 000 r[2]=r[11]; z 16IntCopy8 3 000 r[3]=r[8]; rowid 17MakeRecord 2 2 100 r[1]=mkrec(r[2..3]); for FooPartialZ <<<=== 18Noop 0 0 000 uniqueness check for FooX 19SCopy 9 5 000 r[5]=r[9]; x 20IntCopy8 6 000 r[6]=r[8]; rowid 21MakeRecord 5 2 400 r[4]=mkrec(r[5..6]); for FooX 22Column 0 2 13 00 r[13]=Foo.z 23Le 142713(BINARY) 51 if r[13]<=r[14] goto 27 24Column 0 2 15 00 r[15]=Foo.z 25Rowid 0 16000 r[16]=rowid 26IdxDelete 1 15200 key=r[15..16] 27Column 0 0 15 00 r[15]=Foo.x 28Rowid 0 16000 r[16]=rowid 29IdxDelete 3 15200 key=r[15..16] 30IsNull 1 32000 if r[1]==NULL goto 32 31IdxInsert 1 1 2 2 00 key=r[1] <<<=== 32IdxInsert 3 4 5 2 00 key=r[4] 33MakeRecord 9 3 13 00 r[13]=mkrec(r[9..11]) 34Insert 0 138 Foo05 intkey=r[8] data=r[13] 35Halt 0 0 000 36Transaction0 1 4 0 01 usesStmtJournal=0 37Integer4214000 r[14]=42 38Goto 0 1 000 -Original Message- From: drhsql...@gmail.com On Behalf Of Richard Hipp Sent: Saturday, September 15, 2018 2:46 PM To: SQLite mailing list Cc: de...@outlook.com Subject: Re: [sqlite] SQLITE touches unchanged expression indexes on update On 9/11/18, Deon Brewis wrote: > It seems like there is an opportunity for improvement on updates if an > index contains expressions. This enhancement did not make the cutoff for 3.25.0. But as 3.25.0 is now out, I have started the next release cycle and you can find this enhancement on the latest trunk version of SQLite. You'll need to grab a tarball (or clone the Fossil repository) and compile it yourself. If you can, please do this and try out the code and let me know whether or not it works, that will be appreciated. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] Last_row_id
I can't seem to find where/if it's mentioned, but I believe the only use of a select statement in a trigger is to call the raise function and trigger some level of error. https://www.sqlite.org/lang_createtrigger.html -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Hick Gunter Sent: Monday, September 17, 2018 8:06 AM To: 'SQLite mailing list' Subject: Re: [sqlite] [EXTERNAL] Last_row_id A trigger program does not return any result rows. -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Yadwindersingh Gesendet: Sonntag, 16. September 2018 03:28 An: sqlite-users@mailinglists.sqlite.org Betreff: [EXTERNAL] [sqlite] Last_row_id Hi all; I am using vs15.x Create trigger return_pono after insert on po Begin Select last_insert_rowid(); End Trigger works quite fine in sqlite but fails to return any value to vb.net statement Dim lrow as int64 Lrow = some_cmd.executescalar() Please help Thank you ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] Last_row_id
A trigger program does not return any result rows. -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Yadwindersingh Gesendet: Sonntag, 16. September 2018 03:28 An: sqlite-users@mailinglists.sqlite.org Betreff: [EXTERNAL] [sqlite] Last_row_id Hi all; I am using vs15.x Create trigger return_pono after insert on po Begin Select last_insert_rowid(); End Trigger works quite fine in sqlite but fails to return any value to vb.net statement Dim lrow as int64 Lrow = some_cmd.executescalar() Please help Thank you ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Building sqlite-autoconf-3250000 with enable-rtree and enable-session
I'm building on Debian10 : warning: ISO C99 requires whitespace after the macro name : error: missing binary operator before token "1" sqlite3.c:308:5: note: in expansion of macro ‘SQLITE_ENABLE_RTREE’ * 'space' missing after $BUILD_CFLAGS --- a/configure 2018-09-15 15:36:05.0 +0200 +++ b/sqlite-autoconf-325/configure2018-09-17 09:30:57.824054134 +0200 @@ -13638,7 +13638,7 @@ fi if test x"$enable_session" = "xyes"; then - BUILD_CFLAGS="$BUILD_CFLAGS-DSQLITE_ENABLE_SESSION -DSQLITE_ENABLE_PREUPDATE_HOOK" + BUILD_CFLAGS="$BUILD_CFLAGS -DSQLITE_ENABLE_SESSION -DSQLITE_ENABLE_PREUPDATE_HOOK" fi #--- ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Possible bug in window function queries.
Been playing around with the new window functions in 3.25, and ran into something that looks like an infinite loop. Working with the t1 table many of the examples in the documentation use, I've come up with a minimal test case: sqlite> select id, b, lead(c, 1) over (order by c) as x from t1 where id > 1 order by b; id b x -- -- -- 2 B two 3 C three 4 D one 5 E 6 F two 7 G three is all well and good. However, after adding a LIMIT to the query: sqlite> select id, b, lead(c, 1) over (order by c) as x from t1 where id > 1 order by b limit 1; (time goes by) ^CError: interrupted A LIMIT of just a few rows causes the query to hang and never produce any output while sqlite3 uses 100% CPU. LIMIT 4 and up works, but change the comparison to >= and LIMIT 4 also freezes, but LIMIT 5 works. Take out the ORDER BY b and no freeze. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Regression with sqlite 3.25, table renaming and triggers referencing rtree
Hi, I just found an issue when a renaming a table when one of its trigger references a column of a rtree echo ' CREATE TABLE "mytable" ( "fid" INTEGER PRIMARY KEY, "geom" BLOB); CREATE VIRTUAL TABLE "rtree_mytable_geom" USING rtree(id, minx, maxx, miny, maxy); CREATE TRIGGER "rtree_mytable_geom_update2" AFTER UPDATE OF "geom" ON "mytable" WHEN OLD."fid" = NEW."fid" AND NEW."geom" IS NULL BEGIN DELETE FROM "rtree_mytable_geom" WHERE id = OLD."fid"; END; ALTER TABLE mytable RENAME TO mytable_renamed; ' | $HOME/install-sqlite-3.25.0/bin/sqlite3 Error: near line 5: error in trigger rtree_mytable_geom_update2 after rename: no such column: id This used to work fine with previous versions. Even -- Spatialys - Geospatial professional services http://www.spatialys.com ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] X64
Is there no new X64 version? -- With kind regards, Evert van Dijken De Hazelaar 5 6921XA Duiven (NL). --- Deze e-mail is gecontroleerd op virussen door AVG. http://www.avg.com ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Last_row_id
Hi all; I am using vs15.x Create trigger return_pono after insert on po Begin Select last_insert_rowid(); End Trigger works quite fine in sqlite but fails to return any value to vb.net statement Dim lrow as int64 Lrow = some_cmd.executescalar() Please help Thank you ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] The "natural" order of the query results.
On 9/17/18 2:19 AM, John Found wrote: > On Mon, 17 Sep 2018 06:02:37 + > Hick Gunter wrote: > >> SQLite handles GROUP BY and ORDER BY in basically the same way. If there is >> an apropirate index, then it will use this index and the rows will be >> returned in visitation order of this index. If, for exmaple by adding a new >> index or even an upgrade of the Query Planner, a different execution plan is >> constructed, then the order of the returned rows will "change". > Yes, of course, but you forgot about INDEXED BY clause. It will force using > particular index. So, the query planner will always use exactly this index, > regardless of how optimal it is and > as long as GROUP BY and ORDER BY are working the same way, this gives some > guarantee for the ordering without ORDER BY clause. Or my logic is wrong? I think the issue is that INDEXED BY forces the use of that index, but does NOT imply a final result order, that still requires the use of an ORDER BY clause. Please note the SQLite documentation for the INDEX BY clause description of the purpose of the INDEX BY clause, to detect unintended changes in Schema, not performance tuning, and is expected to be added at the very end of development (and by implication, will have no noticeable impact on the results). -- Richard Damon ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] The "natural" order of the query results.
In particular, there's a not implausible optimisation opportunity that could alter things... In the general case, the execution of "GROUP BY a, b" will "naturally" involve a sort on "a, b" to bring all the "to be grouped" entries together. In the OP's case, there's a primary key on "a, b" so there can only be one entry per group. As I understand it, it would therefore be valid to just scan the table and emit the "groups" (of size one) in whatever order they happen to be stored. Graham. Sent from my Samsung Galaxy S7 - powered by Three Original message From: Simon Slavin Date: 17/09/2018 09:09 (GMT+00:00) To: SQLite mailing list Subject: Re: [sqlite] [EXTERNAL] The "natural" order of the query results. On 17 Sep 2018, at 7:19am, John Found wrote: > Yes, of course, but you forgot about INDEXED BY clause. It will force using > particular index. So, the query planner will always use exactly this index, > regardless of how optimal it is and > as long as GROUP BY and ORDER BY are working the same way, this gives some > guarantee for the ordering without ORDER BY clause. No. Just because it works now doesn't mean it will continue to work that way in the future, unless the documentation says so. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] The "natural" order of the query results.
On 17 Sep 2018, at 7:19am, John Found wrote: > Yes, of course, but you forgot about INDEXED BY clause. It will force using > particular index. So, the query planner will always use exactly this index, > regardless of how optimal it is and > as long as GROUP BY and ORDER BY are working the same way, this gives some > guarantee for the ordering without ORDER BY clause. No. Just because it works now doesn't mean it will continue to work that way in the future, unless the documentation says so. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] The "natural" order of the query results.
On Mon, 17 Sep 2018 06:02:37 + Hick Gunter wrote: > SQLite handles GROUP BY and ORDER BY in basically the same way. If there is > an apropirate index, then it will use this index and the rows will be > returned in visitation order of this index. If, for exmaple by adding a new > index or even an upgrade of the Query Planner, a different execution plan is > constructed, then the order of the returned rows will "change". Yes, of course, but you forgot about INDEXED BY clause. It will force using particular index. So, the query planner will always use exactly this index, regardless of how optimal it is and as long as GROUP BY and ORDER BY are working the same way, this gives some guarantee for the ordering without ORDER BY clause. Or my logic is wrong? Anyway, read my second response to the DRHs example. IMHO, there is some kind of misbehavior with the ORDER BY planning when ordering descending. > > Please note that GROUP BY id,o1,o2 and ORDER BY o1,o2 are DIFFERENT > expressions and require reordering, which is the reason for an additional > BTree step. > > If you need the rows to be returned in a specific order, then you must say so > explicitly with an ORDER BY clause; otherwise, SQLite is free to return rows > in any order that happens to be convenient for the DB Engine. Relying on the > "natural" order is a common way of creating code that breaks unexpectedly. > > Similarly, if you need the result columns to have certain names, you must > provide these via AS clauses. > > -Ursprüngliche Nachricht- > Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im > Auftrag von John Found > Gesendet: Sonntag, 16. September 2018 10:30 > An: sqlite-users@mailinglists.sqlite.org > Betreff: [EXTERNAL] [sqlite] The "natural" order of the query results. > > Is there some relation between the indexes used in the query, the GROUP BY > fields used and the order of the result rows, when no "ORDER BY" clause is > used? > > I am asking, because I noticed, that on some queries, when I am using "ORDER > BY" the query always use temporary b-tree for ordering, but by including the > needed fields in the "GROUP BY" clause and removing the ORDER BY clause, the > query returns the rows in the proper order without temp b-tree. > > So, is it safe to use this implicit ordering, or this behavior can be changed > in the future versions of SQLite? > > Here is an example: > > create table A ( > id integer primary key autoincrement, > o1 integer, > o2 integer > ); > > create table B ( > Aid integer references A(id), > data text > ); > > create index idxA on A(o1 desc, o2 desc); > > insert into A(o1, o2) values (1, 100), (2, 50), (3, 200), (5, 300); insert > into B(Aid, data) values (1, "b"), (1, "a"), (2, "c"), (2, "d"), (2, "e"), > (3, "f"), (3, "g"); > > -- Always uses temp b-tree for order by > select > group_concat(B.data), o1, o2 > from > A > left join > B on A.id = B.Aid > group by > A.id > order by > A.o1 desc, A.o2 desc; > > explain query plan: > id parent notused detail > 8 0 0 SCAN TABLE A > 19 0 0 SEARCH TABLE B USING AUTOMATIC COVERING INDEX (Aid=?) > 58 0 0 USE TEMP B-TREE FOR ORDER BY > > > -- This one returns the rows in the needed order without ORDER BY select > group_concat(B.data), o1, o2 > from > A indexed by idxA > left join B on A.id = B.Aid > group by A.id, A.o1, A.o2; > > explain query plan: > id parent notused detail > 7 0 0 SCAN TABLE A USING COVERING INDEX idxA > 18 0 0 SEARCH TABLE B USING AUTOMATIC COVERING INDEX (Aid=?) > > > -- But if I add ORDER BY it still begins to use temp b-tree > -- regardless that it does not change the order. > select > group_concat(B.data), o1, o2 > from > A indexed by idxA > left join B on A.id = B.Aid > group by A.id, A.o1, A.o2 > order by A.o1 desc, A.o2 desc; > > explain query plan: > 8 0 0 SCAN TABLE A > 19 0 0 SEARCH TABLE B USING AUTOMATIC COVERING INDEX (Aid=?) > 60 0 0 USE TEMP B-TREE FOR ORDER BY > > > All the above queries, returns the same result rows in the same order: > > group_concat(B.data) o1 o2 > NULL 5 300 > f,g3 200 > c,d,e 2 50 > a,b1 100 > > > > > -- > John Found > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ___ > Gunter Hick | Software Engineer | Scientific Games International GmbH | > Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) > +43 1 80100 - 0 > > May be privileged. May be confidential. Please delete if not the addressee. > ___ > sqlite-users
Re: [sqlite] [EXTERNAL] The "natural" order of the query results.
SQLite handles GROUP BY and ORDER BY in basically the same way. If there is an apropirate index, then it will use this index and the rows will be returned in visitation order of this index. If, for exmaple by adding a new index or even an upgrade of the Query Planner, a different execution plan is constructed, then the order of the returned rows will "change". Please note that GROUP BY id,o1,o2 and ORDER BY o1,o2 are DIFFERENT expressions and require reordering, which is the reason for an additional BTree step. If you need the rows to be returned in a specific order, then you must say so explicitly with an ORDER BY clause; otherwise, SQLite is free to return rows in any order that happens to be convenient for the DB Engine. Relying on the "natural" order is a common way of creating code that breaks unexpectedly. Similarly, if you need the result columns to have certain names, you must provide these via AS clauses. -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von John Found Gesendet: Sonntag, 16. September 2018 10:30 An: sqlite-users@mailinglists.sqlite.org Betreff: [EXTERNAL] [sqlite] The "natural" order of the query results. Is there some relation between the indexes used in the query, the GROUP BY fields used and the order of the result rows, when no "ORDER BY" clause is used? I am asking, because I noticed, that on some queries, when I am using "ORDER BY" the query always use temporary b-tree for ordering, but by including the needed fields in the "GROUP BY" clause and removing the ORDER BY clause, the query returns the rows in the proper order without temp b-tree. So, is it safe to use this implicit ordering, or this behavior can be changed in the future versions of SQLite? Here is an example: create table A ( id integer primary key autoincrement, o1 integer, o2 integer ); create table B ( Aid integer references A(id), data text ); create index idxA on A(o1 desc, o2 desc); insert into A(o1, o2) values (1, 100), (2, 50), (3, 200), (5, 300); insert into B(Aid, data) values (1, "b"), (1, "a"), (2, "c"), (2, "d"), (2, "e"), (3, "f"), (3, "g"); -- Always uses temp b-tree for order by select group_concat(B.data), o1, o2 from A left join B on A.id = B.Aid group by A.id order by A.o1 desc, A.o2 desc; explain query plan: id parent notused detail 8 0 0 SCAN TABLE A 19 0 0 SEARCH TABLE B USING AUTOMATIC COVERING INDEX (Aid=?) 58 0 0 USE TEMP B-TREE FOR ORDER BY -- This one returns the rows in the needed order without ORDER BY select group_concat(B.data), o1, o2 from A indexed by idxA left join B on A.id = B.Aid group by A.id, A.o1, A.o2; explain query plan: id parent notused detail 7 0 0 SCAN TABLE A USING COVERING INDEX idxA 18 0 0 SEARCH TABLE B USING AUTOMATIC COVERING INDEX (Aid=?) -- But if I add ORDER BY it still begins to use temp b-tree -- regardless that it does not change the order. select group_concat(B.data), o1, o2 from A indexed by idxA left join B on A.id = B.Aid group by A.id, A.o1, A.o2 order by A.o1 desc, A.o2 desc; explain query plan: 8 0 0 SCAN TABLE A 19 0 0 SEARCH TABLE B USING AUTOMATIC COVERING INDEX (Aid=?) 60 0 0 USE TEMP B-TREE FOR ORDER BY All the above queries, returns the same result rows in the same order: group_concat(B.data) o1 o2 NULL 5 300 f,g3 200 c,d,e 2 50 a,b1 100 -- John Found ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
