exclude() does not return the opposite of filter() for one to many relationships

[Greg Brown]

Consider the following models (in an app called "news"):

class Region(models.Model):
    name = models.CharField(max_length=255)

class Career(AuditedBaseModel):
    name = models.CharField(max_length=255)
    region = models.ForeignKey(Region)

I'm trying to select all regions that have at least one career, using:

Region.objects.exclude(career=None)

but I'm getting no results. The opposite - retrieving all regions with no career - works fine:

Region.objects.filter(career=None)

The filter() call above uses the following sql:

SELECT "news_region"."id", "news_region"."name"
FROM "news_region"
LEFT OUTER JOIN "news_career" ON ("news_region"."id" = "news_career"."region_id")
WHERE "news_career"."id" IS NULL;

and the exclude() uses:

SELECT "news_region"."id", "news_region"."name"
FROM "news_region"
WHERE NOT (
    "news_region"."id" IN (
        SELECT U1."region_id" 
        FROM "news_region" U0 
        LEFT OUTER JOIN "news_career" U1 ON (U0."id" = U1."region_id") 
        WHERE U1."id" IS NULL
    )
);

which doesn't work. I guess there's reasons for it to be done this way, but if exclude actually just wrapped the filter() clause in a NOT(), ie:

SELECT "news_region"."id", "news_region"."name"
FROM "news_region"
LEFT OUTER JOIN "news_career" ON ("news_region"."id" = "news_career"."region_id")
WHERE NOT("news_career"."id" IS NULL);

it would work fine. Also, the presence of a "not equal" operator would solve my problem.

[Malcolm Tredinnick]

You never want anything involving the word "equality" when working with NULLs, since they don't compare equal to anything. Fortunately, there's already the __isnull lookup type, so filtering on career__isnull=False will do what you want (it will generate the latter SQL). Spotting that the exclude() call you wrote is a special case that can be transformed into the __isnull equivalent is not something Django will do -- it's quite complex to spot such transformations efficiently and always make the correct one.

I cannot immediately spot the problem with the exclude() call, however. Since I'm working on this area, I'll add this to my test cases.

[Malcolm Tredinnick]

Closing as a dupe of #8921 after creating a test case.