... This is the update syntax used in PostgreSQL and other databases. I also believe this is easier to understand and easier to implement (since the select is just the source for data) ---- _2006-Jan-24 19:21:15 by anonymous:_ {linebreak} Already supported via correlated subquery: update b set val = (select a.val from a where a.id = b.id); See: http://www.sqlite.org/lang_update.html
#e8e8bd 209 new active 2002 Dec anonymous BTree 2003 Nov 1 1 update libtool The version of libtool shipped with sqlite is very out of date (well over a year old). It does not properly detect the ability to build shared libraries on darwin, for example, which is bad. You need to update to the latest version of gnu libtool.
#e8e8bd 1332 new active 2005 Jul anonymous Unknown 2005 Jul 5 5 update 20x slower when using a temp table I have two versions of the same algorithm. The first operates directly on the main db table. The second operates on a temp table containing only the working set. The problem is that the second version is about 20x slower, 1.5 sec versus 30 sec. If the EXISTS line in the second version is commented out the execution time drops to 9 sec. The test dataset has 12000 rows in group_article and the query deals with 6400 rows. The final version will be expected to work with >2 million rows. Version 1: {linebreak} UPDATE group_article SET parent=null WHERE group_id=?; UPDATE group_article SET parent= ( SELECT article.id FROM refs ,article WHERE refs.article_id=group_article.article_id AND reference=hash AND EXISTS (SELECT id FROM group_article WHERE group_id=?1 AND article_id=article.id) ORDER BY refs.id DESC LIMIT 1 ) WHERE group_id=?1
Version 2:{linebreak} CREATE TEMP TABLE thrd(aid UNIQUE, parent); INSERT INTO thrd(aid) SELECT article_id FROM group_article WHERE group_id=?; UPDATE thrd SET parent= ( SELECT article.id FROM refs ,article WHERE refs.article_id=thrd.aid AND reference=hash AND EXISTS (SELECT aid FROM thrd WHERE aid=article.id) ORDER BY refs.id DESC LIMIT 1 ); UPDATE group_article SET parent= ( SELECT parent FROM thrd WHERE aid=article_id ) WHERE group_id=? ;
Here are partial table defs. {linebreak} table group_article (id PRIMARY, group_id INT, article_id INT); UNIQUE index on (group_id, article_id); table refs (id PRIMARY, article_id int, reference BLOB(15)); index on (article_id) table article (id PRIMARY, hash BLOB(15)); UNIQUE index on (hash)
Almost forgot, I'm running this on windows xp, compiled with mingw gcc 3.4.4. The appears to be an enhancment request - you are wanting better optimization in the SQLite. It seems to get the correct answer, you just want to get the answer faster. So I have changed this ticket to an enhancment request and set the priority very low. If you want help optimizing your query, the proper place to ask is on the mailing list. ---- _2005-Jul-24 03:50:31 by anonymous:_ {linebreak} After taking a day away from the code I finnaly found the problem. Declaring a column as UNIQUE without giving it a type means that either the index is not created or it's not used. A full table scan was being used each time in the second version. Adding INT as the type on thrd.aid fixed the performance problem. Assuming I didn't miss this in the docs, this is either a bug or a note is needed so others don't make the same mistake.
#e8e8bd 1741 warn active 2006 Mar anonymous VDBE 2006 Mar 5 4 unused variable with SQLITE_OMIT_UTF16 defined vdbemem.c, function sqlite3VdbeChangeEncoding(): int sqlite3VdbeChangeEncoding(Mem *pMem, int desiredEnc){
int rc;
if( !(pMem->flags&MEM_Str) || pMem->enc==desiredEnc ){
return SQLITE_OK;
}
#ifdef SQLITE_OMIT_UTF16
return SQLITE_ERROR;
#else
...
If SQLITE_OMIT_UTF16 is defined then the "rc" variable is unused and compiler (Windows Intel 7.0) emits useless warning.
#f2dcdc 637 code active 2004 Mar anonymous Parser 2004 Mar 5 4 union uses non-standard column names for other dbms, such as postgresql, mysql, etc, issuing a select x as name, y as value from first_table union select x, y from second_table will produce a resultset with column names of 'name' and 'value' (the first resultset). in sqlite it will produce a resultset with column names of 'x' and 'y' (the last resultset). Code causing this appears to be in multiSelect function in src/select.c.
#f2dcdc 2297 code active 2007 Apr anonymous 2007 Apr drh 3 3 uninitialized var (with patch) Warnings with amalgamation and NDEBUG. _2007-Apr-12 21:21:29 by drh:_ {linebreak} I looked at the suggested changes and I didn't find any cases where it really was possible to use an uninitialized variable, at least not in a harmful way. Did I overlook something, or is this ticket just a request to silence compiler warnings? ---- _2007-Apr-13 00:08:36 by anonymous:_ {linebreak} vdbe.c with n, n64, payloadSize and payloadSize64{linebreak} sqlite3BtreeKeySize,sqlite3BtreeLast return are not checked. You can not be sure the pointer passed as second argument will be init depending on the return of restoreOrClearCursorPosition (btree.c).{linebreak} page.c with ro{linebreak} Compiled with -DNDEBUG, the return of sqlite3OsOpenReadWrite is not checked before making a move with 'ro'. For sContext.zAuthContext in delete.c/update.c, you're the one. gcc (compiler in general) warnings are quite usefull, i don't think it's a good idea to ignore them and accumulate danger. Perhaps one day, one line in a subroutine will modify some tricky behavior and (re)raise a previous checked warning, making it completely normal and 'under control'.
#e8e8bd 2629 doc active 2007 Sep anonymous 2007 Sep 4 4 typo in os_unix.c for nolock Index: src/os_unix.c =================================================================== RCS file: /sqlite/sqlite/src/os_unix.c,v retrieving revision 1.165 diff -u -3 -p -r1.165 os_unix.c --- src/os_unix.c 5 Sep 2007 13:56:32 -0000 1.165 +++ src/os_unix.c 6 Sep 2007 17:53:47 -0000 @@ -2126,7 +2126,7 @@ static const sqlite3_io_methods sqlite3D /* ** This vector defines all the methods that can operate on an sqlite3_file -** for unix with dotlock style file locking. +** for unix with nolock style file locking. */ static const sqlite3_io_methods sqlite3NolockLockingUnixIoMethod = { 1, /* iVersion */
#e8e8bd 1503 doc active 2005 Oct anonymous 2005 Oct 5 4 typo in datatype3.html http://www.sqlite.org/datatype3.html SQLite treats the expression "a IN (x, y, z)" as equivalent to "a = z OR a = y OR a = z". should be ... equivalent to "a = x OR ...
#e8e8bd 1689 new active 2006 Feb anonymous 2006 Mar 2 4 triggers and temporary tables CREATE TRIGGER trg_upd_dict AFTER UPDATE ON dict BEGIN UPDATE dict SET code = (SELECT code from tmp_connected_user) WHERE old.dict_id = dict_id ; END ;
This trigger doesn't work if tmp_connected_user is a temporary table. The message is : SQL error: no such table: main.tmp_connected_user The goal is to have persistant triggers who works with temporary tables. Exemple of use : - Workarround who replace the non existing connection by user / password. When we insert/update, the database doesn't know who insert/update. If we have a table user, we can on each table fill by trigger fields like last_user_id, last_modif_d. The trigger cannot know who make the connection but we stock the user_id when he connects to the db in a temporary table, the trigger will work. - Security (no one can update / insert the database if a special temporary table is not created and filled). ---- _2006-Mar-03 20:25:41 by drh:_ {linebreak} You can create a TEMP trigger that will reference tables in the main database and/or attached databases. But SQLite currently does not allow triggers in the main or attached database to reference tables in other databases. I will enter this as an enhancement request. ---- _2006-Mar-06 16:17:11 by anonymous:_ {linebreak} Workarround for this ticket : if we only need 1 result, we can use user defined function instead temporary table in the trigger. Tested with php : it works :)
#e8e8bd 510 new active 2003 Nov anonymous Unknown 2003 Nov 5 2 trigger: addition of create ability this code COULD be implemented outside the .sql script, but if this were possible it would cut that code by about 70-80%! not to mention makes it 10x easier to handle the db. this is only a chunk: /* create group info table on addtion to master group list /*{linebreak} create trigger group_make_new after insert on group_list {linebreak} begin {linebreak} /* make table of users for group /* {linebreak} create table group_[new.groupID] ( {linebreak} userID unique, {linebreak} userlevel {linebreak} ); {linebreak} /* insert owner as a member /* {linebreak} insert into group_[new.groupID] values (new.owner,3); {linebreak} end; {linebreak} note: you'll have to fix the comments cause of this posting format....
#f2dcdc 2012 code active 2006 Oct anonymous 2006 Oct 4 3 trigger4.test aborts "make test" on Windows The failure to remove these files causes "make test" to abort without completing remaining tests: trigger4-99.9... Ok ./testfixture: error deleting "trigtest.db": permission denied while executing "file delete -force trigtest.db trigtest.db-journal" (file "test/trigger4.test" line 199) fix: Index: test/trigger4.test =================================================================== RCS file: /sqlite/sqlite/test/trigger4.test,v retrieving revision 1.9 diff -u -3 -p -r1.9 trigger4.test --- test/trigger4.test 4 Oct 2006 11:55:50 -0000 1.9 +++ test/trigger4.test 9 Oct 2006 14:09:07 -0000 @@ -195,6 +195,6 @@ do_test trigger4-7.2 { integrity_check trigger4-99.9 -file delete -force trigtest.db trigtest.db-journal +catch {file delete -force trigtest.db trigtest.db-journal} finish_test Not sure why this ticket was set to Fixed_in_3.0, but I can reproduce the "make test" abort on Windows. ---- _2006-Oct-11 00:27:16 by drh:_ {linebreak} I do not know why the resolution was set to "Fixed_In_3.0" either. It seems to have been set that why by the original submitter. I will fix this eventually, but since it does not represent a real malfunction, it has a lower priority.
#e8e8bd 1249 new active 2005 May anonymous Unknown 2005 May 5 4 transaction It would be handy to have a sqlite3_in_transaction function that would return whether or not a databases connection was in the middle of a transaction or not. I'm thinking this could be as simple as just returning the value of the autoCommit flag. I suppose it might be interesting to have it return the type of the transaction, but that would be strictly for extra credit. _2005-May-18 15:20:00 by anonymous:_ {linebreak} Actually, what we probably really want is a call that tells us the value of the autoCommit flag.
#f2dcdc 2755 code active 2007 Nov anonymous 2007 Nov 3 3 trace interfere with transaction Tcl interface When using the transaction method of the Tcl interface to the SQLite with a registered "trace" function, the stack trace is lost in case an error occurs inside the transaction. As an example I provide two outputs, the first one without a registered trace function and the second one with one (in which it *cannot* be seen where the exception cames from): ========= First: > ./a.tcl vorher BUMMM while executing "a" invoked from within "db transaction { puts "vorher" a puts "nachher" }" ("uplevel" body line 1) invoked from within "uplevel 1 [list db transaction { puts "vorher" a puts "nachher" }]" (procedure "b" line 2) invoked from within "b" (file "./a.tcl" line 28) ========= Second: > ./a.tcl BEGIN vorher ROLLBACK while executing "db transaction { puts "vorher" a puts "nachher" }" ("uplevel" body line 1) invoked from within "uplevel 1 [list db transaction { puts "vorher" a puts "nachher" }]" (procedure "b" line 2) invoked from within "b" (file "./a.tcl" line 28) ******** A scritp that demostrates this behaviour is attached. The only workaround is not to trace. Thanks
#f2dcdc 2352 code active 2007 May anonymous 2007 May 5 3 timeout just 500 msec to soon After upgrading from 3.3.12 to 3.3.17, the setting of a timeout behaves differently. It occurs exactly 500 msecs sooner. Of course assuming the database is locked and the timeout is set to a value larger than 500 msecs.
#e8e8bd 2200 new active 2007 Jan anonymous 2007 Jan 5 4 threadsafe status not reported in .pc file Some application based on sqlite (for exampe Tracker indexing and searching tool) could need the threadsafe. Currently there in no way to know the status of threadsafe of installed sqlite. It could be good add a "threadsafe" variable in sqlite3.pc like libdir=${exec_prefix}/lib includedir=${prefix}/include threadsafe=yes that developers could query using: $ pkg-config --variable=threadsafe sqlite3 yes
#f2dcdc 2886 code active 2008 Jan anonymous 2008 Jan 3 3 testfixture: -fPIC needed when building extension(s) (this fix/change is probably needed in older versions too, i meant to send this in earlier) -fPIC is needed when building extensions (some platforms don't need this or don't care --- x86-64 does) diff --git a/test/loadext.test b/test/loadext.test index 81e152f..2a7fa2e 100644 --- a/test/loadext.test +++ b/test/loadext.test @@ -64,7 +64,7 @@ if {![file exists $testextension]} { set srcdir [file dir $testdir]/src set testextsrc $srcdir/test_loadext.c if {[catch { - exec gcc -Wall -I$srcdir -I. -g -shared $testextsrc -o $testextension + exec gcc -Wall -fPIC -I$srcdir -I. -g -shared $testextsrc -o $testextension } msg]} { puts "Skipping loadext tests: Test extension not built..." puts $msg
#f2dcdc 2705 code active 2007 Oct anonymous 2007 Oct 4 4 testfixture unresolved externals with SQLITE_OMIT_GET_TABLE Cannot build/run "make test" with -DSQLITE_OMIT_GET_TABLE due to testfixture link error: In function `test_get_table_printf': ./src/test1.c:526: undefined reference to `sqlite3_get_table' ./src/test1.c:541: undefined reference to `sqlite3_free_table' collect2: ld returned 1 exit status make: *** [testfixture] Error 1
#f2dcdc 1838 code active 2006 Jun anonymous 2006 Jun 5 5 test types3-1.3 fails on 64-bit Linux I just compiled 3.3.6 on my 64-bit Linux system (OpenSuse 10, using a self-compiled Tcl 8.4.11) and got one failure from "make test": types3-1.3... Expected: [wideInt integer] Got: [int integer] This seems to be an error in the test suite itself: Changing set V [expr {1+123456789012345}] to set V [expr {wide(1+123456789012345)}] gets rid of the failure.
#f2dcdc 1056 code active 2004 Dec anonymous Shell 2004 Dec 3 3 test pragma-9.4 fails during second pass in "make fulltest" During a "make fulltest" run, the pragma tests appear to run twice. On the first run, pragma-9.4 runs properly. On the second run, it gives an error: pragma-9.4... Expected: [] Got: [/Volumes/Local/Users/sqlite/test/bld] (where the path listed is the build directory for this build of sqlite). The pragma-9.4 test is a recent addition to sqlite. This is currently the only failure I'm seeing in a "make fulltest" of the current cvs tree on Mac OS X when the build/test directory is on a hard drive. 1 errors out of 68411 tests Failures on these tests: pragma-9.4 make: *** [fulltest] Error 1
#e8e8bd 2469 build active 2007 Jun anonymous 2007 Jul 1 1 test fails on Solaris I have a problem running the test suite on Solaris 9. Build was done using gcc 4.2.0. The build completes without error but many tests fail. I've created my own minimal test that exhibits the problem: set testdir [file dirname $argv0] source $testdir/tester.tcl db close file delete -force test.db test.db-journal sqlite db test.db do_test tdb-1 { execsql { PRAGMA auto_vacuum = 1; BEGIN; CREATE TABLE t1(a, b); } execsql { COMMIT; } } {} integrity_check tdb-2 finish_test
When running this test I get the following output: tdb-1... Ok tdb-2... Expected: [ok] Got: [{*** in database main *** List of tree roots: 2nd reference to page 1 Page 3 is never used}] Thread-specific data deallocated properly 1 errors out of 3 tests Failures on these tests: tdb-2
This error happens on lots of, but not all, tests. I'm happy to do whatever is necessary to help debug this. Thanks, Tim. _2007-Jun-27 10:44:14 by anonymous:_ {linebreak} Further to this, it appears to be related to gcc 4.2.0. It works fine with gcc 3.4.6. ---- _2007-Jun-28 09:54:35 by anonymous:_ {linebreak} Further more, it doesn't appear to be specific to Solaris. The same problem occurs on Linux with gcc 4.2.0. So I guess the subject of this ticket should be changed to "build/test problems with gcc 4.2.0". This is probably a significant problem - the build completes find but the resultant code is broken. People may not notice this until it's too late. ---- _2007-Jun-28 12:24:05 by drh:_ {linebreak} I installed gcc 4.2.0 on my SuSE linux i686 desktop and built test harnesses under three different configurations: gcc420 -g -O0 -Wall -fstrict-aliasing gcc420 -g -O3 -Wall gcc420 -g -O3 -fstrict-aliasing -fomit-frame-pointer The first two configurations used separate source files. The third configuration was built using the amalgamation. I ran the "quick" test under all configurations. All tests ran to completion with no errors. ---- _2007-Jun-28 13:22:20 by anonymous:_ {linebreak} Two ideas: 1. Compile with gcc 4.2.0 using -O0 instead of -O2 and see what happens. Disable any other optimizations you may have. 2. Run truss with full read/write buffer display on the gcc 3.4.6 compiled testfixture running your simple test case and compare its output to the gcc 4.2.0 compiled test case. ---- _2007-Jul-01 19:00:40 by anonymous:_ {linebreak} I've done tests with optimisation, and this appears to tickle the problem. With no optimisation, -O, -O0, -O1 and -03 it works. With -O2 and -Os it's broken. I was compiling with -O2 when I submitted the initial report. Tim. ---- _2007-Jul-01 19:54:54 by drh:_ {linebreak} I can reproduce the problem now on Linux when compiling as follows: gcc420 -g -O2 -Wall ---- _2007-Jul-01 21:50:42 by drh:_ {linebreak} This appears to be a bug in GCC 4.3.0. A work-around is to compile with the -fno-tree-vrp option. GCC appears to miscompile a single loop within the logic that implements the integrity_check PRAGMA. The code that gets miscompiled is in the file vdbe.c (lines numbers added): 4308 for(j=0; j RCS file: /sqlite/sqlite/src/vdbe.c,v retrieving revision 1.636 diff -u -3 -p -r1.636 vdbe.c --- src/vdbe.c 1 Jul 2007 21:18:40 -0000 1.636 +++ src/vdbe.c 21 Jul 2007 19:10:13 -0000 @@ -4306,7 +4306,8 @@ case OP_IntegrityCk: { pnErr = &p->aMem[j]; assert( (pnErr->flags & MEM_Int)!=0 ); for(j=0; ju.i; } aRoot[j] = 0; popStack(&pTos, nRoot);