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.