postgresql exclude issue

[Sergey Dobrov]

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 :)

[Russell Keith-Magee]

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.

[Aymeric Augustin]

Change UI/UX from NULL to False.

[Aymeric Augustin]

Change Easy pickings from NULL to False.

[Anssi Kääriäinen]

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.