Opened 16 years ago

Closed 16 years ago

#7369 closed (fixed)

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

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

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

Download all attachments as: .zip

Change History (3)

by George Vilches, 16 years ago

Attachment: null_fk_r7574.patch added

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

comment:1 by Jacob, 16 years ago

Owner: changed from nobody to Jacob
Status: newassigned

comment:2 by Jacob, 16 years ago

Resolution: fixed
Status: assignedclosed

(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