id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
7369	ForeignKey non-null relationship after null relationship on select_related() generates invalid query	George Vilches	Jacob	"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.

"		closed	Database layer (models, ORM)	dev		fixed	null foreignkey		Unreviewed	1	0	0	0	0	0