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
12823	Wrong SQL query when using .exclude()	olepw	Anssi Kääriäinen <akaariai@…>	"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 
}}}"	Bug	closed	Database layer (models, ORM)	1.1	Normal	fixed		philippe@… hvdklauw@… sebastian.goll@…	Accepted	1	0	0	0	0	0