Code

Opened 6 years ago

Closed 6 years ago

#7197 closed (duplicate)

Incorrect use of IEXACT in manipulator_validator_unique_together

Reported by: Scott Moonen <smoonen@…> Owned by: anonymous
Component: Database layer (models, ORM) Version: master
Severity: Keywords:
Cc: canburak@…, peter.kese@… Triage Stage: Unreviewed
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: UI/UX:

Description

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!

Attachments (2)

ticket_7197.patch (790 bytes) - added by adamfast@… 6 years ago.
Created patch from instructions inline in the ticket
ticket_7197.2.patch (687 bytes) - added by anonymous 6 years ago.

Download all attachments as: .zip

Change History (10)

comment:1 Changed 6 years ago by anonymous

  • Cc canburak@… added
  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset

comment:2 Changed 6 years ago by 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.

Changed 6 years ago by adamfast@…

Created patch from instructions inline in the ticket

comment:3 Changed 6 years ago by adamfast@…

  • Has patch set

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

comment:4 Changed 6 years ago by anonymous

  • Owner changed from nobody to anonymous
  • Status changed from new to assigned

comment:5 Changed 6 years ago by anonymous

  • Owner changed from anonymous to adamfast
  • Status changed from assigned to new

Changed 6 years ago by anonymous

comment:6 Changed 6 years ago by anonymous

  • Owner changed from adamfast to anonymous

comment:7 Changed 6 years ago by anonymous

  • Cc peter.kese@… added

comment:8 Changed 6 years ago by anonymous

  • Resolution set to duplicate
  • Status changed from new to closed

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.

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as closed
as The resolution will be set. Next status will be 'closed'
The resolution will be deleted. Next status will be 'new'
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.