[Django] #37140: Document NULL handling of exclude() with __in subqueries

[Django]

#37140: Document NULL handling of exclude() with __in subqueries
-------------------------------------+-------------------------------------
Reporter: verajs | Type:
| Cleanup/optimization
Status: new | Component:
| Documentation
Version: dev | Severity: Normal
Keywords: exclude in subquery | Triage Stage:
NULL NOT IN | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
The `in` field lookup reference (docs/ref/models/querysets.txt) documents
passing a queryset on the right-hand side of `__in`, but does not mention
that `exclude(field__in=qs)` where `qs.values(...)` can return NULL
produces
a silently-empty result set on Postgres (and any database following SQL's
three-valued logic).

This is the subquery counterpart of #20024 (literal-list flavor, fixed in
cec10f99). The subquery flavor was identified by Anssi Kääriäinen and
Simon
Riggs on django-developers in 2013 [1], but the ORM-side fix never landed,
and there is currently no warning in the docs.

We hit this in production on a real invoice queryset and tracked it down
to
the documented NOT IN / NULL semantics. The workaround we shipped is the
same one Simon Riggs recommended in the 2013 thread: rewrite to
`~Exists(OuterRef(...))`, which compiles to NULL-safe `NOT EXISTS`.

Proposal: add a `.. warning::` block to the `in` field lookup reference
that

1. describes the failure mode (NULL in subquery -> NOT IN evaluates to
UNKNOWN -> empty queryset)
2. points readers to `Exists` + `OuterRef` as the NULL-safe replacement
3. cross-references #20024 for the literal-list case

Plus a one-line cross-reference under the `exclude()` method docs pointing
to the new warning.

[1] https://groups.google.com/g/django-developers/c/OG1unUV-MOU
--
Ticket URL: <https://code.djangoproject.com/ticket/37140>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#37140: Document NULL handling of exclude() with __in subqueries
-------------------------------------+-------------------------------------

Reporter: Sebastian Vera | Owner: (none)
Type: | Status: new
Cleanup/optimization |
Component: Documentation | Version: dev
Severity: Normal | Resolution:

Keywords: exclude in subquery | Triage Stage:
NULL NOT IN | Unreviewed

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Sebastian Vera):

* has_patch: 0 => 1

Comment:

https://github.com/django/django/pull/21414
--
Ticket URL: <https://code.djangoproject.com/ticket/37140#comment:1>