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
9342	query optimization bug	spatovich@…	nobody	"The generated SQL for the following example is incorrect. It appears that an optimization to reduce the number of table joins is at fault.

Example:
{{{
class tableA(models.Model):
    key = models.AutoField(primary_key=True)     

class tableB(models.Model):
    key = models.ForeignKey(tableA)

class tableC(models.Model):
    key = models.OneToOneField(tableA, primary_key=True)
    data = models.CharField(max_length=40, null=True)
}}}
The following filter will produce incorrect SQL:
{{{
qs = tableB.objects.exclude(key__tablec__data__iexact='test')
qs.count()
}}}
The generated SQL will be something like:
{{{
SELECT ""tableB"".""key_id""
FROM ""tableB"" 
     WHERE NOT (""tableB"".""key_id"" IN (SELECT U2.""key_id"" FROM ""tableB"" U0 
                                                          INNER JOIN ""tableC"" U2 ON (U1.""key_id"" = U2.""key_id"") WHERE UPPER(U2.""data""::text) = UPPER('test') ))
}}}

In the sub-select, tableA is being referenced as alias ""U1"" but is not defined. The non-optimized SQL would have been:

{{{
SELECT ""tableB"".""key_id""
FROM ""tableB"" 
     WHERE NOT (""tableB"".""key_id"" IN (SELECT U2.""key_id"" FROM ""tableB"" U0 
                                                          INNER JOIN ""tableA"" U1 ON (U1.""key_id"" = U0.""key_id"")
                                                          INNER JOIN ""tableC"" U2 ON (U1.""key_id"" = U2.""key_id"") WHERE UPPER(U2.""data""::text) = UPPER('test') ))
}}}

Tracing through the source, it seems that code in functions:
{{{ 
Query::add_filter(self, filter_expr, connector=AND, negate=False, trim=False,
            can_reuse=None, process_extras=True)

and

Query::setup_joins(self, names, opts, alias, dupe_multis, allow_many=True,
            allow_explicit_fk=False, can_reuse=None, negate=False,
            process_extras=True)
}}} 

possibly are not agreeing."		closed	Uncategorized	1.0		duplicate			Unreviewed	0	0	0	0	0	0