#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.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion visit
https://groups.google.com/d/msgid/django-updates/0107019e8ddbdaaa-fddeba8b-cde6-4970-ac5e-4b2a84685fba-000000%40eu-central-1.amazonses.com.
