Wrong SQL query when using .exclude()

[olepw]

Given the following model:

from django.db import models

class Book(models.Model):
  title = models.TextField()
  chapter = models.ForeignKey('Chapter')

class Chapter(models.Model):
  title = models.TextField()
  paragraph = models.ForeignKey('Paragraph')


class Paragraph(models.Model):
  text = models.TextField()
  page = models.ManyToManyField('Page')
  
class Page(models.Model):
  text = models.TextField()

Create the following query:

q = Book.objects.exclude(chapter__paragraph__page__text='foo')

From this I would expect to get all books that does not contain the text 'foo'

print q.query

This produces:

SELECT `book_book`.`id`, `book_book`.`title`, `book_book`.`chapter_id` FROM `book_book` 
INNER JOIN `book_chapter` ON (`book_book`.`chapter_id` = `book_chapter`.`id`) 
WHERE NOT (`book_chapter`.`paragraph_id` IN 
(SELECT U1.`id` FROM `book_chapter` U1 
INNER JOIN `book_paragraph` U2 ON (U1.`paragraph_id` = U2.`id`) 
INNER JOIN `book_paragraph_page` U3 ON (U2.`id` = U3.`paragraph_id`) 
INNER JOIN `book_page` U4 ON (U3.`page_id` = U4.`id`) 
WHERE U4.`text` = foo ) 
AND `book_chapter`.`paragraph_id` IS NOT NULL)

The error here is:

WHERE NOT (`book_chapter`.`paragraph_id` IN 
(SELECT U1.`id` FROM `book_chapter` U1 

Where django tries to compare book_chapter.paragraph_id with book_chapter.id, two fields from the same table.
I would expect the query to be:

WHERE NOT (`book_book`.`chapter_id` IN 
(SELECT U1.`id` FROM `book_chapter` U1 

[PhiR_42]

This may or may not be a dup of #11052

[PhiR_42]

A quick note on the tests: I had to modify some ids to make the bug show because it basically boils down to comparing one model's PK against a PK value from another model. When you're testing with hand-created models whose PKs are all 1,2,3... it's easy to miss.

[PhiR_42]

patch and test vs 1.1.2

[Łukasz Rekucki]

The patch should be against SVN trunk. Version 1.1 is in security-fixes-only mode.

[PhiR_42]

patch and test vs trunk

[PhiR_42]

Sorry I was in the process of porting the tests to trunk. My production app uses 1.1 so that's what I used to work.

[Harro]

I can confirm this bug.

Don't look at the models, the example is horrible and makes no sense in relation to real books ;-)

Setting this as 1.3 milestone.

[Harro]

Resuling SQL from the patch applied to Django 1.2.3 (with books as appname instead of book):

SELECT "books_book"."id", "books_book"."title", "books_book"."chapter_id" FROM "books_book" INNER JOIN "books_chapter" ON ("books_book"."chapter_id" = "books_chapter"."id") WHERE NOT (("books_book"."chapter_id" IN (SELECT U1."id" FROM "books_chapter" U1 INNER JOIN "books_paragraph" U2 ON (U1."paragraph_id" = U2."id") INNER JOIN "books_paragraph_page" U3 ON (U2."id" = U3."paragraph_id") INNER JOIN "books_page" U4 ON (U3."page_id" = U4."id") WHERE U4."text" = foo ) AND "books_chapter"."paragraph_id" IS NOT NULL))

I'm a bit unsure about the JOIN on the "books_chapter" in the main query which then isn't used at all.

[PhiR_42]

I think I understand the problem: we need to unref the corresponding alias in the query so the extra unused join isn't generated.

[PhiR_42]

After giving it some thought I realized the join is actually used by the AND "books_chapter"."paragraph_id" IS NOT NULL)) clause. It looks like this is by design, but then maybe we could optimize the inner query to use the chapter table directly instead of joining it again ?

[patchhammer]

12823_trunk.diff fails to apply cleanly on to trunk

[Philippe Raoult]

updated (sorry for the username change)

[Philippe Raoult]

Disregard previous patches, but I can't remove them because I used a different account...

[Jacob]

Milestone 1.3 deleted

[Philippe Raoult]

Updated for current trunk

[Anssi Kääriäinen]

This is fixed in current master. The generated query is:

SELECT "queries_book"."id", "queries_book"."title", "queries_book"."chapter_id"
  FROM "queries_book"
 INNER JOIN "queries_chapter" ON ("queries_book"."chapter_id" = "queries_chapter"."id")
 WHERE NOT ("queries_chapter"."paragraph_id" IN
            (SELECT U3."paragraph_id"
               FROM "queries_paragraph_page" U3
              INNER JOIN "queries_page" U4 ON (U3."page_id" = U4."id")
 WHERE U4."text" = pg1 ))

I will also add a little patch to sql/query.py which will get rid of one extra IS NOT NULL check.

[Anssi Kääriäinen <akaariai@…>]

In 06de130dae8b7a6c95143077d7a82fab37da0bc0:

Fixed #12823 -- Was already fixed in master, tests added

Also added a little improvement to sql/query.py to get rid of
non-necessary IS NOT NULL check.

[PhiR_42]

updated patch for django 1.5.5 (bug not fixed in this release)