bgcolor # Status Created By Subsys Due Date SCR Assigned Svr Pri Title _Description _Remarks #f2dcdc 2916 active 2008 Feb anonymous Pending 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 #cfe8bd 2914 active 2008 Jan anonymous Pending 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. #e8e8bd 2911 active 2008 Jan anonymous Pending 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 #cfe8bd 2908 active 2008 Jan anonymous Pending 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 2907 active 2008 Jan anonymous Pending 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) #e8e8bd 2905 active 2008 Jan anonymous Pending 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) #e8e8bd 2903 active 2008 Jan anonymous Pending 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. #cfe8bd 2902 active 2008 Jan anonymous Pending 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. #cfe8bd 2901 active 2008 Jan anonymous Pending 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 2898 active 2008 Jan anonymous Pending 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 2897 active 2008 Jan anonymous Pending 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. #f2dcdc 2893 active 2008 Jan anonymous Pending 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? #cfe8bd 2886 active 2008 Jan anonymous Pending 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 #cfe8bd 2882 active 2008 Jan anonymous Pending 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. #cfe8bd 2879 active 2008 Jan anonymous Pending 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 2878 active 2008 Jan anonymous Fixed 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. #e8e8bd 2875 active 2008 Jan anonymous Pending 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 active 2008 Jan anonymous Pending 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 active 2008 Jan anonymous Pending 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." #e8e8bd 2867 active 2008 Jan anonymous Pending 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 2865 active 2007 Dec anonymous Pending 1 2 FTS3 does not build with amalgamation in CVS Grab the latest CVS sources, then run: ./configure make sqlite3.c grep sqlite3Fts3Init sqlite3.c extern int sqlite3Fts3Init(sqlite3*); rc = sqlite3Fts3Init(db); If you compile sqlite3.c with -DSQLITE_ENABLE_FTS3, then sqlite3Fts3Init is unresolved. For some reason, sqlite3Fts3Init and fts3.c was not included in the sqlite3.c amalg. It used to work correctly in 3.5.4. _2007-Dec-30 18:17:57 by anonymous:_ {linebreak} Nevermind, "make sqlite3.c" has never built with the fts3 sources in 3.5.4 or before. You have to run ext/fts3/mkfts3amal.tcl ---- _2007-Dec-30 18:20:56 by anonymous:_ {linebreak} It seems that the sqlite3+fts3 amalg can only be built from main.mk, not Makefile. #cfe8bd 2863 active 2007 Dec anonymous Pending 2 3 test cast-3.14, cast-3.18 and cast-3.24 fail test cast-3.{14,18,24} fail on freebsd-6.3-PRERELEASE2: cast-3.14...^M Expected: [9223372036854774784]^M Got: [9223372036854773760]^M cast-3.18...^M Expected: [-9223372036854774784]^M Got: [-9223372036854773760]^M cast-3.24...^M Expected: [9223372036854774784]^M Got: [9223372036854773760]^M I used tcl8.4 from ports with no threads and here was the config line: ../sqlite-3.5.4/configure --prefix=/home/marc/local --with-tcl=/usr/local/lib/tcl8.4/ This was built on an ibm t30 laptop #e8e8bd 2859 active 2007 Dec anonymous Pending drh 3 2 Inconsistent column names with DISTINCT Given the following SQL:{linebreak} CREATE TABLE foo(a,b); INSERT INTO foo (a, b) VALUES (1,2); SQLite returns inconsistent column names when using the DISTINCT clause:{linebreak} SELECT DISTINCT foo.A, foo.B FROM foo; foo.A|foo.B 1|2 SELECT DISTINCT a, b FROM foo; a|b 1|2 SELECT DISTINCT * FROM foo; a|b 1|2 SELECT DISTINCT foo.* FROM foo; a|b 1|2 Compared with SELECT without DISTINCT:{linebreak} SELECT foo.A, foo.B FROM foo; a|b 1|2 SELECT a, b FROM foo; a|b 1|2 SELECT * FROM foo; a|b 1|2 SELECT foo.* FROM foo; a|b 1|2 #e8e8bd 2857 active 2007 Dec anonymous Pending 2 2 GROUP BY cost estimate wrong with WHERE clause There seems to be an issue with the sqlite cost heuristic with an INDEX present on GROUP BY with certain types of WHERE clauses. Given the database formed by running these statements: create table stuff(a,b,c,d); insert into stuff values(1,2,3,4); create temp view v1 as select random()%100, random()%100, random()%1000, random()%10000 from stuff x, stuff y; insert into stuff select * from v1; insert into stuff select * from v1; insert into stuff select * from v1; insert into stuff select * from v1; insert into stuff select * from v1; create index stuff_b on stuff(b); create index stuff_c on stuff(c); create index stuff_d on stuff(d); analyze; Using sqlite.org's sqlite3-3.5.4.bin, this query takes 47 seconds: select c from stuff where a=23 group by c; while this query takes just 2 seconds: select c from stuff where a=23 group by +c; It is more efficient in this case to do a full table scan instead of using the INDEX on column c. _2007-Dec-23 23:14:06 by anonymous:_ {linebreak} The queries above both run in a couple of seconds with this naive patch:
--- src/where.c 12 Dec 2007 17:42:53 -0000 1.266 +++ src/where.c 23 Dec 2007 22:48:37 -0000 @@ -1514,6 +1514,12 @@ static double bestIndex( flags = 0; } + if( pWC && pWC->nTerm>0 && pOrderBy ){ + /* Reduce cost if both an ORDER/GROUP BY exists with a WHERE. */ + cost /= 100; /* A very rough guess. */ + WHERETRACE(("... WHERE + ORDER BY decreases cost to: %.9g\n", cost)); + } + /* If the table scan does not satisfy the ORDER BY clause, increase ** the cost by NlogN to cover the expense of sorting. */ if( pOrderBy ){But it has not been tested on queries with more than one table. Its logic could be flawed. ---- _2007-Dec-24 00:09:00 by drh:_ {linebreak} The complaint is centered around these two queries: /* 1 */ SELECT c FROM stuff WHERE a=23 GROUP BY c; /* 2 */ SELECT c FROM stuff WHERE a=23 GROUP BY +c; Query 1 runs in about 40 seconds and query 2 in about 1.5 seconds on my macbook. But with the patch, both queries run in about 1.5 seconds. Fair enough. But now consider these two queries: /* 3 */ SELECT c FROM stuff WHERE a!=23 GROUP BY c; /* 4 */ SELECT c FROM stuff WHERE a!=23 GROUP BY +c; In this case, query 3 runs in 42 seconds on an unpatched version of 3.5.4 and query 4 runs in about 109 seconds. So in cases where the WHERE clause is not particularly selective, the first version is faster than the second by a good margin. On a patched version of 3.5.4, both queries 3 and 4 run in about 110 seconds. So it seems to me that the patch is robbing Peter to pay Paul. It makes ORDER BY queries with very selective WHERE clauses run faster but at the expense of making queries with unselective WHERE clauses running slower. But notice this: in the current (unpatched) implementation, the programmer at least has the ability to select a different algorithm by the judicious placement of a "+" sign. After the patch, this is no longer possible. The patch forces the second algorithm to be used in all cases, even cases where it is slower. It seems to me to be better to leave things as they are since the current approach at least allows the programmer to override SQLite's algorithm selection if SQLite chooses incorrectly. The only way, it seems to me, to automatically choose the correct algorithm is to devise some test that will determine (in advance) whether or not the WHERE clause weeds out many or few rows from the result set. I'm thinking that determination is going to be very hard (or impossible) to do without first doing a full table scan. ---- _2007-Dec-24 05:40:47 by anonymous:_ {linebreak} It think it would be surprising to average users that _adding_ an index (on column C in this case) may significantly _decrease_ query performance for some queries. It was surprising to me, at least. In my opinion, a query being 20 times slower in a default bad guess situation is worse than a query only being 2.5 times slower with a default bad guess in a worst case scenario. It's a question of relative magnitude of the difference. This is why I think that the database should err on the side of the WHERE clause having a more selective bias. (Side note: the query timings difference is less pronounced if you use PRAGMA temp_store=memory, in which case query 3 running on an unpatched 3.5.4 takes just 50% more time to run than query 4 on my machine.) But you raise a good point in that if there's a wrong guess in the selectivity bias it would be nice to be able to manually override it. How much do you hate this type of syntax that some other databases use? select c from stuff where a!=23 group by /*+stuff_c*/ c; SQLite does not currently offer a way to pick a specific index. I think it would be quite useful. ---- _2007-Dec-24 17:05:16 by anonymous:_ {linebreak} Another option is to collect WHERE clause statistics in a table like create table sqlite_stat2( where_clause_md5 BLOB primary key, where_clause TEXT, rows_examined INT, rows_true INT ); where the last 2 columns are cumulative for each query. The statistics option could be enabled/disabled via a PRAGMA sqlite_collect_statistics. The where_clause column could be a string generated fairly easily from the walking the parse tree of the resolved Select statement's pWhere. This way the where_clause is normalized and a single query with many subselects could generate more than 1 where_clause, and different queries that happen to use the same normalized where clause would update the same entry in the stat2 table. where_clause normalization would strip off aliases and only refer to the original table and column names. For example the 2 queries below: -- CREATE TABLE t1(a, b); -- CREATE TABLE t2(b, c); SELECT t1.a*c as AC, t2.b-a as BA FROM t1, t2 WHERE AC>BA; SELECT *, t1.a Foo FROM t2, t1 WHERE Foo*c > t2.b - t1.a; would generate the same normalized where_clause string "(T1.A*T2.C)>(T2.B-T1.A)". The table information is already encoded within it. The generated VDBE code would have to generate Mem counters that would be incremented by each WHERE test, and lazily updated at the end of transactions or periodically written to the stat2 table to minimize disk use, as this information is not critical. One could also manually set the stat2 table with statistical values they would like their queries to use even if PRAGMA sqlite_collect_statistics=off; Any time the schema is changed, the entire sqlite_stat2 table would be cleared. #f2dcdc 2842 active 2007 Dec anonymous Pending 1 1 .import does not recongnise NULL values .import function fails to see NULL values in csv files as NULL values...instead they are treated as the string "NULL". This is with .mode list and separator , But behaves similarly for .mode csv Also if one outputs a table with NULL values to a file, then re-imports that file, again .import does not recognise the values as NULL, but as "NULL". Everything here also applies to empty strings in files, e.g. instead of "NULL" using nothing... This is a showstopper for us since we want to import a large amount of data with many tables containing NULL values. I can't see any valid reason for .import not to recognise the same syntax as the command line. Note that something like: sqlite3 my.db insert into MY_TABLE values (1,"foo","bar",NULL) ..works fine. It is just .import that appears to be broken. _2007-Dec-14 16:39:51 by rdc:_ {linebreak} .import only inserts string values into database tables. If your column has a declared type that changes the columns affinity to numeric or integer, then those strings will be converted to numeric values by the SQLIte library. The workaround is to simply insert a unique string where ever you want a NULL value, and then run an update that replaces those strings with real NULL values. If you inserted the string 'NULL' then do this after the .import update t set field = null where field = 'NULL'; You will have to repeat this for each field in your table that might contain the 'NULL' string. #cfe8bd 2825 active 2007 Dec anonymous Pending 3 3 FormatMessage (win32) should use extra flag and convert from Unicode The call to FormatMessageA in the win32 source code needs to have the flags changed from: FORMAT_MESSAGE_FROM_SYSTEM to FORMAT_MESSAGE_FROM_SYSTEM | FORMAT_MESSAGE_IGNORE_INSERTS This ensures that any system messages that expect arguments do not try to grab the argument from some random memory location. ref: http://blogs.msdn.com/oldnewthing/archive/2007/11/28/6564257.aspx _2007-Dec-06 14:07:53 by anonymous:_ {linebreak} I also noticed that the result is NOT converted to UTF-8. FormatMessageA returns the text in the local ANSI codepage. FormatMessageW should be used on NT systems, and either result should be converted to the SQLite UTF-8 default. ---- _2007-Dec-11 00:34:37 by anonymous:_ {linebreak} to simplify what is meant even more... http://www.sqlite.org/cvstrac/fileview?f=sqlite/src/os_win.c&v=1.118 Search for FormatMessageA (only 1 instance) - FORMAT_MESSAGE_FROM_SYSTEM, + FORMAT_MESSAGE_FROM_SYSTEM | FORMAT_MESSAGE_IGNORE_INSERTS, No breakage, ensures that no crashes with some messages (e.g. filesystem errors). The encoding issue should be addressed separately. ---- _2007-Dec-11 01:27:07 by anonymous:_ {linebreak} The function should be changed to the following to correctly handle the conversion from Unicode/MBCS.
static void winDlError(sqlite3_vfs *pVfs, int nBuf, char *zBufOut){ int error = GetLastError(); #if OS_WINCE if( error>0x7FFFFFF ){ sqlite3_snprintf(nBuf, zBufOut, "OsError 0x%x", error); }else{ sqlite3_snprintf(nBuf, zBufOut, "OsError %d", error); } #else if( isNT() ){ LPWSTR zWinTemp = NULL; DWORD dwLen = FormatMessageW( FORMAT_MESSAGE_ALLOCATE_BUFFER | FORMAT_MESSAGE_FROM_SYSTEM | FORMAT_MESSAGE_IGNORE_INSERTS, NULL, error, 0, (LPWSTR) &zWinTemp, 0, 0 ); if (dwLen > 0) { char * zOut = unicodeToUtf8(zWinTemp); LocalFree(zWinTemp); sqlite3_snprintf(nBuf, zBufOut, "%s", zOut); free(zOut); } }else{ LPSTR zWinTemp = NULL; DWORD dwLen = FormatMessageA( FORMAT_MESSAGE_ALLOCATE_BUFFER | FORMAT_MESSAGE_FROM_SYSTEM | FORMAT_MESSAGE_IGNORE_INSERTS, NULL, error, 0, (LPSTR) &zWinTemp, 0, 0 ); if (dwLen > 0) { char * zOut = mbcsToUtf8(zWinTemp); LocalFree(zWinTemp); sqlite3_snprintf(nBuf, zBufOut, "%s", zOut); free(zOut); } } #endif }#cfe8bd 2814 active 2007 Nov anonymous Pending 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. #f2dcdc 2810 active 2007 Nov anonymous Pending 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| #f2dcdc 2809 active 2007 Nov anonymous Pending 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. #cfe8bd 2793 active 2007 Nov anonymous Pending 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 active 2007 Nov anonymous Pending 1 1 Allow building FTS[123] as part of sqlite library with configure See attached patch. #f2dcdc 2770 active 2007 Nov anonymous Pending 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 .... #f2dcdc 2766 active 2007 Nov drh Pending 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.... #cfe8bd 2761 active 2007 Nov anonymous Pending 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. #cfe8bd 2755 active 2007 Nov anonymous Pending 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 #cfe8bd 2753 active 2007 Nov anonymous New 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
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. #f2dcdc 2715 active 2007 Oct anonymous Pending 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. #e8e8bd 2708 active 2007 Oct anonymous Pending 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 2684 active 2007 Oct anonymous Pending 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 2664 active 2007 Sep danielk1977 Pending 1 1 attaching the same db twice in shared-cache mode fails The following SQL script can cause an assert() to fail in shared-cache mode. ATTACH 'db' AS aux1; ATTACH 'db' AS aux2; CREATE TABLE aux1.abc(a, b, c); CREATE TABLE aux2.abc(a, b, c); See also #2653 #f2dcdc 2652 active 2007 Sep drh Pending 1 1 Aggregate function cannot be used from within a subquery The following SQL fails: CREATE TABLE t1(x,y); INSERT INTO t1 VALUES(1,2); CREATE TABLE t2(z); INSERT INTO t2 VALUES(1); SELECT (SELECT y FROM t1 WHERE x=min(z)) FROM t2; Problem reported on the mailing list. _2007-Sep-23 16:01:09 by anonymous:_ {linebreak} Your syntax appears to be incorrect.{linebreak} SQLite v3.4.2 CREATE TABLE t1(x,y); CREATE TABLE t2(z); INSERT INTO t1 VALUES(1,21); INSERT INTO t1 VALUES(2,22); INSERT INTO t1 VALUES(3,23); INSERT INTO t2 VALUES(3); INSERT INTO t2 VALUES(2); INSERT INTO t2 VALUES(1); What you wanted to do: SELECT y FROM t1 WHERE x=(SELECT min(z) FROM t2); 21 -- works as expected What you did: SELECT (SELECT y FROM t1 WHERE x=min(z)) FROM t2; SQL error near line []: misuse of aggregate function min() #cfe8bd 2634 active 2007 Sep anonymous Pending 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#c8c8c8 2632 review 2007 Sep anonymous Works_As_Designed danielk1977 5 2 sqlite3* not available within loadable extension
sqlite3* db = context->s.db;is the required variable for use with sqlite3_prepare, sqlite3_step and sqlite3_finalize etc. (That's my work-around){linebreak} What is needed only for loadable extension developers to stay aligned with new versions is the re-arrangement of existing struct sqlite3_context {Mem s} and struct Mem {sqlite3 *db} to become able to simply *typecast* sqlite3_context* into a sqlite3*; all the other internals of Mem would stay private to vdbe even when version numbers will grow.{linebreak} And now to answer the question "why the hell is he asking for this?" here my explanation by an example of what I made running:{linebreak}
select execSQL("select 'iniTable' as Title;select * from iniTable", '|', X'0D0A', X'0D0A'||'----'||X'0D0A' ); iniTable ---- Common|Integer|4711 Common|Float|3.1416927 Common|Double|3.1416927 Common|Text|Dies ist Text Common|Blob|X'0D0A'Think about an application which has to create a whole InMemory-Database table structure inclusive triggers and views:
select execSQL(stmt) from StatementTable where Usage='InMemory';And all within one and the same transaction. It is really that easy! This could answer also some points in the wishlist of the SQLite3 web site; all to do this is reordering Mem s in sqlite3_context and sqlite3*db in struct Mem to the beginning of the structures.{linebreak}Doing this with the release of version 3.5 is really a good moment, isn't it? _2007-Sep-07 11:54:38 by danielk1977:_ {linebreak} The usual approach is to pass the sqlite3* handle to sqlite3_create_function() as the 5th argument and then retrieve it from within the user function implementation using sqlite3_user_data(). If you depend on the definitions of internal data structures, you're living dangerously. ;) ---- _2007-Sep-07 12:54:56 by anonymous:_ {linebreak} Is then the following correct and will work in the same expected way (same pointer within ld-ex-function)? Even with sqlite_auto_extension called at DLL load time? (Win32 with Amalgamation and BDS2006){linebreak}
#includeThank's for the smilie and your hint. Of cause, I do not want to live dangerous. This was the purpose of my posting to get this fixed officially with the next release. But if you say it is not worth the "small change" of reordering and I'm doing it correct this way, then that's fine. I started similar to your proposal by using a static variable and assigned it from within sqlite3_extension_init but this would not work with more than one db-connection (the last one would win). The other open point for me is the right place and methode to link the same source code as used for the Loadable Extension Functions to become a DLL (for SQlite3.exe .load) as auto-loaded extensions into a specialized made of SQLite3.DLL for my applications. Do you have another trick than modifying the sqlite3_open-functions with compiler switches? This would be great. Even if above statements work, do you think the reordering makes sense for an easier transfer for the sqlite3* into the loadable extension function? The 5th argument would be kept free for other purposes. What is your estimation of likelyhood for that proposed change? What's against it? ---- _2007-Sep-07 14:11:38 by danielk1977:_ {linebreak} The code above is correct as far as I can see. As far as reordering structures to allow tricky casting, there's no particular argument against, other than it makes the code a bit tricky to follow. And limits the ways in which we can change the contents of the currently opaque sqlite3_context structure in the future. Something like: sqlite3 *sqlite3_get_connection(sqlite3_context *p){ return p->s.db; } might be a chance I guess. ---- _2007-Sep-10 18:58:09 by anonymous:_ {linebreak} That is a very good idea. Now I have a case, where I would like to make use of both, the db connection AND the user data:{linebreak} I have two database selections (by a list of keys) and I would like to used one function to compare "equal" and another "unequal". In this case I could use the same function body and use the userdata to distinguish between the cases equal and unequal while using the db connection to query the selection with the given keys.{linebreak} Using 2 functions which are sharing the same body of a 3rd function will be my workaround until this new API function becomes available with the next 3.5.x version. Many thanks. ---- _2007-Sep-10 19:09:51 by anonymous:_ {linebreak} I agree. It is very reasonable to want to use an sqlite3* connection from within a registered function as well. Scenario - perform an SQL query on same connection from within a user-defined function. Setting user data just for this common action is awkward. ---- _2008-Jan-08 14:46:14 by anonymous:_ {linebreak} when will this function become usable? will it come with 3.5.5 ? {linebreak} sqlite3 *sqlite3_get_connection(sqlite3_context *p) #e8e8bd 2627 active 2007 Sep anonymous Pending 3 2 Improper parsing of nested JOIN SQLite has a problem with multiple nested JOINs. The only way to get it workig is to remove the surrounding brackets. Removing the brackets unfortunately do not work in other DB systems such as MS SQL, mysql etc. This does not work: Select ContactPhone.* From (ContactPhone LEFT OUTER JOIN ContactLocation ON ContactPhone.PHNLCT_ID = ContactLocation.LCT_ID) LEFT OUTER JOIN ContactItem ON ContactLocation.LCTITM_ID = ContactItem.ITM_ID (It complains about LCT_ID or similar) This works after removing the brackets: Select ContactPhone.* From ContactPhone LEFT OUTER JOIN ContactLocation ON ContactPhone.PHNLCT_ID = ContactLocation.LCT_ID LEFT OUTER JOIN ContactItem ON ContactLocation.LCTITM_ID = ContactItem.ITM_ID All other major DB systems require the surrounding brackets. Do you think it is possible to fix it? Apart from this little little SQLite is an awesome project. Thank you Jakub Klos _2007-Sep-06 13:07:32 by anonymous:_ {linebreak} I don't have access to MS SQL Server, but MySQL and Oracle have no issue with the query without parentheses:SQLITE_EXTENSION_INIT1 int sqlite3_extension_init( sqlite3 *db, char **pzErrMsg, const sqlite3_api_routines *pApi ) { SQLITE_EXTENSION_INIT2(pApi) sqlite3_create_function(db, "execSQL", -1, SQLITE_UTF8, db, execSqlFunc, 0, 0); ... } static void execSqlFunc( sqlite3_context *context, int argc, sqlite3_value **argv ) { sqlite3 *db = sqlite3_user_data(context ); /* instead of using context's internals ( sqlite3 *db = context->s.db ); or waiting for reordering of them and using typecase ( sqlite3 *db = (sqlite3*)context; ) respective ( sqlite3 *db = *(sqlite3**)context; )? */ ... }
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 #cfe8bd 2613 active 2007 Sep anonymous Pending 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. #e8e8bd 2580 active 2007 Aug anonymous Pending anonymous 1 2 Can't open a query if text to search is Greek for example: SELECT * FROM mytable WHERE mycolumn LIKE '%some greek text%' I get wrong results, using the 3.4.2 version. No problem instead using other earlier version. I tested only in Windows. #cfe8bd 2558 active 2007 Aug anonymous Pending 2 3 Multiple JOIN USING() gives incorrect results I'm having a problem joining multiple tables with USING. It appears to work, but the results are incorrect. Here is an example to illustrate the problem. I believe the three SELECT statements should be equivalent, but they produce three different results. .header on .mode column CREATE TABLE Main (pk INTEGER PRIMARY KEY, name VARCHAR); CREATE TABLE OptA (pk INTEGER PRIMARY KEY, alpha VARCHAR); CREATE TABLE OptB (pk INTEGER PRIMARY KEY, beta VARCHAR); INSERT INTO Main VALUES (1, 'One'); INSERT INTO Main VALUES (2, 'Two'); INSERT INTO Main VALUES (3, 'Three'); INSERT INTO Main VALUES (4, 'Four'); INSERT INTO OptA VALUES (1, 'Alpha1'); INSERT INTO OptA VALUES (4, 'Alpha4'); INSERT INTO OptB VALUES (2, 'Beta2'); INSERT INTO OptB VALUES (4, 'Beta4'); SELECT * FROM Main LEFT JOIN OptA USING (pk) LEFT JOIN OptB USING (pk); SELECT * FROM Main LEFT JOIN OptB USING (pk) LEFT JOIN OptA USING (pk); SELECT Main.pk, name, alpha, beta FROM Main LEFT JOIN OptA ON Main.pk = OptA.pk LEFT JOIN OptB ON Main.pk = OptB.pk; Joining Main, OptA, and OptB omits Beta2: pk name alpha beta ---------- ---------- ---------- ---------- 1 One Alpha1 2 Two 3 Three 4 Four Alpha4 Beta4 Joining Main, OptB, and OptA omits Alpha1: pk name beta alpha ---------- ---------- ---------- ---------- 1 One 2 Two Beta2 3 Three 4 Four Beta4 Alpha4 Only by using ON instead of USING do we get the correct results: pk name alpha beta ---------- ---------- ---------- ---------- 1 One Alpha1 2 Two Beta2 3 Three 4 Four Alpha4 Beta4 I think this is basically the same issue as ticket #1637, but it's a more serious example. In that one, the query simply failed to compile. In this case, it seems to work, but gives you the wrong results. I've also tried this script in PostgreSQL 8.0.13. All three queries give (the same) correct results. _2007-Aug-08 17:34:27 by anonymous:_ {linebreak} The problem is that SQLite is transforming SELECT * FROM Main LEFT JOIN OptA USING (pk) LEFT JOIN OptB USING (pk); into SELECT Main.pk, name, alpha, beta FROM Main LEFT JOIN OptA ON Main.pk = OptA.pk LEFT JOIN OptB ON OptA.pk = OptB.pk; Here is a workaround to this bug that makes use of a subquery: select * from (SELECT * FROM Main LEFT JOIN OptA USING (pk)) LEFT JOIN OptB USING (pk); Conceivably all LEFT JOIN chains could be transformed into the above form, but that would decrease performance due to the intermediate result set of the subquery. Having it work without the subquery is tricky since sqlite must deduce that the last USING (pk) is equivalent to the first pk in the chain of joined tables, namely Main.pk, and not OptA.pk. Joe Wilson #cfe8bd 2547 active 2007 Aug danielk1977 Pending 5 3 Changing db encoding of an attached db can confuse shared cache mode. This is quite obscure, but in shared-cache mode: 1) Open db A, attach empty db B. 2) Using another connection from the same thread, set the encoding of B to be different from that of A. Add some data to B. 3) Using the original connection, access database B. It assumes the encoding of A (and therefore mangling any text data). The correct response is to return an error - "attached databases must use the same text encoding as main database". #f2dcdc 2543 active 2007 Jul anonymous Pending 1 1 Chinese charset not support?? when i create a table. the table name is " " (chinese) after this "alter table add column aaa text null" error why??/ thank you #e8e8bd 2539 active 2007 Jul anonymous Pending 2 2 WinCE: Temporary etilqs_ files are not removed from temporary folder Hi, when temporary etilqs_* files are created during SQLite work on Windows CE devices, they are not removed at all. Temporary folder at CE devices: /Application Data/Volatile I've research that it winClose(os_win.c) function has been changed at do not remove this file, assuming it to be removed at winceDestroyLock(os_win.c), so if no lock was happened then files will stay here forever. Has fixed it in my local copy, with hope that it will be fixed when new cool versions of SQLite will be available. My fix at os_win.c:
static int winClose(OsFile **pId){ winFile *pFile; int rc = 1; if( pId && (pFile = (winFile*)*pId)!=0 ){ int rc, cnt = 0; OSTRACE2("CLOSE %d\n", pFile->h); do{ rc = CloseHandle(pFile->h); }while( rc==0 && cnt++ < MX_CLOSE_ATTEMPT && (Sleep(100), 1) ); #if OS_WINCE winceDestroyLock(pFile); // fix begin if( pFile->zDeleteOnClose ){ DeleteFileW(pFile->zDeleteOnClose); sqliteFree(pFile->zDeleteOnClose); } // fix end #endif OpenCounter(-1); sqliteFree(pFile); *pId = 0; } return rc ? SQLITE_OK : SQLITE_IOERR; }Thanks, Fedor _2007-Jul-28 16:41:41 by anonymous:_ {linebreak} The solution is to revert checkin 3836 and re-open ticket #2294.
Looking at the wince locking mechanism, the only time we ever use the zDeleteOnClose flag is when we've opened a database for exclusive access in sqlite3WinOpenExclusive. To save time and resources (and because its not necessary) we never bother creating a locking mechanism for exclusively-opened files. So pFile->hMutex is NULL when hitting winceDestroyLock(), and the file is never deleted.
Is it possible that the original poster of #2294 was trying to close the same connection on multiple threads at the same time? ---- _2007-Jul-31 05:32:39 by anonymous:_ {linebreak} This is actually a duplicate of #2533 ---- _2007-Sep-21 14:20:05 by anonymous:_ {linebreak} So when the fix of [3836] was applied, the code to delete the file was only put in the section that is called when we have a mutex. I wonder, if the deletion of the file should also take place if there was no mutex. Works for me at least: static void winceDestroyLock(winFile *pFile){ if (pFile->hMutex){ /* Acquire the mutex */ winceMutexAcquire(pFile->hMutex); /* The following blocks should probably assert in debug mode, but they are to cleanup in case any locks remained open */ if (pFile->local.nReaders){ pFile->shared->nReaders --; } if (pFile->local.bReserved){ pFile->shared->bReserved = FALSE; } if (pFile->local.bPending){ pFile->shared->bPending = FALSE; } if (pFile->local.bExclusive){ pFile->shared->bExclusive = FALSE; } /* De-reference and close our copy of the shared memory handle */ UnmapViewOfFile(pFile->shared); CloseHandle(pFile->hShared); * if( pFile->zDeleteOnClose ){ * DeleteFileW(pFile->zDeleteOnClose); * sqliteFree(pFile->zDeleteOnClose); * pFile->zDeleteOnClose = 0; * } /* Done with the mutex */ winceMutexRelease(pFile->hMutex); CloseHandle(pFile->hMutex); pFile->hMutex = NULL; } + else + { + if( pFile->zDeleteOnClose ){ + DeleteFileW(pFile->zDeleteOnClose); + sqliteFree(pFile->zDeleteOnClose); + pFile->zDeleteOnClose = 0; + } + } } The code marked with * was put there in #cfe8bd 2530 active 2007 Jul anonymous Pending 2 3 Unable to write to windows share, even with exclusive lock It has been mentioned that the file locking does not work on windows shared network drives (Samba or SMB drives from Windows or Linux). It seems that an exclusive lock should be a workaround for this problem if you need to write to a shared drive. Currently a more complicated locking is being attempted and failing on network drives. With an exclusive lock, SQLite could resort to simply holding a open write or append enabled file handle to the database as a more primitive locking system that is more likely to work on network drive. No other process could open the database but that would be expected with an exclusive lock. The following case should then function: grudy@gamma:~$ mount | grep Files //winserver/FileDump on /mnt/Files type cifs (rw,mand,noexec,nosuid,nodev) grudy@gamma:~$ touch /mnt/Files/i_have_write_permissions.txt; rm /mnt/Files/i_have_write_permissions.txt grudy@gamma:~$ sqlite3 /mnt/Files/foo.sqlite SQLite version 3.3.17 Enter ".help" for instructions sqlite> PRAGMA locking_mode = EXCLUSIVE; exclusive sqlite> create table bar (foobar); SQL error: database is locked sqlite> #f2dcdc 2517 active 2007 Jul anonymous Pending dflam 1 1 exception on reading text in vista but not xp My companies sqlite 3.1 db works perfectly on Win XP but when we moved to Vista (I'm using Vista 64)it is trowing an exception when I access a text field that contains this data: 'A/C Pressure Sensor, raw1 = A/C on, 0 = A/C off (A/C status determines which IACTx cell is used)' Interestingly when I view data I've inserted using sqliteman3 it has unprintable characters added to it. (A/C status determines which IACTx cell is used)9 If I define the field as Char[512] this artifact goes away. But reading your literature this isn't supposed to make a difference because everything is char. I've changed the values in the error column, but the error seems to be depending on length rather than value. Any help appreciatied! Jim _2007-Jul-19 15:13:41 by drh:_ {linebreak} We will be better able to help you with your problem on the SQLite mailing list. See http://www.sqlite.org/support.html for instructions on joining the mailing list. #f2dcdc 2512 active 2007 Jul shess Pending 1 1 FTS virtual table name quoting problem All table names should be quoted in the FTS module code. with TRACE enabled in ext/fts2/fts2.c:
sqlite> create virtual table "a b c" using fts2 (t); FTS2 Create FTS2 sql: CREATE TABLE main.a b c_content(c0t) SQL error: vtable constructor failed: a b c_2007-Jul-18 06:44:21 by anonymous:_ {linebreak} A similar problem shows if a FTS column has the same name as the FTS table: CREATE VIRTUAL TABLE a USING fts2 (a); Returns "vtable constructor failed: a.". #e8e8bd 2511 active 2007 Jul anonymous Pending drh 3 2 Inconsistent Pragma output Pragma output is inconsistent when setting the value. Most do not generate any output and silently set the value, while others generate a singleton row with the set value. Here is a list of pragmas that generate output while setting the values: sqlite> PRAGMA locking_mode = NORMAL; normal sqlite> PRAGMA max_page_count = 100000; 100000 The following do not generate any output upon query: PRAGMA case_sensitive_like; PRAGMA incremental_vacuum; Sqlite was built from almagamation using the following configuration flags: --enable-threadsafe --disable-tcl --enable-tempstore #f2dcdc 2510 active 2007 Jul anonymous Pending 1 1 Vacuum modified FTS2 rowids VACUUM modifies FTS2 rowids. Here is the test: drop table if exists a; create virtual table a using fts2 (t); insert into a (t) values ('one'); insert into a (t) values ('two'); insert into a (t) values ('three'); select rowid, * from a; delete from a where t = 'two'; vacuum; select rowid, * from a; Unfortunately there is no workaround since table a is auto-generated by the FTS2 module. _2007-Jul-17 14:05:58 by anonymous:_ {linebreak} http://www.sqlite.org/cvstrac/chngview?cn=4157 ---- _2007-Jul-17 14:24:29 by anonymous:_ {linebreak} Yes, this behavior has been recently documented, but there is no user workaround like PRIMARY KEY for FTS2 rowids. Therefore I consider this as a bug which should be fixed in fts2.c. ---- _2007-Jul-17 14:55:57 by anonymous:_ {linebreak} Should virtual tables be VACUUMable? What exactly is being vacuumed here - an internal table? ---- _2007-Jul-17 16:34:55 by shess:_ {linebreak} I agree, I think this is a bug. Rather severe, too, the entire fts system implicitely depends on rowids not changing, this means that vacuum will break fts tables (fts1 or fts2).
drop table if exists t; create virtual table t using fts2; insert into t (content) values ('This is a test'); insert into t (content) values ('This is a string'); insert into t (content) values ('That was a test'); insert into t (content) values ('A random string'); select content from t where t MATCH 'test'; delete from t where content = 'This is a string'; vacuum; select content from t where t MATCH 'test';The first select outputs 'This is a test' and 'That was a test'. The second select outputs 'This is a test', and 'A random string'. ---- _2007-Jul-17 17:27:21 by anonymous:_ {linebreak} This patch seems to address the FTS2 VACUUM problem and passes all fts2 tests. It adds an INTEGER PRIMARY KEY docid column to the hidden %_content table. Note: this new table format is not backwards compatible with existing FTS2 databases. -Joe Wilson
Index: ext/fts2/fts2.c =================================================================== RCS file: /sqlite/sqlite/ext/fts2/fts2.c,v retrieving revision 1.40 diff -u -3 -p -r1.40 fts2.c --- ext/fts2/fts2.c 2 Jul 2007 10:16:50 -0000 1.40 +++ ext/fts2/fts2.c 17 Jul 2007 17:19:49 -0000 @@ -1769,9 +1769,9 @@ typedef enum fulltext_statement { */ static const char *const fulltext_zStatement[MAX_STMT] = { /* CONTENT_INSERT */ NULL, /* generated in contentInsertStatement() */ - /* CONTENT_SELECT */ "select * from %_content where rowid = ?", + /* CONTENT_SELECT */ "select * from %_content where docid = ?", /* CONTENT_UPDATE */ NULL, /* generated in contentUpdateStatement() */ - /* CONTENT_DELETE */ "delete from %_content where rowid = ?", + /* CONTENT_DELETE */ "delete from %_content where docid = ?", /* BLOCK_INSERT */ "insert into %_segments values (?)", /* BLOCK_SELECT */ "select block from %_segments where rowid = ?", @@ -1860,14 +1860,14 @@ static struct fulltext_vtab *cursor_vtab static const sqlite3_module fts2Module; /* forward declaration */ /* Return a dynamically generated statement of the form - * insert into %_content (rowid, ...) values (?, ...) + * insert into %_content (docid, ...) values (?, ...) */ static const char *contentInsertStatement(fulltext_vtab *v){ StringBuffer sb; int i; initStringBuffer(&sb); - append(&sb, "insert into %_content (rowid, "); + append(&sb, "insert into %_content (docid, "); appendList(&sb, v->nColumn, v->azContentColumn); append(&sb, ") values (?"); for(i=0; i---- _2007-Jul-18 00:13:56 by shess:_ {linebreak} BTW, AFAICT this only happens for sqlite3.4. Older versions don't seem to have the problem. ---- _2007-Jul-18 01:31:49 by anonymous:_ {linebreak} The rowid changing after VACUUM predates 3.4.0...nColumn; ++i) @@ -1878,7 +1878,7 @@ static const char *contentInsertStatemen /* Return a dynamically generated statement of the form * update %_content set [col_0] = ?, [col_1] = ?, ... - * where rowid = ? + * where docid = ? */ static const char *contentUpdateStatement(fulltext_vtab *v){ StringBuffer sb; @@ -1893,7 +1893,7 @@ static const char *contentUpdateStatemen append(&sb, v->azContentColumn[i]); append(&sb, " = ?"); } - append(&sb, " where rowid = ?"); + append(&sb, " where docid = ?"); return stringBufferData(&sb); } @@ -2027,15 +2027,15 @@ static int sql_step_leaf_statement(fullt return rc; } -/* insert into %_content (rowid, ...) values ([rowid], [pValues]) */ -static int content_insert(fulltext_vtab *v, sqlite3_value *rowid, +/* insert into %_content (docid, ...) values ([docid], [pValues]) */ +static int content_insert(fulltext_vtab *v, sqlite3_value *docid, sqlite3_value **pValues){ sqlite3_stmt *s; int i; int rc = sql_get_statement(v, CONTENT_INSERT_STMT, &s); if( rc!=SQLITE_OK ) return rc; - rc = sqlite3_bind_value(s, 1, rowid); + rc = sqlite3_bind_value(s, 1, docid); if( rc!=SQLITE_OK ) return rc; for(i=0; i nColumn; ++i){ @@ -2047,7 +2047,7 @@ static int content_insert(fulltext_vtab } /* update %_content set col0 = pValues[0], col1 = pValues[1], ... - * where rowid = [iRowid] */ + * where docid = [iRowid] */ static int content_update(fulltext_vtab *v, sqlite3_value **pValues, sqlite_int64 iRowid){ sqlite3_stmt *s; @@ -2075,7 +2075,7 @@ static void freeStringArray(int nString, free((void *) pString); } -/* select * from %_content where rowid = [iRow] +/* select * from %_content where docid = [iRow] * The caller must delete the returned array and all strings in it. * null fields will be NULL in the returned array. * @@ -2101,10 +2101,10 @@ static int content_select(fulltext_vtab values = (const char **) malloc(v->nColumn * sizeof(const char *)); for(i=0; i nColumn; ++i){ - if( sqlite3_column_type(s, i)==SQLITE_NULL ){ + if( sqlite3_column_type(s, i+1)==SQLITE_NULL ){ values[i] = NULL; }else{ - values[i] = string_dup((char*)sqlite3_column_text(s, i)); + values[i] = string_dup((char*)sqlite3_column_text(s, i+1)); } } @@ -2120,7 +2120,7 @@ static int content_select(fulltext_vtab return rc; } -/* delete from %_content where rowid = [iRow ] */ +/* delete from %_content where docid = [iRow ] */ static int content_delete(fulltext_vtab *v, sqlite_int64 iRow){ sqlite3_stmt *s; int rc = sql_get_statement(v, CONTENT_DELETE_STMT, &s); @@ -2870,7 +2870,7 @@ static int fulltextCreate(sqlite3 *db, v if( rc!=SQLITE_OK ) return rc; initStringBuffer(&schema); - append(&schema, "CREATE TABLE %_content("); + append(&schema, "CREATE TABLE %_content(docid INTEGER PRIMARY KEY, "); appendList(&schema, spec.nColumn, spec.azContentColumn); append(&schema, ")"); rc = sql_exec(db, spec.zDb, spec.zName, stringBufferData(&schema)); @@ -3731,8 +3731,8 @@ static int fulltextFilter( TRACE(("FTS2 Filter %p\n",pCursor)); - zSql = sqlite3_mprintf("select rowid, * from %%_content %s", - idxNum==QUERY_GENERIC ? "" : "where rowid=?"); + zSql = sqlite3_mprintf("select * from %%_content %s", + idxNum==QUERY_GENERIC ? "" : "where docid=?"); sqlite3_finalize(c->pStmt); rc = sql_prepare(v->db, v->zDb, v->zName, &c->pStmt, zSql); sqlite3_free(zSql);
SQLite version 3.3.7 Enter ".help" for instructions sqlite> CREATE TABLE t(a); sqlite> INSERT INTO "t" VALUES('one'); sqlite> INSERT INTO "t" VALUES('two'); sqlite> INSERT INTO "t" VALUES('three'); sqlite> select rowid, * from t; 1|one 2|two 3|three sqlite> delete from t where a = 'one'; sqlite> select rowid, * from t; 2|two 3|three sqlite> vacuum; sqlite> select rowid, * from t; 1|two 2|three SQLite version 3.2.0 Enter ".help" for instructions sqlite> CREATE TABLE t(a); sqlite> INSERT INTO "t" VALUES('one'); sqlite> INSERT INTO "t" VALUES('two'); sqlite> INSERT INTO "t" VALUES('three'); sqlite> select rowid, * from t; 1|one 2|two 3|three sqlite> delete from t where a = 'one'; sqlite> select rowid, * from t; 2|two 3|three sqlite> vacuum; sqlite> select rowid, * from t; 1|two 2|three---- _2007-Jul-18 15:59:24 by anonymous:_ {linebreak} As you may know, INTEGER PRIMARY KEY indexes are the ROWID, so I must supect they would change after a VACUUM. The best workaround is to put docid as INTEGER, then adding a PRIMARY KEY index for the docid column. #f2dcdc 2509 active 2007 Jul anonymous New 1 1 SQLITE_DATE SELECT CAST(MyDate AS DATE), CAST(MyTime AS TIME) FROM MyData I hope, it will result/return DATE, TIME. Please support to SQLITE_DATE and SQLITE_TIME. Thanks. #f2dcdc 2508 active 2007 Jul anonymous Pending 1 1 utf8ToUnicode() does not work on some WinCE devices On some WinCE devices first call to =MultiByteToWideChar()= in =utf8ToUnicode()= always fails. Tried calling =GetLastError()= after it fails and it returns error code 87 -- =ERROR_INVALID_PARAMETER=. To fix this had to change code page from =CP_UTF8= to =CP_ACP= -- no idea why this works. Original =utf8ToUnicode()= ---- static WCHAR *utf8ToUnicode(const char *zFilename) { int nChar; WCHAR *zWideFilename; nChar = MultiByteToWideChar(CP_UTF8, 0, zFilename, -1, NULL, 0); zWideFilename = sqliteMalloc( nChar*sizeof(zWideFilename[0]) ); if( zWideFilename==0 ){ return 0; } nChar = MultiByteToWideChar(CP_UTF8, 0, zFilename, -1, zWideFilename, nChar); if( nChar==0 ){ sqliteFree(zWideFilename); zWideFilename = 0; } return zWideFilename; } ---- Fixed =utf8ToUnicode()= ---- static WCHAR *utf8ToUnicode(const char *zFilename) { int nChar; WCHAR *zWideFilename; nChar = MultiByteToWideChar(CP_ACP, 0, zFilename, -1, NULL, 0); if( nChar == 0 ) { DWORD dwError = GetLastError(); OSTRACE2("MultiByteToWideChar() failed, last error: %d\n", dwError); return 0; } zWideFilename = sqliteMalloc( nChar*sizeof(zWideFilename[0]) ); if( zWideFilename==0 ){ return 0; } nChar = MultiByteToWideChar(CP_ACP, 0, zFilename, -1, zWideFilename, nChar); if( nChar==0 ){ sqliteFree(zWideFilename); zWideFilename = 0; } return zWideFilename; } ---- _2007-Jul-17 23:56:10 by anonymous:_ {linebreak} =unicodeToUtf8()= needs to be fixed the same way. Before: ---- static char *unicodeToUtf8(const WCHAR *zWideFilename){ int nByte; char *zFilename; nByte = WideCharToMultiByte(CP_UTF8, 0, zWideFilename, -1, 0, 0, 0, 0); zFilename = sqliteMalloc( nByte ); if( zFilename==0 ){ return 0; } nByte = WideCharToMultiByte(CP_UTF8, 0, zWideFilename, -1, zFilename, nByte, 0, 0); if( nByte == 0 ){ sqliteFree(zFilename); zFilename = 0; } return zFilename; } ---- After: ---- static char *unicodeToUtf8(const WCHAR *zWideFilename){ int nByte; char *zFilename; nByte = WideCharToMultiByte(CP_ACP, 0, zWideFilename, -1, NULL, 0, NULL, NULL); if ( nByte == 0 ) { DWORD dwError = GetLastError(); OSTRACE2("WideCharToMultiByte() failed, last error = %d\n", dwError); return 0; } zFilename = sqliteMalloc( nByte ); if( zFilename==0 ){ return 0; } nByte = WideCharToMultiByte(CP_ACP, 0, zWideFilename, -1, zFilename, nByte, 0, 0); if( nByte == 0 ){ sqliteFree(zFilename); zFilename = 0; } return zFilename; } ---- Note that while original code with =CP_UTF8= works on Windows and SOME WinCE devices, this modified code works well and Windows and all WinCE devices I've tested so far. ---- _2007-Jul-18 16:01:21 by anonymous:_ {linebreak} Why not using the conversions from SQLite internals ? It can change a UTF-16 to UTF-8 and vice-versa. Or using UTF-16 variants in windows ce should be the best case. ---- _2007-Aug-09 20:47:04 by anonymous:_ Why not using the conversions from SQLite internals ? It can change a UTF-16 to UTF-8 and vice-versa. Or using UTF-16 variants in windows ce should be the best case. Not so simple. =unicodeToUtf8()= is used a lot internally regardless of what whether you use UTF-16 or UTF-8 yourself. For example, =unicodeToUtf8()= is used by =sqlite3WinTempFileName()= which is in turn used by =sqlite3PagerOpentemp()= -- I think you get the idea. ---- _2007-Dec-20 00:29:33 by anonymous:_ {linebreak} We've found that using CP_UTF8 fails on WinCE kernels that don't include SYSGEN_CORELOC (http://msdn2.microsoft.com/en-us/library/ms903883.aspx). To make the code handle any device it should be changed to: static WCHAR *utf8ToUnicode(const char *zFilename) { int nChar; WCHAR *zWideFilename; nChar = MultiByteToWideChar(CP_UTF8, 0, zFilename, -1, NULL, 0); if( nChar == 0 ) { nChar = MultiByteToWideChar(CP_ACP, 0, zFilename, -1, NULL, 0); if( nChar == 0 ) { DWORD dwError = GetLastError(); OSTRACE2("MultiByteToWideChar() failed, last error: %d\n", dwError); return 0; } } zWideFilename = sqliteMalloc( nChar*sizeof(zWideFilename[0]) ); if( zWideFilename==0 ) { return 0; } nChar = MultiByteToWideChar(CP_UTF8, 0, zFilename, -1, zWideFilename, nChar); if( nChar==0 ) { nChar = MultiByteToWideChar(CP_ACP, 0, zFilename, -1, zWideFilename, nChar); if( nChar==0 ) { sqliteFree(zWideFilename); zWideFilename = 0; } } return zWideFilename; } #e8e8bd 2498 active 2007 Jul anonymous Pending 3 2 sqlite memory org on linux (related ticket #2473)... he sample programme that I run(wrote) in tty1 and there I operate the command of ps at tty2, there seems two items from the programme of ps command. This error was not at the version 3.3.13 but now it is happening at sqlite versions although i change nothing from the programme, If I turn to old versions, there is seen only one item again. When I upgrade to version 3.3.13 or later, there is seen two items again Is it normal or there is any mistake? (excuse my poor english) _2007-Jul-11 16:44:22 by anonymous:_ {linebreak} So you are seeing 2 processes instead of 1 on Linux? Linux 2.4 and earlier kernels show threads as seperate processes with unique process IDs. Is your program creating any threads? The only place where SQLite creates threads is the function below - but it joins with the thread right away.
/* ** This procedure attempts to determine whether or not threads ** can override each others locks then sets the ** threadsOverrideEachOthersLocks variable appropriately. */ static void testThreadLockingBehavior(int fd_orig){ int fd; struct threadTestData d[2]; pthread_t t[2]; fd = dup(fd_orig); if( fd<0 ) return; memset(d, 0, sizeof(d)); d[0].fd = fd; d[0].lock.l_type = F_RDLCK; d[0].lock.l_len = 1; d[0].lock.l_start = 0; d[0].lock.l_whence = SEEK_SET; d[1] = d[0]; d[1].lock.l_type = F_WRLCK; pthread_create(&t[0], 0, threadLockingTest, &d[0]); pthread_create(&t[1], 0, threadLockingTest, &d[1]); pthread_join(t[0], 0); pthread_join(t[1], 0); close(fd); threadsOverrideEachOthersLocks = d[0].result==0 && d[1].result==0; }If you post a small C program demonstrating what you're seeing, someone may be able to offer a suggestion. ---- _2007-Jul-11 16:47:10 by anonymous:_ {linebreak} I suppose it's not inconceivable that the join failed. Perhaps these pthread_join calls' return codes should be examined for errors. ---- _2007-Jul-11 18:53:36 by anonymous:_ {linebreak} If you're playing games with tty's and you've got an early Linux 2.6 kernel, it's possible that processes are dying because of http://lkml.org/lkml/2004/10/21/119. It was, last I checked, fixed in 2.6.10. The SIGHUP being generated might also interfer with a =pthread_join()=, although =pthread_join()= doesn't say anything about ever generating =EINTR=... c. ---- _2007-Jul-12 06:12:28 by anonymous:_ {linebreak} my example program is very simple, i not use threading-multithreading structure... If I turn to old versions of sqlite, there is seen only one item again, when I upgrade to version 3.3.13 or later, there is seen two items again Is it. note: /lib/libpthread.so.0 linked to /lib/libpthread-0.10.so (size 55468 byte) ---- _2007-Jul-12 11:36:37 by anonymous:_ {linebreak} Your description of the problem isn't clear enough, so the answers you're getting are just guesses. You may have more luck by describing the problem (with as much detail as possible) in your native language and hoping someone in the SQLite community can add a translation. I know you're doing your best with the english you speak, but it's not working well enough for someone to help with your problem. Adding code samples and command-line output would also help considerably, since that sort of this is mostly language independent. #f2dcdc 2491 active 2007 Jul anonymous Pending 1 1 Mingw Warnings w/ 3.4.0 Amalgamation When compiling the 3.4.0 amalgamation sqlite3.c file w/ no defines, you get the following warnings: sqlite3/sqlite3.c: In function `sqlite3BtreeFindCell':{linebreak} sqlite3/sqlite3.c:23249: warning: unused variable `data'{linebreak} sqlite3/sqlite3.c: In function `vxprintf':{linebreak} sqlite3/sqlite3.c:8488: warning: 'xtype' might be used uninitialized in this function{linebreak} sqlite3/sqlite3.c: In function `sqlite3BtreeOpen':{linebreak} sqlite3/sqlite3.c:19488: warning: 'nameLen' might be used uninitialized in this function{linebreak} sqlite3/sqlite3.c: In function `getOverflowPage':{linebreak} sqlite3/sqlite3.c:25386: warning: 'rc' might be used uninitialized in this function{linebreak} sqlite3/sqlite3.c: In function `sqlite3Select':{linebreak} sqlite3/sqlite3.c:56300: warning: 'pEList' might be used uninitialized in this function{linebreak} sqlite3/sqlite3.c:56301: warning: 'pTabList' might be used uninitialized in this function{linebreak} sqlite3/sqlite3.c: At top level:{linebreak} sqlite3/sqlite3.c:16020: warning: 'sqlite3GenericAllocationSize' defined but not used{linebreak} sqlite3/sqlite3.c:6188: warning: 'sqlite3Utf16Substr' declared `static' but never defined{linebreak} sqlite3/sqlite3.c:6307: warning: 'sqlite3Get2byte' declared `static' but never defined{linebreak} sqlite3/sqlite3.c:6309: warning: 'sqlite3Put2byte' declared `static' but never defined{linebreak} sqlite3/sqlite3.c:23248: warning: 'sqlite3BtreeFindCell' defined but not used{linebreak} sqlite3/sqlite3.c:63547: warning: 'sqlite3ParserAlloc' defined but not used{linebreak} sqlite3/sqlite3.c:63673: warning: 'sqlite3ParserFree' defined but not used{linebreak} sqlite3/sqlite3.c:65286: warning: 'sqlite3Parser' defined but not used{linebreak} I know the uninitialized warnings are false warnings but the defined functions that aren't used seem to be an error in building the amalgamation. #f2dcdc 2487 active 2007 Jul anonymous Pending 1 1 SQLite database locked error on NFS mounted home dir I have a c program using the provided API. My home directory is NFS mounted, Im using SQLite 3.3.17. I open a new database using "sqlite3_open", then strcpy () a SQL command to create a table, and run "sqlite3_exec" with this string. I get a return code of 5=database locked. I then tried to manually (command line using sqlite3) create a table within a database in my home dir, that fails too. =========== x@y> sqlite3 db2 SQLite version 3.3.17 Enter ".help" for instructions sqlite> create table test (Lastname varchar); SQL error: database is locked sqlite> ============== If I try this on my local machine (a Mac), it works fine, but I need it to work in my home directory mounted via NFS as that is where the output of our program goes _2007-Jul-06 19:04:15 by anonymous:_ {linebreak} If you're using a Mac, compile sqlite with SQLITE_ENABLE_LOCKING_STYLE in os_unix.c ---- _2007-Jul-07 11:51:10 by drh:_ {linebreak} This is a problem with your NFS implementation - it does not appear to support posix advisory locking. There is nothing much that SQLite can do about this. Anonymous above suggests making use of the dot-locking mechanism contributed by Apple. This might be an effective work-around. But remember that there is performance impact. Also remember that an SQLite database that uses dot-locking is subtly imcompatible with a standard SQLite database. The file format itself is the same, but if two processes try to access the database file at the same time and one uses dot-locks and the other uses posix advisory locks, you will end up with corruption. ---- _2007-Jul-07 12:44:09 by anonymous:_ {linebreak} It's very odd that Apple does not fix their Mac OSX POSIX locks for NFS given their resources. #f2dcdc 2479 active 2007 Jun anonymous Pending 1 1 WinCE regression on some systems. Any db open fails. Because Windows CE is a modular system, meaning many parts of it can be optionally ommited by the system builder, some don't include the CP_UTF8 conversion algorithms for MultiByteToWideChar and family. I believe Windows 95 and early 98 systems can also lack this encoding if not updated with a later Internet Explorer version. Solution is to just use the sqlite internal functions that already know how to do the same thing. Attached is an untested patch to os_win.c (I don't have a windows machine nor a cross-compiler set up) to show where the problem is and a possible (sub-optimal) solution. I believe the right thing to do would be to just drop the utf8ToUnicode and unicodeToUtf8 functions, add the sqlite3Utf8to16 equivalent to utf.c and use them instead. ~Nuno Lucas _2007-Jun-29 14:54:11 by anonymous:_ {linebreak} The title is wrong. It should say "Any db open using the UTF-8 API", as using the open16 API will work. #e8e8bd 2440 active 2007 Jun rse Pending 2 2 pkg-config(1) script "sqlite.pc" does not provide Autoconf's LIBS On some platforms it isn't sufficient to link a library just against "-lsqlite". For instance under Solaris one needs "-lsqlite -lrt" because of the use of "fdatasync()". The SQLite Autoconf glue already contains the necessary check for this in order to correctly build SQLite and especially link its sqlite(1). But this information is not passed through to the applications which use pkg-config(1) to build against SQLite. Possible fix from OpenPKG's "sqlite" package is following:
Index: sqlite3.pc.in --- sqlite3.pc.in.orig 2004-07-19 06:25:47 +0200 +++ sqlite3.pc.in 2007-06-20 18:09:00 +0200 @@ -8,5 +8,5 @@ Name: SQLite^M Description: SQL database engine^M Version: @VERSION@^M -Libs: -L${libdir} -lsqlite3^M +Libs: -L${libdir} -lsqlite3 @LIBS@^M Cflags: -I${includedir}^M#f2dcdc 2414 active 2007 Jun anonymous Unable_to_fix 1 1 Unable t I designed a tool in C# using the Sqlite.Net.dll and sqlite3.dll v 3.2.5. The call to sqlite3_step after the sqlite3_prepare function, causes a huge delay to return (~7MINS), sometimes it doesn't return at all. This happens when I'm using v3.2.5 but when I replace it with v 3.3.7, everything works normal. I have tried so many combination of things to get it to work on v3.2.5 (this is the version of the libraries the hardware uses), which includes setting the PRAGMA legacy_file_format to 1, but after every save it reverts back to 0. i will appreciate if I can get a response with any suggestions. Thanks in advance. _2007-Jun-13 15:58:00 by anonymous:_ {linebreak} Please post the schema and the SELECT command that is slow under 3.2.5 and is fast under 3.3.7 so it can be reproduced using the sqlite3 commandline shell. #f2dcdc 2413 active 2007 Jun anonymous Pending drh 1 1 1 bug and 2 suggestions in lemon Hello, ... {linebreak} Sorry for my english :-) and if i post this with Severity/Priority error. {linebreak} I found some not serious bug and have some suggetions. {linebreak} ============================================================================={linebreak} BUG FIX: {linebreak} lemon.c for Win32. It not found lempar.c - backslash-bug. {linebreak} function: {linebreak} PRIVATE char *pathsearch(argv0,name,modemask); {linebreak} PATCH: {linebreak} ---- CUT --------------------------------------------------------------------{linebreak} --- C:/lemon.c Wed Jun 13 15:02:37 2007 {linebreak} +++ D:/Den/Lemon/lemon.c Wed Jun 13 16:25:22 2007 {linebreak} @@ -2911,7 +2911,11 @@ {linebreak} c = *cp; {linebreak} *cp = 0; {linebreak} path = (char *)malloc( strlen(argv0) + strlen(name) + 2 ); {linebreak} - if( path ) sprintf(path,"%s/%s",argv0,name); {linebreak} + #ifdef __WIN32__ {linebreak} + if( path ) sprintf(path,"%s\\%s",argv0,name); {linebreak} + #else {linebreak} + if( path ) sprintf(path,"%s/%s",argv0,name); {linebreak} + #endif {linebreak} *cp = c; {linebreak} }else{ {linebreak} extern char *getenv(); {linebreak} @@ -2920,11 +2924,19 @@ {linebreak} path = (char *)malloc( strlen(pathlist)+strlen(name)+2 ); {linebreak} if( path!=0 ){ {linebreak} while( *pathlist ){ {linebreak} - cp = strchr(pathlist,':'); {linebreak} + #ifdef __WIN32__ {linebreak} + cp = strchr(pathlist,';'); {linebreak} + #else {linebreak} + cp = strchr(pathlist,':'); {linebreak} + #endif {linebreak} if( cp==0 ) cp = &pathlist[strlen(pathlist)]; {linebreak} c = *cp; {linebreak} *cp = 0; {linebreak} - sprintf(path,"%s/%s",pathlist,name); {linebreak} + #ifdef __WIN32__ {linebreak} + sprintf(path,"%s\\%s",pathlist,name); {linebreak} + #else {linebreak} + sprintf(path,"%s/%s",pathlist,name); {linebreak} + #endif {linebreak} *cp = c; {linebreak} if( c==0 ) pathlist = ""; {linebreak} else pathlist = &cp[1]; {linebreak} ---- CUT --------------------------------------------------------------------{linebreak} ============================================================================= {linebreak} SUGGESTION 1: {linebreak} Why we allocate parser with mallocProc parameter of ParseAlloc function {linebreak} and free with freeProc of ParseFree function? {linebreak} We do this because we want what parser is user-allocatable {linebreak} with USER-DEFINED-MEMORY-ALOCATION-WAY but not with "malloc"/"free" from stdlib... am i right? {linebreak} If so... why we still allocate memory for parser stack with "realloc" function? {linebreak} It's bad for solutions where is no stdlib. {linebreak} My suggestion is {linebreak} FIRST WAY: {linebreak} To add to yyParser struct 3 variables like {linebreak} void *mem_alloc_fn; {linebreak} void *mem_realloc_fn; {linebreak} void *mem_free_fn; {linebreak} and add 3 directives like {linebreak} %memory_alloc {linebreak} %memory_realloc {linebreak} %memory_free {linebreak} and if it declared - use it for allocating/free/reallocating memory in parser. {linebreak} and {linebreak} - void *ParseAlloc(void *(*mallocProc)(size_t)); {linebreak} will now as void *ParseAlloc(); {linebreak} - void ParseFree(void *pParser, void (*freeProc)(void*)); {linebreak} will now as void ParseFree(void *pParser); {linebreak} OR SECOND WAY (very simple): {linebreak} To add to yyParser struct 1 variable like {linebreak} void *mem_realloc_fn; {linebreak} - void *ParseAlloc(void *(*mallocProc)(size_t)); {linebreak} will now as void *ParseAlloc(void *(*mallocProc)(size_t), void *(*reallocProc)(void *, size_t)); {linebreak} store reallocProc in mem_realloc_fn in yyParser {linebreak} and in yyGrowStack something like this: {linebreak} ... yyGrowStack (...) {linebreak} { {linebreak} .... {linebreak} if(pParser->mem_realloc_fn != NULL) {linebreak} { {linebreak} pNew = pParser->mem_realloc_fn(p->yystack, newSize*sizeof(pNew[0])); {linebreak} } {linebreak} else {linebreak} { {linebreak} pNew = realloc(p->yystack, newSize*sizeof(pNew[0])); {linebreak} } {linebreak} .... {linebreak} } {linebreak} and use it for reallocating memory in parser. {linebreak} In this ways - memory allocating in parser is under FULL user control. {linebreak} ============================================================================= {linebreak} SUGGESTION 2: {linebreak} I build lemon with VC 8.0 with option /Wp64 (Detect 64-Bit Portability Issues) {linebreak} and have warnings. Type int, size_t, pointer and unsigned long have diferent size on x32 and x64 platforms. {linebreak} Can you fix type difference, please? {linebreak} Only you can choice better way for this - type conversion OR change type of 'warning' variables. {linebreak} WARNINGS: {linebreak} d:\den\lemon\lemon.c(1331) : warning C4267: '=' : conversion from 'size_t' to 'int', possible loss of data {linebreak} d:\den\lemon\lemon.c(1337) : warning C4267: '=' : conversion from 'size_t' to 'int', possible loss of data {linebreak} d:\den\lemon\lemon.c(1455) : warning C4113: 'int (__cdecl *)()' differs in parameter lists from 'int (__cdecl *)(const void *,const void *)' {linebreak} d:\den\lemon\lemon.c(1578) : warning C4311: 'type cast' : pointer truncation from 'char *' to 'unsigned long' {linebreak} d:\den\lemon\lemon.c(1578) : warning C4312: 'type cast' : conversion from 'unsigned long' to 'char **' of greater size {linebreak} d:\den\lemon\lemon.c(1581) : warning C4311: 'type cast' : pointer truncation from 'char *' to 'unsigned long' {linebreak} d:\den\lemon\lemon.c(1581) : warning C4312: 'type cast' : conversion from 'unsigned long' to 'char **' of greater size {linebreak} d:\den\lemon\lemon.c(1586) : warning C4311: 'type cast' : pointer truncation from 'char *' to 'unsigned long' {linebreak} d:\den\lemon\lemon.c(1586) : warning C4312: 'type cast' : conversion from 'unsigned long' to 'char **' of greater size {linebreak} d:\den\lemon\lemon.c(1588) : warning C4311: 'type cast' : pointer truncation from 'char *' to 'unsigned long' {linebreak} d:\den\lemon\lemon.c(1588) : warning C4312: 'type cast' : conversion from 'unsigned long' to 'char **' of greater size {linebreak} d:\den\lemon\lemon.c(1590) : warning C4311: 'type cast' : pointer truncation from 'char *' to 'unsigned long' {linebreak} d:\den\lemon\lemon.c(1590) : warning C4312: 'type cast' : conversion from 'unsigned long' to 'char **' of greater size {linebreak} d:\den\lemon\lemon.c(1592) : warning C4311: 'type cast' : pointer truncation from 'char *' to 'unsigned long' {linebreak} d:\den\lemon\lemon.c(1592) : warning C4312: 'type cast' : conversion from 'unsigned long' to 'char **' of greater size {linebreak} d:\den\lemon\lemon.c(1595) : warning C4311: 'type cast' : pointer truncation from 'char *' to 'unsigned long' {linebreak} d:\den\lemon\lemon.c(1595) : warning C4312: 'type cast' : conversion from 'unsigned long' to 'char **' of greater size {linebreak} d:\den\lemon\lemon.c(1596) : warning C4311: 'type cast' : pointer truncation from 'char *' to 'unsigned long' {linebreak} d:\den\lemon\lemon.c(1596) : warning C4312: 'type cast' : conversion from 'unsigned long' to 'char **' of greater size {linebreak} d:\den\lemon\lemon.c(1624) : warning C4311: 'type cast' : pointer truncation from 'char **' to 'unsigned long' {linebreak} d:\den\lemon\lemon.c(1624) : warning C4311: 'type cast' : pointer truncation from 'char *' to 'unsigned long' {linebreak} d:\den\lemon\lemon.c(1628) : warning C4311: 'type cast' : pointer truncation from 'char *' to 'unsigned long' {linebreak} d:\den\lemon\lemon.c(1628) : warning C4312: 'type cast' : conversion from 'unsigned long' to 'char **' of greater size {linebreak} d:\den\lemon\lemon.c(1629) : warning C4311: 'type cast' : pointer truncation from 'char *' to 'unsigned long' {linebreak} d:\den\lemon\lemon.c(1629) : warning C4312: 'type cast' : conversion from 'unsigned long' to 'char **' of greater size {linebreak} d:\den\lemon\lemon.c(1658) : warning C4267: '=' : conversion from 'size_t' to 'int', possible loss of data {linebreak} d:\den\lemon\lemon.c(1661) : warning C4267: '+=' : conversion from 'size_t' to 'int', possible loss of data {linebreak} d:\den\lemon\lemon.c(1774) : warning C4311: 'type cast' : pointer truncation from 'char *' to 'unsigned long' {linebreak} d:\den\lemon\lemon.c(1774) : warning C4311: 'type cast' : pointer truncation from 'char *' to 'unsigned long' {linebreak} d:\den\lemon\lemon.c(1785) : warning C4311: 'type cast' : pointer truncation from 'char *' to 'unsigned long' {linebreak} d:\den\lemon\lemon.c(1785) : warning C4311: 'type cast' : pointer truncation from 'char *' to 'unsigned long' {linebreak} d:\den\lemon\lemon.c(1883) : warning C4267: '=' : conversion from 'size_t' to 'int', possible loss of data {linebreak} d:\den\lemon\lemon.c(2722) : warning C4267: '=' : conversion from 'size_t' to 'int', possible loss of data {linebreak} d:\den\lemon\lemon.c(3171) : warning C4267: '=' : conversion from 'size_t' to 'int', possible loss of data {linebreak} d:\den\lemon\lemon.c(3173) : warning C4018: '>=' : signed/unsigned mismatch {linebreak} d:\den\lemon\lemon.c(3184) : warning C4267: '+=' : conversion from 'size_t' to 'int', possible loss of data {linebreak} d:\den\lemon\lemon.c(3340) : warning C4267: '=' : conversion from 'size_t' to 'int', possible loss of data {linebreak} d:\den\lemon\lemon.c(3346) : warning C4267: '=' : conversion from 'size_t' to 'int', possible loss of data {linebreak} d:\den\lemon\lemon.c(3542) : warning C4267: '=' : conversion from 'size_t' to 'int', possible loss of data {linebreak} Ups ... drh, sorry - title change. #f2dcdc 2409 active 2007 Jun anonymous New drh 1 1 Database malformed with SQLite3.3.17 on WindowsXP I encountered a problem with SQLite3.3.17 on Windows XP. Under certain situation, database file got seriously corrupted. SQLite version: 3.3.17 Windows Binary Platform:Windows XP SP2(Japanese) Code wrtten in: Visual C++ 6.0 Here are the procedures to reproduce the problem: 1) Run a program SQLiteCrush.exe. This program updates 'test.db' repeatedly. Insert data to work table, copy them into items table, then delete records from work. 2) Open 'test.db' from sqlite3.exe. 3) Do '.read check.sql' repeatedly. check.sql is made from many lines of 'pragma integrity_check;'. 4) Keep doing 1 -3 for several minuites, and 'pragma integrity_check' starts to report something like "rowid 91667 missing from index sqlite_autoindex_link_1". So far, I didn't see the database corrupted with SQLite 3.3.7. Also, without 3), the database was not corrupted. Instead of 'pragma integrity_check', issueing many select statements also make it currupted. _2007-Jun-12 02:51:26 by anonymous:_ {linebreak} I did more tests to make it clear from which version it happens. With 3.3.8, I couldn't reproduce the problem. With 3.3.9, I can reproduce the problem. It seems there's some change between these two that causes the problem... ---- _2007-Jun-12 03:06:47 by anonymous:_ {linebreak} Can you try it against the latest version in CVS, or 3.3.17? A lot of code has changed since 3.3.9. ---- _2007-Jun-12 05:00:50 by anonymous:_ {linebreak} I Already tried 3.3.17. It happenes. Where can I get the latest CVS precompiled binary for windows platform ? tamagawa ---- _2007-Jun-13 14:25:47 by drh:_ {linebreak} The problem was introduced in SQLite version 3.3.0, specifically check-in [2848]. There are related problems that go back even further in time, we believe. The root of the problem is a logic error in my design of the pager layer. We are working on a fix now, as well as a set of test cases that will ensure that similar errors do not reappear in the future. I will also soon publish instructions on how to work around the problem in effected versions of SQLite. The problem can be easily reproduced by running the script below using the "testfixture" program that we use for testing SQLite.
#f2dcdc 2408 active 2007 Jun anonymous Pending 2 1 BLOBs not output correctly in .mode insert (shell.c - isnumber) The method {linebreak} static int isNumber(const char *z, int *realnum) {linebreak} {linebreak} from shell.c is wrong. {linebreak} Steps to reproduce: {linebreak} 1. Get the file www.smatei.3x.ro/project1.zip {linebreak} 2. extract project1.db from the zip file {linebreak} 3. execute {linebreak} sqlite3 project1.db {linebreak} sqlite>.mode insert {linebreak} sqlite> select ID, Name, Color, Active, Priority, PrioritySource, IndexOrder, Language, 0 from Keywords; {linebreak} INSERT INTO Keywords VALUES(42,#####,0,1,0,0,42,'',0); {linebreak} INSERT INTO Keywords VALUES(41,'######',0,1,0,0,43,'',0);{linebreak} If you look at the 42 item, the string next to 42 is not enclosed by the string delimiter '. {linebreak} This is because the method isnumber returns that the string is number. It is not a number, it is a string in Hebrew (I inserted ### instead of the real strings). {linebreak} A fix for this might be to set the first parameter unsigned char {linebreak} static int isNumber(unsigned const char *z, int *realnum) {linebreak} but I am not sure, because I haven't written C code for a long time. {linebreak} If you have any more questions, please ask. {linebreak} Best Regards, {linebreak} Stefan _2007-Jun-11 14:46:03 by drh:_ {linebreak} Please show me what you get from the following query: SELECT ID, quote(cast(Name AS BLOB)) FROM Keywords WHERE ID IN (41,42); I need this information in order to track down the problem. ---- _2007-Jun-11 14:50:33 by anonymous:_ {linebreak} The output is 41|X'D791D7A8D799D799D7A7D793D790D7A0D7A1'{linebreak} 42|X'D7A1D798D7A4D7A1' ---- _2007-Jun-11 16:29:46 by drh:_ {linebreak} When I do this: CREATE TABLE t1(x); INSERT INTO t1 VALUES(cast(X'D791D7A8D799D799D7A7D793D790D7A0D7A1' as text)); INSERT INTO t1 VALUES(cast(X'D7A1D798D7A4D7A1' AS text)); .mode insert xyz SELECT * FROM t1; I see the Hebrew characters, properly quoted. Can you suggest another way to reproduce the problem? ---- _2007-Jun-11 16:34:55 by anonymous:_ SQLite version 3.3.17 Enter ".help" for instructions sqlite> create table t1(b blob); sqlite> insert into t1 values(X'0102030405060708090a0dABCD'); sqlite> .dump BEGIN TRANSACTION; CREATE TABLE t1(b blob); INSERT INTO "t1" VALUES(X'0102030405060708090A0DABCD'); COMMIT; sqlite> .mode insert sqlite> select * from t1; INSERT INTO table VALUES(' «Í'); sqlite> ---- _2007-Jun-11 20:07:09 by anonymous:_ {linebreak} If I recall correctly, sqlite3.exe assumes all I/O to and from the console is UTF-8. Windows consoles are either MBCS or UNICODE depending on the build settings. Sqlite3.exe was not compiled as UNICODE so anything inserted into a sqlite3 database will be inserted as MBCS. So if you insert into a database using a 3rd party application that does proper UTF-8, and then query it from the command-line, it will look wrong. Likewise, anything you insert from the command-line will look wrong when queried from an application that uses UTF-8. ---- _2007-Jun-11 20:42:18 by anonymous:_ {linebreak} .mode insert treats BLOBs as strings, which is a problem since it stops outputting at the first nil character. CREATE TABLE t1(a blob); INSERT INTO t1 VALUES(X'00000008090A0D0D0A00'); .mode insert whatever select * from t1; ---- _2007-Jun-12 07:39:12 by anonymous:_ {linebreak} Hi, My problem is not the way the string is displayed in the console. The application that reads the database reads it correctly. The problem is when I try to dump a certain table in a file using the following sequence: .output "file.txt"{linebreak} .mode insert whatever{linebreak} select f1, f2, f3 from t1;{linebreak} The result is a file that some Hebrew strings are not enclosed in ' (apostrophe). After debugging the code, I saw that the method isnumber does not return 0 for that string. It considers all the characters digits. This problem occurs only on Windows. We tried this on Mac and Linux, and it worked fine. Best Regards,{linebreak} Stefan{linebreak} #cfe8bd 2398 active 2007 Jun anonymous Pending 2 3 systemcalls should be restarted when they return EINTR Introduction: I'm a developer for Alcatel-Lucent, where I program softswitches. I'm a bit of a code-correctness nazi, because I've programmed several systems that have to be very reliable (5 figures), or I've corrected them if they didn't. I was browsing thru the source code of SQLite, to become familiar with it, and to see how reliable it was. Not that we currently use it in a product, but you may never known. The very first thing I noticed was that none of the systemcalls in os_unix.c was checked for EINTR. From experience, I know that a systemcall can be interrupted while inside the kernel, and return EINTR in errno. The correct reaction is to repeat the system call, just as if nothing happened. This is *not* an error, just a temporary situation which will be fixed the next time you call it again. One reason is that you call was interrupted by a higher priority interrupt (incoming IO for instance), or to avoid a deadlock in the kernel (returning your syscall will release any locks you might have). It's pretty rare in most usage, but I've seen it lots of times when testing a server under load. Example of the solution for seekAndWrite (this only shows write, but pwrite is similar) : got = write(id->h, pBuf, cnt); should be : do { got = write(id->h, pBuf, cnt); } while ( (got < 0) && (errno == EINTR) ); This has to be done for *every* systemcall that mentions it on its manpage (but please note, not every OS is the same). This includes many calls where everyone assumes that the call is safe to use, for instance close(). Almost nobody seems to realize that a close can fail, and often there's not even a check for the return code. But I can assure you, it can fail. _2007-Jun-05 13:39:08 by anonymous:_ {linebreak} I prefer the current SQLite behavior NOT to retry in the event of EINTR. I've seen too many UNIX OSes with bugs related to EINTR for system calls within threads over the years, including the most popular ones. Most of these OS bugs have since been fixed, but it's still not worth the trouble in case you come across an unpatched platform. Getting an infinite loop of EINTR errnos in a tight loop due to an OS bug is no fun in a production application. Technically, POSIX requires that you only read errno if -1 is returned by the system call. QNX is picky about this sort of thing. ---- _2007-Jun-05 17:02:55 by anonymous:_ {linebreak} Read my introduction again : I help make systems reliable in my daytime job. Ignoring a read- or write-error is not how you achieve 99.999% availability, especially not when it's easily correctable (a disk-full message or a I/O error due to a corrupt harddisk is another matter ofcourse). >Technically, POSIX requires that you only read errno if -1 is returned by the system call. QNX is picky about this sort of thing. well yes, that's what I mean. ---- _2007-Jun-05 17:20:07 by anonymous:_ {linebreak} All of SQLite's operations can also be retried if they fail. SQLite has an extensive test suite for disk failures, malloc failures and many other types of failures that you've likely never considered. I tend to trust the judgement of the authors of SQLite more than an anonymous guy puffing his chest out in this ticket tracking system. ---- _2007-Jun-05 22:24:09 by anonymous:_ {linebreak} hi. instead of blaming in this ticket, the ticket author could place a patch like:# Prepare the database: # file delete -force test.db test.db-journal sqlite3 db test.db db eval { CREATE TABLE t1 ( x TEXT UNIQUE NOT NULL, y BLOB ); } # Open a second connection to the database that will be # used to lock the database file. # set DB2 [sqlite3 db2 test.db] if {$DB2==""} { set DB2 [sqlite3_connection_pointer db2] } # A small cache will cause an early cache spill. # db eval {PRAGMA cache_size=10} # Acquire read lock on the database file using the second connection. # set STMT [sqlite3_prepare $DB2 {SELECT rowid FROM sqlite_master} -1 TAIL] sqlite3_step $STMT # Insert a short record into the index (10 bytes) and a large record # into the table (15K). The index record goes in Ok, but during the # insert into the table, SQLite attempts to upgrade to an EXCLUSIVE # lock to do a cache flush. When this happens, the cache is left in # an inconsistent state. # set zShort [string repeat 0123456789 1] set zLong [string repeat 0123456789 1500] db eval {BEGIN} set rc [catch { db eval {INSERT INTO t1 VALUES($zShort, $zLong)} } msg] puts "rc=$rc msg=$msg" sqlite3_finalize $STMT db eval {COMMIT} db close sqlite3 db test.db puts [db eval {PRAGMA integrity_check}]
got = write(id->h, pBuf, cnt);to do things like:
#ifdef SQLITE3_RETRY_EINTR_SYSCALLS do { #endif // SQLITE3_RETRY_EINTR_SYSCALLS got = write(id->h, pBuf, cnt); #ifdef SQLITE3_RETRY_EINTR_SYSCALLS } while ( (got < 0) && (errno == EINTR) ); #endif // SQLITE3_RETRY_EINTR_SYSCALLSby leaving SQLITE3_RETRY_EINTR_SYSCALLS undefined, this should not affect the current code behaviour and should address the issues ticket author says and he can compile with those 'paranoic' checks. []'s ---- _2007-Jun-05 22:25:06 by anonymous:_ {linebreak} I did pass my name (visible for the developers), but I'm not logged in. That why it's shown as anonymous (just as you are). Note that my co-workers have already decided it would be seen up as a negative point for this DBMS. At least for us, because it's a requirement for us to survive these errors. So if we ever are going to use SQLite, we're definitely going to use those changes. Hey, I'm a code-review nazi, don't blame me. It's just my job :-) ---- _2007-Jun-06 00:58:16 by anonymous:_ {linebreak} I think that the retrying would destroy the function expected of =sqlite3_interrupt=. Is there a problem in the following methods? *: Re-execute the SQLite API. *: Mask the signals while calling the API of SQLite. *: Mask the signals in the threads that call the API of SQLite. ---- _2007-Jun-06 01:04:47 by anonymous:_ {linebreak} As long as #ifdef SQLITE3_RETRY_EINTR_SYSCALLS is not enabled by default, knock yourselves out. ---- _2007-Jun-06 06:38:51 by anonymous:_ {linebreak} sqlite3_interrupt() knows nothing about UNIX signals. It just sets a flag that sqlite's VDBE checks from time to time to see if it should gracefully abort an SQL operation. shell.c happens to install an SIGINT signal handler that happens to call sqlite3_interrupt(), but that's unrelated. sqlite3_interrupt() could be called via any other means. ---- _2007-Jun-06 10:47:27 by anonymous:_ {linebreak} See also the SA_RESTART flag for sigaction(), which automatically will restart the systemcall instead of returning EINTR. Unfortunately not available everywhere, and not very consistent either : see Linus reply at http://lkml.org/lkml/2005/7/23/119 . Applications should still be able to handle EINTR returns gracefully. They're not errors per se (that would be EIO or similar), you can safely restart the system call. I haven't seen endless loops yet, but it's true that on some OS you might want this solution. But you still have to deal with the error (which might cause a rollback in SQLite). ---- _2007-Jun-06 14:51:14 by anonymous:_ {linebreak} I would be in favor of optionally compiling SQLite with a #define SQLITE_USE_SA_RESTART _or_ #define SQLITE_RETRY_ON_EINTR, where they are mutually exclusive and disabled by default. SQLITE_RETRY_ON_EINTR should have a maximum retry limit on buggy OSes, though. #cfe8bd 2396 active 2007 Jun anonymous Pending 1 3 -column output truncates characters from varchar field. CREATE TABLE unix (unix_id integer primary key, ip_address varchar(16) unique, status enum, updated timestamp); INSERT INTO unix VALUES (NULL,'172.26.242.92','in use',timestamp('now')); INSERT INTO unix VALUES (NULL,'172.26.242.129','in use',timestamp('now')); INSERT INTO unix VALUES (NULL,'172.26.242.131','in use',timestamp('now')); INSERT INTO unix VALUES (NULL,'172.26.242.132','in use',timestamp('now')); INSERT INTO unix VALUES (NULL,'172.26.242.136','in use',timestamp('now')); INSERT INTO unix VALUES (NULL,'172.26.242.213','in use',timestamp('now')); INSERT INTO unix VALUES (NULL,'10.193.33.7','in use',timestamp('now')); INSERT INTO unix VALUES (NULL,'10.193.32.239','in use',timestamp('now')); Now: sqlite3 -column test.db "SELECT ip_address, status, updated FROM unix"; 172.26.242.92 in use 2007-05-31 22:11:39 172.26.242.12 in use 2007-05-31 22:11:47 172.26.242.13 in use 2007-05-31 22:11:51 172.26.242.13 in use 2007-05-31 22:11:59 172.26.242.13 in use 2007-05-31 22:12:04 172.26.242.21 in use 2007-05-31 22:12:11 10.193.33.7 in use 2007-05-31 23:27:09 10.193.32.239 in use 2007-05-31 23:27:17 The output of the varchar column is always truncated regardless of which column it is placed in. Even if it's the only column. sqlite3 -column test.db "SELECT ip_address FROM unix"; 172.26.242.92 172.26.242.12 172.26.242.13 172.26.242.13 172.26.242.13 172.26.242.21 10.193.33.7 10.193.32.239 _2007-Jun-01 18:54:42 by anonymous:_ {linebreak} Smaller test case: sqlite> CREATE TABLE t9(a); sqlite> INSERT INTO "t9" VALUES('a234567890'); sqlite> INSERT INTO "t9" VALUES('a23456789012345'); sqlite> select * from t9; a234567890 a23456789012345 sqlite> .mode column sqlite> select * from t9; a234567890 a234567890 sqlite> select * from t9 order by a desc; a23456789012345 a234567890 .mode column seems to be remembering the first row's column lengths and using them as the maximum column lengths for subsequent rows. ---- _2007-Jun-01 19:11:11 by anonymous:_ {linebreak} .mode column could only work correctly if the outputting of the rows took place only after the maximum column width of each column for each row was determined in advance. This would require buffering alls rows in memory before *any* data rows would be output. I recommend to remove MODE_Column functionality from shell.c completely, as it never worked correctly. ---- _2007-Jun-05 14:54:30 by anonymous:_ {linebreak} Column mode works fine for most applications. It bases it column widths on the width of the title or a minimum width of 10. If this is not suitable, you can set the column widths manually using the .width command in the shell. If you set your IP address column width to 16 or more everything will be fine. #cfe8bd 2388 active 2007 May anonymous Pending 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. #cfe8bd 2383 active 2007 May anonymous Pending 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 2378 active 2007 May anonymous Pending 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 #cfe8bd 2376 active 2007 May anonymous Pending 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. #e8e8bd 2371 active 2007 May anonymous Pending 2 2 sqlite3_errcode() and sqlite3_errmsg() return unexpected results The manual says that sqlite3_step() directly returns explicit error code if the query has been prepared with the new API sqlite3_prepare_v2(). Subsequently, the functions sqlite3_errcode() and sqlite3_errmsg() should return the correct appropriate error values as well, which they don't - instead something not matching the error is returned. One has to call sqlite3_reset() to get the correct values which should be unnecessary. See the example output below : sqlite3_step: result rc = 19, errcode = 1, errmsg = SQL logic error or missing database sqlite3_reset: result rc = 19, errcode = 19, errmsg = PRIMARY KEY must be unique Code for this example: #include
--- trunk/tools/lemon/lemon.c 2007/01/15 19:11:29 20441 +++ trunk/tools/lemon/lemon.c 2007/01/15 19:48:06 20442 @@ -3245,6 +3245,8 @@ for(i=0; iIt appears that the change in [3593] causes yy_destructor() to be called more than once for tokens, while the one posted here (and applied to wireshark) does not. in the meanwhile I changed the wireshark copy back to havenrhs; i++) used[i] = 0; lhsused = 0; + if (! rp->code) rp->code = "\n"; + append_str(0,0,0,0); for(cp=rp->code; *cp; cp++){ if( safe_isalpha(*cp) && (cp==rp->code || (!safe_isalnum(cp[-1]) && cp[-1]!='_')) ){ @@ -3875,7 +3877,7 @@ /* Generate code which execution during each REDUCE action */ for(rp=lemp->rule; rp; rp=rp->next){ - if( rp->code ) translate_code(lemp, rp); + translate_code(lemp, rp); } for(rp=lemp->rule; rp; rp=rp->next){ struct rule *rp2;
if (! rp->code) rp->code = "\n"; append_str(0,0,0,0); for(cp=rp->code; *cp; cp++){but there's an issue with this the, generated parser gets
#line 0 "./dtd_grammar.lemon"instead of the number pointing to the line where the rule is. I'll keep investigating and keep you POSTed (literally :-) #cfe8bd 2165 active 2007 Jan anonymous Pending drh 4 3 pager performance and checksum pager.c Embedd the 2 byte pager_pagehash() result into the page, near the beginning of the page. Use the intire page to calculate the pager_pagehash exclusive of the two byte page_hash data embedded in the page. That way a simple xor as in CHECK_PAGE of the entire page including the 2 byte pager_pagehash is all that is needed to validate a page. Also you could include the "4 byte" random at the beginning and at the end... But that would be a bit of overkill. The sampling of only every 200 bytes is interesting. Review change the SQLITE_CHECK_PAGES ifdef to a SQLITE_OMIT_PAGE_CHECK.. As on disk page validity is very important. Could this be integrated into a pragma setting? _2007-Jan-12 18:08:49 by anonymous:_ {linebreak} uint16_t pg_chkval(Dpage *pg, uint32_t pg_size) { register int i; register uint16_t val = 0; register uint16_t *bw = (uint16_t *) pg; for(i= 0; i < pg_size;i=i+2 ) val= val^ *bw++ ; return val; } uint16_t pg_calcval(Dpage *pg, uint32_t page_size) { int i; register uint16_t val = 0; register uint16_t *bw = (uint16_t *) pg; /* Scan up to location where chk val is stored */ for(i= 0; i < 8;i=i+2 ) val= val^ *bw++ ; val = val^ 0; bw ++ ; /* Now scan the tail of the block */ for(i=10; i < page_size;i=i+2 ) val= val^ *bw++ ; return val; } #f2dcdc 2140 active 2007 Jan anonymous Pending 3 1 sqlite doesn't link to readline sqlite relies on another library to link to libreadline, causing this error with LDFLAGS=-Wl,--as-needed: gcc -O2 -march=i686 -pipe -DOS_UNIX=1 -DHAVE_USLEEP=1 -DHAVE_FDATASYNC=1 -I. -I./src -DNDEBUG -DTHREADSAFE=1 -DSQLITE_THREAD_OVERRIDE_LOCK=-1 -DSQLITE_OMIT_CURSOR -DHAVE_READLINE=1 -I/usr/include/readline -o .libs/sqlite3 ./src/shell.c ./.libs/libsqlite3.so -lpthread -lncurses /tmp/cclOD1M7.o: In function `process_input': shell.c:(.text+0x37a5): undefined reference to `readline' shell.c:(.text+0x37c0): undefined reference to `add_history' /tmp/cclOD1M7.o: In function `main': shell.c:(.text+0x3f01): undefined reference to `read_history' shell.c:(.text+0x3f1a): undefined reference to `stifle_history' shell.c:(.text+0x3f22): undefined reference to `write_history' collect2: ld returned 1 exit status #cfe8bd 2131 active 2006 Dec anonymous Pending 2 3 Add substring() function (Part of SQL 99) sqlite> SELECT substring('foobar.class',-6,6) = '.class'; SQL error: no such function: substring sqlite> SELECT SUBSTRING('foobar.class',-6,6) = '.class'; SQL error: no such function: SUBSTRING sqlite> SELECT SUBSTR('foobar.class',-6,6) = '.class'; 1 sqlite> SELECT substring('foobar.class' FROM -6 FOR 6) = '.class'; SQL error: near "FROM": syntax error Looking at: http://www.oreilly.com/catalog/sqlnut/chapter/ch04.html SQL99 Syntax SUBSTRING(extraction_string FROM starting_position [FOR length] [COLLATE collation_name]) It would be useful for sqlite to support this syntax too to make the SQL more portable. _2006-Dec-28 16:03:03 by anonymous:_ {linebreak} sqlite has the substr() routine (func.c code):
{ "substr", 3, 0, SQLITE_UTF8, 0, substrFunc }, #ifndef SQLITE_OMIT_UTF16 { "substr", 3, 0, SQLITE_UTF16LE, 0, sqlite3utf16Substr }, #endifthis could be 'aliased' to help you using the substring() SQL99 std just doing:
{ "substr", 3, 0, SQLITE_UTF8, 0, substrFunc }, { "substring", 3, 0, SQLITE_UTF8, 0, substrFunc }, #ifndef SQLITE_OMIT_UTF16 { "substr", 3, 0, SQLITE_UTF16LE, 0, sqlite3utf16Substr }, { "substring", 3, 0, SQLITE_UTF16LE, 0, sqlite3utf16Substr }, #endif#cfe8bd 2128 active 2006 Dec anonymous Pending 4 3 virtual table code doesn't verify type of rowid (calling xUpdate) The virtual tables code doesn't verify the type of rowid when calling update. For example I used the following query: UPDATE foo SET rowid='a string' WHERE 1 This results in a call to xUpdate with argv[0] equal the current rowid but argv[1] is 'a string'. While I'd be quite happy for rowids to be any SQLite type, the xRowid call only allows 64 bit integers. I believe SQLite should check the new rowid in a case like this is an integer and reject it, rather than calling xUpdate with the bad value. (I also just checked with rowid=3.4 and rowid=NULL and they get passed through as is as well) A workaround is to document that the xUpdate method must check the new rowid is an integer type. #cfe8bd 2127 active 2006 Dec anonymous Pending 2 3 Virtual tables do not always free zErrmsg The documentation for virtual tables and in particular the sqlite3_vtab structure says "The SQLite core will free and zero the content of zErrMsg when it delivers the error message text to the client application or when it destroys the virtual table." The latter part does not appear to be true ("when it destroys the virtual table"). I can't find any code that does actually that. (eg vtab.c:496 definitely doesn't, nor does vtab.c:76) Usually the former case happens. However some operations have their error codes ignored (eg xClose). This can result in the zErrMsg pointing to a message but no error code returned upstream (which would clear the message). Finally as far as I can tell the responsibility for freeing sqlite3_vtab is with the xDisconnect/xDestroy callbacks since the corresponding xCreate/xConnect callbacks allocated it. Consequently there is no way for SQLite to even access zErrmsg since it would be a member of a freed structure after xDisconnect/xDestroy returned. #f2dcdc 2126 active 2006 Dec anonymous Pending 3 1 Update hook not invoked when deleteing all rows from table I was testing the update hook feature of SQLite and incidentally I noticed that hook is not invoked for "DELETE FROM" statement with no WHERE clause. Hook works well for "DELETE FROM ... WHERE ..." statement. Steps to reproduce: 1: Open database, setup update hook 2: Execute:{linebreak}CREATE TABLE Test(Test INTEGER); 3: Insert some data:{linebreak}INSERT INTO TEST (Test) VALUES (1); -- update hook invoked for INSERT{linebreak}INSERT INTO TEST (Test) VALUES (2); -- update hook invoked for INSERT{linebreak}INSERT INTO TEST (Test) VALUES (3); -- update hook invoked for INSERT 4: Execute:{linebreak}DELETE FROM TEST; -- update hook IS NOT INVOKED(!) for each row. _2006-Dec-22 15:38:44 by drh:_ {linebreak} Triggers don't work either. This is a feature not a bug. When you do "DELETE FROM table" with no WHERE clause, SQLite drops and recreates the table. Doing it this way have a huge speed boost. If you really need the update hook to work add a "WHERE 1" to the end of the query. ---- _2006-Dec-22 16:11:11 by anonymous:_ {linebreak} I don't really need it, maybe other users. You should update the documentation of _sqlite3_update_hook()_ and _triggers_ mentioning this behaviour. Another solution is to perform "DELETE FROM" as if it was with WHERE clause, if there is an update hook regitered. #c8c8c8 2122 review 2006 Dec anonymous Fixed 1 3 Potential wrong string could stay in with convertUtf8Filename sqlite3WinOpenExclusive uses the following code to convert the utf8 string to a os string:
void *zConverted = convertUtf8Filename(zFilename); if( zConverted==0 ){ return SQLITE_NOMEM; }and use another string converted with another function to save the filename that will be deleted in windows ce:
#if OS_WINCE f.zDeleteOnClose = delFlag ? utf8ToUnicode(zFilename) : 0; f.hMutex = NULL; #endifalso, the variable fileflags should be a DWORD (unsigned integer 32-bit) instead of a native C compiler integer. the following patch correct those things:
int sqlite3WinOpenExclusive(const char *zFilename, OsFile **pId, int delFlag){ winFile f; HANDLE h; DWORD fileflags; void *zConverted = convertUtf8Filename(zFilename); if( zConverted==0 ){ return SQLITE_NOMEM; } assert( *pId == 0 ); fileflags = FILE_FLAG_RANDOM_ACCESS; #if !OS_WINCE if( delFlag ){ fileflags |= FILE_ATTRIBUTE_TEMPORARY | FILE_FLAG_DELETE_ON_CLOSE; } #endif if( isNT() ){ int cnt = 0; do{ h = CreateFileW((WCHAR*)zConverted, GENERIC_READ | GENERIC_WRITE, 0, NULL, CREATE_ALWAYS, fileflags, NULL ); }while( h==INVALID_HANDLE_VALUE && cnt++ < 2 && (Sleep(100), 1) ); }else{ #if OS_WINCE return SQLITE_NOMEM; #else int cnt = 0; do{ h = CreateFileA((char*)zConverted, GENERIC_READ | GENERIC_WRITE, 0, NULL, CREATE_ALWAYS, fileflags, NULL ); }while( h==INVALID_HANDLE_VALUE && cnt++ < 2 && (Sleep(100), 1) ); #endif /* OS_WINCE */ } if( h==INVALID_HANDLE_VALUE ){ sqliteFree(zConverted); return SQLITE_CANTOPEN; } f.h = h; #if OS_WINCE f.zDeleteOnClose = delFlag ? (WCHAR*) zConverted : 0; f.hMutex = NULL; if (!delFlag) #endif sqliteFree(zConverted); TRACE3("OPEN EX %d \"%s\"\n", h, zFilename); return allocateWinFile(&f, pId); }_2006-Dec-21 00:48:28 by drh:_ {linebreak} On wince, convertUtf8Filename() calls utf8ToUnicode() to do its work, so these routines end up computing exactly the same filename. ---- _2006-Dec-21 01:30:33 by anonymous:_ {linebreak} Yes, I figured out that they still calling the same function, but why converting / freeing then converting agains ? just save some CPU cycles (this could help a lot in Windows CE < 200 mhz, just like my app runs (windows ce 2.11, SH3 130MHz HP Jornada 680)!!! Please apply my patch to make it faster. #f2dcdc 2100 active 2006 Dec anonymous Pending 1 1 Fixes for SQL lower() and upper() As acknowledged in the documentation, the SQL lower() and upper() functions might not work correctly on UTF-8 characters. This bug might show if a country specific locale is used instead of the standard C locale. Under certain circumstances, SQL lower() or upper() can even corrupt the UTF-8 string into invalid UTF-8 if the tolower() and toupper() C functions convert character values starting from 0x80. Below I propose implementations of lowerFunc() and upperFunc() which work correctly with UTF-8 characters, regardless of the implementation of the C library tolower() and toupper() functions. If these C functions are implemented to support high ASCII or even Unicode case conversion, the new SQL lower() and upper() will support them as well. The proposed C implementation applies a technique also found in sqlite3VdbeMemTranslate() in utf.c and makes use of some macros contained in that unit. To avoid duplicating existing code, it could make sense to move lowerFunc() and lowerFunc() to utf.c, just as it has been done with sqlite3utf16Substr(). Finally, here is the code: /* ** Implementation of the upper() and lower() SQL functions. */ static void upperFunc(sqlite3_context *context, int argc, sqlite3_value **argv){ const unsigned char *zIn, *zInTerm; unsigned char *z, *zOut; int c, l; if( argc<1 || SQLITE_NULL==sqlite3_value_type(argv[0]) ) return; zIn = sqlite3_value_text(argv[0]); if( zIn==0 ) return; l = sqlite3_value_bytes(argv[0]); zInTerm = &zIn[l]; /* When converting case, the maximum growth results from ** translating a 1-byte UTF-8 character to a 4-byte UTF-8 character. */ zOut = sqliteMalloc( l * 4 ); z = zOut; while( zIn
#ifdef SQLITE_UNICODE_UPPERLOWERFUNCS #define WCHAR_T_SIZE sizeof(wchar_t) #if (WCHAR_T_SIZE == 2) #define MAXUPPERLOWERCHAR_AVAIL 0x0000ffff #else // (WCHAR_T_SIZE == 4) #define MAXUPPERLOWERCHAR_AVAIL 0x7fffffff #endif // (WCHAR_T_SIZE == 2) #define TOLOWERSQLFUNC(c) unicode_tolower #define TOUPPERSQLFUNC(c) unicode_toupper int unicode_tolower(const int c) { wchar_t buff [2]; if (c > MAXUPPERLOWERCHAR_AVAIL) return c; buff[0] = (wchar_t) c; buff[1] = 0; _wcslwr(buff); return (int) buff[0]; } int unicode_toupper(const int c) { wchar_t buff [2]; if (c > MAXUPPERLOWERCHAR_AVAIL) return c; buff[0] = (wchar_t) c; buff[1] = 0; _wcsupr(buff); return (int) buff[0]; } #else // SQLITE_UNICODE_UPPERLOWERFUNCS #define TOLOWERSQLFUNC(c) (c > 255 ? c : tolower(c)) #define TOUPPERSQLFUNC(c) (c > 255 ? c : toupper(c)) #endif // SQLITE_UNICODE_UPPERLOWERFUNCS /* ** Implementation of the upper() and lower() SQL functions. */ static void upperFunc(sqlite3_context *context, int argc, sqlite3_value **argv){ const unsigned char *zIn, *zInTerm; unsigned char *z, *zOut; int c, l; if( argc<1 || SQLITE_NULL==sqlite3_value_type(argv[0]) ) return; zIn = sqlite3_value_text(argv[0]); if( zIn==0 ) return; l = sqlite3_value_bytes(argv[0]); zInTerm = &zIn[l]; /* When converting case, the maximum growth results from ** translating a 1-byte UTF-8 character to a 4-byte UTF-8 character. */ zOut = sqliteMalloc( l * 4 ); z = zOut; while( zIn