PostgreSQL: r8536 broke iexact lookups with underscores

[rene.puls@…]

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.

[Malcolm Tredinnick]

(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]).

[Jacob]

Milestone 1.0 deleted