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
20788	exclude makes bad query following two FKs and a M2M	david@…	nobody	"Hi,

With models like this:

{{{
from django.db import models


class Publication(models.Model):
    pass


class Article(models.Model):
    publications = models.ManyToManyField(Publication)


class Section(models.Model):
    article = models.ForeignKey(Article)


class Sentence(models.Model):
    section = models.ForeignKey(Section)
}}}

The query to get all sentences that aren't in publication 1 looks like:

{{{
>>> models.Sentence.objects.exclude(section__article__publications__id=1)
}}}

However, this generates the following SQL:

{{{
SELECT ""marbury_sentence"".""id"", ""marbury_sentence"".""section_id""

FROM ""marbury_sentence""
    INNER JOIN ""marbury_section"" ON (""marbury_sentence"".""section_id"" = ""marbury_section"".""id"")
    INNER JOIN ""marbury_article"" ON (""marbury_section"".""article_id"" = ""marbury_article"".""id"")

WHERE NOT (

    (
        ""marbury_section"".""article_id"" IN (
            SELECT U1.""id""
            FROM ""marbury_section"" U1
                INNER JOIN ""marbury_article"" U2 ON (U1.""article_id"" = U2.""id"")
                INNER JOIN ""marbury_article_publications"" U3 ON (U2.""id"" = U3.""article_id"")
            WHERE (U3.""publication_id"" = 1  AND U1.""id"" IS NOT NULL)
        )
        AND ""marbury_article"".""id"" IS NOT NULL
    )
)
}}}

The WHERE clause compares ""marbury_section"".""article_id"" to *section* IDs returned by the subquery.  This means that the exclusion won't be enforced, or worse, will be enforced incorrectly, if article IDs and section IDs overlap.

Here is a simple test:

{{{
# Make two articles
models.Article.objects.create()
article = models.Article.objects.create()

# Make a publication for the second article
publication = article.publications.create()

# Make a section for the article
section = models.Section.objects.create(article=article)

# Make a sentence for the section
models.Sentence.objects.create(section=section)

# Get all sentences that aren't in the publication
sentences_not_in_pub = models.Sentence.objects.exclude(
section__article__publications=publication)

# There should be none
self.assertEquals(len(sentences_not_in_pub), 0)
}}}"	Bug	closed	Database layer (models, ORM)	1.5	Normal	fixed			Accepted	0	0	0	0	0	0