Code

Opened 6 years ago

Closed 6 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 6 years ago.
Patch to correct non-null relationships after null ForeignKeys, against r7574.

Download all attachments as: .zip

Change History (3)

Changed 6 years ago by gav

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

comment:1 Changed 6 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 6 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

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as closed
as The resolution will be set. Next status will be 'closed'
The resolution will be deleted. Next status will be 'new'
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.