Repository: django/django
Issue category: Performance Issue

Issue:
Make ModelAdmin.search_fields raise data errors on __exact lookups for non-string fields.
Description
	 
Currently, ​all queries are done as string lookups which gives something like this on PostgreSQL, for example: ("admin_views_pluggablesearchperson"."age"::text) = UPPER(20)). It would be more efficient if the admin cast the search value to an integer and used that for the query.


Hints:
['PR: \u200bhttps://github.com/django/django/pull/6219', 1456649223.0]
['I think we should favor the approach suggested in #26184 instead.', 1456652736.0]
["I agree, let's revisit this if it doesn't.", 1457113813.0]
["As far as I tested, you can now use something like search_fields = ['votes__exact'] to do the appropriate query, however, if a non-integer search term is entered, the page crashes with ValueError invalid literal for int() with base 10: 'abc' from IntegerField.get_prep_value(). Probably that exception should be caught in ModelAdmin.get_search_results() and no results returned.", 1532099527.0]
['PR: \u200bhttps://github.com/django/django/pull/10339', 1535321698.0]
["Replying to Tim Graham: As far as I tested, you can now use something like search_fields = ['votes__exact'] to do the appropriate query, however, if a non-integer search term is entered, the page crashes with ValueError invalid literal for int() with base 10: 'abc' from IntegerField.get_prep_value(). Probably that exception should be caught in ModelAdmin.get_search_results() and no results returned. We have to consider that there might be multiple search fields of mixed types though, i.e. search_fields = ['votes__exact', 'title', 'text__contains'], just so the matches for other fields does not get discarded just because one of the fields threw an error. Instead of trying to catch these errors from .filter() as in the previous patch, we could run to_python() for each search field in order to test if that particular field would raise an exception later on when the query is run.", 1593498353.0]
['#34191 was a duplicate for DecimalField.', 1669781193.0]
['Replying to Mariusz Felisiak: #34191 was a duplicate for DecimalField. You can use iexact instead of exact for non-string fields, I think, it even makes sense, because the word exact means complete equality even with type. It works for me.', 1671817064.0]
['can i work on this?', 1706431804.0]
['PR: \u200bhttps://github.com/django/django/pull/17885', 1708402891.0]

Patch:
diff --git a/django/contrib/admin/options.py b/django/contrib/admin/options.py
index 6d5c0708a322..78063a134d2a 100644
--- a/django/contrib/admin/options.py
+++ b/django/contrib/admin/options.py
@@ -41,6 +41,7 @@
 from django.core.paginator import Paginator
 from django.db import models, router, transaction
 from django.db.models.constants import LOOKUP_SEP
+from django.db.models.functions import Cast
 from django.forms.formsets import DELETION_FIELD_NAME, all_valid
 from django.forms.models import (
     BaseInlineFormSet,
@@ -1207,9 +1208,33 @@ def construct_search(field_name):
         may_have_duplicates = False
         search_fields = self.get_search_fields(request)
         if search_fields and search_term:
-            orm_lookups = [
-                construct_search(str(search_field)) for search_field in search_fields
-            ]
+            str_annotations = {}
+            orm_lookups = []
+            for field in search_fields:
+                if field.endswith("__exact"):
+                    field_name = field.rsplit("__exact", 1)[0]
+                    try:
+                        field_obj = queryset.model._meta.get_field(field_name)
+                    except FieldDoesNotExist:
+                        lookup = construct_search(field)
+                        orm_lookups.append(lookup)
+                        continue
+                    # Add string cast annotations for non-string exact lookups.
+                    if not isinstance(field_obj, (models.CharField, models.TextField)):
+                        str_annotations[f"{field_name}_str"] = Cast(
+                            field_name, output_field=models.CharField()
+                        )
+                        orm_lookups.append(f"{field_name}_str__exact")
+                    else:
+                        lookup = construct_search(field)
+                        orm_lookups.append(lookup)
+                else:
+                    lookup = construct_search(str(field))
+                    orm_lookups.append(lookup)
+
+            if str_annotations:
+                queryset = queryset.annotate(**str_annotations)
+
             term_queries = []
             for bit in smart_split(search_term):
                 if bit.startswith(('"', "'")) and bit[0] == bit[-1]:


Test patch:
diff --git a/tests/admin_changelist/admin.py b/tests/admin_changelist/admin.py
index 349ef7d465b6..d9dc498e8427 100644
--- a/tests/admin_changelist/admin.py
+++ b/tests/admin_changelist/admin.py
@@ -48,6 +48,7 @@ class ChildAdmin(admin.ModelAdmin):
     list_display = ["name", "parent"]
     list_per_page = 10
     list_filter = ["parent", "age"]
+    search_fields = ["age__exact", "name__exact"]
 
     def get_queryset(self, request):
         return super().get_queryset(request).select_related("parent")
diff --git a/tests/admin_changelist/tests.py b/tests/admin_changelist/tests.py
index d8055a809be2..a823a72f7d7f 100644
--- a/tests/admin_changelist/tests.py
+++ b/tests/admin_changelist/tests.py
@@ -860,6 +860,25 @@ def test_custom_lookup_with_pk_shortcut(self):
         cl = m.get_changelist_instance(request)
         self.assertCountEqual(cl.queryset, [abcd])
 
+    def test_search_with_exact_lookup_for_non_string_field(self):
+        child = Child.objects.create(name="Asher", age=11)
+        model_admin = ChildAdmin(Child, custom_site)
+
+        for search_term, expected_result in [
+            ("11", [child]),
+            ("Asher", [child]),
+            ("1", []),
+            ("A", []),
+            ("random", []),
+        ]:
+            request = self.factory.get("/", data={SEARCH_VAR: search_term})
+            request.user = self.superuser
+            with self.subTest(search_term=search_term):
+                # 1 query for filtered result, 1 for filtered count, 1 for total count.
+                with self.assertNumQueries(3):
+                    cl = model_admin.get_changelist_instance(request)
+                self.assertCountEqual(cl.queryset, expected_result)
+
     def test_no_distinct_for_m2m_in_list_filter_without_params(self):
         """
         If a ManyToManyField is in list_filter but isn't in any lookup params,