bgcolor # Type Status Created By Subsys Changed Assigned Svr Pri Title _Description _Remarks #f2dcdc 2916 code active 2008 Feb anonymous 2008 Feb 1 1 sqlitedll-3_5_5.zip is older 3.5.4 binary sqlitedll-3_5_5.zip in download section is same with old 3.5.4 binary. _2008-Feb-01 12:13:04 by anonymous:_ {linebreak} Yes , I can confirm it #f2dcdc 2907 code active 2008 Jan anonymous 2008 Jan 1 1 Issues of sqlite3 with Windows Mobile 5/6 hi. we are currently using sqlite3 for our mobile application. it has been running without a hitch on pocket pc 2003 and previous versions. come windows mobile 5 and 6 we have been getting errors, although not consistent yet. one example is 'EXCEPTION_DATATYPE_MISALIGNMENT'. another is 'SELECT STATMENTS TO THE LEFT AND RIGHT OF UNION ARE NOT EQUAL'. i was wondering if you have any known compatibility issues of your product with this version of windows mobile. thanks in advance. _2008-Jan-28 13:26:26 by anonymous:_ {linebreak} EXCEPTION_DATATYPE_MISALIGNMENT is thrown when you try to use and Odd pointer address. I wrote a custom allocator for WinCE/ARM platform, and I have to take care about memory alignment (I used to align at 2 bytes, and at that time it solved the problem) #f2dcdc 2898 code active 2008 Jan anonymous 2008 Jan 1 1 Latest CVS for 3.5.4 fails to build test1.c gcc -pipe -O3 -g -Wall -DSQLITE_DISABLE_DIRSYNC=1 -I. -I../src -DNDEBUG -I/usr/include -DSQLITE_THREADSAFE=1 -DSQLITE_THREAD_OVERRIDE_LOCK=1 -DSQLITE_OMIT_LOAD_EXTENSION=1 -DTCLSH=1 -DSQLITE_TEST=1 -DSQLITE_CRASH_TEST=1 -DSQLITE_NO_SYNC=1 -DTEMP_STORE=1 -o .libs/testfixture ../src/attach.c ../src/btree.c ../src/build.c ../src/date.c ../src/expr.c ../src/func.c ../src/insert.c ../src/malloc.c ../src/os.c ../src/os_os2.c ../src/os_unix.c ../src/os_win.c ../src/pager.c ../src/pragma.c ../src/prepare.c ../src/printf.c ../src/select.c ../src/test1.c ../src/test2.c ../src/test3.c ../src/test4.c ../src/test5.c ../src/test6.c ../src/test7.c ../src/test8.c ../src/test9.c ../src/test_autoext.c ../src/test_async.c ../src/test_btree.c ../src/test_config.c ../src/test_hexio.c ../src/test_malloc.c ../src/test_md5.c ../src/test_onefile.c ../src/test_schema.c ../src/test_server.c ../src/test_tclvar.c ../src/test_thread.c ../src/tokenize.c ../src/utf.c ../src/util.c ../src/vdbe.c ../src/vdbeapi.c ../src/vdbeaux.c ../src/vdbemem.c ../src/where.c parse.c ../src/tclsqlite.c ./.libs/libsqlite3.so -L/usr/lib64 -ltcl8.4 -ldl -lpthread -lieee -lm -Wl,--rpath -Wl,/common/pkgs/sqlite-3.5.4.3/lib ../src/build.c: In function 'sqlite3RefillIndex': ../src/build.c:2275: warning: cast to pointer from integer of different size ../src/func.c: In function 'trimFunc': ../src/func.c:919: warning: cast from pointer to integer of different size ../src/func.c: In function 'sqlite3RegisterBuiltinFunctions': ../src/func.c:1464: warning: cast to pointer from integer of different size ../src/func.c:1483: warning: cast to pointer from integer of different size ../src/insert.c: In function 'sqlite3GenerateConstraintChecks': ../src/insert.c:1200: warning: cast to pointer from integer of different size ../src/insert.c:1034: warning: 'j2' may be used uninitialized in this function ../src/insert.c: In function 'sqlite3Insert': ../src/insert.c:373: warning: 'regFromSelect' may be used uninitialized in this function ../src/test1.c: In function 'test_collate_func': ../src/test1.c:2085: warning: cast from pointer to integer of different size ../src/test1.c: In function 'test_collate_needed_cb': ../src/test1.c:2209: warning: cast to pointer from integer of different size ../src/test1.c: In function 'alignmentCollFunc': ../src/test1.c:2258: warning: cast from pointer to integer of different size ../src/test1.c:2259: warning: cast from pointer to integer of different size ../src/test8.c: In function 'echoBestIndex': ../src/test8.c:722: warning: 'nRow' may be used uninitialized in this function ../src/vdbe.c: In function 'sqlite3VdbeExec': ../src/vdbe.c:502: warning: 'pOut' may be used uninitialized in this function ../src/vdbe.c:501: warning: 'pIn3' may be used uninitialized in this function ../src/vdbe.c:501: warning: 'pIn2' may be used uninitialized in this function ../src/vdbe.c:501: warning: 'pIn1' may be used uninitialized in this function ../src/vdbeaux.c: In function 'sqlite3VdbeChangeP4': ../src/vdbeaux.c:529: warning: cast from pointer to integer of different size ../src/vdbemem.c: In function 'sqlite3ValueText': ../src/vdbemem.c:911: warning: cast from pointer to integer of different size /tmp/ccsuOeus.o: In function `reset_prng_state': /build/work/sqlite-3.5.4.3/bld/../src/test1.c:4280: undefined reference to `sqlite3ResetPrngState' /tmp/ccsuOeus.o: In function `restore_prng_state': /build/work/sqlite-3.5.4.3/bld/../src/test1.c:4267: undefined reference to `sqlite3RestorePrngState' /tmp/ccsuOeus.o: In function `save_prng_state': /build/work/sqlite-3.5.4.3/bld/../src/test1.c:4254: undefined reference to `sqlite3SavePrngState' collect2: ld returned 1 exit status make: *** [testfixture] Error 1 _2008-Jan-17 23:54:58 by anonymous:_ {linebreak} Problem appears to be here in libsqlite.3.so.0.8.6 as shown by: nm -A .libs/libsqlite3.so.0.8.6 | grep sqlite3ResetPrngState which shows no entry point. And: nm -A .libs/random.o | grep sqlite3ResetPrngState which also shows no entry point. ---- _2008-Jan-17 23:56:55 by anonymous:_ {linebreak} Ah... It appears -DSQLITE_TEST should be passed when building test1.c and left off when building prior to install. ---- _2008-Jan-21 20:16:00 by anonymous:_ {linebreak} In the makefile the right flag appears to be set, it's just not making it through to the compile for some reason. ---- _2008-Jan-21 20:16:24 by anonymous:_ {linebreak} Still fails the same based on today's cvs update. ---- _2008-Jan-23 03:14:49 by anonymous:_ {linebreak} This bug fixed as of latest cvs pull #f2dcdc 2878 code active 2008 Jan anonymous 2008 Jan 1 1 Memory leaks with latest CVS [4693] This SQL leaks memory with CVS [4693]: CREATE TABLE x(id integer primary key, a TEXT NULL); INSERT INTO x (a) VALUES ('first'); CREATE TABLE tempx(id integer primary key, a TEXT NULL); INSERT INTO tempx (a) VALUES ('t-first'); CREATE VIEW tv1 AS SELECT x.id, tx.id FROM x JOIN tempx tx ON tx.id=x.id; One leak is caused by "CREATE TABLE tempx", a second one by "CREATE VIEW tv1". The above SQL is a digest of select7.test, select7-2.1. _2008-Jan-14 17:51:11 by anonymous:_ {linebreak} I have tested again with CVS [4711] and it does no longer show the original leaks. I therefore consider this issue fixed and I will now close this ticket. ---- _2008-Jan-14 23:56:34 by anonymous:_ {linebreak} Doing a fulltest with -MSQLITE_MEMDEUG I see reports of memory leaks. I assume these are of little or minimal interest at present because of the amount of code flux. If you do want details, please let me know (I'll recheck this ticket tomorrow I guess). ---- _2008-Jan-15 08:23:56 by anonymous:_ {linebreak} Thanks for the follow-up. I am not running the original test-suite but have have ported a great number of them to Delphi. If you could just let me know which tests caused the leaks you fixed in [4712] I'd be more than glad the port these test as well and let you know my findings. ---- _2008-Jan-19 00:56:17 by anonymous:_ {linebreak} A test from 20 minutes ago passes cleanly. This could be closed. #f2dcdc 2897 code active 2008 Jan anonymous 2008 Jan 1 1 String or BLOB exceed size limit This error was shown after attemp to read script from SQLite 3.5.4 shell in order to recreate old DB. Details: 1. Database was created with SQLite 3.3.4. Around 20 standard fieds and one BLOB. 2. The only one existed table was dumped with shell of SQLite 3.5.4. SQL script seems to be coorrect. 3. Opened SQLite 3.5.4 and read script in new DB. The error "String or BLOB exceed size limit" are sown for several lines. Many records missing. 4. Attempted to dump table with shell of version 3.3.6 (have no more 3.3.4 shell) and read into new DB with 3.5.4 shell The same errors are shown. The same steps was attempted with 3.3.6. shell only. All seems to be correct. _2008-Jan-17 20:23:25 by drh:_ {linebreak} This size limit on BLOBs in SQLite version 3.5.4 is 1GB. How big is your blob, exactly? ---- _2008-Jan-17 22:22:24 by anonymous:_ {linebreak} BLOB in each record is no more than few MB. Mostly it is few KB (e-client and news application). Whole DB have around 200MB. ---- _2008-Jan-18 02:28:11 by drh:_ {linebreak} This issue is probably resolved by check-in [4636], then. ---- _2008-Jan-18 14:28:13 by anonymous:_ {linebreak} If directive SQLITE_MAX_SQL_LENGTH is not defined it is set to 1,000,000 (10^6) in amalgamation code of 3.5.4. #f2dcdc 2893 code active 2008 Jan anonymous 2008 Jan 1 1 incorrect integer range tests recently a function that performs integer range tests was added to the cvs (check-in [4706]), but if i am correct there is a problem in the return value of the function in the file vdbemem.c: static i64 doubleToInt64(double r){ ... if( r<(double)minInt ){ return minInt; }else if( r>(double)maxInt ){ return minInt; <-- is this correct, shouldn't it be maxInt? }else{ return (i64)r; } } _2008-Jan-16 17:33:56 by drh:_ {linebreak} See the remarks on ticket #2280. The code duplicates the behavior of the FPU on x86. ---- _2008-Jan-16 18:21:28 by anonymous:_ {linebreak} did you mean ticket #2880? didn't read that ticket before, but since there was no comment regarding that behavior in the function it seemed (to my eyes) that it was a mistake. maybe adding a small comment in there would clarify this issue ---- _2008-Jan-16 18:39:42 by anonymous:_ {linebreak} Just because the double to int overflow behavior happens to be that way with GCC on x86, is it desirable? #e8e8bd 2881 build active 2008 Jan anonymous 2008 Jan 1 1 Latest sqlite-3.5.4 build fail on latest Fedora 2.6.23.12-52.fc7 Two test cases fail. io-4.1... Expected: [3] Got: [2] io-4.2.1... Ok io-4.2.2... Ok io-4.2.3... Expected: [3] Got: [2] io-4.3.1... Ok Let me know how to run individual test cases and how this might be fixed. Here's how I built sqlite using latest CVS. If something is wrong here, let me know and I'll rebuild/retest. I'm building on latest Fedora fc7. Thanks. _______ net1#uname -a Linux net1.coolsurf.com 2.6.23.12-52.fc7 #1 SMP Tue Dec 18 20:27:10 EST 2007 x86_64 x86_64 x86_64 GNU/Linux net1#build_sqlite mkdir -p /build/work/sqlite-3.5.4 cd /build/work/sqlite-3.5.4 unset CDPATH export CFLAGS='-pipe -O3 -g -DSQLITE_DISABLE_DIRSYNC=1 -Wall' rm -rf bld cvs -d :pserver:anonymous@www.sqlite.org:/sqlite -r update . mkdir bld cd bld ../configure --prefix=/common/pkgs/sqlite-3.5.4 --enable-tcl --with-tcl=/usr/lib64 --enable-threadsafe --enable-threads-override-locks make groupadd vuser || /bin/true useradd -M -g vuser -d /vhost/davidfavor.com/users/david -s /bin/zsh david || /bin/true useradd -M -g vuser -d /vhost/livefeast.com/users/yemiah -s /bin/zsh yemiah || /bin/true chown david:vuser -R .. su -c "make test" david _2008-Jan-11 17:18:52 by anonymous:_ {linebreak} Same tests still fail with CVS of today around 11AM CST. ---- _2008-Jan-11 17:41:55 by drh:_ {linebreak} FWIW, both those test cases pass on SuSE 10.1. I do not understand why they are failing on Fedora. But in any event, the tests in question are verifying logic that implements an optimization that is not used on Fedora, ever. So the failures are of no consequence. If those are the only two tests that fail, then you can safely use the build for whatever it is you are trying to do. ---- _2008-Jan-11 19:16:19 by anonymous:_ {linebreak} Failures when 'make fulltest' built with CFLAGS of '-pipe -O3 -g -Wall -DSQLITE_DISABLE_DIRSYNC=1 -DSQLITE_MEMDEBUG' exclusive-malloc-1.transient.746...make: *** [fulltest] Segmentation fault Failures when 'make fulltest' built with CFLAGS of '-pipe -O3 -g -Wall -DSQLITE_DISABLE_DIRSYNC=1' Skipping malloc tests: not compiled with -DSQLITE_MEMDEBUG... 6 errors out of 61998 tests Failures on these tests: exclusive-ioerr-2.280.4 exclusive-ioerr-2.281.4 exclusive-ioerr-2.282.4 incrvacuum-ioerr-1.31.4 io-4.1 io-4.2.3 All memory allocations freed - no leaks Maximum memory usage: 14376554 bytes Pre 3.5.x builds work fine on Fedora. If you're open to debugging all these, I'd like to go through and resolve all these one by one, so Fedora has a clean build/fulltest. Please let me know how to run each test individually and I'll try to figure out the problem with each. Thanks. #f2dcdc 2874 code active 2008 Jan anonymous 2008 Jan 1 1 THREADSAFE #define HAVE_LOCALTIME_R, HAVE_GMTIME_R in os_unix.c The precompiled shared sqlite3 library for Linux on sqlite.org which appears to be built with pthread support is using localtime and gmtime which are not threadsafe. For THREADSAFE builds could either configure be changed to detect the functions gmtime_r and localtime_r or change os_unix.c to explicitly #define HAVE_LOCALTIME_R and HAVE_GMTIME_R? #f2dcdc 2873 code active 2008 Jan anonymous 2008 Jan 1 1 HAVE_USLEEP, HAVE_FDATASYNC=1 detected but not used by configure; make I noticed that a couple of open source projects were not picking up usleep() for recent sqlite builds and used the coarser grained sleep() instead. Around 11 months ago something changed in sqlite's build process. It seems that both -DHAVE_USLEEP=1, -DHAVE_FDATASYNC=1 and -DOS_UNIX=1 are detected correctly by configure but not used by the generated Makefile. As result, UNIX builds of sqlite3 via ./configure do not use usleep() and fdatasync() and do not define OS_UNIX. I don't know whether the lack of fdatasync() versus the default fsync() affects anyone. Please apply the following patch which corrects the problem with "./configure && make". Thank you.
Index: configure =================================================================== RCS file: /sqlite/sqlite/configure,v retrieving revision 1.45 diff -u -3 -p -r1.45 configure --- configure 27 Nov 2007 14:50:07 -0000 1.45 +++ configure 5 Jan 2008 07:41:00 -0000 @@ -18520,9 +18520,9 @@ if test "$TARGET_EXEEXT" = ".exe"; then OS_UNIX=0 OS_WIN=0 OS_OS2=1 - TARGET_CFLAGS="$TARGET_CFLAGS -DOS_OS2=1" + CFLAGS="$CFLAGS -DOS_OS2=1" if test "$ac_compiler_gnu" == "yes" ; then - TARGET_CFLAGS="$TARGET_CFLAGS -Zomf -Zexe -Zmap" + CFLAGS="$CFLAGS -Zomf -Zexe -Zmap" BUILD_CFLAGS="$BUILD_CFLAGS -Zomf -Zexe" fi else @@ -18530,14 +18530,14 @@ if test "$TARGET_EXEEXT" = ".exe"; then OS_WIN=1 OS_OS2=0 tclsubdir=win - TARGET_CFLAGS="$TARGET_CFLAGS -DOS_WIN=1" + CFLAGS="$CFLAGS -DOS_WIN=1" fi else OS_UNIX=1 OS_WIN=0 OS_OS2=0 tclsubdir=unix - TARGET_CFLAGS="$TARGET_CFLAGS -DOS_UNIX=1" + CFLAGS="$CFLAGS -DOS_UNIX=1" fi @@ -19392,7 +19392,7 @@ fi echo "$as_me:$LINENO: result: $ac_cv_func_usleep" >&5 echo "${ECHO_T}$ac_cv_func_usleep" >&6 if test $ac_cv_func_usleep = yes; then - TARGET_CFLAGS="$TARGET_CFLAGS -DHAVE_USLEEP=1" + CFLAGS="$CFLAGS -DHAVE_USLEEP=1" fi @@ -19491,7 +19491,7 @@ fi echo "$as_me:$LINENO: result: $ac_cv_func_fdatasync" >&5 echo "${ECHO_T}$ac_cv_func_fdatasync" >&6 if test $ac_cv_func_fdatasync = yes; then - TARGET_CFLAGS="$TARGET_CFLAGS -DHAVE_FDATASYNC=1" + CFLAGS="$CFLAGS -DHAVE_FDATASYNC=1" fi Index: configure.ac =================================================================== RCS file: /sqlite/sqlite/configure.ac,v retrieving revision 1.31 diff -u -3 -p -r1.31 configure.ac --- configure.ac 27 Nov 2007 14:50:07 -0000 1.31 +++ configure.ac 5 Jan 2008 07:41:00 -0000 @@ -310,9 +310,9 @@ if test "$TARGET_EXEEXT" = ".exe"; then OS_UNIX=0 OS_WIN=0 OS_OS2=1 - TARGET_CFLAGS="$TARGET_CFLAGS -DOS_OS2=1" + CFLAGS="$CFLAGS -DOS_OS2=1" if test "$ac_compiler_gnu" == "yes" ; then - TARGET_CFLAGS="$TARGET_CFLAGS -Zomf -Zexe -Zmap" + CFLAGS="$CFLAGS -Zomf -Zexe -Zmap" BUILD_CFLAGS="$BUILD_CFLAGS -Zomf -Zexe" fi else @@ -320,14 +320,14 @@ if test "$TARGET_EXEEXT" = ".exe"; then OS_WIN=1 OS_OS2=0 tclsubdir=win - TARGET_CFLAGS="$TARGET_CFLAGS -DOS_WIN=1" + CFLAGS="$CFLAGS -DOS_WIN=1" fi else OS_UNIX=1 OS_WIN=0 OS_OS2=0 tclsubdir=unix - TARGET_CFLAGS="$TARGET_CFLAGS -DOS_UNIX=1" + CFLAGS="$CFLAGS -DOS_UNIX=1" fi AC_SUBST(BUILD_EXEEXT) @@ -565,13 +565,13 @@ AC_SUBST(TARGET_DEBUG) ######### # Figure out whether or not we have a "usleep()" function. # -AC_CHECK_FUNC(usleep, [TARGET_CFLAGS="$TARGET_CFLAGS -DHAVE_USLEEP=1"]) +AC_CHECK_FUNC(usleep, [CFLAGS="$CFLAGS -DHAVE_USLEEP=1"]) #-------------------------------------------------------------------- # Redefine fdatasync as fsync on systems that lack fdatasync #-------------------------------------------------------------------- -AC_CHECK_FUNC(fdatasync, [TARGET_CFLAGS="$TARGET_CFLAGS -DHAVE_FDATASYNC=1"]) +AC_CHECK_FUNC(fdatasync, [CFLAGS="$CFLAGS -DHAVE_FDATASYNC=1"]) ######### # Generate the output files._2008-Jan-05 08:24:29 by anonymous:_ {linebreak} It appears that http://www.sqlite.org/sqlite3-3.5.4.bin.gz and http://www.sqlite.org/sqlite-3.5.4.so.gz use sleep and fsync even though usleep and fdatasync are available on Linux. On the Linux man page, it claims that fdatasync is more efficient than fsync: "Unfortunately, fsync() will always initiate two write operations: one for the newly written data and another one in order to update the modification time stored in the inode. If the modification time is not a part of the transaction concept fdatasync() can be used to avoid unnecessary inode disk write operations." #e8e8bd 2866 build active 2008 Jan anonymous 2008 Jan 1 3 Problems building Windows native in cygwin/mingw environment Trying to build Windows native version using the Cygwin build environment. $ gcc -v Reading specs from /usr/lib/gcc/i686-pc-cygwin/3.4.4/specs Configured with: /usr/build/package/orig/test.respin/gcc-3.4.4-3/configure --verbose --prefix=/usr --exec-prefix=/usr --sysconfdir=/etc --libdir=/usr/lib --libexecdir=/usr/lib --mandir=/usr/share/man --infodir=/usr/share/info --enable-languages=c,ada,c++,d,f77,pascal,java,objc --enable-nls --without-included-gettext --enable-version-specific-runtime-libs --without-x --enable-libgcj --disable-java-awt --with-system-zlib --enable-interpreter --disable-libgcj-debug --enable-threads=posix --enable-java-gc=boehm --disable-win32-registry --enable-sjlj-exceptions --enable-hash-synchronization --enable-libstdcxx-debug Thread model: posix gcc version 3.4.4 (cygming special, gdc 0.12, using dmd 0.125) $ $ CFLAGS=-mno-cygwin ./configure --disable-tcl --enable-threadsafe $ make A) The make appears to build sqlite3.exe just fine, without errors or warnings. This binary does work from cmd.exe, BUT not from within the bash cygwin shell for some reason, unlike other Windows native binaries I've built. Next... $ make install B) The cc sqlite3.c -o sqlite3 fails to rebuild sqlite3.exe correctly with the -mno-cygwin option. The output follows: rm -rf tsrc mkdir -p tsrc cp ./src/alter.c ./src/analyze.c ./src/attach.c ./src/auth.c ./src/btmutex.c ./src/btree.c ./src/btree.h ./src/build.c ./src/callback.c ./src/complete.c ./src/date.c ./src/delete.c ./src/expr.c ./src/func.c ./src/hash.c ./src/hash.h ./src/insert.c ./src/journal.c ./src/legacy.c ./src/loadext.c ./src/main.c ./src/malloc.c ./src/mem1.c ./src/mem2.c ./src/mem3.c ./src/mutex.c ./src/mutex_os2.c ./src/mutex_unix.c ./src/mutex_w32.c ./src/os.c ./src/os_unix.c ./src/os_win.c ./src/os_os2.c ./src/pager.c ./src/pager.h ./src/parse.y ./src/pragma.c ./src/prepare.c ./src/printf.c ./src/random.c ./src/select.c ./src/shell.c ./src/sqlite.h.in ./src/sqliteInt.h ./src/table.c ./src/tclsqlite.c ./src/tokenize.c ./src/trigger.c ./src/utf.c ./src/update.c ./src/util.c ./src/vacuum.c ./src/vdbe.c ./src/vdbe.h ./src/vdbeapi.c ./src/vdbeaux.c ./src/vdbeblob.c ./src/vdbefifo.c ./src/vdbemem.c ./src/vdbeInt.h ./src/vtab.c ./src/where.c ./ext/fts1/fts1.c ./ext/fts1/fts1.h ./ext/fts1/fts1_hash.c ./ext/fts1/fts1_hash.h ./ext/fts1/fts1_porter.c ./ext/fts1/fts1_tokenizer.h ./ext/fts1/fts1_tokenizer1.c sqlite3.h ./src/btree.h ./src/btreeInt.h ./src/hash.h ./src/sqliteLimit.h ./src/mutex.h opcodes.h ./src/os.h ./src/os_common.h ./src/sqlite3ext.h ./src/sqliteInt.h ./src/vdbe.h parse.h ./ext/fts1/fts1.h ./ext/fts1/fts1_hash.h ./ext/fts1/fts1_tokenizer.h ./src/vdbeInt.h tsrc cp: warning: source file `./src/btree.h' specified more than once cp: warning: source file `./src/hash.h' specified more than once cp: warning: source file `./src/sqliteInt.h' specified more than once cp: warning: source file `./src/vdbe.h' specified more than once cp: warning: source file `./ext/fts1/fts1.h' specified more than once cp: warning: source file `./ext/fts1/fts1_hash.h' specified more than once cp: warning: source file `./ext/fts1/fts1_tokenizer.h' specified more than once cp: warning: source file `./src/vdbeInt.h' specified more than once rm tsrc/sqlite.h.in tsrc/parse.y cp parse.c opcodes.c keywordhash.h tsrc tclsh ./tool/mksqlite3c.tcl cc sqlite3.c -o sqlite3 /usr/lib/gcc/i686-pc-cygwin/3.4.4/../../../libcygwin.a(libcmain.o):(.text+0xab): undefined reference to `_WinMain@16' collect2: ld returned 1 exit status make: *** [sqlite3] Error 1 $ #f2dcdc 2865 code active 2007 Dec anonymous 2007 Dec 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. #f2dcdc 2508 code active 2007 Jul anonymous 2007 Dec 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 916 new active 2004 Sep anonymous Unknown 2007 Dec 1 1 No delete notification for INSERT OR REPLACE It would be nice if the "ON DELETE" trigger is called for the row substituted with a new one during REPLACE. Or, even better, one could add the OLD statement for the "ON INSERT" trigger and set it to point to the same row as NEW if a new row is inserted or to the deleting row if replace occurs. Thanks. _2007-Dec-17 21:36:40 by anonymous:_ {linebreak} I have the same problem. My solution would be to stick with the documentation of the ON REPLACE algorithm: "When a UNIQUE constraint violation occurs, the pre-existing rows that are causing the constraint violation are removed prior to inserting or updating the current row". That is, to call ON DELETE trigger whenever rows are removed. Thank you, and keep going, you do wonderful job anyway. #f2dcdc 2842 code active 2007 Dec anonymous 2007 Dec 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. #e8e8bd 2841 todo active 2007 Dec anonymous 2007 Dec 1 1 The sqlite mailing list has become overrun by trolls The sqlite mailing list is very useful. The S/N is at times a little high but nonetheless quite manageable. Recently (see the DeviceSQL thread) it got really bad. Would moderation be unacceptable during these periods of time where people feel the need to protect their ego's? The sqlite mailing list is primarily about sqlite (well, and lemon), not a marketing vector for other products? Surely they have their own lists and resources for that? #e8e8bd 2813 build active 2007 Nov anonymous 2007 Nov 1 1 compile error on Windows CE environment: visual c++ 2005 window ce 6.0 customize sdk sqlite-amalgamation-3_5_3 I get error: Error 27 error C2040: 'localtime' : 'tm *(const time_t *)' differs in levels of indirection from 'int ()' d:\SubProjects\Sqlite\sqlite3.c 18574 but if I add code in line 7095: struct tm *__cdecl localtime(const time_t *t); then Success! #f2dcdc 2809 code active 2007 Nov anonymous 2007 Nov 1 1 PRAGMA collation_list shows unregistered collations As presented on the mailing list: Imagine that a SQLite3 database opened in a custom application with a registered a collation sequence named "unknown" has created the following table: CREATE TABLE a (b COLLATE unknown); Now open this table in the default SQLite3 CLI. Up to here, everything works as expected. Next issue "PRAGMA collation_list;" and notice that "unknown" lists next to the other registered collations, even though "unknown" is not registered with the default SQLite3 CLI: sqlite> PRAGMA collation_list; 0|unknown 1|NOCASE 2|BINARY Responses from the mailing list indicate that this is not the expected behaviour. "PRAGMA collation_list;" should list registered collations only. _2007-Nov-28 16:12:17 by anonymous:_ {linebreak} I don't think this is a bug. If the CLI is not aware of the collation, it should not process the query that makes use of the collation because it would certainly be wrong if it simply ignored the collation. This is not unlike a user-registered SQL function that does not exist in the CLI. I would not expect or want the sqlite3 CLI to ignore the unknown function, nor would I want the CLI to process queries ignoring the custom collation. #f2dcdc 2810 code active 2007 Nov anonymous 2007 Nov 1 1 Unregistered collation problems with simple subselects As discussed on the mailing-list: Imagine that a SQLite3 database opened in a custom application with a registered a collation sequence named "unknown" has created the following table: CREATE TABLE a (b COLLATE unknown); Now open this table in the default SQLite3 CLI. Up to here, everything works as expected. Simple queries like "SELECT * FROM a;" work fine. But subselects, in their most basic form and with no sorting or comparisons, result in an error: sqlite> INSERT INTO a VALUES ('one'); sqlite> SELECT * FROM a, (SELECT * FROM a); SQL error: no such collation sequence: unknown sqlite> SELECT * FROM (SELECT * FROM a); SQL error: no such collation sequence: unknown sqlite> SELECT *, * FROM a; one|one This is surprising because the collation sequence should not matter to the queries. In fact, the union without the subselect works just fine and without errors. To demonstrate, here is the explain output of a table with a registered collation sequence. No mention of the collation name here: sqlite> CREATE TABLE b (b collate nocase); sqlite> EXPLAIN SELECT * FROM b, (SELECT * FROM b); 0|Goto|0|17| 1|Integer|0|0| 2|OpenRead|0|3| 3|SetNumColumns|0|1| 4|Integer|0|0| 5|OpenRead|2|3| 6|SetNumColumns|2|1| 7|Rewind|0|14| 8|Rewind|2|13| 9|Column|0|0| 10|Column|2|0| 11|Callback|2|0| 12|Next|2|9| 13|Next|0|8| 14|Close|0|0| 15|Close|2|0| 16|Halt|0|0| 17|Transaction|0|0| 18|VerifyCookie|0|4| 19|TableLock|0|3|b 20|Goto|0|1| 21|Noop|0|0| #f2dcdc 2791 code active 2007 Nov anonymous 2007 Nov 1 1 Allow building FTS[123] as part of sqlite library with configure See attached patch. #f2dcdc 2770 code active 2007 Nov anonymous 2007 Nov 1 1 Problem with BLOB in 3.5.x ? After I've switched from 3.3.18 to 3.5.2, selecting from table which contains BLOB LONGER THAN ABOUT 990 BYTES returns error "SQL logic error or missing database" after call to _sqlite3_step(). I'm using preprocessed sources downloaded from here. DEBUG build of preprocessed sources works correctly, problem is only in RELEASE build. I'm using VC6.0 to compile. Any idea what could be wrong? Thank you! Can you try to reproduce this with the sqlite shell tool? Thanks. Large blobs work for me with both release and debug builds (not msvc though, gcc/linux). ---- _2007-Nov-12 18:41:37 by anonymous:_ {linebreak} sqlite3.exe provided here works with the database. Problem is only with release build (static library linked into test application). Here is test app which exits with "Error 1" in release build: int main(int argc, char* argv[]) { int rc; sqlite3* db; sqlite3_stmt* stmt; rc = sqlite3_open("n2.db3", &db); rc = sqlite3_prepare(db, "CREATE TABLE [ttt] ([bbb] BLOB)", -1, &stmt, 0 ); rc = sqlite3_step(stmt); rc = sqlite3_reset(stmt); char text[10000],query[20000]; strnset(text,'a',sizeof(text)-1); sprintf(query,"insert into [ttt] values (?)"); rc = sqlite3_prepare(db, query, -1, &stmt, 0 ); rc = sqlite3_bind_blob(stmt,1,text,sizeof(text), SQLITE_TRANSIENT); rc = sqlite3_step(stmt); rc = sqlite3_reset(stmt); rc = sqlite3_prepare(db, "select * from ttt", -1, &stmt, 0 ); rc = sqlite3_step(stmt); if (rc == SQLITE_ROW) { printf("%s: OK",sqlite3_column_text(stmt,1)); } else if (rc == SQLITE_DONE) { printf("DONE"); } else { printf("Error %d",rc); } return 0; } ---- _2007-Nov-12 18:56:24 by drh:_ {linebreak} You should be using sqlite3_finalize() instead of sqlite3_reset(). You are leaking memory. Also, you should use sqlite3_prepare_v2() to avoid problems with changing schemas. But even without those fixes, I cannot reproduce the problem on Linux. ---- _2007-Nov-12 19:39:31 by anonymous:_ {linebreak} Suggested fixes didn't help. I've tried to debug it. It fails in btree.c, line 3056: if( offset+amt > nKey+pCur->info.nData ){ /* Trying to read or write past the end of the data is an error */ return SQLITE_ERROR; } there seems to be different values in release mode. My debugger does not show values of variables in release mode, so I can be wrong, but it seems in release offset is 5 and in debug it is 4. There can be something wrong with compilation, I'll try to figure this out tomorrow. BTW compilation of static libraty in VC6.0 gives 185 warnings. I don't know if it is ok, it haven't caused problems in older sqlite ---- _2007-Nov-13 08:47:28 by anonymous:_ {linebreak} I've turned off "Maximize Speed" option - this is causing the problem. No optimizations and optimize for size seems to be working. But it still makes me nervous :(( I really don't need corrupted database and now I hope it won't slow down too much. Unfortunately old library does not implement replace function so I don't want to switch back. This could be warning to others, I'm using VC++ 6.0 SP 6. Thank you for your time. ---- _2007-Nov-22 17:20:31 by anonymous:_ {linebreak} I have exactly the same problem here (win XP, vc6 SP2) when I link against my sqlite static or dynamic library in release. I have also used boundschecker to check sqlite, and it detects many dangling pointers ! But the strange thing is that I cannot find why these pointers are dangling, here an example: In prepare.c@188 pTab = sqlite3FindTable(db, zMasterName, db->aDb[iDb].zName); Boundchecker say that zMasterName is a dangling pointer, previously released here: in build.c@711: void sqlite3StartTable( Parse *pParse, /* Parser context */ Token *pName1, /* First part of the name of the table or view */ Token *pName2, /* Second part of the name of the table or view */ int isTemp, /* True if this is a TEMP table */ int isView, /* True if this is a VIEW */ int isVirtual, /* True if this is a VIRTUAL table */ int noErr /* Do nothing if table already exists */ ){ } It does not make sens for me, maybe it a false positive from boundchecker, but it is weird. I don't know if these "errors" are related to the "blob" bug in release mode. I will try to debug these error with some "printf" in release mode. Note: The provided dll (the one from the sqlite site) does not have this "bug". ---- _2007-Nov-22 18:27:35 by anonymous:_ {linebreak} More info: It seems that there is a bug in the VC6 (SP6) compiler. In btree.c, line 3056: if( offset+amt > nKey+pCur->info.nData ){ /* Trying to read or write past the end of the data is an error */ return SQLITE_ERROR; } After adding some printf around, It seems that the "speed optimization" compilation flag of VC6 changes the code order in a way that the offset variable is miss incremented !! Two remarks: *: I've traced the calling function, sqlite3BtreeData, and the it call accessPayload with the good offset value *: VC6 produces an internal error: "fatal error C1001: INTERNAL COMPILER ERROR" in the accessPayload function, if I try to access the offset value before this line: aPayload = pCur->info.pCell + pCur->info.nHeader; A dirty workaround could be to change the code order or the local var usage. I'm trying .... #f2dcdc 2766 code active 2007 Nov drh 2007 Nov 1 1 TCL transaction started from within a query does not commit This is a problem with the TCL interface. Consider the following TCL script: file delete -force test.db test.db-journal sqlite3 db test.db db eval { CREATE TABLE t1(x,y); INSERT INTO t1 VALUES(1,2); CREATE TABLE t2(a,b); INSERT INTO t2 VALUES(8,9); } db eval {SELECT * FROM t1} { db transaction { db eval {UPDATE t2 SET a=a*2} } } The [db transaction] statement starts a transaction and it is suppose to commit the tranaction at the end of the code block. But because the transaction started while a query was active, the tranaction is unable to commit. The TCL interface never commits the tranaction nor does it give any kind of error indication. It is unclear if an error should be returned or if the commit should be deferred until outer query finishes. If the code within the [db transaction] block throws an error, we really need the transaction to rollback right away. Perhaps there should be a new API that cancels all pending queries. Perhaps a call to sqlite3_interrupt() would suffice for this. Need to investigate further.... #e8e8bd 2756 new active 2007 Nov anonymous 2007 Nov 1 1 allow vacuum to change pragma setting instead of using existing ones we've got databases created with page_size of 1k, and we'd like to change that setting to 4k. vacuum creates a temporary db, attach it to the current connection, creates the tables (based on what's in the old db), and then selects from the old db and inserts into the new one. vacuum does exactly what we need (creating a new db from an old one), but it re-uses the existing pragmas for page size, auto vacuum and reserved page size. from sqlite.c, see sqlite3RunVacuum() sqlite3BtreeSetPageSize(pTemp, sqlite3BtreeGetPageSize(pMain), sqlite3BtreeGetReserve(pMain)); dr hipp points out that the the operands to vacuum are unused. from sqlite.c: sqlite3VdbeAddOp(v, OP_Vacuum, 0, 0); one solution would be to allow the user to specify the page size, reserve page size, and autovacuum as optional params to vacuum. he had an idea of using the signedness of the first operand to represent the autovacuum setting (since after a table is created, you can change the setting from auto to incremental, but you can't change it from none to auto (or none to incremental) #f2dcdc 2715 code active 2007 Oct anonymous 2007 Oct 1 1 no authorization needed to remove authorizer there should be a new auth code created and the auth function should be consulted for permission for removal. _2007-Oct-10 01:08:48 by drh:_ {linebreak} I'm assuming that this feature request comes from {quote: RockShox} and that the development language is Tcl. No. If your adversary has the ability to invoke the interface that removes an authorizer, then you system is already pwned. What you really need is the ability to [interp alias] the eval method into a safe interpreter. That way you can: *: Open the database in the main interpreter *: Set up the authorizer in the main interpreter to invoke a script in the main interpreter *: Set up the [interp alias] so that the safe interpreter can do [db eval ...] but not [db auth ...] It seems like an "-interp" option on the "eval" method of the database connection object would likely be the right interface. Or perhaps there should be separate "safeeval" method. Either way, it has been years and years since I have done anything with safe interpreters so I will have to look into what needs to be done to make that happen. ---- _2007-Oct-17 20:11:23 by anonymous:_ {linebreak} ok i think i agree with that. currently you cannot use an interp alias since the target command runs in the target interp and all your variables and commands are in the wrong scope. this means one needs to load sqlite again in the new interp, and sqlite will not load in a safe interp so a regular interp is required. to be useful, a -interp flag would need to execute in the current scope of the interp and not the global scope. #e8e8bd 2729 doc active 2007 Oct anonymous 2007 Oct 1 1 Lemon: %fallback, %wildcard, and @X uncodumented I noticed that the lemon documentation does not mention the %fallback and %wildcard directives. Both are in the code and are apparently doing useful work in SQLite's parse.y. Can other users benefit from them as well? The symbol @X is also undocumented. From a source code comment I read that it "If the argument is of the form @X then substituted the token number of X, not the value of X". A short documentation example would help to understand where and how it can be useful to apply this syntax. Are there other nice but undocumented Lemon goodies lacking documentation? #f2dcdc 2725 code active 2007 Oct anonymous 2007 Oct 1 1 memory leak in sqlite3_open_v2() when it fails only happens with flags = SQLITE_OPEN_READWRITE; and when res = sqlite3_open_v2(sourcename, &conn, flags, NULL); seems to leak 674 bytes per call _2007-Oct-15 07:07:07 by danielk1977:_ {linebreak} Are you calling sqlite3_close(conn) after the error occurs? All calls to sqlite3_open_v2() need to be matched by a call to sqlite3_close(), even if an error occurs. #f2dcdc 2684 code active 2007 Oct anonymous 2007 Oct 1 1 Accessing sqlite from an NT service will lock the complete databse. Accessing sqlite from a NT service (application 1) will lock the complete database. Any other process trying to open an sqlite db (application 2) will get error "80004005 unable to lock database" If application 1 runs as normal application, started by local user, this problem doesnt occur and both applications can open the db. _2007-Oct-02 15:48:05 by anonymous:_ {linebreak} SQLite has no knowledge of Windows services. How do you propose to work around this Windows anachronism? ---- _2007-Oct-02 17:20:38 by anonymous:_ {linebreak} Suggesion: Try running the service in the same account as the other program that needs to access the database. Anachronism? Service is just another word for daemon. -knu- ---- _2007-Oct-02 17:33:56 by anonymous:_ {linebreak} Re: Anachronism, the OP suggested there was something fundamentally different about file access using a service. You've pointed out that it's just a file permissions issue. ---- _2007-Oct-05 14:45:07 by drh:_ {linebreak} Two points: 1: The error message "80004005 unable to lock database" is not generated by SQLite. There must be some middleware someplace that is producing this message. The problem might be in that middleware and not in SQLite. 2: None of the SQLite developers run windows. Consequently any fixes for this problem will need to come from the community. Please append patches to this ticket if you find a fix. Or close the ticket if you discover that the problem is outside of SQLite. #f2dcdc 2664 code active 2007 Sep danielk1977 2007 Sep 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 code active 2007 Sep drh 2007 Sep 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() #f2dcdc 2580 code active 2007 Aug anonymous 2007 Aug 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. #e8e8bd 2555 new active 2007 Aug anonymous 2007 Aug 1 1 FTS index without original text Is it possible to build FTS index without storing original text? I want to use fts index without features of snippets etc. I just want to find ID of the record not the content of indexed phrase. I suppose that the table myname_content stores this content. I have tried to update all columns of myname_content and set its values to “xyz” (without one column in which I store ID of the record). After this operation FTS search works good, but unfortunately the table isn’t smaller (I cant’t use vacuum on FTS tables). Is there any other way to have pure text indexes without source level changes? #f2dcdc 2545 code active 2007 Jul anonymous 2007 Jul 1 4 Group by returns table name with field name imaginate a table:
create table test (
id INTEGER PRIMARY KEY,
name varchar(50) not null,
age integer not null
);
Then: insert into test (name,age) values ('foo',22);
insert into test (name,age) values ('foo',23);
insert into test (name,age) values ('bar',22);
insert into test (name,age) values ('bar',35);
insert into test (name,age) values ('bar',72);
Now try this; sqlite> .headers on
sqlite> select test.name, test.age from test order by name;
name|age
bar|22
bar|35
bar|72
foo|22
foo|23
sqlite> select test.name, test.age from test group by name;
test.name|test.age
bar|72
foo|23
You see ? if i use "GROUP BY", the field name contains tablename. Because i use "SELECT test.name" and not "SELECT name". If i set an alias, i get alias, that's ok. The trouble appears to be very high on Copix (http://wwW.copix.org). We create some DAO (Data Access Objects) automatically. The "groupBy" method doesn't works with SQLite... Is this normal ? Mysql, PostgreSql, Oracle... doesn't need to create alias. _2007-Jul-31 15:54:52 by anonymous:_ {linebreak} There's probably 4 other tickets reporting this. I don't think it will get fixed. The workaround is to use aliases (AS "whatever") for the selected columns. ---- _2007-Jul-31 23:02:19 by anonymous:_ {linebreak} Ok, we have created a special support for SQLite. PS: I love this database :) Simple, nice, usefull, quick and easy Regards
#f2dcdc 2543 code active 2007 Jul anonymous 2007 Jul 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 2469 build active 2007 Jun anonymous 2007 Jul 1 1 test fails on Solaris I have a problem running the test suite on Solaris 9. Build was done using gcc 4.2.0. The build completes without error but many tests fail. I've created my own minimal test that exhibits the problem: set testdir [file dirname $argv0] source $testdir/tester.tcl db close file delete -force test.db test.db-journal sqlite db test.db do_test tdb-1 { execsql { PRAGMA auto_vacuum = 1; BEGIN; CREATE TABLE t1(a, b); } execsql { COMMIT; } } {} integrity_check tdb-2 finish_testWhen running this test I get the following output:
tdb-1... Ok tdb-2... Expected: [ok] Got: [{*** in database main *** List of tree roots: 2nd reference to page 1 Page 3 is never used}] Thread-specific data deallocated properly 1 errors out of 3 tests Failures on these tests: tdb-2This error happens on lots of, but not all, tests. I'm happy to do whatever is necessary to help debug this. Thanks, Tim. _2007-Jun-27 10:44:14 by anonymous:_ {linebreak} Further to this, it appears to be related to gcc 4.2.0. It works fine with gcc 3.4.6. ---- _2007-Jun-28 09:54:35 by anonymous:_ {linebreak} Further more, it doesn't appear to be specific to Solaris. The same problem occurs on Linux with gcc 4.2.0. So I guess the subject of this ticket should be changed to "build/test problems with gcc 4.2.0". This is probably a significant problem - the build completes find but the resultant code is broken. People may not notice this until it's too late. ---- _2007-Jun-28 12:24:05 by drh:_ {linebreak} I installed gcc 4.2.0 on my SuSE linux i686 desktop and built test harnesses under three different configurations: gcc420 -g -O0 -Wall -fstrict-aliasing gcc420 -g -O3 -Wall gcc420 -g -O3 -fstrict-aliasing -fomit-frame-pointer The first two configurations used separate source files. The third configuration was built using the amalgamation. I ran the "quick" test under all configurations. All tests ran to completion with no errors. ---- _2007-Jun-28 13:22:20 by anonymous:_ {linebreak} Two ideas: 1. Compile with gcc 4.2.0 using -O0 instead of -O2 and see what happens. Disable any other optimizations you may have. 2. Run truss with full read/write buffer display on the gcc 3.4.6 compiled testfixture running your simple test case and compare its output to the gcc 4.2.0 compiled test case. ---- _2007-Jul-01 19:00:40 by anonymous:_ {linebreak} I've done tests with optimisation, and this appears to tickle the problem. With no optimisation, -O, -O0, -O1 and -03 it works. With -O2 and -Os it's broken. I was compiling with -O2 when I submitted the initial report. Tim. ---- _2007-Jul-01 19:54:54 by drh:_ {linebreak} I can reproduce the problem now on Linux when compiling as follows: gcc420 -g -O2 -Wall ---- _2007-Jul-01 21:50:42 by drh:_ {linebreak} This appears to be a bug in GCC 4.3.0. A work-around is to compile with the -fno-tree-vrp option. GCC appears to miscompile a single loop within the logic that implements the integrity_check PRAGMA. The code that gets miscompiled is in the file vdbe.c (lines numbers added): 4308 for(j=0; j
RCS file: /sqlite/sqlite/src/vdbe.c,v retrieving revision 1.636 diff -u -3 -p -r1.636 vdbe.c --- src/vdbe.c 1 Jul 2007 21:18:40 -0000 1.636 +++ src/vdbe.c 21 Jul 2007 19:10:13 -0000 @@ -4306,7 +4306,8 @@ case OP_IntegrityCk: { pnErr = &p->aMem[j]; assert( (pnErr->flags & MEM_Int)!=0 ); for(j=0; ju.i; } aRoot[j] = 0; popStack(&pTos, nRoot);