Casting on __exact searches over non-text fields in admin misses indexes

[Mike Lissner]

https://code.djangoproject.com/ticket/26001 from 10 years ago was fixed in PR ​https://github.com/django/django/pull/17885/, which was released in Django 5.2.

Unfortunately, this fix casts all admin search queries to varchar, which makes the query miss the DB index instead of using it. In large tables, this results in the query timing out, as documented as a comment on the original issue:

https://code.djangoproject.com/ticket/26001#comment:24

And in my project's issue tracker:

​https://github.com/freelawproject/courtlistener/issues/6790

We're fixing this in our system by patching the get_search_results method:

​https://github.com/freelawproject/courtlistener/pull/6792

I'm not at all familiar with this part of Django, but I spent some time with claude and I think I have a fix here:

​https://github.com/django/django/pull/20538

I did my best to be surgical and to add tests, but, again, this is foreign stuff to me. I hope this helps.

[Simon Charette]

​This problem was discussed in a follow up MR to #26001.

As pointed out on ​the proposed patch (which you should prefix with #36865 to be automatically linked here Mike) using either field.to_python (or field.formfield().to_python as we're dealing with untrusted user input) can likely be used as a signal to determine if a cast is necessary.

I'm also wondering why we even search against cast'ed to text integer field when provided a string not composed of digits (AKA when it fails to_python) when using the exact lookup. It seems like the logic could be adjusted the other way around by avoiding exact lookups for search terms that search fields fail to_python and we could drop the casting altogether.

e.g.

class Author(models.Model):
    name = models.CharField()
    dob = models.DateField()

class AuthorAdmin(models.ModelAdmin):
    search_fields = ["name", "dob"]

then a search of the form ?q=foo 2010-01-15 should result in

filter(
    Q(name="foo")
    | Q(name="2010-01-15")
    | Q(dob=date(2010, 1, 15)
)

Where we don't even try dob_str="foo" as DateField.formfield().to_python("foo") identity that the value is not valid for this field and thus cannot be an exact match.

[Mike Lissner]

Thanks. I've updated the title of the PR so it links here (magic?) and I've pushed a commit with the change you suggest here. I agree — much better. Again, I've had a lot of help from Claude on this, so hopefully the code is good. It's a change I wouldn't have dared a few months ago, but I'm hopeful will be good enough now.

I see this ticket says it needs tests and that it needs improvement. I'm assuming it's up to maintainers to uncheck those, yes?

[Antoliny]

Replying to Mike Lissner:

I see this ticket says it needs tests and that it needs improvement. I'm assuming it's up to maintainers to uncheck those, yes?

Thank you Mike!
Once you've completed your work, you can switch "needs tests" and "patch needs improvement" to no.
When "needs tests", "needs patch improvement", and "needs documentation" are all set to no, it will be added to the review queue and can proceed after being reviewed by maintainers or reviewers.
I think this ​document might be helpful :)

[Mike Lissner]

This should be ready for review again.

[Mike Lissner]

Tweaks made, pushed. Ready for more review, bosses.

[Jacob Walls <jacobtylerwalls@…>]

In b25bc24:

Refs #36865 -- Added test for invalid value handling in admin changelist.

[Jacob Walls <jacobtylerwalls@…>]

In 4cecf303:

Fixed #36865 -- Removed casting from exact lookups in admin searches.

Instead of casting non-text fields to CharField (which prevents index
usage), skip exact lookups when the search term fails
formfield.to_python().

This preserves index usage for valid searches while gracefully handling
invalid search terms by simply not including them in the query for that
field.

For multi-term searches like 'foo 123' on search_fields=['name', 'ageexact']:

• 'foo': invalid for age, so only name lookup is used
• '123': valid for both, so both lookups are used

This entails a slight increase in permissiveness for search terms that
can be normalized by formfield.to_python().

[Jacob Walls <jacobtylerwalls@…>]

In 674eda1c:

Refs #36865 -- Fixed test_exact_lookup_validates_each_field_independently() crash on databases that don't support primitives in JSONFields.

For example on Oracle < 21c.