bgcolor # Status Created By Subsys Due Date SCR Assigned Svr Pri Title _Description _Remarks
#e8e8bd 369 active 2003 Jun anonymous 3 2 Testsuite fails on btree-1.1.1 (Mac OS X, SQLite 2.8.4) On Mac OS X the testsuite fails: btree-1.1.1..../src/btree.c:2687: failed assertion `pPage->isInit' make: *** [test] Abort trap SQLite version: 2.8.4, OS Version: 10.2.6 Obtained same result. Mac OS X 10.2.6, Developer Tools Dec 2002, SQLite 2.8.5. ---- Shared libraries are busted on Macs. As far as I can tell, this appears to be Apple's fault. Until a workaround is devised, do not attempt to compile using shared libraries. Add the --disable-shared option to the configure script: ../sqlite/configure --disable-shared ---- On 2.8.5+, this shows up on 2689. Also, configure does not allow the use of --disable-shared (probably requries a fix in the configure scripts). On a G5 in 10.3.2, this error shows up as a Bus Error. Builds work fine otherwise. This issue may be related to the warnings received in src/test1.c thru src/test4.c and in src/tclsqlite.c regarding Tcl_SetVar, Tcl_GetInt, Tcl_GetBoolean, Tcl_GetIndexFromObj. All warnings are regarding promotion of arguments to pointers of invalid type. oso2k/Louis ---- _2004-Feb-11 22:57:17 by anonymous:_ {linebreak} I did some quick testing of 2.8.12 on the machines I have available to me. In general, there seems to be more warnings than I remember (I believe I was testing 2.8.9 from cvs before it went live).{linebreak} {linebreak} *:G3 700MHz/640MB iBook 10.2.8{linebreak} Same results as we last spoke. Fails make test at:{linebreak} btree-1.1.1{linebreak} {linebreak} *:Dual G4 800MHz/1.25GB 10.2.8{linebreak} Same results as we last spoke. Fails make test at:{linebreak} btree-1.1.1{linebreak} {linebreak} *:G5 1.6GHz/1.25GB 10.3.2{linebreak} Something really weird happens here. There is no longer a bus error. Right after make test gets past bigfile-1.1, the machine seems to enter an infinite loop or something.
#cfe8bd 608 active 2004 Feb anonymous 3 3 Problem with "pragma show_datatypes = on" and busy timeout When a busy timeout is set, pragma show_datatypes = on and SQLite sleeps some time on the lock, no datatypes are passed to the exec callback function. The attachment is an archive with a Makefile, a shell script and a program that reproduce the error. _2004-Feb-12 21:05:28 by anonymous:_ {linebreak} This problem breaks the auto-typing feature of PySQLite when a busy timeout is used.
#cfe8bd 627 active 2004 Feb anonymous 3 3 sqliteRunVacuum returning wrong code? The last 3 lines of sqliteRunVacuum, as of the version checked in on Feb 12 2004, are: if( rc==SQLITE_ABORT ) rc = SQLITE_ERROR; if( sVac.rc!=SQLITE_OK ) rc = sVac.rc; return sVac.rc; It seems suspicious to set a local variable, rc, that one is never going to use again. I suspect that the last line should be return rc; _2004-Feb-27 00:54:03 by anonymous:_ {linebreak} The fix by check-in 1271 still doesn't look right to me. If one of the execsql calls returns SQLITE_CANTOPEN (which I have seen happen), then rc will be SQLITE_CANTOPEN and sVac.rc will be 0, and sqliteRunVacuum will return 0.
#e8e8bd 923 active 2004 Sep anonymous Pending anonymous 3 2 Missing quotes in 2.8.15 .dump cause data loss when loading in sqlite3 When converting a database by means of the command: sqlite old.db .dump | sqlite3 new.db the content of char/varchar fields is dumped by sqlite without quotes (e.g. 00001) and then when reloaded by sqlite3 it looses the heading zeroes (i.e. becomes '1', which is a really different thing for an alphanumeric field). This could be solved by a new release (sqlite 2.8.16 ?) which add quotes to alphanumeric fields (as sqlite3 does), or by a filter script that adds the quotes to the sqlite2 .dump output (I used a quick and dirty perl script to fix my dump...). _2005-Jul-11 20:08:11 by anonymous:_ {linebreak} This does not appear to be solved in sqlite 2.8.16.
#cfe8bd 1063 active 2005 Jan anonymous Pending 1 3 Lemon bug: Strings in rule code should not be interpreted There are two related bugs in the lemon parser related to processing code snippets defined in rule actions. Here is a simple grammar that demonstrates the problem: %include { extern int line_number; extern const char *file_name; } result(r) ::= TOKEN(s). { printf("BAD: Got a token on line '%d'\n", line_number); printf("BAD: \tFile = '%s'\n", file_name); r = s; } The first bug is that the "%d" in the first printf is interpreted by the append_str function, when it shouldn't be, producing code that looks like: printf("BAD: Got a token on line '0d'\n", line_number); I believe that the solution is to have append_str() NOT do %d substitution when it is copying the code. The second bug is that the "s" in the "%s" format is being interpreted as a symbolic name, producing code that looks like: printf("BAD: \tFile = '%yymsp[0].minor.yy0'\n", file_name); I believe that the solution is to have translate_code() ignore symbolic names inside of quoted strings.
#cfe8bd 1078 active 2005 Jan anonymous Pending 2 3 Lemon destructor bugs that don't affect sqlite I found a few bugs Lemon's destructor handling code. I don't think that they affect sqlite, but the do affect other grammars. - The code that collapses cases for default destructors erroneously assumes that all symbols have the same type. - If a reduction rule doesn't have code, then the RHS symbols will not have their destructors called. - The default destructor shouldn't be called on the auto-generated "error" symbol - In the internal function "append_str", zero-length strings may be returned un-terminated. I have some proposed fixes that I'll try to attach to this ticket. _2005-Jan-14 13:33:52 by drh:_ {linebreak} Do you also have some test grammars? That would really be helpful. ---- _2005-Jan-14 17:14:15 by anonymous:_ {linebreak} Sure. Here is one grammar that will demonstrate the "Tokens leak when rule has no code" bug:
%token_type { char * } %token_destructor { printf("Deleting token '%s' at %x\n", $$, (int)$$); free($$); } result ::= nt. nt ::= FOO BAR.
Running the following code against the grammar should theoretically show 2 allocations and two destructions. It won't though, unless you modify the rule for nt to have an empty body, like: {linebreak} nt ::= FOO BAR. {} char *mkStr(const char *s) { printf("Allocating '%s' at 0x%x\n", s, (int)(s)); return strdup(s); } int main(int argc, char **argv) { void *parser = ParseAlloc(malloc); Parse(parser, FOO, mkStr("foo")); Parse(parser, BAR, mkStr("bar")); Parse(parser, 0, 0); ParseFree(parser, free); return 0; }
---- _2005-Jan-14 17:50:26 by anonymous:_ {linebreak} Here is another test grammar. This one demonstrates (a) default destructors being called on the 'error' symbol, and (b) problems with default destructors being called on the wrong symbol type. %token_type { char * } %token_destructor { delete [] $$; } %default_destructor { delete $$; } %type result { int } %destructor result { } result ::= fooStruct barStruct. { } %type fooStruct { Foo * } fooStruct(lhs) ::= FOO(f). { lhs = new Foo(f); } %type barStruct { Bar * } barStruct(lhs) ::= BAR(b). { lhs = new Bar(b); }
Here is the code generated by lemon (with comments added & removed for clarity): typedef union { ParseTOKENTYPE yy0; int yy4; Bar * yy5; Foo * yy7; int yy15; } YYMINORTYPE; static const char *const yyTokenName[] = { "$", "FOO", "BAR", "error", "result", "fooStruct", "barStruct", }; static void yy_destructor(YYCODETYPE yymajor, YYMINORTYPE *yypminor){ switch( yymajor ){ case 1: case 2: { delete [] (yypminor->yy0); } break; case 3: /* error */ case 5: /* fooStruct of type "Foo *" */ case 6: /* barStruct of type "Bar *" */ #line 3 "typeBug.y" { delete (yypminor->yy5); } /* Yikes! yy5 is a "Bar *" */ #line 308 "typeBug.c" break; case 4: #line 6 "typeBug.y" { } #line 313 "typeBug.c" break; default: break; /* If no destructor action specified: do nothing */ } }
#cfe8bd 1085 active 2005 Jan anonymous Pending 2 3 pragma full_column_names and short_column_names still broken the following statement : SELECT T1.*,D1.* FROM test T1,dt D1 WHERE T1.id=D1.id does not give "long" column names, even if full_column_names is ON. But, the following does: SELECT T1.ID,D1.NAME FROM test T1,dt D1 WHERE T1.id=D1.id in other words, tablename prefix is applied only to explicit columns, not to "*" selected columns.
#cfe8bd 1100 active 2005 Feb anonymous Pending 3 3 make test segfaults at capi2-7.12 on amd64 system System is Gentoo Linux 2004.1 on Opteron processor; gcc v3.3.3, creating 64 bit binaries. Here is a traceback: capi2-7.11... Ok capi2-7.11a... Ok capi2-7.12... Program received signal SIGSEGV, Segmentation fault. 0x0000002a95b25830 in strlen () from /lib/libc.so.6 (gdb) where #0 0x0000002a95b25830 in strlen () from /lib/libc.so.6 #1 0x000000000043991c in sqlite3VdbeList (p=0x5aab60) at src/vdbeaux.c:528 #2 0x0000000000438737 in sqlite3_step (pStmt=0x13000a6023d0064) at src/vdbeapi.c:207 #3 0x0000000000416a80 in test_step (clientData=0x13000a6023d0064, interp=0x55a450, objc=0, objv=0x4) at src/test1.c:2070 #4 0x0000002a956978fe in TclEvalObjvInternal () from /usr/lib/libtcl8.4.so #5 0x0000002a956ba181 in TclCompEvalObj () from /usr/lib/libtcl8.4.so #6 0x0000002a956b9648 in TclCompEvalObj () from /usr/lib/libtcl8.4.so #7 0x0000002a956e4f66 in TclObjInterpProc () from /usr/lib/libtcl8.4.so #8 0x0000002a956978fe in TclEvalObjvInternal () from /usr/lib/libtcl8.4.so #9 0x0000002a956982d8 in Tcl_EvalEx () from /usr/lib/libtcl8.4.so #10 0x0000002a95698097 in Tcl_EvalTokensStandard () from /usr/lib/libtcl8.4.so #11 0x0000002a95698273 in Tcl_EvalEx () from /usr/lib/libtcl8.4.so #12 0x0000002a95698767 in Tcl_EvalObjEx () from /usr/lib/libtcl8.4.so #13 0x0000002a956e4a93 in Tcl_UplevelObjCmd () from /usr/lib/libtcl8.4.so #14 0x0000002a956978fe in TclEvalObjvInternal () from /usr/lib/libtcl8.4.so #15 0x0000002a956ba181 in TclCompEvalObj () from /usr/lib/libtcl8.4.so #16 0x0000002a956b9648 in TclCompEvalObj () from /usr/lib/libtcl8.4.so #17 0x0000002a956e4f66 in TclObjInterpProc () from /usr/lib/libtcl8.4.so #18 0x0000002a956978fe in TclEvalObjvInternal () from /usr/lib/libtcl8.4.so #19 0x0000002a956982d8 in Tcl_EvalEx () from /usr/lib/libtcl8.4.so #20 0x0000002a95698767 in Tcl_EvalObjEx () from /usr/lib/libtcl8.4.so #21 0x0000002a956e4a93 in Tcl_UplevelObjCmd () from /usr/lib/libtcl8.4.so #22 0x0000002a956978fe in TclEvalObjvInternal () from /usr/lib/libtcl8.4.so #23 0x0000002a956ba181 in TclCompEvalObj () from /usr/lib/libtcl8.4.so #24 0x0000002a956b9648 in TclCompEvalObj () from /usr/lib/libtcl8.4.so #25 0x0000002a95698815 in Tcl_EvalObjEx () from /usr/lib/libtcl8.4.so #26 0x0000002a9569c486 in Tcl_CatchObjCmd () from /usr/lib/libtcl8.4.so #27 0x0000002a956978fe in TclEvalObjvInternal () from /usr/lib/libtcl8.4.so #28 0x0000002a956ba181 in TclCompEvalObj () from /usr/lib/libtcl8.4.so #29 0x0000002a956b9648 in TclCompEvalObj () from /usr/lib/libtcl8.4.so #30 0x0000002a95698815 in Tcl_EvalObjEx () from /usr/lib/libtcl8.4.so #31 0x0000002a9569ef7e in Tcl_IfObjCmd () from /usr/lib/libtcl8.4.so #32 0x0000002a956978fe in TclEvalObjvInternal () from /usr/lib/libtcl8.4.so #33 0x0000002a956ba181 in TclCompEvalObj () from /usr/lib/libtcl8.4.so #34 0x0000002a956b9648 in TclCompEvalObj () from /usr/lib/libtcl8.4.so #35 0x0000002a956e4f66 in TclObjInterpProc () from /usr/lib/libtcl8.4.so #36 0x0000002a956978fe in TclEvalObjvInternal () from /usr/lib/libtcl8.4.so #37 0x0000002a956982d8 in Tcl_EvalEx () from /usr/lib/libtcl8.4.so #38 0x0000002a956d1e41 in Tcl_FSEvalFile () from /usr/lib/libtcl8.4.so #39 0x0000002a956d120e in Tcl_EvalFile () from /usr/lib/libtcl8.4.so #40 0x0000000000424641 in main (argc=2, argv=0x7fbffff138) at src/tclsqlite.c:1744 (gdb) _2005-Feb-04 23:11:02 by anonymous:_ {linebreak} Some more information from a gdb session (I added the printf at line 527 and recompiled; the line numbers below will be off by one from the stack trace above): (gdb) up #1 0x000000000043996d in sqlite3VdbeList (p=0x5aab60) at src/vdbeaux.c:529 529 pMem->n = strlen(pMem->z); (gdb) list 525,535 525 526 pMem->flags = MEM_Static|MEM_Str|MEM_Term; 527 printf("src/vdbeaux.c sqlite3VdbeList pOp->opcode= %d\n", pOp->opcode); 528 pMem->z = sqlite3OpcodeNames[pOp->opcode]; /* Opcode */ 529 pMem->n = strlen(pMem->z); 530 pMem->type = SQLITE_TEXT; 531 pMem->enc = SQLITE_UTF8; 532 pMem++; 533 534 pMem->flags = MEM_Int; 535 pMem->i = pOp->p1; /* P1 */ (gdb) p pOp->opcode $1 = 40 '(' (gdb) p pMem->z $2 = 0x175002100200173
#cfe8bd 1134 active 2005 Feb anonymous Pending drh 3 3 select command with a view and a condition gets no result In a view with more than one tables it is no longer possible to use that view within a select command; the columns of the view are not found.
#f2dcdc 1149 active 2005 Feb anonymous Pending 2 1 VACUUM, DUMP/RESTORE fail in certain cases When a database has interacting views, such as the following: CREATE VIEW test1 AS SELECT * FROM tableA; CREATE VIEW test AS SELECT COUNT(*) FROM test1;
then a dump and restore fails, because the views will be created in the alphabetical order of the table names, rather than the order of their dependence. Thus, the create of table test will fail. Because the script otherwise runs to completion, the restore will usually be adequate except that the views are not recreated. VACUUM appears to fail the same way, probably for the same reason. In this case, however, the VACUUM fails without clearly alerting the user. I ran into this problem trying to VACUUM a large file, which produced the otherwise inexplicable error message about the syntax of a dependent view definition. Users can workaround, once the problem is understood, either by renaming views so that their names have alphabetical sequence consistent with their dependency, or by dropping them prior to a vacuum or dump/restore and then recreating them later. Of course, the view order can be repaired in the dump file, but this usually requires patience with a powerful text editor, and some capacity to understand the problem.
#cfe8bd 1200 active 2005 Apr anonymous Pending 2 3 new versions of SQLite return different (incorrect) results. Newer versions of SQLite are returning different, and I believe incorrect, results compared to those returned by older versions. Using version 3.0.8 the following query returns the correct results given the attached database. sqlite> select * from device_property_list where device_property_value = 0; 1|Station|3|Initial Volume|0 1|Station|1|Template|0 2|Station|3|Initial Volume|0 2|Station|1|Template|0 3|Station|3|Initial Volume|0 3|Station|1|Template|0 This same query does not return any results using versions 3.1.6 and 3.2. If the query is changed slightly, by quoting the zero in the where condition, then both of the newer versions return the same set of results as 3.0.8. sqlite> select * from device_property_list where device_property_value = '0'; 1|Station|3|Initial Volume|0 1|Station|1|Template|0 2|Station|3|Initial Volume|0 2|Station|1|Template|0 3|Station|3|Initial Volume|0 3|Station|1|Template|0 The device_property_list is a view that hides a complex join of several tables, and a long case expression that select the value to return for device_property_value; the field that is being tested by the condition. I have attached a sample database and the sql script used to create it for testing.
#cfe8bd 1255 active 2005 May anonymous Pending 4 3 Decrease number of warnings with Microsoft Visual C++ Add this to sqliteInt.h to decrease the number of warnings produced by sqlite: #if defined(_MSC_VER) #pragma warning (disable: 4018) // signed/unsigned mismatch #pragma warning (disable: 4244) // conversion from 'unsigned __int64 ' to 'unsigned char ', possible loss of data #pragma warning (disable: 4761) // integral size mismatch in argument; conversion supplied #endif _2005-May-20 19:27:50 by drh:_ {linebreak} Is there no command-line option on microsoft to disable these warnings? ---- _2005-May-21 08:56:15 by anonymous:_ {linebreak} It's possible to lower the warning level, from say 3 to 2 using the command line. But this is not as selective and will remove more warnings than those #pragmas.
#e8e8bd 1278 active 2005 Jun anonymous Pending 1 2 sqlite3_finalize doesn't clear previous error code or message A call to sqlite3_finalize(), after an error during sqlite3_prepare() of another statement, returns the correct result SQLITE_OK, but does not reset the error code or error message returned by sqlite3_errcode() and sqlite3_errmsg(). The error reporting functions still return the error code and message associated with the error that occurred during the previous prepare. The attached code demonstrates the problem. One statement is prepared successfully. Then an second statement is prepared. This one fails and returns an error result. The correct error code and message are retrieved using the error reporting API functions. Next, the first statement is finalized, which returns SQLITE_OK. Calling the sqlite_errcode() function at this point still returns the error code from the previous error. I believe the error code and message should be cleared by the successful call to the sqlite3_finalize() API function.
#f2dcdc 1323 active 2005 Jul anonymous Pending 1 1 misuse-4.4...gmake: *** [test] Segmentation Fault (core dumped) misuse-4.4...gmake: *** [test] Segmentation Fault (core dumped) on both solaris 8 and 9 build env export CPPFLAGS="-I/tps/include" export LDFLAGS="-L/tps/lib -R/tps/lib" export PKG_CONFIG_PATH=/tps/lib/pkgconfig CC=/tps/bin/gcc CXX=/tps/bin/g++ LD_LIBRARY_PATH=/tps/lib:/tps/lib/sparcv9:/lib:/usr/lib:/usr/local/lib:\ /usr/ccs/lib:/usr/dt/lib:/usr/ucblib:/usr/openwin/lib PATH=/tps/bin:/tps/java/bin:/dsw/source/bin:/dsw/depot-5.13/bin:\ /usr/ccs/bin:/usr/bin:/usr/openwin/bin:/bin:/usr/local/bin:/sbin:\ /usr/sbin:/usr/ucb:/etc:.:/sfoc/bin:/usr/dt/bin:\ /dsw/source/harvest/bin:/usr/afsws/bin:/dsw/pgp-2.6.2s/bin export CC CXX LD_LIBRARY_PATH PATH where /tps is my version of /usr/local where I put all the configuration controlled open source and licensed s/w for my network. [525]$ ../configure --prefix=/dsw/sqlite-3.2.2 --with-tcl=/tps/lib gmake gmake test then got error gcc -v Reading specs from /dsw/gcc-3.4.0/lib/gcc/sparc-sun-solaris2.9/3.4.0/specs Configured with: ../configure --prefix=/dsw/gcc-3.4.0 --disable-nls --enable-languages=c,c++,f77,objc --disable-libgcj --srcdir=/export/build/gcc-3.4.0 --with-ld=/usr/ccs/bin/ld Thread model: posix gcc version 3.4.0 using ActiveTcl8.4.5.0
#f2dcdc 1342 active 2005 Jul anonymous Pending 1 1 sqlite 3.2.2 will not load on Suse Linux 9.3 when trying to load the sqlite 3.2.2 .so lib with tcl I get this problem: couldn't load file "/usr/lib/sqlite3/tclsqlite-3.2.2.so": /usr/lib/sqlite3/tclsqlite-3.2.2.so: undefined symbol: sqlite3_version Sqlite 3.2.1 does not give an error, with the same script
#cfe8bd 1365 active 2005 Aug anonymous Pending 3 3 64 bit types not completely overridable The current 64 bit types in sqlite3.h and sqliteInt.h do not allow the type to be overriden using a preprocessor definition, unlike all the other base types. The current 64 bit typedefs assume that a "long long" is 64 bits - this is not guaranteed (and on PS2 it is wrong, long long is 128 bits). Here are some minor patches that should allow these types to be overriden, but keep the old behavior if they are not: ==== //sqlite-3.2.2/src/sqlite3.h#1 - sqlite-3.2.2\src\sqlite3.h ==== 81,83c81,83 < #if defined(_MSC_VER) || defined(__BORLANDC__) < typedef __int64 sqlite_int64; < typedef unsigned __int64 sqlite_uint64; --- > #ifdef INT64_TYPE > typedef INT64_TYPE sqlite_int64; > typedef unsigned INT64_TYPE sqlite_uint64; 85,86c85,93 < typedef long long int sqlite_int64; < typedef unsigned long long int sqlite_uint64; --- > # if defined(_MSC_VER) || defined(__BORLANDC__) > typedef __int64 sqlite_int64; > typedef unsigned __int64 sqlite_uint64; > # else > typedef long long int sqlite_int64; > typedef unsigned long long int sqlite_uint64; > # endif > # define INT64_TYPE sqlite_int64 > # define UINT64_TYPE sqlite_uint64 ==== sqlite-3.2.2/src/sqliteInt.h#1 - sqlite-3.2.2\src\sqliteInt.h ==== 157,163d156 < #ifndef UINT64_TYPE < # if defined(_MSC_VER) || defined(__BORLANDC__) < # define UINT64_TYPE unsigned __int64 < # else < # define UINT64_TYPE unsigned long long int < # endif < #endif 183c176 < typedef UINT64_TYPE u64; /* 8-byte unsigned integer */ --- > typedef sqlite_uint64 u64; /* 8-byte unsigned integer */ _2005-Aug-27 16:45:09 by drh:_ {linebreak} Can someone suggest a suitable #ifdef that will automatically identify a PS and do the right thing to provide a 64-bit integer type, similar to what is down for windows?
#f2dcdc 1382 active 2005 Aug anonymous Pending drh 1 1 Assert nErr==0 on corrupt db I'm working on an embedded filesystem where files can be randomly altered. Sometimes my .db files get messed up. I've attached an example db. I'd like to catch the asserts and return an error rather than crash. $ sqlite corrupt-assert.db 'select count(*) from sensor' sqlite: src/main.c:120: sqliteInitCallback: Assertion `nErr==0' failed. Aborted
#cfe8bd 1445 active 2005 Sep anonymous Pending 3 3 Errors testing sqlite 3.2.6 (& v3.3.7) $ make test [...] conflict-6.0... Ok conflict-6.1... Ok conflict-6.2... Expected: [0 {7 6 9} 1 1] Got: [0 {7 6 9} 1 0] conflict-6.3... Expected: [0 {6 7 3 9} 1 1] Got: [0 {6 7 3 9} 1 0] conflict-6.4... Ok conflict-6.5... Ok conflict-6.6... Ok conflict-6.7... Expected: [0 {6 7 3 9} 1 1] Got: [0 {6 7 3 9} 1 0] conflict-6.8... Expected: [0 {7 6 9} 1 1] Got: [0 {7 6 9} 1 0] conflict-6.9... Expected: [0 {6 7 3 9} 1 1] Got: [0 {6 7 3 9} 1 0] conflict-6.10... Expected: [0 {7 6 9} 1 1] Got: [0 {7 6 9} 1 0] conflict-6.11... Expected: [0 {6 7 3 9} 1 1] Got: [0 {6 7 3 9} 1 0] conflict-6.12... Expected: [0 {6 7 3 9} 1 1] Got: [0 {6 7 3 9} 1 0] conflict-6.13... Expected: [0 {7 6 9} 1 1] Got: [0 {7 6 9} 1 0] conflict-6.14... Ok conflict-6.15... Ok conflict-6.16... Ok [...] date-3.12... Ok date-3.13... Ok date-3.14... Ok date-3.15... Ok date-3.16... Ok date-3.17... Ok /tmp/sqlite-3.2.6/.libs/lt-testfixture: invalid command name "clock" while executing "clock seconds" invoked from within "clock format [clock seconds] -format "%Y-%m-%d" -gmt 1" invoked from within "set now [clock format [clock seconds] -format "%Y-%m-%d" -gmt 1]" (file "./test/date.test" line 142) invoked from within "source $testfile" ("foreach" body line 4) invoked from within "foreach testfile [lsort -dictionary [glob $testdir/*.test]] { set tail [file tail $testfile] if {[lsearch -exact $EXCLUDE $tail]>=0} continue so..." (file "./test/quick.test" line 45) make: *** [test] Error 1 _2005-Sep-19 23:03:56 by drh:_ {linebreak} The test scripts do not (yet) work with Tcl 8.5. Use Tcl 8.4. ---- _2005-Sep-20 01:59:42 by anonymous:_ {linebreak} FYI, The conflict failures occur even when using tcl-8.4. The problem was reported on the mailing list: http://www.mail-archive.com/sqlite-users%40sqlite.org/msg10203.html Curiously, the failures correspond exactly to the test cases that were changed by the following patch: http://www.sqlite.org/cvstrac/filediff?f=sqlite/test/conflict.test&v1=1.24&v2=1.25 ---- _2006-Aug-31 23:49:40 by anonymous:_ {linebreak} building v337 on OSX 10.4.7 w/ TCL8.5 installed as Framework, 'make test' still fails w/: date-3.16... Ok date-3.17... Ok /usr/ports/sqlite-3.3.7/build/.libs/testfixture: invalid command name "clock" while executing "clock seconds" invoked from within "clock format [clock seconds] -format "%Y-%m-%d" -gmt 1" invoked from within "set now [clock format [clock seconds] -format "%Y-%m-%d" -gmt 1]" (file "../test/date.test" line 142) invoked from within "source $testfile" ("foreach" body line 4) invoked from within "foreach testfile [lsort -dictionary [glob $testdir/*.test]] { set tail [file tail $testfile] if {[lsearch -exact $EXCLUDE $tail]>=0} continue so..." (file "../test/quick.test" line 66) make: *** [test] Error 1 any resolution for this, other than revert to TCL 8.4? ---- _2006-Sep-01 01:26:37 by anonymous:_ {linebreak} SQLite under Cygwin fails all tests that involve integers larger than 32 bits. Sqlite produces the correct 64 bit values, but Tcl as distributed with Cygwin cannot grok 64 bit ints, so the comparisons fail. Would it be possible to change Sqlite's test harness to compare SQL results as strings rather than as integers? Then it would not matter if Tcl worked in 64 bit or not. ---- _2006-Sep-01 15:50:48 by drh:_ {linebreak} The test suite has been revised so that it now works with Tcl8.5. But, no, it is not practical to rewrite the tests to compare the results using strings instead of integers in order to work with the (broken) tcl implementation that comes with cygwin. ---- _2006-Sep-06 02:39:24 by anonymous:_ updating to latest cvs-checkout to get the aforementioned fix for: date-3.17... Ok /usr/ports/sqlite-3.3.7/build/.libs/testfixture: invalid command name "clock" while executing i can verify that _that_ is now ok: ... date-3.14... Ok date-3.15... Ok date-3.16... Ok date-3.17... Ok date-4.1... Expected: [2006-09-01] Got: [2006-09-06] date-5.1... Ok date-5.2... Ok date-5.3... Ok ... but now, 'make test' fails next @: delete-8.4... Ok delete-8.5... Ok delete-8.6... Ok delete-8.7... Ok /usr/ports/sqlite-cvs/build/.libs/testfixture: error deleting "test.db": not owner while executing "file delete -force test.db" (file "../test/tester.tcl" line 62) invoked from within "source $testdir/tester.tcl" (file "../test/delete2.test" line 36) invoked from within "source $testfile" ("foreach" body line 4) invoked from within "foreach testfile [lsort -dictionary [glob $testdir/*.test]] { set tail [file tail $testfile] if {[lsearch -exact $EXCLUDE $tail]>=0} continue so..." (file "../test/quick.test" line 66) make: *** [test] Error 1 ---- _2006-Sep-06 11:11:19 by drh:_ {linebreak} Run the build starting from an empty directory as a non-root user. ---- _2006-Sep-06 13:27:18 by anonymous:_ {linebreak} per INSTALL instructions, i did: cvs -d :pserver:anonymous@www.sqlite.org:/sqlite checkout -d sqlite-cvs sqlite cd /usr/ports/sqlite-cvs mkdir build cd build ../configure \ ... make chown -R myuser:wheel /usr/ports/sqlite-cvs sudo -u myuser make test and, as reported, the error was the result. ---- _2006-Sep-30 21:43:45 by anonymous:_ {linebreak} bump. anyone? ---- _2006-Sep-30 22:19:24 by anonymous:_ {linebreak} If you don't happen to be testing on Linux/gcc or Windows/VC++ I find that the Tcl test results have more than a few failures. It is not always easy to discern which failures are due to some odd quirk of Tcl or whether it is a legitimate SQLite issue on a given platform. Be prepared to change test scripts and tinker with the code.
#f2dcdc 1488 active 2005 Oct anonymous Pending 1 1 Collate Reverse does not exists? when I execute below SQL{linebreak} CREATE Unique INDEX index10 On Test2 ({linebreak} F1 Collate BINARY ,{linebreak} F2 Collate REVERSE DESC){linebreak} there is a error message:{linebreak} no such collation sequence: REVERSE {linebreak} but the latest document said that binary ,nocase , reverse is common collate function. what is wrong with my sql?
#e8e8bd 1489 active 2005 Oct anonymous Pending 3 2 Bad permissions on install-sh prevent 'make install' from completing It's a trivial problem - install-sh has incorrect permissions via CVS, resulting in 'make install' failing. Error: make installtclsh ../sqlite/tclinstaller.tcl 3.2../sqlite/install-sh -c -d /usr/local/libmake: execvp: ../sqlite/install-sh: Permission deniedmake: *** [install] Error 127 Permissions: -rw-r--r-- 1 cat other 5598 Sep 28 2001 ../sqlite/install-sh Fix: chmod 755 ../sqlite/install-sh
#f2dcdc 1546 active 2005 Nov anonymous Pending 1 1 Creating unique index on non-unique column leads to corr. on SQLite2 Like ticket #1115, SQLite version 2 suffer too from the bug where : BEGIN; CREATE TABLE t1(a); INSERT INTO t1 VALUES(1); INSERT INTO t1 VALUES(1); CREATE UNIQUE INDEX i1 ON t1(a); COMMIT; PRAGMA integrity_check fails. When "CREATE UNIQUE INDEX" fails within a transaction, (and within a transaction only), the index is still created.
#c8c8c8 1598 review 2006 Jan anonymous Pending 3 2 Incorrect case-insensitive comparison of non-latin UTF-8 characters Sqlite incorrectly compares case-insensitivly UTF-8 non-latin characters. I created a patch that fixes this problem and posted it to the mailing list. I wonder if someone could review my patch and eventually include it in the main project. Regards Stanislav Nikolov _2006-Jan-10 22:49:08 by drh:_ {linebreak} The sqlite3_create_collation() and sqlite3_create_function() APIs exists for the purpose of allowing users to define comparisons and any other operations in any way they see fit. There is no need to make changes to the SQLite core to accomodate cyrillic comparisons. Indeed, there are good reasons not to, namely if we correctly compare cyrillic, we should also need to correctly compare chinese, japanese, and korean to name but a few. Very quickly the comparison functions can grow to be many many times larger than the rest of SQLite. We conclude, therefore, that this is all best left to the discretion of the programmer who uses SQLite in their project. Hence we provide the afore mentioned sqlite3_create_collation() and sqlite3_create_function() APIs. ---- _2006-Jan-11 00:24:50 by anonymous:_ {linebreak} Okay, let me try that again. First, the patch I created does not correspond only to Cyrillic letters, but also to the Greek and the accented characters up to U+044F. According to UNICODE.ORG there are actually five alphabets in the world (of which one does not use cases anymore) that have different cases: (from http://www.unicode.org/reports/tr21/tr21-5.html#Introduction) Case is a normative property of characters in specific alphabets (Latin, Greek, Cyrillic, Armenian, and archaic Georgian) whereby characters are considered to be variants of a single letter. These variants, which may differ markedly in shape and size, are called the uppercase letter (also known as capital or majuscule) and the lowercase letter (also known as small or minuscule). The uppercase letter is generally larger than the lowercase letter. Alphabets with case differences are called bicameral; those without are called unicameral. Therefore, I don't think someone will need support for case-insensitive comparison for Japanese, Chinese or Korean characters and I guess that adding support for the remaining Armenian alphabet is a matter of minutes and will not add up to the complexity of the code. Of course, perhaps it is possible for every project and/or developer to design their own "collation schemes" but I don't find it very practical. I can't really see the reason behind rejecting the patch. Perhaps you could actually look at it ? Regards, Stanislav Nikolov ---- _2006-Jan-11 00:33:19 by drh:_ {linebreak} Please attach the patch to this ticket. ---- _2006-Jan-11 00:52:38 by drh:_ {linebreak} OK, I was able to reconstruct the patch from the mailing list. I observer that as written it increases the size of the SQLite library by a little over 4KiB. That might not seem like much, but embedded device manufacturers (that is to say, most of my paying customers) are _very_ sensitive to this kind of library size growth. I will look into reducing the size somewhat and getting it into a future release as a compile-time option. ---- _2006-Jan-11 03:10:09 by drh:_ {linebreak} Based on what I can glean from http://www.unicode.org/Public/UNIDATA/CaseFolding.txt, the case folding table in the patch seems to be incomplete. A full unicode case folding table would need to be much larger. Perhaps somebody with more experience in unicode case folding can comment. ---- _2006-Jan-11 09:52:38 by anonymous:_ {linebreak} I think that the size of the library could be effectivly shrunk if we don't use an array, because over 50% of the information is redundant. I think that the same effect could be achieved by changing sqlite3UpperToLower[] to a function, and in there to check for the ranges of the capital letters (that is, we need to check for 4-5 different regions and return x+20 ort x+1 for capital letters for example). I can try do that? ---- _2006-Jun-08 10:32:06 by anonymous:_ {linebreak} Has anybody worked on this lately? This is quite an issue if you happen to use non-latin chars. Keep up the good work. Anze ---- _2006-Oct-11 10:58:15 by anonymous:_ {linebreak} could you tell me how to run the patch for WindowXP? Thanks a lot.
#f2dcdc 1622 active 2006 Jan danielk1977 Pending 1 1 Compiling with OMIT_PRAGMA causes an error in the test suite Compiling with OMIT_PRAGMA causes an error in the test suite. The error is a Tcl level error thrown by a [db eval] command when it encounters the unknown SQL keyword "PRAGMA".
#cfe8bd 1822 active 2006 May anonymous Duplicate 3 3 Table Alias together with Subquery seems not to work proper SELECT * FROM auth AS a LEFT JOIN (SELECT tm.team FROM teammbs AS tm) AS tr ON a.ateam=tr.team; Error message: No such colum tr.team But if I run the sub-query itself, it works fine. Of course, this example can be expressed different, so no subquery required. But the complete expression looks like this: SELECT a.auth, a.avalue FROM auth a LEFT JOIN (SELECT tm.member, tm.team FROM teammbs tm, team t WHERE tm.team=t.teamid AND (t._state<64 or (t._state>120 AND t._state<192)) AND (tm._state<64 or (tm._state>120 AND tm._state<192))) AS tr ON a.ateam=tr.team WHERE (a._state<64 or (a._state>120 AND a._state<192)) AND (a.auser='test' OR tr.member='test') ORDER BY a.auth; It works fine with MySQL 5, and brings the same error on SQLite 3: No such column tr.team. Any idea?
#cfe8bd 1856 active 2006 Jun anonymous Pending 2 3 SQLITE_OMIT_UTF16 breaks 'make test' When compiling sqlite 3.3.6 with -DSQLITE_OMIT_UTF16 and you say 'make test' it fails: make test ./libtool --mode=link gcc -g -O2 -DOS_UNIX=1 -DHAVE_USLEEP=1 -DHAVE_FDATASYNC=1 -I. -I./src -DSQLITE_DEBUG=2 -DSQLITE_MEMDEBUG=2 -DSQLITE_OMIT_UTF16 -I/usr/include -DTHREADSAFE=1 -DSQLITE_THREAD_OVERRIDE_LOCK=-1 -DSQLITE_OMIT_CURSOR -DTCLSH=1 -DSQLITE_TEST=1 -DSQLITE_CRASH_TEST=1 \ -DTEMP_STORE=1 -o testfixture ./src/btree.c ./src/date.c ./src/func.c ./src/os.c ./src/os_unix.c ./src/os_win.c ./src/os_os2.c ./src/pager.c ./src/pragma.c ./src/printf.c ./src/test1.c ./src/test2.c ./src/test3.c ./src/test4.c ./src/test5.c ./src/test6.c ./src/test7.c ./src/test_async.c ./src/test_md5.c ./src/test_server.c ./src/utf.c ./src/util.c ./src/vdbe.c ./src/where.c ./src/tclsqlite.c \ libsqlite3.la -L/usr/lib -ltcl8.4 -ldl -lpthread -lieee -lm gcc -g -O2 -DOS_UNIX=1 -DHAVE_USLEEP=1 -DHAVE_FDATASYNC=1 -I. -I./src -DSQLITE_DEBUG=2 -DSQLITE_MEMDEBUG=2 -DSQLITE_OMIT_UTF16 -I/usr/include -DTHREADSAFE=1 -DSQLITE_THREAD_OVERRIDE_LOCK=-1 -DSQLITE_OMIT_CURSOR -DTCLSH=1 -DSQLITE_TEST=1 -DSQLITE_CRASH_TEST=1 -DTEMP_STORE=1 -o .libs/testfixture ./src/btree.c ./src/date.c ./src/func.c ./src/os.c ./src/os_unix.c ./src/os_win.c ./src/os_os2.c ./src/pager.c ./src/pragma.c ./src/printf.c ./src/test1.c ./src/test2.c ./src/test3.c ./src/test4.c ./src/test5.c ./src/test6.c ./src/test7.c ./src/test_async.c ./src/test_md5.c ./src/test_server.c ./src/utf.c ./src/util.c ./src/vdbe.c ./src/where.c ./src/tclsqlite.c ./.libs/libsqlite3.so -L/usr/lib -ltcl8.4 -ldl -lpthread -lieee -lm -Wl,--rpath -Wl,/home/cla/proj/caissadb/sqlite/sqlite/lib ./src/test1.c: In function 'Sqlitetest1_Init': ./src/test1.c:3742: error: 'unaligned_string_counter' undeclared (first use in this function) ./src/test1.c:3742: error: (Each undeclared identifier is reported only once ./src/test1.c:3742: error: for each function it appears in.) make: *** [testfixture] Error 1 Maybe there is a '#ifndef SQLITE_OMIT_UTF16' / '#endif' needed around Tcl_LinkVar(interp, "unaligned_string_counter", (char*)&unaligned_string_counter, TCL_LINK_INT); in Line 3742 in file src/test1.c? Regards.
#cfe8bd 1872 active 2006 Jun anonymous Pending 4 3 sqlite3_open doesn't support RFC1738 format for filename sqlite3_open only supports UTF-8 encoding as a format for its filename argument (http://www.sqlite.org/capi3ref.html#sqlite3_open). If your application receives a RFC1738 encoded URL for filename, that has to be UTF-8-encoded for use in SQLite. It would be nice if that could be instead passed directly to sqlite3_open. Is RFC1738 URL decoding support planned for SQLite? (RFC1738 link: http://www.cse.ohio-state.edu/cgi-bin/rfc/rfc1738.html)
#f2dcdc 1882 active 2006 Jul anonymous Pending 1 1 Wrong algorithm of SQLITE_VERSION_NUMBER calculation The sqlite3.h comment describing how numeric version number is calculated is as follows: "The SQLITE_VERSION_NUMBER is an integer with the value (X*100000 + Y*1000 + Z). For example, for version "3.1.1beta", SQLITE_VERSION_NUMBER is set to 3001001." But the value of SQLITE_VERSION_NUMBER is greater than the equation above suggests. The value X*100000 should be changed to X*1000000 (one milion).
#e8e8bd 1884 active 2006 Jul anonymous Pending 3 2 pragma table_info caches results from previous query this problem is observed with pysqlite's latest windows build 2.3.2 and others. it does not occur on unix-based builds, which is why I suspect the issue is in sqlite, since pysqlite's code is platform-neutral. if you get a result from a "pragma table_info()" call, and do not consume all the results, then a subsequent call to the same statement does not return up-to-date results, i.e. if the table had been dropped in between. it behaves as though the results of "pragma table_info" are globally cached somewhere, ignoring the fact that is was executed again. this test program illustrates the problem: from pysqlite2 import dbapi2 as sqlite connection = sqlite.connect(':memory:') # check for a nonexistent table c = connection.execute("pragma table_info(users)") row = c.fetchone() assert row is None # its good. # now create the table connection.execute(""" create table users ( foo VARCHAR(10), name VARCHAR(40) ) """) # do the table_info pragma. returns two rows c = connection.execute("pragma table_info(users)") # get the first row row = c.fetchone() print row # but then dont get the second, close out the cursor instead. #row2 = c.fetchone() # uncomment to fully consume both rows, then it works c.close() c = None # rollback too. connection.rollback() # now drop the table connection.execute("DROP TABLE users") print "dropped" # now it should be gone, right? well it is, but the pragma # call starts off with the former result set c = connection.execute("pragma table_info(users)") row = c.fetchone() print row assert row is None # fails.
#cfe8bd 1893 active 2006 Jul anonymous Pending 3 3 sqlite doesn't use indexes containing primary key in prim. key selects I have table: CREATE TABLE IF NOT EXISTS 'customers' ( 'rowid' INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, 'fname' CHAR(40) NOT NULL, 'sname' CHAR(40) NOT NULL, 'birthno' CHAR(11) NULL) And index: CREATE UNIQUE INDEX IF NOT EXISTS 'idx_customers_sname' ON 'customers' ( 'sname' ASC, 'fname' ASC, 'rowid' ASC ); Command SELECT * FROM customers ORDER BY sname ASC, fname ASC, rowid ASC; doesn't use created index. Command SELECT * FROM customers ORDER BY sname ASC, fname ASC; uses index idx_customers_sname. I think this is a bug, but maybe (i don't know), it is by desing. If I don't specify rowid in ORDER BY, is the resultset ordered by rowid anyway? _2006-Jul-24 16:02:29 by anonymous:_ {linebreak} In SQL single quotes are used around string literals, and double quotes are used around identifiers where required to enclose keywords and/or embedded spaces. In your case no quotes are required at all because your table and column identifiers are continuos (i.e. do not contain embedded spaces) non-keyword names. If you are going to include unnecessary quotes then you should at least use the correct ones. CREATE TABLE IF NOT EXISTS "customers" ( "rowid" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "fname" CHAR(40) NOT NULL, "sname" CHAR(40) NOT NULL, "birthno" CHAR(11) NULL); CREATE UNIQUE INDEX IF NOT EXISTS "idx_customers_sname" ON "customers" ( "sname" ASC, "fname" ASC, "rowid" ASC ); Aside from that, this does look like a bug. SQLite is doing an unnecessary sort for the first query, and correctly using the index for the second. I suspected that it might be related to handling of the special column name rowid, but it does the same thing if rowid is replaced with a more generic name like id as shown below. SQLite version 3.3.6 Enter ".help" for instructions sqlite> CREATE TABLE IF NOT EXISTS "customers" ( ...> "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, ...> "fname" CHAR(40) NOT NULL, ...> "sname" CHAR(40) NOT NULL, ...> "birthno" CHAR(11) NULL); sqlite> sqlite> CREATE UNIQUE INDEX IF NOT EXISTS "idx_customers_sname" ...> ON "customers" ( "sname" ASC, "fname" ASC, "id" ASC ); sqlite> sqlite> sqlite> explain query plan SELECT * FROM customers ORDER BY sname ASC, fname ASC , id ASC; 0|0|TABLE customers sqlite> explain query plan SELECT * FROM customers ORDER BY sname ASC, fname ASC ; 0|0|TABLE customers WITH INDEX idx_customers_sname ORDER BY sqlite> sqlite> .explain on sqlite> sqlite> explain SELECT * FROM customers ORDER BY sname ASC, fname ASC, id ASC; addr opcode p1 p2 p3 ---- -------------- ---------- ---------- --------------------------------- 0 OpenVirtual 1 5 keyinfo(3,BINARY,BINARY) 1 Goto 0 34 2 Integer 0 0 3 OpenRead 0 2 4 SetNumColumns 0 4 5 Rewind 0 19 6 Rowid 0 0 7 Column 0 1 8 Column 0 2 9 Column 0 3 10 MakeRecord 4 0 11 Column 0 2 12 Column 0 1 13 Rowid 0 0 14 Sequence 1 0 15 Pull 4 0 16 MakeRecord 5 0 17 IdxInsert 1 0 18 Next 0 6 19 Close 0 0 20 OpenPseudo 2 0 21 SetNumColumns 2 4 22 Sort 1 32 23 Integer 1 0 24 Column 1 4 25 Insert 2 0 26 Column 2 0 27 Column 2 1 28 Column 2 2 29 Column 2 3 30 Callback 4 0 31 Next 1 23 32 Close 2 0 33 Halt 0 0 34 Transaction 0 0 35 VerifyCookie 0 2 36 Goto 0 2 37 Noop 0 0 sqlite> explain SELECT * FROM customers ORDER BY sname ASC, fname ASC; addr opcode p1 p2 p3 ---- -------------- ---------- ---------- --------------------------------- 0 Noop 0 0 1 Goto 0 21 2 Integer 0 0 3 OpenRead 0 2 4 SetNumColumns 0 4 5 Integer 0 0 6 OpenRead 2 4 keyinfo(3,BINARY,BINARY) 7 Rewind 2 18 8 RowKey 2 0 9 IdxIsNull 0 17 10 IdxRowid 2 0 11 MoveGe 0 0 12 Rowid 0 0 13 Column 0 1 14 Column 0 2 15 Column 0 3 16 Callback 4 0 17 Next 2 8 18 Close 0 0 19 Close 2 0 20 Halt 0 0 21 Transaction 0 0 22 VerifyCookie 0 2 23 Goto 0 2 24 Noop 0 0 sqlite> ---- _2006-Aug-03 17:33:51 by anonymous:_ {linebreak} Thank you for clarification of single and double quotes usage. I will drop the quotes completely since using double quotes is little bit annoying inside C string literals... It seems that the problem with index and sorting on primary key is independent of primary key column name. In fact, previously I was using "id" :-) and the result was the same as you mentioned.
#f2dcdc 1941 active 2006 Aug anonymous Pending 1 1 Unrevolved _sqlite3ExprCodeAndCache with SQLITE_OMIT_TRIGGER If =SQLITE_OMIT_TRIGGER= is set, linker complains about an unresolved =_sqlite3ExprCodeAndCache= symbol. =sqlite3ExprCodeAndCache= is defined in =expr.c= and wrapped with =#ifndef SQLITE_OMIT_TRIGGER=. However, references in insert.c, line 536 update.c, line 348 and 362 are not wrapped with #ifndef =SQLITE_OMIT_TRIGGER=. I followed the suggestion quoted below (posted earlier to this list) without avail. Is it safe (or even required?) to change sqliteInt.h to #ifndef SQLITE_OMIT_TRIGGER void sqlite3ExprCodeAndCache(Parse*, Expr*); #else # define sqlite3ExprCodeAndCache(A,B) #endif In the mailing list, DRH argued that the above change will probably fail and suggested that a safer fix would be to remove the #ifndef SQLITE_OMIT_TRIGGER from around the sqlite3ExprCodeAndCache function. _2006-Oct-12 17:35:32 by anonymous:_ {linebreak} The problem is still present in 3.3.8. Removing the #ifndef SQLITE_OMIT_TRIGGER from around the sqlite3ExprCodeAndCache function seems to fix it. Could you commit this?
#e8e8bd 1960 active 2006 Sep anonymous Pending 4 2 Issues with .import in sqlite.exe I ran into two possible problems when using the .import operation in sqlite3: - .import seems to be confused by NULLs; in the file NullTest.dat the null is at the end of the line - .import chokes on empty field when importing to field of type: integer PRIMARY KEY AUTOINCREMENT For example line like: ~2~3~4~5~6 Example: Schema: --Table with autoincrement CREATE TABLE test1( id integer PRIMARY KEY AUTOINCREMENT, c1 integer NULL , c2 integer NULL , c3 text NULL, c4 text NULL, c5 text NULL ); -- Table with no autoincrement field CREATE TABLE test2( id integer NULL, c1 integer NULL , c2 integer NULL , c3 text NULL, c4 text NULL, c5 text NULL ); .separator ~ .import NullTest.dat test1 .import NullTest.dat test2 .import NoNullTest.dat test2 I have short test files that I can email to the person who is looking at this.
#f2dcdc 1980 active 2006 Sep drh Pending 1 1 Initializing FTS1 twice causes it to fail. If you try to load the shared module twice, it causes the module to no longer work.
#e8e8bd 1983 active 2006 Sep anonymous Pending 2 2 I/O Error at a size of 4GB and auto_vacuum=1 when i'm building a database with auto_vacuum=1 and page_size=8192, i get an I/O error at a size of about 4GB. All tables are still readable but then it isn't possible to insert any more data. The table is filled with a column of BLOBs and some columns with numbers. I use the 3.3.7 binary with Windows 2000 Server.
#f2dcdc 1990 active 2006 Sep anonymous Pending 1 1 sqlite3_close doesn't release always the file handle I *think* that sqlite3_close behave strangly. I use version 3.3.7 on Linux (Fedora Core 5). What I do is to open a database, and start a transaction in it. Then, without ending the transaction, open again the database and simply close it. I found out, that the inner sqlite3_close return 0 (SQLITE_OK), but the file handle is not released. So if I do it too many times, I run out of file handles. You are free to ask why I open and close that many times the same database while it is already in transaction. This is my mistake. Actually, it is already fixed. But I still wonder - shouldn't the sqlite3_close return other thing then just SQLITE_OK? Especially if the file handle is not released? If it did, I would find my mistake much earlier. Here is my script that demonstrate it (you can use /usr/sbin/lsof in linux to see how many times the file is opened): #include int main(int argc, char **argv) { sqlite3* db; sqlite3* db_inner; int rc; int i; system("rm -f open_many_test.db"); rc = sqlite3_open("open_many_test.db", &db); sqlite3_exec(db, "begin", 0, 0, 0); sqlite3_stmt *pStmt; rc = sqlite3_prepare(db, "create table a (id varchar)", -1, &pStmt, 0); rc = sqlite3_step(pStmt); sqlite3_finalize(pStmt); rc = sqlite3_prepare(db, "insert into a values('bla')", -1, &pStmt, 0); rc = sqlite3_step(pStmt); sqlite3_finalize(pStmt); for (i = 0; i < 10000; i++) { rc = sqlite3_open("open_many_test.db", &db_inner); printf("sqlite3_open gives %d\n", rc); rc = sqlite3_close(db_inner); printf("sqlite3_close gives %d\n", rc); } sqlite3_exec(db, "commit", 0, 0, 0); rc = sqlite3_close(db); } _2006-Sep-23 15:29:46 by drh:_ {linebreak} This behavior is intentional. It is there to work around bugs in the design of posix advistory locks. See ticket #561 and check-in [1171]. Under posix, if you have the same file open multiple times and you close one of the file descriptors, all locks on that file for all file descriptors are cleared. To prevent this from occurring, SQLite defers closing file descriptors until all locks on the file have been released. One possible work-around would be to reuse file descriptors that waiting to be closed for the next open, rather than creating a new file descriptor. ---- _2006-Sep-23 15:35:21 by anonymous:_ {linebreak} The inner call should to sqlite3_open() should simply fail in that case, rather than set up a condition where by a file descriptor is leaked (which no one wants). This is unfortunate because sqlite3_open()'s behavior would not be uniform across platforms. ---- _2006-Sep-23 16:43:32 by anonymous:_ {linebreak} SQLite should do a lookup via stat()'s st_dev/st_ino fields prior to open() and if found to be the same as an already opened database file, it should use the same (refcounted) file descriptor, eliminating the need for open() in this case. ...upon reflection, having two sqlite connections using the same file descriptor would be a bad thing. stat() could be used to decide if a fd pending close() is recyclable, though. ---- _2006-Sep-23 18:17:34 by drh:_ {linebreak} Two points: 1: SQLite does not and has never leaked file descriptors. All file descriptors are eventually closed. The close is merely deferred until the pending transaction COMMITs. 2: I will be taking a very caution and careful approach toward resolving this issue. The issue itself is minor (it has only just now been reported but the behavior has been there for 3 years) but the consequences of getting the fix wrong are severe (database corruption.) And there are abundant opportunities for getting the fix wrong.
#f2dcdc 1992 active 2006 Sep anonymous Fixed shess 1 1 FTS1: Problems after dropping utility tables There are problems if FTS1 utilities tables are dropped from a database. See following SQL for details. drop table if exists x; -- Create a FTS1 table. create virtual table x using fts1 ('content'); -- Drop table x_content: Works fine, but should this be allowed? -- The same errors below also show if table x_term is dropped. drop table x_content; -- All attempts to access table x now result in errors, -- including dropping table x. There seems to be no way out -- except of recreating the database. All three commands below -- cause the same error, regardless if executed in sequence -- or individually: insert into x (content) values ('one two three'); -- Error! delete from x; -- Error! drop table x; -- Error! Added "not exists" to allow dropping an fts table with corrupted backing. Allowing updates to such tables is unlikely to happen (not even clear what it would mean, in most cases!).
#cfe8bd 2010 active 2006 Oct anonymous Fixed_in_3.0 3 3 Timeout ignored in Shared-Cache locking model With shared cache enabled, the busy timeout seems to be ignored. SQLITE_BUSY comes immediately. This occurs at least for locking situations within one shared cache. My server (if i may call the cache sharing thread that way) has its own timeout handling. But I thought that a small timeout in sqlite3 might help to distinguish locks from deadlocks. This was reproduced with both Python wrappers. These just call sqlite3_enable_shared_cache and sqlite3_busy_timeout and then execute BEGIN IMMEDIATE from two connections. _2006-Oct-06 13:56:21 by anonymous:_ {linebreak} Weird, I thought it's my fault, but I see exactly the same behaviour with the C# ADO.NET 2.0 wrapper w/ the shared cache patch.
#e8e8bd 2011 active 2006 Oct anonymous New 3 2 Escaping Porblem with .mode insert (double apostrophe) select * from messages where message_id="74B23AAF-5FFD6BF2"; 74B23AAF-5FFD6BF2|75|0|0|0|0|Europe talks, acts tough on Iran||http://www.ncr-iran.org/index.php?option=com_content&task=view&id=1052&Itemid=71|1140529235.0|By Gareth HardingThe United Press International, BRUSSELS -- Europeans are supposed to prefer soft to hard power, jaw-jaw to war-war and appeasement to confrontation. In short, in the words of neo-conservative scholar Robert Kagan: \'Americans are from Mars; Europeans are from Venus.\' The ".mode insert / .output" file looks like this. INSERT INTO messages VALUES('74B23AAF-5FFD6BF2',75,0,0,0,0,'Europe talks, acts tough on Iran','','http://www.ncr-iran.org/index.php?option=com_content&task=view&id=1052&Itemid=71',1140529235.0,'By Gareth HardingThe United Press International, BRUSSELS -- Europeans are supposed to prefer soft to hard power, jaw-jaw to war-war and appeasement to confrontation. In short, in the words of neo-conservative scholar Robert Kagan: \''Americans are from Mars; Europeans are from Venus.\'''); Now there are two apostrophe and the Escaping is broken.
#cfe8bd 2012 active 2006 Oct anonymous New 4 3 trigger4.test aborts "make test" on Windows The failure to remove these files causes "make test" to abort without completing remaining tests: trigger4-99.9... Ok ./testfixture: error deleting "trigtest.db": permission denied while executing "file delete -force trigtest.db trigtest.db-journal" (file "test/trigger4.test" line 199) fix: Index: test/trigger4.test =================================================================== RCS file: /sqlite/sqlite/test/trigger4.test,v retrieving revision 1.9 diff -u -3 -p -r1.9 trigger4.test --- test/trigger4.test 4 Oct 2006 11:55:50 -0000 1.9 +++ test/trigger4.test 9 Oct 2006 14:09:07 -0000 @@ -195,6 +195,6 @@ do_test trigger4-7.2 { integrity_check trigger4-99.9 -file delete -force trigtest.db trigtest.db-journal +catch {file delete -force trigtest.db trigtest.db-journal} finish_test Not sure why this ticket was set to Fixed_in_3.0, but I can reproduce the "make test" abort on Windows. ---- _2006-Oct-11 00:27:16 by drh:_ {linebreak} I do not know why the resolution was set to "Fixed_In_3.0" either. It seems to have been set that why by the original submitter. I will fix this eventually, but since it does not represent a real malfunction, it has a lower priority.
#cfe8bd 2013 active 2006 Oct anonymous Pending drh 4 3 Autoincrement increments on failing INSERT OR IGNORE % package require sqlite3 3.3.8 % sqlite3 db "" % db eval "CREATE TABLE test (counter INTEGER PRIMARY KEY AUTOINCREMENT, value text NOT NULL UNIQUE)" % db eval "INSERT INTO test VALUES(4, 'hallo')" % db eval "SELECT * FROM sqlite_sequence" test 4 % db eval "INSERT OR IGNORE INTO test(value) VALUES('hallo')" % db eval "SELECT * FROM sqlite_sequence" test 5 ---> there has no dataset been inserted but the AUTOINCREMENT-counter is incremented % db eval "INSERT OR IGNORE INTO test VALUES(4, 'hallo')" % db eval "SELECT * FROM sqlite_sequence" test 5 ---> right behavior: no inserted dataset and no incrementation This maybe could be a problem if the "INSERT OR IGNORE" happens very often.
#cfe8bd 2014 active 2006 Oct anonymous Pending anonymous 4 3 Enhancement Req: CREATE [TEMP | TEMPORARY] VIRTUAL TABLE Regarding the experimental VIRTUAL TABLE implementation, I believe it would of benefit to provide a "temp", or volatile construct when working with them. -- From a SQL syntax perspective, adding an optional keyword "TEMP" to the declaration: CREATE [TEMP | TEMPORARY] VIRTUAL TABLE. -- From a code perspective, I would envision this to invoke xCreate as it does now, but when the database is closed, the table is automatically dropped like any temp table, and xDestroy invoked rather than xDisconnect. One sticky point I can picture is behavior when multiple opens exist to a single database from the same process space. Since virtual tables are already reference counted (in SQLite 3.3.8), perhaps the reference count could be made to span database handles and be bubbled up to the process level instead. That would allow the table to be CREATEd on one handle, CONNECTed on a second handle, then DISCONNECTed/DESTROYed based on the process-wide reference count. I feel that there are numerous implementation possibilities for this. Having no option to auto-drop a virtual table can lead to stray module references, creating SQLite database files that cannot be properly utilized if the vtable module is not available. Of course this can be implemented by the application calling DROP TABLE on it's own, but an embedded solution that takes care of it seems more 'proper' given the thought that goes into SQLite as a whole.
#f2dcdc 2017 active 2006 Oct anonymous Pending 1 1 DROP TABLE fails on FTS1 utility tables with certain OMIT_s defined The following SQL fails when SQLite is compiled with the SQLITE_OMIT_ defines stated below: create virtual table foo using fts1 (content); drop table foo; create virtual table foo using fts1 (content); Cause: The foo_content and foo_term tables are not deleted. To verify, please define these SQLITE_OMIT_s: OPTS += -DSQLITE_OMIT_ALTERTABLE OPTS += -DSQLITE_OMIT_ANALYZE OPTS += -DSQLITE_OMIT_AUTHORIZATION OPTS += -DSQLITE_OMIT_AUTOINCREMENT OPTS += -DSQLITE_OMIT_AUTOVACUUM OPTS += -DSQLITE_OMIT_BETWEEN_OPTIMIZATION OPTS += -DSQLITE_OMIT_BLOB_LITERAL OPTS += -DSQLITE_OMIT_CAST OPTS += -DSQLITE_OMIT_CHECK OPTS += -DSQLITE_OMIT_COMPLETE OPTS += -DSQLITE_OMIT_COMPOUND_SELECT OPTS += -DSQLITE_OMIT_EXPLAIN OPTS += -DSQLITE_OMIT_FLAG_PRAGMAS OPTS += -DSQLITE_OMIT_FOREIGN_KEY OPTS += -DSQLITE_OMIT_GET_TABLE OPTS += -DSQLITE_OMIT_GLOBALRECOVER OPTS += -DSQLITE_OMIT_INTEGRITY_CHECK OPTS += -DSQLITE_OMIT_LIKE_OPTIMIZATION OPTS += -DSQLITE_OMIT_MEMORYDB OPTS += -DSQLITE_OMIT_OR_OPTIMIZATION OPTS += -DSQLITE_OMIT_ORIGIN_NAMES OPTS += -DSQLITE_OMIT_PAGER_PRAGMAS OPTS += -DSQLITE_OMIT_PROGRESS_CALLBACK OPTS += -DSQLITE_OMIT_QUICKBALANCE OPTS += -DSQLITE_OMIT_REINDEX OPTS += -DSQLITE_OMIT_SCHEMA_VERSION_PRAGMAS OPTS += -DSQLITE_OMIT_SHARED_CACHE OPTS += -DSQLITE_OMIT_SUBQUERY OPTS += -DSQLITE_OMIT_TCL_VARIABLE OPTS += -DSQLITE_OMIT_TEMPDB OPTS += -DSQLITE_OMIT_TRACE OPTS += -DSQLITE_OMIT_TRIGGER OPTS += -DSQLITE_OMIT_UTF16 OPTS += -DSQLITE_OMIT_VACUUM OPTS += -DSQLITE_OMIT_VIEW Without the SQLITE_OMIT_s, everything works just fine.
#f2dcdc 2019 active 2006 Oct anonymous Pending 1 1 FTS1: Create table in transaction raises Out of Sequence error (21) This error: SQL error: library routine called out of sequence is caused if the following script is executed by the Windows version of the SQLite3 console application with .load fts1.dll extension. If it does not show immediately, it will eventually surface if the script is run multiple times. The cause of the problem seems to be related to the transaction, the create virtual table as well as the amount of data inserted. Finally, the script is attached.
#f2dcdc 2022 active 2006 Oct anonymous Pending 1 1 .import command is not working I have a windows system running version 3.3.6 and a linux system running 3.3.3 when I run .import catalog.csv TEMPDATA on the windows system, it works fine. On the linux system, no data gets imported. There are no error messages. Is this a known issue in 3.3.3? _2006-Oct-14 01:15:07 by anonymous:_ {linebreak} A sample SQL schema and a 3 line import file demonstrating the problem would be helpful. ---- _2006-Nov-08 15:48:28 by anonymous:_ {linebreak} Schema: CREATE TABLE Catalog ( UPC text , SKU text primary key , DESC text , PACK text , PRICE text , SIZE text ); test.csv contents 00000000103,103,EFFEM CHOCOLATE FUNSIZE 75PPK 1 X1EA,1,$155.94,1 EA 00000000152,414317,CLEARLIGHT SLUSH CUP 16OZ CDL16 1X50EA,1,$5.04,50 EA 00000000152,56880,CLEARLIGHT SLUSH CUP 16OZ CDL16 20X50EA,20,$96.31,50 EA Command that does nothing: .import test.csv Catalog ---- _2006-Nov-08 15:52:40 by anonymous:_ {linebreak} Sorry, I'll try this again: Schema: CREATE TABLE Catalog ( UPC text , SKU text primary key , DESC text , PACK text , PRICE text , SIZE text ); test.csv contents 00000000103,103,EFFEM CHOCOLATE FUNSIZE 75PPK 1 X1EA,1,$155.94,1 EA 00000000152,414317,CLEARLIGHT SLUSH CUP 16OZ CDL16 1X50EA,1,$5.04,50 EA 00000000152,56880,CLEARLIGHT SLUSH CUP 16OZ CDL16 20X50EA,20,$96.31,50 EA Command that does nothing: .import test.csv Catalog
#f2dcdc 2027 active 2006 Oct anonymous Pending 1 1 FTS: Phrase searches return Offsets for individual phrase words With FTS (one as well as two), phrase searches return offsets for all individual words instead of the phrase as a whole, like in select name, ingredients from recipe where ingredients match '"broccoli cheese"'; Offsets() returns at least two matches for both individual words: *: broccoli *: cheese
#e8e8bd 2028 active 2006 Oct anonymous Pending 4 2 FTS1: UNIQUE() expression and UPDATE command not working I'm working with tables, containing around 1,4 million entries (1GB file size). To allow faster fulltext search I tried FTS1 now. What I saw is: creating the virtual FTS1 table with one keyword "UNIQUE(code), reference, text, ..." I had the idea to have faster access to "code", because this entry is only one time existing in table. In my actual SQLITE table "UNIQUE" was good idea, because "UPDATE"ing of entries was much faster as without "UNIQUE" expression. Unfortunately, in that moemnt I use "UNIQUE" expression in fulltext table, the FTS1 table doesn't accept insertion of entries like "INSERT into receipe (code, reference, text) values ('4711', 'RefnotAvailable', 'Test');" So I removed the "UNIQUE" keyword, knowing that later "UPDATE" command to modify entries will be slower. So I built new table with additional FTS1 fulltext table. Then I tried to "UPDATE" one entry. In that moment the program stopped immediately working (WIN XP system), what means that the application stopped without comment and returned to desktop. I tried the same in SQLITE3.exe (command line program) but also that program suspended immediately after the UPDATE command (like "UPDATE Volltext SET code = '4710', reference = 'RefChanged', text = 'notext';" That seems to me to be a bug. By the way, creating fulltext table to search inside my whole database increased the filesize a lot (4 times). May be that is solved in FTS2? Last wish: Fulltext search like "foo*" to find "fool" and "foot" would be a really great improvement. Best regards Ingo _2006-Oct-23 13:56:59 by anonymous:_ {linebreak} Ooops, as I saw today, also "DELETE" statements are causing SQLITE to stop working (crash). Program returns to Desktop on WIN XP after DELETE command.
#f2dcdc 2032 active 2006 Oct anonymous Pending 1 1 AV in btree.c running FTS2 compiled with SQLITE_OMIT_SHARED_CACHE If compiled with FTS2 support as well as SQLITE_OMIT_SHARED_CACHE=1, the sqlite console application causes an Access Violation: btree.c, line 3538: Read of address x00000014 if( pCur->idx>=pPage->nCell ){ if the SQL (attatched) is executed. I believe that this is a bug in btree.c, for the following reasons: *: The AV does not show if the #ifndef SQLITE_OMIT_SHARED_CACHE (lines 3514 and 3525) are commentet out. *: From my reading, all virtual tables use the extension API only and do not access the btree directly. _2006-Oct-25 06:30:43 by shess:_ {linebreak} Note that the attached SQL has exactly 273 INSERT statements. 273==256+16+1, so this is kicking in at a merge point. Don't know how that's relevant, but it seems suspicious. ---- _2006-Oct-25 16:31:34 by anonymous:_ {linebreak} Many thanks for looking into this - it was driving me mad until I came up with the rather simple SQL to reproduce it. I am not sure if the number of INSERTS is 100% the number needed to cause the problem, but the crash always happens after the exact same number of inserts. I did not count them but added roughly enough of them to cause the error. Sidenote: I can also make FTS2 to crash at another point, which I thought was related to the sizeof() bug I also reported. But apprarently it is not. Unfortunately I can not provide a test case for this since I can reproduce it only after adding some 3000 or so copyrighted documents to an empty database. At the time of the crash the DB is about 250 MB in size. However, I will run a test after the next commits to FTS2. ---- _2006-Oct-26 08:57:41 by anonymous:_ {linebreak} My previious comments from yesterday seem to be invalidated by the latest checkins [3486], [3488] and [3489]. Many thanks for those! However, the problem with =SQLITE_OMIT_SHARED_CACHE= still persists.
#f2dcdc 2037 active 2006 Oct anonymous Pending 1 1 Sqlite3 can't use datafile in Chinese path with Win2000 and WindowsXP. Sqlite3 can't use datafile in Chinese path with Win2000 and WindowsXP. This is a bug in os_win.c . My firend modify code to so , it work right. /* ** Convert a UTF-8 string to UTF-32. Space to hold the returned string ** is obtained from sqliteMalloc. */ static WCHAR *utf8ToUnicode(const char *zFilename){ int nChar; WCHAR *zWideFilename; if( !isNT() ){ return 0; } nChar = MultiByteToWideChar(CP_THREAD_ACP, MB_COMPOSITE, zFilename, -1, NULL, 0); zWideFilename = sqliteMalloc( nChar*sizeof(zWideFilename[0]) ); if( zWideFilename==0 ){ return 0; } nChar = MultiByteToWideChar(CP_THREAD_ACP, MB_COMPOSITE, zFilename, -1, zWideFilename, nChar); if( nChar==0 ){ sqliteFree(zWideFilename); zWideFilename = 0; } return zWideFilename; } /* ** Convert UTF-32 to UTF-8. Space to hold the returned string is ** obtained from sqliteMalloc(). */ static char *unicodeToUtf8(const WCHAR *zWideFilename){ int nByte; char *zFilename; nByte = WideCharToMultiByte(CP_THREAD_ACP, WC_COMPOSITECHECK, zWideFilename, -1, 0, 0, 0, 0); zFilename = sqliteMalloc( nByte ); if( zFilename==0 ){ return 0; } nByte = WideCharToMultiByte(CP_THREAD_ACP, WC_COMPOSITECHECK, zWideFilename, -1, zFilename, nByte, 0, 0); if( nByte == 0 ){ sqliteFree(zFilename); zFilename = 0; } return zFilename; } _2006-Oct-20 10:26:46 by anonymous:_ {linebreak} The proposed fix is completely wrong, but the bug exists nonetheless. The problem is that SQLite expects file names in UTF-8 encoding (and there is probably bug in your application too guessing from the proposed fix). While this works fine on NT systems where the UTF-8 encoding is converted to UTF-16 and passed to system wide-character APIs, the code path for non-NT systems (Win 9x) with ANSI-only APIs doesn't convert the UTF-8 file names into the ANSI code page which is expected by the system APIs.
#f2dcdc 2043 active 2006 Oct anonymous Pending 1 1 Spaces in view statement If you have a table defined with fields that contain spaces. create table table1 ("field one", "field two", "field three"); Then you do a select select "field one" from table1; That works fine. However if you save it as a view create view view_one as select "field one" from table1; Then if you run a select on the view it fails. select * from view_one;
#f2dcdc 2046 active 2006 Oct anonymous Fixed shess 1 1 FTS1 - Error closing database due to unfinished statements The following script causes an error in SQLite3.exe with FTS1. The error will surface only AFTER the script has finished AND you have typed .exit at the sqlite> prompt to quit SQLite3. The problem seems that the SELECT statement is not properly finalized due to an internal error. -- The next line is for Windows only, please adopt it -- if running Linux or use a FTS1-enabled SQLite3 binary. select load_extension ('fts1.dll'); CREATE TABLE Snippets( SnippetID INTEGER PRIMARY KEY, SnippetTitle TEXT, FtsID INTEGER); CREATE VIRTUAL TABLE SnippetsFts USING FTS1 (SnippetTitle, SnippetText); INSERT INTO Snippets (SnippetTitle) VALUES ('one'); INSERT INTO Snippets (SnippetTitle) VALUES ('two'); SELECT SnippetID FROM Snippets JOIN SnippetsFts ON FtsID = +SnippetsFts.RowID WHERE SnippetsFts MATCH 'one'; -- After the script is done, type .exit at the prompt to close the database. -- -- SQLite3 will close, but report the following error before doing so: -- -- "error closing database: Unable to close due to unfinalised statements" -- -- Does this qualify for a bug? The script is also attached to this ticket. _2006-Nov-27 22:58:49 by shess:_ {linebreak} Attached tighter version of the replication script, generated in isolating what mattered to the bug.
#f2dcdc 2048 active 2006 Oct anonymous Pending drh 1 1 table_info on columns with no default value are returned as string On line 486, noDflt is declared as{linebreak} static const Token noDflt = { (unsigned char*)"", 0, 0 };{linebreak} {linebreak} And on line 493:{linebreak} if( pDflt->z ){{linebreak} sqlite3VdbeOp3(v, OP_String8, 0, 0, (char*)pDflt->z, pDflt->n);{linebreak} }else{{linebreak} sqlite3VdbeAddOp(v, OP_Null, 0, 0);{linebreak} {linebreak} So columns with no default value aren't being set to null because the (pDflt->z) condition is non-null.
#f2dcdc 2057 active 2006 Nov anonymous Pending 3 1 full_column_names when 2 or more tables are joined is not working Version 2.8 has the behavior described in the documentation in respect to full_column_names when 2 or more tables are present with (table/alias).*, but 3.3.8 doesn't, mixing the pragmas "full_column_names" and "short_column_names" can only force to have full_column_names allways or never, some programs expect the behavior described in the documentation to remain working. _2006-Nov-08 20:10:13 by anonymous:_ {linebreak} Version 3.3.3 as well has the same problem. ---- _2006-Nov-09 09:34:52 by anonymous:_ {linebreak} Changing the line 977 of select.c (3.3.8) from: if( pEList->a[i].zName){ to: if( pEList->a[i].zName && pTabList->nSrc==1){ with pragma short_column_names = 0 behaves like 2.8 series.
#f2dcdc 2059 active 2006 Nov anonymous Pending 1 1 Still missing .DEF file from Windows 3.3.8 source code distribution The file sqlite3.def is missing from the zip archive of sources used to build sqlite3 on Windows. Ticket number 2031 was closed with a remark that this file is generated during the build process. That is true if one is building on Linux with MinGW32 configured as a cross-compiler. If one were building using that method then I assume one would not be downloading the src.zip archive anyway. My impression is that the src.zip archive is prepared once the build has been performed on Linux so Windows developers can directly build sqlite (and the generated files) without need of the other tools that the build process depends on. If this is accurate, then it would be very helpful if the src.zip archive could also include the sqlite3.def file. Without this file it is not possible for Windows developers to create a DLL from the src.zip archive. Thanks _2006-Nov-09 20:05:23 by anonymous:_ {linebreak} Works fine as is with MinGW ./configure && make sqlite3.exe
#f2dcdc 2060 active 2006 Nov anonymous Pending 1 1 Table references enclosed in parenthesis become "invisible" Hi, I'm developing an RDF-based system, which translates queries from SPARQL into SQL. While trying to add support for SQLite (MySQL is already supported) I came across the following problem: when table references in a FROM clause are enclosed in parenthesis, they cannot be referenced from outside the parenthesized expression. For example, given the table definitions CREATE TABLE IF NOT EXISTS t1 (a, b); CREATE TABLE IF NOT EXISTS t2 (c, d); CREATE TABLE IF NOT EXISTS t3 (e, f); The following queries all fail with "no such column" errors: SELECT t1.a, t3.f FROM (t1 CROSS JOIN t2 ON t1.b = t2.c) LEFT JOIN t3 ON t2.d = t3.e; SELECT t1.a, t3.f FROM t1 CROSS JOIN (t2 LEFT JOIN t3 ON t2.d = t3.e) ON t1.b = t2.c; SELECT t1.a, t2.d FROM (t1), (t2) WHERE t1.b = t2.c; I'm not sure if it is always possible to reformulate the queries in such a way that the extra parenthesis aren't necessary, but I suspect that complex expressions involving joins may require them to achieve the intended semantics. In any case, my system would require large changes to be able to get rid of the parenthesized subjoins, so it would be nice if this problem could be fixed. :-) _2006-Nov-10 03:56:46 by anonymous:_ {linebreak} For what it's worth, here's the parse trees of two similar queries ("SELECT t1.a, t2.d FROM t1, t2 WHERE t1.b = t2.c" and "SELECT t1.a, t2.d FROM (t1), (t2) WHERE t1.b = t2.c"), as well as one of the other more complicated join queries previously listed. SELECT t1.a, t2.d FROM t1, t2 WHERE t1.b = t2.c; Select { op: TK_SELECT isResolved: 1 pSrc: { a[0]: { zName: t1 iCursor: 0 colUsed: 0x00000003 pTab: t1 jointype: JT_INNER } a[1]: { zName: t2 iCursor: 1 colUsed: 0x00000003 pTab: t2 } } pEList: { a[0]: { pExpr: { op: TK_COLUMN span: {t1.a} affinity: SQLITE_AFF_NONE iTable: 0 iColumn: 0 pTab: t1 } } a[1]: { pExpr: { op: TK_COLUMN span: {t2.d} affinity: SQLITE_AFF_NONE iTable: 1 iColumn: 1 pTab: t2 } } } pWhere: { op: TK_EQ span: {t1.b = t2.c} pLeft: { op: TK_COLUMN span: {t1.b} affinity: SQLITE_AFF_NONE iTable: 0 iColumn: 1 pTab: t1 } pRight: { op: TK_COLUMN span: {t2.c} affinity: SQLITE_AFF_NONE iTable: 1 iColumn: 0 pTab: t2 } } } SELECT t1.a, t2.d FROM (t1), (t2) WHERE t1.b = t2.c; Select { op: TK_SELECT isResolved: 1 pSrc: { a[0]: { zAlias: sqlite_subquery_5C0A10_ iCursor: 0 pTab: sqlite_subquery_5C0A10_ pSelect: { op: TK_SELECT isResolved: 1 pSrc: { a[0]: { zName: t1 iCursor: 1 colUsed: 0x00000003 pTab: t1 } } pEList: { a[0]: { zName: a pExpr: { op: TK_COLUMN token: {a} span: {a} affinity: SQLITE_AFF_NONE iTable: 1 iColumn: 0 pTab: t1 } } a[1]: { zName: b pExpr: { op: TK_COLUMN token: {b} span: {b} affinity: SQLITE_AFF_NONE iTable: 1 iColumn: 1 pTab: t1 } } } } jointype: JT_INNER } a[1]: { zAlias: sqlite_subquery_5BE4F0_ iCursor: 2 pTab: sqlite_subquery_5BE4F0_ pSelect: { op: TK_SELECT isResolved: 1 pSrc: { a[0]: { zName: t2 iCursor: 3 colUsed: 0x00000003 pTab: t2 } } pEList: { a[0]: { zName: c pExpr: { op: TK_COLUMN token: {c} span: {c} affinity: SQLITE_AFF_NONE iTable: 3 iColumn: 0 pTab: t2 } } a[1]: { zName: d pExpr: { op: TK_COLUMN token: {d} span: {d} affinity: SQLITE_AFF_NONE iTable: 3 iColumn: 1 pTab: t2 } } } } } } pEList: { a[0]: { pExpr: { op: TK_COLUMN span: {t1.a} flags: EP_Resolved EP_Error iTable: -1 iColumn: 0 } } a[1]: { pExpr: { op: TK_DOT span: {t2.d} pLeft: { op: TK_ID token: {t2} span: {t2} } pRight: { op: TK_ID token: {d} span: {d} } } } } pWhere: { op: TK_EQ span: {t1.b = t2.c} pLeft: { op: TK_DOT span: {t1.b} pLeft: { op: TK_ID token: {t1} span: {t1} } pRight: { op: TK_ID token: {b} span: {b} } } pRight: { op: TK_DOT span: {t2.c} pLeft: { op: TK_ID token: {t2} span: {t2} } pRight: { op: TK_ID token: {c} span: {c} } } } } SQL error: no such column: t1.a SELECT t1.a, t3.f FROM (t1 CROSS JOIN t2 ON t1.b = t2.c) LEFT JOIN t3 ON t2.d = t3.e; Select { op: TK_SELECT isResolved: 1 pSrc: { a[0]: { zAlias: sqlite_subquery_5BFA30_ iCursor: 0 pTab: sqlite_subquery_5BFA30_ pSelect: { op: TK_SELECT isResolved: 1 pSrc: { a[0]: { zName: t1 iCursor: 1 colUsed: 0x00000003 pTab: t1 jointype: JT_INNER JT_CROSS } a[1]: { zName: t2 iCursor: 2 colUsed: 0x00000003 pTab: t2 } } pEList: { a[0]: { zName: a pExpr: { op: TK_COLUMN span: {t1.a} affinity: SQLITE_AFF_NONE iTable: 1 iColumn: 0 pTab: t1 } } a[1]: { zName: b pExpr: { op: TK_COLUMN span: {t1.b} affinity: SQLITE_AFF_NONE iTable: 1 iColumn: 1 pTab: t1 } } a[2]: { zName: c pExpr: { op: TK_COLUMN span: {t2.c} affinity: SQLITE_AFF_NONE iTable: 2 iColumn: 0 pTab: t2 } } a[3]: { zName: d pExpr: { op: TK_COLUMN span: {t2.d} affinity: SQLITE_AFF_NONE iTable: 2 iColumn: 1 pTab: t2 } } } pWhere: { op: TK_EQ span: {t1.b = t2.c} flags: EP_FromJoin EP_Resolved iRightJoinTable: 2 pLeft: { op: TK_COLUMN span: {t1.b} affinity: SQLITE_AFF_NONE flags: EP_FromJoin EP_Resolved iTable: 1 iColumn: 1 iRightJoinTable: 2 pTab: t1 } pRight: { op: TK_COLUMN span: {t2.c} affinity: SQLITE_AFF_NONE flags: EP_FromJoin EP_Resolved iTable: 2 iColumn: 0 iRightJoinTable: 2 pTab: t2 } } } jointype: JT_LEFT JT_OUTER } a[1]: { zName: t3 iCursor: 3 pTab: t3 } } pEList: { a[0]: { pExpr: { op: TK_COLUMN span: {t1.a} flags: EP_Resolved EP_Error iTable: -1 iColumn: 0 } } a[1]: { pExpr: { op: TK_DOT span: {t3.f} pLeft: { op: TK_ID token: {t3} span: {t3} } pRight: { op: TK_ID token: {f} span: {f} } } } } pWhere: { op: TK_EQ span: {t2.d = t3.e} flags: EP_FromJoin iRightJoinTable: 3 pLeft: { op: TK_DOT span: {t2.d} flags: EP_FromJoin iRightJoinTable: 3 pLeft: { op: TK_ID token: {t2} span: {t2} flags: EP_FromJoin iRightJoinTable: 3 } pRight: { op: TK_ID token: {d} span: {d} flags: EP_FromJoin iRightJoinTable: 3 } } pRight: { op: TK_DOT span: {t3.e} flags: EP_FromJoin iRightJoinTable: 3 pLeft: { op: TK_ID token: {t3} span: {t3} flags: EP_FromJoin iRightJoinTable: 3 } pRight: { op: TK_ID token: {e} span: {e} flags: EP_FromJoin iRightJoinTable: 3 } } } } SQL error: no such column: t1.a ---- _2006-Nov-11 18:29:33 by anonymous:_ {linebreak} The resolving bug appears to be that unique column names or column aliases are searched across all subqueries, but table names and table aliases are only searched at their current SELECT level only. With this in mind, here are mechanical workarounds without using column aliases (assumes the column names in all joined tables are unique): SELECT a, f FROM (t1 CROSS JOIN t2 ON t1.b = t2.c) LEFT JOIN t3 ON d = e; SELECT t1.a, f FROM t1 CROSS JOIN (t2 LEFT JOIN t3 ON t2.d = t3.e) ON t1.b = c; SELECT a, d FROM (t1), (t2) WHERE b = c; And here are mechanical workarounds using column aliases (assumes the column names are not unique between tables): SELECT t1.a, t3f FROM t1 CROSS JOIN (select t3.f t3f, t2.c t2c from t2 LEFT JOIN t3 ON t2.d = t3.e) ON t1.b = t2c; SELECT t1a, t3.f FROM (select t1.a t1a, t2.d t2d from t1 CROSS JOIN t2 ON t1.b = t2.c) LEFT JOIN t3 ON t2d = t3.e; SELECT t1a, t2d FROM (select t1.a t1a, t1.b t1b from t1), (select t2.c t2c, t2.d t2d from t2) WHERE t1b = t2c; Notice that t3.f in the second query did not require an alias because the table "t3" was part of its immediate SELECT. You could make an alias for every column just in case, I just wanted to highlight the difference. ---- _2007-Feb-13 15:40:31 by anonymous:_ {linebreak} Fixing this issue would slow down SELECT parsing and column resolution for all queries (more specifically all prepared statements) due to the recursion required for column resolution. It would be easier to change your SQL code generator to accomodate SQLite. Just make aliases for every table at every subselect level and have the SELECT at any given level only work with the table aliases at that level.
#e8e8bd 2066 active 2006 Nov anonymous Pending 2 2 Incorrect error message in the case of ENOLCK If you're trying to open a sqlite database that is stored on a filesystem that doesn't support locking, then you'll get the error when you try to execute any commands on it: Error: file is encrypted or is not a database If you run sqlite under strace, you see: read(0, ".schema\n.quit\n", 4096) = 14 fcntl64(3, F_SETLK64, {type=F_RDLCK, whence=SEEK_SET, start=1073741824, len=1}, 0xafa5cd70) = 0 fcntl64(3, F_SETLK64, {type=F_RDLCK, whence=SEEK_SET, start=1073741826, len=510}, 0xafa5cd70) = 0 fcntl64(3, F_SETLK64, {type=F_UNLCK, whence=SEEK_SET, start=1073741824, len=1}, 0xafa5cd70) = 0 access("/mnt/www/zzz_old_sites/trac.db-journal", F_OK) = -1 ENOENT (No such file or directory) fstat64(3, {st_mode=S_IFREG|0644, st_size=584704, ...}) = 0 _llseek(3, 0, [0], SEEK_SET) = 0 read(3, "** This file contains an SQLite "..., 1024) = 1024 fcntl64(3, F_SETLK64, {type=F_UNLCK, whence=SEEK_SET, start=0, len=0}, 0xafa5cdd0) = -1 ENOLCK (No locks available) write(2, "Error: file is encrypted or is n"..., 46Error: file is encrypted or is not a database Sqlite should really check the exact error code, and give a more helpful error (eg "Locking not available on this filesystem. Databases may only be stored on filesystems that support locking")
#cfe8bd 2075 active 2006 Nov anonymous Pending 3 3 Improve VACUUM speed and INDEX page locality In testing several 100 Meg - 1 Gig databases (including the Monotone OpenEmbedded database) I found that changing the order of the SQL commands executed by VACUUM to create indexes after table inserts results in 15% faster VACUUM times, and up to 25% faster cold-file-cache queries when indexes are used. This patch effectively makes the pages of each index contiguous in the database file after a VACUUM, as opposed to being scattered throughout the pages of the table related to the index. Your results may vary, but I think this is a very safe change that can potentially boost average database performance. Index: src/vacuum.c =================================================================== RCS file: /sqlite/sqlite/src/vacuum.c,v retrieving revision 1.65 diff -u -3 -p -r1.65 vacuum.c --- src/vacuum.c 18 Nov 2006 20:20:22 -0000 1.65 +++ src/vacuum.c 20 Nov 2006 21:09:27 -0000 @@ -143,14 +143,6 @@ int sqlite3RunVacuum(char **pzErrMsg, sq " AND rootpage>0" ); if( rc!=SQLITE_OK ) goto end_of_vacuum; - rc = execExecSql(db, - "SELECT 'CREATE INDEX vacuum_db.' || substr(sql,14,100000000)" - " FROM sqlite_master WHERE sql LIKE 'CREATE INDEX %' "); - if( rc!=SQLITE_OK ) goto end_of_vacuum; - rc = execExecSql(db, - "SELECT 'CREATE UNIQUE INDEX vacuum_db.' || substr(sql,21,100000000) " - " FROM sqlite_master WHERE sql LIKE 'CREATE UNIQUE INDEX %'"); - if( rc!=SQLITE_OK ) goto end_of_vacuum; /* Loop through the tables in the main database. For each, do ** an "INSERT INTO vacuum_db.xxx SELECT * FROM xxx;" to copy @@ -162,10 +154,22 @@ int sqlite3RunVacuum(char **pzErrMsg, sq "FROM sqlite_master " "WHERE type = 'table' AND name!='sqlite_sequence' " " AND rootpage>0" - ); if( rc!=SQLITE_OK ) goto end_of_vacuum; + /* Create indexes after the table inserts so that their pages + ** will be contiguous resulting in (hopefully) fewer disk seeks. + */ + rc = execExecSql(db, + "SELECT 'CREATE UNIQUE INDEX vacuum_db.' || substr(sql,21,100000000) " + " FROM sqlite_master WHERE sql LIKE 'CREATE UNIQUE INDEX %'"); + if( rc!=SQLITE_OK ) goto end_of_vacuum; + + rc = execExecSql(db, + "SELECT 'CREATE INDEX vacuum_db.' || substr(sql,14,100000000)" + " FROM sqlite_master WHERE sql LIKE 'CREATE INDEX %' "); + if( rc!=SQLITE_OK ) goto end_of_vacuum; + /* Copy over the sequence table */ rc = execExecSql(db, _2007-Feb-11 00:49:50 by drh:_ {linebreak} My alternative plan is to modify insert.c so that it recognizes the special case of INSERT INTO table1 SELECT * FROM table2; when table1 and table2 have identical schemas, including all the same indices. When this special case is recognized, the generated bytecode will first transfer all table entries from table2 to table1, using a row by row transfer without decoding each row into its constituient columns. Then it will do the same for each index. There will be two benefits here. First, when the above construct occurs during the course of a VACUUM, the table and each index, including intrisic indices associated with UNIQUE and PRIMARY KEY constraints, will be transferred separately so that all of there pages will be adjacent in the database file. The second benefit will occur when trying to load large quantities of data into an indexed table. Loading indexed data into a very large table is currently slow because the index entries are scattered haphazardly around in the file. But if data is first loaded into a smaller temporary table with the same schema, it can then be transferred to the main table using an INSERT statement such as the above in what amounts to a merge operation. ---- _2007-Feb-11 06:58:36 by anonymous:_ {linebreak} There's no question that your proposal will greatly improve VACUUM speed which relies on the "INSERT INTO table1 SELECT * from table2" construct. But would it be possible for you to relax the restriction on having identical indexes for table1 and table2? For that matter it would be nice if table2 could be any subselect or view. Then "REPLACE INTO table1 SELECT ...anything..." could also be optimized. Since you can detect that SQLite is doing a bulk insert anyway, it could generate code to make a temporary staging table with automatically generated identical indexes to table1 which could be periodically merged into table1 and truncated every X rows. X could be either set via pragma or be a function of the size of the page cache. The temporary staging table would be dropped after the bulk INSERT INTO ... SELECT. Every user inserting large volumes of data would have to perform this procedure anyway. Manually recreating all the indexes for a given temporary table to match the original table and performing the looping logic is cumbersome and error-prone. It would be very conveniant if SQLite were to do it on the user's behalf. This scheme could only work if there are no triggers on table1, of course. ---- _2007-Feb-11 09:16:25 by drh:_ {linebreak} My initial enhancement does nothing to preclude the more agressive enhancement described by anonymous. In order to avoid subtle bugs, and in view of my limited time available to work on this, I think it best to take the more conservative approach first and defer the more elaborate optimization suggested by anonymous until later. ---- _2007-Feb-11 13:54:34 by anonymous:_ {linebreak} It should be possible to identify contiguous blocks of individual "INSERT INTO table1 VALUES(...)" statements to the same table within a large transaction and perform the same proposed optimization as with "INSERT INTO table1 SELECT ...". This would require higher level coordination by the parser. Anytime a read operation (SELECT, UPDATE) occurs on such a table marked for bulk INSERT within the large transaction, its temp staging table could be merged into the INSERT destination table and the staging table truncated. The process could be repeated for the remainder of the transaction. Such an optimization would be a huge benefit to SQLite users since they would need not know the idiosynchracies of the implementation of "INSERT INTO table1 SELECT ..." in order to have efficient table and index population. Alternatively, if you wish to avoid the complexity of re-assembling and staging individual INSERT statements, it might be a good opportunity for SQLite to support the multi-row variant of the INSERT command: INSERT INTO table1 (a,b,c) VALUES(1,2,3), (4,5,6), (7,8,9); Which is essentially a transform of: CREATE TEMP TABLE table1_staging (a,b,c); INSERT INTO table1_staging VALUES(1,2,3); INSERT INTO table1_staging VALUES(4,5,6); INSERT INTO table1_staging VALUES(7,8,9); INSERT INTO table1 SELECT * FROM table1_staging; -- TRUNCATE OR DROP table1_staging as necessary which could use the same bulk staging optimization. ---- _2007-Feb-13 02:42:41 by anonymous:_ {linebreak} Any harm in checking in the simple patch above for the 3.3.13 release? ---- _2007-Feb-13 12:51:47 by drh:_ {linebreak} I have a much better fix standing by that I intend to check-in as soon as I get 3.3.13 out the door. I don't want this in 3.3.13 for stability reasons. ---- _2007-Feb-18 23:07:08 by anonymous:_ {linebreak} Some related analysis and an .import patch using a :memory: staging table with the "INSERT INTO table1 SELECT FROM table2" construct can be found here: http://www.mail-archive.com/sqlite-users%40sqlite.org/msg22143.html
#f2dcdc 2076 active 2006 Nov anonymous Pending a.rottmann 1 1 % exists as value in varchar abnormal abend of client application (C++) when sqlite returns stream of data containing "%" value. Is % a special character? _2006-Nov-21 14:14:25 by anonymous:_ {linebreak} % is not a special character. Can you post a small C program demonstrating the problem?
#f2dcdc 2077 active 2006 Nov anonymous Pending 2 1 Problems with using ASCII symbols 0x80 - 0xFF in database path Platform: Windows.
The SQLite library and executable doesn't see database files that are placed into folders named using ASCII symbols with codes 0x80-0xFF. That symbols are used to represent language-specific symbols (for example, Russian). In result, database cannot be placed into folder with name in Russian language. This bug is "unstable": it doesn't appear in all cases. Below are logs from my experiments with this problem. In all cases the path I requested exists, and database file is placed there. I have noticed that problem depends on filename path and name lengths. =========================================================
// creating test database
E:\!DISTRIB\sqlite-3_3_7>sqlite3.exe test.sqb
SQLite version 3.3.7
Enter ".help" for instructions
sqlite> create table a(id int);
sqlite> insert into a values (1);
sqlite> ^C
E:\!DISTRIB\sqlite-3_3_7>copy test.sqb e:\test.sqb
'3'\'`'a'Z'b'`'S'Q'_'` 'f'Q'[']'`'S: 1. //This means that 1 file was copied
E:\!DISTRIB\sqlite-3_3_7>sqlite3 e:\test.sqb
SQLite version 3.3.7
Enter ".help" for instructions
sqlite> select * from a;
1
sqlite> ^C
// Works!
E:\!DISTRIB\sqlite-3_3_7>mkdir e:\'/
//Using ASCII symbol "'/" (0x8D) to represent cyrillic letter which can be entered in the command line by using Alt+(141) combination
E:\!DISTRIB\sqlite-3_3_7>copy test.sqb E:\'/\test.sqb
'3'\'`'a'Z'b'`'S'Q'_'` 'f'Q'[']'`'S: 1.
E:\!DISTRIB\sqlite-3_3_7>sqlite3 e:\'/\test.sqb
SQLite version 3.3.7
Enter ".help" for instructions
sqlite> select * from a;
1
sqlite> ^C
// That is works too!
E:\!DISTRIB\sqlite-3_3_7>mkdir E:\'/\1
E:\!DISTRIB\sqlite-3_3_7>copy test.sqb E:\'/\1\test.sqb
'3'\'`'a'Z'b'`'S'Q'_'` 'f'Q'[']'`'S: 1.
E:\!DISTRIB\sqlite-3_3_7>sqlite3 E:\'/\1\test.sqb
Unable to open database "E:\(T\1\test.sqb": unable to open database file
// Doesn't work, and writes the wrong symbol "(T" in place of "'/"! I've noticed that if we convert symbol "'/" from DOS encoding to Windows encoding and then write it in DOS encoding, then we'll get "(T".
E:\!DISTRIB\sqlite-3_3_7>copy test.sqb E:\'/\tst.sqb
'3'\'`'a'Z'b'`'S'Q'_'` 'f'Q'[']'`'S: 1.
E:\!DISTRIB\sqlite-3_3_7>sqlite3 E:\'/\tst.sqb
SQLite version 3.3.7
Enter ".help" for instructions
sqlite> select * from a;
SQL error: no such table: a
sqlite> ^C
// It seems to work, i don't get an error, but doesn't see the tables! =(
=================================
#f2dcdc 2081 active 2006 Nov anonymous Pending doughenry 1 1 sqlite3_column_decltype throws exception, if selection is grouped If I "group by" a selection over several columns I can't find out the orgin type of these columns using sqlite3_column_decltype(..). An exception is thrown. _2006-Nov-23 18:37:47 by anonymous:_ {linebreak} You also get no decl type from a subselect. This goes to the typeless nature of SQLite - I don't think a type can even be derived in this case.
#cfe8bd 2084 active 2006 Nov anonymous Pending 4 3 Add API function mapping column decl string to SQLite type This is an API feature request. It would be nice to be able to obtain the SQLite type (e.g. SQLITE_INTEGER) from the declared column type string as returned by sqlite3_column_decltype. This was discussed briefly on the mailing list here: http://marc.10east.com/?l=sqlite-users&m=116422872301957&w=2 The function I have in mind is: int sqlite3_decltype_to_type(const char *decl) { Token decl_token; char aff_type; int col_type; decl_token.z = decl; if( decl_token.z ){ decl_token.n = strlen(decl_token.z); aff_type = sqlite3AffinityType(&decl_token); switch( aff_type ){ case SQLITE_AFF_INTEGER: col_type = SQLITE_INTEGER; break; case SQLITE_AFF_NUMERIC: /* falls through */ case SQLITE_AFF_REAL: col_type = SQLITE_FLOAT; break; case SQLITE_AFF_TEXT: col_type = SQLITE_TEXT; break; case SQLITE_AFF_NONE: col_type = SQLITE_BLOB; break; default: col_type = 0; /* unknown */ break; } } return col_type; } If this seems agreeable, I would be willing to put together a real patch. However, I would need some guidance on where it should go. I'm not sure what should happen when no type can be determined. _2006-Nov-26 22:32:45 by anonymous:_ {linebreak} According to the comment above the function sqlite3AffinityType: "If none of the substrings in the above table are found, SQLITE_AFF_NUMERIC is returned". The default condition in sqlite3_decltype_to_type will not be reached. ---- _2006-Nov-26 23:04:23 by anonymous:_ {linebreak} Thanks for pointing to that comment. Looks like SQLITE_AFF_NUMERIC is, for these purposes, unknown. So the case statement could be: switch( aff_type ){ case SQLITE_AFF_INTEGER: col_type = SQLITE_INTEGER; break; case SQLITE_AFF_REAL: col_type = SQLITE_FLOAT; break; case SQLITE_AFF_TEXT: col_type = SQLITE_TEXT; break; case SQLITE_AFF_NONE: col_type = SQLITE_BLOB; break; case SQLITE_AFF_NUMERIC: /* falls through */ default: col_type = 0; /* unknown */ break; } ---- _2006-Nov-27 02:43:06 by anonymous:_ {linebreak} Your first function was correct, it just had some unreachable code. There's no unknown affinity, in the absence of a match the affinity is assumed to be numeric: int sqlite3_decltype_to_type(const char *decl) { int type = SQLITE_FLOAT; if( decl ){ Token token; token.z = decl; token.n = strlen(token.z); switch( sqlite3AffinityType(&token) ){ case SQLITE_AFF_INTEGER: type = SQLITE_INTEGER; break; case SQLITE_AFF_TEXT: type = SQLITE_TEXT; break; case SQLITE_AFF_NONE: type = SQLITE_BLOB; break; default: break; } } return type; }
#cfe8bd 2089 active 2006 Nov anonymous Pending 3 3 Decouple sqlite_int64 from other 64bit datatypes Currently sqlite3 makes the (valid) assumption that sqlite_int64 (or i64, u64) is 64 bit wide, matches with Tcl_WideInt and has the same datasize (and byte order) than double. The following patch fixes this and allows sqlite_int64 to be any integral type, e.g. a 32bit int (with the limitations of the reduced datatype size). The use case for this is for systems that do not support 64bit integers (e.g. lack of compiler feature, embedded system), db's of small data size, and systems without large file support. The patch allows compiling with -DSQLITE_INT64_TYPE=int -DSQLITE_32BIT_ROWID for such a system. _2006-Nov-29 01:13:07 by anonymous:_ {linebreak} Hm, now I wanted to add the patch file but I don't get the formatting right without editing the file and removing empty lines. How am I supposed to add a patch file (created with diff -ru)?
#cfe8bd 2093 active 2006 Dec anonymous Pending 2 3 sqlite3_vtab_cursor doesn't have errMsg The sqlite3_vtab_cursor structure doesn't have a zErrMsg pointer. Only the containing vtable does. This means that operations on cursor objects that have an error have to set the error on the vtable not the cursor. Unfortunately this means that there are race conditions since two different cursors on the same vtable could have errors at the same time. If the cursors are in different threads then a crash or worse can happen.
#cfe8bd 2096 active 2006 Dec anonymous Pending 3 3 ATTACH DATABASE returns SQLITE_ERROR when database is locked From an email sent to DRH: I am working on a problem surrounding the inability to ATTACH to a database file. The error text being returned is "database is locked", which should be SQLITE_BUSY, however, the error code being returned by sqlite3_exec is SQLITE_ERROR. Is sqlite3_exec wrong in returning SQLITE_ERROR rather than SQLITE_BUSY? I have some nagging feeling that I determined or read that the attachFunc function does not return a truly-relevant status code, but I can't see why offhand nor can I find any evidence to support that theory. If sqlite3_exec is doing the right thing, however, then the question becomes one of identifying when to retry the ATTACH statement; we're currently keying off SQLITE_BUSY or SQLITE_LOCKED, as appropriate, and I'd rather not be trying to trap errors based on error text.
#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