Opened 7 years ago

Closed 7 years ago

Last modified 4 years ago

#8597 closed (fixed)

PostgreSQL: r8536 broke iexact lookups with underscores

Reported by: rene.puls@… Owned by: nobody
Component: Database layer (models, ORM) Version: master
Severity: Keywords:
Cc: Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: UI/UX:

Description

Changeset [8536] changed the way field iexact lookups are translated in the PostgreSQL backend. Before the patch, these were translated to "ILIKE %s". Now it's "UPPER(field) = UPPER(%s)". However, the %s value is still escaped for use in a LIKE expression, that is, all underscores are prepended with "
".

Before the patch (correct):

>>> User.objects.filter(username__iexact='test_user').query.as_sql()
('SELECT [...] FROM "auth_user" WHERE "auth_user"."username"::text ILIKE %s ', (u'test\\_user',))

After the patch (wrong):

User.objects.filter(username__iexact='test_user').query.as_sql()
('SELECT [...] FROM "auth_user" WHERE UPPER("auth_user"."username"::text) = UPPER(%s) ', (u'test\\_user',))

As a result, iexact lookups no longer work in the current version with PostgreSQL, if the comparison value contains an underscore.

Change History (3)

comment:1 Changed 7 years ago by jacob

  • milestone set to 1.0
  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Triage Stage changed from Unreviewed to Accepted

comment:2 Changed 7 years ago by mtredinnick

  • Resolution set to fixed
  • Status changed from new to closed

(In [8646]) Fixed #8597 -- Allow the use of strings containing underscores and percentage
signs in "iexact" queries on PostgreSQL again (this case was missed in [8536]).

comment:3 Changed 4 years ago by jacob

  • milestone 1.0 deleted

Milestone 1.0 deleted

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