Opened 6 years ago

Closed 5 years ago

Last modified 4 years ago

#11535 closed (fixed)

GenericRelation query with OR creates incorrect SQL

Reported by: brianglass Owned by: kmtracey
Component: Database layer (models, ORM) Version: master
Severity: Keywords:
Cc: tobias 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 5 years ago.
patch with test demonstrating problem and proposed fix

Download all attachments as: .zip

Change History (13)

comment:1 Changed 6 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 6 years ago by dc

  • milestone changed from 1.1 to 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 Changed 5 years ago by tobias

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

comment:4 Changed 5 years ago by tobias

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

Changed 5 years ago by tobias

patch with test demonstrating problem and proposed fix

comment:6 Changed 5 years ago by tobias

  • Has patch set
  • Triage Stage changed from Unreviewed to Accepted

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

comment:7 Changed 5 years ago by Alex

  • Triage Stage changed from Accepted to Ready for checkin

I concur with tobias, this patch appears RFC.

comment:8 Changed 5 years ago by tobias

  • Owner changed from tobias to kmtracey
  • Status changed from assigned to new

comment:9 Changed 5 years ago by tobias

  • Cc tobias added

comment:10 Changed 5 years ago by kmtracey

  • Resolution set to fixed
  • Status changed from new to 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 Changed 5 years ago by kmtracey

(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 4 years ago by jacob

  • milestone 1.2 deleted

Milestone 1.2 deleted

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