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
14056	Wrong query generated when using reverse foreign key	premalshah	nobody	"Here is the model setup

{{{
from django.db import models

class Base(models.Model):
    id = models.AutoField(primary_key=True)
    field1 = models.CharField(max_length=11)
    
class Child(models.Model):
    id = models.AutoField(primary_key=True)
    obj = models.ForeignKey(Base)
    field1 = models.CharField(max_length=11)
    field2 = models.IntegerField()
}}}        

Here are some sample queries and the corresponding sql statements generated and sql statements expected

'''Query 1'''

{{{Base.objects.filter(child__obj=1).query}}}

''Generated SQL''
{{{
SELECT `apptest_base`.`id`, `apptest_base`.`field1` FROM `apptest_base` WHERE `apptest_base`.`id` = 1;
}}}

''Expected SQL''
{{{
SELECT `apptest_base`.`id`, `apptest_base`.`field1` FROM `apptest_base` INNER JOIN `apptest_child` 
ON (`apptest_base`.`id` = `apptest_child`.`obj_id`) WHERE `apptest_child`.`obj_id` = 1;
}}}

'''Query 2'''
{{{Base.objects.filter(child__obj=1, child__field1='a').query}}}

''Generated SQL''
{{{
SELECT `apptest_base`.`id`, `apptest_base`.`field1` FROM `apptest_base` INNER JOIN `apptest_child` T4 
ON (`apptest_base`.`id` = T4.`obj_id`) WHERE (`apptest_base`.`id` = 1  AND T4.`field1` = a );
}}}

''Expected SQL''
{{{
SELECT `apptest_base`.`id`, `apptest_base`.`field1` FROM `apptest_base` INNER JOIN `apptest_child` 
ON (`apptest_base`.`id` = `apptest_child`.`obj_id`) WHERE (`apptest_child`.`obj_id` = 1  AND `apptest_child`.`field1` = a );
}}}

'''Query 3'''
{{{
Base.objects.filter(child__obj=1, child__field1='a').order_by('child__field2', 'child__obj').query
}}}

''Generated SQL''
{{{
SELECT `apptest_base`.`id`, `apptest_base`.`field1` FROM `apptest_base` LEFT OUTER JOIN `apptest_child` 
ON (`apptest_base`.`id` = `apptest_child`.`obj_id`) INNER JOIN `apptest_child` T4 
ON (`apptest_base`.`id` = T4.`obj_id`) WHERE (`apptest_base`.`id` = 1  AND T4.`field1` = a ) 
ORDER BY `apptest_child`.`field2` ASC, `apptest_base`.`id` ASC;
}}}

''Expected SQL''
{{{
SELECT `apptest_base`.`id`, `apptest_base`.`field1` FROM `apptest_base` LEFT OUTER JOIN `apptest_child` 
ON (`apptest_base`.`id` = `apptest_child`.`obj_id`) WHERE (`apptest_child`.`obj_id` = 1  AND 
`apptest_child`.`field1` = a ) ORDER BY `apptest_child`.`field2` ASC, `apptest_child`.`obj_id` ASC;
}}}

'''Problems:'''

1) Query 1 is missing a join.

2) Query 2 is using apptest_base.id in the where clause instead of apptest_child.obj_id. The results from the generated sql and expected sql would be the same. However, indexes created on the apptest_child table to optimize the query cannot be used hence leading to a slow query.

3) Query 3 has the same problem as query 2 in the where clause as well as the order by clause. In addition to that, it also performs an extra join on the apptest_child table which gives different results from the generated sql and the expected sql statements.

I believe that this happens because of the reverse foreign key field {{{obj}}}. When {{{child__obj}}} is used, the query generation logic does not know that we are referencing the {{{obj}}} field on the Child table and not the {{{id}}} field on the Base table. If we could explicitly specify {{{child__obj_id}}}, it would help, but thats not an options with the current database model api code.

"	Bug	closed	Database layer (models, ORM)	dev	Normal	fixed	foreign key, reverse lookup	dtyschenko@… Łukasz Rekucki Seán Hayes	Accepted	1	0	0	0	0	0