Question on Django ORM adding not null clause to exclude with not in

Django Internals ORM
1

I’m puzzled by Django behavior when using NOT IN query to filter queryset by value of a field from related nullable relationship.

If we have the following models:

class Vendor(models.Model):
    name = models.CharField(max_length=128)

class Asset(models.Model):
    vendor = models.ForeignKey(to=Vendor, blank=True, null=True, on_delete=models.PROTECT, related_name=‘vendor’)

And attempt to find all Assets that do not match vendor name ‘Unknown’, Django will generate something along these lines:

In [1]: l = list(Asset.objects.exclude(vendor__name__in=['unknown']))
SELECT "app_asset"."id"
  FROM "app_asset"
  LEFT OUTER JOIN "app_vendor"
    ON ("app_asset"."vendor_id" = "app_vendor"."id")
 WHERE NOT ("app_vendor"."name" IN ('unknown') AND "app_vendor"."title" IS NOT NULL)

If we have Assets with vendor = None, such query will filter out all such rows, which is obviously not what the user would want (excluding only Assets with the specific vendor name).

I’ve spent hours googling and reading Django sources, but I still do not understand why extra AND field IS NOT NULL clause is added and how can I suppress it.

I’ve added workaround to my app by using additional OR field IS NULL clause, but want to get to the bottom of the issue - maybe Django adds this clause for a reason. Does anybody know?

2

The exclude method is designed to return the opposite data set of what filter would return.

In this case doing

Asset.objects.filter(vendor__name__in=['unknown'])

excludes assets without a vendor (AKA asset.vendor_id IS NULL) and thus

Asset.objects.exclude(vendor__name__in=['unknown'])

includes them.

If you’d want filter to include assets without a vendor in your initial query you’d do

Asset.objects.filter(Q(vendor__name__in=['unknown']) | Q(vendor=None))

and thus the exclude counterpart, which is the workaround you’ve identified, would exclude them

Asset.objects.exclude(Q(vendor__name__in=['unknown']) | Q(vendor=None))

In other words this is expected and just the way Django deals with nullable columns.

3 Likes
3

Aha, thank you. I’ve misinterpreted the resulting query. You’re right and Django is right. Question closed.