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 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.

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 2026 sprint.

[Jacob Walls <jacobtylerwalls@…>]

In 31c3e4c:

Refs #37372 -- Simplified NULL handling logic in sql.Query.build_filter().

Reorganizing its logic to branch of lookup_type at an earlier layer should
simplify its upcoming adjustment to adequately handle in=[None] lookups and
should make it easier to reason about eventually replacing it by Expression
flags and methods refs #24267.

[Jacob Walls]

Annabelle's PR will need a rebase after 31c3e4cf7544597600141d9be60bd919371114c9.

[Annabelle Wiegart]

I rebased ​PR 22040 on the current main to incorporate ​31c3e4cf7544597600141d9be60bd919371114c9.

[Simon Charette]

Thanks for the rebase Anna, I was right in the middle of the review so I'll flip it back to needs improvement until the null handling part is ported to build_filter and the eager OUTER JOIN for negated __in=[None] is addressed.