Opened 8 years ago

Closed 8 years ago

Last modified 5 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 8 years ago by Jacob

milestone: 1.0
Needs documentation: unset
Needs tests: unset
Patch needs improvement: unset
Triage Stage: UnreviewedAccepted

comment:2 Changed 8 years ago by Malcolm Tredinnick

Resolution: fixed
Status: newclosed

(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 5 years ago by Jacob

milestone: 1.0

Milestone 1.0 deleted

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