Excluding some value in a related model excludes objects without that model

[adehnert]

I have models that look something like:

class Group(models.Model):
    name = models.CharField(max_length=100)
    activity_category = models.ForeignKey('ActivityCategory', null=True, blank=True, )
class ActivityCategory(models.Model):
    name = models.CharField(max_length=50)

I'm trying to find each Group that doesn't have some ActivityCategory:

qobj = Q(activity_category__name='Dorm')
queryset = groups.models.Group.objects.filter(~qobj)

Intuitively, I would expect this to find both groups with no ActivityCategory and groups where the ActivityCategory exists and it isn't "Dorm". Reading ​https://docs.djangoproject.com/en/dev/topics/db/queries/#lookups-that-span-relationships seems to support this interpretation --- "If you are filtering across multiple relationships and one of the intermediate models doesn't have a value that meets the filter condition, Django will treat it as if there is an empty (all values are NULL), but valid, object there." Unfortunately, the ORM generates a query using INNER JOIN instead of LEFT OUTER JOIN, so my Group with a null ActivityCategory will not be returned.

I can work around this by explicitly indicating that null is possible, but it's annoying and unintuitive:

qobj = Q(activity_category__isnull = True) | ~(Q(activity_category__name='Dorm') | Q(activity_category__name='FSILG'))
queryset = groups.models.Group.objects.filter(qobj)

[Aymeric Augustin]

The docs basically say that the ORM won't crash'n'burn when you're filtering on foo__bar=42 just because one of your objects doesn't have a related Foo object. See the paragraph that follows your quote. It doesn't really say anything about "negative queries".

That said, I agree with your analysis. Assuming a given object doesn't have an associated Foo object, the value of its foo__bar is something akin to NaN. Since NaN != <whatever> evaluates to True, this analogy supports your interpretation.

Fixing this problem would be backwards incompatible: it would change the result of "negative queries". IMO, we can't say it's an obvious bug, rather an under-defined behavior. So I'll mark it as DDN.

[Aymeric Augustin]

Addendum: this might be a duplicate of #14876 — I'm not sure.

[adehnert]

Mmm.

If the outcome of the design decision is to stick with the status quo (which would make me sad, but such is life), it'd be nice to more clearly document that behavior. (Admittedly, I'm not sure that it'll actually help very many people.)

[Aleksandra Sendecka]

Can't reproduce it anymore with a current trunk version. The query contains no INNER JOIN, but LEFT OUTER join.

[adehnert]

models.py to reproduce

[adehnert]

Hm, okay, so apparently I over-simplified the my steps to reproduce. It looks like the bug is actually in how combining Q objects is handled, somehow?

In particular, it appears that the LEFT OUTER JOIN occurs if (a) your qobj is a single clause, and (b) you don't try to NOT any Q wrapping the qobj. If you either do an OR (e.g., Q(activity_category__name='Dorm') | Q(activity_category__name='Dorm'), which should have the same result set) or NOT a wrapping Q (for example, by doing a .exclude instead of a .filter), things will break.

The following demonstrates behavior with various different Q objects (see the attached models.py for what they are exactly):

>>> import groups.models; groups.models.test()
or'd/exclude [<Group: foo (cat=1)>]
or'd/filter [<Group: foo (cat=1)>]
or'd/filterout [<Group: foo (cat=1)>]
or'd/filterin [<Group: foo (cat=1)>]
single/exclude [<Group: foo (cat=1)>]
single/filter [<Group: foo (cat=1)>, <Group: Bar (cat=None)>]
single/filterout [<Group: foo (cat=1)>]
single/filterin [<Group: foo (cat=1)>, <Group: Bar (cat=None)>]

SELECT "groups_group"."id", "groups_group"."name", "groups_group"."activity_category_id" FROM "groups_group" INNER JOIN "groups_activitycategory" ON ("groups_group"."activity_category_id" = "groups_activitycategory"."id") WHERE NOT (("groups_activitycategory"."name" = Dorm  OR "groups_activitycategory"."name" = Dorm ))
SELECT "groups_group"."id", "groups_group"."name", "groups_group"."activity_category_id" FROM "groups_group" INNER JOIN "groups_activitycategory" ON ("groups_group"."activity_category_id" = "groups_activitycategory"."id") WHERE NOT (("groups_activitycategory"."name" = Dorm  OR "groups_activitycategory"."name" = Dorm ))
SELECT "groups_group"."id", "groups_group"."name", "groups_group"."activity_category_id" FROM "groups_group" INNER JOIN "groups_activitycategory" ON ("groups_group"."activity_category_id" = "groups_activitycategory"."id") WHERE NOT (("groups_activitycategory"."name" = Dorm  OR "groups_activitycategory"."name" = Dorm ))
SELECT "groups_group"."id", "groups_group"."name", "groups_group"."activity_category_id" FROM "groups_group" INNER JOIN "groups_activitycategory" ON ("groups_group"."activity_category_id" = "groups_activitycategory"."id") WHERE NOT (("groups_activitycategory"."name" = Dorm  OR "groups_activitycategory"."name" = Dorm ))
SELECT "groups_group"."id", "groups_group"."name", "groups_group"."activity_category_id" FROM "groups_group" INNER JOIN "groups_activitycategory" ON ("groups_group"."activity_category_id" = "groups_activitycategory"."id") WHERE NOT ("groups_activitycategory"."name" = Dorm )
SELECT "groups_group"."id", "groups_group"."name", "groups_group"."activity_category_id" FROM "groups_group" LEFT OUTER JOIN "groups_activitycategory" ON ("groups_group"."activity_category_id" = "groups_activitycategory"."id") WHERE NOT (("groups_activitycategory"."name" = Dorm  AND NOT ("groups_activitycategory"."id" IS NULL)))
SELECT "groups_group"."id", "groups_group"."name", "groups_group"."activity_category_id" FROM "groups_group" INNER JOIN "groups_activitycategory" ON ("groups_group"."activity_category_id" = "groups_activitycategory"."id") WHERE NOT ("groups_activitycategory"."name" = Dorm )
SELECT "groups_group"."id", "groups_group"."name", "groups_group"."activity_category_id" FROM "groups_group" LEFT OUTER JOIN "groups_activitycategory" ON ("groups_group"."activity_category_id" = "groups_activitycategory"."id") WHERE NOT (("groups_activitycategory"."name" = Dorm  AND NOT ("groups_activitycategory"."id" IS NULL)))

Given that ORing together identical Q objects will change the queryset produced by filtering on it, this seems a little more clearly a bug (rather than a wart) than it previously appeared.

[Aymeric Augustin]

(I was wrong in my first comment -- in SQL NULL != <whatever> evaluates to False.)

Anyway, I just tried your example, and Django now consistently uses LEFT OUTER JOIN. This was fixed, maybe at the same time as #14876.