Opened 5 years ago

Closed 2 years ago

#13280 closed Bug (fixed)

postgresql exclude issue

Reported by: binary Owned by: nobody
Component: Database layer (models, ORM) Version: 1.1
Severity: Normal Keywords:
Cc: Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

Hello.

Sorry, I can't make simple testcase for my issue, so I'll state it as is.

I have QuerySet like this:

qforum = Forum.objects.filter(category=category).exclude(
              forumlink__campaign_link_keyword__campaign_link=forumlink.campaign_link_keyword.campaign_link)

My models look like this:

class Category(models.Model):
    name = models.CharField(max_length=10)

class Forum(models.Model):
    category = models.ForeignKey(Category)

class CampaignLink(models.Model):
    pass

class CampaignLinkKeyword(models.Model):
    campaign_link = models.ForeignKey(CampaignLink)

class ForumLink(models.Model):
    forum = models.ForeignKey(Forum, null=True, blank=True)
    campaign_link_keyword = models.ForeignKey(CampaignLinkKeyword)
    status = models.Charfield(max_length=10)

Django produces next SQL query for my QuerySet:

SELECT "targets_forum"."id", "targets_forum"."url", "targets_forum"."pr", "targets_forum"."category_id", "targets_forum"."status", "targets_forum"."added", "targets_forum"."modified", "targets_forum"."username", "targets_forum"."password", "targets_forum"."email", "targets_forum"."activation_url" 

FROM "targets_forum" WHERE 
("targets_forum"."category_id" = 10  AND NOT 
  ("targets_forum"."id" IN 
    (SELECT U1."forum_id" FROM "campaigns_forumlink" U1 INNER JOIN "campaigns_campaignlinkkeyword" U2 ON (U1."campaign_link_keyword_id" = U2."id") 
      WHERE U2."campaign_link_id" = 46 ) AND NOT ("targets_forum"."id" IS NULL) AND "targets_forum"."id" IS NOT NULL)) LIMIT 21

But it has no results because subquery returns null values too, so this query works fine:

SELECT "targets_forum"."id", "targets_forum"."url", "targets_forum"."pr", "targets_forum"."category_id", "targets_forum"."status", "targets_forum"."added", "targets_forum"."modified", "targets_forum"."username", "targets_forum"."password", "targets_forum"."email", "targets_forum"."activation_url" 

FROM "targets_forum" WHERE 
("targets_forum"."category_id" = 10  AND NOT 
  ("targets_forum"."id" IN 
    (SELECT U1."forum_id" FROM "campaigns_forumlink" U1 INNER JOIN "campaigns_campaignlinkkeyword" U2 ON (U1."campaign_link_keyword_id" = U2."id") 
      WHERE U2."campaign_link_id" = 46 AND U1."forum_id" IS NOT NULL) AND NOT ("targets_forum"."id" IS NULL) AND "targets_forum"."id" IS NOT NULL)) LIMIT 21

I wrote workaround with raw sql query usage and it works but I hope that we can fix this issue. I ready to answer any questions.

Btw, "AND NOT ("targets_forum"."id" IS NULL) AND "targets_forum"."id" IS NOT NULL" looks funny :)

Change History (5)

comment:1 Changed 5 years ago by russellm

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

There's a possibility that this is actually a duplicate of another issue, but I'll accept because I can't find an obvious dupe candidate.

comment:2 Changed 4 years ago by julien

  • Severity set to Normal
  • Type set to Bug

comment:3 Changed 4 years ago by aaugustin

  • UI/UX unset

Change UI/UX from NULL to False.

comment:4 Changed 4 years ago by aaugustin

  • Easy pickings unset

Change Easy pickings from NULL to False.

comment:5 Changed 2 years ago by akaariai

  • Resolution set to fixed
  • Status changed from new to closed

Seems like this one is fixed, the generated query is:

SELECT "queries_forum"."id", "queries_forum"."category_id"
  FROM "queries_forum"
 WHERE ("queries_forum"."category_id" = 2  AND NOT ("queries_forum"."id" IN (
    SELECT U1."forum_id" FROM "queries_forumlink" U1
INNER JOIN "queries_campaignlinkkeyword" U2 ON ( U1."campaign_link_keyword_id" = U2."id" )
     WHERE (U2."campaign_link_id" = 1  AND U1."forum_id" IS NOT NULL))))

Note that the query has U1."forum_id" IS NOT NULL condition. It is hard to say if the rest of the NULL conditions are needed - likely not, if I recall correctly they were removed on purpose.

I am closing this ticket without a test, there isn't one available, and there are other similar tests added already in master.

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