Understanding Django queryset field lookup in

Question

Short version: Why does Model.objects.exclude(..__in=[None]) exclude every object?

I have encountered an interesting behaviour of django field lookup that I do not understand. Let's say I have 21 objects of a given model:

>>> Model.objects.count()
21

If I exclude a given private key directly or with in field lookup I get the expected behaviour:

>>> Model.objects.exclude(pk=1).count()
20
>>> Model.objects.exclude(pk__in=[1]).count()
20

If I exclude the private key of value None I get the expected result:

>>> Model.objects.exclude(pk=None).count()
21

However if I do the same with the in field lookup I get nothing back:

>>> Model.objects.exclude(pk__in=[None]).count()
0

Why is this so?

Answer 1

I suspect this is because of the way SQL treats NULL. The query compiles to SELECT COUNT(*) FROM mymodel WHERE NOT (id IN (NULL));.

See for example this question for a discussion of why NOT IN with NULL always returns empty.