Schrödinger’s logic: Neither IN nor NOT IN a tuple

Interesting and peculiar. It turns out that Tonya’s way of deleting entries is to just delete everything that is not resubmitted. This should work, but it fails on the last entry. The reason why it doesn’t work is a little bit subtle and weird.

The query in question is

db()->execPrintf('DELETE FROM am_releases_templates WHERE release_id = %i AND id NOT IN %@i', $release_id, array_keys($template_ids));

The question is, what happens when $template_ids is empty? What does printfQuery() do? printfQuery() is mine, of course, so I should know, and what I did was to pass in the tuple (NULL), since SQL considers NULL not equal to anything. So, I thought, for any value x, `x IN (NULL)` should be false—and consequently, `x NOT IN (NULL)` must be true. Stupidly, I didn’t test and verify this.

It turns out that MySQL returns an empty result set when you compare against the tuple (NULL). That is, `...AND id NOT IN (NULL)` is *not* the complement of `...AND id IN (NULL)`, so the union of `x and not x` is...an empty set, rather than all the elements. This is rather weird.

Conclusion: I really don’t like MySQL.

Update: Not just MySQL, but SQL in general, it seems.