Android 2.2 (Froyo) SQLite missing some IN clause functionality?

Question

Recently I updated my Nexus One to Froyo (2.2) and I've noticed some significantly different behavior with SQLite.

For example, I had been using a subquery (returning string data) as part of an IN clause, and the subquery portion no longer appears to function correctly. I've attached an example SQL query working with the contacts database below (Direct query shown for illustrative purposes only):

SELECT _id FROM data WHERE display_name IN (SELECT display_name FROM contacts);

Up until Android 2.1 this was not a problem. After the update however, this returns an empty record set unless I run the subquery separately, surround each of the resulting values in single quotes and append those values directly into the IN clause.

I have noticed this loss of functionality in multiple applications I'm working on, but I should note that it does appear to work when the results would be numeric rather than string based.

Has anyone else run into this issue?

Answer 1

If the behaviour of SQLite has indeed changed, you could try re-writing the query using the EXISTS operator instead:

SELECT _id FROM data WHERE EXISTS
    (SELECT * FROM contacts WHERE contacts.display_name = data.display_name)

This removes the dependence on the IN operator.