Fixed #20024 -- Made exclude(field__in=[None, ...]) generate IS NULL

Jkhall81 · Jkhall81 · commit c8d48e51f651 · 2025-07-31T16:12:54.000-07:00
clause.

this fixes a long-standing issue where exclude(field__in=[None, ...])
would incorrectly return no results due to SQL NULL handling in NOT IN
clauses.

Now, if the list includes None, the ORM splits the condition into:
	IS NULL OR IN (...)

This aligns exclude(...__in=...) behavior with user expectations and
avoids leaking SQL's tri-valued logic into the ORM.

Includes tests and a release note entry under 5.2.5.
diff --git a/django/db/models/lookups.py b/django/db/models/lookups.py
@@ -553,6 +553,32 @@ def as_sql(self, compiler, connection):
             and len(self.rhs) > max_in_list_size
         ):
             return self.split_parameter_list_as_sql(compiler, connection)
+
+        if self.rhs_is_direct_value() and isinstance(self.rhs, (list, tuple)):
+            values = list(self.rhs)
+            has_none = None in values
+            filtered_values = [v for v in values if v is not None]
+            if not filtered_values and not has_none:
+                # Avoid circular import when importing NothingNode
+                from django.db.models.sql.where import NothingNode
+
+                where = NothingNode()
+                return compiler.compile(where)
+
+            lhs, lhs_params = self.process_lhs(compiler, connection)
+            sql_parts = []
+            params = []
+
+            if filtered_values:
+                placeholders = ", ".join(["%s"] * len(filtered_values))
+                sql_parts.append(f"{lhs} IN ({placeholders})")
+                params.extend(lhs_params + filtered_values)
+
+            if has_none:
+                sql_parts.append(f"{lhs} IS NULL")
+
+            return " OR ".join(sql_parts), params
+
         return super().as_sql(compiler, connection)
 
     def split_parameter_list_as_sql(self, compiler, connection):
diff --git a/tests/queries/tests.py b/tests/queries/tests.py
@@ -4615,3 +4615,15 @@ def test_ticket_23622(self):
             set(Ticket23605A.objects.filter(qy).values_list("pk", flat=True)),
         )
         self.assertSequenceEqual(Ticket23605A.objects.filter(qx), [a2])
+
+
+class ExcludedNoneInTest(TestCase):
+    def setUp(self):
+        Tag.objects.create(name="null", parent_id=None)
+        Tag.objects.create(name="one", parent_id=1)
+        Tag.objects.create(name="two", parent_id=2)
+
+    def test_exclude_in_with_none(self):
+        qs = Tag.objects.exclude(parent_id__in=[None, 1])
+        values = list(qs.values_list("parent_id", flat=True))
+        self.assertEqual(values, [2])
