Thanks for the report. The current test suite has many cases testing queries of the form you mention. I added one specifically for the query you suggested to in4.test. Everything passes for the current version. If you could test with the latest version of SQLite, it might help show where the problem lies (in SQSLite, the Python bindings, or possibly in your application code). Most of the historical versions of SQLite are also available from the website. If this was a problem in an older version, but not with the latest, it might be beneficial for you to try incremental versions to find where it was fixed.
-Shane On Fri, Nov 7, 2008 at 6:48 PM, Matt Craighead < [EMAIL PROTECTED]> wrote: > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
