GenericRelation query with OR creates incorrect SQL

[brianglass]

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

[brianglass]

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

[dc]

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

[Tobias McNulty]

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

[Tobias McNulty]

test for this bug added in ​http://bitbucket.org/tobias.mcnulty/django-trunk-tobiasmcnulty/changeset/2ec8de3c0e54/

[Tobias McNulty]

patch with test demonstrating problem and proposed fix

[Tobias McNulty]

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

[Alex Gaynor]

I concur with tobias, this patch appears RFC.

[Karen Tracey]

(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.

[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.

[Jacob]

Milestone 1.2 deleted