Re: [Django] #37140: NULL handling of exclude() with __in varies between nullable expressions versus literal None (was: Document NULL handling of exclude() with __in subqueries)

[Django]

#37140: NULL handling of exclude() with __in varies between nullable expressions
versus literal None
-------------------------------------+-------------------------------------
Reporter: verajs | Owner: (none)
Type: Bug | Status: new
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
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
-------------------------------------+-------------------------------------
Changes (by Jacob Walls):

* cc: Simon Charette (added)
* component: Documentation => Database layer (models, ORM)
* has_patch: 1 => 0
* summary: Document NULL handling of exclude() with __in subqueries =>
NULL handling of exclude() with __in varies between nullable
expressions versus literal None
* type: Cleanup/optimization => Bug

Comment:

Thanks for the suggestion. It's rare for us to document bugs and
workarounds, but we have done it in some cases, and it might make sense
here.

To decide that, I want to get a handle first on whether we even plan to
fix this.

ticket:31667#comment:7 (2020) mentioned the fact that
`filter(field__in=list(nullable_subquery))` started behaving differently
from `filter(field__in=nullable_subquery)` as a reason to revert that
change, a decision we never took.

ticket:20024#comment:14 (2020) suggests we can live with that drift after
all.

ticket:32043#comment:3 (2020) suggested we wouldn't even try to fix this
for nullable expressions, due to difficulty, but in a comment around the
same time (ticket:20024#comment:9) Simon provided some hints. I'm not sure
if Simon gave that a shot at some point and decided it was not practical.

Given all that, I'd like to triage the bug aspect first before accepting a
docs fix.
----
I checked the [https://github.com/django/django/pull/16817 WIP] for a
nullable expression flag (#24267), and it doesn't currently handle this.
In it, I see this explanation:

{{{#!py
class Query(...
def is_nullable(self, nullable_aliases):
# A subquery can always return no rows and thus be nullable but
the ORM
# currently has some expectations with regards to IN vs ANY
lookups that
# must be revisited to allow this logic to work properly.
return False
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/37140#comment:2>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.