#10478: Multiple querysets as rvalues generating bad SQL
------------------------------------------+---------------------------------
Reporter: mtredinnick | Owner: mtredinnick
Status: new | Milestone: 1.1
Component: Database layer (models, ORM) | Version: SVN
Keywords: | Stage: Unreviewed
Has_patch: 0 |
------------------------------------------+---------------------------------
Using these models for illustration:
{{{
#!python
class Person(models.Model):
name = models.CharField(max_length=50)
class Group(models.Model):
name = models.CharField(max_length=50)
people = models.ManyToManyField(Person, related_name="groups")
}}}
If `people` is a queryset for `Person` objects, then this is a valid
construction (in theory):
{{{
#!python
person_count = people.annotate(num=models.Count("id")).values("num")
groups = Group.objects.filter(people__in=people). \
annotate(num_people=Count("people")). \
filter(num_people=person_count)
}}}
Problem is, it gives the wrong answer. The SQL for the `person_count`
inner query uses the same alias as the SQL for the `people` inner query.
{{{
#!sql
SELECT "example_group"."id", "example_group"."name",
COUNT("example_group_people"."person_id") AS "num"
FROM "example_group"
LEFT OUTER JOIN "example_group_people"
ON ("example_group"."id" = "example_group_people"."group_id")
WHERE "example_group_people"."person_id" IN (
SELECT U0."id"
FROM "example_person" U0
WHERE U0."name" IN (%s, %s))
GROUP BY "example_group"."id", "example_group"."name"
HAVING COUNT("example_group_people"."person_id") = (
SELECT COUNT(U0."id") AS "num_x"
FROM "example_person" U0
WHERE U0."name" IN (%s, %s) GROUP BY U0."id", U0."name")
}}}
This looks potentially fiddly to fix. Certainly need to rearrange the
parameters to some calls, by the looks of it. To avoid having to store
complex objects (pickling problems), we compute the SQL when the rvalue is
inserted into the filter and it has no way of passing back the aliases it
uses to the main query. We use `get_db_prep_lookup()` to do that SQL
conversion, which has a bit of a limited interface. Overloading that use
may not be the right way.
Should be fixable, but I need to think about it. Mostly creating a bug so
that it's noted as a known issue.
--
Ticket URL: <http://code.djangoproject.com/ticket/10478>
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
-~----------~----~----~----~------~----~------~--~---
