#9342: query optimization issue
---------------------------------+------------------------------------------
Reporter: [EMAIL PROTECTED] | Owner: nobody
Status: new | Milestone:
Component: Uncategorized | Version: 1.0
Keywords: | Stage: Unreviewed
Has_patch: 0 |
---------------------------------+------------------------------------------
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.
--
Ticket URL: <http://code.djangoproject.com/ticket/9342>
Django <http://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/django-updates?hl=en
-~----------~----~----~----~------~----~------~--~---
