id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
8597	PostgreSQL: r8536 broke iexact lookups with underscores	rene.puls@…	nobody	"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.

"		closed	Database layer (models, ORM)	dev		fixed			Accepted	0	0	0	0	0	0