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
7197	Incorrect use of IEXACT in manipulator_validator_unique_together	Scott Moonen <smoonen@…>	anonymous	"The function manipulator_validator_unique_together uses IEXACT to check !ForeignKey relations.  Perhaps this works in MySQL, but in PostgreSQL, it results in the following query:

{{{
'SELECT ""users"".""id"", ""users"".""organization_id"", ""users"".""created_at"", ""users"".""updated_at"", ""users"".""last_login_at"",
""users"".""username"", ""users"".""password"", ""users"".""password_salt"", ""users"".""is_active"", ""users"".""is_org_admin"", 
""users"".""first_name"", ""users"".""last_name"", ""users"".""email"", ""users"".""tz"", ""users"".""locale"", ""users"".""home_phone"", 
""users"".""work_phone"", ""users"".""mobile_phone"" FROM ""users"" INNER JOIN ""organizations"" ON (""users"".""organization_id"" = 
""organizations"".""id"") WHERE ""users"".""organization_id"" ILIKE \'2\' AND ""users"".""username"" ILIKE \'badkins\' ORDER BY 
""organizations"".""short_name"" ASC, ""users"".""username"" ASC'
}}}

which results in the exception !ProgrammingError: operator does not exist: integer unknown at character 513 HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. 

The problem occurs on the ""users"".""organization_id"" ILIKE \'2\'.  Since organization_id is an integer foreign key, PostgreSQL is complaining that we are trying to pass <int, text> to the ILIKE operator, which expects parameters of type <text, text>.  In my case, the organization_id field is a simple !ForeignKey in the User model that points to the Organization model.  For what it's worth, the unique_together value for my User model is: ('organization', 'username').

I notice that manipulator_validator_unique_together treats fields![0] differently from fields[1:].  I think that the treatment for fields[1:] is correct.  In my working copy I changed this line:

{{{
        kwargs = {'%s__%s__iexact' % (field_name_list[0], field_list[0].rel.field_name): field_data}
}}}

to this instead:

{{{
        kwargs = {'%s__pk' % field_name_list[0]: field_data}
}}}

and it seems to work correctly.  I am certainly not well-versed in the workings of the database wrapper, so it's possible my change is not quite correct.

Thanks in advance for looking into this and resolving it!
"		closed	Database layer (models, ORM)	dev		duplicate		canburak@… peter.kese@…	Unreviewed	1	0	0	0	0	0