Opened 11 months ago

Last modified 7 months ago

#27833 assigned Bug

prefetch_related fails with SQLite when used with 1000 parent records

Reported by: Jason Barnabe Owned by: Raphael Michel
Component: Database layer (models, ORM) Version: 1.10
Severity: Normal Keywords:
Cc: Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: yes
Easy pickings: no UI/UX: no

Description (last modified by Tim Graham)

This is described in ticket:16937#comment:3, but I don't see any issue filed for it.

"[prefetch_related] will not work on some backends if you have a lot of objects in your queryset. For example the SQLite backend has a limitation of 999 parameters to single SQL query, so if you have 1000 objects, prefetch_related will fail as you need to supply 1000 id values to the query."'

Batch it up like in #16426 and #17788, which dealt with the same limitation?

Change History (10)

comment:1 Changed 11 months ago by Tim Graham

Description: modified (diff)
Triage Stage: UnreviewedAccepted

comment:2 Changed 10 months ago by Raphael Michel

Owner: changed from nobody to Raphael Michel
Status: newassigned

comment:3 Changed 10 months ago by Raphael Michel

Has patch: set

comment:4 Changed 9 months ago by François Freitag

Patch needs improvement: set

comment:5 Changed 9 months ago by Raphael Michel

Patch needs improvement: unset

comment:6 Changed 7 months ago by Tim Graham

Patch needs improvement: set

comment:7 Changed 7 months ago by Raphael Michel

Patch needs improvement: unset

comment:8 Changed 7 months ago by Tim Graham

Triage Stage: AcceptedReady for checkin

comment:9 Changed 7 months ago by François Freitag

Triage Stage: Ready for checkinAccepted

As mentioned in the PR, I think there is one major issue: prefetch_related expects the results of the prefetch query to be unique.
Splitting the query in batches do not enforce uniqueness and can lead to incorrect results. In short, if related objects are shared between several instances, when instances are split into batches, nothing prevents a related objects from appearing twice in the results. For example:

# Suppose there are 2 instances (A, B) and 2 related objects (1, 2)
A -> 1
B -> 2
A -> 1

Prefetching in batches of 2 results in prefetch queries for: [A, B] then [C].
Which gives the following mapping of related objects: [(A, 1), (B, 2)] then [(A, 1)]. The rel_obj_cache would then be:

{
    (1,): [A, A],  # A should only be present once
    (2,): [B]
}

A more detailed example is available on the PR.

Maybe the way forward is to use a set or a dict to store the related queries' results (either for all_related_objects or for rel_obj_cache).

comment:10 Changed 7 months ago by Tim Graham

Patch needs improvement: set
Note: See TracTickets for help on using tickets.
Back to Top