Clarify the operator each database backend uses for text lookups (startswith, istartswith, contains, icontains, endswith and iendswith)

[a_nekhaychik@…]

istartswith

On the page: ​https://docs.djangoproject.com/en/dev/ref/models/querysets/#field-lookups we can read:

istartswith
Case-insensitive starts-with.
Example:
Entry.objects.filter(headlineistartswith='will')
SQL equivalent:
SELECT ... WHERE headline ILIKE 'Will%';

But there is no db backend which uses ILIKE. LIKE will be used for MySQL.

Again if you assume MySQL by default in documentation then:

startswith
Case-sensitive starts-with.
Example:
Entry.objects.filter(headlinestartswith='Will')
SQL equivalent:
SELECT ... WHERE headline LIKE 'Will%';

is incorrect because LIKE BINARY will be used.

The same for contains, icontains, endswith and iendswith.

[Sasha Romijn]

But the documentation speaks of an SQL equivalent, not of the SQL that the ORM query would produce.

The equivalent is provided as "this is how you would do a query like this in SQL without the Django ORM", probably so that it's easier for people familiar with SQL to understand the ORM syntax. In other words, I see no error in the documentation here.

[a_nekhaychik@…]

But it is really confuses. If reader knows SQL very well, he knows that there is big performance difference between LIKE, LIKE BINARY and ILIKE.

And let's scroll down to regex section:

regex
.........
Example:
Entry.objects.get(titleregex=r'^{(An?|The) +')}

SQL equivalents:
SELECT ... WHERE title REGEXP BINARY '^{(An?|The) +'; -- MySQL
SELECT ... WHERE REGEXP_LIKE(title, '}(an?|the) +', 'c'); -- Oracle
SELECT ... WHERE title ~ '^{(An?|The) +'; -- PostgreSQL
SELECT ... WHERE title REGEXP '}(An?|The) +'; -- SQLite

We also see here "SQL equivalents" but for concrete backend.

If you insist that current description is correct, I'd recommend add concrete examples for MySQL and PostgreSQL.

[Tim Graham]

I think it's probably fine to clarify the documentation. Hopefully this can be done in a fashion that's not overly verbose as I imagine most users won't care about these implementation details.

[Areski Belaid]

Right now the documentation is easy to read for new comers, and adding this will complicate the documentation awfully. I could agree that it may be confusing for SQL experts but they are more likely to understand what SQL equivalent means.

If the goal is to improve the documentation for people knowing very well SQL, then we could simply disclose that the "SQL equivalent" is not database or datatype specific.

[Jacob Walls]

​PR

Needs more concision, and linters are failing.

[Mohit Kumar]

PR: ​https://github.com/django/django/pull/20649

[Mohit Kumar]

Thanks for the review.

I’ve updated the PR to follow the contribution guidelines, made the section more concise, and filled in the PR checklist. The “Patch needs improvement” flag has been unset.

Replying to Jacob Walls:

​PR

Needs more concision, and linters are failing.

[Jacob Walls]

PR got further from the target during iterations.

[Mohit Kumar]

Thanks for the feedback.

I understand that the current PR drifted from the original goal of
clarifying backend-specific operators for text lookups.

I will revisit the ticket and update the documentation to explicitly
clarify backend behavior as originally requested.
Replying to Jacob Walls:

PR got further from the target during iterations.

[Lakshya Prasad]

Hi, I’d like to work on this ticket.

I’m planning to add a concise clarification that the SQL equivalents
shown in the documentation are representative examples and may vary
depending on the database backend, rather than listing operators
for each backend.

Please let me know if that approach aligns with expectations.

[Lakshya Prasad]

Submitted PR: ​https://github.com/django/django/pull/20681