id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
31883	exclude(pk__in=[None]) returns no objects	Adam Sołtysik	nobody	"The problem: `Model.objects.exclude(pk__in=[None])` always returns 0 objects.

This behaviour is very surprising (especially in more complicated cases) and is essentially useless, so it should be considered a bug. `Model.objects.filter(pk__in=[None])` and `Model.objects.filter(pk=None)` also (correctly) return no objects, and `Model.objects.exclude(pk=None)` always (correctly) returns all objects.

The issue has already been raised on the internet, see e.g. https://stackoverflow.com/questions/47058497/django-queryset-exclude-why-are-all-excluded, but I haven't found a bug report in Django. I know this is caused by how `NULL`s are handled in SQL, but broken SQL is certainly not a reason for Django's ORM to be broken the same way.

It could be fixed by filtering out the `None`s and splitting the query, so that the first example would be equivalent to `Model.objects.exclude(pk__in=[]).exclude(pk=None)`, which does correctly return all objects. The same should be done in the `filter` case, so that `Model.objects.filter(field__in=[something, None]` would also find objects with `field=None`, which it currently ignores (this is quite unexpected as well, and one needs to use two `Q` objects to get around this). Alternatively, to avoid a breaking change, `None`s could be just ignored in `exclude` too.

There is also a case with passing a queryset instead of a list, e.g. `Model.objects.exclude(pk__in=Model2.objects.values('field'))`. Even if there are no `NULL`s and it works correctly, there is another problem: it is terribly slow when the tables are large (at least in PostgreSQL). Both problems could be solved by always using a `JOIN` instead of `field [NOT] IN (SELECT ...)`, like if `related_name` were used: `Model.objects.filter(model2=None)`. Or, if this is too complicated, the inner query could receive `WHERE field IS NOT NULL` and `NULL`s should be handled separately (or ignored). Or the queryset could be simply converted to a list right away (which would of course make things even slower, but at least it would be clear what really happens).


"	Bug	closed	Database layer (models, ORM)	3.1	Normal	duplicate	exclude in nulls		Unreviewed	0	0	0	0	0	0