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

  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

Change History (1)

comment:1 by verajs, 31 minutes ago

Has patch: set
Last edited 30 minutes ago by verajs (previous) (diff)
Note: See TracTickets for help on using tickets.
Back to Top