Opened 2 years ago

Last modified 10 months ago

#20775 new Cleanup/optimization

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

Reported by: a_nekhaychik@… Owned by: nobody
Component: Documentation Version: 1.5
Severity: Normal Keywords:
Cc: a_nekhaychik@…, eromijn@… Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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.

Change History (5)

comment:1 Changed 2 years ago by anonymous

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Summary changed from Incorrect description of istartswith to Incorrect description of startswith, istartswith, contains, icontains, endswith and iendswith

comment:2 Changed 2 years ago by erikr

  • Cc eromijn@… added
  • Resolution set to invalid
  • Status changed from new to closed

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.

comment:3 Changed 2 years ago by a_nekhaychik@…

  • Resolution invalid deleted
  • Status changed from closed to new

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.

comment:4 Changed 2 years ago by timo

  • Summary changed from Incorrect description of startswith, istartswith, contains, icontains, endswith and iendswith to Clarify the operator each database backend uses for text lookups (startswith, istartswith, contains, icontains, endswith and iendswith)
  • Triage Stage changed from Unreviewed to Accepted
  • Type changed from Bug to Cleanup/optimization

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.

comment:5 Changed 10 months ago by areski

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.

Note: See TracTickets for help on using tickets.
Back to Top