Opened 15 years ago

Closed 14 years ago

Last modified 13 years ago

#11535 closed (fixed)

GenericRelation query with OR creates incorrect SQL

Reported by: brianglass Owned by: Karen Tracey
Component: Database layer (models, ORM) Version: dev
Severity: Keywords:
Cc: Tobias McNulty Triage Stage: Ready for checkin
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

Example:

Contact.objects.filter( Q(organizations__name__iregex=r'\mtiftosi\w{0,4}\M') | Q(notes__note__icontains=r'tiftosi') )

Notes:

organizations is manytomany related to Contact. notes is a GenericRelation from Contact to Note. Also note (and I think this is probably irrelevant) that the GenericRelation comes from a parent class Source.

The above query returns 0 rows when in reality it should return 1 row. It produces the following SQL (PostgreSQL backend):

SELECT "contacts_contact"."id", "contacts_contact"."tags", "contacts_contact"."privacy", "contacts_contact"."first_name", "contacts_contact"."first_name_phonetic", "contacts_contact"."middle_name", "contacts_contact"."last_name", "contacts_contact"."last_name_phonetic", "contacts_contact"."suffix_or_prefix", "contacts_contact"."photo", "contacts_contact"."retired", "contacts_contact"."vetted", "contacts_contact"."birth_date", "contacts_contact"."death_date", "contacts_contact"."home_city", "contacts_contact"."home_state", "contacts_contact"."home_country" 
FROM "contacts_contact" 
LEFT OUTER JOIN "contacts_organizationmembership" ON ("contacts_contact"."id" = "contacts_organizationmembership"."contact_id") 
LEFT OUTER JOIN "contacts_organization" ON ("contacts_organizationmembership"."organization_id" = "contacts_organization"."id") 
LEFT OUTER JOIN "contacts_note" ON ("contacts_contact"."id" = "contacts_note"."object_id") 
WHERE (
("contacts_organization"."name" ~* E'\\mtiftosi\\w{0,4}\\M'  
OR "contacts_note"."note" ~* E'\\mtiftosi\\w{0,4}\\M' ) 

AND "contacts_note"."content_type_id" = 28 ) 

ORDER BY "contacts_contact"."last_name" ASC, "contacts_contact"."first_name" ASC LIMIT 21

The contacts_note.content_type_id=28 qualification should be in parens with the contacts_note.note regex qualification. Making this change produces the correct results. Moving the first opening paren of the WHERE clause to just after the first OR fixes the problem. The correct SQL should look something like:

SELECT "contacts_contact"."id", "contacts_contact"."tags", "contacts_contact"."privacy", "contacts_contact"."first_name", "contacts_contact"."first_name_phonetic", "contacts_contact"."middle_name", "contacts_contact"."last_name", "contacts_contact"."last_name_phonetic", "contacts_contact"."suffix_or_prefix", "contacts_contact"."photo", "contacts_contact"."retired", "contacts_contact"."vetted", "contacts_contact"."birth_date", "contacts_contact"."death_date", "contacts_contact"."home_city", "contacts_contact"."home_state", "contacts_contact"."home_country" 
FROM "contacts_contact" 
LEFT OUTER JOIN "contacts_organizationmembership" ON ("contacts_contact"."id" = "contacts_organizationmembership"."contact_id") 
LEFT OUTER JOIN "contacts_organization" ON ("contacts_organizationmembership"."organization_id" = "contacts_organization"."id") 
LEFT OUTER JOIN "contacts_note" ON ("contacts_contact"."id" = "contacts_note"."object_id") 
WHERE (
"contacts_organization"."name" ~* E'\\mtiftosi\\w{0,4}\\M'  
OR ("contacts_note"."note" ~* E'\\mtiftosi\\w{0,4}\\M' ) 

AND "contacts_note"."content_type_id" = 28 ) 

ORDER BY "contacts_contact"."last_name" ASC, "contacts_contact"."first_name" ASC LIMIT 21

Attachments (1)

generic_relations_or.diff​ (5.1 KB ) - added by Tobias McNulty 14 years ago.
patch with test demonstrating problem and proposed fix

Download all attachments as: .zip

Change History (13)

comment:1 by brianglass, 15 years ago

I just discovered a workaround. After switching the two terms in the |, the query works correctly.

comment:2 by dc, 15 years ago

milestone: 1.1 → 1.2

Please provide detailed steps to reproduce this bug (including models). Step by step described bug is easy to reproduce and fix.

comment:3 by Tobias McNulty, 14 years ago

Owner: changed from nobody to Tobias McNulty
Status: new → assigned

comment:4 by Tobias McNulty, 14 years ago

I think I've seen this one before. I'll see if I can provide a test and potentially a fix.

by Tobias McNulty, 14 years ago

Attachment: generic_relations_or.diff​ added

patch with test demonstrating problem and proposed fix

comment:6 by Tobias McNulty, 14 years ago

Has patch: set
Triage Stage: Unreviewed → Accepted

This is probably Ready for checkin; all tests pass after the patch is applied.

comment:7 by Alex Gaynor, 14 years ago

Triage Stage: Accepted → Ready for checkin

I concur with tobias, this patch appears RFC.

comment:8 by Tobias McNulty, 14 years ago

Owner: changed from Tobias McNulty to Karen Tracey
Status: assigned → new

comment:9 by Tobias McNulty, 14 years ago

Cc: Tobias McNulty added

comment:10 by Karen Tracey, 14 years ago

Resolution: → fixed
Status: new → closed

(In [12405]) Fixed #11535: Corrected SQL generated for queries involving generic relations and ORed Q objects. Thanks to brianglass for report, tobias for fix and test, and Alex for review.

comment:11 by Karen Tracey, 14 years ago

(In [12406]) [1.1.X] Fixed #11535: Corrected SQL generated for queries involving generic relations and ORed Q objects. Thanks to brianglass for report, tobias for fix and test, and Alex for review.

r12405 from trunk.

comment:12 by Jacob, 13 years ago

milestone: 1.2

Milestone 1.2 deleted

Note: See TracTickets for help on using tickets.
Back to Top