[4.2.x] Fixed #34840 -- Avoided casting string base fields on PostgreSQL.

Thanks Alex Vandiver for the report.

Regression in 09ffc5c1212d4ced58b708cbbf3dfbfb77b782ca.

Backport of 779cd28acb1f7eb06f629c0ea4ded99b5ebb670a from main.

6 files changed, 78 insertions, 15 deletions

diff --git a/django/db/backends/postgresql/operations.py b/django/db/backends/postgresql/operations.py
index 18cfcb29cb..c4d90b56ab 100644
--- a/django/db/backends/postgresql/operations.py
+++ b/django/db/backends/postgresql/operations.py
@@ -153,17 +153,6 @@ class DatabaseOperations(BaseDatabaseOperations):
 
     def lookup_cast(self, lookup_type, internal_type=None):
         lookup = "%s"
-
-        if lookup_type == "isnull" and internal_type in (
-            "CharField",
-            "EmailField",
-            "TextField",
-            "CICharField",
-            "CIEmailField",
-            "CITextField",
-        ):
-            return "%s::text"
-
         # Cast text lookups to text to allow things like filter(x__contains=4)
         if lookup_type in (
             "iexact",
diff --git a/django/db/models/lookups.py b/django/db/models/lookups.py
index d3697b2003..a4729c640a 100644
--- a/django/db/models/lookups.py
+++ b/django/db/models/lookups.py
@@ -568,11 +568,15 @@ class IsNull(BuiltinLookup):
             raise ValueError(
                 "The QuerySet value for an isnull lookup must be True or False."
             )
-        if isinstance(self.lhs, Value) and self.lhs.value is None:
-            if self.rhs:
-                raise FullResultSet
+        if isinstance(self.lhs, Value):
+            if self.lhs.value is None or (
+                self.lhs.value == ""
+                and connection.features.interprets_empty_strings_as_nulls
+            ):
+                result_exception = FullResultSet if self.rhs else EmptyResultSet
             else:
-                raise EmptyResultSet
+                result_exception = EmptyResultSet if self.rhs else FullResultSet
+            raise result_exception
         sql, params = self.process_lhs(compiler, connection)
         if self.rhs:
             return "%s IS NULL" % sql, params
diff --git a/docs/releases/4.2.6.txt b/docs/releases/4.2.6.txt
index 23d5f2a04f..d89b5ff6b1 100644
--- a/docs/releases/4.2.6.txt
+++ b/docs/releases/4.2.6.txt
@@ -12,3 +12,13 @@ Bugfixes
 * Fixed a regression in Django 4.2.5 where overriding the deprecated
   ``DEFAULT_FILE_STORAGE`` and ``STATICFILES_STORAGE`` settings in tests caused
   the main ``STORAGES`` to mutate (:ticket:`34821`).
+
+* Fixed a regression in Django 4.2 that caused unnecessary casting of string
+  based fields (``CharField``, ``EmailField``, ``TextField``, ``CICharField``,
+  ``CIEmailField``, and ``CITextField``) used with the ``__isnull`` lookup on
+  PostgreSQL. As a consequence, the pre-Django 4.2 indexes didn't match and
+  were not used by the query planner (:ticket:`34840`).
+
+  You may need to recreate indexes propagated to the database with Django
+  4.2 - 4.2.5 as they contain unnecessary ``::text`` casting that is avoided as
+  of this release.
diff --git a/tests/backends/postgresql/tests.py b/tests/backends/postgresql/tests.py
index 947d51ea1e..3a1d2da35d 100644
--- a/tests/backends/postgresql/tests.py
+++ b/tests/backends/postgresql/tests.py
@@ -376,6 +376,20 @@ class Tests(TestCase):
                         "::citext", do.lookup_cast(lookup, internal_type=field_type)
                     )
 
+    def test_lookup_cast_isnull_noop(self):
+        from django.db.backends.postgresql.operations import DatabaseOperations
+
+        do = DatabaseOperations(connection=None)
+        # Using __isnull lookup doesn't require casting.
+        tests = [
+            "CharField",
+            "EmailField",
+            "TextField",
+        ]
+        for field_type in tests:
+            with self.subTest(field_type=field_type):
+                self.assertEqual(do.lookup_cast("isnull", field_type), "%s")
+
     def test_correct_extraction_psycopg_version(self):
         from django.db.backends.postgresql.base import Database, psycopg_version
 
diff --git a/tests/constraints/tests.py b/tests/constraints/tests.py
index 5f59b3a47e..8578aa85d5 100644
--- a/tests/constraints/tests.py
+++ b/tests/constraints/tests.py
@@ -779,6 +779,42 @@ class UniqueConstraintTests(TestCase):
             exclude={"name"},
         )
 
+    def test_validate_nullable_textfield_with_isnull_true(self):
+        is_null_constraint = models.UniqueConstraint(
+            "price",
+            "discounted_price",
+            condition=models.Q(unit__isnull=True),
+            name="uniq_prices_no_unit",
+        )
+        is_not_null_constraint = models.UniqueConstraint(
+            "price",
+            "discounted_price",
+            condition=models.Q(unit__isnull=False),
+            name="uniq_prices_unit",
+        )
+
+        Product.objects.create(price=2, discounted_price=1)
+        Product.objects.create(price=4, discounted_price=3, unit="ng/mL")
+
+        msg = "Constraint “uniq_prices_no_unit” is violated."
+        with self.assertRaisesMessage(ValidationError, msg):
+            is_null_constraint.validate(
+                Product, Product(price=2, discounted_price=1, unit=None)
+            )
+        is_null_constraint.validate(
+            Product, Product(price=2, discounted_price=1, unit="ng/mL")
+        )
+        is_null_constraint.validate(Product, Product(price=4, discounted_price=3))
+
+        msg = "Constraint “uniq_prices_unit” is violated."
+        with self.assertRaisesMessage(ValidationError, msg):
+            is_not_null_constraint.validate(
+                Product,
+                Product(price=4, discounted_price=3, unit="μg/mL"),
+            )
+        is_not_null_constraint.validate(Product, Product(price=4, discounted_price=3))
+        is_not_null_constraint.validate(Product, Product(price=2, discounted_price=1))
+
     def test_name(self):
         constraints = get_constraints(UniqueConstraintProduct._meta.db_table)
         expected_name = "name_color_uniq"
diff --git a/tests/lookup/tests.py b/tests/lookup/tests.py
index ab3d968aac..9ab8eb87e6 100644
--- a/tests/lookup/tests.py
+++ b/tests/lookup/tests.py
@@ -1309,6 +1309,16 @@ class LookupTests(TestCase):
                 with self.assertRaisesMessage(ValueError, msg):
                     qs.exists()
 
+    def test_isnull_textfield(self):
+        self.assertSequenceEqual(
+            Author.objects.filter(bio__isnull=True),
+            [self.au2],
+        )
+        self.assertSequenceEqual(
+            Author.objects.filter(bio__isnull=False),
+            [self.au1],
+        )
+
     def test_lookup_rhs(self):
         product = Product.objects.create(name="GME", qty_target=5000)
         stock_1 = Stock.objects.create(product=product, short=True, qty_available=180)