id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
11535	GenericRelation query with OR creates incorrect SQL	brianglass	Karen Tracey	"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
}}}
"		closed	Database layer (models, ORM)	dev		fixed		Tobias McNulty	Ready for checkin	1	0	0	0	0	0