Opened 46 minutes ago
Last modified 30 minutes ago
#37140 new Cleanup/optimization
Document NULL handling of exclude() with __in subqueries
| Reported by: | verajs | Owned by: | |
|---|---|---|---|
| Component: | Documentation | Version: | dev |
| Severity: | Normal | Keywords: | exclude in subquery NULL NOT IN |
| Cc: | Triage Stage: | Unreviewed | |
| Has patch: | yes | Needs documentation: | no |
| Needs tests: | no | Patch needs improvement: | no |
| Easy pickings: | no | UI/UX: | no |
Description
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
- describes the failure mode (NULL in subquery -> NOT IN evaluates to UNKNOWN -> empty queryset)
- points readers to
Exists+OuterRefas the NULL-safe replacement - 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
I have a patch ready:
https://github.com/django/django/pull/21414