Opened 6 months ago

Closed 6 months ago

Last modified 6 months ago

#36290 closed Bug (fixed)

Unnecessary query performed when prefetching nullable foreign key relationships

Reported by: Simon Charette Owned by: Simon Charette
Component: Database layer (models, ORM) Version: 5.2
Severity: Release blocker Keywords: prefetch_related foreign object key null
Cc: Baptiste Mispelon Triage Stage: Ready for checkin
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by Simon Charette)

​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 ​logic the usage of TupleIn introduced in 626d77e52a3f247358514bcf51c761283968099c to resolve #36116 resulted in the unnecessary execution of queries of the form

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.

Change History (5)

comment:1 by Simon Charette, 6 months ago

Description: modified (diff)
Has patch: set

comment:2 by Jacob Walls, 6 months ago

Triage Stage: Unreviewed → Accepted

comment:3 by Mariusz Felisiak, 6 months ago

Triage Stage: Accepted → Ready for checkin

comment:4 by Mariusz Felisiak <felisiak.mariusz@…>, 6 months ago

Resolution: → fixed
Status: assigned → closed

In f7f38f3:

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.

comment:5 by Mariusz Felisiak <felisiak.mariusz@…>, 6 months ago

In 8ebdd37a:

[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

Note: See TracTickets for help on using tickets.
Back to Top