#18583: exclude generates wrong SQL query
----------------------------------------------+----------------------------
Reporter: rbreu | Owner: nobody
Type: Uncategorized | Status: new
Component: Database layer (models, ORM) | Version: 1.4
Severity: Normal | Keywords: query exclude
Triage Stage: Unreviewed | sql
Easy pickings: 0 | Has patch: 0
| UI/UX: 0
----------------------------------------------+----------------------------
exclude generates a wrong SQL query in a certain setup. Given the
following models:
{{{#!python
class A(models.Model):
pass
class AGroups(models.Model):
a = models.ForeignKey(A, related_name='groups')
group = models.ForeignKey(Group)
class B(models.Model):
a = models.ForeignKey(A)
class C(models.Model):
b = models.ForeignKey(B)
}}}
Then the following:
{{{#!python
g = Group.objects.create(name='foo')
C.objects.exclude(b__a__groups__group__in=[g])
}}}
generates the following query:
{{{
SELECT "books_c"."id", "books_c"."name", "books_c"."b_id" FROM "books_c"
INNER JOIN "books_b" ON ("books_c"."b_id" = "books_b"."id")
INNER JOIN "books_a" ON ("books_b"."a_id" = "books_a"."id")
WHERE NOT (("books_b"."a_id" IN (
SELECT U1."id" FROM "books_b" U1
INNER JOIN "books_a" U2 ON (U1."a_id" = U2."id")
INNER JOIN "books_agroups" U3 ON (U2."id" = U3."a_id")
WHERE (U3."group_id" IN (3) AND U1."id" IS NOT NULL)
)
AND "books_a"."id" IS NOT NULL)) LIMIT 21
}}}
Line 4 should be
{{{
WHERE NOT (("books_b"."id" IN (
}}}
instead.
The same issue happens with
{{{#!python
agroups = AGroups.objects.filter(group__in=[g])
C.objects.exclude(b__a__groups__in=agroups)
}}}
This one works:
{{{#!python
b_excludes = B.objects.filter(a__groups__group__in=[g])
C.objects.exclude(b__in=b_excludes)
}}}
(Using Django 1.4, Python 2.7.3rc2, sqlite)
See attachment for more testing code.
--
Ticket URL: <https://code.djangoproject.com/ticket/18583>
Django <https://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 django-updates@googlegroups.com.
To unsubscribe from this group, send email to
django-updates+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/django-updates?hl=en.
