Incorrect use of IEXACT in manipulator_validator_unique_together

[Scott Moonen <smoonen@…>]

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!

[adamfast@…]

This patch worked for me (getting the same error). I'm not sure what revision the original reporter was using, but I've tried it on latest svn (Rev 7722) and the last pre-qsrf merge (Rev 7476).

Mac OS 10.5, Python 2.5 (default) and Postgres 8.3.1.

[adamfast@…]

Created patch from instructions inline in the ticket

[adamfast@…]

Not sure what the farther-reaching ramifications of this are, but I have created a patch from the described technique.

[anonymous]

Same as #4562

This ticket seems to be the same as #4562 and considering it happened on Postgres 8.3, it is likely triggered by #6523.