Opened 14 years ago

Closed 11 years ago

#12823 closed Bug (fixed)

Wrong SQL query when using .exclude()

Reported by: olepw Owned by: Anssi Kääriäinen <akaariai@…>
Component: Database layer (models, ORM) Version: 1.1
Severity: Normal Keywords:
Cc: philippe@…, hvdklauw@…, sebastian.goll@… Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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 

Attachments (6)

12823_1.1.diff (3.6 KB ) - added by PhiR_42 13 years ago.
patch and test vs 1.1.2
12823_trunk.diff (2.4 KB ) - added by PhiR_42 13 years ago.
patch and test vs trunk
12823_trunk.2.diff (3.0 KB ) - added by Philippe Raoult 13 years ago.
updated (sorry for the username change)
12823_1.2.diff (3.0 KB ) - added by Philippe Raoult 13 years ago.
12823_trunk.2.2.diff (2.9 KB ) - added by Philippe Raoult 12 years ago.
Updated for current trunk
12823_1.5.diff (3.0 KB ) - added by PhiR_42 10 years ago.
updated patch for django 1.5.5 (bug not fixed in this release)

Download all attachments as: .zip

Change History (28)

comment:1 by Alex Gaynor, 14 years ago

Triage Stage: UnreviewedAccepted

comment:2 by PhiR_42, 13 years ago

This may or may not be a dup of #11052

comment:3 by PhiR_42, 13 years ago

Has patch: set

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.

by PhiR_42, 13 years ago

Attachment: 12823_1.1.diff added

patch and test vs 1.1.2

comment:4 by Łukasz Rekucki, 13 years ago

Patch needs improvement: set

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

by PhiR_42, 13 years ago

Attachment: 12823_trunk.diff added

patch and test vs trunk

comment:5 by PhiR_42, 13 years ago

Patch needs improvement: unset

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.

comment:6 by PhiR_42, 13 years ago

Cc: philippe@… added

comment:7 by Harro, 13 years ago

milestone: 1.3

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.

comment:8 by Harro, 13 years ago

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.

comment:9 by Harro, 13 years ago

Cc: hvdklauw@… added

comment:10 by PhiR_42, 13 years ago

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

comment:11 by PhiR_42, 13 years ago

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 ?

comment:12 by Luke Plant, 13 years ago

Type: Bug

comment:13 by Luke Plant, 13 years ago

Severity: Normal

comment:14 by patchhammer, 13 years ago

Easy pickings: unset
Patch needs improvement: set

12823_trunk.diff fails to apply cleanly on to trunk

by Philippe Raoult, 13 years ago

Attachment: 12823_trunk.2.diff added

updated (sorry for the username change)

by Philippe Raoult, 13 years ago

Attachment: 12823_1.2.diff added

comment:15 by Philippe Raoult, 13 years ago

Patch needs improvement: unset
UI/UX: unset

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

comment:16 by Jacob, 12 years ago

milestone: 1.3

Milestone 1.3 deleted

by Philippe Raoult, 12 years ago

Attachment: 12823_trunk.2.2.diff added

Updated for current trunk

comment:17 by Sebastian Goll, 12 years ago

Cc: sebastian.goll@… added

comment:18 by Aymeric Augustin, 12 years ago

Owner: changed from nobody to Aymeric Augustin

comment:19 by Aymeric Augustin, 11 years ago

Status: newassigned

comment:20 by Aymeric Augustin, 11 years ago

Owner: Aymeric Augustin removed
Status: assignednew

comment:21 by Anssi Kääriäinen, 11 years ago

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.

comment:22 by Anssi Kääriäinen <akaariai@…>, 11 years ago

Owner: set to Anssi Kääriäinen <akaariai@…>
Resolution: fixed
Status: newclosed

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.

by PhiR_42, 10 years ago

Attachment: 12823_1.5.diff added

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

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