[Django] #37372: Handling of null values in __in lookups is inconsistent

[Django]

#37372: Handling of null values in __in lookups is inconsistent
-------------------------------------+-------------------------------------
Reporter: Annabelle | Owner: Annabelle Wiegart
Wiegart |
Type: | Status: assigned
Uncategorized |
Component: | Version: 6.1
Uncategorized |
Severity: Normal | Keywords: null, in, lookup
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
When the right-hand side of an `__in` lookup contains None, several
problems are surfaced.
1. The resulting querysets differ from the same query being constructed
with `Q` queries combined with OR.
2. `.filter()` and `.exclude()` produce non-complementary querysets.
3. Objects with null None values are discarded.

This happens when testing against a single table as well when spanning
relationships.
The problems are related to the interference between two tickets:
#20024 was created 14 years ago. The suggested change made sense back
then, but conflicts with the fix for #31667 implemented in the meantime.
This led to the fix for #20024 being ineffective, and the targeting of
null values in `__in` lookups being made impossible.

A bit more context for 2.: It was decided to discard None from the rhs for
`__in` lookups with `.filter()`, since NULL is never equal to anything.
That means that the None values have to be included in the `.exclude()`
queryset in order for `.filter()` and `.exclude()` to produce
complementary querysets.

DryORM fiddle: https://dryorm.xterm.info/in-lookups-with-none
--
Ticket URL: <https://code.djangoproject.com/ticket/37372>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#37372: Handling of null values in __in lookups is inconsistent
-------------------------------------+-------------------------------------

Reporter: Annabelle Wiegart | Owner: Annabelle
| Wiegart
Type: Bug | Status: assigned
Component: Database layer | Version: 6.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: null, in, lookup | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0

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

Changes (by Simon Charette):

* cc: Simon Charette (added)
* component: Uncategorized => Database layer (models, ORM)
* stage: Unreviewed => Accepted
* type: Uncategorized => Bug

Comment:

Annabelle and I worked on this project during the Django on the Med 2027
sprint.
--
Ticket URL: <https://code.djangoproject.com/ticket/37372#comment:1>

[Django]

#37372: Handling of null values in __in lookups is inconsistent
-------------------------------------+-------------------------------------
Reporter: Annabelle Wiegart | Owner: Annabelle
| Wiegart
Type: Bug | Status: assigned
Component: Database layer | Version: 6.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: null, in, lookup | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Description changed by Annabelle Wiegart:

Old description:

> When the right-hand side of an `__in` lookup contains None, several
> problems are surfaced.
> 1. The resulting querysets differ from the same query being constructed
> with `Q` queries combined with OR.
> 2. `.filter()` and `.exclude()` produce non-complementary querysets.
> 3. Objects with null None values are discarded.
>
> This happens when testing against a single table as well when spanning
> relationships.
> The problems are related to the interference between two tickets:
> #20024 was created 14 years ago. The suggested change made sense back
> then, but conflicts with the fix for #31667 implemented in the meantime.
> This led to the fix for #20024 being ineffective, and the targeting of
> null values in `__in` lookups being made impossible.
>
> A bit more context for 2.: It was decided to discard None from the rhs
> for `__in` lookups with `.filter()`, since NULL is never equal to
> anything. That means that the None values have to be included in the
> `.exclude()` queryset in order for `.filter()` and `.exclude()` to
> produce complementary querysets.
>
> DryORM fiddle: https://dryorm.xterm.info/in-lookups-with-none

New description:

When the right-hand side of an `__in` lookup contains None, several
problems are surfaced.
1. The resulting querysets differ from the same query being constructed
with `Q` queries combined with OR.
2. `.filter()` and `.exclude()` produce non-complementary querysets.

3. Objects with None values are discarded.

This happens when testing against a single table as well when spanning
relationships.
The problems are related to the interference between two tickets:
#20024 was created 14 years ago. The suggested change made sense back
then, but conflicts with the fix for #31667 implemented in the meantime.
This led to the fix for #20024 being ineffective, and the targeting of
null values in `__in` lookups being made impossible.

A bit more context for 2.: It was decided to discard None from the rhs for
`__in` lookups with `.filter()`, since NULL is never equal to anything.
That means that the None values have to be included in the `.exclude()`
queryset in order for `.filter()` and `.exclude()` to produce
complementary querysets.

DryORM fiddle: https://dryorm.xterm.info/in-lookups-with-none

--

--
Ticket URL: <https://code.djangoproject.com/ticket/37372#comment:2>

[Django]

#37372: Handling of null values in __in lookups is inconsistent
-------------------------------------+-------------------------------------
Reporter: Annabelle Wiegart | Owner: Annabelle
| Wiegart
Type: Bug | Status: assigned
Component: Database layer | Version: 6.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: null, in, lookup | Triage Stage: Accepted

Has patch: 1 | Needs documentation: 0

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

Changes (by Annabelle Wiegart):

* has_patch: 0 => 1

--
Ticket URL: <https://code.djangoproject.com/ticket/37372#comment:3>