Django's .exclude() does not drop your NULL rows

I spent an afternoon measuring a bug that does not exist. Here is the
measurement, and the rule I should have applied an hour earlier.

---

I was adding a check to a static analyser for Django, and I had what felt like
a good candidate.

Take a nullable column:

class Task(models.Model):
    status = models.CharField(max_length=20, null=True)

and the query everybody writes:

Task.objects.exclude(status="done")

Read that out loud and it means everything that is not done. Now think about
what the database does with a row whose status is NULL. In SQL,
NULL = 'done' is not false. It is NULL. And NOT NULL is still NULL,
which is not true, and a WHERE clause keeps a row only when its condition is
true.

So the row disappears. Not excluded because it is done — excluded because the
database does not know whether it is done, and silently declines to guess.

This is real. It is the classic three-valued-logic trap, it is in every
database textbook, and it bites people in raw SQL constantly. My check would
find every .exclude(field=value) where field is nullable and say: these
rows are vanishing and nobody told you.

The prevalence looked excellent

The bar in that repository is that you measure before you build, so I did. I
cloned nine large open-source Django projects — Wagtail, Saleor,
django-oscar, Misago, Weblate, NetBox, pretix, DefectDojo, Read the Docs —
and counted .exclude() calls with a plain keyword argument, skipping
anything already using __isnull because that author was clearly thinking
about NULLs already.

742 call sites. 588 of them outside test code.

That is a lot. For comparison, a different candidate I had measured that same
morning — a Meta.ordering that spans a relation, which forces a JOIN on
every query including .count() and .exists() — appeared 6 times in 562
orderings. I dropped that one for being too rare to be worth a check.

588 is not too rare. 588 is a check that fires on nearly every project it
touches.

Then I checked the prior art, and it disagreed with me

Before writing anything I went looking for whoever had already solved this.
What I found instead was a page asserting, in passing, that Django's ORM "is
generally smart enough to include rows where the status is NULL."

That is the opposite of my premise.

One of us was wrong, and I could not tell which from reading. A blog post is
not evidence and neither is my recollection of a textbook. The answer was in
the SQL Django actually generates, which is twenty lines away from anyone who
wants it.

Twenty lines

import django
from django.conf import settings

settings.configure(
    INSTALLED_APPS=["django.contrib.contenttypes"],
    DATABASES={"default": {"ENGINE": "django.db.backends.sqlite3",
                           "NAME": ":memory:"}},
)
django.setup()

from django.db import connection, models


class Thing(models.Model):
    status = models.CharField(max_length=20, null=True)
    note = models.CharField(max_length=20)

    class Meta:
        app_label = "contenttypes"


with connection.schema_editor() as editor:
    editor.create_model(Thing)

Thing.objects.create(status="done", note="a")
Thing.objects.create(status="open", note="b")
Thing.objects.create(status=None, note="c")     # the row in question

qs = Thing.objects.exclude(status="done")
print(qs.query)
print(sorted(qs.values_list("note", flat=True)))

Output:

SELECT ... FROM "contenttypes_thing"
WHERE NOT ("contenttypes_thing"."status" = done
           AND "contenttypes_thing"."status" IS NOT NULL)

['b', 'c']

c is there. The NULL row survives.

Look at the WHERE clause and you can see exactly why. Django did not write
NOT (status = 'done'). It wrote NOT (status = 'done' AND status IS NOT
NULL). For the NULL row the inner expression is NULL AND FALSE, which is
FALSE, and NOT FALSE is TRUE. The row is kept.

Django put the guard in. My premise was about SQL, and it was correct about
SQL — but I was not writing SQL, and the ORM had already thought about this.

It is not one lucky path

Three shapes, same result:

	keeps the NULL row
exclude(status="done")	yes
filter(~Q(status="done"))	yes
exclude(status__in=["done"])	yes

And the guard is not applied blindly. Same model, one nullable field and one
that is not:

exclude(nullable="done")
  NOT ("thing"."nullable" = done AND "thing"."nullable" IS NOT NULL)

exclude(required="done")
  NOT ("thing"."required" = done)

Django adds the IS NOT NULL only where a NULL is possible. It is not
defensive boilerplate; the ORM knows which columns need it.

While I was there: the multi-valued case

There is a neighbouring warning that gets repeated a lot — that filter() and
exclude() stop being mirror images once you cross a multi-valued relation.
I had half-expected to find a second check hiding there, so I measured that
too:

Parent.objects.exclude(children__tag="red")

with four parents: one with a red child, one with red and blue, one with only
blue, one with no children at all.

result:            ['no_children', 'only_blue']
naive expectation: ['no_children', 'only_blue']

Identical. The generated SQL is a NOT EXISTS subquery, which expresses
parents with no red child precisely, including the childless one.

There is a genuine subtlety in this area about chaining and about what
filter() does across multiple calls, but the scary version of the warning —
that exclude() across a relation quietly returns nonsense — is not what
modern Django does.

What I did with the 588

Nothing. There is no check.

Had I built it on the strength of the prevalence number, it would have opened
588 findings, every one of them wrong, in nine of the most carefully
maintained Django codebases in existence. And the worst part is that it would
have looked authoritative. Each finding would have quoted a real line, named a
real nullable field, and explained a real property of SQL. A reviewer without
a SQLite shell open would have believed every word.

Static analysis does not fail by crashing. It fails by being confidently
wrong, and a plausible sentence that sends someone in the wrong direction
costs more than no sentence at all.

The rule

I had two numbers that afternoon: 6 out of 562, and 588.

I rejected the first for being too rare, which was the easy call. I nearly
accepted the second because it was large — and prevalence measures how often
your pattern appears, not whether it is a defect. They are separate
questions and only one of them was answered.

Prevalence is not permission. Measure that the thing is real before you
measure how often it happens.

Twenty lines and about four minutes. That is the entire cost of finding out.

---

I write and maintain
django-chainsaw-mcp, a
static analyser for the Django questions that stop a deploy. The check that
did get built out of that afternoon is in the next post. The tool is written
largely with Claude — docs/authorship.md sets out which parts, and the
deciding, rejecting and measuring are the parts I do.