[Django] #36290: Unnecessary query performed when prefetching nullable foreign key relationships

[Django]

#36290: Unnecessary query performed when prefetching nullable foreign key
relationships
-------------------------------------+-------------------------------------
Reporter: Simon | Owner: Simon Charette
Charette |
Type: Bug | Status: assigned
Component: Database | Version: 5.2
layer (models, ORM) |
Severity: Release | Keywords: prefetch_related
blocker | foreign object key null
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
[https://discord.com/channels/856567261900832808/859997770274045954/1357036282651345006
As reported by Baptiste on Discord]

---

Because the `tuple_lookups.TupleIn.process_rhs` logic doesn't replicate
the `None` eliding logic on `lookups.In.process_rhs`
[https://github.com/django/django/blob/c7ff347c641f2f97fa9f2f7d182982f789a211b4/django/db/models/lookups.py#L527-L536
logic] the usage of `TupleIn` introduced in
626d77e52a3f247358514bcf51c761283968099c to resolve #36116 resulted in the
unnecessary execution of queries of the form

{{{#sql
SELECT "releases_release"."version" FROM "releases_release" WHERE
("releases_release"."version") IN ((NULL))
}}}

which are never going to match because `NULL != NULL` in SQL.

This has to relation to #31667 which introduced this optimization for the
same prefetch related optimization purposes.
--
Ticket URL: <https://code.djangoproject.com/ticket/36290>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#36290: Unnecessary query performed when prefetching nullable foreign key
relationships
-------------------------------------+-------------------------------------

Reporter: Simon Charette | Owner: Simon

| Charette
Type: Bug | Status: assigned

Component: Database layer | Version: 5.2
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: prefetch_related | Triage Stage:
foreign object key null | Unreviewed
Has patch: 1 | Needs documentation: 0

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

Changes (by Simon Charette):

* has_patch: 0 => 1


Old description:

> [https://discord.com/channels/856567261900832808/859997770274045954/1357036282651345006
> As reported by Baptiste on Discord]
>
> ---
>
> Because the `tuple_lookups.TupleIn.process_rhs` logic doesn't replicate
> the `None` eliding logic on `lookups.In.process_rhs`
> [https://github.com/django/django/blob/c7ff347c641f2f97fa9f2f7d182982f789a211b4/django/db/models/lookups.py#L527-L536
> logic] the usage of `TupleIn` introduced in
> 626d77e52a3f247358514bcf51c761283968099c to resolve #36116 resulted in
> the unnecessary execution of queries of the form
>
> {{{#sql
> SELECT "releases_release"."version" FROM "releases_release" WHERE
> ("releases_release"."version") IN ((NULL))
> }}}
>
> which are never going to match because `NULL != NULL` in SQL.
>
> This has to relation to #31667 which introduced this optimization for the
> same prefetch related optimization purposes.

New description:

[https://discord.com/channels/856567261900832808/859997770274045954/1357036282651345006
As reported by Baptiste on Discord]

-----

Because the `tuple_lookups.TupleIn.process_rhs` logic doesn't replicate
the `None` eliding logic on `lookups.In.process_rhs`
[https://github.com/django/django/blob/c7ff347c641f2f97fa9f2f7d182982f789a211b4/django/db/models/lookups.py#L527-L536
logic] the usage of `TupleIn` introduced in
626d77e52a3f247358514bcf51c761283968099c to resolve #36116 resulted in the
unnecessary execution of queries of the form

{{{#!sql

SELECT "releases_release"."version" FROM "releases_release" WHERE
("releases_release"."version") IN ((NULL))
}}}

which are never going to match because `NULL != NULL` in SQL.

This has to relation to #31667 which introduced this optimization for the
same prefetch related optimization purposes.

--

--
Ticket URL: <https://code.djangoproject.com/ticket/36290#comment:1>

[Django]

#36290: Unnecessary query performed when prefetching nullable foreign key
relationships
-------------------------------------+-------------------------------------
Reporter: Simon Charette | Owner: Simon
| Charette
Type: Bug | Status: assigned
Component: Database layer | Version: 5.2
(models, ORM) |
Severity: Release blocker | Resolution:

Keywords: prefetch_related | Triage Stage: Accepted
foreign object key null |

Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Jacob Walls):

* stage: Unreviewed => Accepted

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

[Django]

#36290: Unnecessary query performed when prefetching nullable foreign key
relationships
-------------------------------------+-------------------------------------
Reporter: Simon Charette | Owner: Simon
| Charette
Type: Bug | Status: assigned
Component: Database layer | Version: 5.2
(models, ORM) |
Severity: Release blocker | Resolution:

Keywords: prefetch_related | Triage Stage: Ready for
foreign object key null | checkin

Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Mariusz Felisiak):

* stage: Accepted => Ready for checkin

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

[Django]

#36290: Unnecessary query performed when prefetching nullable foreign key
relationships
-------------------------------------+-------------------------------------
Reporter: Simon Charette | Owner: Simon
| Charette

Type: Bug | Status: closed

Component: Database layer | Version: 5.2
(models, ORM) |

Severity: Release blocker | Resolution: fixed

Keywords: prefetch_related | Triage Stage: Ready for
foreign object key null | checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Mariusz Felisiak <felisiak.mariusz@…>):

* resolution: => fixed
* status: assigned => closed

Comment:

In [changeset:"f7f38f3a0b44d8c6d14344dae66b6ce52cd77b55" f7f38f3]:
{{{#!CommitTicketReference repository=""
revision="f7f38f3a0b44d8c6d14344dae66b6ce52cd77b55"
Fixed #36290 -- Made TupleIn() lookup discard tuples containing None.

Just like the In() lookup discards of None members TupleIn() should
discard tuples containing any None as NULL != NULL in SQL and the
framework expects such queries to be elided under some circumstances.

Refs #31667, #36116.

Thanks Basptise Mispelon for bisecting the regression to 626d77e.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/36290#comment:4>

[Django]

#36290: Unnecessary query performed when prefetching nullable foreign key
relationships
-------------------------------------+-------------------------------------
Reporter: Simon Charette | Owner: Simon
| Charette
Type: Bug | Status: closed
Component: Database layer | Version: 5.2
(models, ORM) |
Severity: Release blocker | Resolution: fixed
Keywords: prefetch_related | Triage Stage: Ready for
foreign object key null | checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Mariusz Felisiak <felisiak.mariusz@…>):

In [changeset:"8ebdd37a0b1755842baae3bd34d388156ad4bf53" 8ebdd37a]:
{{{#!CommitTicketReference repository=""
revision="8ebdd37a0b1755842baae3bd34d388156ad4bf53"
[5.2.x] Fixed #36290 -- Made TupleIn() lookup discard tuples containing

None.

Just like the In() lookup discards of None members TupleIn() should
discard tuples containing any None as NULL != NULL in SQL and the
framework expects such queries to be elided under some circumstances.

Refs #31667, #36116.

Thanks Basptise Mispelon for bisecting the regression to 626d77e.

Backport of f7f38f3a0b44d8c6d14344dae66b6ce52cd77b55 from main
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/36290#comment:5>