bgcolor # Status Created By Subsys Due Date SCR Assigned Svr Pri Title _Description _Remarks
#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
#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:
/* 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.
#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 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.
#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 =?, =? ... WHERE rowid=?'. We cannot update to sqlite v3.1.x because of this. ---- _2005-Mar-15 08:36:31 by drh:_ {linebreak} There are lots of people telling me the current behavior is wrong. But nobody has yet suggested what the correct behavior should be. Until I know what the correct behavior should be, there is little I can do to fix the problem. Rather than just telling me the current behavior is wrong, please offer an explanation of what the correct behavior should be. What do Oracle and PostgreSQL do in the same situation? ---- _2005-Mar-16 09:04:30 by anonymous:_ {linebreak} Dr. Hipp, very sorry for the confusion I've caused! My comments were actually targeted at bug #1141! I've included the same remarks there. As for this bug: I can only tell you how SQL Server reacts: select * from a inner join b Line 1: Incorrect syntax near 'b'. select * from a inner join b on a.id = b.id | id | x | id | y | ------------------- | 1 | 1 | 1 | 2 | So SQL Server behaves like sqlite in returning column names, although it doesn't accept the syntax 'select * from a inner join b' I guess this behavior is normal and it's the programmer's resposability to use aliases in sql statement. Just my personal oppinion, though.
#f2dcdc 1158 active 2005 Mar anonymous VDBE Pending 1 1 core dump on solaris, some sqlite functions core dumped on solaris. I tracked this back to a byte alignment problem in Vdbeint.h. I added the __attribute__ ((__aligned__(16))) to the zshort character string. /* ** 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)
#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.
#e8e8bd 1278 active 2005 Jun anonymous Pending 1 2 sqlite3_finalize doesn't clear previous error code or message A call to sqlite3_finalize(), after an error during sqlite3_prepare() of another statement, returns the correct result SQLITE_OK, but does not reset the error code or error message returned by sqlite3_errcode() and sqlite3_errmsg(). The error reporting functions still return the error code and message associated with the error that occurred during the previous prepare. The attached code demonstrates the problem. One statement is prepared successfully. Then an second statement is prepared. This one fails and returns an error result. The correct error code and message are retrieved using the error reporting API functions. Next, the first statement is finalized, which returns SQLITE_OK. Calling the sqlite_errcode() function at this point still returns the error code from the previous error. I believe the error code and message should be cleared by the successful call to the sqlite3_finalize() API function.
#f2dcdc 1305 active 2005 Jun anonymous TclLib Pending 1 1 Tcl installs pkgIndex with wrong path Correct script below: # This script attempts to install SQLite3 so that it can be used # by TCL. Invoke this script with single argument which is the # version number of SQLite. Example: # # tclsh tclinstaller.tcl 3.0 # set VERSION [lindex $argv 0] set LIBFILE .libs/libtclsqlite3[info sharedlibextension] if { ![info exists env(DESTDIR)] } { set DESTDIR "" } else { set DESTDIR $env(DESTDIR) } set LIBDIR [lindex $auto_path 0] set LIBNAME [file tail $LIBFILE] set LIB $LIBDIR/sqlite3/$LIBNAME file delete -force $DESTDIR$LIBDIR/sqlite3 file mkdir $DESTDIR$LIBDIR/sqlite3 set fd [open $DESTDIR$LIBDIR/sqlite3/pkgIndex.tcl w] puts $fd "package ifneeded sqlite3 $VERSION \[list load $LIB sqlite3\]" close $fd # We cannot use [file copy] because that will just make a copy of # a symbolic link. We have to open and copy the file for ourselves. # set in [open $LIBFILE] fconfigure $in -translation binary set out [open $DESTDIR$LIB w] fconfigure $out -translation binary puts -nonewline $out [read $in] close $in close $out
#f2dcdc 1312 active 2005 Jun anonymous Shell Pending 1 1 CSV file import / export is all wrong! Four problems: Importing a proper CSV file (which delimits strings within double-quotes) is impossible. The '.import' command just treats the double-quotes as ordinary text characters. So (1) Error "expected 2 columns of data but found 3" can occur if one of the strings contains a comma (2) the delimiting double-quotes are NOT stripped off before inserting the data into the table as it should (3) it doesn't understand the standard convention that to represent a double-quote character within a double-quoted string you use TWO double-quote (eg. "3.5"" Floppy Drive") (4) outputting data in CSV mode also doesn't use this standard convention -------------------------------------------- _Product.csv contains: "A001","McVities" "B001","Heinz" "C001","Callard,Bowser" sqlite> .mode csv sqlite> .import _Product.csv Product _Product.csv line 3: expected 2 columns of data but found 3 _2005-Aug-12 23:13:54 by anonymous:_ {linebreak} Commas are also not accounted for when using the sqlite3_mprintf() function(s). I'm guessing the %q flag should also escape these.
#f2dcdc 1323 active 2005 Jul anonymous Pending 1 1 misuse-4.4...gmake: *** [test] Segmentation Fault (core dumped) misuse-4.4...gmake: *** [test] Segmentation Fault (core dumped) on both solaris 8 and 9 build env export CPPFLAGS="-I/tps/include" export LDFLAGS="-L/tps/lib -R/tps/lib" export PKG_CONFIG_PATH=/tps/lib/pkgconfig CC=/tps/bin/gcc CXX=/tps/bin/g++ LD_LIBRARY_PATH=/tps/lib:/tps/lib/sparcv9:/lib:/usr/lib:/usr/local/lib:\ /usr/ccs/lib:/usr/dt/lib:/usr/ucblib:/usr/openwin/lib PATH=/tps/bin:/tps/java/bin:/dsw/source/bin:/dsw/depot-5.13/bin:\ /usr/ccs/bin:/usr/bin:/usr/openwin/bin:/bin:/usr/local/bin:/sbin:\ /usr/sbin:/usr/ucb:/etc:.:/sfoc/bin:/usr/dt/bin:\ /dsw/source/harvest/bin:/usr/afsws/bin:/dsw/pgp-2.6.2s/bin export CC CXX LD_LIBRARY_PATH PATH where /tps is my version of /usr/local where I put all the configuration controlled open source and licensed s/w for my network. [525]$ ../configure --prefix=/dsw/sqlite-3.2.2 --with-tcl=/tps/lib gmake gmake test then got error gcc -v Reading specs from /dsw/gcc-3.4.0/lib/gcc/sparc-sun-solaris2.9/3.4.0/specs Configured with: ../configure --prefix=/dsw/gcc-3.4.0 --disable-nls --enable-languages=c,c++,f77,objc --disable-libgcj --srcdir=/export/build/gcc-3.4.0 --with-ld=/usr/ccs/bin/ld Thread model: posix gcc version 3.4.0 using ActiveTcl8.4.5.0
#f2dcdc 1342 active 2005 Jul anonymous Pending 1 1 sqlite 3.2.2 will not load on Suse Linux 9.3 when trying to load the sqlite 3.2.2 .so lib with tcl I get this problem: couldn't load file "/usr/lib/sqlite3/tclsqlite-3.2.2.so": /usr/lib/sqlite3/tclsqlite-3.2.2.so: undefined symbol: sqlite3_version Sqlite 3.2.1 does not give an error, with the same script
#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.
#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
#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.
#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.)
#e8e8bd 1461 active 2005 Sep anonymous Unknown Pending drh 1 2 3.2.7 DLL can not deal File paths with international characters My platform:winxpsp2 chinese version,my database file under a path with chinese charater,with the dll(3.2.1) sqliteexplore works fine,if I change the dll to 3.2.7,it show sqlite error 14:can't open the file,and then I change the path fully english,it can work fine again,so I think mybe it relate to Check-in:2656 _2005-Sep-28 15:31:34 by anonymous:_ {linebreak} and more,the source version has no such problem. ---- _2005-Sep-28 16:09:04 by anonymous:_ {linebreak} I notice in os_win.c, function "sqlite3OsFileExists" use GetFileAttributesA and GetFileAttributesW,but I tried GetFileAttributes works ok.
#f2dcdc 1465 active 2005 Oct anonymous CodeGen Pending 1 1 fdatasync not available and not yet fixed fdatasync is still there ... i downloaded the current configure files which should check for it. do i have to use something else too? ./libtool --mode=link cc -g -DOS_UNIX=1 -DHAVE_USLEEP=1 -I. -I./src -DNDEBUG -DTHREADSAFE=0 -DSQLITE_OMIT_CURSOR -DHAVE_READLINE=0 \ -o sqlite3 ./src/shell.c libsqlite3.la -lcurses cc -g -DOS_UNIX=1 -DHAVE_USLEEP=1 -I. -I./src -DNDEBUG -DTHREADSAFE=0 -DSQLITE_OMIT_CURSOR -DHAVE_READLINE=0 -o .libs/sqlite3 ./src/shell.c ./.libs/libsqlite3.so -lcurses "./src/shell.c", line 355: warning: argument #1 is incompatible with prototype: prototype: pointer to const unsigned char : "./src/shell.c", line 84 argument : pointer to const char "./src/shell.c", line 523: warning: argument #1 is incompatible with prototype: prototype: pointer to const unsigned char : "./src/shell.c", line 84 argument : pointer to char "./src/shell.c", line 694: warning: argument #2 is incompatible with prototype: prototype: pointer to const char : "./src/shell.c", line 583 argument : pointer to const unsigned char Undefined first referenced symbol in file fdatasync ./.libs/libsqlite3.so ld: fatal: Symbol referencing errors. No output written to .libs/sqlite3 make: *** [sqlite3] Error 1 _2005-Oct-04 21:42:26 by drh:_ {linebreak} Clear out your build directory (or start a new one) and rerun configure from scratch. Save the output of configure. Then rerun make. If you still have a problem, attach the output of configure to this ticket.
#cfe8bd 1487 active 2005 Oct anonymous BTree Pending 1 3 Corrupt database causes indefinite loop in sqlite3_step() I had a database become corrupt (no idea why, 57 other databases of similiar information are fine). When attempting to work with the database and execute a SELECT query the application froze in an endless loop inside sqlite3_step(). Upon further investigation (which is when I found the db was corrupt) it seems to be stuck inside the btree code (as reported by Sample). I tested the same query and alternates from sqlite3 CLI and got the same results. The exact query causes an infinite loop. Leaving off part of the WHERE statement (and making it broader) or removing one of the reporting columns simply causes a corruption error. I have the original database as-is and the SQL query that can be run to cause the problem. OS: Mac OS X 10.4.2
#f2dcdc 1488 active 2005 Oct anonymous Pending 1 1 Collate Reverse does not exists? when I execute below SQL{linebreak} CREATE Unique INDEX index10 On Test2 ({linebreak} F1 Collate BINARY ,{linebreak} F2 Collate REVERSE DESC){linebreak} there is a error message:{linebreak} no such collation sequence: REVERSE {linebreak} but the latest document said that binary ,nocase , reverse is common collate function. what is wrong with my sql?
#e8e8bd 1494 active 2005 Oct anonymous Unknown Pending 1 2 intermittent null reference exception in sqlite3_open Doing a c# (.net 1.1 / visual studio 2003 / winxp) project with a (so far) small db (5 tables, max 1000 lines per table). About 10% of startups will fail with a NullReferenceException in sqlite3_open(). This is on a dell optiplex, win xp pro sp 2, 1G ram, P4@2.6GHz, using c# in visual studio 2003 version 7.1.3088 with .net framework 1.1.4322 sp 1. Methods in Sqlite3.dll are imported with dllimport like this: [DllImport("Sqlite3.Dll", EntryPoint="sqlite3_open")] public static extern int sqlite3_open( string filename, out IntPtr dbhandle ); This would seem to be an error on my part or .net, except that this is the first sqlite call in the entire application and it fails only sometimes and the file name parameter is hard-coded. 90% of the time the application works fine. Of course, it could be .net messing things up. Anyway I can't find this on the web, sorry for taking up your time if it's not a bug. And no, I haven't got a program specifically for testing this thing. Below is the stack trace for the System.NullReferenceException, the top line is the call into sqlite3.dll. The filename parameter is not null, it is "C:\DOCUMENTS AND SETTINGS\MARTIN.WANGEL\MY DOCUMENTS\VISUAL STUDIO PROJECTS\SOLUTION\BIN\DEBUG\SOLUTIONDATA.SQLITE" and it is thoroughly checked for null and for emptiness. at Solution.mwDatabaseSQLite.sqlite3_open(String filename, IntPtr& dbhandle)\r\n at Solution.mwDatabaseSQLite.Open() in c:\\documents and settings\\martin\\my documents\\visual studio projects\\solution\\mwdatabaseclasses.cs:line 149\r\n at Solution.DBUtils.VerifyDatabase(Type t, String connstr, dbtype dbtyp) in c:\\documents and settings\\martin\\my documents\\visual studio projects\\solution\\dbutils.cs:line 287\r\n at Solution.FrmMain..ctor() in c:\\documents and settings\\martin\\my documents\\visual studio projects\\solution\\form1.cs:line 1499\r\n at Solution.FrmMain.Main() in c:\\documents and settings\\martin\\my documents\\visual studio projects\\solution\\form1.cs:line 1285 /Martin _2005-Oct-27 21:12:22 by anonymous:_ {linebreak} Maybe you should look at "Wiki", "SQLite wrappers", ".NET Framework" for an other way to do this. ---- _2005-Oct-28 00:39:27 by anonymous:_ {linebreak} the calling conventions of standard built DLL are __cdecl, not __stdcall (or WINAPI)... check if .NET Framework is able to call __cdecl import functions... in my mind it will call only STDCALL routines (which is the default on WIN32 API)
#cfe8bd 1504 active 2005 Nov anonymous Pager Pending 1 3 Multithreaded DB lock not released using Begin/Commit between threads When using transaction-based insertion of rows in v3.2.7 in a multi-threaded environment, one thread appears not to release the database lock for any competing threads to be able to issue an "INSERT" statement (the thread issuing the "COMMIT" apparently). This problem does not appear in v2.8.16. The problem also appears in Linux (RH-9) as well. I'm attaching "testsqlite.c", a test application (compiled in a WIN32 environment) that will duplicate the issue (Define SQLITE_2_8_16 or SQLITE_3_2_7 depending on the version of SQLite library to test against). Direct any questions to Erik -> lonepenguin@hotmail.com Thank You.
#cfe8bd 1508 active 2005 Nov anonymous BTree Pending 1 3 sqlite 2.8.16 crashes on 64-bit / strict memory alignment archs A few months ago, sqlite3 was fixed on 64-bit / strict memory alignment architectures. Would it be possible for those fixes to be backported to the version_2 code? I have an OpenBSD/sparc64 machine which I can provide ssh access to (as I did before to drh). I know sqlite2 is mostly unsupported, but as php5 uses sqlite2, it would be nice to have these fixes backported. _2006-Jan-05 02:28:23 by anonymous:_ {linebreak} with the attached patch, sqlite 2.8.17 passes all the regressions tests on openbsd/amd64 and openbsd/sparc64.
#cfe8bd 1522 active 2005 Nov anonymous Unknown Pending 1 3 Make test fails in manydb 1.82-3.299 mac os x 10.4.3 ppc OS: Mac OS X 10.4.3 ppc Compiler: powerpc-apple-darwin8-gcc-4.0.1 (GCC) 4.0.1 (Apple Computer, Inc. build 5247) While running "make test" from a cvs checkout on Sun Nov 13 19:01:54 PST 2005, I get these errors: 653 errors out of 23390 tests Failures on these tests: manydb-1.82 manydb-1.83 manydb-1.84 ................. manydb-3.296 manydb-3.297 manydb-3.298 manydb-3.299 _2005-Nov-15 01:13:13 by drh:_ {linebreak} These failures likely result from running out of file descriptors. The manydb tests need about 1000 file descriptors. Linux provides this many (on most distributions). But perhaps Mac OS X does not. Does anybody know?
#f2dcdc 1541 active 2005 Nov anonymous Unknown Pending 1 1 Ticket #924 not fixed in the 2.8 branch Ticket 924: http://www.sqlite.org/cvstrac/tktview?tn=924 This problem seams to have been fixed on the 3.2 branch but is still in the 2.8.16
#f2dcdc 1546 active 2005 Nov anonymous Pending 1 1 Creating unique index on non-unique column leads to corr. on SQLite2 Like ticket #1115, SQLite version 2 suffer too from the bug where : BEGIN; CREATE TABLE t1(a); INSERT INTO t1 VALUES(1); INSERT INTO t1 VALUES(1); CREATE UNIQUE INDEX i1 ON t1(a); COMMIT; PRAGMA integrity_check fails. When "CREATE UNIQUE INDEX" fails within a transaction, (and within a transaction only), the index is still created.
#f2dcdc 1622 active 2006 Jan danielk1977 Pending 1 1 Compiling with OMIT_PRAGMA causes an error in the test suite Compiling with OMIT_PRAGMA causes an error in the test suite. The error is a Tcl level error thrown by a [db eval] command when it encounters the unknown SQL keyword "PRAGMA".
#cfe8bd 1735 active 2006 Mar anonymous Unknown Pending 1 3 Encoding problem I use latin2 (iso-8859-2) encoding in my system. When operating on sqlite 3 I can insert data that contains national characters into a database (for example using sqlite3 console) and then when I select them back, I am given the proper result. But when I use sqlite driver from Qt4, which uses sqlite3_column_text16() to fetch data from the database, I don't get the expected result (meaning the conversion to UTF-16 probably messed things up). Now the problem can be in one of two places -- either sqlite3 console application doesn't use a proper conversion to convert from my locale encoding into its internal encoding or the database internal mechanisms mess some things up. In short: sqlite3(somelatin2string) ==> SQLITE DMBS ==> sqlite3_column_text16() ==> garbage != somelatin2string At first I thought this was Qt problem as data stored through sqlite console and retrieved from it was correct and data stored by Qt and retrieved by Qt was also correct whereas data stored by Qt and retrieved by sqlite3 console or stored by the console and retrieved by Qt was not correct. I contacted Qt support guys @ trolltech and talked about it and it looks like Qt side if fine -- it expects a UTF-16 encoded data (because it uses the function mentioned earlier) and it converts from UTF-16 to whatever encoding it needs (and vice versa). So the error is probably somewhere in the line between the console and the database itself or in the database internally. It could be that sqlite3 expects UTF-8 (or UTF-16) encoded data on input but is given ISO-8859-2 data (entered manually by me at the console). _2006-Mar-27 16:36:26 by anonymous:_ {linebreak} The console app doesn't convert from your local code page to UTF-8 (or UTF-16). ---- _2006-Mar-27 22:45:21 by anonymous:_ {linebreak} It probably should, in the documentation of sqlite a suggested method of converting databases between versions 2 and 3 is: sqlite OLD.DB .dump | sqlite3 NEW.DB Now =sqlite= outputs the data in "local" format and if =sqlite3= doesn't encode it properly, such a conversion will be invalid because the incoming data won't be utf encoded. A solution could be to do: sqlite OLD.DB .dump | iconv -f -t UTF-8 | sqlite3 NEW.DB But it is the console which should be responsible for the conversion. Also because otherwise using =sqlite3= console on a non-utf system with a perfectly well UTF-8 encoded database will result in improper output too.
#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
#f2dcdc 1791 active 2006 May anonymous Unknown Pending 1 1 Native threads support for BeOS BeOS ports lacks native thread support. BeOS has very powerful but lightweight threading system, being throughout multithreaded, but it differs from posix-thread ideology, thus our pthreads implementation atm looks more like flacky workaround. Ideally will be to have separate implementation for thread-support, like for Win16/32 versions. At the moment this problem caused bustage of BeOS Mozilla port, https://bugzilla.mozilla.org/show_bug.cgi?id=330340 nearest workaround might be pthreads usage, inspite its flackyness, but it also causes mess for Mozilla build/configure system, because for other parts in Mozilla we use nspr-threads, which, for BeOS, use native version _2006-Oct-27 05:48:51 by anonymous:_ {linebreak} BeOS locking extensions (using native bthreads) have been written and are included in the SQLite3 built into Mozilla Firefox. Is there some process wherein these changes might be incorporated into the SQLite tree? ---- _2006-Oct-27 12:48:11 by anonymous:_ {linebreak} Follow the example of OS/2 and propose a patch against the latest SQLite CVS that has proper #ifdef's around BeOS code so it won't break other platforms. Since you're probably the only one interested in this patch, you'll have to do the diffing/merging/testing work yourself. ---- _2006-Nov-07 03:55:36 by anonymous:_ {linebreak} Thanks for the advice. We've completed updates to code so it works with the sqlite 3.3.8 patches proposed for Firefox. Current implementation has a parallel os-specific file (os_beos.c). However, with the latest round of locking enhancements to os_unix.c, we're now wondering if it makes more sense to simply enhance this file to support BeOS locking. (yes, we. surprisingly, there is more than one BeOS user left on the planet.) :)
#f2dcdc 1797 active 2006 May anonymous TclLib Pending drh 1 1 COPY command doesn't work in tclsqlite 3.3.5 The COPY command doesn't seem to work in the tcl sqlite lib. This same script and datafile works in version 3.2.7. load ./lib/libtclsqlite[info sharedlibextension] sqlite MEMORY_DB :memory: MEMORY_DB onecolumn "PRAGMA empty_result_callbacks=1" puts [MEMORY_DB version] MEMORY_DB eval "create table xyz (col1,col2)" MEMORY_DB copy ignore win_pol /home/centadm/win_pol4.csv \t MEMORY_DB eval "select * from xyz" sqlite_array { puts "Here in the callback" foreach sqlite_value $sqlite_array(*) { puts "$sqlite_value $sqlite_array($sqlite_value)" } } The data file win_pol4.csv consists of two columns, tab seperated. DATA1 DATA2 And the output: -bash-3.00$ tclsh test_sqlite.tcl 3.3.5 while executing "MEMORY_DB copy ignore win_pol /home/centadm/win_pol4.csv \t" (file "test_sqlite.tcl" line 5) -bash-3.00$ pwd /home/centadm -bash-3.00$ ls -l /home/centadm/win_pol4.csv -rw-r--r-- 1 centadm centadm 12 May 5 14:21 /home/centadm/win_pol4.csv -bash-3.00$ more /home/centadm/win_pol4.csv DATA1 DATA2 A TCL Error is returned from the copy command, no message tho. I have used catch to capture the command and verified that there is no data going into the table. Also, PRAGMA empty_result_callbacks=1 still doesn't seem to work in the tcllib. If you catch the COPY command above, you still never see the "Here in the callback" message. _2006-May-05 17:57:42 by anonymous:_ {linebreak} Clarification: The line MEMORY_DB copy ignore win_pol /home/centadm/win_pol4.csv \t should read MEMORY_DB copy ignore xyz /home/centadm/win_pol4.csv \t However the result is the same: -bash-3.00$ tclsh test_sqlite.tcl 3.3.5 while executing "MEMORY_DB copy ignore xyz /home/centadm/win_pol4.csv \t" (file "test_sqlite.tcl" line 7) -bash-3.00$ ---- _2006-May-05 19:46:56 by anonymous:_ {linebreak} I have narrowed it down to the code here in tclsqlite.c: zSql = sqlite3_mprintf("SELECT * FROM '%q'", zTable); if( zSql==0 ){ Tcl_AppendResult(interp, "Error: no such table: ", zTable, 0); return TCL_ERROR; } nByte = strlen(zSql); rc = sqlite3_prepare(pDb->db, zSql, 0, &pStmt, 0); sqlite3_free(zSql); if( rc ){ Tcl_AppendResult(interp, "Error: ", sqlite3_errmsg(pDb->db), 0); nCol = 0; }else{ nCol = sqlite3_column_count(pStmt); <--- RETURNING 0 FOR COLUMN COUNT, HAVE VERIFIED TABLE HAS TWO COLUMNS } sqlite3_finalize(pStmt); if( nCol==0 ) { return TCL_ERROR; <--- NO ERROR MESSAGE RETURNED } ---- _2006-May-16 17:51:28 by anonymous:_ {linebreak} I found the problem. The first sqlite3_prepare under DB_COPY should have -1 as it's third argument. When this was change from a 0 to -1 the copy command works in tclsqlite. rc = sqlite3_prepare(pDb->db, zSql,0, &pStmt, 0); should be rc = sqlite3_prepare(pDb->db, zSql,-1, &pStmt, 0); ---- _2006-May-16 18:01:11 by anonymous:_ {linebreak} There is also another reference (the insert statement) to the prepare statement under DB_COPY that needs to change it's third argument from 0 to -1. ---- _2006-Sep-27 16:24:53 by anonymous:_ {linebreak} The same problem is present with version 3.3.7 over here. However, the indicated patch seem to work.
#cfe8bd 1809 active 2006 May anonymous CodeGen Pending 1 3 Huge slowdown/increased memory use when using GROUP BY on big dataset This seemingly nonsensical query is a greatly reduced test case taken from several queries I use with SQLite 3.2.1. The real example joins various huge tables and much more complicated views. I'd like to upgrade beyond SQLite 3.2.1, but this is a showstopper. It takes 13 seconds to run on SQLite 3.2.1 and uses just 1.2M of memory. With 3.3.5+ from CVS it takes 185 seconds and uses 230M of memory. PRAGMA temp_store=MEMORY; CREATE TABLE n1(a integer primary key); INSERT INTO "n1" VALUES(1); INSERT INTO "n1" VALUES(2); INSERT INTO "n1" VALUES(3); INSERT INTO "n1" VALUES(4); INSERT INTO "n1" VALUES(5); INSERT INTO "n1" VALUES(6); INSERT INTO "n1" VALUES(7); INSERT INTO "n1" VALUES(8); INSERT INTO "n1" VALUES(9); INSERT INTO "n1" VALUES(10); INSERT INTO "n1" VALUES(11); INSERT INTO "n1" VALUES(12); INSERT INTO "n1" VALUES(13); INSERT INTO "n1" VALUES(14); INSERT INTO "n1" VALUES(15); CREATE VIEW vu as select v3.a a, v5.a-v2.a*v7.a b from n1 v1,n1 v2,n1 v3,n1 v4,n1 v5,n1 v6,n1 v7; select a a, sum(b) T from vu where a=7 group by a; It seems that SQLite 3.2.1 had a much more efficient GROUP BY algorithm that discarded unnecessary data as the view was traversed. _2006-May-13 03:01:28 by anonymous:_ {linebreak} Seeing as this ticket concerns the GROUP BY statement it would make more sense to have an example like this: select a a, sum(b) T from vu where a<4 group by a; But both queries exhibit the same slowdown and memory increase, in any event. ---- _2006-May-13 15:09:39 by anonymous:_ {linebreak} This GROUP BY slowdown/memory increase is not specific to VIEWs. I repeated the test against a comparably sized table with the same results. You'll see this effect for any SELECT operating on a large number of rows using GROUP BY. ---- _2006-May-13 16:44:04 by anonymous:_ {linebreak} The slowdown first appears in SQLite 3.2.6 in check-in [2662]. ---- _2006-May-24 13:19:29 by anonymous:_ {linebreak} Here's an example to show an actual real-life use of GROUP BY in SQLite <= 3.2.5... Imagine performing mathematical operations on every combination of rows in several large tables for statistical analysis. The GROUP BY algorithm change in 3.2.6 now makes using GROUP BY on huge cross joins not usable for this purpose because it creates an intermediate result set of the product of all cross joins - several times larger than the size of the (already huge) database itself. Indexing is not useful in this case because there is nothing to index by design. All table rows must be traversed. Older versions of SQLite performed this operation extremely efficiently because grouping took place in the main traversal loop. I would think that the old algorithm could be used, but instead of keeping the intermediate results in memory, an index and a table in temp store could be used.
#e8e8bd 1816 active 2006 May anonymous VDBE Pending 1 2 Database corruption with pragma auto_vacuum We had a database created with PRAGMA auto_vacuum=1, that started returning the following message on a DELETE statement. SQL error: database disk image is malformed Running the VACUUM command and running the same DELETE statement succeeds. Running PRAGMA integrity_check on the database (before the VACUUM command is issued) results in the following output: sqlite> PRAGMA integrity_check; *** in database main *** Page 3393 is never used Page 3398 is never used Page 3400 is never used Page 3401 is never used Page 3402 is never used Page 3405 is never used Page 3406 is never used sqlite> VACUUM; sqlite> PRAGMA integrity_check; ok We tried as a temporary workaround, running PRAGMA integrity_check and, based on the result, deciding whether or not to run VACUUM, but this can consume too much time. If needed, I can send a small database that exhibits this problem. _2006-May-22 21:45:47 by drh:_ {linebreak} The database is probably not helpful. What I need to know is: *: What sequence of SQL statements do you issue to cause this to occur? *: What operating system you are using. *: Is the application multi-threaded? *: Is the problem reproducible? *: Are you using a precompiled binary or did you compile it yourself? *: Does the problem go away if you turn off autovacuum? ---- _2006-May-22 22:11:09 by anonymous:_ {linebreak} *: What sequence of SQL statements do you issue to cause this to occur? It is unknown exactly what all of the the statements are leading up to the corruption. I can send the possible statements via private e-mail. *: What operating system you are using. Windows XP Professional w/ Service Pack 2. *: Is the application multi-threaded? Yes. *: Is the problem reproducible? The corruption happens on occasion -- so far it is not known to be easily reproducable in a finite number of steps. *: Are you using a precompiled binary or did you compile it yourself? Self-compiled library. When we use the database in our application, it is contained in abstracted classes with concurrency control. *: Does the problem go away if you turn off autovacuum? We have not seen database corruption if auto_vacuum is off when the database is initially created. Is it possible to turn off auto vacuum after the database tables have been created (no when using pragma auto_vacuum, according to the docs)? ---- _2006-May-22 22:28:46 by anonymous:_ {linebreak} Rather than relying on trial and error to reproduce the bug, one technique the bug reporter might try to reproduce the problem is to take a snapshot of the database when it is in a known good state and save it somewhere and then have every process that comes into contact with the database file log every SQLite command (and pragma) complete with millisecond-resolution timestamp and process/thread ID as follows: SELECT * FROM WHATEVER; -- 2006-05-23 14:44:45.237 PID 345 Thread 0 insert into blah values(3,4,5); -- 2006-05-23 14:50:15.345 PID 345 Thread 0 update foo set v=5 where y>4; -- 2006-05-23 15:05:12.930 PID 239 Thread 0 Should the problem happen again, each command could easily be replayed in an appropriate thread in the same order from the last known "good" state, greatly increasing the chances of repeating the bug. If repeating these commands does not lead to database corruption, it is fairly likely that the bug is in your multithreaded code, and not in SQLite. Perhaps SQLite already has such a command tracing facility already. I don't know. ---- _2006-May-22 22:42:04 by anonymous:_ sqlite3_trace(); It passes all the caller-generated SQL statements to a callback (although it doesn't fill in bindings). It also outputs a lot of "internal" SQL statements (VACUUM, for example, is a collection of operations on a temp table), but you should be able to recognize that stuff as something your app would never generate.
#f2dcdc 1861 active 2006 Jun anonymous Pager Pending 1 1 Problem in using Triggers and multithreading I am using SQLite3 database with triggers . This database is used by my processing engine which is having 10 threads accessing the same database. Trigger is used to updata and insert records in a table and that very table is also updated by threads. Processing engine crashes whenever a trigger updates or inserts a record in the table. Can you tell me how to configure my existing engine to avoid crashing? Is it safe to use trigger?
#f2dcdc 1862 active 2006 Jun anonymous TclLib Pending tclguy 1 1 SQLite cannot load/import data from file I found the problem when I tried to load a data file into a table. To reproduce the problem, I got a mini testcase. DATA FILE - test.dat --------------------------- 1 0 0 2 90000 0 3 366000 0 --------------------------- Log from SQLite: ------------------------------------------------------ khronos-yajun>sqlite3 test SQLite version 3.3.6 Enter ".help" for instructions sqlite> create table test (id INT, x1 INT, x2 INT); sqlite> .import test.dat test test.dat line 1: expected 3 columns of data but found 1 sqlite> .exit ------------------------------------------------------- The problem also exists when I use tcl wrapper (sql copy abort test test.dat). I looked into the code in src/tclsqlite.c, In Lines 1045 nByte = strlen(zSql); 1046 rc = sqlite3_prepare(pDb->db, zSql, 0, &pStmt, 0); 1047 sqlite3_free(zSql); Is the third argument of sqlite3_prepare supposed to be the length of zSql, hence nByte? Also in lines 1070 zSql[j++] = ')'; 1071 zSql[j] = 0; 1072 rc = sqlite3_prepare(pDb->db, zSql, 0, &pStmt, 0); 1073 free(zSql); If I change these two places to reflect the length of zSql, I seem to succeed. Yajun _2006-Sep-27 16:25:47 by anonymous:_ {linebreak} This is a duplicate of #1797
#cfe8bd 1867 active 2006 Jun anonymous BTree Pending 1 3 Access Violation after set a new page_size An access violation occured on W2K when I try to create a new table in the empty database. There was a following sequence of SQL commands select count(*)==2 as cnt from sqlite_master where type='table' and tbl_name in ('tbl1', 'tbl2'); so if cnt is equal 0 then I execute command pragma page_size=4096; and then create a new table. I gess that some of internal structures by this time have been initialized and so when I try to create new table the page_size is lower then needed. we overwrite memory in the function zeroPage in instruction: memset(&data[hdr], 0, pBt->usableSize - hdr); Size of structure data less then pBt->usableSize Below result after memset 0:000> dt MemPage 004c3cf0
+0x000 isInit : 0 ''
+0x001 idxShift : 0 ''
+0x002 nOverflow : 0 ''
+0x003 intKey : 0x1 ''
+0x004 leaf : 0x1 ''
+0x005 zeroData : 0 ''
+0x006 leafData : 0x1 ''
+0x007 hasData : 0 ''
+0x008 hdrOffset : 0 ''
+0x009 childPtrSize : 0 ''
+0x00a maxLocal : 0
+0x00c minLocal : 0
+0x00e cellOffset : 0
+0x010 idxParent : 0
+0x012 nFree : 0xf94
+0x014 nCell : 0
+0x018 aOvfl : [5] _OvflCell
+0x040 pBt : (null)
+0x044 aData : (null)
+0x048 pgno : 0
+0x04c pParent : (null)
0012ea50 10006861 004c3cf0 0000000d 00000064 dblited!decodeFlags+0x80 [D:\sqllite\sqlite-3.3.6\btree.c @ 1349]
0012ea70 10006710 004c3cf0 0000000d 004c3cf0 dblited!zeroPage+0xd0 [D:\sqllite\sqlite-3.3.6\btree.c @ 1466]
0012ea8c 10006215 002fd390 002fd390 00000000 dblited!newDatabase+0xf9 [D:\sqllite\sqlite-3.3.6\btree.c @ 2061]
0012eaa0 10052ba0 002f7c30 00000001 0012f0e4 dblited!sqlite3BtreeBeginTrans+0xd6 [D:\sqllite\sqlite-3.3.6\btree.c @ 2141]
0012f0a4 10057cf5 004c3d80 0012f13c 0012f478 dblited!sqlite3VdbeExec+0x2c6d [D:\sqllite\sqlite-3.3.6\vdbe.c @ 2386]
0012f0e4 00412801 004c3d80 0012f1d4 0012f478 dblited!sqlite3_step+0x1db [D:\sqllite\sqlite-3.3.6\vdbeapi.c @ 223]
#f2dcdc 1882 active 2006 Jul anonymous Pending 1 1 Wrong algorithm of SQLITE_VERSION_NUMBER calculation The sqlite3.h comment describing how numeric version number is calculated is as follows: "The SQLITE_VERSION_NUMBER is an integer with the value (X*100000 + Y*1000 + Z). For example, for version "3.1.1beta", SQLITE_VERSION_NUMBER is set to 3001001." But the value of SQLITE_VERSION_NUMBER is greater than the equation above suggests. The value X*100000 should be changed to X*1000000 (one milion).
#f2dcdc 1900 active 2006 Jul anonymous Unknown Pending a.rottmann 1 1 CURRENT_TIMESTAMP keyword not inserting UTC date in column This is the schema for my table. create table char (player varchar(64) NOT NULL default '~', name varchar(64) NOT NULL default '~', date timestamp NOT NULL default current_timestamp) Whenever an insert is made to the table the column 'date' does get a UTC timestamp, it gets a string value 'current_timestamp'. Is my schema wrong? _2006-Jul-30 22:31:06 by anonymous:_ {linebreak} *doesnt get a UTC timestap ---- _2006-Jul-31 00:38:49 by anonymous:_ {linebreak} Works fine for me. What's the exact syntax of your INSERT statement?
#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?
#f2dcdc 1954 active 2006 Sep anonymous Unknown Pending 1 1 Dual Core Processor Lockup I seem to be seeing a problem with dual core processors in the the Open call is locking and does not release or throw an exception. It does not occur every time, but occurs around 50% of the time. I have not seen the problem on non dual core processors. _2006-Sep-02 21:06:38 by anonymous:_ {linebreak} This ticket is way too vague to be actionable. What operating system? AMD or Intel? What specific version of SQLite? Was the library precompiled or did you compile it yourself? Personally, I can report no errors or problems with dual-core CPU's on Windows XP using an AMD X2 4400+ dual-core CPU. Tested with both a 32-bit build and a 64-bit build of SQLite on x64 Windows.
#f2dcdc 1974 active 2006 Sep anonymous Unknown Pending 1 1 column type not consistent in views package require sqlite3 sqlite3 db test.db db eval { create table one ( size FLOAT ); create view two as select size from one; } db eval {insert into one values(50.0)} puts [db eval {select size from one}] puts [db eval {select size from two}] outputs: 50.0 50
#f2dcdc 1980 active 2006 Sep drh Pending 1 1 Initializing FTS1 twice causes it to fail. If you try to load the shared module twice, it causes the module to no longer work.
#f2dcdc 1990 active 2006 Sep anonymous Pending 1 1 sqlite3_close doesn't release always the file handle I *think* that sqlite3_close behave strangly. I use version 3.3.7 on Linux (Fedora Core 5). What I do is to open a database, and start a transaction in it. Then, without ending the transaction, open again the database and simply close it. I found out, that the inner sqlite3_close return 0 (SQLITE_OK), but the file handle is not released. So if I do it too many times, I run out of file handles. You are free to ask why I open and close that many times the same database while it is already in transaction. This is my mistake. Actually, it is already fixed. But I still wonder - shouldn't the sqlite3_close return other thing then just SQLITE_OK? Especially if the file handle is not released? If it did, I would find my mistake much earlier. Here is my script that demonstrate it (you can use /usr/sbin/lsof in linux to see how many times the file is opened): #include int main(int argc, char **argv) { sqlite3* db; sqlite3* db_inner; int rc; int i; system("rm -f open_many_test.db"); rc = sqlite3_open("open_many_test.db", &db); sqlite3_exec(db, "begin", 0, 0, 0); sqlite3_stmt *pStmt; rc = sqlite3_prepare(db, "create table a (id varchar)", -1, &pStmt, 0); rc = sqlite3_step(pStmt); sqlite3_finalize(pStmt); rc = sqlite3_prepare(db, "insert into a values('bla')", -1, &pStmt, 0); rc = sqlite3_step(pStmt); sqlite3_finalize(pStmt); for (i = 0; i < 10000; i++) { rc = sqlite3_open("open_many_test.db", &db_inner); printf("sqlite3_open gives %d\n", rc); rc = sqlite3_close(db_inner); printf("sqlite3_close gives %d\n", rc); } sqlite3_exec(db, "commit", 0, 0, 0); rc = sqlite3_close(db); } _2006-Sep-23 15:29:46 by drh:_ {linebreak} This behavior is intentional. It is there to work around bugs in the design of posix advistory locks. See ticket #561 and check-in [1171]. Under posix, if you have the same file open multiple times and you close one of the file descriptors, all locks on that file for all file descriptors are cleared. To prevent this from occurring, SQLite defers closing file descriptors until all locks on the file have been released. One possible work-around would be to reuse file descriptors that waiting to be closed for the next open, rather than creating a new file descriptor. ---- _2006-Sep-23 15:35:21 by anonymous:_ {linebreak} The inner call should to sqlite3_open() should simply fail in that case, rather than set up a condition where by a file descriptor is leaked (which no one wants). This is unfortunate because sqlite3_open()'s behavior would not be uniform across platforms. ---- _2006-Sep-23 16:43:32 by anonymous:_ {linebreak} SQLite should do a lookup via stat()'s st_dev/st_ino fields prior to open() and if found to be the same as an already opened database file, it should use the same (refcounted) file descriptor, eliminating the need for open() in this case. ...upon reflection, having two sqlite connections using the same file descriptor would be a bad thing. stat() could be used to decide if a fd pending close() is recyclable, though. ---- _2006-Sep-23 18:17:34 by drh:_ {linebreak} Two points: 1: SQLite does not and has never leaked file descriptors. All file descriptors are eventually closed. The close is merely deferred until the pending transaction COMMITs. 2: I will be taking a very caution and careful approach toward resolving this issue. The issue itself is minor (it has only just now been reported but the behavior has been there for 3 years) but the consequences of getting the fix wrong are severe (database corruption.) And there are abundant opportunities for getting the fix wrong.
#f2dcdc 1992 active 2006 Sep anonymous Fixed shess 1 1 FTS1: Problems after dropping utility tables There are problems if FTS1 utilities tables are dropped from a database. See following SQL for details. drop table if exists x; -- Create a FTS1 table. create virtual table x using fts1 ('content'); -- Drop table x_content: Works fine, but should this be allowed? -- The same errors below also show if table x_term is dropped. drop table x_content; -- All attempts to access table x now result in errors, -- including dropping table x. There seems to be no way out -- except of recreating the database. All three commands below -- cause the same error, regardless if executed in sequence -- or individually: insert into x (content) values ('one two three'); -- Error! delete from x; -- Error! drop table x; -- Error! Added "not exists" to allow dropping an fts table with corrupted backing. Allowing updates to such tables is unlikely to happen (not even clear what it would mean, in most cases!).
#cfe8bd 1994 active 2006 Sep anonymous Parser Pending 1 3 Columns from nested joins aren't properly propagated When using this query: _:SELECT * FROM ROLE_ATTRIBUTE INNER JOIN (ROLE INNER JOIN PERSON ON ROLE.PERSON_ID=PERSON.ID) ON ROLE_ATTRIBUTE.PERSON_ID=ROLE.PERSON_ID AND ROLE_ATTRIBUTE.PROJECT_ID=ROLE.PROJECT_ID WHERE ((PERSON.FIRSTNAME = "bob")); the parser fails with an error "no such column: ROLE.PROJECT_ID". It seems that doing an inner join with more than one subexpression doesn't work. _2006-Sep-25 22:41:52 by anonymous:_ {linebreak} Your query will run without the brackets. SELECT * FROM PERSON P INNER JOIN ROLE_ATTRIBUTE RA ON P.ID = RA.PERSON_ID INNER JOIN ROLE R ON RA.PROJECT_ID = R.PROJECT_ID AND P.ID = R.PERSON_ID WHERE P.FIRSTNAME = 'bob'; ---- _2006-Sep-25 23:03:28 by navaraf:_ {linebreak} Hm, you're right. So actually the thing SQLite chokes on is the parenthesis syntax as JOIN parameter. I can try to modify the generator to produce the expanded form, but since the same code is used for MSSQL, MySQL and Oracle I still think it would be handy to allow it in SQLite too. Also it's not my code that generates these horrible expressions and I'd rather try to avoid modifying it. ---- _2006-Sep-26 09:59:13 by anonymous:_ {linebreak} I changed the title to correctly describe the problem. Also I found another thread on the mailing list that describes exactly the same problem: http://marc.10east.com/?t=115378699000001 ---- _2006-Sep-26 11:42:38 by navaraf:_ {linebreak} I believe the "lookupName" function in src/expr.c should do recursion for ephemeral tables found in the pSrcList (at least those that were created as subqueries in the FROM clause of the SELECT statement).
#f2dcdc 2017 active 2006 Oct anonymous Pending 1 1 DROP TABLE fails on FTS1 utility tables with certain OMIT_s defined The following SQL fails when SQLite is compiled with the SQLITE_OMIT_ defines stated below: create virtual table foo using fts1 (content); drop table foo; create virtual table foo using fts1 (content); Cause: The foo_content and foo_term tables are not deleted. To verify, please define these SQLITE_OMIT_s: OPTS += -DSQLITE_OMIT_ALTERTABLE OPTS += -DSQLITE_OMIT_ANALYZE OPTS += -DSQLITE_OMIT_AUTHORIZATION OPTS += -DSQLITE_OMIT_AUTOINCREMENT OPTS += -DSQLITE_OMIT_AUTOVACUUM OPTS += -DSQLITE_OMIT_BETWEEN_OPTIMIZATION OPTS += -DSQLITE_OMIT_BLOB_LITERAL OPTS += -DSQLITE_OMIT_CAST OPTS += -DSQLITE_OMIT_CHECK OPTS += -DSQLITE_OMIT_COMPLETE OPTS += -DSQLITE_OMIT_COMPOUND_SELECT OPTS += -DSQLITE_OMIT_EXPLAIN OPTS += -DSQLITE_OMIT_FLAG_PRAGMAS OPTS += -DSQLITE_OMIT_FOREIGN_KEY OPTS += -DSQLITE_OMIT_GET_TABLE OPTS += -DSQLITE_OMIT_GLOBALRECOVER OPTS += -DSQLITE_OMIT_INTEGRITY_CHECK OPTS += -DSQLITE_OMIT_LIKE_OPTIMIZATION OPTS += -DSQLITE_OMIT_MEMORYDB OPTS += -DSQLITE_OMIT_OR_OPTIMIZATION OPTS += -DSQLITE_OMIT_ORIGIN_NAMES OPTS += -DSQLITE_OMIT_PAGER_PRAGMAS OPTS += -DSQLITE_OMIT_PROGRESS_CALLBACK OPTS += -DSQLITE_OMIT_QUICKBALANCE OPTS += -DSQLITE_OMIT_REINDEX OPTS += -DSQLITE_OMIT_SCHEMA_VERSION_PRAGMAS OPTS += -DSQLITE_OMIT_SHARED_CACHE OPTS += -DSQLITE_OMIT_SUBQUERY OPTS += -DSQLITE_OMIT_TCL_VARIABLE OPTS += -DSQLITE_OMIT_TEMPDB OPTS += -DSQLITE_OMIT_TRACE OPTS += -DSQLITE_OMIT_TRIGGER OPTS += -DSQLITE_OMIT_UTF16 OPTS += -DSQLITE_OMIT_VACUUM OPTS += -DSQLITE_OMIT_VIEW Without the SQLITE_OMIT_s, everything works just fine.
#f2dcdc 2019 active 2006 Oct anonymous Pending 1 1 FTS1: Create table in transaction raises Out of Sequence error (21) This error: SQL error: library routine called out of sequence is caused if the following script is executed by the Windows version of the SQLite3 console application with .load fts1.dll extension. If it does not show immediately, it will eventually surface if the script is run multiple times. The cause of the problem seems to be related to the transaction, the create virtual table as well as the amount of data inserted. Finally, the script is attached.
#f2dcdc 2022 active 2006 Oct anonymous Pending 1 1 .import command is not working I have a windows system running version 3.3.6 and a linux system running 3.3.3 when I run .import catalog.csv TEMPDATA on the windows system, it works fine. On the linux system, no data gets imported. There are no error messages. Is this a known issue in 3.3.3? _2006-Oct-14 01:15:07 by anonymous:_ {linebreak} A sample SQL schema and a 3 line import file demonstrating the problem would be helpful. ---- _2006-Nov-08 15:48:28 by anonymous:_ {linebreak} Schema: CREATE TABLE Catalog ( UPC text , SKU text primary key , DESC text , PACK text , PRICE text , SIZE text ); test.csv contents 00000000103,103,EFFEM CHOCOLATE FUNSIZE 75PPK 1 X1EA,1,$155.94,1 EA 00000000152,414317,CLEARLIGHT SLUSH CUP 16OZ CDL16 1X50EA,1,$5.04,50 EA 00000000152,56880,CLEARLIGHT SLUSH CUP 16OZ CDL16 20X50EA,20,$96.31,50 EA Command that does nothing: .import test.csv Catalog ---- _2006-Nov-08 15:52:40 by anonymous:_ {linebreak} Sorry, I'll try this again: Schema: CREATE TABLE Catalog ( UPC text , SKU text primary key , DESC text , PACK text , PRICE text , SIZE text ); test.csv contents 00000000103,103,EFFEM CHOCOLATE FUNSIZE 75PPK 1 X1EA,1,$155.94,1 EA 00000000152,414317,CLEARLIGHT SLUSH CUP 16OZ CDL16 1X50EA,1,$5.04,50 EA 00000000152,56880,CLEARLIGHT SLUSH CUP 16OZ CDL16 20X50EA,20,$96.31,50 EA Command that does nothing: .import test.csv Catalog
#f2dcdc 2027 active 2006 Oct anonymous Pending 1 1 FTS: Phrase searches return Offsets for individual phrase words With FTS (one as well as two), phrase searches return offsets for all individual words instead of the phrase as a whole, like in select name, ingredients from recipe where ingredients match '"broccoli cheese"'; Offsets() returns at least two matches for both individual words: *: broccoli *: cheese
#f2dcdc 2032 active 2006 Oct anonymous Pending 1 1 AV in btree.c running FTS2 compiled with SQLITE_OMIT_SHARED_CACHE If compiled with FTS2 support as well as SQLITE_OMIT_SHARED_CACHE=1, the sqlite console application causes an Access Violation: btree.c, line 3538: Read of address x00000014 if( pCur->idx>=pPage->nCell ){ if the SQL (attatched) is executed. I believe that this is a bug in btree.c, for the following reasons: *: The AV does not show if the #ifndef SQLITE_OMIT_SHARED_CACHE (lines 3514 and 3525) are commentet out. *: From my reading, all virtual tables use the extension API only and do not access the btree directly. _2006-Oct-25 06:30:43 by shess:_ {linebreak} Note that the attached SQL has exactly 273 INSERT statements. 273==256+16+1, so this is kicking in at a merge point. Don't know how that's relevant, but it seems suspicious. ---- _2006-Oct-25 16:31:34 by anonymous:_ {linebreak} Many thanks for looking into this - it was driving me mad until I came up with the rather simple SQL to reproduce it. I am not sure if the number of INSERTS is 100% the number needed to cause the problem, but the crash always happens after the exact same number of inserts. I did not count them but added roughly enough of them to cause the error. Sidenote: I can also make FTS2 to crash at another point, which I thought was related to the sizeof() bug I also reported. But apprarently it is not. Unfortunately I can not provide a test case for this since I can reproduce it only after adding some 3000 or so copyrighted documents to an empty database. At the time of the crash the DB is about 250 MB in size. However, I will run a test after the next commits to FTS2. ---- _2006-Oct-26 08:57:41 by anonymous:_ {linebreak} My previious comments from yesterday seem to be invalidated by the latest checkins [3486], [3488] and [3489]. Many thanks for those! However, the problem with =SQLITE_OMIT_SHARED_CACHE= still persists.
#f2dcdc 2037 active 2006 Oct anonymous Pending 1 1 Sqlite3 can't use datafile in Chinese path with Win2000 and WindowsXP. Sqlite3 can't use datafile in Chinese path with Win2000 and WindowsXP. This is a bug in os_win.c . My firend modify code to so , it work right. /* ** Convert a UTF-8 string to UTF-32. Space to hold the returned string ** is obtained from sqliteMalloc. */ static WCHAR *utf8ToUnicode(const char *zFilename){ int nChar; WCHAR *zWideFilename; if( !isNT() ){ return 0; } nChar = MultiByteToWideChar(CP_THREAD_ACP, MB_COMPOSITE, zFilename, -1, NULL, 0); zWideFilename = sqliteMalloc( nChar*sizeof(zWideFilename[0]) ); if( zWideFilename==0 ){ return 0; } nChar = MultiByteToWideChar(CP_THREAD_ACP, MB_COMPOSITE, zFilename, -1, zWideFilename, nChar); if( nChar==0 ){ sqliteFree(zWideFilename); zWideFilename = 0; } return zWideFilename; } /* ** Convert UTF-32 to UTF-8. Space to hold the returned string is ** obtained from sqliteMalloc(). */ static char *unicodeToUtf8(const WCHAR *zWideFilename){ int nByte; char *zFilename; nByte = WideCharToMultiByte(CP_THREAD_ACP, WC_COMPOSITECHECK, zWideFilename, -1, 0, 0, 0, 0); zFilename = sqliteMalloc( nByte ); if( zFilename==0 ){ return 0; } nByte = WideCharToMultiByte(CP_THREAD_ACP, WC_COMPOSITECHECK, zWideFilename, -1, zFilename, nByte, 0, 0); if( nByte == 0 ){ sqliteFree(zFilename); zFilename = 0; } return zFilename; } _2006-Oct-20 10:26:46 by anonymous:_ {linebreak} The proposed fix is completely wrong, but the bug exists nonetheless. The problem is that SQLite expects file names in UTF-8 encoding (and there is probably bug in your application too guessing from the proposed fix). While this works fine on NT systems where the UTF-8 encoding is converted to UTF-16 and passed to system wide-character APIs, the code path for non-NT systems (Win 9x) with ANSI-only APIs doesn't convert the UTF-8 file names into the ANSI code page which is expected by the system APIs.
#f2dcdc 2043 active 2006 Oct anonymous Pending 1 1 Spaces in view statement If you have a table defined with fields that contain spaces. create table table1 ("field one", "field two", "field three"); Then you do a select select "field one" from table1; That works fine. However if you save it as a view create view view_one as select "field one" from table1; Then if you run a select on the view it fails. select * from view_one;
#f2dcdc 2046 active 2006 Oct anonymous Fixed shess 1 1 FTS1 - Error closing database due to unfinished statements The following script causes an error in SQLite3.exe with FTS1. The error will surface only AFTER the script has finished AND you have typed .exit at the sqlite> prompt to quit SQLite3. The problem seems that the SELECT statement is not properly finalized due to an internal error. -- The next line is for Windows only, please adopt it -- if running Linux or use a FTS1-enabled SQLite3 binary. select load_extension ('fts1.dll'); CREATE TABLE Snippets( SnippetID INTEGER PRIMARY KEY, SnippetTitle TEXT, FtsID INTEGER); CREATE VIRTUAL TABLE SnippetsFts USING FTS1 (SnippetTitle, SnippetText); INSERT INTO Snippets (SnippetTitle) VALUES ('one'); INSERT INTO Snippets (SnippetTitle) VALUES ('two'); SELECT SnippetID FROM Snippets JOIN SnippetsFts ON FtsID = +SnippetsFts.RowID WHERE SnippetsFts MATCH 'one'; -- After the script is done, type .exit at the prompt to close the database. -- -- SQLite3 will close, but report the following error before doing so: -- -- "error closing database: Unable to close due to unfinalised statements" -- -- Does this qualify for a bug? The script is also attached to this ticket. _2006-Nov-27 22:58:49 by shess:_ {linebreak} Attached tighter version of the replication script, generated in isolating what mattered to the bug.
#f2dcdc 2048 active 2006 Oct anonymous Pending drh 1 1 table_info on columns with no default value are returned as string On line 486, noDflt is declared as{linebreak} static const Token noDflt = { (unsigned char*)"", 0, 0 };{linebreak} {linebreak} And on line 493:{linebreak} if( pDflt->z ){{linebreak} sqlite3VdbeOp3(v, OP_String8, 0, 0, (char*)pDflt->z, pDflt->n);{linebreak} }else{{linebreak} sqlite3VdbeAddOp(v, OP_Null, 0, 0);{linebreak} {linebreak} So columns with no default value aren't being set to null because the (pDflt->z) condition is non-null.
#f2dcdc 2059 active 2006 Nov anonymous Pending 1 1 Still missing .DEF file from Windows 3.3.8 source code distribution The file sqlite3.def is missing from the zip archive of sources used to build sqlite3 on Windows. Ticket number 2031 was closed with a remark that this file is generated during the build process. That is true if one is building on Linux with MinGW32 configured as a cross-compiler. If one were building using that method then I assume one would not be downloading the src.zip archive anyway. My impression is that the src.zip archive is prepared once the build has been performed on Linux so Windows developers can directly build sqlite (and the generated files) without need of the other tools that the build process depends on. If this is accurate, then it would be very helpful if the src.zip archive could also include the sqlite3.def file. Without this file it is not possible for Windows developers to create a DLL from the src.zip archive. Thanks _2006-Nov-09 20:05:23 by anonymous:_ {linebreak} Works fine as is with MinGW ./configure && make sqlite3.exe
#f2dcdc 2060 active 2006 Nov anonymous Pending 1 1 Table references enclosed in parenthesis become "invisible" Hi, I'm developing an RDF-based system, which translates queries from SPARQL into SQL. While trying to add support for SQLite (MySQL is already supported) I came across the following problem: when table references in a FROM clause are enclosed in parenthesis, they cannot be referenced from outside the parenthesized expression. For example, given the table definitions CREATE TABLE IF NOT EXISTS t1 (a, b); CREATE TABLE IF NOT EXISTS t2 (c, d); CREATE TABLE IF NOT EXISTS t3 (e, f); The following queries all fail with "no such column" errors: SELECT t1.a, t3.f FROM (t1 CROSS JOIN t2 ON t1.b = t2.c) LEFT JOIN t3 ON t2.d = t3.e; SELECT t1.a, t3.f FROM t1 CROSS JOIN (t2 LEFT JOIN t3 ON t2.d = t3.e) ON t1.b = t2.c; SELECT t1.a, t2.d FROM (t1), (t2) WHERE t1.b = t2.c; I'm not sure if it is always possible to reformulate the queries in such a way that the extra parenthesis aren't necessary, but I suspect that complex expressions involving joins may require them to achieve the intended semantics. In any case, my system would require large changes to be able to get rid of the parenthesized subjoins, so it would be nice if this problem could be fixed. :-) _2006-Nov-10 03:56:46 by anonymous:_ {linebreak} For what it's worth, here's the parse trees of two similar queries ("SELECT t1.a, t2.d FROM t1, t2 WHERE t1.b = t2.c" and "SELECT t1.a, t2.d FROM (t1), (t2) WHERE t1.b = t2.c"), as well as one of the other more complicated join queries previously listed. SELECT t1.a, t2.d FROM t1, t2 WHERE t1.b = t2.c; Select { op: TK_SELECT isResolved: 1 pSrc: { a[0]: { zName: t1 iCursor: 0 colUsed: 0x00000003 pTab: t1 jointype: JT_INNER } a[1]: { zName: t2 iCursor: 1 colUsed: 0x00000003 pTab: t2 } } pEList: { a[0]: { pExpr: { op: TK_COLUMN span: {t1.a} affinity: SQLITE_AFF_NONE iTable: 0 iColumn: 0 pTab: t1 } } a[1]: { pExpr: { op: TK_COLUMN span: {t2.d} affinity: SQLITE_AFF_NONE iTable: 1 iColumn: 1 pTab: t2 } } } pWhere: { op: TK_EQ span: {t1.b = t2.c} pLeft: { op: TK_COLUMN span: {t1.b} affinity: SQLITE_AFF_NONE iTable: 0 iColumn: 1 pTab: t1 } pRight: { op: TK_COLUMN span: {t2.c} affinity: SQLITE_AFF_NONE iTable: 1 iColumn: 0 pTab: t2 } } } SELECT t1.a, t2.d FROM (t1), (t2) WHERE t1.b = t2.c; Select { op: TK_SELECT isResolved: 1 pSrc: { a[0]: { zAlias: sqlite_subquery_5C0A10_ iCursor: 0 pTab: sqlite_subquery_5C0A10_ pSelect: { op: TK_SELECT isResolved: 1 pSrc: { a[0]: { zName: t1 iCursor: 1 colUsed: 0x00000003 pTab: t1 } } pEList: { a[0]: { zName: a pExpr: { op: TK_COLUMN token: {a} span: {a} affinity: SQLITE_AFF_NONE iTable: 1 iColumn: 0 pTab: t1 } } a[1]: { zName: b pExpr: { op: TK_COLUMN token: {b} span: {b} affinity: SQLITE_AFF_NONE iTable: 1 iColumn: 1 pTab: t1 } } } } jointype: JT_INNER } a[1]: { zAlias: sqlite_subquery_5BE4F0_ iCursor: 2 pTab: sqlite_subquery_5BE4F0_ pSelect: { op: TK_SELECT isResolved: 1 pSrc: { a[0]: { zName: t2 iCursor: 3 colUsed: 0x00000003 pTab: t2 } } pEList: { a[0]: { zName: c pExpr: { op: TK_COLUMN token: {c} span: {c} affinity: SQLITE_AFF_NONE iTable: 3 iColumn: 0 pTab: t2 } } a[1]: { zName: d pExpr: { op: TK_COLUMN token: {d} span: {d} affinity: SQLITE_AFF_NONE iTable: 3 iColumn: 1 pTab: t2 } } } } } } pEList: { a[0]: { pExpr: { op: TK_COLUMN span: {t1.a} flags: EP_Resolved EP_Error iTable: -1 iColumn: 0 } } a[1]: { pExpr: { op: TK_DOT span: {t2.d} pLeft: { op: TK_ID token: {t2} span: {t2} } pRight: { op: TK_ID token: {d} span: {d} } } } } pWhere: { op: TK_EQ span: {t1.b = t2.c} pLeft: { op: TK_DOT span: {t1.b} pLeft: { op: TK_ID token: {t1} span: {t1} } pRight: { op: TK_ID token: {b} span: {b} } } pRight: { op: TK_DOT span: {t2.c} pLeft: { op: TK_ID token: {t2} span: {t2} } pRight: { op: TK_ID token: {c} span: {c} } } } } SQL error: no such column: t1.a SELECT t1.a, t3.f FROM (t1 CROSS JOIN t2 ON t1.b = t2.c) LEFT JOIN t3 ON t2.d = t3.e; Select { op: TK_SELECT isResolved: 1 pSrc: { a[0]: { zAlias: sqlite_subquery_5BFA30_ iCursor: 0 pTab: sqlite_subquery_5BFA30_ pSelect: { op: TK_SELECT isResolved: 1 pSrc: { a[0]: { zName: t1 iCursor: 1 colUsed: 0x00000003 pTab: t1 jointype: JT_INNER JT_CROSS } a[1]: { zName: t2 iCursor: 2 colUsed: 0x00000003 pTab: t2 } } pEList: { a[0]: { zName: a pExpr: { op: TK_COLUMN span: {t1.a} affinity: SQLITE_AFF_NONE iTable: 1 iColumn: 0 pTab: t1 } } a[1]: { zName: b pExpr: { op: TK_COLUMN span: {t1.b} affinity: SQLITE_AFF_NONE iTable: 1 iColumn: 1 pTab: t1 } } a[2]: { zName: c pExpr: { op: TK_COLUMN span: {t2.c} affinity: SQLITE_AFF_NONE iTable: 2 iColumn: 0 pTab: t2 } } a[3]: { zName: d pExpr: { op: TK_COLUMN span: {t2.d} affinity: SQLITE_AFF_NONE iTable: 2 iColumn: 1 pTab: t2 } } } pWhere: { op: TK_EQ span: {t1.b = t2.c} flags: EP_FromJoin EP_Resolved iRightJoinTable: 2 pLeft: { op: TK_COLUMN span: {t1.b} affinity: SQLITE_AFF_NONE flags: EP_FromJoin EP_Resolved iTable: 1 iColumn: 1 iRightJoinTable: 2 pTab: t1 } pRight: { op: TK_COLUMN span: {t2.c} affinity: SQLITE_AFF_NONE flags: EP_FromJoin EP_Resolved iTable: 2 iColumn: 0 iRightJoinTable: 2 pTab: t2 } } } jointype: JT_LEFT JT_OUTER } a[1]: { zName: t3 iCursor: 3 pTab: t3 } } pEList: { a[0]: { pExpr: { op: TK_COLUMN span: {t1.a} flags: EP_Resolved EP_Error iTable: -1 iColumn: 0 } } a[1]: { pExpr: { op: TK_DOT span: {t3.f} pLeft: { op: TK_ID token: {t3} span: {t3} } pRight: { op: TK_ID token: {f} span: {f} } } } } pWhere: { op: TK_EQ span: {t2.d = t3.e} flags: EP_FromJoin iRightJoinTable: 3 pLeft: { op: TK_DOT span: {t2.d} flags: EP_FromJoin iRightJoinTable: 3 pLeft: { op: TK_ID token: {t2} span: {t2} flags: EP_FromJoin iRightJoinTable: 3 } pRight: { op: TK_ID token: {d} span: {d} flags: EP_FromJoin iRightJoinTable: 3 } } pRight: { op: TK_DOT span: {t3.e} flags: EP_FromJoin iRightJoinTable: 3 pLeft: { op: TK_ID token: {t3} span: {t3} flags: EP_FromJoin iRightJoinTable: 3 } pRight: { op: TK_ID token: {e} span: {e} flags: EP_FromJoin iRightJoinTable: 3 } } } } SQL error: no such column: t1.a ---- _2006-Nov-11 18:29:33 by anonymous:_ {linebreak} The resolving bug appears to be that unique column names or column aliases are searched across all subqueries, but table names and table aliases are only searched at their current SELECT level only. With this in mind, here are mechanical workarounds without using column aliases (assumes the column names in all joined tables are unique): SELECT a, f FROM (t1 CROSS JOIN t2 ON t1.b = t2.c) LEFT JOIN t3 ON d = e; SELECT t1.a, f FROM t1 CROSS JOIN (t2 LEFT JOIN t3 ON t2.d = t3.e) ON t1.b = c; SELECT a, d FROM (t1), (t2) WHERE b = c; And here are mechanical workarounds using column aliases (assumes the column names are not unique between tables): SELECT t1.a, t3f FROM t1 CROSS JOIN (select t3.f t3f, t2.c t2c from t2 LEFT JOIN t3 ON t2.d = t3.e) ON t1.b = t2c; SELECT t1a, t3.f FROM (select t1.a t1a, t2.d t2d from t1 CROSS JOIN t2 ON t1.b = t2.c) LEFT JOIN t3 ON t2d = t3.e; SELECT t1a, t2d FROM (select t1.a t1a, t1.b t1b from t1), (select t2.c t2c, t2.d t2d from t2) WHERE t1b = t2c; Notice that t3.f in the second query did not require an alias because the table "t3" was part of its immediate SELECT. You could make an alias for every column just in case, I just wanted to highlight the difference. ---- _2007-Feb-13 15:40:31 by anonymous:_ {linebreak} Fixing this issue would slow down SELECT parsing and column resolution for all queries (more specifically all prepared statements) due to the recursion required for column resolution. It would be easier to change your SQL code generator to accomodate SQLite. Just make aliases for every table at every subselect level and have the SELECT at any given level only work with the table aliases at that level.
#f2dcdc 2076 active 2006 Nov anonymous Pending a.rottmann 1 1 % exists as value in varchar abnormal abend of client application (C++) when sqlite returns stream of data containing "%" value. Is % a special character? _2006-Nov-21 14:14:25 by anonymous:_ {linebreak} % is not a special character. Can you post a small C program demonstrating the problem?
#f2dcdc 2081 active 2006 Nov anonymous Pending doughenry 1 1 sqlite3_column_decltype throws exception, if selection is grouped If I "group by" a selection over several columns I can't find out the orgin type of these columns using sqlite3_column_decltype(..). An exception is thrown. _2006-Nov-23 18:37:47 by anonymous:_ {linebreak} You also get no decl type from a subselect. This goes to the typeless nature of SQLite - I don't think a type can even be derived in this case.
#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