[Django] #37300: Custom Prefetch querysets on forward FK/O2O fields are no longer routed to the parent queryset's database

[Django]

#37300: Custom Prefetch querysets on forward FK/O2O fields are no longer routed to
the parent queryset's database
--------------------------+-----------------------------------------
Reporter: norkans7 | Type: Uncategorized
Status: new | Component: Uncategorized
Version: 6.1 | Severity: Normal
Keywords: | Triage Stage: Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
--------------------------+-----------------------------------------
In Django ≤ 6.0, when a Prefetch with a custom queryset (with no explicit
.using()) was used to prefetch a forward ForeignKey or OneToOneField,
get_prefetch_querysets() called
queryset._add_hints(instance=instances[0]), so the default router's
db_for_read() resolved to the parent instances' database via the instance
hint. The custom prefetch queryset therefore ran against the same database
as the outer queryset.

In 6.1, commit 821619aa876158e8d6dcedebd0dbc2e0e0b41295 (Refs #28586,
"Simplified related descriptor get_queryset() methods") moved the hint
into get_queryset(instance=...), which is only called when no custom
queryset is passed. The custom-queryset path in
ForwardManyToOneDescriptor.get_prefetch_querysets() and
ReverseOneToOneDescriptor.get_prefetch_querysets() lost the _add_hints()
call entirely, so a custom prefetch queryset now silently runs against
"default" even when the outer queryset uses another alias. The reverse
many-to-one and many-to-many manager paths still call _add_hints() for
custom querysets, so those relation types are unaffected — making the
forward FK/O2O behavior inconsistent with them as well as with 6.0.

Reproduction, with a "replica" alias configured identically to "default":


{{{
from django.db.models import Prefetch

book = Book.objects.using("replica").prefetch_related(
Prefetch("author", queryset=Author.objects.only("id", "name")),
)[0]

book._state.db # "replica" in both versions
book.author._state.db # 6.0: "replica" / 6.1: "default"
Without the custom queryset (.prefetch_related("author")), 6.1 still
correctly returns "replica".

}}}

Beyond queries silently hitting the wrong database (e.g. a primary instead
of a read replica), this breaks code that assigns prefetched objects onto
instances from the outer database — the forward descriptor's __set__ then
raises:

ValueError: Cannot assign "<Author: ...>": the current database router
prevents this relation.
because the two instances' _state.db values no longer match under the
default router's allow_relation().

This isn't mentioned in the 6.1 release notes, and the commit message
suggests the hint was intended to be set earlier, not dropped for custom
querysets, so it appears unintentional. Restoring
queryset._add_hints(instance=instances[0]) in the custom-queryset branches
of both descriptors' get_prefetch_querysets() fixes it. Still present on
main and stable/6.1.x as of this report.
--
Ticket URL: <https://code.djangoproject.com/ticket/37300>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#37300: Custom Prefetch querysets on forward FK/O2O fields are no longer routed to
the parent queryset's database

-------------------------------------+-------------------------------------
Reporter: Norbert Kwizera | Owner: (none)
Type: Uncategorized | Status: new
Component: Database layer | Version: 6.1
(models, ORM) |
Severity: Normal | Resolution:

Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0

-------------------------------------+-------------------------------------
Changes (by Norbert Kwizera):

* component: Uncategorized => Database layer (models, ORM)

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

[Django]

#37300: Custom Prefetch querysets on forward FK/O2O fields are no longer routed to
the parent queryset's database
-------------------------------------+-------------------------------------
Reporter: Norbert Kwizera | Owner: (none)

Type: Bug | Status: new

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

Severity: Release blocker | Resolution:

Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Clifford Gama):

* severity: Normal => Release blocker
* type: Uncategorized => Bug

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

[Django]

#37300: Custom Prefetch querysets on forward FK/O2O fields are no longer routed to
the parent queryset's database
-------------------------------------+-------------------------------------

Reporter: Norbert Kwizera | Owner: Yassin
| Bahri
Type: Bug | Status: assigned

Component: Database layer | Version: 6.1
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Yassin Bahri):

* owner: (none) => Yassin Bahri
* status: new => assigned

Comment:

I reproduced this on current main and opened PR:
https://github.com/django/django/pull/21828

The regression happens for both custom forward FK prefetch querysets and
custom reverse O2O prefetch querysets. In both cases, the custom queryset
no longer receives the parent instance hint, so routers can choose the
wrong database.

The PR restores `_add_hints(instance=instances[0])` in both descriptor
paths and adds multi-database regression tests for `Book.editor` and
`User.userprofile`.

Locally tested with:

{{{
python tests/runtests.py multiple_database --verbosity 1
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/37300#comment:3>

[Django]

#37300: Custom Prefetch querysets on forward FK/O2O fields are no longer routed to
the parent queryset's database
-------------------------------------+-------------------------------------
Reporter: Norbert Kwizera | Owner: Yassin
| Bahri
Type: Bug | Status: assigned
Component: Database layer | Version: 6.1
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: | Triage Stage:
| Unreviewed

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Yassin Bahri):

* has_patch: 0 => 1

--
Ticket URL: <https://code.djangoproject.com/ticket/37300#comment:4>

[Django]

#37300: Custom Prefetch querysets on forward FK/O2O fields are no longer routed to
the parent queryset's database
-------------------------------------+-------------------------------------
Reporter: Norbert Kwizera | Owner: Yassin
| Bahri
Type: Bug | Status: assigned
Component: Database layer | Version: 6.1
(models, ORM) |
Severity: Release blocker | Resolution:

Keywords: | Triage Stage: Accepted

Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Yassin Bahri):

* stage: Unreviewed => Accepted

--
Ticket URL: <https://code.djangoproject.com/ticket/37300#comment:5>

[Django]

#37300: Custom Prefetch querysets on forward FK/O2O fields are no longer routed to
the parent queryset's database
-------------------------------------+-------------------------------------
Reporter: Norbert Kwizera | Owner: Yassin
| Bahri
Type: Bug | Status: assigned
Component: Database layer | Version: 6.1
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Description changed by Jacob Walls:

Old description:

New description:

In Django ≤ 6.0, when a Prefetch with a custom queryset (with no explicit
.using()) was used to prefetch a forward ForeignKey or OneToOneField,
get_prefetch_querysets() called
queryset._add_hints(instance=instances[0]), so the default router's
db_for_read() resolved to the parent instances' database via the instance
hint. The custom prefetch queryset therefore ran against the same database
as the outer queryset.

In 6.1, commit 821619aa8771ef211c4c4922001efdf914201ca3 (Refs #28586,

--
Ticket URL: <https://code.djangoproject.com/ticket/37300#comment:6>

[Django]

#37300: Custom Prefetch querysets on forward FK/O2O fields are no longer routed to
the parent queryset's database
-------------------------------------+-------------------------------------
Reporter: Norbert Kwizera | Owner: Yassin
| Bahri
Type: Bug | Status: assigned
Component: Database layer | Version: 6.1
(models, ORM) |
Severity: Release blocker | Resolution:

Keywords: | Triage Stage: Ready for
| checkin

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

Changes (by Jacob Walls):

* stage: Accepted => Ready for checkin

--
Ticket URL: <https://code.djangoproject.com/ticket/37300#comment:7>

[Django]

#37300: Custom Prefetch querysets on forward FK/O2O fields are no longer routed to
the parent queryset's database
-------------------------------------+-------------------------------------
Reporter: Norbert Kwizera | Owner: Yassin
| Bahri
Type: Bug | Status: assigned
Component: Database layer | Version: 6.1
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: | Triage Stage: Ready for
| checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Jacob Walls):

* cc: Adam Johnson (added)

--
Ticket URL: <https://code.djangoproject.com/ticket/37300#comment:8>

[Django]

#37300: Custom Prefetch querysets on forward FK/O2O fields are no longer routed to
the parent queryset's database
-------------------------------------+-------------------------------------
Reporter: Norbert Kwizera | Owner: Yassin
| Bahri

Type: Bug | Status: closed

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

Severity: Release blocker | Resolution: fixed

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

Changes (by Jacob Walls <jacobtylerwalls@…>):

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

Comment:

In [changeset:"60a17fccf4f2fa31b823b0f938005897f2f3ef16" 60a17fc]:
{{{#!CommitTicketReference repository=""
revision="60a17fccf4f2fa31b823b0f938005897f2f3ef16"
Fixed #37300 -- Preserved parent instance hints on custom Prefetch
querysets.

Regression in 821619aa8771ef211c4c4922001efdf914201ca3.

Thanks Norbert Kwizera for the report.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/37300#comment:9>

[Django]

#37300: Custom Prefetch querysets on forward FK/O2O fields are no longer routed to
the parent queryset's database
-------------------------------------+-------------------------------------
Reporter: Norbert Kwizera | Owner: Yassin
| Bahri
Type: Bug | Status: closed
Component: Database layer | Version: 6.1
(models, ORM) |
Severity: Release blocker | Resolution: fixed
Keywords: | Triage Stage: Ready for
| checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Jacob Walls <jacobtylerwalls@…>):

In [changeset:"4b0185a5fb02a8c691bee4ce82e70174e061a228" 4b0185a5]:
{{{#!CommitTicketReference repository=""
revision="4b0185a5fb02a8c691bee4ce82e70174e061a228"
[6.1.x] Fixed #37300 -- Preserved parent instance hints on custom Prefetch

querysets.

Regression in 821619aa8771ef211c4c4922001efdf914201ca3.

Thanks Norbert Kwizera for the report.

Backport of 60a17fccf4f2fa31b823b0f938005897f2f3ef16 from main.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/37300#comment:10>