Opened 3 years ago

Closed 2 years ago

#18583 closed Bug (fixed)

exclude generates wrong SQL query

Reported by: rbreu Owned by: nobody
Component: Database layer (models, ORM) Version: 1.4
Severity: Normal Keywords: query exclude sql sprints-django-ar
Cc: Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

exclude generates a wrong SQL query in a certain setup. Given the following models:

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:

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

agroups = AGroups.objects.filter(group__in=[g])
C.objects.exclude(b__a__groups__in=agroups)

This one works:

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.

Attachments (2)

models.py (3.9 KB) - added by rbreu 3 years ago.
18583.diff (3.3 KB) - added by fgallina 2 years ago.

Download all attachments as: .zip

Change History (5)

Changed 3 years ago by rbreu

comment:1 Changed 3 years ago by judy2k

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Triage Stage changed from Unreviewed to Accepted
  • Type changed from Uncategorized to Bug

I've managed to reproduce this in the HEAD

Changed 2 years ago by fgallina

comment:2 Changed 2 years ago by fgallina

This bug is invalid, as the generated SQL is correct.

Given this full example:

    #models 
    from django.utils.encoding import python_2_unicode_compatible
    from django.contrib.auth.models import Group


    # Models for #19659
    @python_2_unicode_compatible
    class A(models.Model):
        pass

        def __str__(self):
            return str(self.pk)

    @python_2_unicode_compatible
    class B(models.Model):
        a = models.ForeignKey(A)

        def __str__(self):
            return str(self.pk)

    @python_2_unicode_compatible
    class C(models.Model):
        b = models.ForeignKey(B)

        def __str__(self):
            return str(self.pk)

    @python_2_unicode_compatible
    class D(models.Model):
        a = models.ForeignKey(A)
        group = models.ForeignKey(Group)

        def __str__(self):
            return str(self.pk)

The following query:

C.objects.exclude(b__a__d__group__in=[foo]).order_by('pk')

Results in:

    SELECT "queries_c"."id", "queries_c"."b_id" FROM "queries_c"
       INNER JOIN "queries_b" ON ("queries_c"."b_id" = "queries_b"."id")
       WHERE NOT (("queries_b"."a_id" IN
                   (SELECT U3."a_id" FROM "queries_d" U3 WHERE U3."group_id" IN (1))
                   AND "queries_b"."a_id" IS NOT NULL))

Which is correct, as the filtering is taken to the a.id field (see the inner SELECT).

Attached is a diff which adds tests showing the query is returning expected results.

Last edited 2 years ago by fgallina (previous) (diff)

comment:3 Changed 2 years ago by fgallina

  • Keywords sprints-django-ar added
  • Resolution set to fixed
  • Status changed from new to closed

this shows as working in current HEAD.

Note: See TracTickets for help on using tickets.
Back to Top