gistfile1.php

I don't think it's about the conflict, consider two simple tables, say posts (p):

|| id ||
||  1 ||
||  2 ||
||  3 ||

And term relationships (tr):

|| post_id || term_id ||
||       1 ||       1 ||
||       1 ||       2 ||
||       1 ||       3 ||
||       2 ||       1 ||
||       3 ||       1 ||

Here our first post is assigned three terms, 1, 2 and 3. Second and third
have only term 1. Assume we want to retrieve posts and exclude ones that
have have term 2. Here's (a very simplified version of) what trunk
currently does:

{{{
SELECT * FROM p JOIN tr ON p.id = tr.post_id WHERE tr.term_id NOT IN (2);
}}}

You'll get all three posts and from the entries you can see that two rows have
been selected for post 1, and the one with term_id=2 was excluded. At first it
might seem odd, but in reality it's exactly what we asked for: join two tables
on post_id, and include every possible variant where term_id NOT IN (2).

|| id || post_id || term_id ||
||  1 ||       1 ||       1 ||
||  1 ||       1 ||       3 ||
||  2 ||       2 ||       1 ||
||  3 ||       3 ||       1 ||

We can rewrite our query to use a subquery:

{{{
SELECT * FROM p JOIN tr ON p.id = tr.post_id WHERE p.id NOT IN (
    SELECT post_id FROM tr WHERE term_id NOT IN (2)
);
}}}

|| id || post_id || term_id ||
||  2 ||       2 ||       1 ||
||  3 ||       3 ||       1 ||

Which reads: join two tables and include all possible variants, but exclude 
the row if the post id is in (select all post ids that contain term 2).
Sort of..

Yeah, that makes total sense. Good catch.

One note: you've got a typo in the last SQL query, you use NOT IN twice rather than NOT IN then IN.