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)
Change History (3)
by , 16 years ago
Attachment: | null_fk_r7574.patch added |
---|
comment:1 by , 16 years ago
Owner: | changed from | to
---|---|
Status: | new → assigned |
comment:2 by , 16 years ago
Resolution: | → fixed |
---|---|
Status: | assigned → closed |
Patch to correct non-null relationships after null ForeignKeys, against r7574.