Fixed #20024 -- Fixed handling of __in lookups with None in exclude().

Thanks Simon Charette and Tim Graham for reviews, and Jason Hall for a
prior iteration.

3 files changed, 29 insertions, 14 deletions

diff --git a/django/db/models/sql/query.py b/django/db/models/sql/query.py
index 7a4cf843c1..8be560856b 100644
--- a/django/db/models/sql/query.py
+++ b/django/db/models/sql/query.py
@@ -13,7 +13,7 @@ import functools
 import sys
 import warnings
 from collections import Counter, namedtuple
-from collections.abc import Iterator, Mapping
+from collections.abc import Iterable, Iterator, Mapping
 from itertools import chain, count, product
 from string import ascii_uppercase
 
@@ -1638,7 +1638,17 @@ class Query(BaseExpression):
                 ):
                     lookup_class = targets[0].get_lookup("isnull")
                     col = self._get_col(targets[0], join_info.targets[0], alias)
-                    clause.add(lookup_class(col, False), AND)
+                    # Use OR + IS NULL when RHS `in` values include None.
+                    if (
+                        lookup_type == "in"
+                        # Check containers (not strings or bytes).
+                        and isinstance(condition.rhs, Iterable)
+                        and not isinstance(condition.rhs, (str, bytes))
+                        and any(v is None for v in condition.rhs)
+                    ):
+                        clause.add(lookup_class(col, True), OR)
+                    else:
+                        clause.add(lookup_class(col, False), AND)
                 # If someval is a nullable column, someval IS NOT NULL is
                 # added.
                 if isinstance(value, Col) and self.is_nullable(value.target):
diff --git a/tests/composite_pk/test_filter.py b/tests/composite_pk/test_filter.py
index 617d8370db..fdaa323fd8 100644
--- a/tests/composite_pk/test_filter.py
+++ b/tests/composite_pk/test_filter.py
@@ -617,9 +617,20 @@ class CompositePKExcludeNoneTests(TestCase):
             tenant=cls.tenant, id=1, email="exclude@example.com"
         )
 
-    def test_pk_in_with_partial_or_all_none(self):
-        qs = User.objects
-        self.assertQuerySetEqual(qs.filter(pk__in=[(1, None)]), [])
-        self.assertQuerySetEqual(qs.filter(pk__in=[(None, None)]), [])
-        self.assertQuerySetEqual(qs.exclude(pk__in=[(1, None)]), [self.user])
-        self.assertQuerySetEqual(qs.exclude(pk__in=[(None, None)]), [self.user])
+    def test_filter_pk_in_partial_none(self):
+        self.assertQuerySetEqual(
+            User.objects.filter(pk__in=[(self.user.pk[0], None)]), []
+        )
+
+    def test_filter_pk_in_full_none(self):
+        self.assertQuerySetEqual(User.objects.filter(pk__in=[(None, None)]), [])
+
+    def test_exclude_pk_in_partial_none(self):
+        self.assertQuerySetEqual(
+            User.objects.exclude(pk__in=[(self.user.pk[0], None)]), [self.user]
+        )
+
+    def test_exclude_pk_in_full_none(self):
+        self.assertQuerySetEqual(
+            User.objects.exclude(pk__in=[(None, None)]), [self.user]
+        )
diff --git a/tests/queries/tests.py b/tests/queries/tests.py
index f2136df243..c00a78e2ed 100644
--- a/tests/queries/tests.py
+++ b/tests/queries/tests.py
@@ -3536,13 +3536,7 @@ class NullInExcludeTest(TestCase):
         # into subquery above
         self.assertIs(inner_qs._result_cache, None)
 
-    @unittest.expectedFailure
     def test_col_not_in_list_containing_null(self):
-        """
-        The following case is not handled properly because
-        SQL's COL NOT IN (list containing null) handling is too weird to
-        abstract away.
-        """
         self.assertQuerySetEqual(
             NullableName.objects.exclude(name__in=[None]), ["i1"], attrgetter("name")
         )