Hi all, I've hit what I believe may be a crash bug in SQLite. I'm using the version bundled with Python 2.5.2 on Windows, which is SQLite 3.3.4, it would appear. I know, this isn't the latest version, but this happens to be the version bundled with the version of Python my product relies on. (I haven't switched to Python 2.6 yet; for one, there are no publicly available mod_python Win32 binaries for Apache.)
Originally my query looked something like: SELECT x FROM table WHERE id IN (SELECT id FROM other_table WHERE y=1) I assumed this would do the subquery only once, but I found (both from slow performance and by using EXPLAIN QUERY PLAN) that in fact it was doing a full table scan of "table", and for each element, looking at its id to see whether that id could be found in other_table. On the other hand, if I did two separate queries, and wrote the second query as follows: SELECT x FROM table WHERE id IN (1,3,5,7) ...it would use table's index, which was a lot faster. Unfortunately if the list of id's was empty, I'd end up constructing a query with a "WHERE x IN ()" clause -- and this would cause a crash deep in the guts of sqlite3.dll. Without symbols it wasn't clear where the crash was coming from. >From the grammar diagram at http://www.sqlite.org/lang_expr.html, it would appear that using () for an empty list is not legal SQL syntax as recognized by SQLite. Is that correct? Even if so, it still shouldn't crash, right? I would think that it should consistently either produce an error, or consistently treat () as an empty set. I don't get an error, and while sometimes it appears to be treated as an empty set, sometimes I get a crash. Oddly this crash doesn't happen all the time. It only seems to happen for the following specific query: SELECT x FROM table WHERE id IN () AND x IN (SELECT x FROM table2 WHERE id=<value>) Any ideas? Has this been fixed in a newer version already? I've already worked around this and I no longer issue SQLite any "IN ()" queries, but I figured I'd still report the problem. -- Matt Craighead Founder/CEO, Conifer Systems LLC http://www.conifersystems.com 512-772-1834 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
