Opened 7 years ago

Closed 7 years ago

Last modified 5 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: master
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: UI/UX:

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 7 years ago.
patch with test demonstrating problem and proposed fix

Download all attachments as: .zip

Change History (13)

comment:1 Changed 7 years ago by brianglass

Needs documentation: unset
Needs tests: unset
Patch needs improvement: unset

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

comment:2 Changed 7 years ago by dc

milestone: 1.11.2

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

comment:3 Changed 7 years ago by Tobias McNulty

Owner: changed from nobody to Tobias McNulty
Status: newassigned

comment:4 Changed 7 years ago by Tobias McNulty

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

Changed 7 years ago by Tobias McNulty

Attachment: generic_relations_or.diff added

patch with test demonstrating problem and proposed fix

comment:6 Changed 7 years ago by Tobias McNulty

Has patch: set
Triage Stage: UnreviewedAccepted

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

comment:7 Changed 7 years ago by Alex Gaynor

Triage Stage: AcceptedReady for checkin

I concur with tobias, this patch appears RFC.

comment:8 Changed 7 years ago by Tobias McNulty

Owner: changed from Tobias McNulty to Karen Tracey
Status: assignednew

comment:9 Changed 7 years ago by Tobias McNulty

Cc: Tobias McNulty added

comment:10 Changed 7 years ago by Karen Tracey

Resolution: fixed
Status: newclosed

(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 Changed 7 years ago by Karen Tracey

(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 Changed 5 years ago by Jacob

milestone: 1.2

Milestone 1.2 deleted

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