bgcolor # Type Status Created By Subsys Changed Assigned Svr Pri Title _Description _Remarks #f2dcdc 2916 code active 2008 Feb anonymous 2008 Feb 1 1 sqlitedll-3_5_5.zip is older 3.5.4 binary sqlitedll-3_5_5.zip in download section is same with old 3.5.4 binary. _2008-Feb-01 12:13:04 by anonymous:_ {linebreak} Yes , I can confirm it #c8c8c8 2910 new closed 2008 Jan anonymous 2008 Jan 1 2 Number of rows in result set - new API Performance problem to retrieve number of records in result set after executed any query using prepare/step is evident. On DB around 200MB using one table with 15.000 records having around 10 fieds, among it's one blob field with data less than 10KB in each record, executed query with 'where' clausule using prepare/step (have no blob field listed) last around 30s to only count records in result set. SQLIte3_Get_Table need only 3s to returning all data as well. I I'm aware that many asserts may slow down the whole process dramatically, however not 10 times. Since there is no an API to retrieve number of records in result set and as prepare/step is far to slow, suggestion is to create one API, with maximum optimization for the purpose. _2008-Jan-31 02:00:07 by anonymous:_ {linebreak} To discover the number of rows, the VDBE engine should iterate over every record of the result. This is the same as calling sqlite_step() until EOF. But you can't do that, because after each sqlite_step(), you lost in memory data fields fetched for each row, so the way is counting rows after iteration. Also, tickets should be used for reporting bugs only. ---- _2008-Jan-31 04:24:29 by danielk1977:_ {linebreak} Internally, sqlite3_get_table() uses sqlite3_exec() which uses sqlite3_prepare()/step()/finalize(). So you should look for another reason why get_table() is 10 times faster than prepare()/step()/finalize(). You could also try a "SELECT count(*) ..." query to determine the expected size of a result set. As the above poster points out, there is no way to efficiently add this capability to SQLite (at least, no more efficiently than evaluating a count(*) query before the "real" query). Dan. ---- _2008-Jan-31 08:19:05 by anonymous:_ {linebreak} Anonymous: As allowed during ticket adding, I propose new feature. Dan: Since this feature should speed-up performance of a SQLite wrapper, "SELECT count(*) ..." is a real problem (require careful parsing to redesine gived query). "SELECT count(*) ..." itself is reasonably fast. ---- _2008-Jan-31 08:54:56 by anonymous:_ {linebreak} In addition to upper. "SELECT count(*)..." exactly last around 3s, Get_Table last as well around 3s, prepare/step/finalyze to only count numbers of records last 30s. This clearly indicate radically performance lost in prepare/step/finalyze. #f2dcdc 2907 code active 2008 Jan anonymous 2008 Jan 1 1 Issues of sqlite3 with Windows Mobile 5/6 hi. we are currently using sqlite3 for our mobile application. it has been running without a hitch on pocket pc 2003 and previous versions. come windows mobile 5 and 6 we have been getting errors, although not consistent yet. one example is 'EXCEPTION_DATATYPE_MISALIGNMENT'. another is 'SELECT STATMENTS TO THE LEFT AND RIGHT OF UNION ARE NOT EQUAL'. i was wondering if you have any known compatibility issues of your product with this version of windows mobile. thanks in advance. _2008-Jan-28 13:26:26 by anonymous:_ {linebreak} EXCEPTION_DATATYPE_MISALIGNMENT is thrown when you try to use and Odd pointer address. I wrote a custom allocator for WinCE/ARM platform, and I have to take care about memory alignment (I used to align at 2 bytes, and at that time it solved the problem) #f2dcdc 2898 code active 2008 Jan anonymous 2008 Jan 1 1 Latest CVS for 3.5.4 fails to build test1.c gcc -pipe -O3 -g -Wall -DSQLITE_DISABLE_DIRSYNC=1 -I. -I../src -DNDEBUG -I/usr/include -DSQLITE_THREADSAFE=1 -DSQLITE_THREAD_OVERRIDE_LOCK=1 -DSQLITE_OMIT_LOAD_EXTENSION=1 -DTCLSH=1 -DSQLITE_TEST=1 -DSQLITE_CRASH_TEST=1 -DSQLITE_NO_SYNC=1 -DTEMP_STORE=1 -o .libs/testfixture ../src/attach.c ../src/btree.c ../src/build.c ../src/date.c ../src/expr.c ../src/func.c ../src/insert.c ../src/malloc.c ../src/os.c ../src/os_os2.c ../src/os_unix.c ../src/os_win.c ../src/pager.c ../src/pragma.c ../src/prepare.c ../src/printf.c ../src/select.c ../src/test1.c ../src/test2.c ../src/test3.c ../src/test4.c ../src/test5.c ../src/test6.c ../src/test7.c ../src/test8.c ../src/test9.c ../src/test_autoext.c ../src/test_async.c ../src/test_btree.c ../src/test_config.c ../src/test_hexio.c ../src/test_malloc.c ../src/test_md5.c ../src/test_onefile.c ../src/test_schema.c ../src/test_server.c ../src/test_tclvar.c ../src/test_thread.c ../src/tokenize.c ../src/utf.c ../src/util.c ../src/vdbe.c ../src/vdbeapi.c ../src/vdbeaux.c ../src/vdbemem.c ../src/where.c parse.c ../src/tclsqlite.c ./.libs/libsqlite3.so -L/usr/lib64 -ltcl8.4 -ldl -lpthread -lieee -lm -Wl,--rpath -Wl,/common/pkgs/sqlite-3.5.4.3/lib ../src/build.c: In function 'sqlite3RefillIndex': ../src/build.c:2275: warning: cast to pointer from integer of different size ../src/func.c: In function 'trimFunc': ../src/func.c:919: warning: cast from pointer to integer of different size ../src/func.c: In function 'sqlite3RegisterBuiltinFunctions': ../src/func.c:1464: warning: cast to pointer from integer of different size ../src/func.c:1483: warning: cast to pointer from integer of different size ../src/insert.c: In function 'sqlite3GenerateConstraintChecks': ../src/insert.c:1200: warning: cast to pointer from integer of different size ../src/insert.c:1034: warning: 'j2' may be used uninitialized in this function ../src/insert.c: In function 'sqlite3Insert': ../src/insert.c:373: warning: 'regFromSelect' may be used uninitialized in this function ../src/test1.c: In function 'test_collate_func': ../src/test1.c:2085: warning: cast from pointer to integer of different size ../src/test1.c: In function 'test_collate_needed_cb': ../src/test1.c:2209: warning: cast to pointer from integer of different size ../src/test1.c: In function 'alignmentCollFunc': ../src/test1.c:2258: warning: cast from pointer to integer of different size ../src/test1.c:2259: warning: cast from pointer to integer of different size ../src/test8.c: In function 'echoBestIndex': ../src/test8.c:722: warning: 'nRow' may be used uninitialized in this function ../src/vdbe.c: In function 'sqlite3VdbeExec': ../src/vdbe.c:502: warning: 'pOut' may be used uninitialized in this function ../src/vdbe.c:501: warning: 'pIn3' may be used uninitialized in this function ../src/vdbe.c:501: warning: 'pIn2' may be used uninitialized in this function ../src/vdbe.c:501: warning: 'pIn1' may be used uninitialized in this function ../src/vdbeaux.c: In function 'sqlite3VdbeChangeP4': ../src/vdbeaux.c:529: warning: cast from pointer to integer of different size ../src/vdbemem.c: In function 'sqlite3ValueText': ../src/vdbemem.c:911: warning: cast from pointer to integer of different size /tmp/ccsuOeus.o: In function `reset_prng_state': /build/work/sqlite-3.5.4.3/bld/../src/test1.c:4280: undefined reference to `sqlite3ResetPrngState' /tmp/ccsuOeus.o: In function `restore_prng_state': /build/work/sqlite-3.5.4.3/bld/../src/test1.c:4267: undefined reference to `sqlite3RestorePrngState' /tmp/ccsuOeus.o: In function `save_prng_state': /build/work/sqlite-3.5.4.3/bld/../src/test1.c:4254: undefined reference to `sqlite3SavePrngState' collect2: ld returned 1 exit status make: *** [testfixture] Error 1 _2008-Jan-17 23:54:58 by anonymous:_ {linebreak} Problem appears to be here in libsqlite.3.so.0.8.6 as shown by: nm -A .libs/libsqlite3.so.0.8.6 | grep sqlite3ResetPrngState which shows no entry point. And: nm -A .libs/random.o | grep sqlite3ResetPrngState which also shows no entry point. ---- _2008-Jan-17 23:56:55 by anonymous:_ {linebreak} Ah... It appears -DSQLITE_TEST should be passed when building test1.c and left off when building prior to install. ---- _2008-Jan-21 20:16:00 by anonymous:_ {linebreak} In the makefile the right flag appears to be set, it's just not making it through to the compile for some reason. ---- _2008-Jan-21 20:16:24 by anonymous:_ {linebreak} Still fails the same based on today's cvs update. ---- _2008-Jan-23 03:14:49 by anonymous:_ {linebreak} This bug fixed as of latest cvs pull #f2dcdc 2897 code active 2008 Jan anonymous 2008 Jan 1 1 String or BLOB exceed size limit This error was shown after attemp to read script from SQLite 3.5.4 shell in order to recreate old DB. Details: 1. Database was created with SQLite 3.3.4. Around 20 standard fieds and one BLOB. 2. The only one existed table was dumped with shell of SQLite 3.5.4. SQL script seems to be coorrect. 3. Opened SQLite 3.5.4 and read script in new DB. The error "String or BLOB exceed size limit" are sown for several lines. Many records missing. 4. Attempted to dump table with shell of version 3.3.6 (have no more 3.3.4 shell) and read into new DB with 3.5.4 shell The same errors are shown. The same steps was attempted with 3.3.6. shell only. All seems to be correct. _2008-Jan-17 20:23:25 by drh:_ {linebreak} This size limit on BLOBs in SQLite version 3.5.4 is 1GB. How big is your blob, exactly? ---- _2008-Jan-17 22:22:24 by anonymous:_ {linebreak} BLOB in each record is no more than few MB. Mostly it is few KB (e-client and news application). Whole DB have around 200MB. ---- _2008-Jan-18 02:28:11 by drh:_ {linebreak} This issue is probably resolved by check-in [4636], then. ---- _2008-Jan-18 14:28:13 by anonymous:_ {linebreak} If directive SQLITE_MAX_SQL_LENGTH is not defined it is set to 1,000,000 (10^6) in amalgamation code of 3.5.4. #c8c8c8 2896 code closed 2008 Jan anonymous 2008 Jan 1 1 FTS3 misses document, but finds non-matching ones instead The following script presents a severe bug in FTS3 where it does not find a document even though it is in the index. Instead it finds other documents which do not match the search criteria. The log below was generated by the original SQLite3.exe 3.5.4 downloaded from the web-site. It shows that the entry 'A-123' is present in the table but is not matched by the query. It also demonstrates that non-matching entries are found instead. DROP TABLE IF EXISTS fts3; CREATE VIRTUAL TABLE fts3 using fts3 (a); INSERT INTO fts3 VALUES ('A-123'); SELECT * FROM fts3; A-123 SELECT * FROM fts3 WHERE fts3 MATCH 'A-123'; INSERT INTO fts3 VALUES ('A-12'); SELECT * FROM fts3 WHERE fts3 MATCH 'A-123'; A-12 INSERT INTO fts3 VALUES ('A-1'); SELECT * FROM fts3 WHERE fts3 MATCH 'A-123'; A-12 A-1 Here is the SQL only (without output, for easy reproduction): DROP TABLE IF EXISTS fts3; CREATE VIRTUAL TABLE fts3 using fts3 (a); INSERT INTO fts3 VALUES ('A-123'); SELECT * FROM fts3; SELECT * FROM fts3 WHERE fts3 MATCH 'A-123'; INSERT INTO fts3 VALUES ('A-12'); SELECT * FROM fts3 WHERE fts3 MATCH 'A-123'; INSERT INTO fts3 VALUES ('A-1'); SELECT * FROM fts3 WHERE fts3 MATCH 'A-123'; _2008-Jan-17 19:36:18 by anonymous:_ {linebreak} Sorry, I must have been dreaming: This is of course *not a bug* but the expected behavior. The '-' character is supposed to exclude the word following it. My sincere apologies if this has caused doubt or uncertainties to anyone! #cfe8bd 2894 code fixed 2008 Jan anonymous 2008 Jan 1 1 virtual table calls Next() and Column() after Eof() returns true I saw this problem in my virtual table implementation. When I had a query that used a left outer join on a column that could be NULL, Next() and Column() were called after Eof() had returned true. I tried to reproduce the problem using test_schema from the source distribution which resulted in a segmentation fault, and I assume it's the same defect. Here is a sample script: .load test_schema.so create virtual table x using schema; -- this produces results select * from x where dflt_value is null; -- this produces a segmentation fault select * from x lhs left outer join x rhs on lhs.dflt_value = rhs.dflt_value; #f2dcdc 2893 code active 2008 Jan anonymous 2008 Jan 1 1 incorrect integer range tests recently a function that performs integer range tests was added to the cvs (check-in [4706]), but if i am correct there is a problem in the return value of the function in the file vdbemem.c: static i64 doubleToInt64(double r){ ... if( r<(double)minInt ){ return minInt; }else if( r>(double)maxInt ){ return minInt; <-- is this correct, shouldn't it be maxInt? }else{ return (i64)r; } } _2008-Jan-16 17:33:56 by drh:_ {linebreak} See the remarks on ticket #2280. The code duplicates the behavior of the FPU on x86. ---- _2008-Jan-16 18:21:28 by anonymous:_ {linebreak} did you mean ticket #2880? didn't read that ticket before, but since there was no comment regarding that behavior in the function it seemed (to my eyes) that it was a mistake. maybe adding a small comment in there would clarify this issue ---- _2008-Jan-16 18:39:42 by anonymous:_ {linebreak} Just because the double to int overflow behavior happens to be that way with GCC on x86, is it desirable? #c8c8c8 2890 build closed 2008 Jan anonymous 2008 Jan 1 1 Assistance request fixing six 3.5.4 i/o test failures Failures include exclusive-ioerr-2.280.4 exclusive-ioerr-2.281.4 exclusive-ioerr-2.282.4 incrvacuum-ioerr-1.31.4 io-4.1 io-4.2.3 Please let me know how to run individual tests, so I can debug these and submit fix suggestions. Thanks. _2008-Jan-15 21:50:52 by drh:_ {linebreak} These errors do not appear when the tests are run individually. They are only reproducible when you run the complete test suite. ---- _2008-Jan-16 03:15:50 by anonymous:_ {linebreak} Please give an example of how to run one individual test. Thanks. #cfe8bd 2883 code fixed 2008 Jan anonymous 2008 Jan 1 1 compile error - missing data I have original sqlite 3.2.8 source code in external SVN and a dashboard has encountered the following compile error win gcc 4.x: "shell.c:364: error: format not a string literal and no format arguments" See http://mail.kde.org/pipermail/kde-dashboard/2008-January/008494.html Any SQLite newer is addected as well. The patch below fixes misused fprintf. Index: shell.c{linebreak} ==================================================================={linebreak} --- shell.c (revision 761314){linebreak} +++ shell.c (working copy){linebreak} @@ -361,7 +361,7 @@{linebreak} output_c_string(p->out, z);{linebreak} }{linebreak} if( bSep ){{linebreak} - fprintf(p->out, p->separator);{linebreak} + fprintf(p->out, "%s", p->separator);{linebreak} }{linebreak} }{linebreak} #f2dcdc 2881 build active 2008 Jan anonymous 2008 Jan 1 1 Latest sqlite-3.5.4 build fail on latest Fedora 2.6.23.12-52.fc7 Two test cases fail. io-4.1... Expected: [3] Got: [2] io-4.2.1... Ok io-4.2.2... Ok io-4.2.3... Expected: [3] Got: [2] io-4.3.1... Ok Let me know how to run individual test cases and how this might be fixed. Here's how I built sqlite using latest CVS. If something is wrong here, let me know and I'll rebuild/retest. I'm building on latest Fedora fc7. Thanks. _______ net1#uname -a Linux net1.coolsurf.com 2.6.23.12-52.fc7 #1 SMP Tue Dec 18 20:27:10 EST 2007 x86_64 x86_64 x86_64 GNU/Linux net1#build_sqlite mkdir -p /build/work/sqlite-3.5.4 cd /build/work/sqlite-3.5.4 unset CDPATH export CFLAGS='-pipe -O3 -g -DSQLITE_DISABLE_DIRSYNC=1 -Wall' rm -rf bld cvs -d :pserver:anonymous@www.sqlite.org:/sqlite -r update . mkdir bld cd bld ../configure --prefix=/common/pkgs/sqlite-3.5.4 --enable-tcl --with-tcl=/usr/lib64 --enable-threadsafe --enable-threads-override-locks make groupadd vuser || /bin/true useradd -M -g vuser -d /vhost/davidfavor.com/users/david -s /bin/zsh david || /bin/true useradd -M -g vuser -d /vhost/livefeast.com/users/yemiah -s /bin/zsh yemiah || /bin/true chown david:vuser -R .. su -c "make test" david _2008-Jan-11 17:18:52 by anonymous:_ {linebreak} Same tests still fail with CVS of today around 11AM CST. ---- _2008-Jan-11 17:41:55 by drh:_ {linebreak} FWIW, both those test cases pass on SuSE 10.1. I do not understand why they are failing on Fedora. But in any event, the tests in question are verifying logic that implements an optimization that is not used on Fedora, ever. So the failures are of no consequence. If those are the only two tests that fail, then you can safely use the build for whatever it is you are trying to do. ---- _2008-Jan-11 19:16:19 by anonymous:_ {linebreak} Failures when 'make fulltest' built with CFLAGS of '-pipe -O3 -g -Wall -DSQLITE_DISABLE_DIRSYNC=1 -DSQLITE_MEMDEBUG' exclusive-malloc-1.transient.746...make: *** [fulltest] Segmentation fault Failures when 'make fulltest' built with CFLAGS of '-pipe -O3 -g -Wall -DSQLITE_DISABLE_DIRSYNC=1' Skipping malloc tests: not compiled with -DSQLITE_MEMDEBUG... 6 errors out of 61998 tests Failures on these tests: exclusive-ioerr-2.280.4 exclusive-ioerr-2.281.4 exclusive-ioerr-2.282.4 incrvacuum-ioerr-1.31.4 io-4.1 io-4.2.3 All memory allocations freed - no leaks Maximum memory usage: 14376554 bytes Pre 3.5.x builds work fine on Fedora. If you're open to debugging all these, I'd like to go through and resolve all these one by one, so Fedora has a clean build/fulltest. Please let me know how to run each test individually and I'll try to figure out the problem with each. Thanks. #cfe8bd 2880 code fixed 2008 Jan anonymous 2008 Jan 1 1 problem with reals bigger then 1e18 We've been reported (and confirmed) that there might be an isue using the Windows sqlite3 dll on large numbers. When defining fields as real then inserting data like 1e18 goes well, but 1e19 generates an error about being not a valid float. If you enter the data using sqlite3.exe there's no problem, retrieving the data is also working well. The problem arises when calling sqlite3step, the prepare does not give any error. We've checked and the same error appears using sqlite administrator (which is a Delphi development too, but using ZEOS). Since it goes wrong within sqlite3step this might be a sqlite isue? albert drent aducom software _2008-Jan-09 13:56:01 by drh:_ {linebreak} You write: "If you enter the data using sqlite3.exe there's no problem." But sqlite3.exe uses the sqlite3_prepare() and sqlite3_step() interface the same as anything else. So if sqlite3.exe is working, that suggests the problem is in your program, and not in the SQLite core. If you can provide a specific example of what is not working we can investigate this problem further. But based on the information provide, we are unable to reproduce the problem and suspect the problem is in the application, not in SQLite. ---- _2008-Jan-09 15:48:49 by anonymous:_ {linebreak} Delphi has been known to futz with the FPU registers. It's possible that Delphi is changing the FPU accuracy and causing this problem. ---- _2008-Jan-09 19:49:57 by anonymous:_ {linebreak} I don't know where the problem lies and will investigate further. It's not a fpu isue since it's a ansistring containing the string, being prepared and executed. There hardly any Delphi involved here. I'll create a small app in Delphi showing the problem and report this issue on Delphi forums too. The string is somewhat like 'insert into demo (myval) value (1e19)' Will come back here. Albert ---- _2008-Jan-10 13:47:28 by anonymous:_ {linebreak} Thanks to sasa: he found out the following It appears to be a problem in used C compiler. Following script is tested in sqlite 3-5-4 shell: create table t1 (ID integer primary key, v1 double); insert into t1(v1) values(1e18); insert into t1(v1) values(1e19); This works correctly on linux compiled with GNU compiler 4.1.2. On windows with MinGW 3.2.0 works correctly too, however it fails when shell is compiled with BCC 5.5.1. Without detail check I can only assume there is a problem in representing and using 8 and 6 bytes floats together in SQLite without explicit casting BCC may require. Or this may be a limitation of BCC 5.5.1 or a bug. Without detail check those are most probably causes. ---- _2008-Jan-10 14:01:30 by drh:_ {linebreak} SQLite does not use any "6 byte floats". I'm not sure what a 6 byte float is. The code in SQLite is suppose to be ANSI C and casts that are not required by ANSI C are generally omitted. If you find an exception to this please let us know. If BCC requires casts above and beyond what is required by ANSI C, then that is a bug in BCC. I assume then, that this is a bug in BCC. I do not have access to BCC so there is nothing I can do to fix it. If you can suggest patches that will work around the BCC bug (for example, some unnecessary explicit casts) we will consider adding them to the core. But I am afraid it will be up to you to supply those patches, since we do not have BCC available to experiment with and SQLite works perfectly for every compiler we have access to. ---- _2008-Jan-10 18:34:56 by anonymous:_ {linebreak} Funny thing is that if the dll is created with a gnu compiler it seems to work. Can you tell me with what compiler the dll (w.o. tcl binding) has been build? ---- _2008-Jan-10 20:16:12 by anonymous:_ {linebreak} This is the reply of sasa, it's way ahead of me... Unfortunately, I have no more time to test any DLL since I do not using any I did not compiled myself. As I understand vac, that one from official SQLite site have this bug. In the signature of DLL can be seen which compiler was used for compilation. BCC 5.5.1 is free and can freely be downloaded from official Borland site by anyone. This is I prepared to add to the ticket, but you can add yourself. I have spent all my spare time for now. Since it do not break anything it will be probably included in official version:
/* ** The MEM structure is already a MEM_Real. Try to also make it a ** MEM_Int if we can. */ SQLITE_PRIVATE void sqlite3VdbeIntegerAffinity(Mem *pMem){ assert( pMem->flags & MEM_Real ); assert( pMem->db==0 || sqlite3_mutex_held(pMem->db->mutex) ); pMem->u.i = pMem->r; // <- This causing termination for values >= 1e19 if( ((double)pMem->u.i)==pMem->r ){ pMem->flags |= MEM_Int; } }This conversion line causing BCC 5.5.1 run-time to raise a conversion error if values are >= 1e19. It is necessary a clean way to convert double to integer/int64. If nothing else, this will solve the problem (code is not essential for correct functionality):
/* ** The MEM structure is already a MEM_Real. Try to also make it a ** MEM_Int if we can. */ SQLITE_PRIVATE void sqlite3VdbeIntegerAffinity(Mem *pMem){ assert( pMem->flags & MEM_Real ); assert( pMem->db==0 || sqlite3_mutex_held(pMem->db->mutex) ); #ifndef __BORLANDC__ pMem->u.i = pMem->r; if( ((double)pMem->u.i)==pMem->r ){ pMem->flags |= MEM_Int; } #endif }[ Bijgewerkt do jan 10 2008, 08:49 ] SZUTILS - link ---- _2008-Jan-11 10:23:37 by anonymous:_ {linebreak} In "sqlite - Check-in [4705]" you have tryed to "attempt to work around this bug in the Borland compiler + ** by moving the value into a temporary variable first so that if + ** the assignment into the integer really does corrupt the right-hand + ** side value, it will corrupt a temporary variable that we do not + ** care about." First of all, thank you for attempt to solve the issue. I will first write several facts: 1. Statically linked SQLite 3.5.4 commandline shell (aka sqlite3.exe) is compiled and SQL script worked fine on Windows with MinGW 3.2.3, however not with BCC 5.5.1 (without any special compiler options). 2. Library (aka sqlite3.dll) compiled with MinGW 2.95 (from official SQLite site), MinGW 3.2.3, BCC 5.5.1 and probably other compilers for Windows does FAIL executing provided SQL script only with large floats >=1e19. With MinGW 3.2.3, library was created from official SQLite 3.5.4 distribution with following script: sh configure make dll 3. Both (integer and double) have 8 byte size in BCC. BCC is ANSI C complient compiler. 4. It is not the fact that BCC corrupt right-hand side value. It fail trying to convert values >=1e19 and assign it to int64 variable. This will raise invalid floating-point operation exception for most C compilers. 5. Since front-end application use only DLL (compiled by several different C compilers, including provided from offical SQLite site) to execute an SQL command by sqlite_exec(), it is nothing which connect the bug and the application itself (made in Delphi or C). Current attempt in 4705 will not work from two reasons: 1. The same conversion logic is used. 2. Variable definition in the middle of the function in not allowed by ANSI C. This will fail not only on BCC, but on any ANSI C compiler. According to upper, conclusion is clear that this is not a bug in BCC, but in all C compiles for Windows - or in conversion double to integer currently used. Thank you for your time. Sasa Zeman ---- _2008-Jan-11 13:22:27 by drh:_ {linebreak} The purpose of the sqlite3VdbeIntegerAffinity() function is to convert a floating point value into a 64-bit integer value if and only if that conversion can be done without loss of precision. Perhaps Sasa Zeman or somebody else can suggest a way to do that successfully on BCC 5.5.1. (The current implementation appears to work on every other compiler.) Or perhaps this is not a compiler issue at all, but an instance of Delphi messing with FPU registers, as was suggested by the anonymous comment on 2008-Jan-09. Please note that I do not own a machine that runs windows, and I am unwilling to purchase one for the purpose of fixing this issue. So I cannot download and install BCC 5.5.1 or Delphi. And thus, I cannot reproduce the problem here. So if you want this problem resolved, you are going to need to suggest code for resolving it. Completely disabling the sqlite3VdbeIntegerAffinity() function is not an acceptable resolution because that breaks quite a few other things. ---- _2008-Jan-11 14:00:05 by anonymous:_ {linebreak} AFAIK it is a general compiler isue on Windows. Reading Sasa's comment this is NOT a Delphi isue since the problem lies in SQLite3.dll. In fact, the tests who are shown here are all done in C++. What I would like to know is how the Windows dll on this website and which DOES have this problem, is created and with what compiler. Appearantly you haven't created this one since you do not own a Windows compiler (?). Then we are in pursuit of an answer and solution. Ignoring the problem is not an option IMHO and would be bad news to all on the Windows community. The Borland C++ compiler with the patch here (SASA does this work?) is completely FREE and doesn't need to be purchased. But if this isue cannot be resolved, and I'm not a C++ guru, then there should be a note here that on Windows the precision is limited to 10e18??? There are now three people busy with this, sure there must be a solution in conjunction with all of us? It's in everyones benefit if this isue can be resolved in stead of ignored. ---- _2008-Jan-11 14:36:11 by drh:_ {linebreak} The windows binaries on the SQLite website are cross-compiled on a Linux box using Mingw-GCC. ---- _2008-Jan-11 17:49:26 by anonymous:_ {linebreak} Which steps do I need to take to reproduce the problem? It seems to work just fine with sqlite3.exe 3.5.0 on Windows XP: sqlite> create table t1 (ID integer primary key, v1 double); sqlite> insert into t1(v1) values(1e18); sqlite> insert into t1(v1) values(1e19); sqlite> select * from t1; 1|1.0e+18 2|1.0e+19 Could someone please post a little C-code which exhibits the problem? Thanks! ---- _2008-Jan-11 18:10:29 by anonymous:_ {linebreak} Thank you for your respond. In order to reproduce the problem, you can create very simple C program using the DLL, which execute upper script by each line, using sqlite3_exec() command. Thank you for your time. Sasa ---- _2008-Jan-11 19:05:51 by anonymous:_ {linebreak} The following: + static const i64 maxInt = (((i64)0x7fffffff)<<32)|0xffffffff; + static const i64 minInt = ((i64)0x80000000)<<32; + + if( r<(double)minInt ){ + return minInt; + }else if( r>(double)maxInt ){ runs faster on my compiler if you do this: + static const double maxInt = (((i64)0x7fffffff)<<32)|0xffffffff; + static const double minInt = ((i64)0x80000000)<<32; + + if( r
Index: configure =================================================================== RCS file: /sqlite/sqlite/configure,v retrieving revision 1.45 diff -u -3 -p -r1.45 configure --- configure 27 Nov 2007 14:50:07 -0000 1.45 +++ configure 5 Jan 2008 07:41:00 -0000 @@ -18520,9 +18520,9 @@ if test "$TARGET_EXEEXT" = ".exe"; then OS_UNIX=0 OS_WIN=0 OS_OS2=1 - TARGET_CFLAGS="$TARGET_CFLAGS -DOS_OS2=1" + CFLAGS="$CFLAGS -DOS_OS2=1" if test "$ac_compiler_gnu" == "yes" ; then - TARGET_CFLAGS="$TARGET_CFLAGS -Zomf -Zexe -Zmap" + CFLAGS="$CFLAGS -Zomf -Zexe -Zmap" BUILD_CFLAGS="$BUILD_CFLAGS -Zomf -Zexe" fi else @@ -18530,14 +18530,14 @@ if test "$TARGET_EXEEXT" = ".exe"; then OS_WIN=1 OS_OS2=0 tclsubdir=win - TARGET_CFLAGS="$TARGET_CFLAGS -DOS_WIN=1" + CFLAGS="$CFLAGS -DOS_WIN=1" fi else OS_UNIX=1 OS_WIN=0 OS_OS2=0 tclsubdir=unix - TARGET_CFLAGS="$TARGET_CFLAGS -DOS_UNIX=1" + CFLAGS="$CFLAGS -DOS_UNIX=1" fi @@ -19392,7 +19392,7 @@ fi echo "$as_me:$LINENO: result: $ac_cv_func_usleep" >&5 echo "${ECHO_T}$ac_cv_func_usleep" >&6 if test $ac_cv_func_usleep = yes; then - TARGET_CFLAGS="$TARGET_CFLAGS -DHAVE_USLEEP=1" + CFLAGS="$CFLAGS -DHAVE_USLEEP=1" fi @@ -19491,7 +19491,7 @@ fi echo "$as_me:$LINENO: result: $ac_cv_func_fdatasync" >&5 echo "${ECHO_T}$ac_cv_func_fdatasync" >&6 if test $ac_cv_func_fdatasync = yes; then - TARGET_CFLAGS="$TARGET_CFLAGS -DHAVE_FDATASYNC=1" + CFLAGS="$CFLAGS -DHAVE_FDATASYNC=1" fi Index: configure.ac =================================================================== RCS file: /sqlite/sqlite/configure.ac,v retrieving revision 1.31 diff -u -3 -p -r1.31 configure.ac --- configure.ac 27 Nov 2007 14:50:07 -0000 1.31 +++ configure.ac 5 Jan 2008 07:41:00 -0000 @@ -310,9 +310,9 @@ if test "$TARGET_EXEEXT" = ".exe"; then OS_UNIX=0 OS_WIN=0 OS_OS2=1 - TARGET_CFLAGS="$TARGET_CFLAGS -DOS_OS2=1" + CFLAGS="$CFLAGS -DOS_OS2=1" if test "$ac_compiler_gnu" == "yes" ; then - TARGET_CFLAGS="$TARGET_CFLAGS -Zomf -Zexe -Zmap" + CFLAGS="$CFLAGS -Zomf -Zexe -Zmap" BUILD_CFLAGS="$BUILD_CFLAGS -Zomf -Zexe" fi else @@ -320,14 +320,14 @@ if test "$TARGET_EXEEXT" = ".exe"; then OS_WIN=1 OS_OS2=0 tclsubdir=win - TARGET_CFLAGS="$TARGET_CFLAGS -DOS_WIN=1" + CFLAGS="$CFLAGS -DOS_WIN=1" fi else OS_UNIX=1 OS_WIN=0 OS_OS2=0 tclsubdir=unix - TARGET_CFLAGS="$TARGET_CFLAGS -DOS_UNIX=1" + CFLAGS="$CFLAGS -DOS_UNIX=1" fi AC_SUBST(BUILD_EXEEXT) @@ -565,13 +565,13 @@ AC_SUBST(TARGET_DEBUG) ######### # Figure out whether or not we have a "usleep()" function. # -AC_CHECK_FUNC(usleep, [TARGET_CFLAGS="$TARGET_CFLAGS -DHAVE_USLEEP=1"]) +AC_CHECK_FUNC(usleep, [CFLAGS="$CFLAGS -DHAVE_USLEEP=1"]) #-------------------------------------------------------------------- # Redefine fdatasync as fsync on systems that lack fdatasync #-------------------------------------------------------------------- -AC_CHECK_FUNC(fdatasync, [TARGET_CFLAGS="$TARGET_CFLAGS -DHAVE_FDATASYNC=1"]) +AC_CHECK_FUNC(fdatasync, [CFLAGS="$CFLAGS -DHAVE_FDATASYNC=1"]) ######### # Generate the output files._2008-Jan-05 08:24:29 by anonymous:_ {linebreak} It appears that http://www.sqlite.org/sqlite3-3.5.4.bin.gz and http://www.sqlite.org/sqlite-3.5.4.so.gz use sleep and fsync even though usleep and fdatasync are available on Linux. On the Linux man page, it claims that fdatasync is more efficient than fsync: "Unfortunately, fsync() will always initiate two write operations: one for the newly written data and another one in order to update the modification time stored in the inode. If the modification time is not a part of the transaction concept fdatasync() can be used to avoid unnecessary inode disk write operations." #c8c8c8 2868 code closed 2008 Jan anonymous 2008 Jan 1 1 Encryption user can't link latest SQLite I have licensed the encryption package and have it working with an earlier version of SQLite. I downloaded the current stable release, copied it over the previous version and compiled it. It compiled fine, but now the pager has unresolved externals, it seems many of the function names have changed. Please advise. Thanks Tim _2008-Jan-03 14:00:20 by drh:_ {linebreak} The license is perpetual with unlimited free updates. You just have to send us private email and we will send you back the latest code. #f2dcdc 2866 build active 2008 Jan anonymous 2008 Jan 1 3 Problems building Windows native in cygwin/mingw environment Trying to build Windows native version using the Cygwin build environment. $ gcc -v Reading specs from /usr/lib/gcc/i686-pc-cygwin/3.4.4/specs Configured with: /usr/build/package/orig/test.respin/gcc-3.4.4-3/configure --verbose --prefix=/usr --exec-prefix=/usr --sysconfdir=/etc --libdir=/usr/lib --libexecdir=/usr/lib --mandir=/usr/share/man --infodir=/usr/share/info --enable-languages=c,ada,c++,d,f77,pascal,java,objc --enable-nls --without-included-gettext --enable-version-specific-runtime-libs --without-x --enable-libgcj --disable-java-awt --with-system-zlib --enable-interpreter --disable-libgcj-debug --enable-threads=posix --enable-java-gc=boehm --disable-win32-registry --enable-sjlj-exceptions --enable-hash-synchronization --enable-libstdcxx-debug Thread model: posix gcc version 3.4.4 (cygming special, gdc 0.12, using dmd 0.125) $ $ CFLAGS=-mno-cygwin ./configure --disable-tcl --enable-threadsafe $ make A) The make appears to build sqlite3.exe just fine, without errors or warnings. This binary does work from cmd.exe, BUT not from within the bash cygwin shell for some reason, unlike other Windows native binaries I've built. Next... $ make install B) The cc sqlite3.c -o sqlite3 fails to rebuild sqlite3.exe correctly with the -mno-cygwin option. The output follows: rm -rf tsrc mkdir -p tsrc cp ./src/alter.c ./src/analyze.c ./src/attach.c ./src/auth.c ./src/btmutex.c ./src/btree.c ./src/btree.h ./src/build.c ./src/callback.c ./src/complete.c ./src/date.c ./src/delete.c ./src/expr.c ./src/func.c ./src/hash.c ./src/hash.h ./src/insert.c ./src/journal.c ./src/legacy.c ./src/loadext.c ./src/main.c ./src/malloc.c ./src/mem1.c ./src/mem2.c ./src/mem3.c ./src/mutex.c ./src/mutex_os2.c ./src/mutex_unix.c ./src/mutex_w32.c ./src/os.c ./src/os_unix.c ./src/os_win.c ./src/os_os2.c ./src/pager.c ./src/pager.h ./src/parse.y ./src/pragma.c ./src/prepare.c ./src/printf.c ./src/random.c ./src/select.c ./src/shell.c ./src/sqlite.h.in ./src/sqliteInt.h ./src/table.c ./src/tclsqlite.c ./src/tokenize.c ./src/trigger.c ./src/utf.c ./src/update.c ./src/util.c ./src/vacuum.c ./src/vdbe.c ./src/vdbe.h ./src/vdbeapi.c ./src/vdbeaux.c ./src/vdbeblob.c ./src/vdbefifo.c ./src/vdbemem.c ./src/vdbeInt.h ./src/vtab.c ./src/where.c ./ext/fts1/fts1.c ./ext/fts1/fts1.h ./ext/fts1/fts1_hash.c ./ext/fts1/fts1_hash.h ./ext/fts1/fts1_porter.c ./ext/fts1/fts1_tokenizer.h ./ext/fts1/fts1_tokenizer1.c sqlite3.h ./src/btree.h ./src/btreeInt.h ./src/hash.h ./src/sqliteLimit.h ./src/mutex.h opcodes.h ./src/os.h ./src/os_common.h ./src/sqlite3ext.h ./src/sqliteInt.h ./src/vdbe.h parse.h ./ext/fts1/fts1.h ./ext/fts1/fts1_hash.h ./ext/fts1/fts1_tokenizer.h ./src/vdbeInt.h tsrc cp: warning: source file `./src/btree.h' specified more than once cp: warning: source file `./src/hash.h' specified more than once cp: warning: source file `./src/sqliteInt.h' specified more than once cp: warning: source file `./src/vdbe.h' specified more than once cp: warning: source file `./ext/fts1/fts1.h' specified more than once cp: warning: source file `./ext/fts1/fts1_hash.h' specified more than once cp: warning: source file `./ext/fts1/fts1_tokenizer.h' specified more than once cp: warning: source file `./src/vdbeInt.h' specified more than once rm tsrc/sqlite.h.in tsrc/parse.y cp parse.c opcodes.c keywordhash.h tsrc tclsh ./tool/mksqlite3c.tcl cc sqlite3.c -o sqlite3 /usr/lib/gcc/i686-pc-cygwin/3.4.4/../../../libcygwin.a(libcmain.o):(.text+0xab): undefined reference to `_WinMain@16' collect2: ld returned 1 exit status make: *** [sqlite3] Error 1 $ #f2dcdc 2865 code active 2007 Dec anonymous 2007 Dec 1 2 FTS3 does not build with amalgamation in CVS Grab the latest CVS sources, then run: ./configure make sqlite3.c grep sqlite3Fts3Init sqlite3.c extern int sqlite3Fts3Init(sqlite3*); rc = sqlite3Fts3Init(db); If you compile sqlite3.c with -DSQLITE_ENABLE_FTS3, then sqlite3Fts3Init is unresolved. For some reason, sqlite3Fts3Init and fts3.c was not included in the sqlite3.c amalg. It used to work correctly in 3.5.4. _2007-Dec-30 18:17:57 by anonymous:_ {linebreak} Nevermind, "make sqlite3.c" has never built with the fts3 sources in 3.5.4 or before. You have to run ext/fts3/mkfts3amal.tcl ---- _2007-Dec-30 18:20:56 by anonymous:_ {linebreak} It seems that the sqlite3+fts3 amalg can only be built from main.mk, not Makefile. #c8c8c8 2861 doc closed 2007 Dec anonymous 2008 Jan 1 1 How do I connect to Crystal Reports - I am using vs2003 & the Finisar Not sure of the right venue for this question. My apologies if this is not the right place. I am developing a simple accounting application for a non-profit organisation. I am using vs2003 and programming in c#. To date I have used Access (Jet) database files and the Crystal Report Generator built in to VS2003. I thought SQLite would be a better choice than the Access database - simple to change my c# code using the Finisar data provider, but I am at a loss to see how I can use this provider in Crystal. Is there any way to get this provider onto the list of ADO DB providers in Crystal - or any other approach I could use. If I cannot use Crystal is there an alternative for producing reports which can link to SQLite. Any help much appreciated. Roger Lovelock _2007-Dec-29 13:58:55 by anonymous:_ {linebreak} Not a bug. Better ask this on the mailing list. To subscribe to the list, send a message to:
capi3-13-4... Ok capi3-13-5...*** glibc detected *** capi3-14.1-misuse... Ok capi3-15.1... Error: error code SQLITE_TOOBIG (18) does not match sqlite3_errcode SQLITE_OK (0) capi3-15.2... Expected: [2] Got: [0] capi3-15.3... Expected: [SQLITE_OK] Got: [SQLITE_MISUSE] capi3-15.4... Error: (21) library routine called out of sequence capi3-15.5... Error: (21) library routine called out of sequence capi3-15.6... Error: (21) library routine called out of sequence capi3-15.7... Error: (21) library routine called out of sequence capi3-15.8... Error: (21) library routine called out of sequence capi3-16.1... Error: (21) library routine called out of sequence capi3-16.2... Error: (21) library routine called out of sequence capi3-16.3... Error: (21) library routine called out of sequence capi3-16.4... Error: (21) library routine called out of sequence capi3-17.1... Error: (21) library routine called out of sequence capi3-17.2... Ok capi3-17.3...*** glibc detected *** ./testfixture: double free or corruption (!prev): 0x0812d650 ***#cfe8bd 2846 build fixed 2007 Dec anonymous 2007 Dec 1 1 --disable-tcl does not work rm -rf tsrc mkdir -p tsrc cp ./src/alter.c ./src/analyze.c ./src/attach.c ./src/auth.c ./src/btmutex.c ./src/btree.c ./src/btree.h ./src/build.c ./src/callback.c ./src/complete.c ./src/date.c ./src/delete.c ./src/expr.c ./src/func.c ./src/hash.c ./src/hash.h ./src/insert.c ./src/journal.c ./src/legacy.c ./src/loadext.c ./src/main.c ./src/malloc.c ./src/mem1.c ./src/mem2.c ./src/mem3.c ./src/mem4.c ./src/mutex.c ./src/mutex_os2.c ./src/mutex_unix.c ./src/mutex_w32.c ./src/os.c ./src/os_unix.c ./src/os_win.c ./src/os_os2.c ./src/pager.c ./src/pager.h ./src/parse.y ./src/pragma.c ./src/prepare.c ./src/printf.c ./src/random.c ./src/select.c ./src/shell.c ./src/sqlite.h.in ./src/sqliteInt.h ./src/table.c ./src/tclsqlite.c ./src/tokenize.c ./src/trigger.c ./src/utf.c ./src/update.c ./src/util.c ./src/vacuum.c ./src/vdbe.c ./src/vdbe.h ./src/vdbeapi.c ./src/vdbeaux.c ./src/vdbeblob.c ./src/vdbefifo.c ./src/vdbemem.c ./src/vdbeInt.h ./src/vtab.c ./src/where.c ./ext/fts1/fts1.c ./ext/fts1/fts1.h ./ext/fts1/fts1_hash.c ./ext/fts1/fts1_hash.h ./ext/fts1/fts1_porter.c ./ext/fts1/fts1_tokenizer.h ./ext/fts1/fts1_tokenizer1.c sqlite3.h ./src/btree.h ./src/btreeInt.h ./src/hash.h ./src/sqliteLimit.h ./src/mutex.h opcodes.h ./src/os.h ./src/os_common.h ./src/sqlite3ext.h ./src/sqliteInt.h ./src/vdbe.h parse.h ./ext/fts1/fts1.h ./ext/fts1/fts1_hash.h ./ext/fts1/fts1_tokenizer.h ./src/vdbeInt.h tsrc cp: warning: source file `./src/btree.h' specified more than once cp: warning: source file `./src/hash.h' specified more than once cp: warning: source file `./src/sqliteInt.h' specified more than once cp: warning: source file `./src/vdbe.h' specified more than once cp: warning: source file `./ext/fts1/fts1.h' specified more than once cp: warning: source file `./ext/fts1/fts1_hash.h' specified more than once cp: warning: source file `./ext/fts1/fts1_tokenizer.h' specified more than once cp: warning: source file `./src/vdbeInt.h' specified more than once rm tsrc/sqlite.h.in tsrc/parse.y cp parse.c opcodes.c keywordhash.h tsrc tclsh ./tool/mksqlite3c.tcl make: tclsh: Command not found make: *** [sqlite3.c] Error 127 _2007-Dec-17 00:45:26 by anonymous:_ {linebreak} Seems the same problem as #2845 #f2dcdc 2842 code active 2007 Dec anonymous 2007 Dec 1 1 .import does not recongnise NULL values .import function fails to see NULL values in csv files as NULL values...instead they are treated as the string "NULL". This is with .mode list and separator , But behaves similarly for .mode csv Also if one outputs a table with NULL values to a file, then re-imports that file, again .import does not recognise the values as NULL, but as "NULL". Everything here also applies to empty strings in files, e.g. instead of "NULL" using nothing... This is a showstopper for us since we want to import a large amount of data with many tables containing NULL values. I can't see any valid reason for .import not to recognise the same syntax as the command line. Note that something like: sqlite3 my.db insert into MY_TABLE values (1,"foo","bar",NULL) ..works fine. It is just .import that appears to be broken. _2007-Dec-14 16:39:51 by rdc:_ {linebreak} .import only inserts string values into database tables. If your column has a declared type that changes the columns affinity to numeric or integer, then those strings will be converted to numeric values by the SQLIte library. The workaround is to simply insert a unique string where ever you want a NULL value, and then run an update that replaces those strings with real NULL values. If you inserted the string 'NULL' then do this after the .import update t set field = null where field = 'NULL'; You will have to repeat this for each field in your table that might contain the 'NULL' string. #f2dcdc 2841 todo active 2007 Dec anonymous 2007 Dec 1 1 The sqlite mailing list has become overrun by trolls The sqlite mailing list is very useful. The S/N is at times a little high but nonetheless quite manageable. Recently (see the DeviceSQL thread) it got really bad. Would moderation be unacceptable during these periods of time where people feel the need to protect their ego's? The sqlite mailing list is primarily about sqlite (well, and lemon), not a marketing vector for other products? Surely they have their own lists and resources for that? #c8c8c8 2836 build closed 2007 Dec anonymous 2007 Dec 1 1 Undefined symbol 'OP_StackDepth' There is a error on current cvs version(12/12/07) with "Undefined symbol 'OP_StackDepth'" message. opcodes.h and opcodes.c havn't 'OP_StackDepth' definition currently. Sorry for my short English. _2007-Dec-13 07:41:07 by danielk1977:_ {linebreak} opcodes.c and opcodes.h are auto-generated files (generated based on vdbe.c). Likely your copies have not been regenerated since yesterdays modifications. Try running "make clean" before rebuilding. If that fails, manually remove opcodes.c and opcodes.h from your build directory. #cfe8bd 2835 code fixed 2007 Dec anonymous 2007 Dec 1 1 Lemon memory read/write errors
$ cat lemonbug.y phrase ::= foo AND A B C foo. phrase ::= foo AND A B C bar. foo ::= BIRD | CAT | DOG. bar ::= CAT | DOG. $ ./lemon lemonbug.y 1 parsing conflicts. $ head -23 lemonbug.out State 0: phrase ::= * foo AND A B C foo phrase ::= * foo AND A B C bar foo ::= * BIRD| CAT| DOG BIRD shift 2 shift 2 phrase accept foo shift 5 State 1: phrase ::= foo AND A B C * foo phrase ::= foo AND A B C * bar foo ::= * BIRD| CAT| DOG bar ::= * CAT| DOG BIRD shift 2 shift 2 reduce 134589256 ** Parsing conflict ** CAT shift 4 foo shift 9 bar shift 10 valgrind output: ==11245== Invalid read of size 1 ==11245== at 0x8049109: SetAdd (lemon.c:4080) ==11245== by 0x804B083: FindFirstSets (lemon.c:649) ==11245== by 0x8051919: main (lemon.c:1464) ==11245== Address 0x416980F is 0 bytes after a block of size 7 alloc'd ==11245== at 0x401F396: malloc (in /usr/lib/valgrind/x86-linux/vgpreload_memcheck.so) ==11245== by 0x804AEF3: SetNew (lemon.c:4057) ==11245== by 0x804AF7D: FindFirstSets (lemon.c:617) ==11245== by 0x8051919: main (lemon.c:1464) ==11245== ==11245== Invalid write of size 1 ==11245== at 0x804910C: SetAdd (lemon.c:4081) ==11245== by 0x804B083: FindFirstSets (lemon.c:649) ==11245== by 0x8051919: main (lemon.c:1464) ==11245== Address 0x416980F is 0 bytes after a block of size 7 alloc'd ==11245== at 0x401F396: malloc (in /usr/lib/valgrind/x86-linux/vgpreload_memcheck.so) ==11245== by 0x804AEF3: SetNew (lemon.c:4057) ==11245== by 0x804AF7D: FindFirstSets (lemon.c:617) ==11245== by 0x8051919: main (lemon.c:1464) ==11245== ==11245== Invalid read of size 4 ==11245== at 0x8048BF4: actioncmp (lemon.c:365) ==11245== by 0x8048DBC: merge (lemon.c:1577) ==11245== by 0x8048E6D: msort (lemon.c:1623) ==11245== by 0x80512C1: FindActions (lemon.c:966) ==11245== by 0x805194E: main (lemon.c:1479) ==11245== Address 0x416B298 is 4 bytes after a block of size 36 alloc'd ==11245== at 0x401F396: malloc (in /usr/lib/valgrind/x86-linux/vgpreload_memcheck.so) ==11245== by 0x804ABF2: State_new (lemon.c:4503) ==11245== by 0x8051015: getstate (lemon.c:759) ==11245== by 0x805113A: buildshifts (lemon.c:829) ==11245== by 0x805104E: getstate (lemon.c:766) ==11245== by 0x805113A: buildshifts (lemon.c:829) ==11245== by 0x805104E: getstate (lemon.c:766) ==11245== by 0x805113A: buildshifts (lemon.c:829) ==11245== by 0x805104E: getstate (lemon.c:766) ==11245== by 0x805113A: buildshifts (lemon.c:829) ==11245== by 0x805104E: getstate (lemon.c:766) ==11245== by 0x805113A: buildshifts (lemon.c:829) ==11245==_2007-Dec-12 18:00:29 by anonymous:_ {linebreak} Another grammar with parsing conflicts, different valgrind error:
article ::= blocks FIN. blocks ::= block. blocks ::= blocks block. block ::= NEWLINE. block ::= stanza. block ::= heading. block ::= stanza heading. heading ::= HEADING_START text HEADING_END. stanza ::= text. stanza ::= stanza NEWLINE text. text ::= textpiece. text ::= text textpiece. textpiece ::= TEXT. textpiece ::= LINK. ==11727== Conditional jump or move depends on uninitialised value(s) ==11727== at 0x804ADBB: CompressTables (lemon.c:3947) ==11727== by 0x8051A2C: main (lemon.c:1482) 3925 void CompressTables(lemp) 3926 struct lemon *lemp; 3927 { 3928 struct state *stp; 3929 struct action *ap, *ap2; 3930 struct rule *rp, *rp2, *rbest; 3931 int nbest, n; 3932 int i; 3933 int usesWildcard; 3934 3935 for(i=0; i---- _2007-Dec-12 18:14:11 by anonymous:_ {linebreak} "./lemon src/parse.y" also produces: ==12904== Conditional jump or move depends on uninitialised value(s) ==12904== at 0x80523AF: CompressTables (lemon.c:3947) ==12904== by 0x804B015: main (lemon.c:1482) ---- _2007-Dec-12 19:00:41 by anonymous:_ {linebreak} This appears to remedy the Conditional jump error for parse.y. The read/write errors in first grammar remains.nstate; i++){ 3936 stp = lemp->sorted[i]; 3937 nbest = 0; 3938 rbest = 0; 3939 usesWildcard = 0; 3940 3941 for(ap=stp->ap; ap; ap=ap->next){ 3942 if( ap->type==SHIFT && ap->sp==lemp->wildcard ){ 3943 usesWildcard = 1; 3944 } 3945 if( ap->type!=REDUCE ) continue; 3946 rp = ap->x.rp; 3947 if( rp->lhsStart ) continue;
--- tool/lemon.c 5 Oct 2007 16:16:36 -0000 1.51 +++ tool/lemon.c 12 Dec 2007 19:02:37 -0000 @@ -2100,6 +2100,7 @@ to follow the previous rule."); psp->prevrule = 0; }else{ int i; + rp->lhsStart = 0; rp->ruleline = psp->tokenlineno; rp->rhs = (struct symbol**)&rp[1]; rp->rhsalias = (char**)&(rp->rhs[psp->nrhs]);---- _2007-Dec-13 23:44:45 by anonymous:_ {linebreak} I originally thought this reading the unitialized memory for rp->lhsStart lemon parser bug was completely inconsequential, but now I see if I apply the following patch and run lemon against parse.y it generates a completely different parse.c file. If the lemon is built with the following patch and your run parse.y against it, lemon will generate a parse.c that is 275868 bytes in size:
diff -u -3 -p -r1.51 lemon.c --- tool/lemon.c 5 Oct 2007 16:16:36 -0000 1.51 +++ tool/lemon.c 13 Dec 2007 23:37:39 -0000 @@ -2100,6 +2100,7 @@ to follow the previous rule."); psp->prevrule = 0; }else{ int i; + rp->lhsStart = 0xffff; // *** change to zero and rerun rp->ruleline = psp->tokenlineno; rp->rhs = (struct symbol**)&rp[1]; rp->rhsalias = (char**)&(rp->rhs[psp->nrhs]);While if the rp->lhsStart value is set to 0, it will produce a completely different parse.c file of 122960 bytes. #cfe8bd 2832 code fixed 2007 Dec anonymous 2007 Dec 1 1 Data corruption with UPDATE or DELETE clause ... On any UPDATE or DELETE operation, if a side effect causes other rows in the same table to be deleted, a stack leak can occur. In rare circumstances, database corruption can result. Side effect deletes might be caused by an OR REPLACE clause or by triggers. This bug appears to be in all prior versions of SQLite 3. The original problem description follows: ----- My scenario is rather simple - I have products table, and details table. Producs have unique column called code. Then I have transform table to renumber my products table. I used update or replace constraint, and my data got corrupted. Here's example, I will try to rewrite column names into english, sorry if I make some typo here: sql "create table products (code text primary key, former_code text, typy text, name text, warehouse text)" sql "create table transform (code text, name text, new_code text, former_code text, type text)" no indices (there are in newer version of my code, but simpler the case the better) And now sql code, which corrupts my data (contains a bit of REBOL code):
sql trim/lines { update or replace products set code = (select new_code from transform where transform.code=products.code), type = (select type from transform where transform.code=products.code) where exists (select code from transform where transform.code=products.code) }So - I just wonder - I am not fluent in SQL, so I am not sure I should update primary key in products that way? I expect this code would either update codes found for transformation, or it would fail, because such code to translate to would be already present on table, so that would be caught by "or replace" constraint, and such item would be replaced. So - what you suggest now? Is that a bug? Or am I behaving really badly to my data? :-) Tried with 3.5.2, 3.5.3, Windows Vista 32bit .... PS: This text is from my original post to ml - tried to subscribe to ml two times, no luck receiving any message, so posting directly to bugbase. Sorry if the bug is already known ... _2007-Dec-11 14:34:41 by drh:_ {linebreak} What makes you think your data is being corrupted, as opposed to merely transformed in exactly the way you have requested? In other words, why do you think this is a bug in SQLite? ---- _2007-Dec-12 08:22:19 by anonymous:_ {linebreak} If you can see that instead of product code you get characters you even can't write on your keyboard probably, then I think data is being corrupted :-) Here's a screenshot: {link: http://www.xidys.com/pekr/sqlite-insert-or-update-data-corruption.jpg corrupted data?} Cheers, Petr ---- _2007-Dec-12 08:35:14 by danielk1977:_ {linebreak} Can you post a database file and the SQL statement that is causing the corruption? This is likely an encoding problem. Are you inserting UTF-8 text into the database? And handling the returned data as UTF-8? ---- _2007-Dec-12 08:56:10 by anonymous:_ {linebreak} OK, before I post some data, I will try to describe what I did: In products table I have product numbers starting with "9", which are going to be translated into other numbers: 9xxxx.xx ---> 1xxxx.xx 9xxxx.xxA --> 1xxxx.xxA 9xxxx.xxBZ--> 9xxxx.xx I import all data from Excel .csv files. Encoding should be OK imo, as data live in one file. However, I noticed "bug" in my logic. Simply put, to make my life easier, I imported 9xxxx.xxBZ codes into my transform table, thinking that when those codes are at the end of the table, those are OK. But - my update clause (posted in my first request), does not "join data" that safe way, so actually what happens is - if the clause reaches 9xxxx.xxBZ code, it updates it to 9xxxx.xx code, but this code is then once again translated to 1xxxx.xx later in the process. If I don't import 9xxxx.xxBZ codes to the end of the table, my data remain being OK. Where can I send my database? I don't want it being public. Thanks a lot ... Petr ---- _2007-Dec-12 10:25:21 by anonymous:_ {linebreak} OK, here is a reduced recordset without sensitive data. {link: http://www.xidys.com/pekr/inventura.zip corruption data example} Just open the database and apply query as stored in query.txt ---- _2007-Dec-12 11:11:11 by anonymous:_ {linebreak} I can confirm the database corruption as provided in the sample. Before running the SQL in query.txt, =pragma integrity_check= returns 'ok', afterwards it returns these errors: rowid 156 missing from index sqlite_autoindex_products_1 rowid 159 missing from index sqlite_autoindex_products_1 rowid 160 missing from index sqlite_autoindex_products_1 rowid 162 missing from index sqlite_autoindex_products_1 rowid 163 missing from index sqlite_autoindex_products_1 Tested with CVS [4603]. ---- _2007-Dec-12 15:41:33 by anonymous:_ {linebreak} Regarding the test case in Check-in [4614], it produces the same expected result for both 3.5.3 and the latest CVS. No sign of database corruption or valgrind error for 3.5.3 on Linux. Are you certain this isolates the problem? ---- _2007-Dec-12 15:56:02 by drh:_ {linebreak} tkt2832.test provokes the problem if you use the OP_StackDepth opcode from check-in [4612]. The change in [4612], together with additional enhancements that will go in as part of this fix, is designed to head off this kind of bug in the future by making the VDBE very unforgiving to the kinds of code generator errors that caused this problem. #cfe8bd 2829 code fixed 2007 Dec anonymous 2007 Dec 1 1 Win32: temp files left over in temp area Seems that temp tables generated on the fly for large group by or order by queries do not have their files removed on CloseHandle. This is because the CreateFile is not done with the right flag. Result is that a lot of files are left over in temp directory, even when queries are run properly. This patch seems to fix it (added: SQLITE_OPEN_DELETEONCLOSE): if( flags & (SQLITE_OPEN_TEMP_DB | SQLITE_OPEN_TEMP_JOURNAL - | SQLITE_OPEN_SUBJOURNAL) ){ + | SQLITE_OPEN_SUBJOURNAL | SQLITE_OPEN_DELETEONCLOSE) ){ _2007-Dec-10 19:54:25 by drh:_ {linebreak} Yikes! I just checked and I have 174260 SQLite temp files in my /var/tmp folder! And cleaning up such a mess is challenging. You cannot do "rm etilqs_*" because the argument list is too long. Even "ls etilqs_*" doesn't work. Here is what I'm doing: ls -U | grep etliqs_ | while read i; do rm $i; done That will have to run for 20 minutes or so to clear out all of the dead files. This problem is serious enough to warrant a new release once we get it fixed. ---- _2007-Dec-10 20:06:32 by anonymous:_ {linebreak} I don't see these temp file on UNIX - which OS is affected by this bug and under what circumstance? ---- _2007-Dec-10 20:50:20 by drh:_ {linebreak} Hmmm. I finally got my /var/tmp folder cleaned up. But now when I rerun the regression suite, no new temp files appear. Perhaps those 174260 temp files came from some older bug that has now been fixed. ---- _2007-Dec-10 21:24:27 by anonymous:_ {linebreak} Did you by chance catch a glimpse of the date of the most recent undeleted tmp files on UNIX? ---- _2007-Dec-11 03:31:38 by drh:_ {linebreak} Sadly, I did not observe the mtimes on any of the files in /var/tmp. Recall that ls was not working very well for me (probably due to the large number of files). I was having to use the -U open (to prevent sorting) in order to get ls to work at all. And I did not have the presence of mind to add a -l to that. Oh well.... ---- _2007-Dec-11 05:26:45 by anonymous:_ {linebreak} find is your friend: find /var/tmp -type f -iname "etliqs_*" -print0 | xargs -r0 rm (yes, you can simplify this a whole lot if you make some assumptions but why bother be unsafe?) #c8c8c8 2828 code closed 2007 Dec anonymous 2007 Dec 1 2 SQLITE_OMIT_SUBQUERY compile error: sqlite3CodeSubselect() missing. The new sqlite3FindInIndex() calls sqlite3CodeSubselect(), but this is kept out if SQLITE_OMIT_SUBQUERY is defined. As it stands now, SQLite does not compiled with SQLITE_OMIT_SUBQUERY defined. #cfe8bd 2827 code fixed 2007 Dec anonymous 2007 Dec 1 1 make test: vacuum2.test did not close all files: 1 Latest unmodified CVS source tree. Run "make test" to see the error.
vacuum2-1.1... Ok vacuum2-2.1... Ok vacuum2-2.1... Ok vacuum2-3.1... Ok vacuum2-3.2... Ok vacuum2-3.3... Ok vacuum2-3.4... Ok vacuum2-3.5... Ok vacuum2-3.6... Ok vacuum2-3.7... Ok vacuum2-3.13... Ok vacuum2-3.14... Ok vacuum2-3.15... Ok vacuum2-3.16... Ok vacuum2-3.17... Ok vacuum2.test did not close all files: 1 ... 1 errors out of 38209 tests Failures on these tests: vacuum2.test All memory allocations freed - no leaks Maximum memory usage: 14164890 bytes make: *** [test] Error 1Note: this does *not* happen if you only run vacuum2.test directory:
$ ./testfixture test/vacuum2.test vacuum2-1.1... Ok vacuum2-2.1... Ok vacuum2-2.1... Ok vacuum2-3.1... Ok vacuum2-3.2... Ok vacuum2-3.3... Ok vacuum2-3.4... Ok vacuum2-3.5... Ok vacuum2-3.6... Ok vacuum2-3.7... Ok vacuum2-3.13... Ok vacuum2-3.14... Ok vacuum2-3.15... Ok vacuum2-3.16... Ok vacuum2-3.17... Ok 0 errors out of 16 tests All memory allocations freed - no leaks Maximum memory usage: 85541 bytes#c8c8c8 2824 code closed 2007 Dec anonymous 2007 Dec 1 1 enable shared cache problem System info: -Celeron M. -Windows Xp. -Single process. -No thread. Code: int WINAPI WinMain(HINSTANCE h1, HINSTANCE h2, LPSTR lpCMD, int nSCMD) { sqlite3* db; sqlite3_stmt * stm; sqlite3_enable_shared_cache(1); sqlite3_open("db", &db); sqlite3_prepare_v2(db, "ATTACH DATABASE db2 AS d", 24, &stm, 0); sqlite3_step(stm); } The problem: the sqlite3_step(stm) execute for an infinite time. MA _2007-Dec-06 07:56:12 by danielk1977:_ {linebreak} I just tried the following program with the cvs version: #include "sqlite3.h" int main (int argc, char **argv){ sqlite3* db; sqlite3_stmt *stm; sqlite3_enable_shared_cache(1); sqlite3_open("db", &db); sqlite3_prepare_v2(db, "ATTACH DATABASE db2 AS d", 24, &stm, 0); sqlite3_step(stm); return 0; } And the sqlite3_step() did not block for any amount of time. Can you confirm that the code above produces the bug in your environment? Thanks. ---- _2007-Dec-07 13:59:02 by anonymous:_ {linebreak} I have ran more test but the problem is never arised. I think it was generated from code or configuration properties from my system, outside sqlite. MA #c8c8c8 2823 code closed 2007 Dec anonymous 2007 Dec 1 1 query not return values I have a database with this description:
and contain this data:CREATE TABLE [coletores] ( [coletor] INTEGER NOT NULL ON CONFLICT ABORT PRIMARY KEY, [senha] NVARCHAR2(20) NOT NULL ON CONFLICT ABORT); CREATE UNIQUE INDEX [SenhaUnica] ON [coletores] ([senha]);
When i run the query: "select coletor, senha from coletores where senha = '43043553'" query not return values. Works when I try it. Perhaps you have some whitespace on one side of the '43043553' value in the database. Whitespace is significant to SQLite. #cfe8bd 2820 code fixed 2007 Dec anonymous 2007 Dec 1 1 rollback doesn't work Code:coletor | senha 1 43043553
prints: database table is locked In sqlite rolback is called (I'v checked in older version), but in test.db there is no table 't'. Tested on win2k. _2007-Dec-03 16:31:50 by anonymous:_ {linebreak} What is the desired outcome? ---- _2007-Dec-03 18:28:38 by anonymous:_ {linebreak} No error. Or if error is reported during delete, nothing should be deleted. #c8c8c8 2817 code closed 2007 Dec danielk1977 2007 Dec danielk1977 1 1 Temp-tables with the same name as database tables can cause corruption Suspect a problem in the CREATE INDEX statement here: CREATE TEMP TABLE abc(a, b, c); CREATE TABLE main.abc(a, b, c); CREATE INDEX main.abc_i ON abc(a, b, c); The attached script demonstrates the problem. #f2dcdc 2813 build active 2007 Nov anonymous 2007 Nov 1 1 compile error on Windows CE environment: visual c++ 2005 window ce 6.0 customize sdk sqlite-amalgamation-3_5_3 I get error: Error 27 error C2040: 'localtime' : 'tm *(const time_t *)' differs in levels of indirection from 'int ()' d:\SubProjects\Sqlite\sqlite3.c 18574 but if I add code in line 7095: struct tm *__cdecl localtime(const time_t *t); then Success! #f2dcdc 2810 code active 2007 Nov anonymous 2007 Nov 1 1 Unregistered collation problems with simple subselects As discussed on the mailing-list: Imagine that a SQLite3 database opened in a custom application with a registered a collation sequence named "unknown" has created the following table: CREATE TABLE a (b COLLATE unknown); Now open this table in the default SQLite3 CLI. Up to here, everything works as expected. Simple queries like "SELECT * FROM a;" work fine. But subselects, in their most basic form and with no sorting or comparisons, result in an error: sqlite> INSERT INTO a VALUES ('one'); sqlite> SELECT * FROM a, (SELECT * FROM a); SQL error: no such collation sequence: unknown sqlite> SELECT * FROM (SELECT * FROM a); SQL error: no such collation sequence: unknown sqlite> SELECT *, * FROM a; one|one This is surprising because the collation sequence should not matter to the queries. In fact, the union without the subselect works just fine and without errors. To demonstrate, here is the explain output of a table with a registered collation sequence. No mention of the collation name here: sqlite> CREATE TABLE b (b collate nocase); sqlite> EXPLAIN SELECT * FROM b, (SELECT * FROM b); 0|Goto|0|17| 1|Integer|0|0| 2|OpenRead|0|3| 3|SetNumColumns|0|1| 4|Integer|0|0| 5|OpenRead|2|3| 6|SetNumColumns|2|1| 7|Rewind|0|14| 8|Rewind|2|13| 9|Column|0|0| 10|Column|2|0| 11|Callback|2|0| 12|Next|2|9| 13|Next|0|8| 14|Close|0|0| 15|Close|2|0| 16|Halt|0|0| 17|Transaction|0|0| 18|VerifyCookie|0|4| 19|TableLock|0|3|b 20|Goto|0|1| 21|Noop|0|0| #f2dcdc 2809 code active 2007 Nov anonymous 2007 Nov 1 1 PRAGMA collation_list shows unregistered collations As presented on the mailing list: Imagine that a SQLite3 database opened in a custom application with a registered a collation sequence named "unknown" has created the following table: CREATE TABLE a (b COLLATE unknown); Now open this table in the default SQLite3 CLI. Up to here, everything works as expected. Next issue "PRAGMA collation_list;" and notice that "unknown" lists next to the other registered collations, even though "unknown" is not registered with the default SQLite3 CLI: sqlite> PRAGMA collation_list; 0|unknown 1|NOCASE 2|BINARY Responses from the mailing list indicate that this is not the expected behaviour. "PRAGMA collation_list;" should list registered collations only. _2007-Nov-28 16:12:17 by anonymous:_ {linebreak} I don't think this is a bug. If the CLI is not aware of the collation, it should not process the query that makes use of the collation because it would certainly be wrong if it simply ignored the collation. This is not unlike a user-registered SQL function that does not exist in the CLI. I would not expect or want the sqlite3 CLI to ignore the unknown function, nor would I want the CLI to process queries ignoring the custom collation. #cfe8bd 2805 code fixed 2007 Nov anonymous 2007 Nov 1 1 Checkin [4573] Home grown recursive mutexes Checkin [4573] would only work on uniprocessors, not SMP hardware. You cannot implement recursive mutexes without some sort of memory barrier or atomic "test and set" instruction. The reason being the each SMP CPU has its own memory image that is not synchronized with main memory until there is a memory barrier or lock/spinlock. Take a look at NPTL or the previous incarnation, LinuxThreads, to see how difficult it is to implement a reliable and efficient recursive mutex on different CPU architectures. It simply can't be done without dropping into architecture-specific assembler. _2007-Nov-28 17:46:31 by anonymous:_ {linebreak} Solaris 2.6 was mentioned in the original homegrown recursive mutex implementation. Be aware that some Sparc machines offer cache coherency, while others do not. So it's a run-time hardware-specific thing, not an OS thing or a compile-time thing. Here is a fairly comprehensive list of cache coherency info (circa 2003) for various CPUs: http://linux.derkeiler.com/Mailing-Lists/Kernel/2003-09/3187.html #c8c8c8 2804 code closed 2007 Nov anonymous 2007 Dec 1 1 PRAGMA legacy_file_format=OFF setting lost with VACUUM It seems that the PRAGMA legacy_file_format=OFF and the ability to use DESC indexes is lost after two VACUUMs and reconnects.#include#include "sqlite3.h" sqlite3 *db; static int callback(void *NotUsed, int argc, char **argv, char **azColName){ sqlite3_exec(db, "DROP TABLE t", 0, 0, 0); printf("%s\n", sqlite3_errmsg(db)); return 0; } int main() { sqlite3_open("test.db", &db); sqlite3_exec(db, "CREATE TABLE IF NOT EXISTS t(a INT)", 0, 0, 0); sqlite3_exec(db, "select name, type from sqlite_master", &callback, 0, 0); sqlite3_close(db); return 0; }
$ rm -f foo.db $ ./sqlite3-3.5.3.bin foo.db SQLite version 3.5.3 Enter ".help" for instructions sqlite> PRAGMA legacy_file_format; 1 sqlite> PRAGMA legacy_file_format=OFF; sqlite> PRAGMA legacy_file_format; 0 sqlite> CREATE TABLE abc(a,b,c); sqlite> CREATE INDEX abc_i on abc(b desc, c asc, a desc); sqlite> explain query plan select * from abc order by b desc, c asc, a desc; 0|0|TABLE abc WITH INDEX abc_i ORDER BY sqlite> vacuum; sqlite> .qIn the next connection we see that the legacy file format reverted back to 1, but the DESC index is still picked up...
$ ./sqlite3-3.5.3.bin foo.db SQLite version 3.5.3 Enter ".help" for instructions sqlite> PRAGMA legacy_file_format; 1 sqlite> explain query plan select * from abc order by b desc, c asc, a desc; 0|0|TABLE abc WITH INDEX abc_i ORDER BY sqlite> vacuum; sqlite> .qBut if connected to another time, the DESC index is not picked up...
$ ./sqlite3-3.5.3.bin foo.db SQLite version 3.5.3 Enter ".help" for instructions sqlite> PRAGMA legacy_file_format; 1 sqlite> explain query plan select * from abc order by b desc, c asc, a desc; 0|0|TABLE abcActually, this works correctly, though the documentation could be clearer. The legacy_file_format pragma does not tell you the file format of the current database. Rather, it tells you what file format will be used when creating new databases (for example, using ATTACH). The behavior shown above is correct. There is no pragma to tell you what the file format of the current database is. The documentation has been clarified. Two new test cases have been inserted into the test suite to verify that the operation is correct. #c8c8c8 2802 code closed 2007 Nov anonymous 2007 Dec 1 1 SELECT UNION and EXCEPT seem to have a memory allocation bug It seems that SELECT UNION and EXCEPT operators work wrong. It looks like memory access overflow or integer overflow. Well, I have a large text file 'text2.text' (~1.5mg), which contains: 20091208{linebreak} 20216510{linebreak} 20149599{linebreak} 20250484{linebreak} 20211910{linebreak} 20199980{linebreak} ........{linebreak} 20183528{linebreak} 20151994{linebreak} 20179184{linebreak} 20077607{linebreak} 182500 lines in total. The meaning of these numbers is a date in YYYYMMDD format. Numbers are random and unsorted at all. The next file is 'test1.text' is the same as 'test2.text' but without the last 1000 lines. The 'diff' between these files shows the difference in last 1000 lines. Than I execute script: @echo off{linebreak} if exist test.db del test.db{linebreak} rem{linebreak} rem Load data into table 'a'{linebreak} rem It prints 181500{linebreak} sqlite3 test.db "create table a (i1 text)"{linebreak} sqlite3 test.db ".import test1.text a"{linebreak} sqlite3 test.db "select count(*) from a"{linebreak} rem{linebreak} rem Load data into table 'b'{linebreak} rem It prints 182500{linebreak} sqlite3 test.db "create table b (i1 text)"{linebreak} sqlite3 test.db ".import test2.text b"{linebreak} sqlite3 test.db "select count(*) from b"{linebreak} rem{linebreak} rem PROBLEM IS HERE{linebreak} rem Now I am trying to merge the tables 'a' and 'b' via UNION.{linebreak} rem It should print '182500' but it prints '16384' !!!{linebreak} sqlite3 test.db "create table c (i1 text)"{linebreak} sqlite3 test.db "insert into c select * from b union select * from a"{linebreak} sqlite3 test.db "select count(*) from c"{linebreak} rem{linebreak} rem Just a check that EXCEPT also has the same bug.{linebreak} rem It should print 1000, but it prints '0' !!!{linebreak} sqlite3 test.db "select count(*) from (select * from c except select * from b)"{linebreak} I suggest that UNION and EXCEPT has some kind of memory overflow. And also it depends on how strong the numbers in the source text file are shuffled and how wide is the range of the numbers. I can attach my files 'test1.text' and 'test2.text' if needed. My system: CPU Intel Core 2 Duo, 1.06GHz, 2GB RAM. Operating system: Windows XP SP2 (Version 5.1.2600) _2007-Nov-27 23:45:42 by drh:_ {linebreak} UNION and EXCEPT both imply DISTINCT. Since you are dealing with dates, it seems unlikely that you really have 182500 distinct dates. That would span 500 years and your sample data seems to all be from the early part of the 21st century. 16384 distinct dates would span 44 years, which seems more reasonable, given your sample data. The fact that the number is a power of two probably results from a poor-quality PRNG used to generate the same data. You can use UNION ALL to to get the union of two tables without the implied DISTINCT. There is no way to omit the implied DISTINCT from EXCEPT. You will probably need to refactor your query to use the NOT IN operator. ---- _2007-Dec-05 20:10:08 by anonymous:_ {linebreak} Actually, in the real data, when I've got this situation on EXCEPT and UNION, there are some extra fields in the database. Except 'date', there were text fields: 'amount', 'owner', 'descr', 'labels' (it's personal cash accouting database). When the test data was generatated, all fields could have duplicate values, but 'amount' field was generated like: $amount = sprintf( "%d.%02d", rand( 1, 10000 ), rand( 0, 99 ) ); so, it can take 9999999 possible values, which is more than 16384 for sure. And the behaviour of the EXCEPT was completely the same as I've described the the original message of this bug report. That's why I've decided that the key of the problem is just in the amount of data, not in values, and I've reduced the database structure for the bug report to make it more clear (I left only one field 'date'), but the 'bug' (I mean this strange behaviour of the EXCEPT and UNION) still happens in the reduced database structure (with only one field 'date'). So DISTINCT function will not merge all values of 'amount' field into 16384 unique values. What do you think? #f2dcdc 2791 code active 2007 Nov anonymous 2007 Nov 1 1 Allow building FTS[123] as part of sqlite library with configure See attached patch. #cfe8bd 2784 code fixed 2007 Nov anonymous 2007 Nov 1 3 Low memory double free and SEGV from flattenSubquery() Low memory double free and SEGV from flattenSubquery() sqlite3SrcListAppend() in build.c can call sqlite3DbRealloc() which can fail under low-memory conditions. In this case it returns zero. It is called by flattenSubquery() in select.c in the section following: /* Move all of the FROM elements of the subquery into the ** the FROM clause of the outer query. Immediately (3 lines) after this call (after the loop) the return value is dereferenced in the start of the next loop: pSrc = sqlite3SrcListAppend(db, pSrc, 0, 0); } p->pSrc = pSrc; for(i=pSrc->nSrc-1; i-extra>=iFrom; i--){ I am also getting a 'double free' error during the cleanup in sqlite3SrcListAppend(), where the old (unextended) structure is freed by sqlite3SrcListDelete(). Apparently one of the pItem->zName strings has already been freed by the lines: sqlite3DeleteTable(pSubitem->pTab); sqlite3_free(pSubitem->zDatabase); sqlite3_free(pSubitem->zName); sqlite3_free(pSubitem->zAlias); immediately before the call in this section of flattenSubquery(). Clearing these pointers after freeing gets rid of the double free error. I can't offer a simple solution to the SEGV The SQL statement is "SELECT * FROM v10_11 LEFT JOIN t9 ON( a=x );" from the join-8.3 runtest. #c8c8c8 2782 code closed 2007 Nov anonymous 2007 Nov 1 1 bind_text(...) into transaction In Transaction i insert some values, using sqlite3_bind_text(...) The first row has undef value(some times it's empty), but other rows have normal values. _2007-Nov-21 13:50:25 by drh:_ {linebreak} If you can describe some kind of problem with SQLite, we will be happy to look into the matter. Your description above sounds like a bug in your own code though. What about this problem makes you think it is a bug in SQLite? #c8c8c8 2778 code closed 2007 Nov anonymous 2007 Nov adixon 1 1 Error 0 CREATE PROCEDURE Driver_Daily_Activity { @comp_code varchar(8), @begin_date datetime, @end_date datetime, @emp_short_name varchar(8), @emp_id int, @driver_time_in output, @driver_time_out output, @driver_beaktime_in output, @driver_beaktime_out output, @driver_lunchtime_in output, @driver_lunchtime_out output, @total_break_time output } AS select @driver_time_in =driver_datetime_in,@driver_time_out =driver_datetime_out from driver_daily_log with (nolock) where company_code=@comp_code and emp_id=@emp_id and driver_datetime_in >=@begin_date and driver_datetime_out<@end_date and driver_datetime_out is not null select @driver_breaktime_in=substring((CAST(break_time_in AS varchar(20))),12,8) ,@driver_breaktime_out=substring((CAST(break_time_out AS varchar(20))),12,8) from driver_daily_break_log with (nolock) where company_code=@comp_code and emp_id=@emp_id and driver_status='OB'. select @driver_lunchtime_in=substring((CAST(break_time_in AS varchar(20))),12,8) ,@driver_lunchtime_out=substring((CAST(break_time_out AS varchar(20))),12,8) from driver_daily_break_log with (nolock) where company_code=@comp_code and emp_id=@emp_id and driver_status='OL'. select @total_break_time =sum(total_break_time) from driver_daily_break_log with (nolock) where company_code=@comp_code and emp_id=@emp_id. This procedure is giving me the following error : SQL State 42000: Error 0 : Syntax error or access violation SQL identifiers may not contain the "@" character. #c8c8c8 2777 code closed 2007 Nov anonymous 2007 Nov shess 1 1 FTS[23] compile bug You need to #include "sqlite3.h" to pick up the sqlite3_malloc() declaration: ./ext/fts3/fts3_hash.c: In function "fts3HashMalloc": ./ext/fts3/fts3_hash.c:38: warning: initialization makes pointer from integer without a cast Also, can you move the following: #ifndef SQLITE_ENABLE_BROKEN_FTS2 #error fts2 has a design flaw and has been deprecated. #endif to be after this line: #if !defined(SQLITE_CORE) || defined(SQLITE_ENABLE_FTS2) in fts2.c? That way you can build fts3 while having fts2 objects in the Makefile without defining -DSQLITE_ENABLE_BROKEN_FTS2 for an fts3-only build. _2007-Nov-23 18:21:04 by anonymous:_ {linebreak} This error still exists in FTS2 and FTS3: ./ext/fts3/fts3_hash.c: In function "fts3HashMalloc": ./ext/fts3/fts3_hash.c:38: warning: initialization makes pointer from integer without a cast On some 64-bit systems this will cause FTS to crash because an int is smaller than a pointer. Nevermind - it was fixed. #cfe8bd 2774 code fixed 2007 Nov shess 2007 Nov shess 1 1 Crash merging empty OR queries in fts3. create virtual table test using fts2(a); insert into test (a) values ('alpha'); insert into test (a) values ('beta'); select a from test where test match ('a:gamma* OR a:delta*'); sqlite3: ../sqlite/ext/fts3/fts3.c:478: dataBufferAppend: Assertion `nSource>0 && pSource!=((void *)0)' failed. "Workaround" is to compile with -DNDEBUG=1, but the person who reported this to me was using the fts2 dll from sqlite.org. Fix on the way. #f2dcdc 2770 code active 2007 Nov anonymous 2007 Nov 1 1 Problem with BLOB in 3.5.x ? After I've switched from 3.3.18 to 3.5.2, selecting from table which contains BLOB LONGER THAN ABOUT 990 BYTES returns error "SQL logic error or missing database" after call to _sqlite3_step(). I'm using preprocessed sources downloaded from here. DEBUG build of preprocessed sources works correctly, problem is only in RELEASE build. I'm using VC6.0 to compile. Any idea what could be wrong? Thank you! Can you try to reproduce this with the sqlite shell tool? Thanks. Large blobs work for me with both release and debug builds (not msvc though, gcc/linux). ---- _2007-Nov-12 18:41:37 by anonymous:_ {linebreak} sqlite3.exe provided here works with the database. Problem is only with release build (static library linked into test application). Here is test app which exits with "Error 1" in release build: int main(int argc, char* argv[]) { int rc; sqlite3* db; sqlite3_stmt* stmt; rc = sqlite3_open("n2.db3", &db); rc = sqlite3_prepare(db, "CREATE TABLE [ttt] ([bbb] BLOB)", -1, &stmt, 0 ); rc = sqlite3_step(stmt); rc = sqlite3_reset(stmt); char text[10000],query[20000]; strnset(text,'a',sizeof(text)-1); sprintf(query,"insert into [ttt] values (?)"); rc = sqlite3_prepare(db, query, -1, &stmt, 0 ); rc = sqlite3_bind_blob(stmt,1,text,sizeof(text), SQLITE_TRANSIENT); rc = sqlite3_step(stmt); rc = sqlite3_reset(stmt); rc = sqlite3_prepare(db, "select * from ttt", -1, &stmt, 0 ); rc = sqlite3_step(stmt); if (rc == SQLITE_ROW) { printf("%s: OK",sqlite3_column_text(stmt,1)); } else if (rc == SQLITE_DONE) { printf("DONE"); } else { printf("Error %d",rc); } return 0; } ---- _2007-Nov-12 18:56:24 by drh:_ {linebreak} You should be using sqlite3_finalize() instead of sqlite3_reset(). You are leaking memory. Also, you should use sqlite3_prepare_v2() to avoid problems with changing schemas. But even without those fixes, I cannot reproduce the problem on Linux. ---- _2007-Nov-12 19:39:31 by anonymous:_ {linebreak} Suggested fixes didn't help. I've tried to debug it. It fails in btree.c, line 3056: if( offset+amt > nKey+pCur->info.nData ){ /* Trying to read or write past the end of the data is an error */ return SQLITE_ERROR; } there seems to be different values in release mode. My debugger does not show values of variables in release mode, so I can be wrong, but it seems in release offset is 5 and in debug it is 4. There can be something wrong with compilation, I'll try to figure this out tomorrow. BTW compilation of static libraty in VC6.0 gives 185 warnings. I don't know if it is ok, it haven't caused problems in older sqlite ---- _2007-Nov-13 08:47:28 by anonymous:_ {linebreak} I've turned off "Maximize Speed" option - this is causing the problem. No optimizations and optimize for size seems to be working. But it still makes me nervous :(( I really don't need corrupted database and now I hope it won't slow down too much. Unfortunately old library does not implement replace function so I don't want to switch back. This could be warning to others, I'm using VC++ 6.0 SP 6. Thank you for your time. ---- _2007-Nov-22 17:20:31 by anonymous:_ {linebreak} I have exactly the same problem here (win XP, vc6 SP2) when I link against my sqlite static or dynamic library in release. I have also used boundschecker to check sqlite, and it detects many dangling pointers ! But the strange thing is that I cannot find why these pointers are dangling, here an example: In prepare.c@188 pTab = sqlite3FindTable(db, zMasterName, db->aDb[iDb].zName); Boundchecker say that zMasterName is a dangling pointer, previously released here: in build.c@711: void sqlite3StartTable( Parse *pParse, /* Parser context */ Token *pName1, /* First part of the name of the table or view */ Token *pName2, /* Second part of the name of the table or view */ int isTemp, /* True if this is a TEMP table */ int isView, /* True if this is a VIEW */ int isVirtual, /* True if this is a VIRTUAL table */ int noErr /* Do nothing if table already exists */ ){ } It does not make sens for me, maybe it a false positive from boundchecker, but it is weird. I don't know if these "errors" are related to the "blob" bug in release mode. I will try to debug these error with some "printf" in release mode. Note: The provided dll (the one from the sqlite site) does not have this "bug". ---- _2007-Nov-22 18:27:35 by anonymous:_ {linebreak} More info: It seems that there is a bug in the VC6 (SP6) compiler. In btree.c, line 3056: if( offset+amt > nKey+pCur->info.nData ){ /* Trying to read or write past the end of the data is an error */ return SQLITE_ERROR; } After adding some printf around, It seems that the "speed optimization" compilation flag of VC6 changes the code order in a way that the offset variable is miss incremented !! Two remarks: *: I've traced the calling function, sqlite3BtreeData, and the it call accessPayload with the good offset value *: VC6 produces an internal error: "fatal error C1001: INTERNAL COMPILER ERROR" in the accessPayload function, if I try to access the offset value before this line: aPayload = pCur->info.pCell + pCur->info.nHeader; A dirty workaround could be to change the code order or the local var usage. I'm trying .... #f2dcdc 2766 code active 2007 Nov drh 2007 Nov 1 1 TCL transaction started from within a query does not commit This is a problem with the TCL interface. Consider the following TCL script: file delete -force test.db test.db-journal sqlite3 db test.db db eval { CREATE TABLE t1(x,y); INSERT INTO t1 VALUES(1,2); CREATE TABLE t2(a,b); INSERT INTO t2 VALUES(8,9); } db eval {SELECT * FROM t1} { db transaction { db eval {UPDATE t2 SET a=a*2} } } The [db transaction] statement starts a transaction and it is suppose to commit the tranaction at the end of the code block. But because the transaction started while a query was active, the tranaction is unable to commit. The TCL interface never commits the tranaction nor does it give any kind of error indication. It is unclear if an error should be returned or if the commit should be deferred until outer query finishes. If the code within the [db transaction] block throws an error, we really need the transaction to rollback right away. Perhaps there should be a new API that cancels all pending queries. Perhaps a call to sqlite3_interrupt() would suffice for this. Need to investigate further.... #c8c8c8 2765 code closed 2007 Nov anonymous 2007 Nov 1 3 bad rollback if temporary table created during transaction Hi, trying to resolve a test failure in an application using pysqlite2 (2.3.5) / sqlite 3.42 for its test suite, I've been able to produce a minimal script which shows that the problem is actually in sqlite... I don't think it comes from the python wrapper (if you think it is tell me and I'll redirect it to the appropriate tracker). Anyway here is the script : import pysqlite2.dbapi2 as sqlite cnx = sqlite.connect('testdb') cursor = cnx.cursor() cursor.execute('SELECT eid from Trinfo') nb = len(cursor.fetchall()) cursor.execute("INSERT INTO Trinfo ( from_state, to_state, wf_info_for, eid, creation_date, modification_date ) VALUES ( 734, 735, 5, 758, 1, 1)") # comment this line and the final assertion is ok cursor.execute("CREATE TEMPORARY TABLE T58d712f777a7170cb8ce7d94cef119ce (C0 integer,C1 timestamp);") # get the same result with or without dropping the temp table #cursor.execute('DROP TABLE T58d712f777a7170cb8ce7d94cef119ce') cnx.rollback() cursor.execute('SELECT eid from Trinfo') nb3 = len(cursor.fetchall()) assert nb3 == nb # FAIL Notice that if you don't create the temporary table, everything run fine, else the insertion is not actually rollbacked. _2007-Nov-09 00:23:38 by drh:_ {linebreak} I tested using the following TCL script: file delete -force test.db test.db-journal sqlite3 db test.db db eval { CREATE TABLE t1(a,b,c); INSERT INTO t1 VALUES(1,2,3); INSERT INTO t1 VALUES(4,5,6); INSERT INTO t1 SELECT a*2, b*2, c*2 FROM t1; INSERT INTO t1 SELECT a*4, b*4, c*4 FROM t1; INSERT INTO t1 SELECT a*8, b*8, c*8 FROM t1; } puts "begin=[md5 [db eval {SELECT * FROM t1}]]" db eval {BEGIN} db eval {INSERT INTO t1 VALUES('hello','out','there');} db eval {CREATE TEMPORARY TABLE T58d712f777(C0 integer,C1 timestamp)} puts " mid=[md5 [db eval {SELECT * FROM t1}]]" db eval {ROLLBACK} puts " end=[md5 [db eval {SELECT * FROM t1}]]" The results are: begin=c17bc52be49269f8f2cd3a4df3caee6e mid=f86cc8ad5b925962323edc041bfaa09d end=c17bc52be49269f8f2cd3a4df3caee6e Begin and end are equal so it appears to work using the TCL interface. I also observe that there are countless tests in the SQLite test suite that verify that this sort of thing works correctly. Perhaps this is something to do with python after all. ---- _2007-Nov-11 20:48:52 by anonymous:_ {linebreak} See: http://www.initd.org/pub/software/pysqlite/doc/usage-guide.html#controlling-transactions #c8c8c8 2759 code closed 2007 Nov anonymous 2007 Nov danielk1977 1 2 crash due to 'select * from virtualTable where x is null' For virtual table T1: select * from t1 where y is NULL; causes: 1301 assert( pTerm->eOperator & (WO_EQ|WO_LT|WO_LE|WO_GT|WO_GE|WO_MATCH) ); (gdb) bt #0 0x005e17a2 in _dl_sysinfo_int80 () from /lib/ld-linux.so.2 #1 0x006217d5 in raise () from /lib/tls/libc.so.6 #2 0x00623149 in abort () from /lib/tls/libc.so.6 #3 0x0061adb1 in __assert_fail () from /lib/tls/libc.so.6 #4 0x00849534 in bestVirtualIndex (pParse=0xbfe2d750, pWC=0xbfe2d1d0, pSrc=0x9a23d2c, notReady=18446744073709551615, pOrderBy=0x0, orderByUsable=1, ppIdxInfo=0x9a372bc) at ../../../../Source/sqlite-3.5.1-hp/src/where.c:1301 #5 0x0084a989 in sqlite3WhereBegin (pParse=0xbfe2d750, pTabList=0x9a23d28, pWhere=0x9a28ed8, ppOrderBy=0xbfe2d59c) at ../../../../Source/sqlite-3.5.1-hp/src/where.c:2074 #6 0x0082c6c6 in sqlite3Select (pParse=0xbfe2d750, p=0x9a28f28, eDest=4, iParm=0, pParent=0x0, parentTab=0, pParentAgg=0x0, aff=0x0) at ../../../../Source/sqlite-3.5.1-hp/src/select.c:3118 #7 0x0081f16c in yy_reduce (yypParser=0x9a33aa8, yyruleno=104) at parse.y:368 #8 0x00820f65 in sqlite3Parser (yyp=0x9a33aa8, yymajor=1, yyminor= {z = 0x9a2e120 ";", dyn = 0, n = 1}, pParse=0xbfe2d750) at parse.c:3419 #9 0x0082e877 in sqlite3RunParser (pParse=0xbfe2d750, zSql=0x9a2e100 "select * from t1 where y is NULL;", pzErrMsg=0xbfe2d74c) at ../../../../Source/sqlite-3.5.1-hp/src/tokenize.c:449 #10 0x008246a0 in sqlite3Prepare (db=0x9920010, zSql=0x9a2e100 "select * from t1 where y is NULL;", nBytes=-1, saveSqlFlag=0, ppStmt=0xbfe2d8e0, pzTail=0xbfe2d8e4) at ../../../../Source/sqlite-3.5.1-hp/src/prepare.c:525 #11 0x00824a2c in sqlite3LockAndPrepare (db=0x9920010, zSql=0x9a2e100 "select * from t1 where y is NULL;", nBytes=-1, saveSqlFlag=0, ppStmt=0xbfe2d8e0, pzTail=0xbfe2d8e4) at ../../../../Source/sqlite-3.5.1-hp/src/prepare.c:604 #12 0x00824bf9 in sqlite3_prepare (db=0x9920010, zSql=0x9a2e100 "select * from t1 where y is NULL;", nBytes=-1, ppStmt=0xbfe2d8e0, pzTail=0xbfe2d8e4) at ../../../../Source/sqlite-3.5.1-hp/src/prepare.c:658 #13 0x0084d862 in sqlite3_exec (db=0x9920010, zSql=0x9a2e100 "select * from t1 where y is NULL;", xCallback=0x8049b23
Index: src/expr.c =================================================================== RCS file: /sqlite/sqlite/src/expr.c,v retrieving revision 1.313 diff -u -3 -p -r1.313 expr.c --- src/expr.c 18 Sep 2007 15:55:07 -0000 1.313 +++ src/expr.c 16 Oct 2007 23:54:17 -0000 @@ -1879,7 +1879,10 @@ void sqlite3ExprCode(Parse *pParse, Expr assert( pLeft ); if( pLeft->op==TK_FLOAT || pLeft->op==TK_INTEGER ){ Token *p = &pLeft->token; - char *z = sqlite3MPrintf(pParse->db, "-%.*s", p->n, p->z); + char *z = sqlite3_malloc(p->n+2); + z[0] = '-'; + memcpy(z+1, p->z, p->n); + z[p->n+1] = 0; if( pLeft->op==TK_FLOAT ){ sqlite3VdbeOp3(v, OP_Real, 0, 0, z, p->n+1); }else{_2007-Oct-17 01:34:39 by drh:_ {linebreak} FWIW, the patch is not correct. It does not work if sqlite3_malloc fails and returns a NULL pointer. But it isn't too hard to fix. I'm curious, though, what kind of program you are running where this makes a 14x speed improvement. One wonders if you couldn't do 100x faster using sqlite3_bind_double() instead. We could go through and make patches like this one all over the place, in order to make this or that special case run a little faster. But that would make the library footprint larger. The sqlite3MPrintf() call in this context is there for reasons of space efficiency, not CPU time. Some years ago, we when through this huge effort to reduce the footprint of the library by replacing the expanded string construction code like you are inserting with instances of sqlite3MPrintf(). I am reluctant to undo that effort for a single special case that could probably be handled more efficiently using sqlite3_bind_double(). ---- _2007-Oct-17 02:21:17 by anonymous:_ {linebreak} I have a script to generate ASCII SQL statements which I use in conjunction with the sqlite3 shell and MySQL to populate huge tables. I use the multi-insert patch on the mailing list for tables that hold millions of rows of negative numbers, with anywhere from 3 to 20 columns. http://www.mail-archive.com/sqlite-users%40sqlite.org/msg28337.html INSERT INTO BIGTABLE VALUES (-334712687065.09, -334712687065.12, -334712687065.13), (-334712687065.09, -334712687065.12, -334712687065.13), ... 10 thousand rows ... (-334712687065.09, -334712687065.12, -334712687065.13), (-334712687065.09, -334712687065.12, -334712687065.13); With the mulit-insert patch and the patch in this ticket, it is 25% faster for inserting than using the equivalent INSERT commands within a transaction: BEGIN INSERT INTO BIGTABLE VALUES(-334712687065.09, -334712687065.12, -334712687065.13); ... 10 thousand rows ... INSERT INTO BIGTABLE VALUES(-334712687065.09, -334712687065.12, -334712687065.13); COMMIT; I have to have no more than 10,000 rows per insert because it blows the stack due to sqlite3Select -> multiSelect recursion. I had to bump a few other sqliteLimits as well to get more than a few thousand rows at a time. I suppose the replacement of the sqlite3MPrintf could be useful in other scenarios as well. ---- _2007-Oct-17 02:27:30 by anonymous:_ {linebreak} The bind double approach is not 100X faster, by the way. It's only around 1.3X faster than the individual ASCII inserts in my tests. But it doesn't matter for my purposes, because it's not MySQL compatible anyway. ---- _2007-Oct-17 03:04:11 by anonymous:_ {linebreak} If the string were not pre-prepended with a '-' char, but instead parsed into a double, and the double simply negated it would be much faster and avoid a needless memory allocation/deallocation. To facilitate this, OP_Real would have to be changed or a new OP code created to accommodate an 8 byte double argument - possibly via straddling P1 and P2. ---- _2007-Oct-23 16:00:39 by anonymous:_ {linebreak} Fixed by Check-in [4507] #f2dcdc 2729 doc active 2007 Oct anonymous 2007 Oct 1 1 Lemon: %fallback, %wildcard, and @X uncodumented I noticed that the lemon documentation does not mention the %fallback and %wildcard directives. Both are in the code and are apparently doing useful work in SQLite's parse.y. Can other users benefit from them as well? The symbol @X is also undocumented. From a source code comment I read that it "If the argument is of the form @X then substituted the token number of X, not the value of X". A short documentation example would help to understand where and how it can be useful to apply this syntax. Are there other nice but undocumented Lemon goodies lacking documentation? #f2dcdc 2725 code active 2007 Oct anonymous 2007 Oct 1 1 memory leak in sqlite3_open_v2() when it fails only happens with flags = SQLITE_OPEN_READWRITE; and when res = sqlite3_open_v2(sourcename, &conn, flags, NULL); seems to leak 674 bytes per call _2007-Oct-15 07:07:07 by danielk1977:_ {linebreak} Are you calling sqlite3_close(conn) after the error occurs? All calls to sqlite3_open_v2() need to be matched by a call to sqlite3_close(), even if an error occurs. #cfe8bd 2722 code fixed 2007 Oct anonymous 2007 Oct 1 1 data mis-alignment in UTF16 collation callback arguments We're using a Unicode build of SQLite, and found that internally, the 'NOCASE' collation was not Unicode aware -- it would convert any wide strings to UTF-8 when needed, and compare those. For performance, we would like to implement a WNOCASE for wide strings. However, upon implementing this, we found that the pointers passed to our collation function are not wide character (short-) aligned. On our platform we receive data mis-alignment errors when comparing the strings. We have already implemented similiar sqlite wide string functions (WLIKE, WCONTAINS), and we never receive mis-aligned strings there (are those guaranteed to be aligned); it would be helpful if this restriction could be applied to collation callbacks. #c8c8c8 2720 code closed 2007 Oct anonymous 2007 Oct 1 1 WinCE - Can't write Application which was working correctly with one of the last sqlite 3.3.16 fails on 3.5.1 to write anything to the database - no create tables and no inserts if the db existed beforehand. It seems to happen always, with either UTF-8 or UTF-16 encoding. _2007-Oct-11 07:26:49 by anonymous:_ {linebreak} I meant, "...one of the last 3.3.x (I think 3.3.16)..." ---- _2007-Oct-11 12:20:13 by drh:_ {linebreak} The developers have no ability to compile or run on winCE, much less any ability to debug on winCE. All winCE support is community provided. There have been many patches to the winCE code since 3.5.1 was released. The writer of this ticket previously reported two other problems that have already been fixed. Several other problems have also been fixed since 3.5.1. We are guessing that this "Can't write" problem will probably go away if the writer will simply get the latest code out of CVS. If not, then there is nothing really we can do about it unless we have patches to fix the problem. #f2dcdc 2715 code active 2007 Oct anonymous 2007 Oct 1 1 no authorization needed to remove authorizer there should be a new auth code created and the auth function should be consulted for permission for removal. _2007-Oct-10 01:08:48 by drh:_ {linebreak} I'm assuming that this feature request comes from {quote: RockShox} and that the development language is Tcl. No. If your adversary has the ability to invoke the interface that removes an authorizer, then you system is already pwned. What you really need is the ability to [interp alias] the eval method into a safe interpreter. That way you can: *: Open the database in the main interpreter *: Set up the authorizer in the main interpreter to invoke a script in the main interpreter *: Set up the [interp alias] so that the safe interpreter can do [db eval ...] but not [db auth ...] It seems like an "-interp" option on the "eval" method of the database connection object would likely be the right interface. Or perhaps there should be separate "safeeval" method. Either way, it has been years and years since I have done anything with safe interpreters so I will have to look into what needs to be done to make that happen. ---- _2007-Oct-17 20:11:23 by anonymous:_ {linebreak} ok i think i agree with that. currently you cannot use an interp alias since the target command runs in the target interp and all your variables and commands are in the wrong scope. this means one needs to load sqlite again in the new interp, and sqlite will not load in a safe interp so a regular interp is required. to be useful, a -interp flag would need to execute in the current scope of the interp and not the global scope. #cfe8bd 2713 build fixed 2007 Oct anonymous 2007 Oct 1 5 build fails for 3.5.1 on win32/cygwin - duplicate opcode labels Problem: build fails on compiling vdbe.c => src/vdbe.c: In function `sqlite3VdbeExec': src/vdbe.c:1136: error: duplicate case value src/vdbe.c:921: error: previously used here src/vdbe.c:1655: error: duplicate case value src/vdbe.c:900: error: previously used here ... Seems like my generated opcodes.h has lots of duplicates: #define OP_Multiply 80 /* same as TK_STAR */ #define OP_Pull 80 #define OP_Dup 71 #define OP_Lt 71 /* same as TK_LT */ I'm running on XP with a c:\cygwin library available. NB. i'm not a regular SQLLite user and was just trying the library out of curiosity so if no one else reports this then don't sweat it... it may be down to my environment screwing up the build process. _2007-Oct-09 17:15:24 by drh:_ {linebreak} Why don't you try compiling the {link: /cvstrac/wiki?p=TheAmalgamation amalgamation} instead? ---- _2007-Oct-09 18:06:22 by anonymous:_ {linebreak} Original poster - just run "make clean" and try again. ---- (OP) OK. tracked it down to the initialisation of the tk array in mkopcodeh.awk which is getting strings like "22 " (not sure if this is a space or a CR). the following patch fixes it for me: --- mkopcodeh.awk-orig 2007-03-29 19:39:30.000000000 +0100 +++ mkopcodeh.awk 2007-10-12 15:14:46.980260800 +0100 @@ -41,7 +41,9 @@ # Remember the TK_ values from the parse.h file /^#define TK_/ { - tk[$2] = $3 + num = $3 + num += 0 # convert "22 " -> 22 + tk[$2] = num } # Scan for "case OP_aaaa:" lines in the vdbe.c file ie. ensure that the keys are numeric. With this patch, the build completes ok. Thanks. ---- _2007-Oct-12 20:08:41 by anonymous:_ {linebreak} It's not an AWK bug in Cygwin. The only way to convert strings to numbers in AWK is to add zero. Cygwin uses the same version of GNU gawk as Linux. It's the exact same code. ---- _2007-Oct-12 20:43:55 by drh:_ {linebreak} Seems like if it were exactly the same code it would work exactly the same. But clearly it does not. I don't know what is going on. Probably some strange DOS \r line ending problems. Presumably the change has fixed the problem. #cfe8bd 2711 code fixed 2007 Oct anonymous 2007 Oct 1 1 WinCE: flags incorrectly checked One last fix for WinCE: checkin 4479 is correct in the omission of certain flags for WinCE, but the flags are still being checked for later on in winOpen:
if( dwFlagsAndAttributes & FILE_FLAG_DELETE_ON_CLOSE ){ pFile->zDeleteOnClose = zConverted; }else ...Since that flag is no longer set, it needs to read:
if( flags & (SQLITE_OPEN_TEMP_DB | SQLITE_OPEN_TEMP_JOURNAL | SQLITE_OPEN_SUBJOURNAL) ){ pFile->zDeleteOnClose = zConverted; }else ...Finally, on WinCE, since FILE_FLAG_RANDOM_ACCESS is always set, it doesn't make sense to set FILE_FLAG_SEQUENTIAL_SCAN (for journals). At least on CE, it hampered performance in our tests. #cfe8bd 2710 code fixed 2007 Oct anonymous 2007 Oct 1 1 Typo in code specific to WinCE (checkin 4479) Checkin [4479] introduces a typo in code specific to WiNCE, returning SQLTIE_OK instead of SQLITE_OK _2007-Oct-09 15:21:50 by anonymous:_ {linebreak} Also, checkin 4479 is correct in the omission of certain flags for WinCE, but the flags are still being checked for later on in winOpen: if( dwFlagsAndAttributes & FILE_FLAG_DELETE_ON_CLOSE ){ pFile->zDeleteOnClose = zConverted; }else ... Since that flag is no longer set, it needs to read: if( flags & (SQLITE_OPEN_TEMP_DB | SQLITE_OPEN_TEMP_JOURNAL | SQLITE_OPEN_SUBJOURNAL) ){ pFile->zDeleteOnClose = zConverted; }else ... #c8c8c8 2707 code closed 2007 Oct anonymous 2007 Oct appledev 1 3 SQLite on Mac OS X seems to have a 4GB database limit This is on Mac OX 10.4 using Apple's Core Data: I run into a strange error, always when I try to save a managed- object-context and the file-size is already at the 4GB limit: An error saving the context: NSError "An error occurred while saving." Domain=NSCocoaError Domain Code=134030 UserInfo={NSUnderlyingException = disk I/O error; } _2007-Oct-09 05:12:47 by anonymous:_ Why do you think it is SQLite error ?? ---- _2007-Oct-09 06:52:50 by danielk1977:_ {linebreak} What kind of file system are you using for the database? I used the attached script to create a 5.5 GB database on HFS+, then checked it using the integrity check and everything seemed Ok. If it's not a simple file-system limitation, is is possible for you to post some code that demonstrates the issue? Thanks. ---- _2007-Oct-12 18:46:31 by drh:_ {linebreak} Unable to reproduce. Submitter does not respond. #cfe8bd 2702 code fixed 2007 Oct anonymous 2007 Oct 1 1 Errors in CreateFileW function use on WinCE CreateFileW function in WindowsCE uses only subspace of flags of desktop windows' version and reports errors, when passed flag that can't be dispatched. For 3.5.1 this means that any attempt to open/create temporary file on WCE fails.{linebreak} Also there is bad argument name passed in winOpen to winceCreateLock.{linebreak} Also, there is 'return' missing in winFullPathname when building for WCE, so any attempt to open file fails. Here is the patch solving all(for me) filesystem errors on WindowsCE.
sqlite-amalgamation-3_5_1.ce.patch ---------------------------------- --- ./sqlite3.c.orig Wed Oct 3 18:08:44 2007 +++ ./sqlite3.c Mon Oct 8 17:04:16 2007 @@ -18730,17 +18730,24 @@ } if( flags & (SQLITE_OPEN_TEMP_DB | SQLITE_OPEN_TEMP_JOURNAL | SQLITE_OPEN_SUBJOURNAL) ){ +#if OS_WINCE + dwFlagsAndAttributes = FILE_ATTRIBUTE_HIDDEN; +#else dwFlagsAndAttributes = FILE_ATTRIBUTE_TEMPORARY | FILE_ATTRIBUTE_HIDDEN | FILE_FLAG_DELETE_ON_CLOSE; +#endif }else{ dwFlagsAndAttributes = FILE_ATTRIBUTE_NORMAL; } if( flags & (SQLITE_OPEN_MAIN_DB | SQLITE_OPEN_TEMP_DB)){ dwFlagsAndAttributes |= FILE_FLAG_RANDOM_ACCESS; - }else{ + } +#if !OS_WINCE + else{ dwFlagsAndAttributes |= FILE_FLAG_SEQUENTIAL_SCAN; } +#endif if( isNT() ){ h = CreateFileW((WCHAR*)zConverted, dwDesiredAccess, @@ -18786,7 +18793,7 @@ #if OS_WINCE if( (flags & (SQLITE_OPEN_READWRITE|SQLITE_OPEN_MAIN_DB)) == (SQLITE_OPEN_READWRITE|SQLITE_OPEN_MAIN_DB) - && !winceCreateLock(zFilename, pFile) + && !winceCreateLock(zName, pFile) ){ CloseHandle(h); free(zConverted); @@ -18957,6 +18964,7 @@ #if OS_WINCE /* WinCE has no concept of a relative pathname, or so I am told. */ sqlite3_snprintf(pVfs->mxPathname, zFull, "%s", zRelative); + return SQLITE_OK; #endif #if !OS_WINCE && !defined(__CYGWIN__)---------------------------------- Thank you! #cfe8bd 2700 code fixed 2007 Oct anonymous 2007 Oct 1 1 invalid name in winOpen when compiling under WinCE in os_win.c:1165, WinCE specific code to create lock still refers to former zFilename variable whereas argument is now named zName. --- os_win.c-orig 2007-10-07 22:04:02.000000000 +0200 +++ os_win.c 2007-10-07 23:32:51.000000000 +0200 @@ -1162,7 +1162,7 @@ #if OS_WINCE if( (flags & (SQLITE_OPEN_READWRITE|SQLITE_OPEN_MAIN_DB)) == (SQLITE_OPEN_READWRITE|SQLITE_OPEN_MAIN_DB) - && !winceCreateLock(zFilename, pFile) + && !winceCreateLock(zName, pFile) ){ CloseHandle(h); free(zConverted); #cfe8bd 2697 code fixed 2007 Oct anonymous 2007 Oct 1 3 sqlite3_open_v2 fails then filename includes non-english chars Can't create database with new sqlite3_open_v2 function if filename is international (non English) char filename[MAX_PATH]; memset(filename, 0, sizeof(filename)); ::WideCharToMultiByte(CP_ACP, 0, jobfilename.c_str(), -1, filename, sizeof(filename)-1, 0, 0); int dbcreateres = sqlite3_open_v2(filename, &m_db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL); dbcreateres == 14 By the way, why we just don't have sqlite3_open16_v2 function !!! It would be best solution for me ! changing the code to ::WideCharToMultiByte(CP_UTF8, ... fixes the issue... sorry for your time... #c8c8c8 2692 build closed 2007 Oct anonymous 2007 Oct 1 1 COmpilation of Sqlite amalgamation v3.4.2. failed Version 3.4.2 (amalgamation) could not be compiled under MS DevStudio 6 the compilation errors are: sqlite3.c(6187) : error C2133: 'sqlite3UpperToLower' : unknown size sqlite3.c(9310) : error C2133: 'sqlite3OpcodeNames' : unknown size sqlite3.c(47159) : error C2133: 'sqlite3IsIdChar' : unknown size _2007-Oct-04 18:19:44 by drh:_ {linebreak} Duplicate of #2574. #c8c8c8 2691 code closed 2007 Oct anonymous 2007 Oct 1 1 SQLITE_OMIT_AUTHORIZATION compilation error sqlite3.Int.h does not compile with SQLITE_OMIT_AUTHORIZATION. Rationale: sqlite3Int.h, line 1768: # define sqlite3AuthRead(a,b,c) is missing a 4th parameter like this: # define sqlite3AuthRead(a,b,c,d) thanks. #f2dcdc 2684 code active 2007 Oct anonymous 2007 Oct 1 1 Accessing sqlite from an NT service will lock the complete databse. Accessing sqlite from a NT service (application 1) will lock the complete database. Any other process trying to open an sqlite db (application 2) will get error "80004005 unable to lock database" If application 1 runs as normal application, started by local user, this problem doesnt occur and both applications can open the db. _2007-Oct-02 15:48:05 by anonymous:_ {linebreak} SQLite has no knowledge of Windows services. How do you propose to work around this Windows anachronism? ---- _2007-Oct-02 17:20:38 by anonymous:_ {linebreak} Suggesion: Try running the service in the same account as the other program that needs to access the database. Anachronism? Service is just another word for daemon. -knu- ---- _2007-Oct-02 17:33:56 by anonymous:_ {linebreak} Re: Anachronism, the OP suggested there was something fundamentally different about file access using a service. You've pointed out that it's just a file permissions issue. ---- _2007-Oct-05 14:45:07 by drh:_ {linebreak} Two points: 1: The error message "80004005 unable to lock database" is not generated by SQLite. There must be some middleware someplace that is producing this message. The problem might be in that middleware and not in SQLite. 2: None of the SQLite developers run windows. Consequently any fixes for this problem will need to come from the community. Please append patches to this ticket if you find a fix. Or close the ticket if you discover that the problem is outside of SQLite. #cfe8bd 2683 code fixed 2007 Oct anonymous 2007 Oct 1 1 Windows CE won't compile, also a problem with sqlite3OsFullPath on CE With some modifications the program will compile, but opening a database returns (sometimes) unknown error, and (sometimes) attempt to write a read-only database. This strange behaviour is due to a missing return statement that should be "return SQLITE_OK;". See the patch. _2007-Oct-16 19:02:55 by anonymous:_ {linebreak} I built version for WindowsCE and tested. Now it could open and create databases ! The only difference is in
@@ -1162,13 +1168,13 @@ #if OS_WINCE if( (flags & (SQLITE_OPEN_READWRITE|SQLITE_OPEN_MAIN_DB)) == (SQLITE_OPEN_READWRITE|SQLITE_OPEN_MAIN_DB) - && !winceCreateLock(zFilename, &f) + && !winceCreateLock(zName, pFile)#c8c8c8 2682 code closed 2007 Oct anonymous 2007 Oct 1 1 SQLite Text Problem I am using SQLite 3 as backend database in my asp application. I am using SQLite3 ODBC driver (latest version). Table Design ------------ CREATE TABLE log ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, from_value TEXT NULL, to_value TEXT NULL ); INSERT 10 RECORDS IN TO DATABASE -------------------------------- Const adOpenStatic = 3 Const adLockOptimistic = 3 Dim SQLString ,objConnection Dim fromVal,toval Set objConnection = CreateObject("ADODB.Connection") for a = 1 to 10 objConnection.Open "DSN=Test;uid=;pwd=;" fromVal = "FROM Value : "& a toVal = "To Value : "& a SQLString = "INSERT INTO log(from_value, to_value) VALUES ('"& fromVal & "' ,'"&toVal& "')" objConnection.Execute SQLString objConnection.Close next Access records in the log table ------------------------------- Dim LogRs,SQLStmt SQLStmt = "SELECT * FROM log " LogRs.ActiveConnection = "DSN=Test;uid=;pwd=;" LogRs.Source= SQLStmt LogRs.CursorType = 3'ADOPENSTATIC LogRs.CursorLocation = 2 'ADUSESERVER LogRs.LockType = 3 'ADLOCKOPTIMISTIC LogRs.Open() RecordCount = LogRs.RecordCount If I use datatype of from_value/to_value as TEXT ,I get RecordCount as zero which is wrong. If I use datatype of from_value/to_value as Varchar(1600) ,I get RecordCount as 10 which is correct. If I use datatype of from_value/to_value as Varchar(1600) and tried to put more than 1600 charcters in it ,SQLite stores data in DB. While retrieving the data I get the error Arguments are of the wrong type,are out of acceptable range,or are in conflict with one another. I enabled ODBC trace log ,but its empty after running my application.So I am not sure where it is ODBC problem or SQLite ODBC driver error? If I open table (database) in SQLite Studio I could see data without any error. If I use MS Access with same table design and same code, I dont get any error . The code works fine as expected . _2007-Oct-01 22:04:18 by drh:_ {linebreak} There are multiple ODBC drivers for SQLite, none of which are support by this site. Your problem appears to be in the ODBC driver. If you find evidence to the contrary, please reopen this ticket. #cfe8bd 2678 code fixed 2007 Sep anonymous 2007 Oct 1 1 amalgamation zip file should include sqlite3ext.h It is not possible to build portable external loadable modules using just sqlite-amalgamation-3_5_0.zip - instead you need the entire sqlite3 source tree. If sqlite3ext.h were bundled with the amalgamation, this would not be necessary. _2007-Sep-28 19:44:00 by anonymous:_ {linebreak} Actually, a better solution would be to get rid of sqlite3ext.h altogether, fold its functionality into sqlite3.h and when people go to compile a module they #define whether they wish to compile an external library or not. #c8c8c8 2676 code closed 2007 Sep anonymous 2007 Sep 1 1 TEMP_STORE does not appear to be set by the compile time switch The compile time switch -DTEMP_STORE=2 does not appear to change TEMP_STORE. When a database is created after compiling the library I tried to view temp_store by using PRAGMA temp_store which returns the value 0. If this is true that would force all temp storage to a file. I want temp storage to be in memory. It's more complex than that. See the table at: http://www.sqlite.org/pragma.html#pragma_temp_store #c8c8c8 2675 new closed 2007 Sep anonymous 2007 Sep 1 1 TEMP_STORE does not appear to be set by the compile time switch The compile time switch -DTEMP_STORE=2 does not appear to change TEMP_STORE. When a database is created after compiling the library I tried to view temp_store by using PRAGMA temp_store which returns the value 0. If this is true that would force all temp storage to a file. I want temp storage to be in memory. See #2676 #cfe8bd 2672 doc fixed 2007 Sep anonymous 2007 Oct 1 3 icu ext init failure: sqlite3_create_function returns SQLITE_BUSY icu extenstion init fails b/c sqlite3_create_function returns 5 (SQLITE_BUSY) in the init code for icu i print the function name and the result code from sqlite3_create_function, i see: sqlite> select load_extension('./icu.so'); create function for regexp gave 0 create function for lower gave 0 create function for lower gave 0 create function for upper gave 0 create function for upper gave 0 create function for lower gave 5 i,e. the first lower with SQLITE_UTF8 fails (i didn't check why yet, i'm wondering was there some API change here though wrt to having both _UST16 and _UTF8?) also, i notice the code on failure doesn't cleanup, so it will leave functions registered even though init failed (recompile & retry probably will crash sooner or later) This is a bug. SQLite does not allow you to change or delete a user function while there are SQL statements running (could cause a seg-fault if the running statement users the function being deleted/modified). Adding new functions is Ok though. The reason this is failing is that SQLite is counting "SELECT load_extension(...)" as a running statement. The first couple of create function calls succeeded because these did not override existing user functions. The easiest workaround would be to use the sqlite3_load_extension() API directly, not via "SELECT load_extension()". In the shell, try this: sqlite> .load icu.so Alternatively, you could try linking the extension staticly with SQLite. To do this, add ext/icu/icu.c to the list of C files to be compiled and add -DSQLITE_ENABLE_ICU to the compiler command line (for all C files, not just icu.c). ---- _2007-Sep-28 17:34:36 by anonymous:_ {linebreak} Ah right! Testing using ".load ./icu.so" does indeed work correctly. However, there still remains the bug that when it does fail to initialize some functions are left registered and accessing those will usually seg-fault. Given that sqlite3_create_function cannot be undone, perhaps there should be some way to detect (inside the extension) if it's being loaded from inside a statement and bail out early? ---- _2007-Sep-28 17:40:14 by anonymous:_ {linebreak} To fix this bug, sqlite need only defer the loading of the extension module until after any pending SQL call is complete. #c8c8c8 2667 code closed 2007 Sep anonymous 2007 Oct 1 1 no such function: hex no such function: regexp I am using SQLite 3 with pysqlite. I have few triggers that uses regexp and hex functions. on linux it worked fine. Once I switch to Windows I am getting no such functions errors. _2007-Oct-01 13:35:49 by drh:_ {linebreak} The regexp function is not supported by SQLite. hex() is supported but we are unable to find anything wrong with it. #f2dcdc 2664 code active 2007 Sep danielk1977 2007 Sep 1 1 attaching the same db twice in shared-cache mode fails The following SQL script can cause an assert() to fail in shared-cache mode. ATTACH 'db' AS aux1; ATTACH 'db' AS aux2; CREATE TABLE aux1.abc(a, b, c); CREATE TABLE aux2.abc(a, b, c); See also #2653 #c8c8c8 2662 code closed 2007 Sep anonymous 2007 Sep 1 1 Serious Performance Degradation from 3.3.4 to 3.3.5 & latest versions Starting from 3.3.5, SQLite suffers a serious performance degradation for the following schema and query. 3.3.4 returnes results *immedialtely*, all later versions take considerable time (multiple seconds) to execute the query. The database is about 6 MB in size. Schema: CREATE TABLE MTV20030105AdLink (AdvertisementID Numeric, AdRelationShipID Numeric, AgencyID Numeric, AdvertiserID Numeric, ProductID Numeric); CREATE TABLE MTV20030105AdType (ADTypeID Numeric, AdTypeName char(50)); CREATE TABLE MTV20030105Adjacency (AdInstID Numeric, ProductID Numeric); CREATE TABLE MTV20030105Advertiser (AdvertiserID Numeric, AdvertiserName char(50)); CREATE TABLE MTV20030105Agency (AgencyID Numeric, AgencyName char(50)); CREATE TABLE MTV20030105Products (ProductID Numeric, ProductName char(50)); CREATE TABLE MTV20030105Programs (ProgramID Numeric, ProgramName char(50)); CREATE TABLE MTV20030105Spots (AdInstID Numeric, AdvertisementID Numeric, KeyNumber char(15), StationID Numeric, SpotDateTime DateTime, Duration Numeric, BreakNumber Numeric, NumAdsInBreak Numeric, PositionInBreak Numeric, PromoBefore Numeric, PromoAfter Numeric, AdTypeID Numeric, CommentID Numeric, ProgramID Numeric, MarketInd Numeric); CREATE TABLE MTV20030105Stations (StationID Numeric, NetworkID Numeric, NetworkName char(50), CallSign char(50), LeadMarketInd Numeric); CREATE INDEX MTV20030105AdLink_i1 ON MTV20030105AdLink(ProductID); CREATE INDEX MTV20030105Spots_i1 ON MTV20030105Spots (AdvertisementID); Query: select SpotDateTime,NetworkName,CallSign,KeyNumber,Duration,ProgramName from MTV20030105Spots,MTV20030105Stations,MTV20030105Programs where MTV20030105Spots.AdvertisementID in (select AdvertisementID from MTV20030105AdLink where productid=90887) and MTV20030105Stations.StationID=MTV20030105Spots.StationID and MTV20030105Programs.ProgramID=MTV20030105Spots.ProgramID; "ANALYZE;" was not executed with any version of SQLite, and running it does not speed up the query with recent versions. I can provide the test database if required, just let me know. _2007-Sep-24 14:49:29 by anonymous:_ {linebreak} This is not a bug. This question belongs on the mailing list. If you run "EXPLAIN QUERY PLAN" on your SELECT you can see that your schema is not optimal, and results in full table scans for stations and programs: 0|1|TABLE MTV20030105Stations 1|2|TABLE MTV20030105Programs 2|0|TABLE MTV20030105Spots WITH INDEX MTV20030105Spots_i1 0|0|TABLE MTV20030105AdLink WITH INDEX MTV20030105AdLink_i1 If you change your schema as follows, it will use the indexes effectively: CREATE TABLE MTV20030105AdLink (AdvertisementID Numeric, AdRelationShipID Numeric, AgencyID Numeric, AdvertiserID Numeric, ProductID Numeric); CREATE TABLE MTV20030105AdType (ADTypeID INTEGER PRIMARY KEY, AdTypeName char(50)); CREATE TABLE MTV20030105Adjacency (AdInstID INTEGER PRIMARY KEY, ProductID Numeric); CREATE TABLE MTV20030105Advertiser (AdvertiserID INTEGER PRIMARY KEY, AdvertiserName char(50)); CREATE TABLE MTV20030105Agency (AgencyID INTEGER PRIMARY KEY, AgencyName char(50)); CREATE TABLE MTV20030105Products (ProductID INTEGER PRIMARY KEY, ProductName char(50)); CREATE TABLE MTV20030105Programs (ProgramID INTEGER PRIMARY KEY, ProgramName char(50)); CREATE TABLE MTV20030105Spots (AdInstID Numeric, AdvertisementID Numeric, KeyNumber char(15), StationID Numeric, SpotDateTime DateTime, Duration Numeric, BreakNumber Numeric, NumAdsInBreak Numeric, PositionInBreak Numeric, PromoBefore Numeric, PromoAfter Numeric, AdTypeID Numeric, CommentID Numeric, ProgramID Numeric, MarketInd Numeric); -- assumes that StationID is a unique identifier CREATE TABLE MTV20030105Stations (StationID INTEGER PRIMARY KEY, NetworkID Numeric, NetworkName char(50), CallSign char(50), LeadMarketInd Numeric); CREATE INDEX MTV20030105AdLink_i1 ON MTV20030105AdLink(ProductID); CREATE INDEX MTV20030105Spots_i1 ON MTV20030105Spots (AdvertisementID); explain query plan select SpotDateTime,NetworkName,CallSign,KeyNumber,Duration,ProgramName from MTV20030105Spots,MTV20030105Stations,MTV20030105Programs where MTV20030105Spots.AdvertisementID in (select AdvertisementID from MTV20030105AdLink where productid=90887) and MTV20030105Stations.StationID=MTV20030105Spots.StationID and MTV20030105Programs.ProgramID=MTV20030105Spots.ProgramID; 0|0|TABLE MTV20030105Spots WITH INDEX MTV20030105Spots_i1 1|1|TABLE MTV20030105Stations USING PRIMARY KEY 2|2|TABLE MTV20030105Programs USING PRIMARY KEY 0|0|TABLE MTV20030105AdLink WITH INDEX MTV20030105AdLink_i1 ---- _2007-Sep-24 15:12:03 by anonymous:_ {linebreak} This query is faster: explain query plan select SpotDateTime,NetworkName,CallSign,KeyNumber,Duration,ProgramName from (select AdvertisementID from MTV20030105AdLink where productid=90887) ads, MTV20030105Spots, MTV20030105Stations, MTV20030105Programs where MTV20030105Spots.AdvertisementID = ads.AdvertisementID and MTV20030105Stations.StationID=MTV20030105Spots.StationID and MTV20030105Programs.ProgramID=MTV20030105Spots.ProgramID; 0|0|TABLE MTV20030105AdLink WITH INDEX MTV20030105AdLink_i1 1|1|TABLE MTV20030105Spots WITH INDEX MTV20030105Spots_i1 2|2|TABLE MTV20030105Stations USING PRIMARY KEY 3|3|TABLE MTV20030105Programs USING PRIMARY KEY ---- _2007-Sep-24 15:45:58 by anonymous:_ {linebreak} Point well taken ;-) For completeness, here is the query plan produced by SQLite 3.3.4: 0|0|TABLE MTV20030105Spots WITH INDEX MTV20030105Spots_i1 1|1|TABLE MTV20030105Stations 2|2|TABLE MTV20030105Programs 0|0|TABLE MTV20030105AdLink WITH INDEX MTV20030105AdLink_i1 The table scan order is different which results in much faster execution times. If anyone knows if this change was intentional in later versions, I'd be glad to read about it on the mailing list. Also, out of plain curiosity: While I realize that the presented DB schema is not defined adequately, would anyone still consider that the new scan order behavior breaks backwards compatibility? For very large tables, the query becomes unusable after 3.3.4. Meet you at the mailing list ... ---- _2007-Sep-24 18:18:28 by anonymous:_ {linebreak} The table/index scan order has changed many times from release to release, and has never been guaranteed. When the query optimizer was first introduced many queries relying on the join order specified in the FROM clause were slower and had to be reworked. Your previous defined schema was just lucky in this regard. If you have proper indexing you would not have had this performance degradation. For that matter, it would not hurt if you used ANALYZE as well. #c8c8c8 2657 build closed 2007 Sep anonymous 2007 Oct anonymous 1 1 fail to compile I fail to compile. It is a "win32 static library" project. I simply create an empty project with "not using MFC" option and then add all files included in "sqlite-source-3_5_0.zip" into the project. Debug configuration is fine, but I cannot compile release. Information: ...sqlite-source-3_5_0\btree.c(3111) : fatal error C1001: INTERNAL COMPILER ERROR (compiler file 'F:\9782\vc98\p2\src\P2\regasg.c', line 646) Please choose the Technical Support command on the Visual C++ Help menu, or open the Technical Support help file for more information Error executing cl.exe. _2007-Sep-21 00:05:42 by anonymous:_ {linebreak} I have the same problem. I am using the preprocessed source code in sqlite-source-3_5_0.zip with Visual Studio 6.0 SP6. Compiler version: Microsoft (R) 32-bit C/C++ Optimizing Compiler Version 12.00.8804 for 80x86 The offending line of btree.c is line 3111, which looks fairly innocent to me: pCur->aOverflow[iIdx] = nextPage; After investigating, it looks like this error is only emitted when global optimizations (/Og) are enabled, which is one of the switches set by optimizing for maximum speed. As a workaround, you can insert the line #pragma optimize("g", off) before line 3026 and the line #pragma optimize( "", on) after line 3156, which are the beginning and ending of the function static int accessPayload(BtCursor,int,int,unsigned char,int,int) This will disable global optimizations for the function, but leave them enabled for the rest of the file. {link: http://msdn2.microsoft.com/en-us/library/y19zxzb2(vs.71).aspx MSDN} has more information about error C1001, but it is not quite the same as what the VS6 docs say: {quote: This error is most often generated [due to] ... Failure of the code generator to find a way to generate correct code for a construct. This is most often caused by the interaction of an expression and an optimization option. The optimization has generated a tree which the compiler does not know how to handle. Such a problem can often be fixed by removing one or more optimization options when compiling the particular function containing the line indicated in the error message. } ---- _2007-Oct-12 18:53:47 by drh:_ {linebreak} In other words, there is a bug in MSVC++ that prevents it from correctly compiling SQLite. Shouldn't this be a problem reported to Microsoft? What exactly can we (the SQLite developers) do about this? Is there some simple code change that we can make to work around the bug in MSVC++? #c8c8c8 2654 code closed 2007 Sep anonymous 2007 Oct jadams 1 1 sqlite3_prepare return 1(fail) result = sqlite3_exec( db, "create table MyTable_1sadsd ( ID integer primary key autoincrement, name nvarchar(32),comp nvarchar(32), file_content blob )",NULL, NULL, errmsg );//ok result=sqlite3_prepare(db,"insert into MyTable_1sadsd ( name,comp,file_content ) values ( 'aaaa','aaaaaa1',? )",-1,&stat,&zLeftover );//fail //the result==1 so fail; //but result = sqlite3_exec( db, "create table MyTable( ID integer primary key autoincrement, name nvarchar(32),comp nvarchar(32), file_content blob )",NULL, NULL, errmsg );//ok result=sqlite3_prepare(db,"insert into MyTable ( name,comp,file_content ) values ( 'aaaa','aaaaaa1',? )",-1,&stat,&zLeftover );//ok //is ok ,because MyTable is shorter than MyTable_1sadsd ; //why ? tks It's not clear from the report. There's nothing wrong with the SQL or th e C code shown. Maybe some other process is changing the schema or locking the dataabase or something. If you can post a complete program that demonstrates the problem, we will be able to help you. #f2dcdc 2652 code active 2007 Sep drh 2007 Sep 1 1 Aggregate function cannot be used from within a subquery The following SQL fails: CREATE TABLE t1(x,y); INSERT INTO t1 VALUES(1,2); CREATE TABLE t2(z); INSERT INTO t2 VALUES(1); SELECT (SELECT y FROM t1 WHERE x=min(z)) FROM t2; Problem reported on the mailing list. _2007-Sep-23 16:01:09 by anonymous:_ {linebreak} Your syntax appears to be incorrect.{linebreak} SQLite v3.4.2 CREATE TABLE t1(x,y); CREATE TABLE t2(z); INSERT INTO t1 VALUES(1,21); INSERT INTO t1 VALUES(2,22); INSERT INTO t1 VALUES(3,23); INSERT INTO t2 VALUES(3); INSERT INTO t2 VALUES(2); INSERT INTO t2 VALUES(1); What you wanted to do: SELECT y FROM t1 WHERE x=(SELECT min(z) FROM t2); 21 -- works as expected What you did: SELECT (SELECT y FROM t1 WHERE x=min(z)) FROM t2; SQL error near line []: misuse of aggregate function min() #c8c8c8 2650 build closed 2007 Sep anonymous 2007 Sep 1 1 lack ; in os_win.c:1329 Hi, I want just to inform you that it misses one; in the file os_win.c (line 1329). in winFullPathname, #if defined(__CYGWIN__) cygwin_conv_to_full_win32_path(zRelative, zFull); return SQLITE_OK <--- lack ';' #endif Regards YoCarBo Duplicate of #2642 and #2647 #cfe8bd 2648 build fixed 2007 Sep anonymous 2007 Sep 1 1 compilation error for PocketPC/WindowsCE function winOpen has some type errors under OS_WINCE preprocessor
#if OS_WINCE if( (flags & (SQLITE_OPEN_READWRITE|SQLITE_OPEN_MAIN_DB)) == (SQLITE_OPEN_READWRITE|SQLITE_OPEN_MAIN_DB) && !winceCreateLock(zFileName, &f) ){ CloseHandle(h); free(zConverted); return SQLITE_CANTOPEN; } if( dwFlagsAndAttributes & FILE_FLAG_DELETE_ON_CLOSE ){ pFile->zDeleteOnClose = zConverted; }else #endifThe line 18511 should be: && !winceCreateLock(zName, pFile) Also it's have mistake in typing FILE_FLAG_DELETE_ON_CLOSE (with last underlines - FILE_FLAG_DELETEONCLOSE in line 18517). Please change the sources to be compiled for WindowsCE. BR. Yuri Noyanov #c8c8c8 2647 code closed 2007 Sep anonymous 2007 Sep 1 1 sqlite-3.5.0 Cygwin: Compilation fails on src/os_win.c - ';' missing There is a ';' symbol missing on line 1329 in file os_win.c (winFullPathname function) after return statement:
#if defined(__CYGWIN__) cygwin_conv_to_full_win32_path(zRelative, zFull); return SQLITE_OK #endifDuplicate of Ticket #2642 #c8c8c8 2644 code closed 2007 Sep anonymous 2007 Sep 1 1 sqlite Will you tell me why sqlite program is running on my laptop computer? Is it part of another program. Your answer and time is really appreciated. Thank you. _2007-Sep-13 18:00:08 by drh:_ {linebreak} SQLite is used by Mcaffee Anti-virus, AOL Mail, Skype, Adobe Lightroom, and countless other programs, most of which we are completely unaware of. This website is for reporting bugs against SQLite, not for getting help with figuring out what it is or why it is running on your computer. Please use the SQLite mailing list for such help. #cfe8bd 2643 code fixed 2007 Sep anonymous 2007 Sep 1 1 bestIndex: Assertion `flags!=0' failed after index creation I get an assertion crash on a database using sqlite 3.3.12 and also 3.4.2:
sqlite> create index idx_media_items_p1 on media_items (p1); sqlite> select count(1) from media_items where p1 is not null; lt-sqlite3: ./src/where.c:1486: bestIndex: Assertion `flags!=0' failed. Aborted (core dumped) Program received signal SIGABRT, Aborted. [Switching to Thread 47454430964672 (LWP 24045)] 0x00002b28d839247b in raise () from /lib/libc.so.6 (gdb) bt #0 0x00002b28d839247b in raise () from /lib/libc.so.6 #1 0x00002b28d8393da0 in abort () from /lib/libc.so.6 #2 0x00002b28d838bbf6 in __assert_fail () from /lib/libc.so.6 #3 0x00002b28d7fd3360 in bestIndex () from /home/steve/dev/sqlite-3.4.2/.libs/libsqlite3-3.4.2.so.0 #4 0x00002b28d7fd3f7c in sqlite3WhereBegin () from /home/steve/dev/sqlite-3.4.2/.libs/libsqlite3-3.4.2.so.0 #5 0x00002b28d7fb3bdc in sqlite3Select () from /home/steve/dev/sqlite-3.4.2/.libs/libsqlite3-3.4.2.so.0 #6 0x00002b28d7fa2568 in yy_reduce () from /home/steve/dev/sqlite-3.4.2/.libs/libsqlite3-3.4.2.so.0 #7 0x00002b28d7fa4e52 in sqlite3Parser () from /home/steve/dev/sqlite-3.4.2/.libs/libsqlite3-3.4.2.so.0 #8 0x00002b28d7fb5719 in sqlite3RunParser () from /home/steve/dev/sqlite-3.4.2/.libs/libsqlite3-3.4.2.so.0 #9 0x00002b28d7fa8e1d in sqlite3Prepare () from /home/steve/dev/sqlite-3.4.2/.libs/libsqlite3-3.4.2.so.0 #10 0x00002b28d7fa92f6 in sqlite3_prepare () from /home/steve/dev/sqlite-3.4.2/.libs/libsqlite3-3.4.2.so.0 #11 0x00002b28d7fd70df in sqlite3_exec () from /home/steve/dev/sqlite-3.4.2/.libs/libsqlite3-3.4.2.so.0 #12 0x0000000000406a74 in process_input () #13 0x0000000000407bc2 in main ()core: http://skrul.com/core.gz I can send the db file upon request. _2007-Sep-13 17:09:29 by anonymous:_ {linebreak} Two additional notes: This does not crash on 3.3.4, plus this particular database was created using the "attach database" command from another database. ---- _2007-Sep-13 17:18:42 by drh:_ {linebreak} I need to know the schema for the media_items table. ---- _2007-Sep-13 17:22:13 by anonymous:_ {linebreak}
steve@steve-linux:~/dev/sqlite-3.4.2/.libs$ ./sqlite3 /home/steve/dev/songbird/db/flat2.db SQLite version 3.4.2 Enter ".help" for instructions sqlite> .schema CREATE TABLE media_items ( media_item_id integer primary key autoincrement, guid text unique not null, created integer not null, updated integer not null, content_url text not null, content_mime_type text, content_length integer, hidden integer not null check(hidden in (0, 1)), media_list_type_id integer, p1 text, p1_sort text, p2 text, p2_sort text, p3 text, p3_sort text, p4 text, p4_sort text, p5 text, p5_sort text, p6 text, p6_sort text, p7 text, p7_sort text, p8 text, p8_sort text, p9 text, p9_sort text, p10 text, p10_sort text , p11 text); CREATE INDEX idx_media_items_p1 on media_items (p1); CREATE INDEX idx_media_items_p11 on media_items (p11); CREATE INDEX idx_media_items_p2 on media_items (p2); sqlite>---- _2007-Sep-13 17:24:37 by drh:_ {linebreak} I also want to see a ".dump" of the sqlite_stat1 table. ---- _2007-Sep-13 17:25:52 by anonymous:_ {linebreak}
sqlite> .dump sqlite_stat1 BEGIN TRANSACTION; ANALYZE sqlite_master; INSERT INTO "sqlite_stat1" VALUES('media_items','idx_media_items_p11','61799 1'); INSERT INTO "sqlite_stat1" VALUES('media_items','sqlite_autoindex_media_items_1','61799 1'); COMMIT; sqlite>---- _2007-Sep-13 18:03:19 by anonymous:_ {linebreak} Great! I applied those changes to my sqlite 3.4.2 where.c and it fixed the problem. Thanks for the unbelievably fast fix! #cfe8bd 2640 code fixed 2007 Sep anonymous 2007 Sep 1 1 sqlite doesn't treat global identifiers correctly in a subquery Apparently, sqlite doesn't treat global identifiers correctly in a subquery; in particular, with my database filling, the following query gives a wrong result (3 rows) whereas changing the innermost p.person_id into d.person_id (which are equal by the preceding condition) gives the correct result (56 rows). select distinct p.name from persons p, directors d where d.person_id=p.person_id and not exists ( select * from directors d1 where d1.person_id=p.person_id except select * from writers w ) _2007-Sep-12 11:36:42 by danielk1977:_ {linebreak} Can you provide a sample populated database? This would be a lot quicker to look into that way... If it's too large to attach to this ticket, you can mail it to me: dan@sqlite.org ---- _2007-Sep-12 14:30:33 by anonymous:_ {linebreak}
CREATE TABLE persons(person_id, name); INSERT INTO "persons" VALUES(1,'fred'); INSERT INTO "persons" VALUES(2,'barney'); INSERT INTO "persons" VALUES(3,'wilma'); INSERT INTO "persons" VALUES(4,'pebbles'); INSERT INTO "persons" VALUES(5,'bambam'); CREATE TABLE directors(person_id); INSERT INTO "directors" VALUES(5); INSERT INTO "directors" VALUES(3); CREATE TABLE writers(person_id); INSERT INTO "writers" VALUES(2); INSERT INTO "writers" VALUES(3); INSERT INTO "writers" VALUES(4); select distinct p.name from persons p, directors d where d.person_id=p.person_id and not exists ( select * from directors d1 where d1.person_id=p.person_id except select * from writers w ); wilma select distinct p.name from persons p, directors d where d.person_id=p.person_id and not exists ( select * from directors d1 where d1.person_id=d.person_id except select * from writers w ); wilma bambam#c8c8c8 2639 code closed 2007 Sep anonymous 2007 Oct 1 2 Russian charset At implementation of query, Russian charset is inserted like a "" _2007-Sep-12 02:29:24 by drh:_ {linebreak} I do not understand the complaint. But a good guess is that you are trying to use a codepage of some kind in a TEXT entry. SQLite understands only Unicode - either UTF8 or UTF16. Translate all of your strings into one of these encodings first, before inserting them into the database, if you want them to be compared correctly. If you do not want to use unicode, insert your data as BLOBs instead of a strings. #c8c8c8 2637 code closed 2007 Sep anonymous 2007 Sep 1 1 Segfault in pthread_getspecific () Hi, I am trying to run Trac 0.11-dev with Apache 2.2.3-3.2ubuntu0.1/mod_python and libsqlite3 3.4.2. Accessing Trac over Apache causes segfault in libsqlite3. This has something to do with threading (models), I assume. I am not sure if this is an configuration issue, but I couldn't find any help after hard googling. I tried also libqlite 3.3.x with the same result. I marked the bug with severity 1, since there is no known workaround AFAIK. I compiled libsqlite3 to get the symbolic traceback:
#0 0xb7c521fa in pthread_getspecific () from /lib/tls/i686/cmov/libpthread.so.0 #1 0xb7d07f01 in sqlite3Insert (pParse=0x0, pTabList=0xb7d39bc8, pList=0xbfe16c98, pSelect=0xb7cebe8c, pColumn=0xb7cdf298, onError=1) at ./src/insert.c:1564 #2 0xb7d1e40e in sqlite3_get_table_cb (pArg=0xb7cdf298, nCol=1, argv=0xa582000, colv=0xb7d39bc8) at ./src/table.c:107 #3 0xb7cebe8c in sqlite3AuthRead (pParse=0xb7cebe8c, pExpr=0xbfe16c98, pTabList=0xb7d39bc8) at ./src/auth.c:118 #4 0xb7cebf18 in sqlite3AuthRead (pParse=0xb7d39bc8, pExpr=0xa582000, pTabList=0x1) at ./src/auth.c:140 #5 0xb7d118b6 in sqlite3Parser (yyp=0xa6ff558, yymajor=_2007-Sep-11 14:47:34 by anonymous:_ {linebreak} Also, Trac works fine if started using tracd standalone daemon tool ---- _2007-Sep-11 14:47:34 by anonymous:_ {linebreak} Could you try sqlite 3.5? The threading code has been completely redone. ---- _2007-Sep-11 14:57:17 by anonymous:_ {linebreak} No dice. libsqlite 3.5.0:, yyminor= {z = 0xffffffff , dyn = 0, n = 0}, pParse=0xbfe16e34) at parse.y:468 #6 0xb7d11fc1 in sqlite3Parser (yyp=0xa6ff558, yymajor= , yyminor= {z = 0xffffffff , dyn = 0, n = 1609611034}, pParse=0xbfe16e38) at parse.y:651 #7 0xb7d2f625 in getMask (pMaskSet=0x1, iCursor=1) at ./src/where.c:289 #8 0xb7d12243 in sqlite3Parser (yyp=0xbfe16ef8, yymajor= , yyminor= {z = 0xbfe16f08 "\222\023ӷ\232cӷ\200dӷ", dyn = 0, n = 0}, pParse=0x0) at parse.y:706 #9 0xb7d12347 in sqlite3Parser (yyp=0xb7cdffdc, yymajor= , yyminor= {z = 0xbfe1734c "X�o\n", dyn = 0, n = 0}, pParse=0xbfe16f78) at parse.y:735 #10 0xb7d1279c in sqlite3Parser (yyp=0xa6ff558, yymajor= , yyminor= {z = 0xa43a300 "system", dyn = 0, n = 86104312}, pParse=0xb7d39bc8) at parse.y:768 #11 0xb7d1288f in sqlite3Parser (yyp=0xbfe1734c, yymajor= , yyminor= {z = 0xa760dc8 "�>u\no", dyn = 0, n = 86104336}, pParse=0xbfe17048) at parse.y:812 #12 0xb7cf46da in sqlite3BtreeClose (p=0x0) at ./src/btree.c:1236 #13 0xb7d16c56 in base_vprintf (xRealloc=0, useInternal=-1210868792, zInitBuf=0x1 , nInitBuf=-1211228996, zFormat=0xa760dc8 "�>u\no", ap=0xb7d36480 "CREATE TABLE sqlite_master(\n type text,\n name text,\n tbl_name text,\n rootpage integer,\n sql text\n)") at ./src/printf.c:785 #14 0xb7d173ce in base_vprintf (xRealloc=0xbfe16f28, useInternal=-1210882944, zInitBuf= , nInitBuf=-1075743924, zFormat= , ---Type to continue, or q to quit--- ap=0xe9e1744a ) at ./src/printf.c:589 #15 0xb7d179d6 in base_vprintf (xRealloc=0xbfe17038, useInternal=0, zInitBuf= , nInitBuf=-1075743924, zFormat= , ap=0xbfe17090 "\030r��") at ./src/printf.c:548 #16 0xb7d0d75d in pager_playback (pPager=0x68, isHot=16777221) at ./src/pager.c:1474 #17 0xb7d1b000 in prepSelectStmt (pParse=0x5cc2c, p=0xb7cdd000) at ./src/select.c:1368 #18 0xb7d1191f in sqlite3Parser (yyp=0xa6ff558, yymajor= , yyminor= {z = 0xffffffff , dyn = 0, n = 0}, pParse=0xa73708c) at parse.y:480 #19 0xb7d11fc1 in sqlite3Parser (yyp=0xa6ff558, yymajor= , yyminor= {z = 0xffffffff , dyn = 0, n = 87668806}, pParse=0xbfe17488) at parse.y:651 #20 0xb6810cef in statement_create () from /usr/lib/python2.5/site-packages/pysqlite2/_sqlite.so #21 0xb680c7ad in connection_call () from /usr/lib/python2.5/site-packages/pysqlite2/_sqlite.so #22 0xb7547987 in PyObject_Call () from /usr/lib/libpython2.5.so.1.0 #23 0xb7547c03 in ?? () from /usr/lib/libpython2.5.so.1.0 #24 0x0a681a70 in ?? () #25 0x0a4154ec in ?? () #26 0x00000000 in ?? ()
#0 0xb7c521fa in pthread_getspecific () from /lib/tls/i686/cmov/libpthread.so.0 #1 0xb7d07f01 in sqlite3Insert (pParse=0x0, pTabList=0xb7d39bc8, pList=0xbfe16c98, pSelect=0xb7cebe8c, pColumn=0xb7cdf298, onError=1) at ./src/insert.c:1465 #2 0xb7d1e40e in sqlite3Select (pParse=0xb7cdf298, p=0x1, eDest=171204008, iParm=-1210868792, pParent=0x0, parentTab=0, pParentAgg=0xbfe16ca8, aff=0xb7cebf18 "�\205�\017\225�\017���\215�&") at ./src/select.c:2915 #3 0xb7cebe8c in attachFunc (context=0x0, argc=0, argv=0x0) at ./src/attach.c:82 #4 0xb7cebf18 in attachFunc (context=0x0, argc=-1211232372, argv=0x1) at ./src/attach.c:114 #5 0xb7d118b6 in sqlite3PagerWrite (pDbPage=0xa8fa470) at ./src/pager.c:4156 #6 0xb7d11fc1 in sqlite3PagerOpen (pVfs=0xa8fa470, ppPager=0xb7d36480, zFilename=0xffffffff , nExtra=-1075745228, flags=-1075745224, vfsFlags=1) at ./src/pager.c:2110 #7 0xb7d2f625 in sqlite3VdbeHalt (p=0xa8fa470) at ./src/vdbeaux.c:1142 #8 0xb7d12243 in yy_find_shift_action (pParser=0x0, iLookAhead=1 '\001') at parse.c:1442 #9 0xb7d12347 in yy_destructor (yymajor=---- _2007-Sep-11 14:58:54 by anonymous:_ {linebreak} Just a shot in the dark - do you have the correct system header files for your version of GLIBC? ---- _2007-Sep-11 15:02:42 by anonymous:_ {linebreak} sqlite 3.5 does not call pthread_getspecific ---- _2007-Sep-11 15:59:51 by anonymous:_ {linebreak} After a little fight, I rollbacked everything on my system to older versions. I'll see whether this problem reappears when Trac 0.11 is released. Meanwhile you can close the ticket, since I cannot provide anymore further info. It must be some sort of configuration issue. Glibc headers might be a good guess. Maybe the latest 3.5.0 was not installed correctly. ---- _2007-Sep-11 16:17:03 by drh:_ {linebreak} Anonymous users can close tickets in CVSTrac. You could have closed this ticket yourself. #c8c8c8 2636 code closed 2007 Sep anonymous 2007 Sep 1 1 Bug in sqlite3_prepare_v2 If we prepare two statements using sqlite3_prepare_v2, lets say DELETE FROM table WHERE ID = ?;{linebreak} and{linebreak} UPDATE table SET ID = ID - ? WHERE ID > ?; at the same time, bind values to it Then execute each of the statements, they get executed, but the changes to the database from the second (UPDATE) statement does not show up in the database. _2007-Sep-11 07:21:12 by danielk1977:_ {linebreak} Can you post a short program to demonstrate the problem? From the report, I'm not sure exactly how to reproduce it. _2007-Sep-12: by drh:_ {linebreak} Unable to reproduce and original submitter is not responding. #cfe8bd 2633 build fixed 2007 Sep anonymous 2007 Sep 1 1 building with gcc3.3.4 - undefined reference to `sqlite3KeywordCode' I'm using gcc 3.3.4 with glibc 2.3.2 trying to cross compile for arm. My configure command line: ../sqlite-3.4.2/configure --disable-readline --disable-tcl --host=arm-linux --prefix=/usr/local/opt/crosstool/arm-linux/gcc-3.3.4-glibc-2.3.2/arm-linux/ During the build I have to compile lemon and mkkeywordhash for my native system and restart the build. Otherwise the build goes smoothly until I get here: ... ranlib .libs/libsqlite3.a creating libsqlite3.la (cd .libs && rm -f libsqlite3.la && ln -s ../libsqlite3.la libsqlite3.la) ./libtool --mode=link gcc -I/usr/local/opt/crosstool/arm-linux/gcc-3.3.4-glibc-2.3.2/arm-linux/include -I. -I../sqlite-3.4.2/src -DNDEBUG -DTHREADSAFE=0 -DSQLITE_THREAD_OVERRIDE_LOCK=-1 -DSQLITE_OMIT_LOAD_EXTENSION=1 -L/usr/local/opt/crosstool/arm-linux/gcc-3.3.4-glibc-2.3.2/arm-linux/lib -DHAVE_READLINE=0 \ -o sqlite3 ../sqlite-3.4.2/src/shell.c libsqlite3.la \ gcc -I/usr/local/opt/crosstool/arm-linux/gcc-3.3.4-glibc-2.3.2/arm-linux/include -I. -I../sqlite-3.4.2/src -DNDEBUG -DTHREADSAFE=0 -DSQLITE_THREAD_OVERRIDE_LOCK=-1 -DSQLITE_OMIT_LOAD_EXTENSION=1 -DHAVE_READLINE=0 -o .libs/sqlite3 ../sqlite-3.4.2/src/shell.c -L/usr/local/opt/crosstool/arm-linux/gcc-3.3.4-glibc-2.3.2/arm-linux/lib ./.libs/libsqlite3.so -Wl,--rpath -Wl,/usr/local/opt/crosstool/arm-linux/gcc-3.3.4-glibc-2.3.2/arm-linux//lib ./.libs/libsqlite3.so: undefined reference to `sqlite3KeywordCode' ./.libs/libsqlite3.so: undefined reference to `keywordCode' collect2: ld returned 1 exit status make: *** [sqlite3] Error 1 I'm trying to build the shared libraries for use on an arm system. I'd just build the sqlite3.c amalgamation into my apps, but I had a grocery list of build errors in gcc 4.1.2 on my native system and I didn't think my gcc3.3.4 cross-compiler would fair much better. So I'd like to at least get the shared libs working. Any help would be appreciated. I figured out that the build does not warn you if keywordhash.h is not created. Perhaps it should or the build will fail. So after compiling the mkkeywordhash tool for the native machine: gcc -g -o mkkeywordhash ../sqlite-3.4.2/tool/mkkeywordhash.c I then had run: ./mkkeywordhash >keywordhash.h ... manually before restarting the build. Build completes without errors now, but build tools/code generators are inherently non-cross-compiler friendly when they are part of the main build process and have to be compiled into machine executable form before being run. Any possibility of moving these to an interpreter-based language that doesn't require compiling? #c8c8c8 2626 code closed 2007 Sep anonymous 2007 Sep 1 1 The Amalgamation compile error when i compile The Amalgamation 3.4.2 with visual studio 2003 in window, i encounted 3 errors : 'sqlite3IsIdChar':unknown size 'sqlite3OpcodeNames':unkown size 'sqlite3UpperToLower':unkown size i think it is caused by following source code in sqlite3.c: #ifndef SQLITE_PRIVATE # define SQLITE_PRIVATE static #endif it should be: #ifndef SQLITE_PRIVATE # define SQLITE_PRIVATE #endif is that right? It looks like vs2003 doesn't like this kind of forward decl: static int aArray[]; ... static int aArray[] = {1, 2, 3}; But is fine with this: int aArray[]; ... int aArray[] = {1, 2, 3}; By defining SQLITE_PRIVATE as an empty string, you are avoiding constructs like the first block above. It's not ideal, but not harmful either. ---- _2007-Sep-06 13:11:51 by anonymous:_ {linebreak} This is fixed in 3.5. #cfe8bd 2624 code fixed 2007 Sep anonymous 2007 Sep 1 1 Invalid documentation references in sqlite.h.in Documentation in sqlite.h.in contains invalid references to the following functions and #defines: *: SQLITE_LOCK_READ -> _SHARED *: sqlite3_register_vfs -> vfs_register *: sqlite3_unregister_vfs -> vfs_unregister *: sqlite3_find_vfs -> vfs_find *: sqltie3_blob_size -> sqltie3_blob_bytes *: sqlite3_finalise -> sqlite3_finalize ( s vs. z) *: sqlite_column_text -> missing 3 *: sqltie3_blob_size -> ti mixed up #cfe8bd 2623 code fixed 2007 Sep anonymous 2007 Sep 1 1 zeroblob(-1) returns non-NULL from sqlite3_column_blob() Please consider this psydo-code: sqlite3_prepare ("select zeroblob(-1)", DB, &Stmt); sqlite3_step(Stmt); sqlite3_column_blob(Stmt, 0) == NULL; // Error here! sqlite3_column_blob() used to return NULL for "zeroblob(-1)" as well as any other negative length value for SQLite 3.4.2. After the 3.5.0 changes it returns an arbitrary non-NULL value. sqlite3_column_bytes() returns 0 both before and after the changes. I suppose that the pre 3.5.0 NULL result is correct and meaningful. Or is it in fact undefined? #cfe8bd 2621 code fixed 2007 Sep anonymous 2007 Sep 1 5 another typo -Application that implement their own OS interface will require modification. +Any application implementing their own OS interface will require modification. #cfe8bd 2616 code fixed 2007 Sep anonymous 2007 Sep 1 1 Flags parameter not honoured in openDatabase() The flags parameter of openDatabase() is not honored. At least I did not find any further reference to it in the function body. As a result, calling sqlite3_open_v2 with the SQLITE_OPEN_READWRITE flag creates a new database file even though, as advertised in the documentation, it should not. #cfe8bd 2615 code fixed 2007 Sep anonymous 2007 Sep 1 1 winRandomness without effect due to < vs. > mixup os_win.c line 1424 checks that if( sizeof(LPSYSTEMTIME)>=nBuf ), supposedly to make sure that the supplied buffer is never overwritten. However, the comparison should be <= instead. The current implementation prevents that the buffer is filled at all because the check always fails. #c8c8c8 2614 code closed 2007 Sep anonymous 2007 Sep 1 1 Custom VFS still calls default xRandomness If a default VFS is registered, any custom VFS's xRandomness callback is never invoked. The only reference to the xRandomness call is hard coded in random.c, line 65: sqlite3OsRandomness(sqlite3_vfs_find(0), 256, k); This makes it impossible to implement custom random seeds for any VFS other than the default. Correct. This fact has now been made explicit in the documentation. #cfe8bd 2612 code fixed 2007 Sep anonymous 2007 Sep 1 1 enterMutex() misnamer enterMutex() in mem1.c is wrongly named as enterMem(). This results in linker errors due to missing enterMutex() function. #c8c8c8 2609 code closed 2007 Sep anonymous 2007 Sep 1 1 Is non-thread safe binaries dangerous for multi-threaded app design ? Can accessing two threads ( one is the main app one ) to one connection silently corrupt in-memory data so that next commit to write the wrong ones to the disk, for the non-threadsafe binaries ? _2007-Sep-01 20:45:04 by anonymous:_ {linebreak} Forgot to mention: only the main app thread writes ( calls COMMIT), the other thread only scrolls & reads the data. ---- _2007-Sep-01 20:57:07 by anonymous:_ {linebreak} You have to compile sqlite with -DTHREADSAFE=1 if you are to use it in multiple threads in the same program. This question really belongs on the mailing list, not the bug ticket system. ---- _2007-Sep-01 22:13:40 by anonymous:_ {linebreak} Then this issue should be noted on the top of official site, and there should also be thread-safe binaries in the download area. #c8c8c8 2605 build closed 2007 Aug anonymous 2007 Aug 1 3 Amalgamated version 3.4.2 doesn't compile (VC++6) In a project that already used versions 3.3.x through 3.3.17 (successfully) in "amalgamated" source, I substituted sqlite3.c and sqlite3.h from version 3.4.2, but I yield three compilation errors: ----------------------------------------------------------------- Compiling... {linebreak} sqlite3.c {linebreak} c:\lavoro\nonclassificato\resanddev\hcsprog\sqlite\sqlite3.c(6187) : error C2133: 'sqlite3UpperToLower' : unknown size {linebreak} c:\lavoro\nonclassificato\resanddev\hcsprog\sqlite\sqlite3.c(9310) : error C2133: 'sqlite3OpcodeNames' : unknown size {linebreak} c:\lavoro\nonclassificato\resanddev\hcsprog\sqlite\sqlite3.c(47159) : error C2133: 'sqlite3IsIdChar' : unknown size {linebreak} Error executing cl.exe. {linebreak} ----------------------------------------------------------------- They all are related to array declaration missing size in brackets. Duplicate of ticket #2574. Already fixed. #c8c8c8 2603 new closed 2007 Aug anonymous 2007 Oct 1 3 B-tree and B+tree allow bidirectional cursor but VDBE does not. B-tree and B+tree allow bidirectional cursor but VDBE does not. We would very much like to have this capability and our fallback is to work with the btree api directly. the closest opcodes are: rewind p1 p2 p3 p1 is p2 is line number if rowcount=p1 and step What we would like to see is something like rstep p1 p1= signed integer for direction of relative motion repos p1 p2 p1=unsigned integer representing low-order 32 bits of 64 bit integer p2=unsigned integer representing hi-order 32 bits of 64 bit integer If this is doable in your opinion then we would be willing to look at implementing it and contributing it to the project. _2007-Oct-12 18:58:46 by drh:_ {linebreak} I'm not really sure what the description above is saying, but my best guess is that it is a request for logic to do a ScrollingCursor. Please see the wiki page for how to accomplish ScrollingCursor in SQLite. #cfe8bd 2602 code fixed 2007 Aug anonymous 2007 Aug 1 1 ATTACH leaks memory Attaching a non-existing file to an empty database leaks memory. The scenario (Win32): *: Create a new empty database. *: sqlite3_prepare_v2 ( "ATTACH DATABASE 'C:\NonExistantFolder\NonExistantFile.db3' AS a;'); returns SQLITE_OK *: sqlite3_step(); returns SQLITE_ERROR *: sqlite3_finalize(); returns SQLITE_ERROR *: Close database and application. This returns a report about a single leak between 77 and 92 bytes in size. FYI: I also receive a 2nd, but smaller (21-28 bytes), leak when executing an FTS1 SELECT on an attached database. If you can confirm the 1st leak I will provide more information on the 2nd, if it still persists. _2007-Aug-30 14:21:25 by drh:_ {linebreak} I am unable to replicate the problem on Linux. My tests show all allocated memory is freed. Both SQLite's own internal accounting reports this, and valgrind too. We observe that the windows OS driver calls malloc() directly, bypassing SQLites internal memory allocator and its accounting procedures. And we do not know of anything like valgrind for windows. So perhaps there is a leak in the windows OS driver someplace. How do you see this leak, Ralf? What tools are you using? Do you get any indication where the leak is occurring? Can you see anything in the windows OS driver that might be causing the problem? ---- _2007-Aug-30 15:37:03 by anonymous:_ {linebreak} Found it: The if block in os_win.c, starting at line 1136, does not free zConverted after the else. The fix is to move the free(zConverted); line up into the 1st if block: if( h==INVALID_HANDLE_VALUE ){ free(zConverted); if( flags & SQLITE_OPEN_READWRITE ){ return winOpen(0, zName, id, ((flags|SQLITE_OPEN_READONLY)&~SQLITE_OPEN_READWRITE), pOutFlags); }else{ return SQLITE_CANTOPEN; } } #c8c8c8 2601 code closed 2007 Aug anonymous 2007 Oct 1 1 Problem with opening database with Unicode filename Problem with opening database with Unicode filename - it doesn't open the database with Unicode filename or path. The description contains insufficient information for us to act on the problem. #cfe8bd 2600 code fixed 2007 Aug anonymous 2007 Aug 1 1 #ifdef SQLITE_MUTEX_WIN32 naming error in mutex_w32.c? Line 23 in mutex_w32.c checks #ifdef SQLITE_MUTEX_WIN32. However, SQLITE_MUTEX_WIN32 is never defined in the SQLite core. Instead, SQLITE_MUTEX_WIN is (notice the missing 32 postfix). mutex_w32.c line 182 #endif /* SQLITE_MUTEX_WIN */ makes me believe that this is just a spelling error? If there are plans for extra support of non-Win32 platforms (Win64, Windows 3.1), it would be reasonable to choose _WIN32 over just _WIN. But then I suppose that this would also be true for the OS_WIN #define as well? #cfe8bd 2599 code fixed 2007 Aug anonymous 2007 Aug 1 1 OS check in mutex.h has no effect OS check in mutex.h (starting from line 34) dependes on OS autodetection in os.h. However, in sqliteInt.h the statement #include "mutex.h" is in line 76, way before #include "os.h" is followed in line 297. OS autodetection is therefore not taken into account for mutex.h and the library is compiled without mutexes, at least in Win32. It did work before mutex.c was split up for different OSes. #cfe8bd 2597 code fixed 2007 Aug anonymous 2007 Oct 1 1 tclsqlite "db eval" with array-name doesn't have "*" entry. (tcl8.5a7) This problem was caused on tcl cvs head(tcl8.5a7) only. Tcl8.4 and 8.5a6 doesn't have the problem. package require sqlite3 sqlite db :memory: db eval "create table t (c1, c2, c3)" db eval "PRAGMA empty_result_callbacks = on" db eval "select * from t" val "" puts [array get val] I expected a value "* {c1 c2 c3}", but the val(*) entry didn't exist. It seems to me that "Tcl_DecrRefCount(pStar);" cause the problem... _2007-Aug-30 03:18:14 by anonymous:_ {linebreak} I'm sorry for the bad format. package require sqlite3{linebreak} sqlite db :memory:{linebreak} db eval "create table t (c1, c2, c3)"{linebreak} db eval "PRAGMA empty_result_callbacks = on"{linebreak} db eval "select * from t" val ""{linebreak} puts [array get val]{linebreak} #c8c8c8 2593 code closed 2007 Aug anonymous 2007 Aug 1 1 btshared unlock bug You have want to avoid a race condition by zeroing p->locked within the mutex. Otherwise another thread may acquire the lock, set locked to 1 (while still holding the mutex) and then this thread outside the mutex will unintentionally clear it. @@ -191,6 +193,7 @@ p->wantToLock--; if( p->wantToLock==0 ){ + p->locked = 0; sqlite3_mutex_leave(p->pBt->mutex); - p->locked = 0; } } } Haven't looked at the code, there may be other occurances of this. _2007-Aug-28 17:57:26 by drh:_ {linebreak} The p->locked variable is protected by a separate mutex, specifically the mutex at p->pSqlite->mutex. The database handle, the thing that sqlite3_open() returns, is at p->pSqlite. Each such handle has its own mutex. Associated with each database handle is one or more Btree structures, one for each open database. There are typically two of these, one for the main database and another for the TEMP database. New Btree structures are added for each ATTACH. Each Btree is tied to a single database handle so it is protected by the same mutex as the database handle. "p" is a pointer to a Btree in this instance. The guts of a database are contained in a Btshared structure. Btree points to Btshared. But if shared cache is enabled, two or more Btree structures from different database handles might point to the same Btshared. So Btshared needs its own mutex. This new mutex covers only the Btshared. The Btree structure is covered by the original database handle mutex. See also #2578 #c8c8c8 2592 warn closed 2007 Aug anonymous 2007 Aug 1 1 The Entry C:\Windows\Temp\sqlite_z6onAYdsVGqLG3Q is Invalid Everytime I reboot and check disk I get " The Entry C:\Windows\Temp\sqlite_z6onAYdsVGqLG3Q is Invalid I believe this has something to do with skype and all messengers rebooting my PC everytime I try to enable voice Thank you This is a question for a skype group. #cfe8bd 2588 code fixed 2007 Aug anonymous 2007 Aug 1 1 bug in sqlite3_mutex_leave Race scenario on same mutex leading to corruption: Thread 1 Thread 2 -------------------------- -------------------------- sqlite3_mutex_enter pthread_mutex_lock ok p->owner = Thread 1 p->nRef = 1; sqlite3_mutex_leave p->nRef--; pthread_mutex_unlock ok sqlite3_mutex_enter sqlite3_mutex_enter pthread_equal is true pthread_equal is false pthread_mutex_lock ok p->owner = Thread 2; p->nRef = 1; p->nRef>0 is true p->nRef++; p->nRef is now 2 and the mutex is "owned" by Thread 2. Any data structure protected by this mutex can now be changed by both threads at the same time. Here's a possible pseudo fix:, yypminor=0x1) at parse.c:1337 #10 0xb7d1279c in sqlite3Parser (yyp=0xa8fa470, yymajor= , yyminor= {z = 0xa8fec48 "system", dyn = 0, n = 86973048}, pParse=0xb7d39bc8) at parse.c:3268 #11 0xb7d1288f in sqlite3Parser (yyp=0xbfe1734c, yymajor=-1075744936, yyminor= {z = 0xa906428 "�l\212\no", dyn = 0, n = 86973072}, pParse=0xbfe17048) at parse.c:3477 #12 0xb7cf46da in ?? () at ./src/btree.c:2498 from /usr/lib/libsqlite3.so.0 #13 0xbfe1734c in ?? () #14 0xb7d39bc8 in ?? () from /usr/lib/libsqlite3.so.0 #15 0x0a906428 in ?? () #16 0x0a5e3520 in ?? () #17 0xbfe17048 in ?? () #18 0xb7f18300 in ?? () from /lib/ld-linux.so.2 #19 0xb7d16c56 in sqlite3Pragma (pParse=0xbfe1734c, pId1=0xa8fec48, pId2=0x0, pValue=0xb757a700, minusFlag=173792084) at ./src/pragma.c:601
Index: src/mutex.c =================================================================== RCS file: /sqlite/sqlite/src/mutex.c,v retrieving revision 1.9 diff -u -3 -p -r1.9 mutex.c --- src/mutex.c 24 Aug 2007 20:46:59 -0000 1.9 +++ src/mutex.c 25 Aug 2007 01:39:10 -0000 @@ -358,6 +358,7 @@ void sqlite3_mutex_leave(sqlite3_mutex * assert( p->nRef>0 ); p->nRef--; if( p->nRef==0 ){ + memset(&p->owner, 0, sizeof(p->owner)); pthread_mutex_unlock(&p->mutex); } }But even this fix is not guaranteed by POSIX standards, as an opaque pthread_t type when memset to zero may indeed be a valid pthread_t for the first (zeroeth) thread. This could lead to the same race condition and corruption. http://mail-archives.apache.org/mod_mbox/apr-dev/200307.mbox/%3c3F285220.7010501@netscape.com%3e This stuff is difficult to get right. Please consult correct recursive mutex implementations from various multi-threaded open source projects. _2007-Aug-25 02:35:35 by anonymous:_ {linebreak} Furthermore, the pthread_equal man page states: "If either t1 or t2 are not valid thread IDs, the behavior is undefined." http://www.opengroup.org/onlinepubs/009695399/functions/pthread_equal.html Only pthread_create and pthread_self can generate a valid pthread_t thread ID. ---- _2007-Aug-25 02:43:32 by anonymous:_ {linebreak} On POSIX systems that support it you should use PTHREAD_MUTEX_RECURSIVE instead of rolling your own. It is more efficient and guaranteed to be correct. http://www.opengroup.org/onlinepubs/000095399/functions/pthread_mutex_lock.html ---- _2007-Aug-25 03:23:56 by anonymous:_ {linebreak} Amusing back story of recursive POSIX mutexes from one of its creators: http://groups.google.com/group/comp.programming.threads/msg/d835f2f6ef8aed99?hl=en ---- _2007-Aug-25 04:13:08 by drh:_ {linebreak} I believe the problem is fixed by doing the p->nRef>0 test first and then the ownership test second. Rationale: 1: If p->nRef>0 it can only mean that some thread is holding the mutex. we don't know which thread, and the mutex might change owners multiple times before the next test. But we do at least know that somebody has held the mutex in the past. And we also know that p->owner has been initialized. 2: If the second pthread_equal() test is true, that means that the current thread claims to own the mutex. No other thread will ever put the current thread's threadid into p->owner. If p->owner==self, then only the current thread put it there. If another thread had claimed the mutex after the current thread, then the other thread would have changed the value of p->owner before raising p->nRef above zero. We know that p->nRef has been above zero, so the current thread must be the last one to have held the mutex. Hence, the current thread must still own the mutex. The above assumes that pthread_equal() is an atomic operation. In other words, it will not fail with a TRUE return if a separate thread changes the value of p->owner while the current thread is testing it. If p->owner is a multi-word value, then pthread_equal() might not be atomic. But pthread_t is an integer on every implementation of pthreads that I am aware of, so I think this is probably safe in most instances. Please double-check my reasoning above and let me know if I've said or done something goofy. Tnx. ---- _2007-Aug-25 04:25:26 by drh:_ {linebreak} Response to Butenhof: Threading in SQLite is not designed to make it more concurrent. It is designed to make the code safer to use by people who insist on writing multithreaded programs when multithreading really isn't necessary. Each database connection has its own mutex. Any thread using the database connection holds that mutex. So only a single thread can use a particular database connection at a time. The mutex is acquired when the API is entered and released when the API exits. But SQLite is reentrant. Top-level APIs can (and are) invoked as callbacks from within other top-level APIs. The only way to allow reentancy for one thread while excluding other threads (the only way that *I* know of, anyhow) is a recursive mutex. If David Butenhof thinks that recursive mutexes can be eliminated from SQLite by do "careful design", I'd like to hear him tell me how. One suspects that Mr. Butenhof has never tried to build a reentrant library for use in a multithreaded environment.... ---- _2007-Aug-25 04:47:52 by anonymous:_ {linebreak} My first impression is that the fix is not correct. Take a look at this post and the accompanying thread: http://groups.google.com/group/comp.programming.threads/msg/329ba20fe934b5e1?hl=en& pthread_equal() is probably not atomic on HPUX where pthread_t is a struct (of 4 int's as I recall). Nothing in the pthread_equal man page suggests that pthread_equal is guaranteed to be atomic, although on platforms where pthread_t is an int it would obviously be fine. Also, there is no concept of an official invalid pthread_t value - and even if there was, the pthread_equal man page is pretty clear that the result of such a comparison with an "invalid" pthread ID is undefined. For what it is worth, Apache Portable Runtime seems to have given up trying to roll their own recursive mutex on POSIX platforms that do not support PTHREAD_MUTEX_RECURSIVE. I appreciate that your use of recursive mutexes is for correctness and ease of API use. An alternate scheme would be to change the SQLite API to simply pass messages via work queues to a background thread that performs the database operations on your behalf. Much like your previous single thread "server" model. But then you have the headache of making every single SQLite API function work asynchronously - and you still would not improve concurrency on the same database. ---- _2007-Aug-25 05:05:17 by anonymous:_ {linebreak} This is somewhat related. When you work with SMP hardware and various optimizing compilers some things are not obvious. Take a look at the seemingly logical double-checked lock mis-pattern: http://www.cs.umd.edu/~pugh/java/memoryModel/DoubleCheckedLocking.html http://en.wikipedia.org/wiki/Double-checked_locking ---- _2007-Aug-25 14:45:21 by drh:_ {linebreak} Well, it turns out that SuSE Linux does support recursive mutexes after all. But in order to get it to work you have to have a line #define _XOPEN_SOURCE 500 prior to your #include
+ if( !isInit ){ + pthread_mutex_lock(&initMutex); + if( !isInit ){ + pthread_mutexattr_init(&recursiveAttr); + pthread_mutexattr_settype(&recursiveAttr, PTHREAD_MUTEX_RECURSIVE); + } + isInit = 1; + pthread_mutex_unlock(&initMutex); + }Although the liklihood of a problem occurring is rare, it is still present. Since SQLite does not create many recursive mutexes I don't think it is worth to try to optimize this case. The calls to pthread_mutexattr_init and pthread_mutexattr_settype are typically very fast. Please consider using this simpler code instead: case SQLITE_MUTEX_RECURSIVE: { p = sqlite3MallocZero( sizeof(*p) ); if( p ){ pthread_mutexattr_t recursiveAttr; pthread_mutexattr_init(&recursiveAttr); pthread_mutexattr_settype(&recursiveAttr, PTHREAD_MUTEX_RECURSIVE); pthread_mutex_init(&p->mutex, &recursiveAttr); p->id = iType; } break; } ---- _2007-Aug-25 16:26:01 by anonymous:_ {linebreak} Forgot the pthread_mutexattr_destroy... case SQLITE_MUTEX_RECURSIVE: { p = sqlite3MallocZero( sizeof(*p) ); if( p ){ pthread_mutexattr_t recursiveAttr; pthread_mutexattr_init(&recursiveAttr); pthread_mutexattr_settype(&recursiveAttr, PTHREAD_MUTEX_RECURSIVE); pthread_mutex_init(&p->mutex, &recursiveAttr); pthread_mutexattr_destroy(&recursiveAttr); p->id = iType; } break; } ---- _2007-Aug-25 16:34:59 by drh:_ {linebreak} You will notice that, unlike the examples cited previously, my double-lock was in fact correct. Nevertheless, your observation that recursive mutexes are rarely allocated (only at sqlite3_open()) and that the initialization and destruction of a pthread_mutexattr_t is relatively cheap are valid. And the code is simpler without the double-lock. So the double-lock has now been removed. ---- _2007-Aug-25 16:48:30 by anonymous:_ {linebreak} Well, I disagree about your double lock being correct. There is a window of failure on SMP machines on some hardware with certain optimizing compilers. http://www.cs.umd.edu/~pugh/java/memoryModel/DoubleCheckedLocking.html Nonetheless, the recursive mutex code as of the latest checkin is fine. The speed of millions of lock/unlock calls of native POSIX recursive mutexes more than makes up for the few cycles of initializing the attribute. Some older OSes will undoubtedly have trouble with _XOPEN_SOURCE, but this is easy enough for the odd port to fix. #c8c8c8 2585 code closed 2007 Aug anonymous 2007 Oct anonymous 1 1 Error Function GetAtomNameA The Procedure entry point GetAtomNameA could not be located in the dynamic link library sqlite3.dll Using CodeBlock _2007-Oct-05 15:11:29 by drh:_ {linebreak} Nothing in SQLite invokes GetAtomNameA(). This must be a problem with some other library. #c8c8c8 2584 new closed 2007 Aug anonymous 2007 Aug 1 2 On Windows function sqlite3WinSleep needs to dispatch messages When sqlite is busy, it spins in the loop, re-trying call every 100 ms. On Windows if the caller thread runs a message loop (for example a GUI application of COM call from an appartment), the message loop stops and the application hangs. As a workaround, sqlite function sqlite3WinSleep needs to call PeekMessage with subsequent calls TranslateMessage/DispatchMessage to continue message processing as following:{linebreak} /*{linebreak} ** Sleep for a little while. Return the amount of time slept.{linebreak} */{linebreak} int sqlite3WinSleep(int ms){linebreak} {{linebreak} //----------------------------------------------------------------{linebreak} //The fix starts{linebreak} MSG msg;{linebreak} while (PeekMessage(&msg, NULL, 0, 0, PM_REMOVE)){linebreak} {{linebreak} TranslateMessage(&msg);{linebreak} DispatchMessage (&msg);{linebreak} if (msg.message == WM_QUIT) //Note - need to handle WM_QUIT,posted by PostQuitMessage{linebreak} break;{linebreak} }{linebreak} //The fix ends{linebreak} //-----------------------------------------------------------------{linebreak} Sleep(ms);{linebreak} return ms;{linebreak} }{linebreak} _2007-Aug-23 16:08:43 by drh:_ {linebreak} Processing GUI events is not the business of a database engine. If you need to process GUI events while waiting on a lock, then use the sqlite3_busy_handler() API to register your own busy handler (perhaps using the code shown in the patch) instead of using the built-in sqlite3_busy_timeout(). ---- _2007-Aug-23 17:54:06 by anonymous:_ {linebreak} You should wrap in a thread your db calls if you wan't to process using GetMessage() calls #c8c8c8 2581 code closed 2007 Aug anonymous 2007 Aug 1 1 ./configure && make broken: new source files: mem[12].c, mutex.c, etc Please apply the diff given by this command to Makefile.in: cvs diff -D'2007-08-14' main.mk _2007-Aug-21 20:31:44 by drh:_ {linebreak} We are in the middle of a major reimplementation of large sections of SQLite. We could fix the makefile, but the build still wouldn't work. So what's the point, really. I promise to fix the makefile before the next release.... #f2dcdc 2580 code active 2007 Aug anonymous 2007 Aug anonymous 1 2 Can't open a query if text to search is Greek for example: SELECT * FROM mytable WHERE mycolumn LIKE '%some greek text%' I get wrong results, using the 3.4.2 version. No problem instead using other earlier version. I tested only in Windows. #c8c8c8 2578 code closed 2007 Aug anonymous 2007 Aug 1 1 sqlite3BtreeEnter, sqlite3BtreeLeave threading issues p->wantToLock++ is not an atomic operation and must be done within a mutex lock, otherwise its result is undefined. Same goes for "variable--". You need an architecture-specific operations to do that safely between threads. See http://www.mozilla.org/projects/nspr/reference/html/pratom.html and http://lxr.mozilla.org/nspr/source/nsprpub/pr/include/pratom.h#60 for an example.
void sqlite3BtreeEnter(Btree *p){ Btree *pLater; /* Some basic sanity checking on the Btree. The list of Btrees ** connected by pNext and pPrev should be in sorted order by ** Btree.pBt value. All elements of the list should belong to ** the same connection. Only shared Btrees are on the list. */ assert( p->pNext==0 || p->pNext->pBt>p->pBt ); assert( p->pPrev==0 || p->pPrev->pBtThis needs fixing:pBt ); assert( p->pNext==0 || p->pNext->pSqlite==p->pSqlite ); assert( p->pPrev==0 || p->pPrev->pSqlite==p->pSqlite ); assert( p->sharable || (p->pNext==0 && p->pPrev==0) ); /* Check for locking consistency */ assert( !p->locked || p->wantToLock>0 ); assert( p->sharable || p->wantToLock==0 ); if( !p->sharable ) return; p->wantToLock++; if( p->locked ) return; /* In most cases, we should be able to acquire the lock we ** want without having to go throught the ascending lock ** procedure that follows. Just be sure not to block. */ if( sqlite3_mutex_try(p->pBt->mutex)==SQLITE_OK ){ p->locked = 1; return; }
Index: src/btree.c =================================================================== RCS file: /sqlite/sqlite/src/btree.c,v retrieving revision 1.401 diff -u -3 -p -r1.401 btree.c --- src/btree.c 17 Aug 2007 16:50:38 -0000 1.401 +++ src/btree.c 17 Aug 2007 20:42:49 -0000 @@ -1403,8 +1403,8 @@ void sqlite3BtreeEnter(Btree *p){ assert( pLater->pNext==0 || pLater->pNext->pBt>pLater->pBt ); assert( !pLater->locked || pLater->wantToLock>0 ); if( pLater->locked ){ - sqlite3_mutex_leave(pLater->pBt->mutex); pLater->locked = 0; + sqlite3_mutex_leave(pLater->pBt->mutex); } } sqlite3_mutex_enter(p->pBt->mutex); @@ -1427,8 +1427,8 @@ void sqlite3BtreeLeave(Btree *p){ p->wantToLock--; if( p->wantToLock==0 ){ assert( p->locked ); - sqlite3_mutex_leave(p->pBt->mutex); p->locked = 0; + sqlite3_mutex_leave(p->pBt->mutex); } } }Otherwise there would be corruption if there was a context switch just after sqlite3_mutex_leave to "if( p->locked ) return;" in sqlite3BtreeEnter() in another thread. You might want to replace the u8's below with int's if you intend to read or write to them outside of a mutex:
struct Btree { sqlite3 *pSqlite; /* The database connection holding this btree */ BtShared *pBt; /* Sharable content of this btree */ u8 inTrans; /* TRANS_NONE, TRANS_READ or TRANS_WRITE */ u8 sharable; /* True if we can share pBt with other pSqlite */ u8 locked; /* True if pSqlite currently has pBt locked */ int wantToLock; /* Number of nested calls to sqlite3BtreeEnter() */ Btree *pNext; /* List of Btrees with the same pSqlite value */ Btree *pPrev; /* Back pointer of the same list */ };Reading from and writing to a non-machine word (i.e., not an int) variable outside of a mutex is not atomic or threadsafe. MIPS, for example, can only manipulate 32 bit values at a time and must use bit operations and shifting to get at individual characters. If you read a char value at the wrong time it may contain 0, 255 or some other value. _2007-Aug-17 21:32:11 by anonymous:_ {linebreak} These 3 values will likely be stored in the same 32 bit integer: u8 inTrans; /* TRANS_NONE, TRANS_READ or TRANS_WRITE */ u8 sharable; /* True if we can share pBt with other pSqlite */ u8 locked; /* True if pSqlite currently has pBt locked */ Writing to any one of them outside of a mutex in a multithreaded situation can scramble the others' values. ---- _2007-Aug-20 00:49:28 by drh:_ {linebreak} Not a bug. The Btree.wantToLock values are accessed under the sqlite3.pMutex lock - or at least they will be once we get the new threading implementation half way completed. ---- _2007-Aug-20 05:17:47 by anonymous:_ {linebreak} Helgrind is useful to find these sorts of multi-thread race conditions, as well as mutex deadlocks. http://valgrind.org/docs/manual/hg-manual.html #c8c8c8 2576 code closed 2007 Aug anonymous 2007 Aug 1 1 Check-in [4239] mutex race conditions Nevermind. I missed seeing SQLITE_MUTEX_STATIC_MASTER. #c8c8c8 2575 code closed 2007 Aug anonymous 2007 Aug 1 1 sqlite3_mutex_enter missing return I know this is experimental #if'd out code, but nonetheless, sqlite3_mutex_enter is missing a return for the final else clause. #c8c8c8 2572 warn closed 2007 Aug anonymous 2007 Aug 1 1 Sqlitw error unrecognize token Sqlite error unrecognize token The "is human" test needs to be improved. ---- _2007-Aug-14 21:20:44 by anonymous:_ {linebreak} wa? #c8c8c8 2571 todo closed 2007 Aug anonymous 2007 Aug 1 1 SQLite opening in Access I am trying to open SQLite database through ODBC connections in Microsoft Access 2000. Some tables in Database have Primary key defined . Steps followed:- System DSN TestSQLite is created using Data sources(ODBC) 1)Open Blank access database named db1.mdb 2)Click on FIle->Get External data->Link tables 3)Select - ODBC databases in Files of type in Link Dialog 4)Select Machine data source TestSQLite=20 5)Select All Tables and click Ok 6)If table does not have Primary key ,getting dialog to select unique record identifier. 7)if Primary key is defined in table ,getting error Reserved error (-7748) There is no msg for this error . =20 I can open tables which does not have Primary Key and cannot use MSAccess to open SQLite database through ODBC (if the SQLite database has primary key defined) . Thanks for your help. _2007-Aug-13 21:23:54 by anonymous:_ {linebreak} Install this sqlite odbc driver. http://www.ch-werner.de/sqliteodbc/ #cfe8bd 2565 code fixed 2007 Aug drh 2007 Aug 1 1 Database corruption following ROLLBACK when soft_heap_limit set With {link: /pragma.html#pragma_auto_vacuum autovacuum mode} turned on low {link: /capi3ref.html#sqlite3_soft_heap_limit sqlite3_soft_heap_limit} database corruption results following a {link: /lang_transaction.html ROLLBACK} of a CREATE TABLE. The following script exhibits the problem: -- set sqlite3_soft_heap_limit to 5000 PRAGMA auto_vacuum=1; CREATE TABLE t1(x); INSERT INTO t1 VALUES(hex(randomblob(5000))); BEGIN; CREATE TABLE t2 AS SELECT * FROM t1; ROLLBACK; PRAGMA integrity_check; This problem was discovered following the enhanced sqlite_soft_heap_limit testing facility added by check-in [4202]. As of this writing, it has not been observed in the wild. _2007-Aug-11 11:55:04 by drh:_ {linebreak} **Background** The rollback journal file consists of a header followed by zero or more page records. Each page record contains original database content which can be used to restore the database back to its original state during a ROLLBACK or when the database is being recovered after a power-loss or crash. The header contains, among other things, the "nRec" field which is a count of the number of pages to follows. Whenever changes are flushed to the database file, the size of the journal is padded out to the next multiple of the disk sector size and a new header is written. (This is a defense against certain kinds of file corruption that might occur during a power failure.) A single journal can contain any number of instances of header followed by zero or more pages. While the journal is being constructed, the nRec value is 0. Then, just prior to writing changes back to the database file, the nRec value is changed to the correct number of records in the journal, the journal file is synced to disk, and only then is it safe to write to the database file. This all works great. **The Problem** The problem is that the ROLLBACK command also uses the journal file to restore changes. But the ROLLBACK command treats the nRec==0 value specially. When the ROLLBACK command sees an nRec of 0, it figures that it was just in the middle of doing some changes that haven't yet by written to the database file, so it tries to recompute the "correct" nRec value based on the current file size. The problem arises when a header contains an nRec value which really is suppose to be zero (no pages written into the journal) but that header is followed by one or more additional headers that contain pages that do need to be rolled back. When a journal is being rolled back during recovery, the recovery process sees the zero nRec, skips to the next header, and every thing works peachy. But during the ROLLBACK command, the zero nRec is converted into some positive value based on the journal file size. The new positive nRec is wrong. The ROLLBACK quickly discovers this and ignores the rest of the journal file under the assumption that it corrupted. This results in an incomplete ROLLBACK and database file corruption. **The Fix** The fix in check-in [4208] is to change the ROLLBACK process so that it only applies the special interpretation to an nRec==0 header if it is the very last header in the journal. **Why We Have Not Seen This Before** The problem only comes up when a header is written into the journal with nRec==0 and subsequent headers are written afterwards. This can only happen when the pager is having great difficulty finding new buffers into which new pages can be loaded and is having to spill dirty pages back to the disk. The minimum cache size is 10, and as it turns out a cache size of 10 still gives a handful of buffered pages before a cache spill occurs, even under the most severe circumstances. So even with a very small cache, the nRec field was always ending up larger than 0. But, if you set the soft heap limit to a ridiculously small number - so small that the pager is under continuous pressure to spill pages back to the database file as soon as they are unpinned - then there are cases where the pager will write non-terminal headers to the journal with nRec==0. Hence, as far as we are aware, you cannot hit this problem as long as you leave the soft heap limit disabled (which is the default configuration) or you set the soft heap limit large enough so that there are always enough memory for the pager to hold 10 or more pages in memory at the same time. It is difficult to imagine a real-world system that would do something different, so even though this problem has serious consequences, we cannot see how it might actually come up in practice. The problem was only noticed when we started stress-testing the soft heap limit mechanism by setting very low soft heap limits and running full regression tests. #c8c8c8 2563 code closed 2007 Aug anonymous 2007 Aug 1 1 sqlite3_column_database_name16() is broken =sqlite3_column_database_name16()= does not work at all. However I form my query, regardless of how many fields and in what order are returned in result, it always returns NULL for every single column. I'm using this code and it never returned anything but NULL: if ( pStatement) { UINT32 cols = sqlite3_column_count( pStatement); for ( UINT16 currCol = 0; !isDatabaseNameRetrieved && ( currCol < cols ); ++currCol ) { const WCHAR *databaseName = static_cast
create table test (
id INTEGER PRIMARY KEY,
name varchar(50) not null,
age integer not null
);
Then: insert into test (name,age) values ('foo',22);
insert into test (name,age) values ('foo',23);
insert into test (name,age) values ('bar',22);
insert into test (name,age) values ('bar',35);
insert into test (name,age) values ('bar',72);
Now try this; sqlite> .headers on
sqlite> select test.name, test.age from test order by name;
name|age
bar|22
bar|35
bar|72
foo|22
foo|23
sqlite> select test.name, test.age from test group by name;
test.name|test.age
bar|72
foo|23
You see ? if i use "GROUP BY", the field name contains tablename. Because i use "SELECT test.name" and not "SELECT name". If i set an alias, i get alias, that's ok. The trouble appears to be very high on Copix (http://wwW.copix.org). We create some DAO (Data Access Objects) automatically. The "groupBy" method doesn't works with SQLite... Is this normal ? Mysql, PostgreSql, Oracle... doesn't need to create alias. _2007-Jul-31 15:54:52 by anonymous:_ {linebreak} There's probably 4 other tickets reporting this. I don't think it will get fixed. The workaround is to use aliases (AS "whatever") for the selected columns. ---- _2007-Jul-31 23:02:19 by anonymous:_ {linebreak} Ok, we have created a special support for SQLite. PS: I love this database :) Simple, nice, usefull, quick and easy Regards
#f2dcdc 2543 code active 2007 Jul anonymous 2007 Jul 1 1 Chinese charset not support?? when i create a table. the table name is " " (chinese) after this "alter table add column aaa text null" error why??/ thank you
#c8c8c8 2538 code closed 2007 Jul anonymous 2007 Jul 1 1 calling sqlite3_interrupt() randomly leaves db->ul.isInterrupted = 1 Using a sequence as follows, all invoked with sqlite3_exec(): BEGIN; ... long running script with many UPDATE/INSERT statements (with triggers firing, etc)... sqlite3_interrupt() is called Often (always?) the next sqlite3_exec call will also return SQLITE_INTERRUPT instead of SQLITE_OK. Apparently there is at least one point in sqlite execution that can be interrupted with sqlite3_interrupt() where db->ul.isInterrupted is not reset to 0, so the next sqlite3_exec() call also to returns SQLITE_INTERRUPT. Since the documentation states that calling sqlite3_interrupt() will rollback any EXPLICIT transactions (why specifically EXPLICIT ?? - I would assume auto [IMPLICIT] transactions would obviously also rollback with a sqlite3_interrupt call), any subsequent action done on the sqlite instance should be SQLITE_OK (or the error specific to that action since the previous call has been fully aborted). This behavior is definitely different than 3.2.8 (the version I am attempting to upgrade from), which works as expected. After much additional testing, I have concluded that this may actually have been due to my error (sqlite3_interrupt() being called again) and not due to sqlite. Sorry for the wasted bandwidth. Please consider the issue closed.
#cfe8bd 2533 code fixed 2007 Jul anonymous 2007 Oct 1 2 temporary files are not deleted on WinCe temporary files are not deleted on WinCe if they are not locked. hMutex is false in struct pFile. function: static void winceDestroyLock(winFile *pFile) line: 16490 in sqlite3.c from sqlite-amalgamation-3_4_1.zip problem: delete temporary files only if pFile->hMutex is true if (pFile->hMutex){ ... if( pFile->zDeleteOnClose ){ DeleteFileW(pFile->zDeleteOnClose); sqliteFree(pFile->zDeleteOnClose); pFile->zDeleteOnClose = 0; } ... } solution: delete temporary files not dependend if pFile->Mutex is true or not if (pFile->hMutex){ ... ... } if( pFile->zDeleteOnClose ){ DeleteFileW(pFile->zDeleteOnClose); sqliteFree(pFile->zDeleteOnClose); pFile->zDeleteOnClose = 0; } regards Juergen Wolters Temporary files never create a lock object, so the hMutex member is always null. Since there is no mutex to cleanup, winceDestroyLock() is never called and temporary files are never deleted. Checkin [3836] needs to be reverted and the original ticket which triggered it re-opened.
#c8c8c8 2531 code closed 2007 Jul anonymous 2007 Jul a.rottmann 1 1 Can't find head files in icu.c /* Include ICU headers */ #include sqlite> create virtual table "a b c" using fts2 (t); FTS2 Create FTS2 sql: CREATE TABLE main.a b c_content(c0t) SQL error: vtable constructor failed: a b c_2007-Jul-18 06:44:21 by anonymous:_ {linebreak} A similar problem shows if a FTS column has the same name as the FTS table: CREATE VIRTUAL TABLE a USING fts2 (a); Returns "vtable constructor failed: a.". #f2dcdc 2510 code active 2007 Jul anonymous 2007 Jul 1 1 Vacuum modified FTS2 rowids VACUUM modifies FTS2 rowids. Here is the test: drop table if exists a; create virtual table a using fts2 (t); insert into a (t) values ('one'); insert into a (t) values ('two'); insert into a (t) values ('three'); select rowid, * from a; delete from a where t = 'two'; vacuum; select rowid, * from a; Unfortunately there is no workaround since table a is auto-generated by the FTS2 module. _2007-Jul-17 14:05:58 by anonymous:_ {linebreak} http://www.sqlite.org/cvstrac/chngview?cn=4157 ---- _2007-Jul-17 14:24:29 by anonymous:_ {linebreak} Yes, this behavior has been recently documented, but there is no user workaround like PRIMARY KEY for FTS2 rowids. Therefore I consider this as a bug which should be fixed in fts2.c. ---- _2007-Jul-17 14:55:57 by anonymous:_ {linebreak} Should virtual tables be VACUUMable? What exactly is being vacuumed here - an internal table? ---- _2007-Jul-17 16:34:55 by shess:_ {linebreak} I agree, I think this is a bug. Rather severe, too, the entire fts system implicitely depends on rowids not changing, this means that vacuum will break fts tables (fts1 or fts2).
drop table if exists t; create virtual table t using fts2; insert into t (content) values ('This is a test'); insert into t (content) values ('This is a string'); insert into t (content) values ('That was a test'); insert into t (content) values ('A random string'); select content from t where t MATCH 'test'; delete from t where content = 'This is a string'; vacuum; select content from t where t MATCH 'test';The first select outputs 'This is a test' and 'That was a test'. The second select outputs 'This is a test', and 'A random string'. ---- _2007-Jul-17 17:27:21 by anonymous:_ {linebreak} This patch seems to address the FTS2 VACUUM problem and passes all fts2 tests. It adds an INTEGER PRIMARY KEY docid column to the hidden %_content table. Note: this new table format is not backwards compatible with existing FTS2 databases. -Joe Wilson
Index: ext/fts2/fts2.c =================================================================== RCS file: /sqlite/sqlite/ext/fts2/fts2.c,v retrieving revision 1.40 diff -u -3 -p -r1.40 fts2.c --- ext/fts2/fts2.c 2 Jul 2007 10:16:50 -0000 1.40 +++ ext/fts2/fts2.c 17 Jul 2007 17:19:49 -0000 @@ -1769,9 +1769,9 @@ typedef enum fulltext_statement { */ static const char *const fulltext_zStatement[MAX_STMT] = { /* CONTENT_INSERT */ NULL, /* generated in contentInsertStatement() */ - /* CONTENT_SELECT */ "select * from %_content where rowid = ?", + /* CONTENT_SELECT */ "select * from %_content where docid = ?", /* CONTENT_UPDATE */ NULL, /* generated in contentUpdateStatement() */ - /* CONTENT_DELETE */ "delete from %_content where rowid = ?", + /* CONTENT_DELETE */ "delete from %_content where docid = ?", /* BLOCK_INSERT */ "insert into %_segments values (?)", /* BLOCK_SELECT */ "select block from %_segments where rowid = ?", @@ -1860,14 +1860,14 @@ static struct fulltext_vtab *cursor_vtab static const sqlite3_module fts2Module; /* forward declaration */ /* Return a dynamically generated statement of the form - * insert into %_content (rowid, ...) values (?, ...) + * insert into %_content (docid, ...) values (?, ...) */ static const char *contentInsertStatement(fulltext_vtab *v){ StringBuffer sb; int i; initStringBuffer(&sb); - append(&sb, "insert into %_content (rowid, "); + append(&sb, "insert into %_content (docid, "); appendList(&sb, v->nColumn, v->azContentColumn); append(&sb, ") values (?"); for(i=0; i---- _2007-Jul-18 00:13:56 by shess:_ {linebreak} BTW, AFAICT this only happens for sqlite3.4. Older versions don't seem to have the problem. ---- _2007-Jul-18 01:31:49 by anonymous:_ {linebreak} The rowid changing after VACUUM predates 3.4.0...nColumn; ++i) @@ -1878,7 +1878,7 @@ static const char *contentInsertStatemen /* Return a dynamically generated statement of the form * update %_content set [col_0] = ?, [col_1] = ?, ... - * where rowid = ? + * where docid = ? */ static const char *contentUpdateStatement(fulltext_vtab *v){ StringBuffer sb; @@ -1893,7 +1893,7 @@ static const char *contentUpdateStatemen append(&sb, v->azContentColumn[i]); append(&sb, " = ?"); } - append(&sb, " where rowid = ?"); + append(&sb, " where docid = ?"); return stringBufferData(&sb); } @@ -2027,15 +2027,15 @@ static int sql_step_leaf_statement(fullt return rc; } -/* insert into %_content (rowid, ...) values ([rowid], [pValues]) */ -static int content_insert(fulltext_vtab *v, sqlite3_value *rowid, +/* insert into %_content (docid, ...) values ([docid], [pValues]) */ +static int content_insert(fulltext_vtab *v, sqlite3_value *docid, sqlite3_value **pValues){ sqlite3_stmt *s; int i; int rc = sql_get_statement(v, CONTENT_INSERT_STMT, &s); if( rc!=SQLITE_OK ) return rc; - rc = sqlite3_bind_value(s, 1, rowid); + rc = sqlite3_bind_value(s, 1, docid); if( rc!=SQLITE_OK ) return rc; for(i=0; i nColumn; ++i){ @@ -2047,7 +2047,7 @@ static int content_insert(fulltext_vtab } /* update %_content set col0 = pValues[0], col1 = pValues[1], ... - * where rowid = [iRowid] */ + * where docid = [iRowid] */ static int content_update(fulltext_vtab *v, sqlite3_value **pValues, sqlite_int64 iRowid){ sqlite3_stmt *s; @@ -2075,7 +2075,7 @@ static void freeStringArray(int nString, free((void *) pString); } -/* select * from %_content where rowid = [iRow] +/* select * from %_content where docid = [iRow] * The caller must delete the returned array and all strings in it. * null fields will be NULL in the returned array. * @@ -2101,10 +2101,10 @@ static int content_select(fulltext_vtab values = (const char **) malloc(v->nColumn * sizeof(const char *)); for(i=0; i nColumn; ++i){ - if( sqlite3_column_type(s, i)==SQLITE_NULL ){ + if( sqlite3_column_type(s, i+1)==SQLITE_NULL ){ values[i] = NULL; }else{ - values[i] = string_dup((char*)sqlite3_column_text(s, i)); + values[i] = string_dup((char*)sqlite3_column_text(s, i+1)); } } @@ -2120,7 +2120,7 @@ static int content_select(fulltext_vtab return rc; } -/* delete from %_content where rowid = [iRow ] */ +/* delete from %_content where docid = [iRow ] */ static int content_delete(fulltext_vtab *v, sqlite_int64 iRow){ sqlite3_stmt *s; int rc = sql_get_statement(v, CONTENT_DELETE_STMT, &s); @@ -2870,7 +2870,7 @@ static int fulltextCreate(sqlite3 *db, v if( rc!=SQLITE_OK ) return rc; initStringBuffer(&schema); - append(&schema, "CREATE TABLE %_content("); + append(&schema, "CREATE TABLE %_content(docid INTEGER PRIMARY KEY, "); appendList(&schema, spec.nColumn, spec.azContentColumn); append(&schema, ")"); rc = sql_exec(db, spec.zDb, spec.zName, stringBufferData(&schema)); @@ -3731,8 +3731,8 @@ static int fulltextFilter( TRACE(("FTS2 Filter %p\n",pCursor)); - zSql = sqlite3_mprintf("select rowid, * from %%_content %s", - idxNum==QUERY_GENERIC ? "" : "where rowid=?"); + zSql = sqlite3_mprintf("select * from %%_content %s", + idxNum==QUERY_GENERIC ? "" : "where docid=?"); sqlite3_finalize(c->pStmt); rc = sql_prepare(v->db, v->zDb, v->zName, &c->pStmt, zSql); sqlite3_free(zSql);
SQLite version 3.3.7 Enter ".help" for instructions sqlite> CREATE TABLE t(a); sqlite> INSERT INTO "t" VALUES('one'); sqlite> INSERT INTO "t" VALUES('two'); sqlite> INSERT INTO "t" VALUES('three'); sqlite> select rowid, * from t; 1|one 2|two 3|three sqlite> delete from t where a = 'one'; sqlite> select rowid, * from t; 2|two 3|three sqlite> vacuum; sqlite> select rowid, * from t; 1|two 2|three SQLite version 3.2.0 Enter ".help" for instructions sqlite> CREATE TABLE t(a); sqlite> INSERT INTO "t" VALUES('one'); sqlite> INSERT INTO "t" VALUES('two'); sqlite> INSERT INTO "t" VALUES('three'); sqlite> select rowid, * from t; 1|one 2|two 3|three sqlite> delete from t where a = 'one'; sqlite> select rowid, * from t; 2|two 3|three sqlite> vacuum; sqlite> select rowid, * from t; 1|two 2|three---- _2007-Jul-18 15:59:24 by anonymous:_ {linebreak} As you may know, INTEGER PRIMARY KEY indexes are the ROWID, so I must supect they would change after a VACUUM. The best workaround is to put docid as INTEGER, then adding a PRIMARY KEY index for the docid column. #f2dcdc 2509 code active 2007 Jul anonymous 2007 Jul 1 1 SQLITE_DATE SELECT CAST(MyDate AS DATE), CAST(MyTime AS TIME) FROM MyData I hope, it will result/return DATE, TIME. Please support to SQLITE_DATE and SQLITE_TIME. Thanks. #f2dcdc 2508 code active 2007 Jul anonymous 2007 Dec 1 1 utf8ToUnicode() does not work on some WinCE devices On some WinCE devices first call to =MultiByteToWideChar()= in =utf8ToUnicode()= always fails. Tried calling =GetLastError()= after it fails and it returns error code 87 -- =ERROR_INVALID_PARAMETER=. To fix this had to change code page from =CP_UTF8= to =CP_ACP= -- no idea why this works. Original =utf8ToUnicode()= ---- static WCHAR *utf8ToUnicode(const char *zFilename) { int nChar; WCHAR *zWideFilename; nChar = MultiByteToWideChar(CP_UTF8, 0, zFilename, -1, NULL, 0); zWideFilename = sqliteMalloc( nChar*sizeof(zWideFilename[0]) ); if( zWideFilename==0 ){ return 0; } nChar = MultiByteToWideChar(CP_UTF8, 0, zFilename, -1, zWideFilename, nChar); if( nChar==0 ){ sqliteFree(zWideFilename); zWideFilename = 0; } return zWideFilename; } ---- Fixed =utf8ToUnicode()= ---- static WCHAR *utf8ToUnicode(const char *zFilename) { int nChar; WCHAR *zWideFilename; nChar = MultiByteToWideChar(CP_ACP, 0, zFilename, -1, NULL, 0); if( nChar == 0 ) { DWORD dwError = GetLastError(); OSTRACE2("MultiByteToWideChar() failed, last error: %d\n", dwError); return 0; } zWideFilename = sqliteMalloc( nChar*sizeof(zWideFilename[0]) ); if( zWideFilename==0 ){ return 0; } nChar = MultiByteToWideChar(CP_ACP, 0, zFilename, -1, zWideFilename, nChar); if( nChar==0 ){ sqliteFree(zWideFilename); zWideFilename = 0; } return zWideFilename; } ---- _2007-Jul-17 23:56:10 by anonymous:_ {linebreak} =unicodeToUtf8()= needs to be fixed the same way. Before: ---- static char *unicodeToUtf8(const WCHAR *zWideFilename){ int nByte; char *zFilename; nByte = WideCharToMultiByte(CP_UTF8, 0, zWideFilename, -1, 0, 0, 0, 0); zFilename = sqliteMalloc( nByte ); if( zFilename==0 ){ return 0; } nByte = WideCharToMultiByte(CP_UTF8, 0, zWideFilename, -1, zFilename, nByte, 0, 0); if( nByte == 0 ){ sqliteFree(zFilename); zFilename = 0; } return zFilename; } ---- After: ---- static char *unicodeToUtf8(const WCHAR *zWideFilename){ int nByte; char *zFilename; nByte = WideCharToMultiByte(CP_ACP, 0, zWideFilename, -1, NULL, 0, NULL, NULL); if ( nByte == 0 ) { DWORD dwError = GetLastError(); OSTRACE2("WideCharToMultiByte() failed, last error = %d\n", dwError); return 0; } zFilename = sqliteMalloc( nByte ); if( zFilename==0 ){ return 0; } nByte = WideCharToMultiByte(CP_ACP, 0, zWideFilename, -1, zFilename, nByte, 0, 0); if( nByte == 0 ){ sqliteFree(zFilename); zFilename = 0; } return zFilename; } ---- Note that while original code with =CP_UTF8= works on Windows and SOME WinCE devices, this modified code works well and Windows and all WinCE devices I've tested so far. ---- _2007-Jul-18 16:01:21 by anonymous:_ {linebreak} Why not using the conversions from SQLite internals ? It can change a UTF-16 to UTF-8 and vice-versa. Or using UTF-16 variants in windows ce should be the best case. ---- _2007-Aug-09 20:47:04 by anonymous:_ Why not using the conversions from SQLite internals ? It can change a UTF-16 to UTF-8 and vice-versa. Or using UTF-16 variants in windows ce should be the best case. Not so simple. =unicodeToUtf8()= is used a lot internally regardless of what whether you use UTF-16 or UTF-8 yourself. For example, =unicodeToUtf8()= is used by =sqlite3WinTempFileName()= which is in turn used by =sqlite3PagerOpentemp()= -- I think you get the idea. ---- _2007-Dec-20 00:29:33 by anonymous:_ {linebreak} We've found that using CP_UTF8 fails on WinCE kernels that don't include SYSGEN_CORELOC (http://msdn2.microsoft.com/en-us/library/ms903883.aspx). To make the code handle any device it should be changed to: static WCHAR *utf8ToUnicode(const char *zFilename) { int nChar; WCHAR *zWideFilename; nChar = MultiByteToWideChar(CP_UTF8, 0, zFilename, -1, NULL, 0); if( nChar == 0 ) { nChar = MultiByteToWideChar(CP_ACP, 0, zFilename, -1, NULL, 0); if( nChar == 0 ) { DWORD dwError = GetLastError(); OSTRACE2("MultiByteToWideChar() failed, last error: %d\n", dwError); return 0; } } zWideFilename = sqliteMalloc( nChar*sizeof(zWideFilename[0]) ); if( zWideFilename==0 ) { return 0; } nChar = MultiByteToWideChar(CP_UTF8, 0, zFilename, -1, zWideFilename, nChar); if( nChar==0 ) { nChar = MultiByteToWideChar(CP_ACP, 0, zFilename, -1, zWideFilename, nChar); if( nChar==0 ) { sqliteFree(zWideFilename); zWideFilename = 0; } } return zWideFilename; } #cfe8bd 2507 code fixed 2007 Jul anonymous 2007 Jul 1 1 test_expr expr-1.104 fails (64-bit int handling) make test fails with: expr-1.104...make: *** [test] Floating point exception System info: gcc-4.1.2, glibc-2.5-r4, 2.6.22.1 x86_64 _2007-Jul-16 18:56:02 by drh:_ {linebreak} Already fixed. See [4130] and [4131]. #c8c8c8 2505 code closed 2007 Jul anonymous 2007 Jul 1 1 does not return error when use sqlite_open to open an non-sqlite db I use sqlite_open api to open an non sqlite database, for example an exe file.The result of sqlite_open is not an error code but sqlite_ok. Sqlite does not actually read the database file until the first SQL query is executed. So the sqlite3_open succeeds but the first call to sqlite3_prepare_v2() or sqlite3_exec() will return an error. #c8c8c8 2504 build closed 2007 Jul anonymous 2007 Jul 1 1 sqlite 3.4.0 fails to build FreeBSD 6.2 64-bit system Attached is the configure and make output. As this is a FreeBSD 6.2 AMD 64-bit box, several "warning: cast from pointer to integer of different size" in func.c, table.c, and vdbemem.c concern me as potential bugs. Added to the fact the the undefined reference to __isnanl. The warnings also appear in 3.3.17 version of SQLite, which I hadn't noticed before, but the there was no undefined reference preventing the build. _2007-Jul-13 17:13:47 by anonymous:_ {linebreak} submitter: anthony howe, achowe@snert.com ---- _2007-Jul-13 17:34:33 by drh:_ {linebreak} Already fixed by check-in [4103]. #cacae5 2499 build defer 2007 Jul anonymous 2007 Aug drh 1 1 undefined reference to `_WinMain@16' when attempting to build I get the error: /usr/lib/gcc/i686-pc-cygwin/3.4.4/../../../libcygwin.a(libcmain.o):(.text+0xab): undefined reference to `_WinMain@16' _2007-Jul-11 18:16:28 by anonymous:_ ls Makefile.in config.sub main.mk sqlite.pc.in Makefile.linux-gcc configure mkdll.sh sqlite3.1 README configure.ac mkopcodec.awk sqlite3.pc.in VERSION contrib mkopcodeh.awk src aclocal.m4 doc mkso.sh tclinstaller.tcl addopcodes.awk ext notes test art install-sh publish.sh tool config.guess ltmain.sh spec.template www ./configure && make install checking build system type... i686-pc-cygwin checking host system type... i686-pc-cygwin checking for gcc... gcc checking for C compiler default output file name... a.exe checking whether the C compiler works... yes checking whether we are cross compiling... no checking for suffix of executables... .exe checking for suffix of object files... o checking whether we are using the GNU C compiler... yes checking whether gcc accepts -g... yes checking for gcc option to accept ISO C89... none needed checking for a sed that does not truncate output... /usr/bin/sed checking for grep that handles long lines and -e... /usr/bin/grep checking for egrep... /usr/bin/grep -E checking for ld used by gcc... /usr/i686-pc-cygwin/bin/ld.exe checking if the linker (/usr/i686-pc-cygwin/bin/ld.exe) is GNU ld... yes checking for /usr/i686-pc-cygwin/bin/ld.exe option to reload object files... -r checking for BSD-compatible nm... /usr/bin/nm -B checking whether ln -s works... yes checking how to recognise dependent libraries... file_magic ^x86 archive import|^x86 DLL checking how to run the C preprocessor... gcc -E checking for ANSI C header files... yes checking for sys/types.h... yes checking for sys/stat.h... yes checking for stdlib.h... yes checking for string.h... yes checking for memory.h... yes checking for strings.h... yes checking for inttypes.h... yes checking for stdint.h... yes checking for unistd.h... yes checking dlfcn.h usability... yes checking dlfcn.h presence... yes checking for dlfcn.h... yes checking for g++... g++ checking whether we are using the GNU C++ compiler... yes checking whether g++ accepts -g... yes checking how to run the C++ preprocessor... g++ -E checking for g77... no checking for xlf... no checking for f77... no checking for frt... no checking for pgf77... no checking for cf77... no checking for fort77... no checking for fl32... no checking for af77... no checking for xlf90... no checking for f90... no checking for pgf90... no checking for pghpf... no checking for epcf90... no checking for gfortran... no checking for g95... no checking for xlf95... no checking for f95... no checking for fort... no checking for ifort... no checking for ifc... no checking for efc... no checking for pgf95... no checking for lf95... no checking for ftn... no checking whether we are using the GNU Fortran 77 compiler... no checking whether accepts -g... no checking the maximum length of command line arguments... 8192 checking command to parse /usr/bin/nm -B output from gcc object... ok checking for objdir... .libs checking for ar... ar checking for ranlib... ranlib checking for strip... strip checking for correct ltmain.sh version... yes checking if gcc supports -fno-rtti -fno-exceptions... no checking for gcc option to produce PIC... checking if gcc static flag -static works... yes checking if gcc supports -c -o file.o... yes checking whether the gcc linker (/usr/i686-pc-cygwin/bin/ld.exe) supports shared libraries... yes checking whether -lc should be explicitly linked in... yes checking dynamic linker characteristics... Win32 ld.exe checking how to hardcode library paths into programs... immediate checking whether stripping libraries is possible... yes checking if libtool supports shared libraries... yes checking whether to build shared libraries... yes checking whether to build static libraries... yes configure: creating libtool appending configuration tag "CXX" to libtool checking for ld used by g++... /usr/i686-pc-cygwin/bin/ld.exe checking if the linker (/usr/i686-pc-cygwin/bin/ld.exe) is GNU ld... yes checking whether the g++ linker (/usr/i686-pc-cygwin/bin/ld.exe) supports shared libraries... yes checking for g++ option to produce PIC... checking if g++ static flag -static works... yes checking if g++ supports -c -o file.o... yes checking whether the g++ linker (/usr/i686-pc-cygwin/bin/ld.exe) supports shared libraries... yes checking dynamic linker characteristics... Win32 ld.exe checking how to hardcode library paths into programs... immediate appending configuration tag "F77" to libtool checking for a BSD-compatible install... /usr/bin/install -c checking for gawk... gawk Version set to 3.4 Release set to 3.4.0 Version number set to 3004000 checking whether to support threadsafe operation... no checking whether to allow connections to be shared across threads... no checking whether threads can override each others locks... no checking whether to support shared library linked as release mode or not... no checking whether to use an in-ram database for temporary tables... no checking if executables have the .exe suffix... unknown checking host system type... (cached) i686-pc-cygwin checking for Tcl configuration... found /usr/lib/tclConfig.sh checking for existence of /usr/lib/tclConfig.sh... loading checking for library containing tgetent... -ltermcap checking for readline in -lreadline... no checking readline.h usability... no checking readline.h presence... no checking for readline.h... no checking for /usr/include/readline.h... no checking for /usr/include/readline/readline.h... no checking for /usr/local/include/readline.h... no checking for /usr/local/include/readline/readline.h... no checking for /usr/local/readline/include/readline.h... no checking for /usr/local/readline/include/readline/readline.h... no checking for /usr/contrib/include/readline.h... no checking for /usr/contrib/include/readline/readline.h... no checking for /mingw/include/readline.h... no checking for /mingw/include/readline/readline.h... no checking for library containing fdatasync... none required checking for usleep... yes checking for fdatasync... yes configure: creating ./config.status config.status: creating Makefile config.status: creating sqlite3.pc gcc -g -O2 -o lemon.exe ./tool/lemon.c cp ./tool/lempar.c . cp ./src/parse.y . ./lemon.exe parse.y mv parse.h parse.h.temp awk -f ./addopcodes.awk parse.h.temp >parse.h cat parse.h ./src/vdbe.c | gawk -f ./mkopcodeh.awk >opcodes.h sort -n -b -k 3 opcodes.h | gawk -f ./mkopcodec.awk >opcodes.c gcc -g -O2 -o mkkeywordhash.exe ./tool/mkkeywordhash.c ./mkkeywordhash.exe >keywordhash.h sed -e s/--VERS--/3.4.0/ ./src/sqlite.h.in | \ sed -e s/--VERSION-NUMBER--/3004000/ >sqlite3.h rm -rf tsrc mkdir -p tsrc cp ./src/alter.c ./src/analyze.c ./src/attach.c ./src/auth.c ./src/btree.c ./src/btree.h ./src/build.c ./src/callback.c ./src/complete.c ./src/date.c ./src/delete.c ./src/expr.c ./src/func.c ./src/hash.c ./src/hash.h ./src/insert.c ./src/legacy.c ./src/loadext.c ./src/main.c ./src/malloc.c ./src/os.c ./src/os_unix.c ./src/os_win.c ./src/os_os2.c ./src/pager.c ./src/pager.h ./src/parse.y ./src/pragma.c ./src/prepare.c ./src/printf.c ./src/random.c ./src/select.c ./src/shell.c ./src/sqlite.h.in ./src/sqliteInt.h ./src/table.c ./src/tclsqlite.c ./src/tokenize.c ./src/trigger.c ./src/utf.c ./src/update.c ./src/util.c ./src/vacuum.c ./src/vdbe.c ./src/vdbe.h ./src/vdbeapi.c ./src/vdbeaux.c ./src/vdbeblob.c ./src/vdbefifo.c ./src/vdbemem.c ./src/vdbeInt.h ./src/vtab.c ./src/where.c ./ext/fts1/fts1.c ./ext/fts1/fts1.h ./ext/fts1/fts1_hash.c ./ext/fts1/fts1_hash.h ./ext/fts1/fts1_porter.c ./ext/fts1/fts1_tokenizer.h ./ext/fts1/fts1_tokenizer1.c sqlite3.h ./src/btree.h ./src/btreeInt.h ./src/hash.h ./src/limits.h opcodes.h ./src/os.h ./src/os_common.h ./src/sqlite3ext.h ./src/sqliteInt.h ./src/vdbe.h parse.h ./ext/fts1/fts1.h ./ext/fts1/fts1_hash.h ./ext/fts1/fts1_tokenizer.h ./src/vdbeInt.h tsrc cp: warning: source file `./src/btree.h' specified more than once cp: warning: source file `./src/hash.h' specified more than once cp: warning: source file `./src/sqliteInt.h' specified more than once cp: warning: source file `./src/vdbe.h' specified more than once cp: warning: source file `./ext/fts1/fts1.h' specified more than once cp: warning: source file `./ext/fts1/fts1_hash.h' specified more than once cp: warning: source file `./ext/fts1/fts1_tokenizer.h' specified more than once cp: warning: source file `./src/vdbeInt.h' specified more than once rm tsrc/sqlite.h.in tsrc/parse.y cp parse.c opcodes.c keywordhash.h tsrc tclsh ./tool/mksqlite3c.tcl cc sqlite3.c -o sqlite3 /usr/lib/gcc/i686-pc-cygwin/3.4.4/../../../libcygwin.a(libcmain.o):(.text+0xab): undefined reference to `_WinMain@16' collect2: ld returned 1 exit status make: *** [sqlite3] Error 1 ---- _2007-Jul-11 18:17:17 by anonymous:_ {linebreak} when initially filing this bug, i was told to not paste large screen dumps. i was also promised that i would be able to add an attachment later. however, that option was not given, so I have attached a screen transcript. ---- _2007-Jul-11 18:18:33 by anonymous:_ {linebreak} my email is metaperl at gmail.com ---- _2007-Jul-11 18:38:53 by drh:_ {linebreak} The [Attach] link in the upper-left is what you use to add attachments. The sqlite3.c source file is a library, not a program. If you want to compile a command-line shell, add the "shell.c" source file: cc -o sqlite3 sqlite3.c shell.c ---- _2007-Jul-23 20:35:06 by anonymous:_ {linebreak} I believe this bug report should be reopened because this error comes when you try to configure and compile USING THE DEFAULTS. When you try to do a standard configure and make, it should either try to compile shell.c as is needed or not compile sqlite.c. It shouldn't put in a command that fails the compile by default. Sorry to bother you about this, but I don't see how this isn't a bug or at least a glitch. It's a problem in the configuration and makefile system. ---- _2007-Aug-15 03:26:05 by anonymous:_ {linebreak} Compile and link passed by following patch (at least for me). diff -ru sqlite-3.4.2/Makefile.in sqlite-3.4.2-new/Makefile.in --- sqlite-3.4.2/Makefile.in 2007-08-15 10:37:18.921875000 +0900 +++ sqlite-3.4.2-new/Makefile.in 2007-08-15 10:36:08.750000000 +0900 @@ -681,7 +681,7 @@ mkdir -p doc mv $(DOC) doc -install: sqlite3 libsqlite3.la sqlite3.h ${HAVE_TCL:1=tcl_install} +install: sqlite3$(TEXE) libsqlite3.la sqlite3.h ${HAVE_TCL:1=tcl_install} $(INSTALL) -d $(DESTDIR)$(libdir) $(LTINSTALL) libsqlite3.la $(DESTDIR)$(libdir) $(INSTALL) -d $(DESTDIR)$(exec_prefix)/bin ---- _2007-Aug-22 12:33:17 by anonymous:_ {linebreak} Why is this bug closes and marked "not a bug"? sqlite fails on make install on Cygwin (as in untar/configure/make/make install), which is *clearly* a bug. The "fix" posted above does not work, make install fails instantly with tclsh ../sqlite-3.4.2-n/tclinstaller.tcl 3.4 can't read "env(DESTDIR)": no such variable while executing "set LIBDIR $env(DESTDIR)$env(TCLLIBDIR)" (file "../sqlite-3.4.2-n/tclinstaller.tcl" line 11) make: *** [tcl_install] Error 1 ---- _2007-Aug-22 12:53:45 by drh:_ {linebreak} I misread the problem. Cygwin is not a supported platform. So in that sense, this is *not* clearly a bug in SQLite. This is an imcompatibility with Cygwin. We will take the problem under advisement, but since Cygwin is not officially support and because we have many much more serious problems to work on, progress on this is likely to be slow. You can expedite a fix by posting a patch. #c8c8c8 2494 code closed 2007 Jul anonymous 2007 Jul 1 1 DRow["ID"] = reader.GetString(reader.GetOrdinal("ID")); fails DRow["ID"] = reader.GetString(reader.GetOrdinal("ID")); This fails under Vista Ultimate, succeeds under XP Pro. It is Sqlite 3.1, will upgrading help? Thanks! Jim Please post your question to the author of the sqlite wrapper for whatever language you're using. #f2dcdc 2491 code active 2007 Jul anonymous 2007 Jul 1 1 Mingw Warnings w/ 3.4.0 Amalgamation When compiling the 3.4.0 amalgamation sqlite3.c file w/ no defines, you get the following warnings: sqlite3/sqlite3.c: In function `sqlite3BtreeFindCell':{linebreak} sqlite3/sqlite3.c:23249: warning: unused variable `data'{linebreak} sqlite3/sqlite3.c: In function `vxprintf':{linebreak} sqlite3/sqlite3.c:8488: warning: 'xtype' might be used uninitialized in this function{linebreak} sqlite3/sqlite3.c: In function `sqlite3BtreeOpen':{linebreak} sqlite3/sqlite3.c:19488: warning: 'nameLen' might be used uninitialized in this function{linebreak} sqlite3/sqlite3.c: In function `getOverflowPage':{linebreak} sqlite3/sqlite3.c:25386: warning: 'rc' might be used uninitialized in this function{linebreak} sqlite3/sqlite3.c: In function `sqlite3Select':{linebreak} sqlite3/sqlite3.c:56300: warning: 'pEList' might be used uninitialized in this function{linebreak} sqlite3/sqlite3.c:56301: warning: 'pTabList' might be used uninitialized in this function{linebreak} sqlite3/sqlite3.c: At top level:{linebreak} sqlite3/sqlite3.c:16020: warning: 'sqlite3GenericAllocationSize' defined but not used{linebreak} sqlite3/sqlite3.c:6188: warning: 'sqlite3Utf16Substr' declared `static' but never defined{linebreak} sqlite3/sqlite3.c:6307: warning: 'sqlite3Get2byte' declared `static' but never defined{linebreak} sqlite3/sqlite3.c:6309: warning: 'sqlite3Put2byte' declared `static' but never defined{linebreak} sqlite3/sqlite3.c:23248: warning: 'sqlite3BtreeFindCell' defined but not used{linebreak} sqlite3/sqlite3.c:63547: warning: 'sqlite3ParserAlloc' defined but not used{linebreak} sqlite3/sqlite3.c:63673: warning: 'sqlite3ParserFree' defined but not used{linebreak} sqlite3/sqlite3.c:65286: warning: 'sqlite3Parser' defined but not used{linebreak} I know the uninitialized warnings are false warnings but the defined functions that aren't used seem to be an error in building the amalgamation. #c8c8c8 2490 code closed 2007 Jul anonymous 2007 Oct 1 1 Latest cvs 3.4.0 seg fault This problem is the same with 3.3.1.13, Fedora's 3.4.0 and the debug version I just built. Using latest Fedora and perl modules: net1#r mx mxaddrs ... perl : 5.8.8 DBI : 1.58 DBD::SQLite : 1.13 SQLite::DBMS: 3.4.0.1 Code is simple db create and inserts. Problem only occurs when a DBI sth->prepare() is used with multiple execute inserts of duplicate data. Instead of returning an error seg fault occurs. If required I can try and put together a small code sample. _2007-Jul-07 06:25:54 by anonymous:_ Why do you think its pure SQLite problem ? ---- _2007-Jul-07 11:46:55 by drh:_ {linebreak} To amplify the remark by anonymous above: what makes you think this is a problem with SQLite and not a problem with perl or the DBD::SQLite module? We have run literally millions of test cases through the core SQLite without hitting any segfaults, yet you say that a simple test case is sufficient to cause the problem. That would seem to implicate DBD::SQLite rather than the SQLite core, would it not? Please tell us why you think this is an SQLite problem. And please also provide us with a reproducible test case, keeping in mind that the core SQLite developers do not normally have DBD::SQLite installed. ---- _2007-Aug-10 15:18:32 by anonymous:_ {linebreak} This is a DBD::SQLite bug. See http://rt.cpan.org/Public/Bug/Display.html?id=28757 for a test case and a patch. Mike #c8c8c8 2489 build closed 2007 Jul anonymous 2007 Jul 1 1 Latest cvs 3.4.0 make test fails related to TCL Here's how I'm building. I've tried --disable-tcl and --enable-tcl with no joy. I'm using latest Fedora 7 with tcl-8.4.13 installed. mkdir -p /build/work/sqlite-3.4.0.1 cd /build/work/sqlite-3.4.0.1 unset CDPATH export CFLAGS='-pipe -O3 -g' make distclean cvs -d :pserver:anonymous@www.sqlite.org:/sqlite -r update . ./configure --prefix=/usr/local/pkgs/sqlite-3.4.0.1 --disable-tcl make groupadd vuser || /bin/true useradd -M -g vuser -d /vhost/davidfavor.com/users/david -s /bin/zsh david || /bin/true useradd -M -g vuser -d /vhost/livefeast.com/users/yemiah -s /bin/zsh yemiah || /bin/true chown david:vuser -R . su -c "make test" david /build/work/sqlite-3.4.0.1/./src/tclsqlite.c:2416: undefined reference to `Tcl_CreateObjCommand' /build/work/sqlite-3.4.0.1/./src/tclsqlite.c:2417: undefined reference to `Tcl_PkgProvide' /build/work/sqlite-3.4.0.1/./src/tclsqlite.c:2418: undefined reference to `Tcl_CreateObjCommand' ... hundreds of errors deleted ... "make test" requires Tcl to function. Post the result of "grep -i tcl config.log" after you run ./configure with no arguments. The key to it working is finding tclConfig.sh
configure:19220: checking for Tcl configuration configure:19302: result: found /usr/lib/tclConfig.sh configure:19305: checking for existence of /usr/lib/tclConfig.sh ac_cv_c_tclconfig=/usr/lib HAVE_TCL='1' TCL_BIN_DIR='/usr/lib' TCL_INCLUDE_SPEC='-I/usr/include/tcl8.4.13' TCL_LIBS='-ldl -lpthread -lieee -lm' TCL_LIB_FILE='libtcl8.4.so' TCL_LIB_FLAG='-ltcl8.4' TCL_LIB_SPEC='-L/usr/lib -ltcl8.4' TCL_SRC_DIR='/usr/include/tcl8.4.13' TCL_STUB_LIB_FILE='libtclstub8.4.a' TCL_STUB_LIB_FLAG='-ltclstub8.4' TCL_STUB_LIB_SPEC='-L/usr/lib -ltclstub8.4' TCL_VERSION='8.4'---- _2007-Jul-07 03:49:21 by anonymous:_ {linebreak} Ah I see now. I'm using a 64 bit machine so tcl lives in the 64 bit directory hierarchy. Here's how I got most of the tests to run: export CFLAGS='-pipe -O3 -g -DSQLITE_DISABLE_DIRSYNC=1 -Wall' make distclean cvs -d :pserver:anonymous@www.sqlite.org:/sqlite -r update . ./configure --prefix=/usr/local/pkgs/sqlite-3.4.0.1 --enable-tcl --with-tcl=/usr/lib64 This ticket can close. I'll do some further testing and open another ticket about the single thread test that fails. ---- _2007-Jul-07 11:43:05 by drh:_ {linebreak} Please note that this is an open ticketing system. Anybody can close a ticket. You do not need to ask me or Dan to do it. #f2dcdc 2487 code active 2007 Jul anonymous 2007 Jul 1 1 SQLite database locked error on NFS mounted home dir I have a c program using the provided API. My home directory is NFS mounted, Im using SQLite 3.3.17. I open a new database using "sqlite3_open", then strcpy () a SQL command to create a table, and run "sqlite3_exec" with this string. I get a return code of 5=database locked. I then tried to manually (command line using sqlite3) create a table within a database in my home dir, that fails too. =========== x@y> sqlite3 db2 SQLite version 3.3.17 Enter ".help" for instructions sqlite> create table test (Lastname varchar); SQL error: database is locked sqlite> ============== If I try this on my local machine (a Mac), it works fine, but I need it to work in my home directory mounted via NFS as that is where the output of our program goes _2007-Jul-06 19:04:15 by anonymous:_ {linebreak} If you're using a Mac, compile sqlite with SQLITE_ENABLE_LOCKING_STYLE in os_unix.c ---- _2007-Jul-07 11:51:10 by drh:_ {linebreak} This is a problem with your NFS implementation - it does not appear to support posix advisory locking. There is nothing much that SQLite can do about this. Anonymous above suggests making use of the dot-locking mechanism contributed by Apple. This might be an effective work-around. But remember that there is performance impact. Also remember that an SQLite database that uses dot-locking is subtly imcompatible with a standard SQLite database. The file format itself is the same, but if two processes try to access the database file at the same time and one uses dot-locks and the other uses posix advisory locks, you will end up with corruption. ---- _2007-Jul-07 12:44:09 by anonymous:_ {linebreak} It's very odd that Apple does not fix their Mac OSX POSIX locks for NFS given their resources. #c8c8c8 2485 code closed 2007 Jul anonymous 2007 Jul 1 2 SQLite + Python segfaults at various places in the code I've been getting segfaults while running unit tests on my code base (http://drproject.org). It is written in Python and uses SQLAlchemy for database abstraction. I believe the Python module is PySQLite2. After taking some core dumps, they all seem to point to SQLite code (but different parts each time). Another guy I'm working with is having similar problems with some code he is working on. I have run memtest over night (turning up nothing), so I don't _think_ it's my machine. I am not sure what would be most helpful for you, so before I start throwing around 20 meg core dumps, I thought I'd check and see what you would prefer. _2007-Jul-04 17:05:32 by anonymous:_ Try the mailing list as your problem is not pure SQLite problem. ---- _2007-Jul-05 06:25:40 by danielk1977:_ {linebreak} If it's different places in the code each time, this really sounds like a corrupted heap. It may be that it is SQLite corrupting the heap, or it may be the wrapper code. Posting to the mailing list is probably the best way to progress this. ---- _2007-Jul-05 13:02:35 by anonymous:_ {linebreak} Setting up and learning the inner workings of another computer language and third party wrappers requires a lot of effort. You greatly increase your odds of finding the source of your bug if you make a small standalone C or Tcl program that demonstrates the problem using only the SQLite API. This eliminates the possibility that the host language or wrapper is corrupting memory or doing something else wrong. Often in the process of creating such a standalone test case, people find their own mistake. #c8c8c8 2483 code closed 2007 Jul anonymous 2007 Jul 1 3 like clause not working while using the Trac SCM tool I've experienced some issues with the database usage of the like clause in certain select statements. with values for name such as 'TracIni', 'TracBrowser', 'TracFqastCGI' the following quert does not catch any of the above names: SELECT name FROM wiki WHERE name like 'Trac'; seems to be that this should catch it. _2007-Jul-04 03:56:32 by anonymous:_ {linebreak} To catch the terms you are looking for, wouldn't you need this query: SELECT name FROM wiki WHERE name like 'Trac%' #c8c8c8 2481 code closed 2007 Jul anonymous 2007 Jul 1 3 sqlite leaks memory (after 3.3.13) After upgrade from 3.3.12 to 3.4.0 I noticed an huge negative impact on memory usage of my SQLite-based programs. I suspected a bug in my code. After some debugging, I created a simple program and narrowed this down to the following: 1: this started from 3.3.14 and is still relevant for 3.4.0 2: 3.3.13 was the last version which "worked" as expected Attached is a sample program which demonstrates the problem. sqlite3 command utility exhibits the same increase and the same memory usage patterns. Basically, what happens is that memory allocated by SQLite is first released after call to sqlite3_close(). It seems like the memory usage will eventually "stabilize" and only increase again in smaller chunks. This is on OpenBSD 4.1/i386.
ssehic@dev-5-i386:/profense/devel$ ls -l /profense/log/logdata.db -rwxrwxr-x 1 _admd profense 12106752 Jul 1 23:15 /profense/log/logdata.db ssehic@dev-5-i386:/profense/devel$ sqlite3 /profense/log/logdata.db "select count(*) from log" 156000 ssehic@dev-5-i386:/profense/devel/sqlmem SQLite version 3.3.13 Usage (bytes) initial, rssize=487424, dsize=241664 Usage (bytes) after SELECT COUNT(*), rssize=1417216, dsize=704512 Usage (bytes) after sqlite3_close, rssize=1318912, dsize=618496 ssehic@dev-5-i386:/profense/devel/sqlmem SQLite version 3.4.0 Usage (bytes) initial, rssize=495616, dsize=241664 Usage (bytes) after SELECT COUNT(*), rssize=22118400, dsize=21331968 Usage (bytes) after sqlite3_close, rssize=1351680, dsize=618496I'm sure something like this should not go unnoticed by the developers. However, I can't believe this is specific to OpenBSD. Something changed after 3.3.13 which broke this in a subtle way. Any hints? _2007-Jul-01 23:03:42 by drh:_ {linebreak} Your test program appears to be OpenBSD-specific. I cannot compile it on Linux. So I cannot replicate your problem. We do test SQLite using both its own internal memory leak detection logic and valgrind, and both report no memory leaks over the entire test suite. ---- _2007-Jul-02 07:31:57 by anonymous:_ {linebreak} Yes. The test program is OpenBSD specific, but only the memory reporting stuff (print_memory_usage()). I'll see if I can provide a Linux variant and complete details on how to reproduce the problem. More to come. ---- _2007-Jul-02 09:28:42 by anonymous:_ {linebreak} I think I figured this one out. The problem is only reproducible if the database is created using sqlite3 prior to 3.3.14 and queries are later run on the same database using 3.4.0. This is weird, since the database format should be backwards compatible between releases. However, lots of "performance improvements" went in after 3.3.13. This could have something to do with it. At http://insecure.dk/bin/logdata.db.gz is a test database created with 3.3.12. If you run a "SELECT COUNT(*) FROM log" on that using 3.4.0, you should see the memory usage increase greatly using sqlite3 utility. ---- _2007-Jul-02 10:38:12 by anonymous:_ {linebreak} This ticket can be closed. Increased memory usage is due to my test database (created with 3.3.12) used PRAGMA default_cache_size = 10000; Apparently versions > 3.3.14 allocate all the memory on the first database query. That's why I saw the jump in memory usage. Sorry for the noise. ---- _2007-Jul-02 10:38:41 by drh:_ {linebreak} I downloaded logdata.db from the link above and ran "SELECT count(*) FROM log" against it using the latest version of SQLite from CVS (which i closer to 3.4.1 than to 3.4.0, I suspect). I did this using valgrind: valgrind --leak-check=full --show-reachable=yes \ ./sqlite3 logdata.db 'select count(*) FROM log' Valgrind tells me that 156 bytes of memory were leaked in 11 allocations. All of the leaked memory was allocated by the getpwuid_r() function in glibc - not from SQLite. The total memory allocated was 13,515,680 bytes, which considering that the logdata.db database specifies a cache size of 10,000 pages and you are reading (and thus attempting to cache) the entire 50MB database, this is about what one would expect. I am unable to reproduce the problem. ---- _2007-Jul-02 10:50:40 by anonymous:_ {linebreak} That was my conclusion as well. Thanks again for the great work and unbelievably speedy responses to "bug" reports. #f2dcdc 2479 code active 2007 Jun anonymous 2007 Jun 1 1 WinCE regression on some systems. Any db open fails. Because Windows CE is a modular system, meaning many parts of it can be optionally ommited by the system builder, some don't include the CP_UTF8 conversion algorithms for MultiByteToWideChar and family. I believe Windows 95 and early 98 systems can also lack this encoding if not updated with a later Internet Explorer version. Solution is to just use the sqlite internal functions that already know how to do the same thing. Attached is an untested patch to os_win.c (I don't have a windows machine nor a cross-compiler set up) to show where the problem is and a possible (sub-optimal) solution. I believe the right thing to do would be to just drop the utf8ToUnicode and unicodeToUtf8 functions, add the sqlite3Utf8to16 equivalent to utf.c and use them instead. ~Nuno Lucas _2007-Jun-29 14:54:11 by anonymous:_ {linebreak} The title is wrong. It should say "Any db open using the UTF-8 API", as using the open16 API will work. #cfe8bd 2475 code fixed 2007 Jun anonymous 2007 Jul 1 3 Handles being left open on unix On unix, I'm seeing processes that have been clone/exec'd inheriting file handles to sqlite databases that were opened in the parent process (and hence subsequently don't/can't get closed in the child process). Which ultimately means that you can't unmount a filesystem. The solution is the FD_CLOEXEC fcntl on the open filehandle, but I'd like to get confirmation from elsewhere/highlight it to DRH/the list to make them aware of it. Tested and fixed by the below patch (against 3.3.6). ==== //depot/qt/4.2/src/3rdparty/sqlite/os_unix.c#2 - /home/bking/depot/qt/4.2/src/3rdparty/sqlite/os_unix.c ==== @@ -701,7 +701,7 @@ OsFile **pId, int *pReadonly ){ - int rc; + int rc, oldflags; unixFile f; CRASH_TEST_OVERRIDE(sqlite3CrashOpenReadWrite, zFilename, pId, pReadonly); @@ -730,6 +730,12 @@ return SQLITE_NOMEM; } TRACE3("OPEN %-3d %s\n", f.h, zFilename); + oldflags = fcntl (f.h, F_GETFD, 0); + if (oldflags >= 0) + { + oldflags |= FD_CLOEXEC; + fcntl (f.h, F_SETFD, oldflags); + } return allocateUnixFile(&f, pId); } @@ -749,7 +755,7 @@ ** On failure, return SQLITE_CANTOPEN. */ int sqlite3UnixOpenExclusive(const char *zFilename, OsFile **pId, int delFlag){ - int rc; + int rc, oldflags; unixFile f; CRASH_TEST_OVERRIDE(sqlite3CrashOpenExclusive, zFilename, pId, delFlag); @@ -772,6 +778,12 @@ unlink(zFilename); } TRACE3("OPEN-EX %-3d %s\n", f.h, zFilename); + oldflags = fcntl (f.h, F_GETFD, 0); + if (oldflags >= 0) + { + oldflags |= FD_CLOEXEC; + fcntl (f.h, F_SETFD, oldflags); + } return allocateUnixFile(&f, pId); } @@ -783,7 +795,7 @@ ** On failure, return SQLITE_CANTOPEN. */ int sqlite3UnixOpenReadOnly(const char *zFilename, OsFile **pId){ - int rc; + int rc, oldflags; unixFile f; CRASH_TEST_OVERRIDE(sqlite3CrashOpenReadOnly, zFilename, pId, 0); @@ -800,6 +812,12 @@ return SQLITE_NOMEM; } TRACE3("OPEN-RO %-3d %s\n", f.h, zFilename); + oldflags = fcntl (f.h, F_GETFD, 0); + if (oldflags >= 0) + { + oldflags |= FD_CLOEXEC; + fcntl (f.h, F_SETFD, oldflags); + } return allocateUnixFile(&f, pId); } @@ -823,6 +841,7 @@ OsFile *id, const char *zDirname ){ + int oldflags; unixFile *pFile = (unixFile*)id; if( pFile==0 ){ /* Do not open the directory if the corresponding file is not already @@ -836,6 +855,12 @@ return SQLITE_CANTOPEN; } TRACE3("OPENDIR %-3d %s\n", pFile->dirfd, zDirname); + oldflags = fcntl (pFile->dirfd, F_GETFD, 0); + if (oldflags >= 0) + { + oldflags |= FD_CLOEXEC; + fcntl (pFile->dirfd, F_SETFD, oldflags); + } return SQLITE_OK; } _2007-Jun-28 23:07:33 by anonymous:_ {linebreak} You'd need #ifdef FD_CLOEXEC around the code at the very least, since some UNIX-like OSes lack this feature. ---- _2007-Jun-29 00:12:29 by anonymous:_ {linebreak} it's not right to fork() and keep using sqlite3 objects handle if I don't misread the documentation. ---- _2007-Jul-02 00:42:22 by anonymous:_ {linebreak} *it's not right to fork() and keep using sqlite3 objects handle if I don't misread the documentation.* That's true, but sqlite without this change is still leaking filehandles (and keeping files open) on a standard practice fork and exec. I discovered it because applications that weren't even using sqlite were holding files open because the parent process had open handles at the point they were created. ---- _2007-Jul-02 21:04:18 by anonymous:_ {linebreak} Which UNIX-like OSes lack this feature? FD_CLOEXEC part of the Single UNIX Specification, Version 2, and POSIX: http://www.opengroup.org/onlinepubs/007908775/xsh/fcntl.h.html #c8c8c8 2472 code closed 2007 Jun anonymous 2007 Jun 1 1 smart quotes turning question marks while storing in oracle 9i DB whenever end-user copy & paste the statement from the word which contains smart quotes into the java application and submit, it stores in the oracle 9i database. But the smart quotes are getting converted into question mark (?) / reverse question mark symbol. Kindly tell me the solution. Environment: Java 2.0,Jrun 4.0, Oracle 9.2.0.1.0 Where does SQLite figure into the system? #c8c8c8 2471 secure closed 2007 Jun anonymous 2007 Jun 1 1 a bug for 64 bit driver under operation system Firstly,I can't find a 64bit driver just like a.so file under the specail system Unix/Linxu. Secondly,I find a drvier writen by java,but it has many problem while multi threads operate the database. Sometimes I lost all of my data. Can you offer a 64bit driver under Unix/Linux in your website? This is probably better discussed on the mailing list. Any bugs in a java wrapper (which one) can be figured out there too. #cfe8bd 2470 code fixed 2007 Jun anonymous 2007 Jun 1 3 subselects causing reproducable SIGSEGV After upgrade to 3.4.0, I started receiving SIGSEGV on a particular sub-select query. This did *not* happen on 3.3.12. I have not tested versions 3.3.13 - 3.3.17. I've trimmed the original statement down as much as I could. The following statement shows the problem:
DROP TABLE table_1; DROP TABLE table_2; CREATE TABLE table_1 (col_10); CREATE TABLE table_2 ( col_1, col_2, col_3, col_4, col_5, col_6, col_7, col_8, col_9, col_10 ); SELECT col_10 FROM (SELECT table_1.col_10 AS col_10 FROM table_1), (SELECT table_1.col_10, table_2.col_9 AS qcol_9 FROM table_1, table_2 GROUP BY table_1.col_10, qcol_9 );What I've noticed is that it takes at least 10 columns in table_2 before the bug is triggered. Remove one (unused) column and it goes away. Also, removing the second parameter in GROUP BY (eg. qcol_9) makes the bug go away. This is on OpenBSD 4.1 (i386). I'm not familiar with SQLite's internals, but below is a backtrace. Another thing I noticed is that bug is much harder to trigger (like 1 out of 10 times) without any malloc debugging options turned on. See http://www.openbsd.org/cgi-bin/man.cgi?query=malloc.conf&apropos=0&sektion=0&manpath=OpenBSD+Current&arch=i386&format=html With MALLOC_OPTIONS "AFGJP", the bug is triggered each time.
ssehic@dev-5-i386:/home/ssehic/devel$ sqlite3 test.db SQLite version 3.4.0 Enter ".help" for instructions sqlite> .read sigsegv.sql Segmentation fault (core dumped) ssehic@dev-5-i386:/home/ssehic/devel$ gdb /usr/local/bin/sqlite3 sqlite3.core GNU gdb 6.3 Copyright 2004 Free Software Foundation, Inc. GDB is free software, covered by the GNU General Public License, and you are welcome to change it and/or distribute copies of it under certain conditions. Type "show copying" to see the conditions. There is absolutely no warranty for GDB. Type "show warranty" for details. This GDB was configured as "i386-unknown-openbsd4.1"... Core was generated by `sqlite3'. Program terminated with signal 11, Segmentation fault. Reading symbols from /usr/local/lib/libsqlite3.so.9.0...done. Loaded symbols for /usr/local/lib/libsqlite3.so.9.0 Reading symbols from /usr/lib/libreadline.so.3.0...done. Loaded symbols for /usr/lib/libreadline.so.3.0 Reading symbols from /usr/lib/libncurses.so.10.0...done. Loaded symbols for /usr/lib/libncurses.so.10.0 Reading symbols from /usr/lib/libc.so.40.3...done. Loaded symbols for /usr/lib/libc.so.40.3 Reading symbols from /usr/libexec/ld.so...done. Loaded symbols for /usr/libexec/ld.so #0 sqlite3ValueFromExpr (pExpr=0xd0d0d0d0, enc=1 '\001', affinity=208 'Ð', ppVal=0xcfbf1618) at src/vdbemem.c:913 913 op = pExpr->op; (gdb) bt #0 sqlite3ValueFromExpr (pExpr=0xd0d0d0d0, enc=1 '\001', affinity=208 'Ð', ppVal=0xcfbf1618) at src/vdbemem.c:913 #1 0x07f47ef0 in sqlite3ColumnDefault (v=0x7e949000, pTab=0x854d2c80, i=8) at src/update.c:62 #2 0x07f2de40 in sqlite3ExprCodeGetColumn (v=0x7e949000, pTab=0x854d2c80, iColumn=8, iTable=2132004048) at src/expr.c:1683 #3 0x07f2df17 in sqlite3ExprCode (pParse=0xcfbf1a90, pExpr=0x7c2e7880) at src/expr.c:1733 #4 0x07f2e782 in sqlite3ExprCodeExprList (pParse=0xcfbf1a90, pList=0x7f13ccd0) at src/expr.c:2121 #5 0x07f45980 in sqlite3Select (pParse=0xcfbf1a90, p=0x7c2e7d00, eDest=8, iParm=2, pParent=0x7c2e7980, parentTab=1, pParentAgg=0xcfbf18cc, aff=0x0) at src/select.c:3218 #6 0x07f45cbd in sqlite3Select (pParse=0xcfbf1a90, p=0x7c2e7980, eDest=4, iParm=0, pParent=0x0, parentTab=0, pParentAgg=0x0, aff=0x0) at src/select.c:2961 #7 0x07f3b2ef in yy_reduce (yypParser=0x7fc1d800, yyruleno=104) at parse.y:369 #8 0x07f3ce04 in sqlite3Parser (yyp=0x7fc1d800, yymajor=1, yyminor={z = 0x1 "", dyn = 0, n = 1066002024}, pParse=0x1) at parse.c:3417 #9 0x07f469a5 in sqlite3RunParser (pParse=0xcfbf1a90, zSql=0x7f13c600 "SELECT\r\n col_10\r\nFROM\r\n (SELECT table_1.col_10 AS col_10 FROM table_1),\r\n (\r\n SELECT table_1.col_10, table_2.col_9 AS qcol_9\r\n FROM table_1, table_2\r\n GROUP BY table_1.co"..., pzErrMsg=0xcfbf1a8c) at src/tokenize.c:452 #10 0x07f3f5de in sqlite3Prepare (db=0x8693ce00, zSql=0x7f13c600 "SELECT\r\n col_10\r\nFROM\r\n (SELECT table_1.col_10 AS col_10 FROM table_1),\r\n (\r\n SELECT table_1.col_10, table_2.col_9 AS qcol_9\r\n FROM table_1, table_2\r\n GROUP BY table_1.co"..., nBytes=-1, saveSqlFlag=0, ppStmt=0xcfbf1bf4, pzTail=0xcfbf1bf8) at src/prepare.c:504 #11 0x07f3f6c9 in sqlite3_prepare (db=0x8693ce00, zSql=0x7f13c600 "SELECT\r\n col_10\r\nFROM\r\n (SELECT table_1.col_10 AS col_10 FROM table_1),\r\n (\r\n SELECT table_1.col_10, table_2.col_9 AS qcol_9\r\n FROM table_1, table_2\r\n GROUP BY table_1.co"..., nBytes=-1, ppStmt=0xcfbf1bf4, pzTail=0xcfbf1bf8) at src/prepare.c:616 #12 0x07f59ced in sqlite3_exec (db=0x8693ce00, zSql=0x7f13c600 "SELECT\r\n col_10\r\nFROM\r\n (SELECT table_1.col_10 AS col_10 FROM table_1),\r\n (\r\n SELECT table_1.col_10, table_2.col_9 AS qcol_9\r\n FROM table_1, table_2\r\n GROUP BY table_1.co"..., xCallback=0x1c001a24Looks like a NULL pointer deref. Hope that is enough information. Please let me know if you need anything else. Thanks. _2007-Jun-27 21:46:10 by drh:_ {linebreak} Though you would never know it to read the description, this turns out to be the same problem as #2445. It has already been fixed by [4122]. ---- _2007-Jun-27 22:10:22 by anonymous:_ {linebreak} Even though the bug was fixed, can you add this test case for this anyway, as Dan's test case in Check-in [4122] for this bug failed to crash or give any valgrind error for me with the sqlite 3.4.0 sources. -- no SEGV, nor any valgrind error for 3.4.0 release CREATE TEMP TABLE t1 (a, b, c, d, e); CREATE TEMP TABLE t2 (f); SELECT t1.e AS alias FROM t2, t1 WHERE alias = 1 ; -- SEGV on 3.4.0 release CREATE TABLE table_1 (col_10); CREATE TABLE table_2 ( col_1, col_2, col_3, col_4, col_5, col_6, col_7, col_8, col_9, col_10 ); SELECT col_10 FROM (SELECT table_1.col_10 AS col_10 FROM table_1), (SELECT table_1.col_10, table_2.col_9 AS qcol_9 FROM table_1, table_2 GROUP BY table_1.col_10, qcol_9 ); ---- _2007-Jun-28 20:03:38 by anonymous:_ {linebreak} Verified to fix the reported problem on OpenBSD 4.1 with the patch backported to 3.4.0. Thanks guys. You certainly rock. #f2dcdc 2469 build active 2007 Jun anonymous 2007 Jul 1 1 test fails on Solaris I have a problem running the test suite on Solaris 9. Build was done using gcc 4.2.0. The build completes without error but many tests fail. I've created my own minimal test that exhibits the problem:, pArg=0xcfbf3af4, pzErrMsg=0xcfbf1c4c) at src/legacy.c:55 #13 0x1c0046a1 in process_input (p=0xcfbf3af4, in=0x24e8f7a0) at src/shell.c:1656 #14 0x1c003286 in do_meta_command (zLine=0x3c000272 "", p=0x7d2a9145) at src/shell.c:1363 #15 0x1c004851 in process_input (p=0xcfbf3af4, in=0x0) at src/shell.c:1616 #16 0x1c004d9f in main (argc=2, argv=0xcfbf4480) at src/shell.c:1991 (gdb) p pExpr->op Cannot access memory at address 0xd0d0d0d0 (gdb) p pExpr $2 = (Expr *) 0xd0d0d0d0
set testdir [file dirname $argv0] source $testdir/tester.tcl db close file delete -force test.db test.db-journal sqlite db test.db do_test tdb-1 { execsql { PRAGMA auto_vacuum = 1; BEGIN; CREATE TABLE t1(a, b); } execsql { COMMIT; } } {} integrity_check tdb-2 finish_testWhen running this test I get the following output:
tdb-1... Ok tdb-2... Expected: [ok] Got: [{*** in database main *** List of tree roots: 2nd reference to page 1 Page 3 is never used}] Thread-specific data deallocated properly 1 errors out of 3 tests Failures on these tests: tdb-2This error happens on lots of, but not all, tests. I'm happy to do whatever is necessary to help debug this. Thanks, Tim. _2007-Jun-27 10:44:14 by anonymous:_ {linebreak} Further to this, it appears to be related to gcc 4.2.0. It works fine with gcc 3.4.6. ---- _2007-Jun-28 09:54:35 by anonymous:_ {linebreak} Further more, it doesn't appear to be specific to Solaris. The same problem occurs on Linux with gcc 4.2.0. So I guess the subject of this ticket should be changed to "build/test problems with gcc 4.2.0". This is probably a significant problem - the build completes find but the resultant code is broken. People may not notice this until it's too late. ---- _2007-Jun-28 12:24:05 by drh:_ {linebreak} I installed gcc 4.2.0 on my SuSE linux i686 desktop and built test harnesses under three different configurations: gcc420 -g -O0 -Wall -fstrict-aliasing gcc420 -g -O3 -Wall gcc420 -g -O3 -fstrict-aliasing -fomit-frame-pointer The first two configurations used separate source files. The third configuration was built using the amalgamation. I ran the "quick" test under all configurations. All tests ran to completion with no errors. ---- _2007-Jun-28 13:22:20 by anonymous:_ {linebreak} Two ideas: 1. Compile with gcc 4.2.0 using -O0 instead of -O2 and see what happens. Disable any other optimizations you may have. 2. Run truss with full read/write buffer display on the gcc 3.4.6 compiled testfixture running your simple test case and compare its output to the gcc 4.2.0 compiled test case. ---- _2007-Jul-01 19:00:40 by anonymous:_ {linebreak} I've done tests with optimisation, and this appears to tickle the problem. With no optimisation, -O, -O0, -O1 and -03 it works. With -O2 and -Os it's broken. I was compiling with -O2 when I submitted the initial report. Tim. ---- _2007-Jul-01 19:54:54 by drh:_ {linebreak} I can reproduce the problem now on Linux when compiling as follows: gcc420 -g -O2 -Wall ---- _2007-Jul-01 21:50:42 by drh:_ {linebreak} This appears to be a bug in GCC 4.3.0. A work-around is to compile with the -fno-tree-vrp option. GCC appears to miscompile a single loop within the logic that implements the integrity_check PRAGMA. The code that gets miscompiled is in the file vdbe.c (lines numbers added): 4308 for(j=0; j
RCS file: /sqlite/sqlite/src/vdbe.c,v retrieving revision 1.636 diff -u -3 -p -r1.636 vdbe.c --- src/vdbe.c 1 Jul 2007 21:18:40 -0000 1.636 +++ src/vdbe.c 21 Jul 2007 19:10:13 -0000 @@ -4306,7 +4306,8 @@ case OP_IntegrityCk: { pnErr = &p->aMem[j]; assert( (pnErr->flags & MEM_Int)!=0 ); for(j=0; ju.i; } aRoot[j] = 0; popStack(&pTos, nRoot);