bgcolor # Type Status Created By Subsys Changed Assigned Svr Pri Title _Description _Remarks #f2dcdc 2914 code active 2008 Jan anonymous 2008 Jan 3 3 ATTACH returns SQLITE_ERROR when it means SQLITE_BUSY I'm seeing the same behavior as in #2096, with SQLite 3.5.4. ATTACH DATABASE fails with SQLITE_ERROR rather than SQLITE_BUSY when the database to be attached, or the main database of the connection being attached to, is EXCLUSIVE-locked by another database connection. For added confusion, sqlite3_errmsg() says "database is locked" when the ATTACH is done via sqlite3_exec(), but "SQL logic error or missing database" when the ATTACH is done via sqlite3_step(). As a result of this bug, it is difficult to distinguish between fatal and transient ATTACH errors, particularly when sqlite3_step() is used. I am attaching a test program that demonstrates the problem. #f2dcdc 2905 code active 2008 Jan anonymous 2008 Jan pweilbacher 2 2 mutex_os2.c - incorrect mutex implementation The OS/2 version of sqlite3_mutex_alloc() is badly broken. It creates named mutexes which, by design, are global rather than process-specific as intended. This might be minimally acceptable except that the function reuses the same name every time it attempts to create a SQLITE_MUTEX_FAST or SQLITE_MUTEX_RECURSIVE. The result is that every call returns the exact same semaphore to every thread in every process using sqlite3. Once this mutex is owned by one process, other processes calling sqlite3_mutex_enter() will be blocked. Much the same is true for the static mutexes. Every process ends up using the exact same SQLITE_MUTEX_STATIC_MASTER, SQLITE_MUTEX_STATIC_MEM, etc. There's another flaw that is fairly minor compared with the above: in an attempt to avoid concurrency when creating the static mutexes, this function uses an API call that is thoroughly deprecated. The attached patch remedies all of these issues. Since the logic that protects the creation of the static mutexes may not be self-evident, here's an explanation: The existence (or non-existence) of a given named mutex is itself a semaphore. If the isInit flag is false, the code attempts to create a mutex whose name is unique to that process. If the attempt is successful, there are two possibilities: (1) either the current thread is the first to reach this code & may proceed; (2) or while the current thread was making its preparations, another thread created the mutex, did the init, then closed the mutex. Testing isInit immediately after creating the mutex determines which possibility is valid. If mutex creation fails due to a duplicate name, then another thread is currently performing the init. In this case, the current thread simply has to wait a while until the other thread is done & isInit becomes true. Submitted by Rich Walsh (richATe-vertise.com) #f2dcdc 2902 code active 2008 Jan anonymous 2008 Jan drh 3 3 Add watch support to SQLite SQLite currently provides only TRIGGERs and the update_hook() as a way for applications to stay informed about changes to the database. But both of these alternatives do not provide enough details about the actual changes to the underlying database file(s). We've prepared a patch for SQLite 3.5.x to allow applications to install a watch_hook into the database, that will be invoked everytime the database is changed with exact details about the change that was performed. _2008-Jan-22 16:06:59 by anonymous:_ {linebreak} Great idea and nice job. This functionality is very useful. ---- _2008-Jan-31 18:27:16 by anonymous:_ {linebreak} Any chance to get this committed for the next release (i.e. 3.5.6)? ---- _2008-Jan-31 19:38:35 by drh:_ {linebreak} Unlikely, for two reasons: 1: I am unconvinced that this patch solves a problem that needs solving. It is vitally important to a project like SQLite that we work to avoid clutter and cruft. That means that any change must have a compelling rational or else it is rejected. 2: The patches are against version 3.5.4. There were many changes to the core for 3.5.5 and the patches no longer work. ---- _2008-Jan-31 20:58:55 by anonymous:_ {linebreak} We can (and will) port the changes to 3.5.5, so the second point will be done. First the first point, I'm not sure how many projects will actually need this functionality, but I guess there are quite a lot of projects that would benefit, and for the others, there's zero overhead due to this patch. ---- _2008-Jan-31 21:21:30 by drh:_ {linebreak} There is a lot of overhead for me because if I accept this patch, that means I have to maintain it forever. Most of the work is in maintenance, not coming up with the original patch. #e8e8bd 2912 new active 2008 Jan anonymous 2008 Jan 5 4 merge join Would it be possible to implement merge joins? #f2dcdc 2903 code active 2008 Jan anonymous 2008 Jan 2 2 tclinstaller.tcl fails on path and permissions issue When compiling using custom PREFIX, pointing to private directory, tclinstaller.tcl fails, because it tries to remove contents from /usr/share/tcl8.4/sqlite3.
./configure --prefix=/my/private/sqlite/sqlite-3.5.4 ... # success make ... # success make install ... tclsh ./tclinstaller.tcl 3.5 error deleting "/usr/share/tcl8.4/sqlite3": not owner while executing "file delete -force $LIBDIR/sqlite3" (file "./tclinstaller.tcl" line 17) make: *** [tcl_install] Error 1I've found two work-arounds: 1: If you run make install as root. 2: If you use ./configure --disable-tcl _2008-Jan-28 17:47:39 by anonymous:_ {linebreak} I also ran into this problem. make install as root will end up copying files into the system's library directory and is almost certainly not what you want if you specified your own --prefix. #f2dcdc 2911 code active 2008 Jan anonymous 2008 Jan 2 2 Adding parentheses to a FROM clause Hi, Parentheses in a FROM statement seem to mess with the ability to use table aliases in the "what" part. Here is an example: Start SQLite: $ sqlite3 employee.db SQLite version 3.5.4 Enter ".help" for instructions create a couple of tables and populate them with test data: sqlite> create table person (id integer, name text, employerid integer); sqlite> create table employer (id integer, name text); sqlite> insert into person (id, name, employerid) values (1, "Dave", 1); sqlite> insert into employer (id, name) values (1, "ACME"); Run a simple query with *no parentheses* in the FROM statement: sqlite> select b.id from person as a inner join employer as b on a.employerid = b.id; 1 Everything works as expected. Now, repeat that query *with parentheses*: sqlite> select b.id from (person as a inner join employer as b on a.employerid = b.id); SQL error: no such column: b.id There you have it. This may be related to ticket #1822, although that ticket deals with aliases and subqueries. This problem seems to be more fundamental. Many thanks, -- Dave #e8e8bd 2909 build active 2008 Jan aswift 2008 Jan aswift 4 4 Omit the _XOPEN_SOURCE 500 define on Mac OS-X (check for __APPLE__) sqliteInt.h attempts to avoid setting _XOPEN_SOURCE on MacOSX by checking if __DARWIN__ is defined, however on Leopard (as of Mac OS X 10.5) this test fails and should be enhanced by testing for both __DARWIN__ and __APPLE__ Building sqlite succeeds with _XOPEN_SOURCE defined, but it causes _POSIX_C_SOURCE to be defined which leads to failing to define F_FULLFSYNC which prevents use of the fullfsync pragma. #f2dcdc 2907 code active 2008 Jan anonymous 2008 Jan 1 1 Issues of sqlite3 with Windows Mobile 5/6 hi. we are currently using sqlite3 for our mobile application. it has been running without a hitch on pocket pc 2003 and previous versions. come windows mobile 5 and 6 we have been getting errors, although not consistent yet. one example is 'EXCEPTION_DATATYPE_MISALIGNMENT'. another is 'SELECT STATMENTS TO THE LEFT AND RIGHT OF UNION ARE NOT EQUAL'. i was wondering if you have any known compatibility issues of your product with this version of windows mobile. thanks in advance. _2008-Jan-28 13:26:26 by anonymous:_ {linebreak} EXCEPTION_DATATYPE_MISALIGNMENT is thrown when you try to use and Odd pointer address. I wrote a custom allocator for WinCE/ARM platform, and I have to take care about memory alignment (I used to align at 2 bytes, and at that time it solved the problem) #f2dcdc 2908 code active 2008 Jan anonymous 2008 Jan 3 3 Add support to examine whether statements modify the database Currently there is no way to check whether a compiled statement will modify the database when being executed. Of course, there is the work-around of misusing the authorizer callback for this purpose, but this is kinda error prone and causes quite some overhead for such a simple purpose. #f2dcdc 2898 code active 2008 Jan anonymous 2008 Jan 1 1 Latest CVS for 3.5.4 fails to build test1.c gcc -pipe -O3 -g -Wall -DSQLITE_DISABLE_DIRSYNC=1 -I. -I../src -DNDEBUG -I/usr/include -DSQLITE_THREADSAFE=1 -DSQLITE_THREAD_OVERRIDE_LOCK=1 -DSQLITE_OMIT_LOAD_EXTENSION=1 -DTCLSH=1 -DSQLITE_TEST=1 -DSQLITE_CRASH_TEST=1 -DSQLITE_NO_SYNC=1 -DTEMP_STORE=1 -o .libs/testfixture ../src/attach.c ../src/btree.c ../src/build.c ../src/date.c ../src/expr.c ../src/func.c ../src/insert.c ../src/malloc.c ../src/os.c ../src/os_os2.c ../src/os_unix.c ../src/os_win.c ../src/pager.c ../src/pragma.c ../src/prepare.c ../src/printf.c ../src/select.c ../src/test1.c ../src/test2.c ../src/test3.c ../src/test4.c ../src/test5.c ../src/test6.c ../src/test7.c ../src/test8.c ../src/test9.c ../src/test_autoext.c ../src/test_async.c ../src/test_btree.c ../src/test_config.c ../src/test_hexio.c ../src/test_malloc.c ../src/test_md5.c ../src/test_onefile.c ../src/test_schema.c ../src/test_server.c ../src/test_tclvar.c ../src/test_thread.c ../src/tokenize.c ../src/utf.c ../src/util.c ../src/vdbe.c ../src/vdbeapi.c ../src/vdbeaux.c ../src/vdbemem.c ../src/where.c parse.c ../src/tclsqlite.c ./.libs/libsqlite3.so -L/usr/lib64 -ltcl8.4 -ldl -lpthread -lieee -lm -Wl,--rpath -Wl,/common/pkgs/sqlite-3.5.4.3/lib ../src/build.c: In function 'sqlite3RefillIndex': ../src/build.c:2275: warning: cast to pointer from integer of different size ../src/func.c: In function 'trimFunc': ../src/func.c:919: warning: cast from pointer to integer of different size ../src/func.c: In function 'sqlite3RegisterBuiltinFunctions': ../src/func.c:1464: warning: cast to pointer from integer of different size ../src/func.c:1483: warning: cast to pointer from integer of different size ../src/insert.c: In function 'sqlite3GenerateConstraintChecks': ../src/insert.c:1200: warning: cast to pointer from integer of different size ../src/insert.c:1034: warning: 'j2' may be used uninitialized in this function ../src/insert.c: In function 'sqlite3Insert': ../src/insert.c:373: warning: 'regFromSelect' may be used uninitialized in this function ../src/test1.c: In function 'test_collate_func': ../src/test1.c:2085: warning: cast from pointer to integer of different size ../src/test1.c: In function 'test_collate_needed_cb': ../src/test1.c:2209: warning: cast to pointer from integer of different size ../src/test1.c: In function 'alignmentCollFunc': ../src/test1.c:2258: warning: cast from pointer to integer of different size ../src/test1.c:2259: warning: cast from pointer to integer of different size ../src/test8.c: In function 'echoBestIndex': ../src/test8.c:722: warning: 'nRow' may be used uninitialized in this function ../src/vdbe.c: In function 'sqlite3VdbeExec': ../src/vdbe.c:502: warning: 'pOut' may be used uninitialized in this function ../src/vdbe.c:501: warning: 'pIn3' may be used uninitialized in this function ../src/vdbe.c:501: warning: 'pIn2' may be used uninitialized in this function ../src/vdbe.c:501: warning: 'pIn1' may be used uninitialized in this function ../src/vdbeaux.c: In function 'sqlite3VdbeChangeP4': ../src/vdbeaux.c:529: warning: cast from pointer to integer of different size ../src/vdbemem.c: In function 'sqlite3ValueText': ../src/vdbemem.c:911: warning: cast from pointer to integer of different size /tmp/ccsuOeus.o: In function `reset_prng_state': /build/work/sqlite-3.5.4.3/bld/../src/test1.c:4280: undefined reference to `sqlite3ResetPrngState' /tmp/ccsuOeus.o: In function `restore_prng_state': /build/work/sqlite-3.5.4.3/bld/../src/test1.c:4267: undefined reference to `sqlite3RestorePrngState' /tmp/ccsuOeus.o: In function `save_prng_state': /build/work/sqlite-3.5.4.3/bld/../src/test1.c:4254: undefined reference to `sqlite3SavePrngState' collect2: ld returned 1 exit status make: *** [testfixture] Error 1 _2008-Jan-17 23:54:58 by anonymous:_ {linebreak} Problem appears to be here in libsqlite.3.so.0.8.6 as shown by: nm -A .libs/libsqlite3.so.0.8.6 | grep sqlite3ResetPrngState which shows no entry point. And: nm -A .libs/random.o | grep sqlite3ResetPrngState which also shows no entry point. ---- _2008-Jan-17 23:56:55 by anonymous:_ {linebreak} Ah... It appears -DSQLITE_TEST should be passed when building test1.c and left off when building prior to install. ---- _2008-Jan-21 20:16:00 by anonymous:_ {linebreak} In the makefile the right flag appears to be set, it's just not making it through to the compile for some reason. ---- _2008-Jan-21 20:16:24 by anonymous:_ {linebreak} Still fails the same based on today's cvs update. ---- _2008-Jan-23 03:14:49 by anonymous:_ {linebreak} This bug fixed as of latest cvs pull #f2dcdc 2901 code active 2008 Jan anonymous 2008 Jan 3 3 ROLLBACK and COMMIT statements should not expire Currently, whenever a statement changes the schema of the database, all prepared statements will be expired, no matter whether they actually need to be prepared again or not. This is especially problematic for ROLLBACK statements in a multi-statement transaction. Currently there is no way to guaranty that a multi-statement transaction can at least be rolled back in case of an error, because one has to (re)prepare the ROLLBACK statement to roll back the transaction, which can fail because of OOM (in a multi-threaded application). #f2dcdc 2899 code active 2008 Jan anonymous 2008 Jan 4 4 sqlite3_reset() after exec() takes > 100 ms to complete I'm not entirely sure whether this is a bug or not... I'm not familiar enough with SQLite to know if this is way too unconventional, but I noticed today that running exec() in conjunction with a prepared statement really kills the performance of sqlite3_reset(), if it's called after exec(): // init sqlite3_prepare_v2( db,{linebreak} "SELECT [file_id],[file_name],[file_mime],[file_type],"{linebreak} "[file_size],[date_created],[date_accessed],[data] "{linebreak} "FROM file_cache WHERE [file_id] = ? LIMIT 1;",{linebreak} -1, &sqQueryStatement, &unused ); // query function -- called repeatedly (usually second or third run starts to cause big delays) /* begin function */ sqlite3_reset( sqQueryStatement );{linebreak} sqlite3_bind_int( sqQuerystatement, 1, 292 );{linebreak} sqlite3_step( sqQueryStatement );{linebreak} bla = sqlite3_column_int( sqQueryStatement, 0 ); /* ... */ char *erm; sqlite3_exec( db, "UPDATE file_cache SET [date_accessed] = DATETIME( 'NOW', 'LOCALTIME' ) WHERE [file_id] = 292", NULL, NULL, &erm ); /* ... */ sqlite3_clear_bindings( sqQueryStatement ); /* end function */ If sqlite3_exec() is commented out, consecutive calls to the function run in less than a millisecond. With sqlite3_exec() included, sqlite3_reset() call in this function takes > 100 ms to complete. Tested: Windows Vista Ultimate 32 bit, Visual Studio 2005 8.0.50727.876 #f2dcdc 2885 code active 2008 Jan anonymous 2008 Jan 4 4 (minor) fulltest failures Minor noise in fulltest on a 64-bit machine (everything seems to work otherwise though): 4 errors out of 61527 tests Failures on these tests: exclusive-ioerr-2.280.4 exclusive-ioerr-2.281.4 exclusive-ioerr-2.282.4 incrvacuum-ioerr-1.31.4 All memory allocations freed - no leaks details: exclusive-ioerr-2.280.3... Ok exclusive-ioerr-2.280.4... Expected: [8ee59fe0b5bc391ecc5002539379b063] Got: [35e56178ad878809d4789c5797265a23] exclusive-ioerr-2.281.1... Ok exclusive-ioerr-2.281.2... Ok exclusive-ioerr-2.281.3... Ok exclusive-ioerr-2.281.4... Expected: [95762fb35ef83ddba65f681325346ef2] Got: [1c20c63d975ad85b395a5e7701d785c2] exclusive-ioerr-2.282.1... Ok exclusive-ioerr-2.282.2... Ok exclusive-ioerr-2.282.3... Ok exclusive-ioerr-2.282.4... Expected: [2c5ea7db8424f38d17cdff41056da0e0] Got: [c02841c40d3e6e0579922745bd9c0260] exclusive-ioerr-2.283.1... Ok [...] incrvacuum-ioerr-1.31.4... Expected: [ea55042a449c3b4759730a882e8271a0] Got: [9951814984696d0811cacbe862060af8] incrvacuum-ioerr-1.32.1... Ok _2008-Jan-19 00:56:30 by anonymous:_ {linebreak} A test from 20 minutes ago passes cleanly. This could be closed. #f2dcdc 2878 code active 2008 Jan anonymous 2008 Jan 1 1 Memory leaks with latest CVS [4693] This SQL leaks memory with CVS [4693]: CREATE TABLE x(id integer primary key, a TEXT NULL); INSERT INTO x (a) VALUES ('first'); CREATE TABLE tempx(id integer primary key, a TEXT NULL); INSERT INTO tempx (a) VALUES ('t-first'); CREATE VIEW tv1 AS SELECT x.id, tx.id FROM x JOIN tempx tx ON tx.id=x.id; One leak is caused by "CREATE TABLE tempx", a second one by "CREATE VIEW tv1". The above SQL is a digest of select7.test, select7-2.1. _2008-Jan-14 17:51:11 by anonymous:_ {linebreak} I have tested again with CVS [4711] and it does no longer show the original leaks. I therefore consider this issue fixed and I will now close this ticket. ---- _2008-Jan-14 23:56:34 by anonymous:_ {linebreak} Doing a fulltest with -MSQLITE_MEMDEUG I see reports of memory leaks. I assume these are of little or minimal interest at present because of the amount of code flux. If you do want details, please let me know (I'll recheck this ticket tomorrow I guess). ---- _2008-Jan-15 08:23:56 by anonymous:_ {linebreak} Thanks for the follow-up. I am not running the original test-suite but have have ported a great number of them to Delphi. If you could just let me know which tests caused the leaks you fixed in [4712] I'd be more than glad the port these test as well and let you know my findings. ---- _2008-Jan-19 00:56:17 by anonymous:_ {linebreak} A test from 20 minutes ago passes cleanly. This could be closed. #f2dcdc 2897 code active 2008 Jan anonymous 2008 Jan 1 1 String or BLOB exceed size limit This error was shown after attemp to read script from SQLite 3.5.4 shell in order to recreate old DB. Details: 1. Database was created with SQLite 3.3.4. Around 20 standard fieds and one BLOB. 2. The only one existed table was dumped with shell of SQLite 3.5.4. SQL script seems to be coorrect. 3. Opened SQLite 3.5.4 and read script in new DB. The error "String or BLOB exceed size limit" are sown for several lines. Many records missing. 4. Attempted to dump table with shell of version 3.3.6 (have no more 3.3.4 shell) and read into new DB with 3.5.4 shell The same errors are shown. The same steps was attempted with 3.3.6. shell only. All seems to be correct. _2008-Jan-17 20:23:25 by drh:_ {linebreak} This size limit on BLOBs in SQLite version 3.5.4 is 1GB. How big is your blob, exactly? ---- _2008-Jan-17 22:22:24 by anonymous:_ {linebreak} BLOB in each record is no more than few MB. Mostly it is few KB (e-client and news application). Whole DB have around 200MB. ---- _2008-Jan-18 02:28:11 by drh:_ {linebreak} This issue is probably resolved by check-in [4636], then. ---- _2008-Jan-18 14:28:13 by anonymous:_ {linebreak} If directive SQLITE_MAX_SQL_LENGTH is not defined it is set to 1,000,000 (10^6) in amalgamation code of 3.5.4. #e8e8bd 2892 new active 2008 Jan anonymous 2008 Jan 5 5 There should be a way in the api to read more precise error message. Currently all errors in sql queries issued via C,C++ api result in error code SQLITE_ERROR = 1 and message "SQL error or missing database". That leaves user completely clueless what mistake in his sql he actually made. Some evidence of confusion can be found here: http://bugs.php.net/bug.php?id=33117 _2008-Jan-16 17:41:00 by drh:_ {linebreak} SQLite gives detailed error information for SQL syntax or logic errors. (Try, for example, entering invalid SQL into the CLI.) I think perhaps that PHP is simply failing to to access those errors and is instead picking up some other error indication from someplace else. ---- _2008-Jan-16 21:59:17 by anonymous:_ {linebreak} Well I shall retest it, but I got the same error message upon a duplicate key in Delphi. Although I'm aware of an enhanced api to get the errormessage. ---- _2008-Jan-17 00:05:58 by drh:_ {linebreak} What error message do you get from the CLI? #f2dcdc 2893 code active 2008 Jan anonymous 2008 Jan 1 1 incorrect integer range tests recently a function that performs integer range tests was added to the cvs (check-in [4706]), but if i am correct there is a problem in the return value of the function in the file vdbemem.c: static i64 doubleToInt64(double r){ ... if( r<(double)minInt ){ return minInt; }else if( r>(double)maxInt ){ return minInt; <-- is this correct, shouldn't it be maxInt? }else{ return (i64)r; } } _2008-Jan-16 17:33:56 by drh:_ {linebreak} See the remarks on ticket #2280. The code duplicates the behavior of the FPU on x86. ---- _2008-Jan-16 18:21:28 by anonymous:_ {linebreak} did you mean ticket #2880? didn't read that ticket before, but since there was no comment regarding that behavior in the function it seemed (to my eyes) that it was a mistake. maybe adding a small comment in there would clarify this issue ---- _2008-Jan-16 18:39:42 by anonymous:_ {linebreak} Just because the double to int overflow behavior happens to be that way with GCC on x86, is it desirable? #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 2882 code active 2008 Jan anonymous 2008 Jan 3 3 fulltest failure: ./testfixture: wrong # args: should be "cksum db" exclusive-ioerr-2.2.1... Ok ./testfixture: wrong # args: should be "cksum db" while executing "ifcapable vacuum { do_ioerr_test ioerr-2 -cksum true -sqlprep { BEGIN; CREATE TABLE t1(a, b, c); INSERT INTO t1 VALUES(1, randstr(50,..." (file "../test/ioerr.test" line 58) invoked from within "source $testdir/ioerr.test" (file "../test/exclusive3.test" line 50) invoked from within "source $testfile" ("foreach" body line 5) invoked from within "foreach testfile [lsort -dictionary [glob $testdir/*.test]] { set tail [file tail $testfile] if {[lsearch -exact $EXCLUDE $tail]>=0} continue ..." ("for" body line 7) invoked from within "for {set Counter 0} {$Counter<$COUNT && $nErr==0} {incr Counter} { if {$Counter%2} { set ::SETUP_SQL {PRAGMA default_synchronous=off;} } else ..." (file "..//test/all.test" line 85) _2008-Jan-14 23:25:49 by anonymous:_ {linebreak} The latest code seems to have fixed this. I would close this but I don't see how to do that. #e8e8bd 2884 new active 2008 Jan anonymous 2008 Jan 4 4 Way to find out limits The page http://www.sqlite.org/limits.html shows some limits that SQLite has. They are consts in the code, and defaults are listed, but there doesn't seem to be any way to find out what values a particular binary was built with. For example, I've gotten SQLite as part of my OS or another program, and I don't know what value of SQLITE_MAX_COLUMN was used to compile this SQLite. I'm trying to track down a bug in my code, and if this value was too small, that would explain it. (In truth, it's probably not the cause, but I'd like to rule it out.) I'd like a way to print the value of these limits at runtime. It doesn't have to have a stable API -- it would mostly be useful for debugging. If the command-line client had a command ".limits" that printed all of these values, that would be super. #e8e8bd 2881 build active 2008 Jan anonymous 2008 Jan 1 1 Latest sqlite-3.5.4 build fail on latest Fedora 2.6.23.12-52.fc7 Two test cases fail. io-4.1... Expected: [3] Got: [2] io-4.2.1... Ok io-4.2.2... Ok io-4.2.3... Expected: [3] Got: [2] io-4.3.1... Ok Let me know how to run individual test cases and how this might be fixed. Here's how I built sqlite using latest CVS. If something is wrong here, let me know and I'll rebuild/retest. I'm building on latest Fedora fc7. Thanks. _______ net1#uname -a Linux net1.coolsurf.com 2.6.23.12-52.fc7 #1 SMP Tue Dec 18 20:27:10 EST 2007 x86_64 x86_64 x86_64 GNU/Linux net1#build_sqlite mkdir -p /build/work/sqlite-3.5.4 cd /build/work/sqlite-3.5.4 unset CDPATH export CFLAGS='-pipe -O3 -g -DSQLITE_DISABLE_DIRSYNC=1 -Wall' rm -rf bld cvs -d :pserver:anonymous@www.sqlite.org:/sqlite -r update . mkdir bld cd bld ../configure --prefix=/common/pkgs/sqlite-3.5.4 --enable-tcl --with-tcl=/usr/lib64 --enable-threadsafe --enable-threads-override-locks make groupadd vuser || /bin/true useradd -M -g vuser -d /vhost/davidfavor.com/users/david -s /bin/zsh david || /bin/true useradd -M -g vuser -d /vhost/livefeast.com/users/yemiah -s /bin/zsh yemiah || /bin/true chown david:vuser -R .. su -c "make test" david _2008-Jan-11 17:18:52 by anonymous:_ {linebreak} Same tests still fail with CVS of today around 11AM CST. ---- _2008-Jan-11 17:41:55 by drh:_ {linebreak} FWIW, both those test cases pass on SuSE 10.1. I do not understand why they are failing on Fedora. But in any event, the tests in question are verifying logic that implements an optimization that is not used on Fedora, ever. So the failures are of no consequence. If those are the only two tests that fail, then you can safely use the build for whatever it is you are trying to do. ---- _2008-Jan-11 19:16:19 by anonymous:_ {linebreak} Failures when 'make fulltest' built with CFLAGS of '-pipe -O3 -g -Wall -DSQLITE_DISABLE_DIRSYNC=1 -DSQLITE_MEMDEBUG' exclusive-malloc-1.transient.746...make: *** [fulltest] Segmentation fault Failures when 'make fulltest' built with CFLAGS of '-pipe -O3 -g -Wall -DSQLITE_DISABLE_DIRSYNC=1' Skipping malloc tests: not compiled with -DSQLITE_MEMDEBUG... 6 errors out of 61998 tests Failures on these tests: exclusive-ioerr-2.280.4 exclusive-ioerr-2.281.4 exclusive-ioerr-2.282.4 incrvacuum-ioerr-1.31.4 io-4.1 io-4.2.3 All memory allocations freed - no leaks Maximum memory usage: 14376554 bytes Pre 3.5.x builds work fine on Fedora. If you're open to debugging all these, I'd like to go through and resolve all these one by one, so Fedora has a clean build/fulltest. Please let me know how to run each test individually and I'll try to figure out the problem with each. Thanks. #f2dcdc 2879 code active 2008 Jan anonymous 2008 Jan anonymous 4 3 VACUUM enters temporary sequence numbers in sqlite_sequence I have two TEMPORARY tables added to a database, that both contain an INTEGER PRIMARY KEY AUTOINCREMENT field. After issuing a VACUUM command, the maximum value of these fields is added to the sqlite_sequence table. And stay there after the connection closes. #f2dcdc 2875 code active 2008 Jan anonymous 2008 Jan 3 2 LIKE does not work with lowercase swedish characters Swedish letters å,ä,ö is not supported by the LIKE statement. When trying to perform a query like SELECT * FROM table WHERE name LIKE "å%" we will not get a match for names starting on Å (which is uppercase for å). To recreate: ============================================== SQLite version 3.5.4 Enter ".help" for instructions sqlite> CREATE TABLE TestingTable(Name varchar(20)); sqlite> INSERT INTO TestingTable values ('Sweden'); sqlite> INSERT INTO TestingTable values ('sweden'); sqlite> INSERT INTO TestingTable values ('Åland'); sqlite> INSERT INTO TestingTable values ('åland'); sqlite> SELECT * FROM TestingTable; Sweden sweden Åland åland sqlite> SELECT * FROM TestingTable WHERE Name LIKE "swe%"; Sweden sweden sqlite> SELECT * FROM TestingTable WHERE Name LIKE åla%"; åland ============================================================ #f2dcdc 2874 code active 2008 Jan anonymous 2008 Jan 1 1 THREADSAFE #define HAVE_LOCALTIME_R, HAVE_GMTIME_R in os_unix.c The precompiled shared sqlite3 library for Linux on sqlite.org which appears to be built with pthread support is using localtime and gmtime which are not threadsafe. For THREADSAFE builds could either configure be changed to detect the functions gmtime_r and localtime_r or change os_unix.c to explicitly #define HAVE_LOCALTIME_R and HAVE_GMTIME_R? #f2dcdc 2873 code active 2008 Jan anonymous 2008 Jan 1 1 HAVE_USLEEP, HAVE_FDATASYNC=1 detected but not used by configure; make I noticed that a couple of open source projects were not picking up usleep() for recent sqlite builds and used the coarser grained sleep() instead. Around 11 months ago something changed in sqlite's build process. It seems that both -DHAVE_USLEEP=1, -DHAVE_FDATASYNC=1 and -DOS_UNIX=1 are detected correctly by configure but not used by the generated Makefile. As result, UNIX builds of sqlite3 via ./configure do not use usleep() and fdatasync() and do not define OS_UNIX. I don't know whether the lack of fdatasync() versus the default fsync() affects anyone. Please apply the following patch which corrects the problem with "./configure && make". Thank you.
Index: configure =================================================================== RCS file: /sqlite/sqlite/configure,v retrieving revision 1.45 diff -u -3 -p -r1.45 configure --- configure 27 Nov 2007 14:50:07 -0000 1.45 +++ configure 5 Jan 2008 07:41:00 -0000 @@ -18520,9 +18520,9 @@ if test "$TARGET_EXEEXT" = ".exe"; then OS_UNIX=0 OS_WIN=0 OS_OS2=1 - TARGET_CFLAGS="$TARGET_CFLAGS -DOS_OS2=1" + CFLAGS="$CFLAGS -DOS_OS2=1" if test "$ac_compiler_gnu" == "yes" ; then - TARGET_CFLAGS="$TARGET_CFLAGS -Zomf -Zexe -Zmap" + CFLAGS="$CFLAGS -Zomf -Zexe -Zmap" BUILD_CFLAGS="$BUILD_CFLAGS -Zomf -Zexe" fi else @@ -18530,14 +18530,14 @@ if test "$TARGET_EXEEXT" = ".exe"; then OS_WIN=1 OS_OS2=0 tclsubdir=win - TARGET_CFLAGS="$TARGET_CFLAGS -DOS_WIN=1" + CFLAGS="$CFLAGS -DOS_WIN=1" fi else OS_UNIX=1 OS_WIN=0 OS_OS2=0 tclsubdir=unix - TARGET_CFLAGS="$TARGET_CFLAGS -DOS_UNIX=1" + CFLAGS="$CFLAGS -DOS_UNIX=1" fi @@ -19392,7 +19392,7 @@ fi echo "$as_me:$LINENO: result: $ac_cv_func_usleep" >&5 echo "${ECHO_T}$ac_cv_func_usleep" >&6 if test $ac_cv_func_usleep = yes; then - TARGET_CFLAGS="$TARGET_CFLAGS -DHAVE_USLEEP=1" + CFLAGS="$CFLAGS -DHAVE_USLEEP=1" fi @@ -19491,7 +19491,7 @@ fi echo "$as_me:$LINENO: result: $ac_cv_func_fdatasync" >&5 echo "${ECHO_T}$ac_cv_func_fdatasync" >&6 if test $ac_cv_func_fdatasync = yes; then - TARGET_CFLAGS="$TARGET_CFLAGS -DHAVE_FDATASYNC=1" + CFLAGS="$CFLAGS -DHAVE_FDATASYNC=1" fi Index: configure.ac =================================================================== RCS file: /sqlite/sqlite/configure.ac,v retrieving revision 1.31 diff -u -3 -p -r1.31 configure.ac --- configure.ac 27 Nov 2007 14:50:07 -0000 1.31 +++ configure.ac 5 Jan 2008 07:41:00 -0000 @@ -310,9 +310,9 @@ if test "$TARGET_EXEEXT" = ".exe"; then OS_UNIX=0 OS_WIN=0 OS_OS2=1 - TARGET_CFLAGS="$TARGET_CFLAGS -DOS_OS2=1" + CFLAGS="$CFLAGS -DOS_OS2=1" if test "$ac_compiler_gnu" == "yes" ; then - TARGET_CFLAGS="$TARGET_CFLAGS -Zomf -Zexe -Zmap" + CFLAGS="$CFLAGS -Zomf -Zexe -Zmap" BUILD_CFLAGS="$BUILD_CFLAGS -Zomf -Zexe" fi else @@ -320,14 +320,14 @@ if test "$TARGET_EXEEXT" = ".exe"; then OS_WIN=1 OS_OS2=0 tclsubdir=win - TARGET_CFLAGS="$TARGET_CFLAGS -DOS_WIN=1" + CFLAGS="$CFLAGS -DOS_WIN=1" fi else OS_UNIX=1 OS_WIN=0 OS_OS2=0 tclsubdir=unix - TARGET_CFLAGS="$TARGET_CFLAGS -DOS_UNIX=1" + CFLAGS="$CFLAGS -DOS_UNIX=1" fi AC_SUBST(BUILD_EXEEXT) @@ -565,13 +565,13 @@ AC_SUBST(TARGET_DEBUG) ######### # Figure out whether or not we have a "usleep()" function. # -AC_CHECK_FUNC(usleep, [TARGET_CFLAGS="$TARGET_CFLAGS -DHAVE_USLEEP=1"]) +AC_CHECK_FUNC(usleep, [CFLAGS="$CFLAGS -DHAVE_USLEEP=1"]) #-------------------------------------------------------------------- # Redefine fdatasync as fsync on systems that lack fdatasync #-------------------------------------------------------------------- -AC_CHECK_FUNC(fdatasync, [TARGET_CFLAGS="$TARGET_CFLAGS -DHAVE_FDATASYNC=1"]) +AC_CHECK_FUNC(fdatasync, [CFLAGS="$CFLAGS -DHAVE_FDATASYNC=1"]) ######### # Generate the output files._2008-Jan-05 08:24:29 by anonymous:_ {linebreak} It appears that http://www.sqlite.org/sqlite3-3.5.4.bin.gz and http://www.sqlite.org/sqlite-3.5.4.so.gz use sleep and fsync even though usleep and fdatasync are available on Linux. On the Linux man page, it claims that fdatasync is more efficient than fsync: "Unfortunately, fsync() will always initiate two write operations: one for the newly written data and another one in order to update the modification time stored in the inode. If the modification time is not a part of the transaction concept fdatasync() can be used to avoid unnecessary inode disk write operations." #f2dcdc 2872 code active 2008 Jan anonymous 2008 Jan 4 4 Some table scan operations could use an index for better data density If a suitable index covers a column being during a table scan, it makes sense to use the index for IO not the table pages themselves for speed. As (contrived) example: CREATE TABLE t1 ( c1 blob, c2 integer, c3 integer ); CREATE INDEX i1 on t1(c2,c3); CREATE INDEX i2 on t1(c3); T1 populated with 4096 rows, c1 being 64K random blobs (to make c2, c3 access slower in this case), c2 and c3 being small random integers. Now: sqlite> select sum(c3) from t1; sum(c3) ---------- 519895 is very slow (several seconds). A table scan is done. Forcing use of an index, in a way that I know *all* rows will be included: sqlite> select sum(c3) from t1 where c2<1000; sum(c3) ---------- 519895 This is instantaneous. It seems to me that if a table scan has to be performance, it makes sense to grab the data from an index whenever possible. ideally the most densely packed index. (BTW; this is contrived, I know putting the blob as the last column will greatly speed things up). #e8e8bd 2869 new active 2008 Jan anonymous 2008 Jan 5 5 add "sqlite3_open16_v2" to the C API I'm using UTF-16, and if the database file does not exist, "sqlite3_open16" will create a new one, but i wish it fails in such conditions. I notice that there's a "sqlite3_open_v2", but it doesn't support UTF-16, although i can implement a "sqlite3_open16_v2" myself, I think it should exists in the offical releases. #f2dcdc 2867 code active 2008 Jan anonymous 2008 Jan 2 2 doesn't build on Cygwin - wrong sqlite3 exe suffix The new Makefile used $(EXE), which doesn't seem to be defined (typo?) _2008-Jan-02 11:12:39 by anonymous:_ {linebreak} Same on mingw: Following patch fixes things:
--- sqlite-3.5.4/Makefile.in Thu Dec 13 19:17:42 2007 +++ sqlite-3.5.4-mingw-fix/Makefile.in Wed Jan 2 11:37:50 2008 @@ -322,7 +322,7 @@ -o $@ $(TOP)/src/shell.c libsqlite3.la \ $(LIBREADLINE) $(TLIBS) -sqlite3$(EXE): $(TOP)/src/shell.c sqlite3.c sqlite3.h +sqlite3$(TEXE): $(TOP)/src/shell.c sqlite3.c sqlite3.h $(LTLINK) $(READLINE_FLAGS) -o $@ \ -DSQLITE_MAX_SQL_LENGTH=1000000000 \ -USQLITE_THREADSAFE -DSQLITE_THREADSAFE=0 \ @@ -577,7 +577,7 @@ -e 's,$$,\\n",' \ $(TOP)/tool/spaceanal.tcl >spaceanal_tcl.h $(LTLINK) -DTCLSH=2 -DSQLITE_TEST=1 $(TEMP_STORE)\ - -o sqlite3_analyzer$(EXE) $(TESTSRC) $(TOP)/src/tclsqlite.c \ + -o sqlite3_analyzer$(TEXE) $(TESTSRC) $(TOP)/src/tclsqlite.c \ libtclsqlite3.la $(LIBTCL)#e8e8bd 2866 build active 2008 Jan anonymous 2008 Jan 1 3 Problems building Windows native in cygwin/mingw environment Trying to build Windows native version using the Cygwin build environment. $ gcc -v Reading specs from /usr/lib/gcc/i686-pc-cygwin/3.4.4/specs Configured with: /usr/build/package/orig/test.respin/gcc-3.4.4-3/configure --verbose --prefix=/usr --exec-prefix=/usr --sysconfdir=/etc --libdir=/usr/lib --libexecdir=/usr/lib --mandir=/usr/share/man --infodir=/usr/share/info --enable-languages=c,ada,c++,d,f77,pascal,java,objc --enable-nls --without-included-gettext --enable-version-specific-runtime-libs --without-x --enable-libgcj --disable-java-awt --with-system-zlib --enable-interpreter --disable-libgcj-debug --enable-threads=posix --enable-java-gc=boehm --disable-win32-registry --enable-sjlj-exceptions --enable-hash-synchronization --enable-libstdcxx-debug Thread model: posix gcc version 3.4.4 (cygming special, gdc 0.12, using dmd 0.125) $ $ CFLAGS=-mno-cygwin ./configure --disable-tcl --enable-threadsafe $ make A) The make appears to build sqlite3.exe just fine, without errors or warnings. This binary does work from cmd.exe, BUT not from within the bash cygwin shell for some reason, unlike other Windows native binaries I've built. Next... $ make install B) The cc sqlite3.c -o sqlite3 fails to rebuild sqlite3.exe correctly with the -mno-cygwin option. The output follows: rm -rf tsrc mkdir -p tsrc cp ./src/alter.c ./src/analyze.c ./src/attach.c ./src/auth.c ./src/btmutex.c ./src/btree.c ./src/btree.h ./src/build.c ./src/callback.c ./src/complete.c ./src/date.c ./src/delete.c ./src/expr.c ./src/func.c ./src/hash.c ./src/hash.h ./src/insert.c ./src/journal.c ./src/legacy.c ./src/loadext.c ./src/main.c ./src/malloc.c ./src/mem1.c ./src/mem2.c ./src/mem3.c ./src/mutex.c ./src/mutex_os2.c ./src/mutex_unix.c ./src/mutex_w32.c ./src/os.c ./src/os_unix.c ./src/os_win.c ./src/os_os2.c ./src/pager.c ./src/pager.h ./src/parse.y ./src/pragma.c ./src/prepare.c ./src/printf.c ./src/random.c ./src/select.c ./src/shell.c ./src/sqlite.h.in ./src/sqliteInt.h ./src/table.c ./src/tclsqlite.c ./src/tokenize.c ./src/trigger.c ./src/utf.c ./src/update.c ./src/util.c ./src/vacuum.c ./src/vdbe.c ./src/vdbe.h ./src/vdbeapi.c ./src/vdbeaux.c ./src/vdbeblob.c ./src/vdbefifo.c ./src/vdbemem.c ./src/vdbeInt.h ./src/vtab.c ./src/where.c ./ext/fts1/fts1.c ./ext/fts1/fts1.h ./ext/fts1/fts1_hash.c ./ext/fts1/fts1_hash.h ./ext/fts1/fts1_porter.c ./ext/fts1/fts1_tokenizer.h ./ext/fts1/fts1_tokenizer1.c sqlite3.h ./src/btree.h ./src/btreeInt.h ./src/hash.h ./src/sqliteLimit.h ./src/mutex.h opcodes.h ./src/os.h ./src/os_common.h ./src/sqlite3ext.h ./src/sqliteInt.h ./src/vdbe.h parse.h ./ext/fts1/fts1.h ./ext/fts1/fts1_hash.h ./ext/fts1/fts1_tokenizer.h ./src/vdbeInt.h tsrc cp: warning: source file `./src/btree.h' specified more than once cp: warning: source file `./src/hash.h' specified more than once cp: warning: source file `./src/sqliteInt.h' specified more than once cp: warning: source file `./src/vdbe.h' specified more than once cp: warning: source file `./ext/fts1/fts1.h' specified more than once cp: warning: source file `./ext/fts1/fts1_hash.h' specified more than once cp: warning: source file `./ext/fts1/fts1_tokenizer.h' specified more than once cp: warning: source file `./src/vdbeInt.h' specified more than once rm tsrc/sqlite.h.in tsrc/parse.y cp parse.c opcodes.c keywordhash.h tsrc tclsh ./tool/mksqlite3c.tcl cc sqlite3.c -o sqlite3 /usr/lib/gcc/i686-pc-cygwin/3.4.4/../../../libcygwin.a(libcmain.o):(.text+0xab): undefined reference to `_WinMain@16' collect2: ld returned 1 exit status make: *** [sqlite3] Error 1 $ #f2dcdc 2917 code active 2008 Feb anonymous 2008 Feb 4 4 Tcl interface - busy callback confusion script/procedure In the Tcl interface the "busy" method doesn't work if a script is supplied instead of a procedure: % package req sqlite3 3.5.1 % sqlite3 db hl7journal.db % db busy {puts busy; expr 0} % db eval {select count(*) from incoming} busy database is locked Here the callback script is only invoked once, even though it returns 0. If we put this code in a procedure it works as desired/expected: % proc b args {puts busy; after 1000; expr 0} % db busy b % db eval {select count(*) from incoming} busy busy busy ^C _2008-Feb-01 12:31:45 by anonymous:_ {linebreak} After researching this a little it appears this happens because the busy callback is invoked with an extra argument. The extra argument leads to an error but that error is only visible through errorInfo, not the result. I humbly suggest the following changes: * mention the extra argument in the documentation of the Tcl interface * forward the error from the busy callback to Tcl (replacing the "database is locked") * enhance errorInfo to make the invokation of the busy callback apparent. Currently, I'm getting this errorInfo: % db busy {puts hi; after 1000; return 0} % db eval "select count(*) from incoming" hi database is locked % set errorInfo bad option "0": must be -code, -errorcode, or -errorinfo while executing "return 0 0" invoked from within "db eval "select count(*) from incoming"" It would be nicer to get something like bad option "0": must be -code, -errorcode, or -errorinfo while executing "return 0 0" from busy callback "puts hi; after 1000; return 0 0" invoked from within "db eval "select count(*) from incoming"" #f2dcdc 2916 code active 2008 Feb anonymous 2008 Feb 1 1 sqlitedll-3_5_5.zip is older 3.5.4 binary sqlitedll-3_5_5.zip in download section is same with old 3.5.4 binary. _2008-Feb-01 12:13:04 by anonymous:_ {linebreak} Yes , I can confirm it #e8e8bd 2915 build active 2008 Feb pweilbacher 2008 Feb 4 3 fix Makefile for platforms that need .exe extension There are some targets that need a $(TEXE) added in Makefile.in. Otherwise a platform that needs a .exe extension cannot run e.g. tests. #f2dcdc 2613 code active 2007 Sep anonymous 2008 Jan drh 3 3 replace doesn't work with blobs containing \x0, otherwise it does The replace expression function does not work with blobs in case of contained zero terminator character; but it does if there is not this special character included. I expected the function to work similar like substr with blob-safety in case of type is blob only. X'nnnn' is of type blob, so following example should have returned a blob type result X'0102FF0405' in the 2nd and 3rd line. How to get to this result?
SQLite version 3.4.2 Enter ".help" for instructions sqlite> select hex(replace(X'0102030405',X'03',X'FF')); 0102FF0405 sqlite> select hex(replace(X'0102000405',X'00',X'FF')); sqlite> select typeof(replace(X'0102000405',X'00',X'FF')); null sqlite>_2007-Sep-03 04:21:12 by anonymous:_ {linebreak} Replace was designed to work with strings. However, working with blobs would be an interesting extension. ---- _2007-Oct-18 06:13:10 by anonymous:_ {linebreak} I've seen a similar situation where I can't reliably store stings with nulls in the middle of them as TEXT. I can convert them to blobs, in which case length(...) works correctly. I if convert them back to strings, length(...) treats them as C-strings. Is this the expected behavior? I notice the entire column is preserved even when it's has TEXT affinity, I can append data to it as a string, cast back to a blob and see everything (am I explaining this poorly?) This all seems a bit counter intuitive in some ways. Perhaps strings shouldn't treat NULL characters as special? ---- _2007-Oct-27 16:45:41 by anonymous:_ {linebreak} Treatment of length operator is - as fas as I know - dependent on type: {linebreak} As text it is the length number of UTF-8 characters and as blob it is the number of bytes. As long as all the UTF-8 characters out of the lower half ASCII char-set (127 of them), this is identical beside the fact of different 0-terminator interpretation. {linebreak} To append is something different than using the replace operator. My suggestion would be to make the replace operator work with bytes (not UTF-8) in case of all 3 parameters are of type blob. {linebreak} Another suggestion: the UTF aware functions are Private declared and not usable from within a loadable extension dll/so. This should be changed. ---- _2008-Jan-28 19:36:39 by anonymous:_ {linebreak} Will there come a solution for this with the next release? It is really not fair to handle a blob only like text which cannot contain a zero terminator. With this unique useful function a zero-containing blob could be formed into a normal text string without loosing the part behind the zero terminator. It would be really a step forward without too much effort. #f2dcdc 2674 code active 2007 Sep anonymous 2007 Sep 3 4 NFS fails without lock manager *Problem:* SQLite fails entirely if the NFS lock manager is not running on the share hosting the DB -- even when all access to the DB is serialized. Under these circumstances, it becomes difficult to create applications that are capable of running in a multitude of environments because restrictions are now imposed upon the storage location. ---- *Reproduction:* *:setup an NFS share *:disable the lock manager *:attempt to perform any transactions on a db on that share *:start the lock manager *:perform the same operations ---- *Request:* *:Make a change to the error handling, as necessary, to allow processes to access a DB over an NFS share without use of a lock manager. *:Make a big bold flashing sign in the FAQ about this failure-mode. The wording of the current FAQ led us to believe that the transaction would go through, but protection from other processes was not guaranteed. ---- *Shameless Fanmail:* Love the product! ---- *Screenshot:* dev-srs08 ~ #
create table x1(a int, b int); create table x2(c int, d int); create table x3(e int, f int); mysql> select x1.* from x1 left join x2 on x1.a=x2.c left join x3 on x2.d=x3.e; Empty set (0.00 sec)---- _2007-Sep-06 19:03:33 by anonymous:_ {linebreak} MSSQL also has no problems without the parens. As a matter of fact, the only DB that I know of that requires them is MS Access (JET). ---- _2007-Sep-06 20:11:31 by anonymous:_ {linebreak} I guess he had no luck filing a JET bug. ---- _2007-Sep-11 17:22:28 by anonymous:_ {linebreak} True, MS access requires the parens but all other major DBs support the query syntax with the parens. So why SQLite does not like it? It should simply ignore them if possible. Thank you #f2dcdc 2634 code active 2007 Sep anonymous 2007 Sep 3 3 .schema uses incorrect ORDER BY giving wrong dependency order When the schema is exported, views are sorted by name instead of by dependency. If there are nested views, the schema may be invalid when used to re-create the database. sqlite3 create table t ( f text ); create view v2 as select f from t; create view v1 as select f from v2; .output test.txt .schema .exit sqlite3 .read test.txt SQL error near line 2: no such table: main.v2 _2007-Sep-07 15:33:06 by anonymous:_ {linebreak} Use .dump instead as a workaround. Unlike .schema, .dump does not use ORDER BY in its queries on sqlite_master and it outputs its rows in order of entry.
SQLite version 3.5.0 Enter ".help" for instructions sqlite> create table t ( f text ); sqlite> create view v2 as select f from t; sqlite> create view v1 as select f from v2; sqlite> sqlite> .schema CREATE TABLE t ( f text ); CREATE VIEW v1 as select f from v2; CREATE VIEW v2 as select f from t; sqlite> sqlite> .dump BEGIN TRANSACTION; CREATE TABLE t ( f text ); CREATE VIEW v2 as select f from t; CREATE VIEW v1 as select f from v2; COMMIT;Suggested patch:
Index: src/shell.c =================================================================== RCS file: /sqlite/sqlite/src/shell.c,v retrieving revision 1.167 diff -u -3 -p -r1.167 shell.c --- src/shell.c 7 Sep 2007 01:12:32 -0000 1.167 +++ src/shell.c 7 Sep 2007 15:28:24 -0000 @@ -1411,8 +1411,7 @@ static int do_meta_command(char *zLine, "SELECT sql FROM " " (SELECT * FROM sqlite_master UNION ALL" " SELECT * FROM sqlite_temp_master) " - "WHERE tbl_name LIKE shellstatic() AND type!='meta' AND sql NOTNULL " - "ORDER BY substr(type,2,1), name", + "WHERE tbl_name LIKE shellstatic() AND type!='meta' AND sql NOTNULL", callback, &data, &zErrMsg); zShellStatic = 0; } @@ -1421,8 +1420,7 @@ static int do_meta_command(char *zLine, "SELECT sql FROM " " (SELECT * FROM sqlite_master UNION ALL" " SELECT * FROM sqlite_temp_master) " - "WHERE type!='meta' AND sql NOTNULL AND name NOT LIKE 'sqlite_%'" - "ORDER BY substr(type,2,1), name", + "WHERE type!='meta' AND sql NOTNULL AND name NOT LIKE 'sqlite_%'", callback, &data, &zErrMsg ); }after patch:
SQLite version 3.5.0 Enter ".help" for instructions sqlite> create table t ( f text ); sqlite> create view v2 as select f from t; sqlite> create view v1 as select f from v2; sqlite> .schema CREATE TABLE t ( f text ); CREATE VIEW v2 as select f from t; CREATE VIEW v1 as select f from v2; sqlite> .q#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 */#f2dcdc 2721 code active 2007 Oct anonymous 2007 Dec 2 1 if db file is in a folder with non-ansi character some functions fail If database file is located in directory with some non-ANSI characters (in my case with a Russian subdirectory c:\Мои документы\Data_Jobs), or it's name is non-ansi. Some functions fail to execute sql. For example (with defined UNICODE):
TCHAR sql[512]; _stprintf(sql, _T("INSERT INTO tab_SurveyedPoints (name, comment, code,") _T("coordinatetype, b, l, h, solutiontype, sigmah, sigmav)") _T(" VALUES ('%s','%s','%s',0,%lf,%lf,%lf,0,%lf,%lf);"), point.m_name.c_str(), point.m_description.c_str(), point.m_code.c_str(), point.m_coordinates.b, point.m_coordinates.l, point.m_coordinates.h, point.m_sigmah, point.m_sigmav); int rc1 = sqlite3_prepare16(m_db, sqlfmt, -1, &stmt, (const void**)&pszTail); rc != SQLITE_OKBut if I move the file to c:\My documents\Data_Jobs this works ok. It's improbable behaviour, but I can't work around yet. Although, prepare() functions work ok as well in both cases. Yuri Noyanov. _2007-Oct-11 19:33:34 by drh:_ {linebreak} All string arguments to SQLite, and especially filename arguments, must be UTF-8 or UTF-16 (depending on the function). If you use string parameters which are not UTF-8 or UTF-16 (as appropriate) then the behavior of SQLite is undefined and probably not what you want. ---- _2007-Oct-12 04:25:56 by anonymous:_ {linebreak} but ALL programs to handle SQLite DBs (SQLIteBrowser, SQLite Control) fail to handle the files as well. Till I move the file to different directory !!! ---- _2007-Oct-12 04:27:54 by anonymous:_ {linebreak} Also I must note, that I CAN open the database, I CAN execute some SQLs with sqlite_prepare function OK. But sqlite_prepare16 FAILS if I just rename my database !!! ---- _2007-Oct-12 04:31:46 by anonymous:_ {linebreak} Also note to make my issue clearer: sqlite_prepare16() with the same code either works OK either doesn't work. depends on database filename or folder path. The database is opened OK in both cases (I used utf8 conversion). sql_prepare() works ok in both cases. ---- _2007-Oct-13 06:37:43 by anonymous:_ {linebreak} That appears to be only with INSERT sql statement. Both SELECT and UPDATE work fine with sqlite_prepare16. #e8e8bd 2716 new active 2007 Oct anonymous 2007 Oct 5 1 Create Clear Command I want a command caled "clear" like in MySQL. This command should erase the screen and then put the sqlite pointer on top of the screen _2007-Oct-11 07:41:45 by anonymous:_ {linebreak} How about a cookie instead? ---- _2007-Oct-30 08:02:04 by anonymous:_ {linebreak} Clearing the screen and moving the cursor are platform-dependent operations. On Unix they are not only platform-dependent, but also terminal-dependent. Thus such a feature does not really belong in the cross-platform and minimalistic sqlite3 shell (in my opinion). #e8e8bd 2749 warn active 2007 Oct anonymous 2007 Oct 3 4 SQLITE_OMIT_FLOATING_POINT, int constants too large Hiya! i'm working from the 3.5.1 (CVS version) of the amalgamation. The code was pulled from CVS sometime around Oct 27th 2007. Platform: Kubuntu Linux 7.04, i386-32, gcc 4.1.2. The compiler output says it all: gcc -c -DSQLITE_OMIT_FLOATING_POINT sqlite3.c sqlite3.c: In function 'bestVirtualIndex': sqlite3.c:65531: warning: integer constant is too large for 'long' type sqlite3.c: In function 'bestIndex': sqlite3.c:65600: warning: integer constant is too large for 'long' type sqlite3.c: In function 'sqlite3WhereBegin': sqlite3.c:66223: warning: integer constant is too large for 'long' type sqlite3.c:66248: warning: integer constant is too large for 'long' type sqlite3.c:66254: warning: integer constant is too large for 'long' type These warnings would seem to indicate potentially serious problems, though i admittedly have not investigated whether overflows are really fatal in the affected contexts. #e8e8bd 2748 warn active 2007 Oct anonymous 2007 Oct 4 4 amalgamation: SQLITE_OMIT_ALTERTABLE warnings Hiya! i'm working from the 3.5.1 (CVS version) of the amalgamation. The code was pulled from CVS sometime around Oct 27th 2007. The compiler output says it all: gcc -c -DSQLITE_OMIT_ALTERTABLE sqlite3.c sqlite3.c:6909: warning: 'sqlite3AlterRenameTable' used but never defined sqlite3.c:6916: warning: 'sqlite3AlterFinishAddColumn' used but never defined sqlite3.c:6917: warning: 'sqlite3AlterBeginAddColumn' used but never defined The relevant code is: sed -ne '6909p;6916p;6917p' sqlite3.c SQLITE_PRIVATE void sqlite3AlterRenameTable(Parse*, SrcList*, Token*); SQLITE_PRIVATE void sqlite3AlterFinishAddColumn(Parse *, Token *); SQLITE_PRIVATE void sqlite3AlterBeginAddColumn(Parse *, SrcList *); Platform: Kubuntu Linux 7.04, i386, gcc 4.1.2. #f2dcdc 2728 code active 2007 Oct anonymous 2007 Oct 4 4 Some indexes could contain pointers not the data It would be nice if there was a class of index(-column) that would contain a reference to the data in (in the table) rather than a copy of the data. This is useful where you have a need to index large strings or blobs and the size penalty of having 2+ copies ends up being *very* expensive. Consider the cost of the implied index on: create table t1 ( c1 BLOB UNIQUE); and store 100s of 1MB objects. The raw file ends up being twice as large as you ideally would like it to be. *WHEN* to use such indexes isn't entirely clear to me, not is the syntax for doing it in general, 30s of thought I can come up with: create table t1 ( c1 BLOB &UNIQUE); and say create index idx1 on t2(c1, &c2, c3); (meaning c2 would be done via reference, c1 & c3 as copies of the data). It's *FAR* from clear what other databases do here (I didn't look). This would also be useful in cases where I have multiple indexes on tables like email (headers) and values which can be very large (100s of bytes in some cases (Subject:) and I might have 5 or more indexes, means a 100-byte column will take over 600 bytes (+ padding)). Allowing for references would be slower in some cases but faster in others because of the smaller footprint and much grater CPU cache utilization. _2007-Oct-14 15:27:12 by anonymous:_ {linebreak} This enhacement has been discussed on the mailing list before, and would break sqlite3 file format compatability. ---- _2007-Oct-15 02:42:02 by anonymous:_ {linebreak} This is non standard sql. Also, the random access to pages to check indexes constraints (unique indexes, also primary keys) will trash the entire database performance. If you need small index disk usage, consider using hashes to your data keys. it´s the best that you could do to solve your problem. ---- _2007-Oct-18 06:07:58 by anonymous:_ {linebreak} Using a hash is what I've tried doing in a couple of cases. It's not very ideal. For one thing is ordering is messed up. I wonder about a virtual index concept, where you can define a function that takes the column value and returns something to actually store in the index? I'm told oracle has this feature, so whilst it's non-standard there is some precedent (albeit in a very different space). #e8e8bd 2736 build active 2007 Oct anonymous 2007 Oct 2 2 build problems on freebsd on freebsd: --disable-threads does not work. it is accepted as a valid option but no defs are added to the makefile -lgcc needs to be included in SHLIB_LD_LIBS pkgIndex.tcl is not built when -DSQLITE_THREADSAFE=0 is added manually, this causes the install target to fail. _2007-Oct-17 21:16:23 by anonymous:_ {linebreak} I forgot to mention this is with the TEA version #f2dcdc 2715 code active 2007 Oct anonymous 2007 Oct 1 1 no authorization needed to remove authorizer there should be a new auth code created and the auth function should be consulted for permission for removal. _2007-Oct-10 01:08:48 by drh:_ {linebreak} I'm assuming that this feature request comes from {quote: RockShox} and that the development language is Tcl. No. If your adversary has the ability to invoke the interface that removes an authorizer, then you system is already pwned. What you really need is the ability to [interp alias] the eval method into a safe interpreter. That way you can: *: Open the database in the main interpreter *: Set up the authorizer in the main interpreter to invoke a script in the main interpreter *: Set up the [interp alias] so that the safe interpreter can do [db eval ...] but not [db auth ...] It seems like an "-interp" option on the "eval" method of the database connection object would likely be the right interface. Or perhaps there should be separate "safeeval" method. Either way, it has been years and years since I have done anything with safe interpreters so I will have to look into what needs to be done to make that happen. ---- _2007-Oct-17 20:11:23 by anonymous:_ {linebreak} ok i think i agree with that. currently you cannot use an interp alias since the target command runs in the target interp and all your variables and commands are in the wrong scope. this means one needs to load sqlite again in the new interp, and sqlite will not load in a safe interp so a regular interp is required. to be useful, a -interp flag would need to execute in the current scope of the interp and not the global scope. #f2dcdc 2714 code active 2007 Oct anonymous 2007 Oct drh 3 4 Shell cannot import large files greater than 2 GB [patch] If I issue an .import command within the SQLite shell and the file size is larger than 2 GB, the shell gives the error message "cannot open file: foo.txt". Putting #define _FILE_OFFSET_BITS 64 before the #include statements in shell.c fixes this problem under Linux. I tested this solution with a 2.6 GB file under Ubuntu Feisty Fawn 7.04 with Linux kernel 2.6.20-16-generic. _2007-Oct-12 18:48:35 by drh:_ {linebreak} I'm not convinced SQLite _should_ be able to import files larger than 2GiB. Does anybody really ever need to read more than 2GiB of SQL text? That is a lot of SQL, don't you think? Is this really a desirable feature? Can you not split it up into two or more smaller files? ---- _2007-Oct-14 09:25:42 by anonymous:_ {linebreak} I'm not 100% certain about this but: #define _FILE_OFFSET_BITS 64 isn't necessarily correct, better to tweak the makefile(s): CFLAGS += $(shell getconf LFS_CLFAGS) (similar for LDFLAGS) should work on 32/64 bit machines and different platforms. For shell.c I really wouldn't bother and think this should be left alone. Using .import for load more than 2GB makes me things the shell should be fleshed out and extended for this sort of abuse, handy as it is right now, it's not a full SQL shell but a minimalist shell and a useful example). ---- _2007-Oct-16 14:08:09 by anonymous:_ {linebreak} I am the original poster. I use the shell to import CSV files in the 3-4 GiB range on a daily basis. I don't see the point of splitting the files or writing my own importer when the shell does a perfectly fine job once large file support has been activated. With all due respect, I don't understand the reluctance to enable a useful feature. Shouldn't the user decide whether it is reasonable for his application to import big files? I work in the health care industry processing insurance claims for several very large companies. There's nothing broken or wrong with my application -- big files are just the nature of the data I'm handling. Neither of you has proposed any disadvantage to enabling LFS in the shell. If there are no drawbacks, why not turn it on? If the core library can open large database files, why shouldn't the shell be able to import large ones? ---- _2007-Oct-16 16:24:22 by anonymous:_ {linebreak} For reasons also unknown to me, the authors appear to regard the shell as a largely unused sample program. Based on my personal experience in talking to various sqlite library users, it is the primary administrative front-end for sqlite. In particular, doing backups and merging databases. #e8e8bd 2729 doc active 2007 Oct anonymous 2007 Oct 1 1 Lemon: %fallback, %wildcard, and @X uncodumented I noticed that the lemon documentation does not mention the %fallback and %wildcard directives. Both are in the code and are apparently doing useful work in SQLite's parse.y. Can other users benefit from them as well? The symbol @X is also undocumented. From a source code comment I read that it "If the argument is of the form @X then substituted the token number of X, not the value of X". A short documentation example would help to understand where and how it can be useful to apply this syntax. Are there other nice but undocumented Lemon goodies lacking documentation? #f2dcdc 2725 code active 2007 Oct anonymous 2007 Oct 1 1 memory leak in sqlite3_open_v2() when it fails only happens with flags = SQLITE_OPEN_READWRITE; and when res = sqlite3_open_v2(sourcename, &conn, flags, NULL); seems to leak 674 bytes per call _2007-Oct-15 07:07:07 by danielk1977:_ {linebreak} Are you calling sqlite3_close(conn) after the error occurs? All calls to sqlite3_open_v2() need to be matched by a call to sqlite3_close(), even if an error occurs. #e8e8bd 2701 new active 2007 Oct anonymous 2007 Oct 5 5 Make INSERT-ing multiple rows MySQL-compatible SQLite syntax allows to insert only one row with
insert into test (a, b, c) values (1, 2, 3);MySQL allows to insert multiple with
insert into test (a, b, c) values (1, 2, 3), (4, 5, 6), (7, 8, 9) -- etcBut SQLite is also capable of inserting multiple by using INSERT...SELECT:
insert into test (a, b, c) select 1, 2, 3 union select 4, 5, 6 -- etcIt would be nice to make INSERT statement syntactically compatible with MySQL, allowing to insert multiple rows with VALUES clause. It can be implemented by simply translating multiple 'VALUES ()()()' to 'select union' - no serious change required at all. _2007-Oct-08 21:45:05 by anonymous:_ {linebreak} You mean "UNION ALL", not "UNION". UNION would remove duplicate rows, and create an ephemeral table that you don't want because it's less efficient. Your idea is a good one and could be implemented largely in the parser. The number of VDBE opcodes would be quite large for such a statement. I wonder if that would present a problem. ---- _2007-Oct-14 08:12:11 by anonymous:_ {linebreak} Patch implementing multi-row INSERT statements against 3.5.1 source tree: http://www.mail-archive.com/sqlite-users%40sqlite.org/msg28337.html #e8e8bd 2727 build active 2007 Oct anonymous 2007 Oct 4 4 building with -malign-double causes strange behavior sqlite 3.5.1 amalgamation has problems when enabling a wide set of compiler features on gcc (GCC) 4.1.2 (Ubuntu 4.1.2-0ubuntu4) on linux/i686 /w glibc-2.5 strange behavior occurs. typical strangeness is that SQLITE_FULL is returned from sqlite3_prepare_v2() (called immediately after a statement was created with sqlite3_mprintf). This happens even though there is a reasonable amount of disk space free (6G). the compile line looks like:{linebreak} cc -g -pg -O1 -march=i686 -msse2 -malign-double -m128bit-long-double -momit-leaf-frame-pointer -minline-all-stringops -D_XOPEN_SOURCE=520 '-DVERSION="0.10r276M"' -Isqlite/ -DSQLITE_OMIT_LOAD_EXTENSION -DTHREADSAFE=0 -DSQLITE_OMIT_EXPLAIN -DSQLITE_ENABLE_COLUMN_METADATA -c -o sqlite/sqlite3.o sqlite/sqlite3.c and valgrind reports:{linebreak} ==15323== Use of uninitialised value of size 4{linebreak} ==15323== at 0x80585B7: insertElement (sqlite3.c:13072){linebreak} ==15323== by 0x807366E: sqlite3HashInsert (sqlite3.c:13290){linebreak} ==15323== by 0x809FE72: unixOpen (sqlite3.c:15403){linebreak} ==15323== by 0x80579A7: sqlite3OsOpen (sqlite3.c:8210){linebreak} ==15323== by 0x806B12F: sqlite3BtreeFactory (sqlite3.c:21317){linebreak} ==15323== by 0x80767BA: openDatabase (sqlite3.c:71237){linebreak} ==15323== by 0x8076BD1: sqlite3_open (sqlite3.c:71337){linebreak} ==15323== by 0x804BEAE: db_init (dbmgr.c:81){linebreak} ==15323== by 0x804CE64: main (main.c:59){linebreak} my dbmgr.c:81:db_init() is: res=sqlite3_open(filename, &system_db); and system_db=NULL and filename="zomg.sqlite" (string literal). so the parameters seem normal. if I turn off -malign-double everything works fine. (this "bug" also seems to be on 3.4.1 amalgamation) _2007-Oct-14 04:35:16 by anonymous:_ {linebreak} This is a compiler issue. -malign-double creates problems for most programs. What are you trying to accomplish? #f2dcdc 2708 code active 2007 Oct anonymous 2007 Oct 4 2 SQL error:disk I/O error I cross-compile sqlite to embedded Linux,but after I insert data to the table ,it failed.the warning is "SQL error:disk I/O error". _2007-Oct-09 05:12:28 by anonymous:_ Why do you think it is SQLite error ?? ---- _2007-Oct-09 05:46:06 by danielk1977:_ {linebreak} We'll need a bit more data than that to figure this out. Did earlier SQLite versions work? Can you post the entire output of the compile process so that we can see if there are any clues there? Can you run strace so that we can see if there really is an IO error, or at least when SQLite believes there to be one? #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 2704 code active 2007 Oct anonymous 2007 Oct 4 4 "make test" aborts before completion with SQLITE_OMIT_BLOB_LITERAL When compiled with -DSQLITE_OMIT_BLOB_LITERAL make test aborts with this error:
substr-2.5.2... Ok ./testfixture: near "'61626364656667'": syntax error while executing "db eval " DELETE FROM t1; INSERT INTO t1(b) VALUES(x'$hex') "" (procedure "subblob-test" line 2) invoked from within "subblob-test 3.1 61626364656667 1 1 61" (file "./test/substr.test" line 86) invoked from within "source $testfile" ("foreach" body line 5) invoked from within "foreach testfile [lsort -dictionary [glob $testdir/*.test]] { set tail [file tail $testfile] if {[lsearch -exact $EXCLUDE $tail]>=0} continue if..." (file "./test/quick.test" line 93) make: *** [test] Error 1#f2dcdc 2703 code active 2007 Oct anonymous 2007 Oct 3 4 make test does not work with SQLITE_OMIT_FLOATING_POINT make test cannot run with SQLITE_OMIT_FLOATING_POINT. Shouldn't these tests be skipped?
./src/test1.c:1255: warning: passing argument 3 of ‘Tcl_GetDouble’ from incompatible pointer type ./src/test1.c: In function ‘sqlite3_mprintf_scaled’: ./src/test1.c:1284: warning: passing argument 3 of ‘Tcl_GetDouble’ from incompatible pointer type ./src/test1.c: In function ‘test_bind_double’: ./src/test1.c:2607: warning: passing argument 3 of ‘Tcl_GetDoubleFromObj’ from incompatible pointer type ./src/tclsqlite.c: In function ‘tclSqlFunc’: ./src/tclsqlite.c:728: warning: passing argument 3 of ‘Tcl_GetDoubleFromObj’ from incompatible pointer type ./src/tclsqlite.c: In function ‘DbObjCmd’: ./src/tclsqlite.c:1609: warning: passing argument 3 of ‘Tcl_GetDoubleFromObj’ from incompatible pointer type ... $ ./testfixture test/select1.test select1-1.1... Ok select1-1.2... Ok select1-1.3... Ok select1-1.4... Ok select1-1.5... Ok select1-1.6... Ok select1-1.7... Ok select1-1.8... Ok select1-1.8.1... Ok select1-1.8.2... Ok select1-1.8.3... Ok ./testfixture: near ".": syntax error while executing "db eval {INSERT INTO test2(r1,r2) VALUES(1.1,2.2)}" ("uplevel" body line 1) invoked from within "uplevel [list $db eval $sql]" (procedure "execsql" line 3) invoked from within "execsql {INSERT INTO test2(r1,r2) VALUES(1.1,2.2)}" (file "test/select1.test" line 69)#f2dcdc 2684 code active 2007 Oct anonymous 2007 Oct 1 1 Accessing sqlite from an NT service will lock the complete databse. Accessing sqlite from a NT service (application 1) will lock the complete database. Any other process trying to open an sqlite db (application 2) will get error "80004005 unable to lock database" If application 1 runs as normal application, started by local user, this problem doesnt occur and both applications can open the db. _2007-Oct-02 15:48:05 by anonymous:_ {linebreak} SQLite has no knowledge of Windows services. How do you propose to work around this Windows anachronism? ---- _2007-Oct-02 17:20:38 by anonymous:_ {linebreak} Suggesion: Try running the service in the same account as the other program that needs to access the database. Anachronism? Service is just another word for daemon. -knu- ---- _2007-Oct-02 17:33:56 by anonymous:_ {linebreak} Re: Anachronism, the OP suggested there was something fundamentally different about file access using a service. You've pointed out that it's just a file permissions issue. ---- _2007-Oct-05 14:45:07 by drh:_ {linebreak} Two points: 1: The error message "80004005 unable to lock database" is not generated by SQLite. There must be some middleware someplace that is producing this message. The problem might be in that middleware and not in SQLite. 2: None of the SQLite developers run windows. Consequently any fixes for this problem will need to come from the community. Please append patches to this ticket if you find a fix. Or close the ticket if you discover that the problem is outside of SQLite. #f2dcdc 2761 code active 2007 Nov anonymous 2007 Dec 3 3 CLI (shell.c) should be bundled with amalgamation The CLI (shell.c) should be bundled with the amalgamation for database administrative purposes without downloading the matching shell.c from the full source tree. I second that! Qt ships with the amalgamated source files, but we also ship shell.c, whch we have to retrieve from the non-amalgamated source files. ---- _2007-Dec-26 15:20:04 by anonymous:_ {linebreak} I also agree. It is inconvenient to retrieve the matching shell.c from the source tree. #f2dcdc 2814 code active 2007 Nov anonymous 2007 Dec 3 3 _XOPEN_SOURCE again Ideally setting _XOPEN_SOURCE should be an opt-in detected by configure, rather than a hardcoded opt-out as it is now. I find you create more problems in setting it than just leaving it out on modern platforms. Can you please give users the option of not defining _XOPEN_SOURCE at all?
+#ifndef SQLITE_DONT_DEFINE_XOPEN_SOURCE #if !defined(_XOPEN_SOURCE) && !defined(__DARWIN__) && SQLITE_THREADSAFE # define _XOPEN_SOURCE 500 /* Needed to enable pthread recursive mutexes */ #endif +#endif_2007-Dec-01 09:23:15 by anonymous:_ {linebreak} Also when using Python, it sets _XOPEN_SOURCE to 600. No idea what the 500 vs 600 difference is about. ---- _2007-Dec-01 15:58:28 by anonymous:_ {linebreak} I've used a couple of different Linux OSes and _XOPEN_SOURCE is not needed. Maybe it's for OSes more than 5 years old. Recursive mutexes are pretty much standard these days since the popularity of Java which uses them extensively. ---- _2007-Dec-01 17:21:05 by drh:_ {linebreak} See also tickets #2673, #2681, and #2741. ---- _2007-Dec-02 02:08:26 by anonymous:_ {linebreak} On Linux, PTHREAD_MUTEX_RECURSIVE is the same as PTHREAD_MUTEX_RECURSIVE_NP: PTHREAD_MUTEX_RECURSIVE = PTHREAD_MUTEX_RECURSIVE_NP, Since PTHREAD_MUTEX_RECURSIVE_NP is always available, you could avoid defining _XOPEN_SOURCE and use this code instead:
- pthread_mutexattr_settype(&recursiveAttr, PTHREAD_MUTEX_RECURSIVE); + pthread_mutexattr_settype(&recursiveAttr, +#ifdef linux + PTHREAD_MUTEX_RECURSIVE_NP +#else + PTHREAD_MUTEX_RECURSIVE +#endif + );---- _2007-Dec-02 02:17:22 by anonymous:_ {linebreak} A quick google search reveals how various projects deal with this recursive mutex declaration problem (in no particular order): *: #define _XOPEN_SOURCE 500 and use PTHREAD_MUTEX_RECURSIVE *: #define _XOPEN_SOURCE 600 and use PTHREAD_MUTEX_RECURSIVE *: #define _GNU_SOURCE and use PTHREAD_MUTEX_RECURSIVE *: don't define anything and use PTHREAD_MUTEX_RECURSIVE_NP on linux, and PTHREAD_MUTEX_RECURSIVE elsewhere. Unfortunately, since PTHREAD_MUTEX_RECURSIVE is an enum on Linux, so you can't use the #ifdef PTHREAD_MUTEX_RECURSIVE compile-time technique. #e8e8bd 2813 build active 2007 Nov anonymous 2007 Nov 1 1 compile error on Windows CE environment: visual c++ 2005 window ce 6.0 customize sdk sqlite-amalgamation-3_5_3 I get error: Error 27 error C2040: 'localtime' : 'tm *(const time_t *)' differs in levels of indirection from 'int ()' d:\SubProjects\Sqlite\sqlite3.c 18574 but if I add code in line 7095: struct tm *__cdecl localtime(const time_t *t); then Success! #f2dcdc 2809 code active 2007 Nov anonymous 2007 Nov 1 1 PRAGMA collation_list shows unregistered collations As presented on the mailing list: Imagine that a SQLite3 database opened in a custom application with a registered a collation sequence named "unknown" has created the following table: CREATE TABLE a (b COLLATE unknown); Now open this table in the default SQLite3 CLI. Up to here, everything works as expected. Next issue "PRAGMA collation_list;" and notice that "unknown" lists next to the other registered collations, even though "unknown" is not registered with the default SQLite3 CLI: sqlite> PRAGMA collation_list; 0|unknown 1|NOCASE 2|BINARY Responses from the mailing list indicate that this is not the expected behaviour. "PRAGMA collation_list;" should list registered collations only. _2007-Nov-28 16:12:17 by anonymous:_ {linebreak} I don't think this is a bug. If the CLI is not aware of the collation, it should not process the query that makes use of the collation because it would certainly be wrong if it simply ignored the collation. This is not unlike a user-registered SQL function that does not exist in the CLI. I would not expect or want the sqlite3 CLI to ignore the unknown function, nor would I want the CLI to process queries ignoring the custom collation. #f2dcdc 2810 code active 2007 Nov anonymous 2007 Nov 1 1 Unregistered collation problems with simple subselects As discussed on the mailing-list: Imagine that a SQLite3 database opened in a custom application with a registered a collation sequence named "unknown" has created the following table: CREATE TABLE a (b COLLATE unknown); Now open this table in the default SQLite3 CLI. Up to here, everything works as expected. Simple queries like "SELECT * FROM a;" work fine. But subselects, in their most basic form and with no sorting or comparisons, result in an error: sqlite> INSERT INTO a VALUES ('one'); sqlite> SELECT * FROM a, (SELECT * FROM a); SQL error: no such collation sequence: unknown sqlite> SELECT * FROM (SELECT * FROM a); SQL error: no such collation sequence: unknown sqlite> SELECT *, * FROM a; one|one This is surprising because the collation sequence should not matter to the queries. In fact, the union without the subselect works just fine and without errors. To demonstrate, here is the explain output of a table with a registered collation sequence. No mention of the collation name here: sqlite> CREATE TABLE b (b collate nocase); sqlite> EXPLAIN SELECT * FROM b, (SELECT * FROM b); 0|Goto|0|17| 1|Integer|0|0| 2|OpenRead|0|3| 3|SetNumColumns|0|1| 4|Integer|0|0| 5|OpenRead|2|3| 6|SetNumColumns|2|1| 7|Rewind|0|14| 8|Rewind|2|13| 9|Column|0|0| 10|Column|2|0| 11|Callback|2|0| 12|Next|2|9| 13|Next|0|8| 14|Close|0|0| 15|Close|2|0| 16|Halt|0|0| 17|Transaction|0|0| 18|VerifyCookie|0|4| 19|TableLock|0|3|b 20|Goto|0|1| 21|Noop|0|0| #e8e8bd 2808 doc active 2007 Nov anonymous 2007 Nov 4 2 Documentation GIF images take up too much space The GIF format used for the documentation images takes up too much space. I believe that this is in strict contrast to the "small" feature of SQLite. Converting the GIFs to PNG images saves up to 181 KB (64%) of the documentation storage space: Documentation images as is (mostly GIF): 381 KB == 100% Documentation images as PNG: 292 KB == 77% Documentation images as PNG 16 colors: 137 KB == 36% 16 colors are more than plenty -- the diagrams show no visibility degradation. You might even cut it down to 8 colors to save even more ... #f2dcdc 2793 code active 2007 Nov anonymous 2007 Nov 3 3 fts3 lacks scoping It would be nice if the fts3 symbols could optionally be made private/static as the rest of the sqlite3 library. Not sure why sqlite3_api becomes public when used with the amalgamation, for that matter. make TOP=`pwd` BCC=gcc TCC=gcc AR=ar RANLIB=echo NAWK=gawk -f \ main.mk sqlite3.h sqlite3.c fts3amal.c cat fts3amal.c >> sqlite3.c gcc -DSQLITE_THREADSAFE -DSQLITE_API=static -DSQLITE_PRIVATE=static \ -DSQLITE_EXTERN=static -DSQLITE_ENABLE_FTS3 -c sqlite3.c nm sqlite3.o | grep -v ' [trUbd] ' 00000004 C sqlite3_api 00064da2 T sqlite3Fts3HashClear 000652a4 T sqlite3Fts3HashFind 00064d60 T sqlite3Fts3HashInit 0006533b T sqlite3Fts3HashInsert 00064b4c T sqlite3Fts3Init 00066b34 T sqlite3Fts3InitHashTable 000669bd T sqlite3Fts3PorterTokenizerModule 0006702d T sqlite3Fts3SimpleTokenizerModule #f2dcdc 2791 code active 2007 Nov anonymous 2007 Nov 1 1 Allow building FTS[123] as part of sqlite library with configure See attached patch. #e8e8bd 2787 build active 2007 Nov anonymous 2007 Nov 4 5 sqlite3.pc is not remade the subject says it all, there is no make rule to rebuild the sqlite3.pc from the .in file. it is only possible by hand (./config.status sqlite3.pc) _2007-Nov-22 12:17:50 by drh:_ {linebreak} I don't know what the sqlite3.pc file does. I certainly do not use it for any of my builds on Linux, Mac OSX, or windows. Why should I leave it in the source tree? Isn't the best solution to this problem to simply delete the file? ---- _2007-Nov-22 17:14:27 by anonymous:_ {linebreak} It is indirectly used by pkgconfig. Here's some info on pkgconfig: http://pkg-config.freedesktop.org/wiki/ ---- _2007-Nov-23 15:22:32 by drh:_ {linebreak} Could somebody who understands what the sqlite3.pc file is used for suggest a makefile rule for rebuilding it? #f2dcdc 2770 code active 2007 Nov anonymous 2007 Nov 1 1 Problem with BLOB in 3.5.x ? After I've switched from 3.3.18 to 3.5.2, selecting from table which contains BLOB LONGER THAN ABOUT 990 BYTES returns error "SQL logic error or missing database" after call to _sqlite3_step(). I'm using preprocessed sources downloaded from here. DEBUG build of preprocessed sources works correctly, problem is only in RELEASE build. I'm using VC6.0 to compile. Any idea what could be wrong? Thank you! Can you try to reproduce this with the sqlite shell tool? Thanks. Large blobs work for me with both release and debug builds (not msvc though, gcc/linux). ---- _2007-Nov-12 18:41:37 by anonymous:_ {linebreak} sqlite3.exe provided here works with the database. Problem is only with release build (static library linked into test application). Here is test app which exits with "Error 1" in release build: int main(int argc, char* argv[]) { int rc; sqlite3* db; sqlite3_stmt* stmt; rc = sqlite3_open("n2.db3", &db); rc = sqlite3_prepare(db, "CREATE TABLE [ttt] ([bbb] BLOB)", -1, &stmt, 0 ); rc = sqlite3_step(stmt); rc = sqlite3_reset(stmt); char text[10000],query[20000]; strnset(text,'a',sizeof(text)-1); sprintf(query,"insert into [ttt] values (?)"); rc = sqlite3_prepare(db, query, -1, &stmt, 0 ); rc = sqlite3_bind_blob(stmt,1,text,sizeof(text), SQLITE_TRANSIENT); rc = sqlite3_step(stmt); rc = sqlite3_reset(stmt); rc = sqlite3_prepare(db, "select * from ttt", -1, &stmt, 0 ); rc = sqlite3_step(stmt); if (rc == SQLITE_ROW) { printf("%s: OK",sqlite3_column_text(stmt,1)); } else if (rc == SQLITE_DONE) { printf("DONE"); } else { printf("Error %d",rc); } return 0; } ---- _2007-Nov-12 18:56:24 by drh:_ {linebreak} You should be using sqlite3_finalize() instead of sqlite3_reset(). You are leaking memory. Also, you should use sqlite3_prepare_v2() to avoid problems with changing schemas. But even without those fixes, I cannot reproduce the problem on Linux. ---- _2007-Nov-12 19:39:31 by anonymous:_ {linebreak} Suggested fixes didn't help. I've tried to debug it. It fails in btree.c, line 3056: if( offset+amt > nKey+pCur->info.nData ){ /* Trying to read or write past the end of the data is an error */ return SQLITE_ERROR; } there seems to be different values in release mode. My debugger does not show values of variables in release mode, so I can be wrong, but it seems in release offset is 5 and in debug it is 4. There can be something wrong with compilation, I'll try to figure this out tomorrow. BTW compilation of static libraty in VC6.0 gives 185 warnings. I don't know if it is ok, it haven't caused problems in older sqlite ---- _2007-Nov-13 08:47:28 by anonymous:_ {linebreak} I've turned off "Maximize Speed" option - this is causing the problem. No optimizations and optimize for size seems to be working. But it still makes me nervous :(( I really don't need corrupted database and now I hope it won't slow down too much. Unfortunately old library does not implement replace function so I don't want to switch back. This could be warning to others, I'm using VC++ 6.0 SP 6. Thank you for your time. ---- _2007-Nov-22 17:20:31 by anonymous:_ {linebreak} I have exactly the same problem here (win XP, vc6 SP2) when I link against my sqlite static or dynamic library in release. I have also used boundschecker to check sqlite, and it detects many dangling pointers ! But the strange thing is that I cannot find why these pointers are dangling, here an example: In prepare.c@188 pTab = sqlite3FindTable(db, zMasterName, db->aDb[iDb].zName); Boundchecker say that zMasterName is a dangling pointer, previously released here: in build.c@711: void sqlite3StartTable( Parse *pParse, /* Parser context */ Token *pName1, /* First part of the name of the table or view */ Token *pName2, /* Second part of the name of the table or view */ int isTemp, /* True if this is a TEMP table */ int isView, /* True if this is a VIEW */ int isVirtual, /* True if this is a VIRTUAL table */ int noErr /* Do nothing if table already exists */ ){ } It does not make sens for me, maybe it a false positive from boundchecker, but it is weird. I don't know if these "errors" are related to the "blob" bug in release mode. I will try to debug these error with some "printf" in release mode. Note: The provided dll (the one from the sqlite site) does not have this "bug". ---- _2007-Nov-22 18:27:35 by anonymous:_ {linebreak} More info: It seems that there is a bug in the VC6 (SP6) compiler. In btree.c, line 3056: if( offset+amt > nKey+pCur->info.nData ){ /* Trying to read or write past the end of the data is an error */ return SQLITE_ERROR; } After adding some printf around, It seems that the "speed optimization" compilation flag of VC6 changes the code order in a way that the offset variable is miss incremented !! Two remarks: *: I've traced the calling function, sqlite3BtreeData, and the it call accessPayload with the good offset value *: VC6 produces an internal error: "fatal error C1001: INTERNAL COMPILER ERROR" in the accessPayload function, if I try to access the offset value before this line: aPayload = pCur->info.pCell + pCur->info.nHeader; A dirty workaround could be to change the code order or the local var usage. I'm trying .... #e8e8bd 2776 warn active 2007 Nov anonymous 2007 Nov 5 1 mailing list sqlite-users-digest doesn't work. It remembers registered addresses but doesn't send any emails. #f2dcdc 2771 code active 2007 Nov anonymous 2007 Nov 4 4 Lemon: Generated parser needs stdlib.h (not in default template) I tested a simple do-nothing parser just to get lemon output, and this doesn't compile (if warnings treated as errors) because of non declaration of the =memset()= function for the following statment: =memset(&yygotominor, 0, sizeof(yygotominor));= (added for the resolution of SQLite ticket #2172). The lempar.c just include =stdio.h=, it would suffice to add =stdlib.h= to get the =memset()= declaration (even if all real parsers must include =stdlib.h= to get something really working). _2007-Nov-14 21:02:25 by anonymous:_ {linebreak} Sorry, the needed header is =string.h=, not =stdlib.h= :-) #f2dcdc 2766 code active 2007 Nov drh 2007 Nov 1 1 TCL transaction started from within a query does not commit This is a problem with the TCL interface. Consider the following TCL script: file delete -force test.db test.db-journal sqlite3 db test.db db eval { CREATE TABLE t1(x,y); INSERT INTO t1 VALUES(1,2); CREATE TABLE t2(a,b); INSERT INTO t2 VALUES(8,9); } db eval {SELECT * FROM t1} { db transaction { db eval {UPDATE t2 SET a=a*2} } } The [db transaction] statement starts a transaction and it is suppose to commit the tranaction at the end of the code block. But because the transaction started while a query was active, the tranaction is unable to commit. The TCL interface never commits the tranaction nor does it give any kind of error indication. It is unclear if an error should be returned or if the commit should be deferred until outer query finishes. If the code within the [db transaction] block throws an error, we really need the transaction to rollback right away. Perhaps there should be a new API that cancels all pending queries. Perhaps a call to sqlite3_interrupt() would suffice for this. Need to investigate further.... #e8e8bd 2763 build active 2007 Nov anonymous 2007 Nov 4 4 AIX build failure due to explicit _XOPEN_SOURCE definition Our AIX machine has started having problems building the new sqlite 3.5.2 source. I'm no AIX expert, but I did some digging and I think it's due to sqliteInt.h defining _XOPEN_SOURCE without also defining _ALL_SOURCE. AIX system header files usually define this themselves (in /usr/include/ standards.h) but only if _XOPEN_SOURCE was previously undefined. Has anyone else come across this bug? I'm willing to believe it's our build system if not; we can work around it by setting _ALL_SOURCE on the command-line, but I thought it might be useful to raise a bug anyway. See also tickets #2673, #2681, and #2741. I do not have access to an AIX machine and so have no ability to debug this problem. If you have suggested patches we will consider them. Otherwise, there is not much we can do about this ticket. #e8e8bd 2760 new active 2007 Nov anonymous 2007 Nov 5 4 request: sqlite3_unlink() to delete db files. Hi! Today i came across a use case where i would like client code to be able to delete an underlying sqlite3 db, but that code doesn't have immediate access to the file name of that db (without refactoring the db wrapper code). An interesting feature addition would, IMO, be: int sqlite3_unlink( sqlite3 * db, bool closeTheFile ); Unlinks the file associated with the given database. It does not alter the database in any way (thus is it a no-op on a :memory: database). The closeTheFile flag specifies whether the file handle associated with db should also be closed (and thus db must also be closed), or just unlinked (e.g., as temporary databases are unlinked right after creation but kept open). After browsing through the VFS API a bit, i see that there is an xDelete function, but i'm not sure if its semantics require that the underlying file handle be closed. i don't see an extra xClose member of VFS, so i assume that xDelete also handles closing the file handle. If these were split into two features, sqlite3_unlink() could be implemented very easily. :) #e8e8bd 2756 new active 2007 Nov anonymous 2007 Nov 1 1 allow vacuum to change pragma setting instead of using existing ones we've got databases created with page_size of 1k, and we'd like to change that setting to 4k. vacuum creates a temporary db, attach it to the current connection, creates the tables (based on what's in the old db), and then selects from the old db and inserts into the new one. vacuum does exactly what we need (creating a new db from an old one), but it re-uses the existing pragmas for page size, auto vacuum and reserved page size. from sqlite.c, see sqlite3RunVacuum() sqlite3BtreeSetPageSize(pTemp, sqlite3BtreeGetPageSize(pMain), sqlite3BtreeGetReserve(pMain)); dr hipp points out that the the operands to vacuum are unused. from sqlite.c: sqlite3VdbeAddOp(v, OP_Vacuum, 0, 0); one solution would be to allow the user to specify the page size, reserve page size, and autovacuum as optional params to vacuum. he had an idea of using the signedness of the first operand to represent the autovacuum setting (since after a table is created, you can change the setting from auto to incremental, but you can't change it from none to auto (or none to incremental) #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 2753 code active 2007 Nov anonymous 2007 Nov drh 3 3 Master journal files sometimes not deleted In the 3.4.1 amalgamation, in vdbeCommit, the master journal file is created, and deleted at the end or if there is an error. But it looks like there is one case where it gets closed but not deleted. The code is: for(i=0; rc==SQLITE_OK && i
Index: src/pager.c =================================================================== RCS file: /sqlite/sqlite/src/pager.c,v retrieving revision 1.393 diff -u -3 -p -r1.393 pager.c --- src/pager.c 20 Oct 2007 13:17:55 -0000 1.393 +++ src/pager.c 26 Oct 2007 21:00:33 -0000 @@ -261,11 +261,11 @@ struct PgHdr { PgHdr *pNextHash, *pPrevHash; /* Hash collision chain for PgHdr.pgno */ PagerLruLink free; /* Next and previous free pages */ PgHdr *pNextAll; /* A list of all pages */ - u8 inJournal; /* TRUE if has been written to journal */ - u8 dirty; /* TRUE if we need to write back changes */ - u8 needSync; /* Sync journal before writing this page */ - u8 alwaysRollback; /* Disable DontRollback() for this page */ - u8 needRead; /* Read content if PagerWrite() is called */ + u8 inJournal:1; /* TRUE if has been written to journal */ + u8 dirty:1; /* TRUE if we need to write back changes */ + u8 needSync:1; /* Sync journal before writing this page */ + u8 alwaysRollback:1; /* Disable DontRollback() for this page */ + u8 needRead:1; /* Read content if PagerWrite() is called */ short int nRef; /* Number of users of this page */ PgHdr *pDirty, *pPrevDirty; /* Dirty pages */ #ifdef SQLITE_ENABLE_MEMORY_MANAGEMENT---- _2007-Oct-26 21:20:09 by anonymous:_ {linebreak} How about a compromise. What about some optional setting like? #ifndef OMIT_SQLITE_BITFIELDS # define SQLITE_BITFIELD(X) : X #else # define SQLITE_BITFIELD(X) #endif ... u8 inJournal SQLITE_BITFIELD(1); /* TRUE if has been written to journal */ #e8e8bd 2377 build active 2007 May pweilbacher 2007 Jul 4 3 Allow easy DLL build on OS/2 =Makefile.in= contains a target to build a DLL on Windows but unfortunately it doesn't work for OS/2. Current GCC versions use a calling convention that prepends underscores and these need to go into the =.def= file. To make a nice DLL header some extra lines in the =.def= file would be nice, too, that are probably incompatible with Windows linkers. Finally, to make the DLL usable we need to create an import library =.lib=. As a nice-to-have feature I would like the DLL to be named after the VERSION but without the dot, as I expect DLLs from version 3.0.x to be imcompatible with 3.3.x or other future 3.x versions... I don't know a clever way to do that other than introducing a new variable into configure.ac and Makefile.in. _2007-Jul-03 23:42:20 by pweilbacher:_ {linebreak} SQLite releases for OS/2 are now built from the amalgamation, so this is only useful for checks of the CVS code between releases. Not sure if it still makes sense to check this in, but at least the patch can stay attached to the ticket for possible future reference. #f2dcdc 2393 code active 2007 May anonymous 2007 May 4 4 pragma cache_size not accurate when set to less than 10 It seems that the smallest the cache_size can be is 10 pages, despite what can be set and is reported by the cache_size and default_cache_size pragmas: /* ** Change the maximum number of in-memory pages that are allowed. */ void sqlite3PagerSetCachesize(Pager *pPager, int mxPage){ if( mxPage>10 ){ pPager->mxPage = mxPage; }else{ pPager->mxPage = 10; } } ... static int sqlite3BtreeSetCacheSize(Btree *p, int mxPage){ BtShared *pBt = p->pBt; sqlite3PagerSetCachesize(pBt->pPager, mxPage); return SQLITE_OK; } ... if( sqlite3StrICmp(zLeft,"cache_size")==0 ){ if( sqlite3ReadSchema(pParse) ) goto pragma_out; if( !zRight ){ returnSingleInt(pParse, "cache_size", pDb->pSchema->cache_size); }else{ int size = atoi(zRight); if( size<0 ) size = -size; pDb->pSchema->cache_size = size; sqlite3BtreeSetCacheSize(pDb->pBt, pDb->pSchema->cache_size); ... if( sqlite3StrICmp(zLeft,"default_cache_size")==0 ){ static const VdbeOpList getCacheSize[] = { { OP_ReadCookie, 0, 2, 0}, /* 0 */ { OP_AbsValue, 0, 0, 0}, { OP_Dup, 0, 0, 0}, { OP_Integer, 0, 0, 0}, { OP_Ne, 0, 6, 0}, { OP_Integer, 0, 0, 0}, /* 5 */ { OP_Callback, 1, 0, 0}, }; int addr; if( sqlite3ReadSchema(pParse) ) goto pragma_out; if( !zRight ){ sqlite3VdbeSetNumCols(v, 1); sqlite3VdbeSetColName(v, 0, COLNAME_NAME, "cache_size", P3_STATIC); addr = sqlite3VdbeAddOpList(v, ArraySize(getCacheSize), getCacheSize); sqlite3VdbeChangeP1(v, addr, iDb); sqlite3VdbeChangeP1(v, addr+5, SQLITE_DEFAULT_CACHE_SIZE); }else{ int size = atoi(zRight); if( size<0 ) size = -size; sqlite3BeginWriteOperation(pParse, 0, iDb); sqlite3VdbeAddOp(v, OP_Integer, size, 0); sqlite3VdbeAddOp(v, OP_ReadCookie, iDb, 2); addr = sqlite3VdbeAddOp(v, OP_Integer, 0, 0); sqlite3VdbeAddOp(v, OP_Ge, 0, addr+3); sqlite3VdbeAddOp(v, OP_Negative, 0, 0); sqlite3VdbeAddOp(v, OP_SetCookie, iDb, 2); pDb->pSchema->cache_size = size; sqlite3BtreeSetCacheSize(pDb->pBt, pDb->pSchema->cache_size); The information reported is not accurate: SQLite version 3.3.17 Enter ".help" for instructions sqlite> pragma cache_size; 2000 sqlite> pragma default_cache_size; 2000 sqlite> pragma default_cache_size=3; sqlite> pragma cache_size=7; sqlite> pragma default_cache_size; 3 (should be 10) sqlite> pragma cache_size; 7 (should be 10) #f2dcdc 2388 code active 2007 May anonymous 2007 May 1 3 ORDER BY fails on compound select The query below was known to be working as late as 3.3.14, but in 3.3.17 it errors with "ORDER BY term number 1 does not match any result column". Debugging with GDB shows that sqlite3NameFromToken(&pE->token) doesn't return anything, and pE->token looks pretty empty, so possibly this could be a parsing issue? The query is: SELECT docs.guid, docs.info, conversations.subject, conversations.date FROM conversations JOIN docs ON docs.guid = conversations.guid WHERE conversations.guid IN ( SELECT docs.guid FROM conversations JOIN docs ON docs.guid = conversations.guid WHERE conversations.date >= (SELECT date FROM conversations WHERE guid = ?6) AND docs.collection = ?1 AND ((?2 == 0) OR (conversations.sources & ?2) != 0) AND ((conversations.sources & ?3) == 0) AND test_info_flags(docs.info, ?7, ?8) LIMIT (?5 + 1) ) UNION SELECT docs.guid, docs.info, conversations.subject, conversations.date FROM conversations JOIN docs ON docs.guid = conversations.guid WHERE conversations.guid IN ( SELECT docs.guid FROM conversations JOIN docs ON docs.guid = conversations.guid WHERE conversations.date < (SELECT date FROM conversations WHERE guid = ?6) AND docs.collection = ?1 AND ((?2 == 0) OR (conversations.sources & ?2) != 0) AND ((conversations.sources & ?3) == 0) AND test_info_flags(docs.info, ?7, ?8) LIMIT ?4 ) ORDER BY conversations.date DESC, docs.guid DESC; _2007-May-27 16:27:23 by anonymous:_ {linebreak} 2 workarounds: ORDER BY 4 DESC, 1 DESC; or SELECT docs.guid as "guid", docs.info as "info", conversations.subject as "subject", conversations.date as "date" ... ORDER BY date DESC, guid DESC; Smaller test case: create table x1(a); select x1.a from x1 union select x1.a from x1 order by x1.a; -- SQL error: ORDER BY term number 1 does not match any result column -- error in latest 3.3.17+ CVS, but works in SQLite 3.2.2, 3.3.13 select x1.a from x1 union select x1.a from x1 order by a; -- SQL error: ORDER BY term number 1 does not match any result column -- has never worked in previous versions select x1.a from x1 union select x1.a from x1 order by 1; -- ok in all versions select x1.a as a from x1 union select x1.a from x1 order by a; -- ok in all versions ---- _2007-May-27 16:46:19 by anonymous:_ {linebreak} Does this query work on other popular databases? create table x1(a integer); select x1.a from x1 union select x1.a from x1 order by x1.a; I'd expect "order by a" to work on most databases, but not "order by x1.a" because the table name would have been lost by the UNION. #f2dcdc 2383 code active 2007 May anonymous 2007 May 3 3 Inconsistent conversion of BLOB revisited BLOBs of function results are converted as UTF-8 even if the encoding is UTF-16. $ ./sqlite3 SQLite version 3.3.17 Enter ".help" for instructions sqlite> pragma encoding = 'UTF-16BE'; sqlite> select quote(cast(cast('ab' as blob) as text)); -- OK 'ab' sqlite> select quote(cast(a as text)) from (select X'00610062' a); -- OK 'ab' sqlite> select quote(cast(X'00610062' as text)); -- fixed by [3975] 'ab' sqlite> select quote(cast(substr(X'110061006222', 2, 4) as text)); -- NG '' sqlite> select quote(cast(substr(X'11616222', 2, 2) as text)); 'ab' sqlite> select quote(upper(substr(X'11616222', 2, 2))); 'AB' #e8e8bd 2381 doc active 2007 May anonymous 2007 May 3 3 'VACUUM' aborts an active transaction. *VACUUM* aborts an active transaction. I'm not sure if this is the intended behavior. I didn't see any documentation to this effect. sqlite> .sc CREATE TABLE bar ( c1 integer primary key not null ); sqlite> select max(oid) from bar; count(oid) ---------- 240 sqlite> begin; sqlite> delete from bar; sqlite> select max(oid) from bar; mcount(oid) ---------- 0 sqlite> vacuum; SQL error: cannot VACUUM from within a transaction sqlite> select max(oid) from bar; count(oid) ---------- 240 #f2dcdc 2363 code active 2007 May anonymous 2007 May 3 3 Couldn't build 3.3.17 on Cygwin/Vista I didn't see anything quite resembling this on a cursory scan thru the buglist; pardon my goof if it's a duplicate. When I tried to build on Cygwin on Vista, things got to the install phase, and then the Makefile tried to execute cc sqlite3.c -o sqlite3 which didn't appear to be a defined rule in the Makefile. This then failed because of a lack of a main program, and the install failed. After a quick scan through the Makefile, I decided to change the target for the install rule to install: sqlite3$(TEXE) libsqlite3.la sqlite3.h ${HAVE_TCL:1=tcl_install} which seemed to remedy the problem. Suggested fix: modify Makefile as shown above. _2007-May-23 14:29:20 by anonymous:_ {linebreak} Another vote for this #f2dcdc 2378 code active 2007 May anonymous 2007 May 2 2 Quoted fields come back corrupted, using GROUP BY *Description:* When executing a query, where field names are quoted, and using GROUP BY, the field names are returned with quotes around. *SQLite version:* SQLite support => enabled PECL Module version => 2.0-dev $Id: sqlite.c,v 1.166.2.13.2.7 2007/03/06 02:17:13 stas Exp $ SQLite Library => 2.8.17 SQLite Encoding => iso8859 *Reproduce code* string(1) "1" } *Actual result* array(1) { [""id""]=> string(1) "1" } _2007-May-21 18:14:27 by anonymous:_ {linebreak} Corrupted is probably not the right term, but the fields are returned with quotes around them in sqlite3 as well when group by is used on a quoted column: SQLite version 3.3.17 Enter ".help" for instructions sqlite> .header on sqlite> create table t1(a); sqlite> insert into t1 values(1); sqlite> select "a" from t1; a 1 sqlite> select "a" from t1 order by 1; a 1 sqlite> select "a" from t1 group by 1; "a" 1 sqlite> select "a" from t1 group by 1 order by 1; "a" 1 sqlite> select "a" from t1 group by a; "a" 1 #f2dcdc 2376 code active 2007 May anonymous 2007 May 3 3 -batch doesn't work soon enough on Microsoft XP, given any.db and a batch file bug.bat containing sqlite3 -batch -init bug.rc any.db "select 1;" and bug.rc containing .mode column Running bug.bat gives the message "Loading resources from bug.rc" I put in "-batch" so that the "Loading..." message would be suppressed, but it printed anyway. #f2dcdc 2374 code active 2007 May anonymous 2007 May 5 5 "ALTER TABLE foo RENAME TO bar" will quote bar (ie. 'bar') Cosmetic issue. When a table is renamed, its representation in the schema ends up being quoted. Whilst this is harmless it also seems to be unnecessary? sqlite> create table foo ( c1 integer ); sqlite> .sc CREATE TABLE foo ( c1 integer ); sqlite> alter table foo rename to bar; sqlite> .sc CREATE TABLE 'bar' ( c1 integer ); sqlite> select oid,sql from sqlite_master; rowid sql ---------- --------------------------------- 1 CREATE TABLE 'bar' ( c1 integer ) #e8e8bd 2373 new active 2007 May anonymous 2007 May 4 4 "create table as explain
typedef unsigned long long int rowNumberContext;
// aggregate step callback
void rowNumberStep(sqlite3_context *context, int argc, sqlite3_value **argv) {
// initialize or get aggregate function context
rowNumberContext *agg_context = (rowNumberContext*)sqlite3_aggregate_context(context, sizeof(rowNumberContext));
(*agg_context)++;
}
// aggregate final callback
void rowNumberFinal(sqlite3_context *context) {
sqlite3_result_int64(context, *((rowNumberContext *)sqlite3_aggregate_context(context, sizeof(rowNumberContext))));
}
// then just create function:
sqlite3_create_function(db_handle, "row_number", 0, SQLITE_ANY, null, null, rowNumberStep, rowNumberFinal);
I hope I get it right. This piece of sample code has *NOT* been tested! Use it at your own risk. ----------- The code above is a good implementation of count(*), but not row_number(). I think Row_Number() is one of the SQL2003 windowing functions. Implementing these would require modifications to the parser, compiler and vdbe layers of sqlite. Not possible using current APIs. At the current time queries that use Row_Number() will have to be rewritten to use temp tables as intermediate steps.
#e8e8bd 2417 new active 2007 Jun anonymous 2007 Nov drh 3 3 Idea for read write concurrency. This is not a problem, but rather an idea on how to resolve the reader/writer concurrency issues encountered in sqlite. The idea is to allow a reader and writer to work concurrently not blocking each other. Dual writers would of course block. When a write occurs: 1. block level changes are made to the database file. 2. Pre-image of that change is written to the journal. Readers: 1. File I/O on the main file would occur normally. 2. If the block encountered is "new" ie one that was written out by the writer. Then get the original block from the Journal file. In order to determine "NEW" a change number could be put on each block. When a READ (select) begins it would first determine the starting global change number. (maybe on the master block?) When a write occurs it would read the Master blocks change number. (increment this in memory) and use write new blocks with the new value. At commit. The Master block would be updated and the txn journal marked for purge if there are pending reads. -- Drawbacks: Reading becomes dependent upon the txn journal. -- Implementation of BLOCK level versioning may ultimately be a simpler approach. Idea would be for a seperate file conaining versioned blocks. This file could be accessed instead of the txn journal. _2007-Nov-08 15:12:00 by anonymous:_ {linebreak} DRH: Also unaddressed in the proposal is how to locate a particular page within the journal file without having to do (performance killing) sequential scan of the possible very large file. Resolution of page access to avoid sequential scans of Txn Journal. When a writer is making the modification to a page first it writes the original page to the journal. At this point the journal file offset location is known. Save this offset in the "NEW" page being written into the database file. This implements a backwards chaining of pages into the txn journal. The reader upon reading the db file page would recognize (see above) that the page is dirty. Acquire the txn journal offset from the dirty page, Read the page from the journal until the starting page is found. This would eliminate any sequential scanning, but may require more than one read request.
#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_testWhen 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-2This 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);