Code

Opened 4 years ago

Closed 14 months 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 3 years ago.
patch and test vs 1.1.2
12823_trunk.diff (2.4 KB) - added by PhiR_42 3 years ago.
patch and test vs trunk
12823_trunk.2.diff (3.0 KB) - added by PhiR 3 years ago.
updated (sorry for the username change)
12823_1.2.diff (3.0 KB) - added by PhiR 3 years ago.
12823_trunk.2.2.diff (2.9 KB) - added by PhiR 2 years ago.
Updated for current trunk
12823_1.5.diff (3.0 KB) - added by PhiR_42 3 months ago.
updated patch for django 1.5.5 (bug not fixed in this release)

Download all attachments as: .zip

Change History (28)

comment:1 Changed 4 years ago by Alex

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

comment:2 Changed 4 years ago by PhiR_42

This may or may not be a dup of #11052

comment:3 Changed 3 years ago by PhiR_42

  • 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.

Changed 3 years ago by PhiR_42

patch and test vs 1.1.2

comment:4 Changed 3 years ago by lrekucki

  • Patch needs improvement set

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

Changed 3 years ago by PhiR_42

patch and test vs trunk

comment:5 Changed 3 years ago by PhiR_42

  • 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 Changed 3 years ago by PhiR_42

  • Cc philippe@… added

comment:7 Changed 3 years ago by hvdklauw

  • milestone set to 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 Changed 3 years ago by hvdklauw

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 Changed 3 years ago by hvdklauw

  • Cc hvdklauw@… added

comment:10 Changed 3 years ago by 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.

comment:11 Changed 3 years ago by 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 ?

comment:12 Changed 3 years ago by lukeplant

  • Type set to Bug

comment:13 Changed 3 years ago by lukeplant

  • Severity set to Normal

comment:14 Changed 3 years ago by patchhammer

  • Easy pickings unset
  • Patch needs improvement set

12823_trunk.diff fails to apply cleanly on to trunk

Changed 3 years ago by PhiR

updated (sorry for the username change)

Changed 3 years ago by PhiR

comment:15 Changed 3 years ago by PhiR

  • Patch needs improvement unset
  • UI/UX unset

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

comment:16 Changed 3 years ago by jacob

  • milestone 1.3 deleted

Milestone 1.3 deleted

Changed 2 years ago by PhiR

Updated for current trunk

comment:17 Changed 2 years ago by sebastian

  • Cc sebastian.goll@… added

comment:18 Changed 2 years ago by aaugustin

  • Owner changed from nobody to aaugustin

comment:19 Changed 14 months ago by aaugustin

  • Status changed from new to assigned

comment:20 Changed 14 months ago by aaugustin

  • Owner aaugustin deleted
  • Status changed from assigned to new

comment:21 Changed 14 months ago by akaariai

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 Changed 14 months ago by Anssi Kääriäinen <akaariai@…>

  • Owner set to Anssi Kääriäinen <akaariai@…>
  • Resolution set to fixed
  • Status changed from new to closed

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.

Changed 3 months ago by PhiR_42

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

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as closed
as The resolution will be set. Next status will be 'closed'
The resolution will be deleted. Next status will be 'new'
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.