bgcolor # Status Created By Subsys Due Date SCR Assigned Svr Pri Title _Description _Remarks
#cfe8bd 154 active 2002 Sep drh Pager drh 3 3 Prohibit links on database files. If a database file is aliased using either hard or symbolic links, it can happen that an aborted transaction will not roll back correctly. Consider this scenario. The database file is named both a.db and a.db. Application one opens a.db and starts to make a change. This creates a journal file a.db-journal. But application one crashes without completing the transaction. Later, application two attempts to open the database as b.db. App two looks for a journal file to rollback, but it thinks the journal should be named b.db-journal. So it fails to see the a.db-journal that app one left and fails to rollback the transaction. The only way I can think of to prevent this kind of thing it to refuse to open any database file that contains two or more hard links and to refuse to open a file through a symbolic link. _2004-Mar-16 20:46:17 by anonymous:_ {linebreak} What if the journal file name wasn't based on the database name, but instead was based on the starting inode of the database file? For instance, "journal-10293" would be used if the starting inode for the associated database file was 10293. ---- _2004-Mar-20 17:17:41 by anonymous:_ {linebreak} Using Inode-numbers to solve this problem is a dangerous proposition, as disk defragmenters can alter the inode the db starts at in between a crash and a subsequent roll-back attempt. ---- _2007-Jun-05 03:57:17 by anonymous:_ {linebreak} On unix, you can use ftok() to solve this problem. It guarantees to return the same key for all paths to the same file, including symbolic and hard links. I have to experience programming in Windows, but have no doubt a similar function call exists in that API.
#cfe8bd 301 active 2003 Apr anonymous Unknown 2 3 Can't acquire lock for database on Mac OS X AppleShare volume I'm using SQLite on Mac OS X 10.2.5. If I try to do a SELECT from a database that resides on an AppleShare volume (from my code or from sqlite), SQLite says that it's locked, even if no other process is using it. It appears that sqliteOsReadLock always returns SQLITE_BUSY for files on AppleShare volumes. I've temporarily solved the problem here by disabling SQLite's locking code and implementing higher-level protections in my application. You may find this link helpful: http://developer.apple.com/technotes/tn/tn2037.html _2004-Mar-22 11:21:56 by anonymous:_ {linebreak} under OSX fcntl returns ENOTSUP (45) = Operation Not Supported when trying to open a DB on AFP or SMB. Local HD is fine (HFS) as well as on USB devices HFS and UFS. Even with the recommendations from Apple's technote tn2037 the problem persists.
#e8e8bd 369 active 2003 Jun anonymous 3 2 Testsuite fails on btree-1.1.1 (Mac OS X, SQLite 2.8.4) On Mac OS X the testsuite fails: btree-1.1.1..../src/btree.c:2687: failed assertion `pPage->isInit' make: *** [test] Abort trap SQLite version: 2.8.4, OS Version: 10.2.6 Obtained same result. Mac OS X 10.2.6, Developer Tools Dec 2002, SQLite 2.8.5. ---- Shared libraries are busted on Macs. As far as I can tell, this appears to be Apple's fault. Until a workaround is devised, do not attempt to compile using shared libraries. Add the --disable-shared option to the configure script: ../sqlite/configure --disable-shared ---- On 2.8.5+, this shows up on 2689. Also, configure does not allow the use of --disable-shared (probably requries a fix in the configure scripts). On a G5 in 10.3.2, this error shows up as a Bus Error. Builds work fine otherwise. This issue may be related to the warnings received in src/test1.c thru src/test4.c and in src/tclsqlite.c regarding Tcl_SetVar, Tcl_GetInt, Tcl_GetBoolean, Tcl_GetIndexFromObj. All warnings are regarding promotion of arguments to pointers of invalid type. oso2k/Louis ---- _2004-Feb-11 22:57:17 by anonymous:_ {linebreak} I did some quick testing of 2.8.12 on the machines I have available to me. In general, there seems to be more warnings than I remember (I believe I was testing 2.8.9 from cvs before it went live).{linebreak} {linebreak} *:G3 700MHz/640MB iBook 10.2.8{linebreak} Same results as we last spoke. Fails make test at:{linebreak} btree-1.1.1{linebreak} {linebreak} *:Dual G4 800MHz/1.25GB 10.2.8{linebreak} Same results as we last spoke. Fails make test at:{linebreak} btree-1.1.1{linebreak} {linebreak} *:G5 1.6GHz/1.25GB 10.3.2{linebreak} Something really weird happens here. There is no longer a bus error. Right after make test gets past bigfile-1.1, the machine seems to enter an infinite loop or something.
#e8e8bd 684 active 2004 Apr anonymous Unknown 3 2 Incorrect function result type when using SQLITE_ARGS I registered a function using the SQLITE_ARGS return type. I then execute the statement "select test('sample')". The type information returned from sqlite_step in the pazColName is incorrectly reported as "NUMERIC". If I use a "0", specifying the first column, instead of SQLITE_ARGS when registering the function, the return value is correctly set to "TEXT".
#cfe8bd 685 active 2004 Apr anonymous CodeGen 1 3 SELECT from a VIEW with GROUP BY When you SELECT from a VIEW (which is having a GROUP BY statement) and try to apply another GROUP BY statement you get: $ sqlite ../../db/main.db SQLite version 2.8.13 Enter ".help" for instructions sqlite> .dump prod_elem_totals BEGIN TRANSACTION; CREATE VIEW prod_elem_totals AS SELECT pe.elem_id AS elem_id, p.prod_id AS prod_id, e.name AS name, p.name AS p_name, pe.count AS count, SUM(b.count) / pe.count AS p_max, SUM(b.count) AS total, SUM(b.price * b.count) / SUM(b.count) AS price, e.min AS min FROM products AS p, elements AS e, batches AS b, prod_elems AS pe WHERE p.prod_id = pe.prod_id AND pe.elem_id = b.elem_id AND pe.elem_id = e.elem_id GROUP BY p.prod_id, pe.elem_id ORDER BY e.name; COMMIT; sqlite> SELECT * FROM prod_elem_totals GROUP BY elem_id; sqlite: src/select.c:1775: flattenSubquery: Assertion `p->pGroupBy==0' failed. Aborted It seams it doesn't matter which column I GROUP BY. I can prepare a full test case if needed. Maybe somehow connected with #678. After further investigation I found that when I add a aggregate function like "SUM (count * 10) AS min" it works...
#f2dcdc 691 active 2004 Apr anonymous Unknown drh 1 1 OS X File Sharing Hello Sir: This ticket may be considered a duplicate of ticket #301. I am unable to access SQLite databases from HFS or SMB network shares when using Mac OS X (10.3.3) as a client. The more technical aspects of the problem are explained well in ticket #301. I am using SQLabs SQLite plugin for RealBasic 5.5, and would like to use SQLite exclusively as my DB.
I am concerned that the original ticket was submitted approximately one year ago. So I am submitting this to see if this issue is being addressed, and if there is a timetable set for its resolution. Thank you, Tony Dellos Milwaukee WI
#cfe8bd 698 active 2004 Apr anonymous Unknown 1 3 .mode list - not going to next line To create a comma delimited output file:{linebreak} ------------------------------------------------{linebreak} C:\SQLite>sqlite locate.db{linebreak} SQLite version 2.8.13{linebreak} Enter ".help" for instructions{linebreak} sqlite> .mode list{linebreak} sqlite> .separator ", "{linebreak} sqlite> .output data.cdf{linebreak} sqlite> select * from parts;{linebreak} sqlite> .quit{linebreak} {linebreak} That should create a text file of something like this:{linebreak} 1st rec field 1, 1st rec field 2, 1st rec field 3, 1st rec field 4{linebreak} 2nd rec field 1, 2nd rec field 2, 2nd rec field 3, 2nd rec field 4{linebreak} 3rd rec field 1, 3rd rec field 2, 3rd rec field 3, 3rd rec field 4{linebreak} {linebreak} but it does not provide a line break after each record, so the output looks like this:{linebreak} {linebreak} 1st rec field 1, 1st rec field 2, 1st rec field 3, 1st rec field 42nd rec field 1, 2nd rec field 2, 2nd rec field 3, 2nd rec field 43rd rec field 1, 3rd rec field 2, 3rd rec field 3, 3rd rec field 4{linebreak} {linebreak} Each record is butted up against the previous record, without even a space. This is inconsitant with the instruction on how it is supposed to work, via this page:{linebreak} {linebreak} http://www.sqlite.org/sqlite.html {linebreak} {linebreak} Also, can you please refer me to somewhere that would explain how I can use SQLite with a batchfile, EG: using a batchfile to add a record, delete a record, query, Etc... {linebreak} {linebreak} Thanks,{linebreak} Tom
#cfe8bd 700 active 2004 Apr anonymous VDBE 2 3 Solaris-sparc segfaults on sum() On Solaris (sparc) trying to do a sum() (sometimes) SEGVs: this is because the result is placed in a chunk of memory which is allocated as a char * and therefore isn't aligned to 16-byte boundaries (which SPARC-Solaris seems to want). One fix for this which seems to work for me is to change vdbeInt.h:118 to char zShort[NBFS] __attribute__ ((__aligned__(16))); /* Space for short strings */ and change sqlite_aggregate_context to assign p->pAgg to zShort rather than z (since z is malloc()ed you can't align it) - I don't know if this would cause problems elsewhere though. _2004-Apr-22 16:10:37 by dougcurrie:_ {linebreak} malloc() should always return memory aligned for any purpose; I don't think this is the problem. Looking at the function sqlite_aggregate_context though, I wonder: *:where is p->z initialized? *:where is p->pAgg sqliteFree()d? *:what happens when sqlite_aggregate_context and sqlite_set_result_string share Mem.zShort? ---- _2004-Apr-23 09:58:03 by anonymous:_ {linebreak} > malloc() should always return memory aligned for any purpose; I don't think this is the problem. I didn't make it clear: I'm getting a Bus Error, not just a normal SEGV. There are several places mentioned on the web which suggests that solaris' malloc() aligns memory to 8-byte boundaries, while (on 64-bit, I assume) a double is 128 bits... however you're correct, the manpage does insist that all malloc()s are aligned to data large enough for any purpose. I suppose if s.z isn't even assigned at this point (but hasn't been cleared at initialisation) it might contain something completely non-aligned. However I now can't reproduce the problem, although that's not to say that it means the thing isn't broken... I'll try and break it again and let you know. As to your other point: the reason I posted was because of exactly this: I don't know the code well enough (I'd never heard of it until yesterday!) to be able to say whether .zShort could be used elsewhere at the same time as a sum() function. ---- _2004-Apr-23 10:35:11 by anonymous:_ {linebreak} Aha. A core file lying around may well help.
... Program terminated with signal 10, Bus Error. ... #0 0xff33d4a4 in sumStep (context=0x2a158, argc=203556, argv=0x3ac08) at src/func.c:421 421 p->sum += sqliteAtoF(argv[0], 0); ... (gdb) list 416 static void sumStep(sqlite_func *context, int argc, const char **argv){ 417 SumCtx *p; 418 if( argc<1 ) return; 419 p = sqlite_aggregate_context(context, sizeof(*p)); 420 if( p && argv[0] ){ 421 p->sum += sqliteAtoF(argv[0], 0); 422 p->cnt++; 423 } 424 } .... (gdb) print &p.sum $5 = (double *) 0x31b24Now my basic maths (a hex 16-byte aligned number should end in 0, right?) says that somehow p.sum has become misaligned by 4 bytes. sqlite_aggregate_sum simply assigns p->pAgg to p->s.z and returns p->pAgg, which means that p->s.z is misaligned also. Further investigation makes more worrying reading:
(gdb) print *context $18 = {pFunc = 0x75736572, s = {i = 0, n = 0, flags = 16, z = 0x0, r = 3.6586602629506839e-309, zShort = '\000'Note that pAgg is 0x10 (!?) and s.z is 0. Something seriously unhappy going on there: I think it's likely there's some corruption going on due to some specific set of events which was being run. I'll rerun the exact scenario and try again. ---- _2004-May-03 02:27:25 by anonymous:_ {linebreak} i have seen this exact same problem on sparc/solaris. my core looks exactly the same. it really does look like an alignment issue. ---- _2004-Jul-12 13:09:26 by anonymous:_ {linebreak} I had this exact problem on solaris 8 with gcc3.3.4 and well, every version of sqlite over 2.5. Heres my solution, hopefully it will help someone with more time and IQ points to figure out the real problem After forcing PTR_FMT to %x in test1.c (so i can run all the tests) I changed src/vbdeInt.h #define NBFS from 32 to 15 (one less than a long double on a sparc) thus forcing all long doubles to be malloced. This allowed me to run all the tests (and my application) bus error free. 5 of the tests failed, which looks like a precision problem and seems harmless in my applications. date-1.19... Expected: [2451545.00000116] Got: [2451545.00000] date-1.20... Expected: [2451545.00000012] Got: [2451545.00000] date-1.21... Expected: [2451545.00000001] Got: [2451545.00000] expr-2.4... Expected: [0.525641025641026] Got: [0.52564102564] expr-2.5... Expected: [1.90243902439024] Got: [1.90243902439] ---- _2004-Jul-17 12:26:31 by anonymous:_ {linebreak} I found a better solution than my changing NBFS solution. from what little info i found, doubles in a structure are aligned to 8. so align zShort to 8 and it works with NBFS as 32. change PTR_FMT to %X instead of %x and it passes all the tests. #f2dcdc 709 active 2004 Apr anonymous Unknown drh 1 1 Unable to unregister or replace functions I believe this started with 2.8.13, as it did work previously. There appears to be a show-stopper with unregistering or replacing existing functions. Specifically, if one tries to replace (or remove by passing nulls) one of the built-in functions, for example "like" or "upper", the function does not get replaced, and is in fact still called and available. The odd thing is that if you try to replace one of the functions with an underscore, such as "change_count", it works fine! This is causing problems as we replace a lot of the existing functions, and allow users to add and replace their own functions, which are now failing. _2004-Apr-26 20:56:19 by anonymous:_ {linebreak} Alright, turns out this is due to mismatch in argument count when unregistering functions. It would be useful if we could unregister all instances of a function name, irregardless of argument counts. ---- _2004-Apr-26 23:11:43 by anonymous:_ {linebreak} Turns out there really is a bug... the problem is in the sqliteFindFunction function's matching of inexact argument counts, when being called from sqliteExprCheck with >0 argument count. This is the scenario. I override the "upper" function with my own, but first removing the old "upper", specifying 1 argument and null for the function. I then register a new "upper" with -1 for the argument count, and a valid function. When sqliteFindFunction attempts to locate the upper function, It locates the new function, but because it is registered with -1, it tries to find a better match. It then runs into the original one, and because it has a null function pointer, it fails. This causes sqliteExprCheck to try again with -1 as the count, and since that matches, it reports an error of wrong_num_args. Unfortunately this means there is no way to override an existing method (it would be good if we could just delete them, rather than override them, although I still think this behaviour with -1 is wrong). ---- _2004-Apr-26 23:29:23 by anonymous:_ {linebreak} I've applied the following patch in the sqliteFindFunction function, that I believe addresses the problem:, "\020\000\000\000\000\000\002ˇ\230", '\000' }, pAgg = 0x10, isError = 0 '\000', isStep = 0 '\000', cnt = 172464}
/* Change this if( p && !createFlag && p->xFunc==0 && p->xStep==0 ){ return 0; } */ /* To this */ if( p && !createFlag && p->xFunc==0 && p->xStep==0 ){ return pMaybe;By returning pMaybe we provide a function that will work, while returning 0 if no variable argument function was found. #e8e8bd 841 active 2004 Aug anonymous Unknown Pending 3 2 inner group by query isn't honored by outer count(*) aggregate CREATE TEMP TABLE A(a int NOT NULL, b int NOT NULL, c int NOT NULL); INSERT INTO A VALUES (1, 1, 1); INSERT INTO A VALUES (1, 2, 1); INSERT INTO A VALUES (2, 1, 1); -- typical behaviour is for this to behave like the DISTINCT query below -- but instead it shows a=1 as having occured twice (but it was grouped in the inner query) SELECT a, count(*) FROM ( SELECT a, c FROM A GROUP BY 1, 2) GROUP BY a; Result: 2|1 1|2 -- shows a=1 as having occured once (correctly) SELECT a, count(*) FROM ( SELECT DISTINCT a, c FROM A) GROUP BY a; Result: 2|1 1|1 -- the top query performs better, which is why I am reporting this bug _2004-Aug-08 18:42:00 by drh:_ {linebreak} SQLite ignores the ORDER BY clause if there are no aggregate functions. #cfe8bd 1026 active 2004 Dec anonymous Unknown Pending 3 3 sqlite automake sqlite3.pc file does not have version information When the configure of sqlite has been done, the sqlite3.pc file does not have information in the Version: section. This means there's no way to check for versions in other autogen/configure files concerning the sqlite version in the system, style: PKG_CHECK_MODULES(SQLITE, sqlite >= 3.0.3, AC_MSG_ERROR([$SQLITE_PKG_ERRORS])) #cfe8bd 1030 active 2004 Dec anonymous Shell Pending 2 3 impossible to import a file and do other things in the same invocation (strongly affects scripting) The sqlite2 shell can be scripted in 2 ways: 1. sqlite mydb 'commands' 2. commands | sqlite mydb The first form has a bug, which I have not submitted, and which also occurs in sqlite3: o dot commands are not fully intermixable with sql commands It will also fail for long command lines. To work around this, it is necessary to use the second form of scripting. However: o for entering data via COPY or .import, a separator is necessary to allow sql after the data o COPY has \. as a separator Here is the sqlite3 bug: o .import has no separator that I know of The only possible workaround seems to be to call the executable more than once. However: o this makes :memory: databases impossible Therefore, I said this is major with a workaround. The workaround is to use temporary files instead of memory databases, and: o inconveniently kludge the sqlite2 behavior using multiple calls to the executable However, files are far slower than memory, making the use of sqlite as an awk-like filter less attractive than it was with sqlite2. Here are 2 solutions to fix the problem, both of which are desirable: o allow dot commands and sqlite commands to be intermixable on the command line such as with "sqlite :memory: '.show;.import ...;select ...;.import ...; select ...'" o allow a separator for .import Here are some related bugs, also not submitted: o .separator is overloaded to mean input and output separators, but for scripting it would be useful to have them separate o csv mode and tabs mode are lightly documented. what are the exact syntaxes for them (line continuation, quoting, etc.), and what is the difference between tabs mode and .separator set to tab? o it might also be useful to have a .with command so that you can do .with .separator ","; .import ...; .endwith to effectively emulate (let ((...)) ...) in lisp. i.e. temporarily set a value to something without having to know what to set it back to when you are done. Sqlite rocks. Thanks. #cfe8bd 1053 active 2004 Dec anonymous Pager Pending 3 3 SQLITE_IOERR and strange rollback when db is busy Environment on which bug was found:{linebreak} Windows XP, both SP1 and SP2, on different computers. The SQLite library was built using the precompiled source from the download page (as static library). Description of bug scenario: One process performs very long reads from a db (multiple joins, so the cartesian product is *very* large, and the reader needs a while to complete). Another process performs a _BEGIN TRANSACTION_ , then executes lots of _INSERT INTO ... VALUES_ .{linebreak} At some point, this process will end up in sqlite3pager_get, when it tries to read some page from the database file (the main file, not a temp file or a journal). It detects that the page is not in the page cache (it ends up in the 'else' branch of _if( pPg==0 )_ ). It runs down to the block of code covered by the following comment: /* Write the page to the database file if it is dirty. */ In this block, pager_write_pagelist( pPg ) returns with SQLITE_BUSY. As a consequence, the changes are rolled back and SQLITE_IOERR is returned. And here seems to be the problem: First, the database file is locked, so I don't understand why the SQLITE_BUSY value isn't propagated back to the caller. If SQLITE_BUSY would be returned, then the application could restart the command. Seconds, sqlite3VdbeHalt decides to perform a sqlite3BtreeRollbackStmt, so only the last command should be rolled back. However, this is not what happens! In fact, all commands back to the beginning of the transaction are rolled back; the transaction, however is not closed. Doesn't this violate the default rollback behaviour (roll back last command, keep transaction open)? As a consequence, even if the application would get SQLITE_BUSY, it couldn't properly react on it. There are other places in sqlite3pager_get where SQLITE_IOERR are returned; I've not checked whether these can also be triggered by the db being locked or if they indicate serious problem. I will attach the code I used to reproduce and track down the problem, together with a Visual Studio 2003 project. If you extract the archive, on toplevel you will find the following: *: Reader: the directory containing the source for the reader *: Writer: the directory contaiing the source for the writer *: SQLite: A directory in which to place the precompiled source for windows users, which is used to build the library. If you want to use the provided project file with Visual Studio, just copy the source in there and everything will build with a single mouse click. *: BugDemo.sln: The Visual Studio project file. *: bugdemo.sql: The SQL statements used to create the test database. How to reproduce: *: Create a database using bugdemo.sql *: Adapt reader.cpp and writer.cpp to include the sqlite3 headers, and set the define at the top of the files to the path of the test database. *: Compile everything. *: Start the reader. *: Start the writer, and wait until it reports an error (for me, it takes < 30 seconds). I tried to keep the source portable, so it shouldn't be too hard to make it compile on Unix. #cfe8bd 1056 active 2004 Dec anonymous Shell Pending 3 3 test pragma-9.4 fails during second pass in "make fulltest" During a "make fulltest" run, the pragma tests appear to run twice. On the first run, pragma-9.4 runs properly. On the second run, it gives an error: pragma-9.4... Expected: [] Got: [/Volumes/Local/Users/sqlite/test/bld] (where the path listed is the build directory for this build of sqlite). The pragma-9.4 test is a recent addition to sqlite. This is currently the only failure I'm seeing in a "make fulltest" of the current cvs tree on Mac OS X when the build/test directory is on a hard drive. 1 errors out of 68411 tests Failures on these tests: pragma-9.4 make: *** [fulltest] Error 1 #cfe8bd 608 active 2004 Feb anonymous 3 3 Problem with "pragma show_datatypes = on" and busy timeout When a busy timeout is set, pragma show_datatypes = on and SQLite sleeps some time on the lock, no datatypes are passed to the exec callback function. The attachment is an archive with a Makefile, a shell script and a program that reproduce the error. _2004-Feb-12 21:05:28 by anonymous:_ {linebreak} This problem breaks the auto-typing feature of PySQLite when a busy timeout is used. #cfe8bd 627 active 2004 Feb anonymous 3 3 sqliteRunVacuum returning wrong code? The last 3 lines of sqliteRunVacuum, as of the version checked in on Feb 12 2004, are: if( rc==SQLITE_ABORT ) rc = SQLITE_ERROR; if( sVac.rc!=SQLITE_OK ) rc = sVac.rc; return sVac.rc; It seems suspicious to set a local variable, rc, that one is never going to use again. I suspect that the last line should be return rc; _2004-Feb-27 00:54:03 by anonymous:_ {linebreak} The fix by check-in 1271 still doesn't look right to me. If one of the execsql calls returns SQLITE_CANTOPEN (which I have seen happen), then rc will be SQLITE_CANTOPEN and sVac.rc will be 0, and sqliteRunVacuum will return 0. #cfe8bd 575 active 2004 Jan anonymous VDBE drh 3 3 pragma (default_)temp_store implementations seems incomplete This problem is a conflict between documented behaviour and actual behaviour, and could fall in the 'Documentation' category as well. There seems to be a problem with 'pragma default_temp_store'. In pragma.c code exists to handle it, and that code stores the provided value in Cookie 5 (as VDBE instruction argument; that is the _sixth_ metadata integer, and would correspond to meta[6] in sqliteInitOne() in main.c). However, the code loading a database (the aforementioned sqliteInitOne() in main.c) never looks at that value, and the setting is ignored. (Also, Vacuum doesn't seem to copy it.) A related problem is that using the default_temp_store or temp_store pragma's doesn't work as advertised, at least not in the precompiled commandline tool sqlite.exe: you will always get the following error, even if you use the pragma at init time: SQL error: The temporary database already exists - its location cannot now be changed Trying to set the flag (the value at offset 0x50 in a database file) to 2 (attempting to force an in-memory database for temp tables) with a hex editor has only partial success: the handcrafted value is reported by _pragma default_temp_store;_ but typing _.databases_ still shows a file name for the temporary data base, and using the filemon tool (a windows file activity monitor, downloadable from www.sysinternals.com) shows that the temp file is actually accessed when giving a 'create temp table' command (not surprising, if there is no code to actually ever initialize the db->temp_store from the Cookie). If for some reason it is infeasible to circumvent the issue that the temp table will always be open before executing the pragma, I suggest changing the semantics of _pragma default_temp_store_ to _only_ change the default (as stored in the file), but _not_ change the current value. This would allow executing _pragma default_temp_store_ even while a temp table is open (though its effect will only be visible when the database is opened again). Note that this issue has a few documentation issues: *: lang.html suggests that _pragma default_temp_store_ and _pragma temp_store_ are currently working. At least in the commandline tool they aren't (I didn't make a dedicated test program to see if the problem already exists at the C-API level) *: fileformat.html doesn't document the location where the temp_store flag is stored. In fact, I consider the fact that the fifth meta value (meta[5] a.k.a. Cookie 4) is seemingly not used anywhere slightly suspicious. *: the number of metadata values is documented inconsistently in fileformat.html: in one place it mentions there are 6 values including the two leading values (which makes 4 metavalues), a bit later 9 metavalues are mentioned... #f2dcdc 798 active 2004 Jul anonymous Unknown 1 1 Unable to run tests on Tru64 bit Linux platform I was able to compile SQLite 3.0.2 on a RedHat 64-bit Linux system; however, when running the tests I would get a segmentation fault when executing a blob test. I was wondering if anyone has attempted to build SQLite for a 64-bit architecture and run all tests successfully. If so I was hoping to get any configuration parameters needed. #cfe8bd 754 active 2004 Jun anonymous Shell drh 2 3 problem opening a dbfile in the upper directory (../dbname) there is a problem in the calculation of a full path name based on a relative path name in an uproot location (../). i have fixed this during my porting to dos, and the relevant diff is at http://www.sqlite.org/cvstrac/tktview?tn=524. best regards alex #cfe8bd 783 active 2004 Jun anonymous Unknown 3 3 Build on MAC with -DSQLITE_DEBUG=1 compile error MacOS 10.3.4 gcc 3.3 Compileing with -DSQLITE_DEBUG=1 gives following error ./libtool --mode=compile gcc -g -O2 -DOS_UNIX=1 -DHAVE_USLEEP=1 -DSQLITE_DEBUG=1 -I. -I../sqlite/src -DTHREADSAFE=0 -c ../sqlite/src/os_unix.c gcc -g -O2 -DOS_UNIX=1 -DHAVE_USLEEP=1 -DSQLITE_DEBUG=1 -I. -I../sqlite/src -DTHREADSAFE=0 -c ../sqlite/src/os_unix.c -fno-common -DPIC -o .libs/os_unix.o ../sqlite/src/os_unix.c: In function `sqlite3OsRead': ../sqlite/src/os_common.h:31: error: inconsistent operand constraints in an `asm' make: *** [os_unix.lo] Error 1 #e8e8bd 721 active 2004 May anonymous Shell drh 3 2 empty .databases information in file shell.c around line 570 the callback_data structure needs to be added: data.cnt = 0; so the column widths are correctly used. or else, it will display empty lines. this problem is visible after executing one sql command #cfe8bd 735 active 2004 May anonymous Shell 4 3 .sqliterc not processed if running on a driver other than C: In shell.c there is a snippet that reads: if (!home_dir) { home_dir = getenv("HOMEPATH"); /* Windows? */ } The HOMEPATH environment variable does not include the drive letter and needs to be concatenated with the HOMEDRIVE environment variable. _2004-May-12 14:43:40 by anonymous:_ {linebreak} That should read "drive" in the title, not "driver" #e8e8bd 744 active 2004 May anonymous BTree anonymous 2 2 make test seg faults on x86_64 Linux I'm running the 64 bit version of Gentoo Linux on an AMD Opteron system. Ordinarily I'd install software with "emerge
#-----------> load ./lib/tclsqlite-3.0.8.so sqlite3 puts [info patchlevel] sqlite3 db :memory: db eval "create table t1(a,b);" puts "before 3.0.8 select, no pragma" db eval "select * from t1;" x { puts "x(*) = $x(*)" } db eval "PRAGMA empty_result_callbacks=1" puts "before 3.0.8 select, yes pragma" db eval "select * from t1;" x { puts "x(*) = $x(*)" } db close load ./lib/tclsqlite-2.8.15.so Tclsqlite sqlite db2 :memory: db2 eval "create table t1(a,b);" puts "before 2.8.15 select, no pragma" db2 eval "select * from t1;" x { puts "x(*) = $x(*)" } db2 eval "PRAGMA empty_result_callbacks=1" puts "before 2.8.15 select, yes pragma" db2 eval "select * from t1;" x { puts "x(*) = $x(*)" } db2 close puts "done" # <-------------------and the results:
$ tclsh test_sqlite.tcl 8.4.3 before 3.0.8 select, no pragma before 3.0.8 select, yes pragma before 2.8.15 select, no pragma before 2.8.15 select, yes pragma x(*) = a b done_2006-May-16 18:29:44 by anonymous:_ {linebreak} This is still a problem in the 3.3.5 version of the tclsqlite library. The tclsqlite.c code never calls the callback code on empty results when PRAGMA empty_result_callbacks=1 is set. #e8e8bd 923 active 2004 Sep anonymous Pending anonymous 3 2 Missing quotes in 2.8.15 .dump cause data loss when loading in sqlite3 When converting a database by means of the command: sqlite old.db .dump | sqlite3 new.db the content of char/varchar fields is dumped by sqlite without quotes (e.g. 00001) and then when reloaded by sqlite3 it looses the heading zeroes (i.e. becomes '1', which is a really different thing for an alphanumeric field). This could be solved by a new release (sqlite 2.8.16 ?) which add quotes to alphanumeric fields (as sqlite3 does), or by a filter script that adds the quotes to the sqlite2 .dump output (I used a quick and dirty perl script to fix my dump...). _2005-Jul-11 20:08:11 by anonymous:_ {linebreak} This does not appear to be solved in sqlite 2.8.16. #cfe8bd 1200 active 2005 Apr anonymous Pending 2 3 new versions of SQLite return different (incorrect) results. Newer versions of SQLite are returning different, and I believe incorrect, results compared to those returned by older versions. Using version 3.0.8 the following query returns the correct results given the attached database. sqlite> select * from device_property_list where device_property_value = 0; 1|Station|3|Initial Volume|0 1|Station|1|Template|0 2|Station|3|Initial Volume|0 2|Station|1|Template|0 3|Station|3|Initial Volume|0 3|Station|1|Template|0 This same query does not return any results using versions 3.1.6 and 3.2. If the query is changed slightly, by quoting the zero in the where condition, then both of the newer versions return the same set of results as 3.0.8. sqlite> select * from device_property_list where device_property_value = '0'; 1|Station|3|Initial Volume|0 1|Station|1|Template|0 2|Station|3|Initial Volume|0 2|Station|1|Template|0 3|Station|3|Initial Volume|0 3|Station|1|Template|0 The device_property_list is a view that hides a complex join of several tables, and a long case expression that select the value to return for device_property_value; the field that is being tested by the condition. I have attached a sample database and the sql script used to create it for testing. #f2dcdc 1201 active 2005 Apr anonymous CodeGen Pending danielk1977 1 1 erro defined type UINT8_TYPE In file sqliteInt.h line 203 typedef UINT8_TYPE i8; /* 1-byte signed integer */ it should be INT8_TYPE. #f2dcdc 1205 active 2005 Apr anonymous Unknown Pending 3 1 accent ecu etc. in connectionstring Hi Whenever I use a accent grave or accent ecu or whatever accented characters in the connectionstring sqlite turns this into weird characters (in the file name of the database. Since my database name is dependent on user input I can't eliminate this situation... bart@arlanet.com _2005-Apr-13 12:16:08 by drh:_ {linebreak} What version of SQLite? What operating system? ---- _2005-Apr-14 12:22:32 by anonymous:_ {linebreak} Windows 2000 (dutch version) Sqlite version 3. the word privé in the connectionstring becomes privĂ©.db3 on the disk, which causes errors afterwards when opening the database ---- _2005-Apr-14 12:23:06 by anonymous:_ {linebreak} I see something goes wrong with the accents as well on the previous remark ---- _2005-Apr-14 12:29:41 by anonymous:_ {linebreak} It really sounds to me like you're taking an accented character encoded in ISO8859-1 or some similar single-byte encoding and feeding it to something that's expecting a UTF-8 encoded string. In UTF-8, any byte that has its high bit set *must* be part of a multi-byte sequence. ---- _2005-Apr-14 14:00:56 by anonymous:_ {linebreak} After converting my string to an UTF8 encoded string, it still does the same thing.... ---- _2005-Apr-14 14:06:41 by anonymous:_ {linebreak} using sqlite.net #e8e8bd 1213 active 2005 Apr anonymous Parser Pending 3 2 Problem with alias columns in subqueries The following query create an SQL error while it seems to me to be SQL complient (in fact it works fine with a lot database servers): sqlite> select i from (select 0 as i union all select 1) as tmp; SQL error: no such column: i I found a workaround writting the following SQL query (it works well if you explicits the implicit variable i) : select i from (select 0 as i union all select 1 as i) as tmp; Thanks for your help Jerome _2005-Apr-17 11:03:52 by anonymous:_ {linebreak} SQLite appears to take the column names from the last clause in a UNION (your originally query works if you reverse the order of the clauses). IIRC, the standard says that the column names in a UNION of clauses with different column names is DBMS-dependent and while many DBMSs take them from the first clause, this is not something to count on. ---- _2005-Apr-18 19:53:09 by anonymous:_ {linebreak} You're perfectly right, but it seems to me that it should be better that sqlite do respect the "de facto" standard. #cfe8bd 1214 active 2005 Apr anonymous Unknown Pending 2 3 sqlite3_column_bytes returns 0 on p3 column with EXPLAINed selects Both functions sqlite3_column_bytes() sqlite3_column_bytes16() do not return the correct length for the p3 text column of EXPLAIN select queries. Both functions always return 0, even if the p3 column contains text. The bug can be easily reproduced with the following query: EXPLAIN SELECT 'text'; The p3 column, row 2, contains the word 'text', but the functions return 0 regardles. I have not seen this bug with non EXPLAIN queries, but it breaks code which relies on the fact that sqlite3_column_bytes always retun the correct length of the text and needs to preallocate memory accordingly. #cfe8bd 1228 active 2005 Apr anonymous Unknown Pending 4 3 problem with select+union on a view with aliased columns CREATE TABLE tbl1 (col1 VARCHAR PRIMARY KEY); INSERT INTO tbl1 VALUES ('1'); INSERT INTO tbl1 VALUES ('2'); CREATE VIEW view1 AS SELECT col1 AS col1a FROM tbl1; This creates a view with one column (col1a). Normal SELECTs on this view return results as expected, but the following: SELECT col1a FROM view1 WHERE col1a = 1 UNION SELECT col1a FROM view1 WHERE col1a = 2; Produces: col1 1 2 When the column name should in fact be col1a (this is the behaviour in postgres). You can work around this by doing: SELECT col1a AS col1a FROM view1 WHERE col1a = 1 UNION SELECT col1a AS col1a FROM view1 WHERE col1a = 2; But that shouldn't be necessary. Thanks. --Sebastian Kun #f2dcdc 1351 active 2005 Aug anonymous Shell Pending 1 1 Unable to parse UTF8 input I'm in the process of writing a program which parses in UTF8 data, and then processes it and writes a UTF8 output into a text file. This textfile needs to be imported into SQLite. However the commandline SQLite program doesnt support UTF8 input text files for its ".read" command. Considaring the database itself supports UTF8, would it be possible to allow UTF8 text file input. I can't progress much further on my program if this can't be fixed. _2005-Aug-08 13:56:24 by drh:_ {linebreak} Please attach an example UTF-8 script that ".read" is not reading correctly. ---- _2005-Aug-08 17:58:44 by anonymous:_ {linebreak} I can't seem to attach a text file, so instead i'll put it on my FTP, and it should be accessible from there. If you have trouble with that, i could email the text file. So if you have trouble, give me an email to send it to. There will be a sample of a text file that won't ".read" available here: ftp://62.231.38.73/ in approximately 10 minutes. Thanks for the fast reply. I'd be surprised if it was a problem with my text file generation, but stranger things have happened :p ---- _2005-Aug-11 01:40:08 by drh:_ {linebreak} Attach files using the [Attach] hyperlink at the top-right of this page. Please do not send RAR files since that is an obscure archive format. If you want to use a compressed archive, make it either ZIP or GZIP. ---- _2005-Aug-11 09:46:23 by anonymous:_ {linebreak} I can only attach one because neither winzip or gzip are great at compressing text files. The files keep ending up over 100kb except for the artists.txt file. Thats the only one that went below 100kb. I'm unwilling to try editing the file to remove lines of text from it because i want you to have the exact output that i'm getting from my program. Not the output that i'd get from notepad if i edited it. That will help identify whether it is a problem in my program, or a problem with SQLite. Its always possible its a proglem with my source data, and its not actually proper UTF8, but i doubt that as SQLite doesn't seem to read the normal text correctly (such as the first line which is supposed to start a transaction, but SQLite instead ignores the line, and throws an error when it reachs the commit; at the end. #cfe8bd 1365 active 2005 Aug anonymous Pending 3 3 64 bit types not completely overridable The current 64 bit types in sqlite3.h and sqliteInt.h do not allow the type to be overriden using a preprocessor definition, unlike all the other base types. The current 64 bit typedefs assume that a "long long" is 64 bits - this is not guaranteed (and on PS2 it is wrong, long long is 128 bits). Here are some minor patches that should allow these types to be overriden, but keep the old behavior if they are not: ==== //sqlite-3.2.2/src/sqlite3.h#1 - sqlite-3.2.2\src\sqlite3.h ==== 81,83c81,83 < #if defined(_MSC_VER) || defined(__BORLANDC__) < typedef __int64 sqlite_int64; < typedef unsigned __int64 sqlite_uint64; --- > #ifdef INT64_TYPE > typedef INT64_TYPE sqlite_int64; > typedef unsigned INT64_TYPE sqlite_uint64; 85,86c85,93 < typedef long long int sqlite_int64; < typedef unsigned long long int sqlite_uint64; --- > # if defined(_MSC_VER) || defined(__BORLANDC__) > typedef __int64 sqlite_int64; > typedef unsigned __int64 sqlite_uint64; > # else > typedef long long int sqlite_int64; > typedef unsigned long long int sqlite_uint64; > # endif > # define INT64_TYPE sqlite_int64 > # define UINT64_TYPE sqlite_uint64 ==== sqlite-3.2.2/src/sqliteInt.h#1 - sqlite-3.2.2\src\sqliteInt.h ==== 157,163d156 < #ifndef UINT64_TYPE < # if defined(_MSC_VER) || defined(__BORLANDC__) < # define UINT64_TYPE unsigned __int64 < # else < # define UINT64_TYPE unsigned long long int < # endif < #endif 183c176 < typedef UINT64_TYPE u64; /* 8-byte unsigned integer */ --- > typedef sqlite_uint64 u64; /* 8-byte unsigned integer */ _2005-Aug-27 16:45:09 by drh:_ {linebreak} Can someone suggest a suitable #ifdef that will automatically identify a PS and do the right thing to provide a 64-bit integer type, similar to what is down for windows? #f2dcdc 1382 active 2005 Aug anonymous Pending drh 1 1 Assert nErr==0 on corrupt db I'm working on an embedded filesystem where files can be randomly altered. Sometimes my .db files get messed up. I've attached an example db. I'd like to catch the asserts and return an error rather than crash. $ sqlite corrupt-assert.db 'select count(*) from sensor' sqlite: src/main.c:120: sqliteInitCallback: Assertion `nErr==0' failed. Aborted #f2dcdc 1397 new 2005 Aug anonymous Shell Pending 1 1 .mode csv creates ASCII output instead of UTF-8 compiled from source .mode csv mixes up the charset IMHO Example "für" becomes "f\37777777703\37777777674r" which makes the output file not usable _2005-Aug-31 13:54:57 by anonymous:_ {linebreak} it's better but not UTF-8 utrac -p says ASCII Güssing now is G\303\274ssing hope it's ok to reopen the bug ---- _2005-Sep-03 15:29:37 by anonymous:_ {linebreak} .mode tab csv list all destroy the output now ---- _2005-Sep-20 08:10:34 by anonymous:_ {linebreak} same problem in 3.2.6 ---- _2005-Oct-06 20:14:37 by anonymous:_ {linebreak} csv of today: .mode cvs still does not work, .mode tab works fine now #cfe8bd 1573 active 2005 Dec anonymous Shell Pending 3 3 Bad CSV output when data contains double quotes SQLite 3.2.7 emits invalid CSV when a field value contains double quotes (or at least, it's CSV that Gnumeric cannot parse). Here's an example: sqlite> create table foo (a text, b text); sqlite> insert into foo values ("hello", "world"); sqlite> insert into foo values ("mr.", "o'reilly"); sqlite> insert into foo values ('12" EP', 'blah'); sqlite> .mode csv sqlite> .output foo.csv sqlite> select * from foo; Here's what foo.csv looks like: $ cat foo.csv "hello","world" "mr.","o'reilly" "12" EP","blah" Note the ambiguous quoting on line 3. If I load this file into Gnumeric, it parses the first two lines just fine. But the last line confuses it. It appears that doubling the quote works -- at least for Gnumeric's CSV parser. That is, if I edit foo.csv to "hello","world" "mr.","o'reilly" "12"" EP","blah" then it's OK. This is basically a duplicate of [1312] and related shell problem reports, though it provides a better test case than the previous reports. #cfe8bd 1100 active 2005 Feb anonymous Pending 3 3 make test segfaults at capi2-7.12 on amd64 system System is Gentoo Linux 2004.1 on Opteron processor; gcc v3.3.3, creating 64 bit binaries. Here is a traceback: capi2-7.11... Ok capi2-7.11a... Ok capi2-7.12... Program received signal SIGSEGV, Segmentation fault. 0x0000002a95b25830 in strlen () from /lib/libc.so.6 (gdb) where #0 0x0000002a95b25830 in strlen () from /lib/libc.so.6 #1 0x000000000043991c in sqlite3VdbeList (p=0x5aab60) at src/vdbeaux.c:528 #2 0x0000000000438737 in sqlite3_step (pStmt=0x13000a6023d0064) at src/vdbeapi.c:207 #3 0x0000000000416a80 in test_step (clientData=0x13000a6023d0064, interp=0x55a450, objc=0, objv=0x4) at src/test1.c:2070 #4 0x0000002a956978fe in TclEvalObjvInternal () from /usr/lib/libtcl8.4.so #5 0x0000002a956ba181 in TclCompEvalObj () from /usr/lib/libtcl8.4.so #6 0x0000002a956b9648 in TclCompEvalObj () from /usr/lib/libtcl8.4.so #7 0x0000002a956e4f66 in TclObjInterpProc () from /usr/lib/libtcl8.4.so #8 0x0000002a956978fe in TclEvalObjvInternal () from /usr/lib/libtcl8.4.so #9 0x0000002a956982d8 in Tcl_EvalEx () from /usr/lib/libtcl8.4.so #10 0x0000002a95698097 in Tcl_EvalTokensStandard () from /usr/lib/libtcl8.4.so #11 0x0000002a95698273 in Tcl_EvalEx () from /usr/lib/libtcl8.4.so #12 0x0000002a95698767 in Tcl_EvalObjEx () from /usr/lib/libtcl8.4.so #13 0x0000002a956e4a93 in Tcl_UplevelObjCmd () from /usr/lib/libtcl8.4.so #14 0x0000002a956978fe in TclEvalObjvInternal () from /usr/lib/libtcl8.4.so #15 0x0000002a956ba181 in TclCompEvalObj () from /usr/lib/libtcl8.4.so #16 0x0000002a956b9648 in TclCompEvalObj () from /usr/lib/libtcl8.4.so #17 0x0000002a956e4f66 in TclObjInterpProc () from /usr/lib/libtcl8.4.so #18 0x0000002a956978fe in TclEvalObjvInternal () from /usr/lib/libtcl8.4.so #19 0x0000002a956982d8 in Tcl_EvalEx () from /usr/lib/libtcl8.4.so #20 0x0000002a95698767 in Tcl_EvalObjEx () from /usr/lib/libtcl8.4.so #21 0x0000002a956e4a93 in Tcl_UplevelObjCmd () from /usr/lib/libtcl8.4.so #22 0x0000002a956978fe in TclEvalObjvInternal () from /usr/lib/libtcl8.4.so #23 0x0000002a956ba181 in TclCompEvalObj () from /usr/lib/libtcl8.4.so #24 0x0000002a956b9648 in TclCompEvalObj () from /usr/lib/libtcl8.4.so #25 0x0000002a95698815 in Tcl_EvalObjEx () from /usr/lib/libtcl8.4.so #26 0x0000002a9569c486 in Tcl_CatchObjCmd () from /usr/lib/libtcl8.4.so #27 0x0000002a956978fe in TclEvalObjvInternal () from /usr/lib/libtcl8.4.so #28 0x0000002a956ba181 in TclCompEvalObj () from /usr/lib/libtcl8.4.so #29 0x0000002a956b9648 in TclCompEvalObj () from /usr/lib/libtcl8.4.so #30 0x0000002a95698815 in Tcl_EvalObjEx () from /usr/lib/libtcl8.4.so #31 0x0000002a9569ef7e in Tcl_IfObjCmd () from /usr/lib/libtcl8.4.so #32 0x0000002a956978fe in TclEvalObjvInternal () from /usr/lib/libtcl8.4.so #33 0x0000002a956ba181 in TclCompEvalObj () from /usr/lib/libtcl8.4.so #34 0x0000002a956b9648 in TclCompEvalObj () from /usr/lib/libtcl8.4.so #35 0x0000002a956e4f66 in TclObjInterpProc () from /usr/lib/libtcl8.4.so #36 0x0000002a956978fe in TclEvalObjvInternal () from /usr/lib/libtcl8.4.so #37 0x0000002a956982d8 in Tcl_EvalEx () from /usr/lib/libtcl8.4.so #38 0x0000002a956d1e41 in Tcl_FSEvalFile () from /usr/lib/libtcl8.4.so #39 0x0000002a956d120e in Tcl_EvalFile () from /usr/lib/libtcl8.4.so #40 0x0000000000424641 in main (argc=2, argv=0x7fbffff138) at src/tclsqlite.c:1744 (gdb) _2005-Feb-04 23:11:02 by anonymous:_ {linebreak} Some more information from a gdb session (I added the printf at line 527 and recompiled; the line numbers below will be off by one from the stack trace above): (gdb) up #1 0x000000000043996d in sqlite3VdbeList (p=0x5aab60) at src/vdbeaux.c:529 529 pMem->n = strlen(pMem->z); (gdb) list 525,535 525 526 pMem->flags = MEM_Static|MEM_Str|MEM_Term; 527 printf("src/vdbeaux.c sqlite3VdbeList pOp->opcode= %d\n", pOp->opcode); 528 pMem->z = sqlite3OpcodeNames[pOp->opcode]; /* Opcode */ 529 pMem->n = strlen(pMem->z); 530 pMem->type = SQLITE_TEXT; 531 pMem->enc = SQLITE_UTF8; 532 pMem++; 533 534 pMem->flags = MEM_Int; 535 pMem->i = pOp->p1; /* P1 */ (gdb) p pOp->opcode $1 = 40 '(' (gdb) p pMem->z $2 = 0x175002100200173 #f2dcdc 1111 active 2005 Feb anonymous Unknown Pending 3 1 no such column error with a subselect as a table Execute the following script. In 3.0.8, you get the following results: line 0 line 2 in 3.1.1 beta, you get the following error: SQL error: no such column: bb.a SCRIPT: create table test1 (a int); insert into test1 values (0); insert into test1 select max(a)+1 from test1; insert into test1 select max(a)+1 from test1; create table test2 (a int, b text); insert into test2 select a,'line ' || a from test1; select test2.b from (select test1.a from test1 where a%2 = 0) as bb join test2 on bb.a = test2.a; _2005-Feb-21 17:48:20 by anonymous:_ {linebreak} Tested in 3.1.3, issue still exists ---- _2005-Jun-12 22:09:08 by drh:_ {linebreak} Workaround: select test2.b from (select test1.a as a from test1 where a%2=0) as bb join test2 on bb.a=test2.a; #cfe8bd 1134 active 2005 Feb anonymous Pending drh 3 3 select command with a view and a condition gets no result In a view with more than one tables it is no longer possible to use that view within a select command; the columns of the view are not found. #f2dcdc 1142 active 2005 Feb anonymous Parser Pending danielk1977 1 1 Column names create table a (id, x); create table b (id, y); insert into a values (1,1); insert into b values (1,2); select * from a inner join b; column names returned: id,x,id,y How am I supposed to use such column names? Ouwey. _2005-Mar-15 07:28:43 by anonymous:_ {linebreak} This bug breaks existing applications where 'SELECT rowid, * FROM table' was used to open any table and then precompiled statements were used to update the changed record by using 'UPDATE table SET
CREATE VIEW test1 AS SELECT * FROM tableA; CREATE VIEW test AS SELECT COUNT(*) FROM test1;then a dump and restore fails, because the views will be created in the alphabetical order of the table names, rather than the order of their dependence. Thus, the create of table test will fail. Because the script otherwise runs to completion, the restore will usually be adequate except that the views are not recreated. VACUUM appears to fail the same way, probably for the same reason. In this case, however, the VACUUM fails without clearly alerting the user. I ran into this problem trying to VACUUM a large file, which produced the otherwise inexplicable error message about the syntax of a dependent view definition. Users can workaround, once the problem is understood, either by renaming views so that their names have alphabetical sequence consistent with their dependency, or by dropping them prior to a vacuum or dump/restore and then recreating them later. Of course, the view order can be repaired in the dump file, but this usually requires patience with a powerful text editor, and some capacity to understand the problem. #cfe8bd 1058 active 2005 Jan anonymous BTree Pending 3 3 btree.c pageSize -> usableSize Check-in [2125] was a fix for Ticket #1010, but left out some of the fixes proposed in the ticket. I'm not sure whether this was an oversight or an intentional omission. I tried to re-open the ticket, but those edits didn't persist (I'm not sure why). Here are the remaining instances of pageSize that I believe should be changed to usableSize in btree.c: --- sqlite/src/btree.c.ORIG Wed Nov 24 18:54:30 2004 +++ sqlite/src/btree.c Wed Nov 24 20:29:21 2004 @@ -220,13 +220,13 @@ /* The following value is the maximum cell size assuming a maximum page ** size give above. */ -#define MX_CELL_SIZE(pBt) (pBt->pageSize-8) +#define MX_CELL_SIZE(pBt) (pBt->usableSize-8) /* The maximum number of cells on a single page of the database. This ** assumes a minimum cell size of 3 bytes. Such small cells will be ** exceedingly rare, but they are possible. */ -#define MX_CELL(pBt) ((pBt->pageSize-8)/3) +#define MX_CELL(pBt) ((pBt->usableSize-8)/3) /* Forward declarations */ typedef struct MemPage MemPage; @@ -1745,7 +1745,7 @@ Pgno finSize; /* Pages in the database file after truncation */ int rc; /* Return code */ u8 eType; - int pgsz = pBt->pageSize; /* Page size for this database */ + int pgsz = pBt->usableSize;/* Usable bytes on each page */ Pgno iDbPage; /* The database page to move */ MemPage *pDbMemPage = 0; /* "" */ Pgno iPtrPage; /* The page that contains a pointer to iDbPage */ #cfe8bd 1063 active 2005 Jan anonymous Pending 1 3 Lemon bug: Strings in rule code should not be interpreted There are two related bugs in the lemon parser related to processing code snippets defined in rule actions. Here is a simple grammar that demonstrates the problem: %include { extern int line_number; extern const char *file_name; } result(r) ::= TOKEN(s). { printf("BAD: Got a token on line '%d'\n", line_number); printf("BAD: \tFile = '%s'\n", file_name); r = s; } The first bug is that the "%d" in the first printf is interpreted by the append_str function, when it shouldn't be, producing code that looks like: printf("BAD: Got a token on line '0d'\n", line_number); I believe that the solution is to have append_str() NOT do %d substitution when it is copying the code. The second bug is that the "s" in the "%s" format is being interpreted as a symbolic name, producing code that looks like: printf("BAD: \tFile = '%yymsp[0].minor.yy0'\n", file_name); I believe that the solution is to have translate_code() ignore symbolic names inside of quoted strings. #cfe8bd 1078 active 2005 Jan anonymous Pending 2 3 Lemon destructor bugs that don't affect sqlite I found a few bugs Lemon's destructor handling code. I don't think that they affect sqlite, but the do affect other grammars. - The code that collapses cases for default destructors erroneously assumes that all symbols have the same type. - If a reduction rule doesn't have code, then the RHS symbols will not have their destructors called. - The default destructor shouldn't be called on the auto-generated "error" symbol - In the internal function "append_str", zero-length strings may be returned un-terminated. I have some proposed fixes that I'll try to attach to this ticket. _2005-Jan-14 13:33:52 by drh:_ {linebreak} Do you also have some test grammars? That would really be helpful. ---- _2005-Jan-14 17:14:15 by anonymous:_ {linebreak} Sure. Here is one grammar that will demonstrate the "Tokens leak when rule has no code" bug:
%token_type { char * } %token_destructor { printf("Deleting token '%s' at %x\n", $$, (int)$$); free($$); } result ::= nt. nt ::= FOO BAR.Running the following code against the grammar should theoretically show 2 allocations and two destructions. It won't though, unless you modify the rule for nt to have an empty body, like: {linebreak} nt ::= FOO BAR. {}
char *mkStr(const char *s) { printf("Allocating '%s' at 0x%x\n", s, (int)(s)); return strdup(s); } int main(int argc, char **argv) { void *parser = ParseAlloc(malloc); Parse(parser, FOO, mkStr("foo")); Parse(parser, BAR, mkStr("bar")); Parse(parser, 0, 0); ParseFree(parser, free); return 0; }---- _2005-Jan-14 17:50:26 by anonymous:_ {linebreak} Here is another test grammar. This one demonstrates (a) default destructors being called on the 'error' symbol, and (b) problems with default destructors being called on the wrong symbol type.
%token_type { char * } %token_destructor { delete [] $$; } %default_destructor { delete $$; } %type result { int } %destructor result { } result ::= fooStruct barStruct. { } %type fooStruct { Foo * } fooStruct(lhs) ::= FOO(f). { lhs = new Foo(f); } %type barStruct { Bar * } barStruct(lhs) ::= BAR(b). { lhs = new Bar(b); }Here is the code generated by lemon (with comments added & removed for clarity):
typedef union { ParseTOKENTYPE yy0; int yy4; Bar * yy5; Foo * yy7; int yy15; } YYMINORTYPE; static const char *const yyTokenName[] = { "$", "FOO", "BAR", "error", "result", "fooStruct", "barStruct", }; static void yy_destructor(YYCODETYPE yymajor, YYMINORTYPE *yypminor){ switch( yymajor ){ case 1: case 2: { delete [] (yypminor->yy0); } break; case 3: /* error */ case 5: /* fooStruct of type "Foo *" */ case 6: /* barStruct of type "Bar *" */ #line 3 "typeBug.y" { delete (yypminor->yy5); } /* Yikes! yy5 is a "Bar *" */ #line 308 "typeBug.c" break; case 4: #line 6 "typeBug.y" { } #line 313 "typeBug.c" break; default: break; /* If no destructor action specified: do nothing */ } }#cfe8bd 1085 active 2005 Jan anonymous Pending 2 3 pragma full_column_names and short_column_names still broken the following statement : SELECT T1.*,D1.* FROM test T1,dt D1 WHERE T1.id=D1.id does not give "long" column names, even if full_column_names is ON. But, the following does: SELECT T1.ID,D1.NAME FROM test T1,dt D1 WHERE T1.id=D1.id in other words, tablename prefix is applied only to explicit columns, not to "*" selected columns. #f2dcdc 1323 active 2005 Jul anonymous Pending 1 1 misuse-4.4...gmake: *** [test] Segmentation Fault (core dumped) misuse-4.4...gmake: *** [test] Segmentation Fault (core dumped) on both solaris 8 and 9 build env export CPPFLAGS="-I/tps/include" export LDFLAGS="-L/tps/lib -R/tps/lib" export PKG_CONFIG_PATH=/tps/lib/pkgconfig CC=/tps/bin/gcc CXX=/tps/bin/g++ LD_LIBRARY_PATH=/tps/lib:/tps/lib/sparcv9:/lib:/usr/lib:/usr/local/lib:\ /usr/ccs/lib:/usr/dt/lib:/usr/ucblib:/usr/openwin/lib PATH=/tps/bin:/tps/java/bin:/dsw/source/bin:/dsw/depot-5.13/bin:\ /usr/ccs/bin:/usr/bin:/usr/openwin/bin:/bin:/usr/local/bin:/sbin:\ /usr/sbin:/usr/ucb:/etc:.:/sfoc/bin:/usr/dt/bin:\ /dsw/source/harvest/bin:/usr/afsws/bin:/dsw/pgp-2.6.2s/bin export CC CXX LD_LIBRARY_PATH PATH where /tps is my version of /usr/local where I put all the configuration controlled open source and licensed s/w for my network.
/* ** A single level of the stack or a single memory cell ** is an instance of the following structure. */ struct Mem { int i; /* Integer value */ int n; /* Number of characters in string value, including '\0' */ int flags; /* Some combination of MEM_Null, MEM_Str, MEM_Dyn, etc. */ double r; /* Real value */ char *z ; /* String value */ char zShort[NBFS] __attribute__ ((__aligned__(16))); /* Space for short strings */ }; typedef struct Mem Mem;_2005-Mar-09 22:06:47 by anonymous:_ {linebreak} I think this is a duplicate of bug #700.... simply adding the attribute seems to reslove the bug... select the sum of an integer column, to reproduce.... sqlite> select sum(seqval) from tbl; Bus Error(coredump) #cfe8bd 1191 active 2005 Mar anonymous Unknown Pending 2 3 last_insert_id() Does Not Work after Insert on View If I have a view with an INSERT trigger attached to it, and then use the view to insert a record, last_insert_rowid() does not return the ID inserted. For example, given this DDL: CREATE TABLE _simple ( id INTEGER NOT NULL PRIMARY KEY, name TEXT ); CREATE VIEW simple AS SELECT _simple.id AS id, _simple.name AS name FROM _simple; CREATE TRIGGER insert_simple INSTEAD OF INSERT ON simple FOR EACH ROW BEGIN INSERT INTO _simple (name) VALUES (NEW.name); END; This is what I get when I use the view to insert: sqlite> insert into simple (name) values ('foo'); sqlite> select last_insert_rowid(); last_insert_rowid() ------------------- 0 It does work if I insert directly into the table, of course: sqlite> insert into _simple (name) values ('foo'); sqlite> select last_insert_rowid(); last_insert_rowid() ------------------- 2 _2005-Mar-31 06:04:59 by anonymous:_ VIEWs are read-only so you can not INSERT into them. See: http://www.sqlite.org/omitted.html Regards, Bartosz. ---- _2005-Mar-31 18:12:10 by anonymous:_ {linebreak} Bartosz, Please note that triggers have been applied to the view, so you can actually insert into them. I do it all the time. For example, given my previous examples, this insert will work: sqlite> insert into simple (id, ame) values (1, 'foo'); Pretty coole, eh? See: http://www.sqlite.org/lang_createtrigger.html --Theory ---- _2005-Mar-31 18:21:08 by drh:_ {linebreak} The last_insert_rowid() routine *does* return the correct insert rowid while you are still within the trigger. Once you leave the trigger, last_insert_rowid() returns the rowid of the most recently inserted row outside of any trigger. This is by design. If last_insert_rowid() were to be responsive to inserts done by triggers, then any AFTER INSERT trigger that happened to update a logfile would overwrite the last_insert_rowid() from the actual INSERT. One could argue, I suppose that last_insert_rowid() should work for inserts performed by an INSTEAD OF trigger but not by other kinds of triggers. I will ponder that notion and might implement it if I cannot think of any objections. ---- _2005-Mar-31 18:25:14 by anonymous:_ {linebreak} Allowing it to persist past the trigger in an INSTEAD OF trigger would certainly do what I need. I think that'd make a lot of sense. All of this should probably be well-documented somewhere. I'd be happy to add a page to the wiki once you've decided how to proceed with this issue. Thanks! Theory ---- _2005-Apr-27 02:21:21 by anonymous:_ {linebreak} Have you had a chance to think more on this issue? Thanks, Theory ---- _2005-Nov-08 03:06:51 by anonymous:_ {linebreak} Just checking in on this issue again. Do you think that it's something that will be resolved, one way or the other, soon? Thanks, Theory ---- _2005-Nov-08 17:44:14 by anonymous:_ {linebreak} The instead of insert trigger can't, in general, update the last_insert_rowid value automatically and do it correctly, since a trigger may do multiple inserts into multiple tables. SQLite has no idea which rowid should be reported back outside the trigger. This is left to the user (i.e. the author of the trigger). You can get the last_insert_rowid after the appropriate insert in the trigger and then save that value into an auxillary table that is visible outside the trigger. This is especially important when your view is created from entries in several joined tables. Your instead of insert trigger must do inserts into the individual tables, and may need to use the last_insert_rowid function to link the records in the tables together correctly. It then needs to return the "master" rowid for the table. SQL doesn't have any syntax to specify which value is returned as the rowid of the instead of trigger, so SQLite doesn't do anything automatically. You need to create a sperate last_rowid table, and inside the trigger you update the value of a row in that table (possibly the only row) with the value you want to return. Then the code that does the insert into the view needs to get the value of that row instead of calling last_insert_rowid. This type of behavior is needed since there is no limit to the number of nested instead of triggers that could be executed by a single SQL statement (i.e. one instead of trigger could do an insert into another view... and so on). The current behavior isn't quite as convenient in the simplest case, but it works correctly in the more complicated general case, where simply updating the last_insert_rowid value would be wrong. ---- _2005-Nov-08 22:51:56 by anonymous:_ {linebreak} I think that there's an argument to be made that last_insert_rowid() should return the last inserted row ID, even if a trigger inserted a bunch. It should simply return the last one entered. However, I agree with your analysis. Could there perhaps be a set_insert_id() function, or some such, that the trigger could use to tell last_insert_row_id() what to return? #cfe8bd 1235 active 2005 May anonymous Unknown Pending drh 4 3 inconsistent pragma handling The pragma user_version and schema_version are handled inconsistently in this respect : the result set returned contains a single column that has no name. all other pragmas return named columns. since some high-level languages complain about db fields with no names (most wrappers will gag at this), I suggest that a simulated "column_name" is also generated here, as with all other pragmas. #f2dcdc 1248 active 2005 May anonymous Unknown Pending 3 1 sqlite3_get_table returns garbage for BLOB data BLOB data returned by sqlite_get_table() is garbage when it contains data bytes holding '0' values. Code: Windows, 3.2.1, VC 6.0 char data[128]; for(int i = 0;i<128;i++) { data[i] = i+1; } sqlite3_exec(thedb,"CREATE TABLE test (b BLOB)",NULL,NULL,NULL); result = sqlite3_prepare(thedb,"INSERT INTO test (b) VALUES (?)",-1,&cstate,NULL); sqlite3_bind_blob(cstate,1,data,128,SQLITE_STATIC); // bind the data to the statement result = sqlite3_step(cstate); result = sqlite3_finalize(cstate); sqlite3_get_table(thedb,"SELECT * FROM test",result,rows,columns,NULL); this sqlite3_get_table() returns a properly layed out table. However, it looks like sqlite_get_table() is converting blob data to another type of data (string?) as it processes the command. The BLOB data it returns using this call is completely corrupted, due to processing '0' valued bytes as EOS characters. Instead it should be inferring BLOB data where appropriate, and returning a correct data block. As a workaround I've been using the prepare/step functions instead. However, the sqlite3_get_table() is a neccessity for many users of this library, as it allows a simple and elegant SQL query mechanism, and should be fixed ASAP to support BLOB data properly. _2005-Sep-27 01:44:39 by anonymous:_ {linebreak} sqlite3_get_table is considered legacy code, intended to make porting of sqlite2 applications (which never had to deal with BLOBs) easier. Its use in new applications is deprecated. If you need something like it that can handle BLOBs, you're best off writing a wrapper function for the prepare/step interface (you can use the sqlite3_get_table code as a template). #cfe8bd 1255 active 2005 May anonymous Pending 4 3 Decrease number of warnings with Microsoft Visual C++ Add this to sqliteInt.h to decrease the number of warnings produced by sqlite: #if defined(_MSC_VER) #pragma warning (disable: 4018) // signed/unsigned mismatch #pragma warning (disable: 4244) // conversion from 'unsigned __int64 ' to 'unsigned char ', possible loss of data #pragma warning (disable: 4761) // integral size mismatch in argument; conversion supplied #endif _2005-May-20 19:27:50 by drh:_ {linebreak} Is there no command-line option on microsoft to disable these warnings? ---- _2005-May-21 08:56:15 by anonymous:_ {linebreak} It's possible to lower the warning level, from say 3 to 2 using the command line. But this is not as selective and will remove more warnings than those #pragmas. #cfe8bd 1264 active 2005 May anonymous Unknown Pending 3 3 access() undefined on MSVC shell.c(1705) : warning C4013: 'access' undefined; assuming extern returning int Add this: #if defined(_WIN32) && defined(_MSC_VER) # include
.mode columns .headers on select 'Create two tables, with nasty column names.' as remark; create table t_a (c_a integer); create table t_b (c_a integer); select 'Create two views which each alias the column names of the above tables.' as remark; create view v_a as select c_a as pretty from t_a; create view v_b as select c_a as pretty from t_b; select 'Insert some data' as remark; insert into t_a values (1); insert into t_b values (2); select 'Notice that the views work fine by themselves.' as remark; select 'The column names are both as we asked.' as remark; select pretty from v_a; select pretty from v_b; select 'Notice that used in concert, with a join, the column name is now wrong.' as remark; select pretty from v_a union select pretty from v_b; select 'Aliasing the name of the column in the first half of the join is no help.' as remark; select pretty as pretty from v_a union select pretty from v_b; select 'Alias the name of the column in the second half of the join "fixes" the result.' as remark; select pretty from v_a union select pretty as pretty from v_b;_2005-Oct-25 03:05:27 by anonymous:_ {linebreak} same as ticket 1228 ---- _2005-Oct-26 07:59:17 by anonymous:_ {linebreak} see also #1327 #f2dcdc 1415 active 2005 Sep anonymous Unknown Pending drh 1 1 Querying for BLOB type fields How do I query for BLOB type fields? I tried 1: field LIKE 'abc' 2: field LIKE quote('abc') and 3: field LIKE X'616263' but nothing seems to return back the record that I am interested in. _2005-Sep-13 08:00:28 by anonymous:_ {linebreak} Using version 3.2.5, you might use the quote function to convert a blob into a string which you can filter using the like operator: select * from test where quote(text) like '%6263%'; is working and usable but may not work as expected because like '%26%' would find the same and this was not expected, isn't it? select * from test where like(quote(text),'%6263%'); doesn't work and select * from test where like(quote(text),'%6263%','%'); doesn't work either ---- _2005-Oct-04 05:44:08 by anonymous:_ {linebreak} This should really be taken to the mailing list, preferably with descriptions of how other DBMSs handle LIKE as applied to BLOB columns. #cfe8bd 1428 active 2005 Sep anonymous TclLib Pending 3 3 tclinstaller.tcl script problems The pkgIndex.tcl file generated by the tclinstaller.tcl script contains absolute pathnames to the TCL extension library. This causes problems if the extension subdirectory is moved. A more portable solution is shown below. A second problem is that the shared library does not have executable permission. This is a problem on HPUX operating systems. Adding the 0755 permission mode to the open command solves the problem for HPPA and does not cause problems for the other platforms. Here's a diff of the changes I made to address these two problems: cvs diff tclinstaller.tcl Index: tclinstaller.tcl =================================================================== RCS file: /sqlite/sqlite/tclinstaller.tcl,v retrieving revision 1.2 diff -r1.2 tclinstaller.tcl 17c17 < puts $fd "package ifneeded sqlite3 $VERSION \[list load $LIB sqlite3\]" --- > puts $fd "package ifneeded sqlite3 $VERSION \[list load \[file join \$dir $LIBNAME\]\]" 25c25,26 < set out [open $LIB w] --- > #Some platforms such as the HP requre that libraries have the executable bit set > set out [open $LIB w 0755] #cfe8bd 1445 active 2005 Sep anonymous Pending 3 3 Errors testing sqlite 3.2.6 (& v3.3.7) $ make test [...] conflict-6.0... Ok conflict-6.1... Ok conflict-6.2... Expected: [0 {7 6 9} 1 1] Got: [0 {7 6 9} 1 0] conflict-6.3... Expected: [0 {6 7 3 9} 1 1] Got: [0 {6 7 3 9} 1 0] conflict-6.4... Ok conflict-6.5... Ok conflict-6.6... Ok conflict-6.7... Expected: [0 {6 7 3 9} 1 1] Got: [0 {6 7 3 9} 1 0] conflict-6.8... Expected: [0 {7 6 9} 1 1] Got: [0 {7 6 9} 1 0] conflict-6.9... Expected: [0 {6 7 3 9} 1 1] Got: [0 {6 7 3 9} 1 0] conflict-6.10... Expected: [0 {7 6 9} 1 1] Got: [0 {7 6 9} 1 0] conflict-6.11... Expected: [0 {6 7 3 9} 1 1] Got: [0 {6 7 3 9} 1 0] conflict-6.12... Expected: [0 {6 7 3 9} 1 1] Got: [0 {6 7 3 9} 1 0] conflict-6.13... Expected: [0 {7 6 9} 1 1] Got: [0 {7 6 9} 1 0] conflict-6.14... Ok conflict-6.15... Ok conflict-6.16... Ok [...] date-3.12... Ok date-3.13... Ok date-3.14... Ok date-3.15... Ok date-3.16... Ok date-3.17... Ok /tmp/sqlite-3.2.6/.libs/lt-testfixture: invalid command name "clock" while executing "clock seconds" invoked from within "clock format [clock seconds] -format "%Y-%m-%d" -gmt 1" invoked from within "set now [clock format [clock seconds] -format "%Y-%m-%d" -gmt 1]" (file "./test/date.test" line 142) invoked from within "source $testfile" ("foreach" body line 4) invoked from within "foreach testfile [lsort -dictionary [glob $testdir/*.test]] { set tail [file tail $testfile] if {[lsearch -exact $EXCLUDE $tail]>=0} continue so..." (file "./test/quick.test" line 45) make: *** [test] Error 1 _2005-Sep-19 23:03:56 by drh:_ {linebreak} The test scripts do not (yet) work with Tcl 8.5. Use Tcl 8.4. ---- _2005-Sep-20 01:59:42 by anonymous:_ {linebreak} FYI, The conflict failures occur even when using tcl-8.4. The problem was reported on the mailing list: http://www.mail-archive.com/sqlite-users%40sqlite.org/msg10203.html Curiously, the failures correspond exactly to the test cases that were changed by the following patch: http://www.sqlite.org/cvstrac/filediff?f=sqlite/test/conflict.test&v1=1.24&v2=1.25 ---- _2006-Aug-31 23:49:40 by anonymous:_ {linebreak} building v337 on OSX 10.4.7 w/ TCL8.5 installed as Framework, 'make test' still fails w/: date-3.16... Ok date-3.17... Ok /usr/ports/sqlite-3.3.7/build/.libs/testfixture: invalid command name "clock" while executing "clock seconds" invoked from within "clock format [clock seconds] -format "%Y-%m-%d" -gmt 1" invoked from within "set now [clock format [clock seconds] -format "%Y-%m-%d" -gmt 1]" (file "../test/date.test" line 142) invoked from within "source $testfile" ("foreach" body line 4) invoked from within "foreach testfile [lsort -dictionary [glob $testdir/*.test]] { set tail [file tail $testfile] if {[lsearch -exact $EXCLUDE $tail]>=0} continue so..." (file "../test/quick.test" line 66) make: *** [test] Error 1 any resolution for this, other than revert to TCL 8.4? ---- _2006-Sep-01 01:26:37 by anonymous:_ {linebreak} SQLite under Cygwin fails all tests that involve integers larger than 32 bits. Sqlite produces the correct 64 bit values, but Tcl as distributed with Cygwin cannot grok 64 bit ints, so the comparisons fail. Would it be possible to change Sqlite's test harness to compare SQL results as strings rather than as integers? Then it would not matter if Tcl worked in 64 bit or not. ---- _2006-Sep-01 15:50:48 by drh:_ {linebreak} The test suite has been revised so that it now works with Tcl8.5. But, no, it is not practical to rewrite the tests to compare the results using strings instead of integers in order to work with the (broken) tcl implementation that comes with cygwin. ---- _2006-Sep-06 02:39:24 by anonymous:_ updating to latest cvs-checkout to get the aforementioned fix for: date-3.17... Ok /usr/ports/sqlite-3.3.7/build/.libs/testfixture: invalid command name "clock" while executing i can verify that _that_ is now ok: ... date-3.14... Ok date-3.15... Ok date-3.16... Ok date-3.17... Ok date-4.1... Expected: [2006-09-01] Got: [2006-09-06] date-5.1... Ok date-5.2... Ok date-5.3... Ok ... but now, 'make test' fails next @: delete-8.4... Ok delete-8.5... Ok delete-8.6... Ok delete-8.7... Ok /usr/ports/sqlite-cvs/build/.libs/testfixture: error deleting "test.db": not owner while executing "file delete -force test.db" (file "../test/tester.tcl" line 62) invoked from within "source $testdir/tester.tcl" (file "../test/delete2.test" line 36) invoked from within "source $testfile" ("foreach" body line 4) invoked from within "foreach testfile [lsort -dictionary [glob $testdir/*.test]] { set tail [file tail $testfile] if {[lsearch -exact $EXCLUDE $tail]>=0} continue so..." (file "../test/quick.test" line 66) make: *** [test] Error 1 ---- _2006-Sep-06 11:11:19 by drh:_ {linebreak} Run the build starting from an empty directory as a non-root user. ---- _2006-Sep-06 13:27:18 by anonymous:_ {linebreak} per INSTALL instructions, i did: cvs -d :pserver:anonymous@www.sqlite.org:/sqlite checkout -d sqlite-cvs sqlite cd /usr/ports/sqlite-cvs mkdir build cd build ../configure \ ... make chown -R myuser:wheel /usr/ports/sqlite-cvs sudo -u myuser make test and, as reported, the error was the result. ---- _2006-Sep-30 21:43:45 by anonymous:_ {linebreak} bump. anyone? ---- _2006-Sep-30 22:19:24 by anonymous:_ {linebreak} If you don't happen to be testing on Linux/gcc or Windows/VC++ I find that the Tcl test results have more than a few failures. It is not always easy to discern which failures are due to some odd quirk of Tcl or whether it is a legitimate SQLite issue on a given platform. Be prepared to change test scripts and tinker with the code. #f2dcdc 1447 active 2005 Sep anonymous BTree Pending 1 1 Abnormal program termination in src/btree.c line 1339 In some circumstances (after having used wxgrid ..) a call to sqlite gives a strange : Assertion Failed: pCur->idx>=0 && pCur->idx < pCur->pPage->nCell, file src/btree line 1339 abnormal program termination there seems to be non way of making a trace back ... any idea? Thanx Doriaqn Tessore _2005-Sep-23 14:32:27 by drh:_ {linebreak} Not much to go on. What version of SQLite is being used? ("SQLite 2" is kind of vague.) #cfe8bd 1451 active 2005 Sep anonymous Shell Pending 4 3 .mode insert does not output BLOBs in an usable way
sqlite> CREATE TABLE a(b); sqlite> INSERT INTO a VALUES (X'41424300500051'); sqlite> .dump BEGIN TRANSACTION; CREATE TABLE a(b); INSERT INTO "a" VALUES(X'41424300500051'); COMMIT; sqlite> .mode insert sqlite> SELECT * FROM a; INSERT INTO table VALUES('ABC');It would be nice for ".mode insert" to print a command that would actually re-create the same data, the same as ".dump" (the obvious difference is that .dump can't filter data in any way, it just dumps it all) or, at least, it would be very nice if the already existing function that "prints binary data as X'-encoded-string" were reachable from SQL, so that one could use something like:
SELECT xencode(b) FROM a;and obtain
X'41424300500051'_2005-Sep-25 17:03:40 by anonymous:_ {linebreak} I am not the original ticket poster, but I noticed that this feature request is related to {link: http://lists.gnu.org/archive/html/monotone-devel/2005-09/msg00294.html Monotone} migrating away from Base64 encoding to using straight Sqlite blobs. ---- _2005-Sep-26 01:49:33 by drh:_ {linebreak} The built-in quote() function converts BLOBs into ascii BLOB literals. Will it not server for the requested xencode() function? SELECT quote(b) FROM a ---- _2005-Sep-26 08:57:47 by anonymous:_ {linebreak} Yes, I guess it can perfectly do. What about ".mode insert" output? Is it supposed to print raw data? #e8e8bd 1455 active 2005 Sep anonymous Shell Pending 3 2 .import error: comma inside a string is read as field separator In Sqlite version 3, when you need to import data into a table you use .import. (You cannot do it with COPY). Well, if you need to import data in 'csv' format, and if there is a string in the input data that contains a
#ifndef SQLITE_OMIT_MEMORYDB /* ** Clear a PgHistory block */ static void clearHistory(PgHistory *pHist){ sqliteFree(pHist->pOrig); sqliteFree(pHist->pStmt); pHist->pOrig = 0; pHist->pStmt = 0; } #else #define clearHistory(x) #endif#f2dcdc 1775 active 2006 Apr anonymous Unknown Pending 1 1 strftime() not working in Windows Mobile 2005 The strftime() is not working in windows mobile 2005 pocket pc. I am using the beta version of visual studio 2005. _2006-Apr-17 13:10:20 by anonymous:_ {linebreak} Is it a compile/link problem or a runtime problem? ---- _2006-Apr-18 07:26:08 by anonymous:_ {linebreak} It's a runtime problem. I am not able to get the dates formated using strftime(). datetime(), date() and time() are working properly. #f2dcdc 1782 active 2006 Apr anonymous Unknown Pending 1 1 journal file exclusion I have a long running process which opens a connection to a sqlite3 database, let's called it a.rdb. The connection is never closed during the life time of the process, and it's set to auto commit mode. Now, if I delete a.rdb, and re-create it again(the long running process is still holding a fd to the deleted file at this point), the long running process is still creating a.rdb-journal from time to time. To make it worse, if at that time, I use the sqlite3 command line to modify the database when the file a.rdb-journal exists, the file in a.rdb-journal is also played back into the new a.rdb file, which doesn't seem to be the correct behavior. Is it the intended design? Thanks, John #e8e8bd 1783 active 2006 Apr anonymous Unknown Pending 3 2 insert times increase with growing table size (when indexed) The time needed to insert (or update) entries in a table with an index on one of the fields increases with the size of the table. For large databases inserts become very slow (which I suppose is likely the problem in ticket #1547). sqlite2 does not have this scaling problem on inserts. (Some of our queries do not scale on sqlite2 however, making its use also impossible.) ----- example code ----- package require dbi package require dbi_sqlite3 dbi_sqlite3 db db create /tmp/test.db db open /tmp/test.db db exec {create table "region" ( "id" integer not null primary key, "start" integer, "end" integer )} db exec {create index "region_index" on "region"("start")} set num 1 for {set j 1} {$j < 20} {incr j} { puts [lindex [time { db begin for {set i 1} {$i < 100000} {incr i} { set s [expr {round(rand()*1000000)}] set e [expr {round(rand()*1000000)}] db exec { insert into "region"("id","start","end") values(?,?,?) } $num $s $e incr num } db commit }] 0] } ----- timings ----- 5712186 6621934 9492997 13234978 14881322 19119044 25296162 26670866 35378986 35877042 44383517 54576510 53317621 63516664 76587973 73791188 88460462 101650099 #f2dcdc 1941 active 2006 Aug anonymous Pending 1 1 Unrevolved _sqlite3ExprCodeAndCache with SQLITE_OMIT_TRIGGER If =SQLITE_OMIT_TRIGGER= is set, linker complains about an unresolved =_sqlite3ExprCodeAndCache= symbol. =sqlite3ExprCodeAndCache= is defined in =expr.c= and wrapped with =#ifndef SQLITE_OMIT_TRIGGER=. However, references in insert.c, line 536 update.c, line 348 and 362 are not wrapped with #ifndef =SQLITE_OMIT_TRIGGER=. I followed the suggestion quoted below (posted earlier to this list) without avail. Is it safe (or even required?) to change sqliteInt.h to #ifndef SQLITE_OMIT_TRIGGER void sqlite3ExprCodeAndCache(Parse*, Expr*); #else # define sqlite3ExprCodeAndCache(A,B) #endif In the mailing list, DRH argued that the above change will probably fail and suggested that a safer fix would be to remove the #ifndef SQLITE_OMIT_TRIGGER from around the sqlite3ExprCodeAndCache function. _2006-Oct-12 17:35:32 by anonymous:_ {linebreak} The problem is still present in 3.3.8. Removing the #ifndef SQLITE_OMIT_TRIGGER from around the sqlite3ExprCodeAndCache function seems to fix it. Could you commit this? #e8e8bd 1946 new 2006 Aug anonymous Unknown New 2 2 .read file fails on blob fields with end-of-file char I've a table with a blob fields. I put there binary data that contains 0x1a (end of file) symbol. It's alright until i try to dump table to file and then trying to import that file. sqlite3 my_db {linebreak}>.output my_file {linebreak}>.dump table_with_blob {linebreak}>.exit {linebreak}del my_db sqlite3 my_db {linebreak}>.read my_file Fails with "Incomplete SQL: ..." SQL break before 0x1a char I'm on windows. Possibly solving with opening file as binary file. Sorry for my English #cfe8bd 1947 active 2006 Aug anonymous Shell Pending 3 3 ".mode insert" works bad with BLOBs .mode insert displays BLOBs as strings, which isn't very good for embedded NULs. Having output more like the one from .dump would be better, IMO. sqlite> select * from t; INSERT INTO table VALUES(''); sqlite> .dump BEGIN TRANSACTION; CREATE TABLE t(f BLOB); INSERT INTO "t" VALUES(X'0041'); COMMIT; #cfe8bd 1948 active 2006 Aug anonymous Shell Pending 2 3 Double quotes are not escaped in csv mode If text is exported using "csv" mode, double quotes in strings are not escaped. Generally double-quotes in a quoted field in CSV should be escaped by repeate. I.e., 'This is a "test".' could be about as "This is a ""test""." This doesn't appear to be the behavior SQLite uses, so, in the meantime, I'll have to export my data using another method and then transform that data into CSV for my import script. #cfe8bd 2093 active 2006 Dec anonymous Pending 2 3 sqlite3_vtab_cursor doesn't have errMsg The sqlite3_vtab_cursor structure doesn't have a zErrMsg pointer. Only the containing vtable does. This means that operations on cursor objects that have an error have to set the error on the vtable not the cursor. Unfortunately this means that there are race conditions since two different cursors on the same vtable could have errors at the same time. If the cursors are in different threads then a crash or worse can happen. #cfe8bd 2096 active 2006 Dec anonymous Pending 3 3 ATTACH DATABASE returns SQLITE_ERROR when database is locked From an email sent to DRH: I am working on a problem surrounding the inability to ATTACH to a database file. The error text being returned is "database is locked", which should be SQLITE_BUSY, however, the error code being returned by sqlite3_exec is SQLITE_ERROR. Is sqlite3_exec wrong in returning SQLITE_ERROR rather than SQLITE_BUSY? I have some nagging feeling that I determined or read that the attachFunc function does not return a truly-relevant status code, but I can't see why offhand nor can I find any evidence to support that theory. If sqlite3_exec is doing the right thing, however, then the question becomes one of identifying when to retry the ATTACH statement; we're currently keying off SQLITE_BUSY or SQLITE_LOCKED, as appropriate, and I'd rather not be trying to trap errors based on error text. #f2dcdc 2100 active 2006 Dec anonymous Pending 1 1 Fixes for SQL lower() and upper() As acknowledged in the documentation, the SQL lower() and upper() functions might not work correctly on UTF-8 characters. This bug might show if a country specific locale is used instead of the standard C locale. Under certain circumstances, SQL lower() or upper() can even corrupt the UTF-8 string into invalid UTF-8 if the tolower() and toupper() C functions convert character values starting from 0x80. Below I propose implementations of lowerFunc() and upperFunc() which work correctly with UTF-8 characters, regardless of the implementation of the C library tolower() and toupper() functions. If these C functions are implemented to support high ASCII or even Unicode case conversion, the new SQL lower() and upper() will support them as well. The proposed C implementation applies a technique also found in sqlite3VdbeMemTranslate() in utf.c and makes use of some macros contained in that unit. To avoid duplicating existing code, it could make sense to move lowerFunc() and lowerFunc() to utf.c, just as it has been done with sqlite3utf16Substr(). Finally, here is the code: /* ** Implementation of the upper() and lower() SQL functions. */ static void upperFunc(sqlite3_context *context, int argc, sqlite3_value **argv){ const unsigned char *zIn, *zInTerm; unsigned char *z, *zOut; int c, l; if( argc<1 || SQLITE_NULL==sqlite3_value_type(argv[0]) ) return; zIn = sqlite3_value_text(argv[0]); if( zIn==0 ) return; l = sqlite3_value_bytes(argv[0]); zInTerm = &zIn[l]; /* When converting case, the maximum growth results from ** translating a 1-byte UTF-8 character to a 4-byte UTF-8 character. */ zOut = sqliteMalloc( l * 4 ); z = zOut; while( zIn
#ifdef SQLITE_UNICODE_UPPERLOWERFUNCS #define WCHAR_T_SIZE sizeof(wchar_t) #if (WCHAR_T_SIZE == 2) #define MAXUPPERLOWERCHAR_AVAIL 0x0000ffff #else // (WCHAR_T_SIZE == 4) #define MAXUPPERLOWERCHAR_AVAIL 0x7fffffff #endif // (WCHAR_T_SIZE == 2) #define TOLOWERSQLFUNC(c) unicode_tolower #define TOUPPERSQLFUNC(c) unicode_toupper int unicode_tolower(const int c) { wchar_t buff [2]; if (c > MAXUPPERLOWERCHAR_AVAIL) return c; buff[0] = (wchar_t) c; buff[1] = 0; _wcslwr(buff); return (int) buff[0]; } int unicode_toupper(const int c) { wchar_t buff [2]; if (c > MAXUPPERLOWERCHAR_AVAIL) return c; buff[0] = (wchar_t) c; buff[1] = 0; _wcsupr(buff); return (int) buff[0]; } #else // SQLITE_UNICODE_UPPERLOWERFUNCS #define TOLOWERSQLFUNC(c) (c > 255 ? c : tolower(c)) #define TOUPPERSQLFUNC(c) (c > 255 ? c : toupper(c)) #endif // SQLITE_UNICODE_UPPERLOWERFUNCS /* ** Implementation of the upper() and lower() SQL functions. */ static void upperFunc(sqlite3_context *context, int argc, sqlite3_value **argv){ const unsigned char *zIn, *zInTerm; unsigned char *z, *zOut; int c, l; if( argc<1 || SQLITE_NULL==sqlite3_value_type(argv[0]) ) return; zIn = sqlite3_value_text(argv[0]); if( zIn==0 ) return; l = sqlite3_value_bytes(argv[0]); zInTerm = &zIn[l]; /* When converting case, the maximum growth results from ** translating a 1-byte UTF-8 character to a 4-byte UTF-8 character. */ zOut = sqliteMalloc( l * 4 ); z = zOut; while( zIn