Handling of null values in __in lookups is inconsistent

[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

[Simon Charette]

Annabelle and I worked on this project during the Django on the Med 2027 sprint.