MySQL "Not IN" query suddenly stopped returning results

Question

I'm doing some work for a site on a shared hosting environment that has a history of flipping settings on us. This week, an update script suddenly stopped working, and the underlying reason is that a NOT IN clause that used to return results is no longer doing so.

The query, in its essence, is this:

SELECT  *
FROM db1.entry
where entry_id not in
(
  select entry_id from db2.content
)

I can verify that there are indeed records that should be returned by inspecting the two tables directly. When running the below two queries, the first one returns entry_ids that the second does not:

SELECT  *
FROM db1.entry 
order by entry_id desc

SELECT  *
FROM db2.content
order by entry_id desc

And to re-iterate, this was all working correctly for several months. No code changes were made, but it's possible that a MySQL setting was changed along the way. It's also possible that something changed in the PHP environment, but that seems less likely since the query in question fails in the same way when run from phpMyAdmin as it does when run from the live site.

And, of course, it still works perfectly on my dev box.

The live site is running MySQL version 4.1.11. My question is, does anybody know of a MySQL setting for that version that would change the way these NOT IN queries work?

Thanks.

Answer 1

Make sure your inner query does not return NULL's.

From documentation:

To comply with the SQL standard, IN returns NULL not only if the expression on the left hand side is NULL, but also if no match is found in the list and one of the expressions in the list is NULL.

Answer 2

you probably have a NULL value in at least one row for entry_id use a LEFT JOIN or NOT EXISTS instead