#11535: GenericRelation query with OR creates incorrect SQL
------------------------------------------+---------------------------------
Reporter: brianglass | Owner: nobody
Status: new | Milestone: 1.1
Component: Database layer (models, ORM) | Version: SVN
Keywords: | Stage: Unreviewed
Has_patch: 0 |
------------------------------------------+---------------------------------
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
}}}
--
Ticket URL: <http://code.djangoproject.com/ticket/11535>
Django <http://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/django-updates?hl=en
-~----------~----~----~----~------~----~------~--~---
