#9342: query optimization bug
------------------------------------------+---------------------------------
Reporter: [EMAIL PROTECTED] | Owner: nobody
Status: new | Milestone:
Component: Uncategorized | Version: 1.0
Resolution: | Keywords:
Stage: Unreviewed | Has_patch: 0
Needs_docs: 0 | Needs_tests: 0
Needs_better_patch: 0 |
------------------------------------------+---------------------------------
Changes (by [EMAIL PROTECTED]):
* summary: query optimization issue => query optimization bug
Comment:
I think the issue with my hack was that it was not considering whether
alias reference count was zero or not in found alias_map. I created a test
case that proves that this issue exists in 1.0 (basically the model
outline above):
{{{
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)
}}}
Here is the doc test:
{{{
>>> tableB.objects.exclude(key__tablec__data__iexact='test').count()
0
}}}
Here is the exception:
{{{
File "C:\Python25\Lib\site-
packages\tests\regressiontests\queries\models.py", line ?, in
regressiontests.queries.models.__test__.API_TESTS
Failed example:
tableB.objects.exclude(key__tablec__data__iexact='test').count()
Exception raised:
Traceback (most recent call last):
File "C:\Python25\lib\site-packages\django\test\_doctest.py", line
1267, in __run
compileflags, 1) in test.globs
File "<doctest
regressiontests.queries.models.__test__.API_TESTS[47]>", line 1, in
<module>
tableB.objects.exclude(key__tablec__data__iexact='test').count()
File "C:\Python25\lib\site-packages\django\db\models\query.py", line
290, in count
return self.query.get_count()
File "C:\Python25\lib\site-packages\django\db\models\sql\query.py",
line 237, in get_count
data = obj.execute_sql(SINGLE)
File "C:\Python25\lib\site-packages\django\db\models\sql\query.py",
line 1720, in execute_sql
cursor.execute(sql, params)
ProgrammingError: missing FROM-clause entry in subquery for table "u1"
LINE 1: ...ies_tableb" U0 INNER JOIN "queries_tablec" U2 ON (U1."key"
=...
}}}
Here is the revised hack:
{{{
def get_from_clause(self):
"""
Returns a list of strings that are joined together to go after the
"FROM" part of the query, as well as a list any extra parameters
that
need to be included. Sub-classes, can override this to create a
from-clause via a "select", for example (e.g. CountQuery).
This should only be called after any SQL construction methods that
might change the tables we need. This means the select columns and
ordering must be done first.
"""
result = []
qn = self.quote_name_unless_alias
qn2 = self.connection.ops.quote_name
first = True
local_alias_refcount = deepcopy(self.alias_refcount)
for alias in self.tables:
if not local_alias_refcount[alias]:
#START HACK: No sure if it is a problem with
self.alias_refcount somewhere else or this code.
# Need to iterate over self.alias_map to ensure that
another alias map does not
# reference an alias that has a reference count of
zero.
for alias_check in self.tables:
#Check only alias maps that initially had a reference
count.
if self.alias_refcount[alias_check] and alias !=
alias_check:
try:
name, rhs, join_type, lhs, lhs_col, col,
nullable = self.alias_map[alias_check]
except KeyError:
continue
if alias in [rhs,lhs]:
local_alias_refcount[alias] = 1
#END HACK
if not local_alias_refcount[alias]:
continue
#if not self.alias_refcount[alias]:
# continue
try:
name, alias, join_type, lhs, lhs_col, col, nullable =
self.alias_map[alias]
except KeyError:
# Extra tables can end up in self.tables, but not in the
# alias_map if they aren't in a join. That's OK. We skip
them.
continue
alias_str = (alias != name and ' %s' % alias or '')
if join_type and not first:
result.append('%s %s%s ON (%s.%s = %s.%s)'
% (join_type, qn(name), alias_str, qn(lhs),
qn2(lhs_col), qn(alias), qn2(col)))
else:
connector = not first and ', ' or ''
result.append('%s%s%s' % (connector, qn(name), alias_str))
first = False
for t in self.extra_tables:
alias, unused = self.table_alias(t)
# Only add the alias if it's not already present (the
table_alias()
# calls increments the refcount, so an alias refcount of one
means
# this is the only reference.
if alias not in self.alias_map or self.alias_refcount[alias]
== 1:
connector = not first and ', ' or ''
result.append('%s%s' % (connector, qn(alias)))
first = False
return result, []
}}}
Revised code passes all tests in:
regressiontests.queries.models.__test__.API_TESTS
--
Ticket URL: <http://code.djangoproject.com/ticket/9342#comment:3>
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
-~----------~----~----~----~------~----~------~--~---
