Databases / Correctness

NOT IN With a NULL Drops Every Row, and the Test That Passed Against an Empty Table

A filter to hide messages from blocked users. Three words of SQL, obviously correct, and it would have made every webhook and bot message disappear from the entire app. The column was nullable, the filter used NOT IN, and in SQL those two facts combine into silent data loss that no error will ever tell you about.

The three-valued logic underneath

SQL comparisons against NULL do not return false. They return NULL, which means unknown. A WHERE clause keeps rows where the condition is true, so unknown is discarded exactly like false:

SELECT NULL NOT IN (1, 2);   -- NULL, not true
SELECT NULL IN (1, 2);       -- NULL, not false
SELECT NULL = NULL;          -- NULL

So this query returns nothing at all for rows where author_id is null:

SELECT * FROM messages WHERE author_id NOT IN (7, 12);

Those rows are neither blocked nor kept. They are gone, and the query reports success.

BE WARNED: IN and NOT IN fail in opposite directions. IN is usually safe by accident, because a null row is not something you were selecting anyway. NOT IN is the dangerous half, because you are asking for everything except a small list, and the null rows are silently excluded from your everything. If your subquery can return a null, the outer NOT IN returns no rows whatsoever.

Spell the null case out

The fix is to say what should happen to nulls, rather than letting three-valued logic decide:

-- keep messages with no author, exclude the blocked ones
WHERE author_id IS NULL OR author_id NOT IN (7, 12)

NOT EXISTS is the version that does not have this trap at all, and it is the better habit for subqueries:

WHERE NOT EXISTS (
  SELECT 1 FROM blocks b WHERE b.blocked_id = messages.author_id
)

An ORM does not save you, because it compiles to the same SQL

This is easy to miss when the query is written in an ORM, because the code reads like a language with ordinary booleans. Prisma's notIn compiles to NOT IN, so it inherits the behaviour exactly:

// silently drops every row where authorId is null
where: { authorId: { notIn: blockedIds } }

// says what you meant
where: { OR: [ { authorId: null }, { authorId: { notIn: blockedIds } } ] }

Wrap it in one helper the moment you have two of these, because this filter spreads. Ours had to apply to channel history, pinned messages, search results and the live event stream, which is four chances to write it correctly and four chances not to.

Ask which columns can actually be null

The real question behind the bug is one you can answer in a second, and almost nobody asks:

SELECT column_name, is_nullable FROM information_schema.columns
 WHERE table_name = 'messages';

Nullable foreign keys are usually deliberate and usually mean "this row was not created by a user". System events, imports, webhooks, integrations. Which means the rows a NOT IN filter quietly deletes are precisely the automated ones nobody has in mind while writing a filter about people.

The part worth taking away: a test that lied

The bug did not ship, and that was very nearly luck. The first end-to-end run included a check for exactly this failure. It passed.

It passed because the test channel contained no null-author messages. Zero rows in, zero rows dropped, nothing to detect. The check proved nothing at all while printing PASS, which is worse than having no check, because it converts an open question into a settled one.

A green check over an empty fixture is not evidence. A test for "X is not dropped" has to first assert that some X exists. Create the rows the test is about, count them before and after, and fail loudly when the before-count is zero. Same rule as any monitoring check that cannot fail, and the same reason it is dangerous.

-- the assertion the passing test was missing
seeded = count of rows WHERE author_id IS NULL
assert seeded > 0                 -- otherwise this test proves nothing
assert visible_after_filter includes those rows

Where else the same shape hides

Anti-joins written as NOT IN (SELECT ...), where the subquery column is nullable. One null in that subquery and the outer query returns zero rows, forever.
<> comparisons. status <> 'archived' also drops null statuses, for the same reason.
Aggregates. COUNT(column) skips nulls while COUNT(*) does not, so two numbers you expect to match will not.
NOT IN against an empty list. Some drivers turn this into something that matches nothing rather than everything, which is the same class of surprise from a different direction.

When this isn't your problem

The column is NOT NULL. Then NOT IN behaves the way everyone expects. Confirm it from the schema rather than from memory, because nullability changes in migrations.
You already use NOT EXISTS everywhere. Nothing to fix, and it is the habit worth keeping.
You are filtering something with no automated rows. The risk is real but low. It is also the situation that changes the day you add a bot.