Opened 7 years ago

Closed 7 years ago

#7369 closed (fixed)

ForeignKey non-null relationship after null relationship on select_related() generates invalid query

Reported by: gav Owned by: jacob
Component: Database layer (models, ORM) Version: master
Severity: Keywords: null foreignkey
Cc: Triage Stage: Unreviewed
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: UI/UX:

Description

When starting from a relationship of a ForeignKey(null=True), and doing a .select_related() into connected models that have null=False relationships, an incorrect query is generated which uses an INNER JOIN instead of a LEFT JOIN. The LEFT JOIN is necessary for any subsequent model that follows a connection from another LEFT JOIN.

Attached is a patch that also includes a couple test cases. The query generated (on a MySQL server) without this patch from the test is:

SELECT `null_fk_comment`.`id`, `null_fk_comment`.`post_id`, `null_fk_comment`.`comment_text`, `null_fk_post`.`id`, `null_fk_post`.`forum_id`, `null_fk_post`.`title`, `null_fk_forum`.`id`, `null_fk_forum`.`system_info_id`, `null_fk_forum`.`forum_name`, `null_fk_systeminfo`.`id`, `null_fk_systeminfo`.`system_name` 
FROM `null_fk_comment` 
LEFT OUTER JOIN `null_fk_post` ON (`null_fk_comment`.`post_id` = `null_fk_post`.`id`) 
LEFT OUTER JOIN `null_fk_forum` ON (`null_fk_post`.`forum_id` = `null_fk_forum`.`id`) 
INNER JOIN `null_fk_systeminfo` ON (`null_fk_forum`.`system_info_id` = `null_fk_systeminfo`.`id`)

which only returns one result, when two are available.

The correct query generated after this patch is:

SELECT `null_fk_comment`.`id`, `null_fk_comment`.`post_id`, `null_fk_comment`.`comment_text`, `null_fk_post`.`id`, `null_fk_post`.`forum_id`, `null_fk_post`.`title`, `null_fk_forum`.`id`, `null_fk_forum`.`system_info_id`, `null_fk_forum`.`forum_name`, `null_fk_systeminfo`.`id`, `null_fk_systeminfo`.`system_name` 
FROM `null_fk_comment` 
LEFT OUTER JOIN `null_fk_post` ON (`null_fk_comment`.`post_id` = `null_fk_post`.`id`) 
LEFT OUTER JOIN `null_fk_forum` ON (`null_fk_post`.`forum_id` = `null_fk_forum`.`id`) 
LEFT OUTER JOIN `null_fk_systeminfo` ON (`null_fk_forum`.`system_info_id` = `null_fk_systeminfo`.`id`)

which returns two results.

Attachments (1)

null_fk_r7574.patch (4.2 KB) - added by gav 7 years ago.
Patch to correct non-null relationships after null ForeignKeys, against r7574.

Download all attachments as: .zip

Change History (3)

Changed 7 years ago by gav

Patch to correct non-null relationships after null ForeignKeys, against r7574.

comment:1 Changed 7 years ago by jacob

  • Needs documentation unset
  • Needs tests unset
  • Owner changed from nobody to jacob
  • Patch needs improvement unset
  • Status changed from new to assigned

comment:2 Changed 7 years ago by jacob

  • Resolution set to fixed
  • Status changed from assigned to closed

(In [7597]) Fixed #7369: fixed a corner-case involving select_related() following non-null FKs after null ones. Thanks, George Vilches

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