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
37372	Handling of null values in __in lookups is inconsistent	Annabelle Wiegart	Annabelle Wiegart	"When the right-hand side of an `__in` lookup contains None, several problems are surfaced. 
1. The resulting querysets differ from the same query being constructed with `Q` queries combined with OR.
2. `.filter()` and `.exclude()` produce non-complementary querysets.
3. Objects with null None values are discarded.

This happens when testing against a single table as well when spanning relationships.
The problems are related to the interference between two tickets:
#20024 was created 14 years ago. The suggested change made sense back then, but conflicts with the fix for #31667 implemented in the meantime. This led to the fix for #20024 being ineffective, and the targeting of null values in `__in` lookups being made impossible.

A bit more context for 2.: It was decided to discard None from the rhs for `__in` lookups with `.filter()`, since NULL is never equal to anything. That means that the None values have to be included in the `.exclude()` queryset in order for `.filter()` and `.exclude()` to produce complementary querysets.

DryORM fiddle: https://dryorm.xterm.info/in-lookups-with-none "	Bug	assigned	Database layer (models, ORM)	6.1	Normal		null, in, lookup	Simon Charette	Accepted	0	0	0	0	0	0