#10250: MySQL quoting not happening for aggregation group by?
-----------------------------+----------------------------------------------
Reporter: kmtracey | Owner:
Status: new | Milestone:
Component: ORM aggregation | Version: 1.0
Keywords: | Stage: Unreviewed
Has_patch: 0 |
-----------------------------+----------------------------------------------
Adding this:
{{{
>>> Entries.objects.annotate(clue_count=Count('clues__ID'))
[]
}}}
to the aggregation tests causes a failure on MySQL/MyISAM (not tested on
InnoDB since aggregation fixtures won't load there):
{{{
======================================================================
FAIL: Doctest: modeltests.aggregation.models.__test__.API_TESTS
----------------------------------------------------------------------
Traceback (most recent call last):
File "/home/kmt/tmp/django/trunk/django/test/_doctest.py", line 2180, in
runTest
raise self.failureException(self.format_failure(new.getvalue()))
AssertionError: Failed doctest test for
modeltests.aggregation.models.__test__.API_TESTS
File
"/home/kmt/tmp/django/trunk/tests/modeltests/aggregation/models.py", line
unknown line number, in API_TESTS
----------------------------------------------------------------------
File "/home/kmt/tmp/django/trunk/tests/modeltests/aggregation/models.py",
line ?, in modeltests.aggregation.models.__test__.API_TESTS
Failed example:
Entries.objects.annotate(clue_count=Count('clues__ID'))
Exception raised:
Traceback (most recent call last):
File "/home/kmt/tmp/django/trunk/django/test/_doctest.py", line
1267, in __run
compileflags, 1) in test.globs
File "<doctest
modeltests.aggregation.models.__test__.API_TESTS[75]>", line 1, in
<module>
Entries.objects.annotate(clue_count=Count('clues__ID'))
File "/home/kmt/tmp/django/trunk/django/db/models/query.py", line
148, in __repr__
data = list(self[:REPR_OUTPUT_SIZE + 1])
File "/home/kmt/tmp/django/trunk/django/db/models/query.py", line
163, in __len__
self._result_cache.extend(list(self._iter))
File "/home/kmt/tmp/django/trunk/django/db/models/query.py", line
281, in iterator
for row in self.query.results_iter():
File "/home/kmt/tmp/django/trunk/django/db/models/sql/query.py",
line 241, in results_iter
for rows in self.execute_sql(MULTI):
File "/home/kmt/tmp/django/trunk/django/db/models/sql/query.py",
line 1974, in execute_sql
cursor.execute(sql, params)
File "/home/kmt/tmp/django/trunk/django/db/backends/mysql/base.py",
line 83, in execute
return self.cursor.execute(query, args)
File "/var/lib/python-support/python2.5/MySQLdb/cursors.py", line
166, in execute
self.errorhandler(self, exc, value)
File "/var/lib/python-support/python2.5/MySQLdb/connections.py",
line 35, in defaulterrorhandler
raise errorclass, errorvalue
ProgrammingError: (1064, "You have an error in your SQL syntax; check
the manual that corresponds to your MySQL server version for the right
syntax to use near 'ID ORDER BY NULL LIMIT 21' at line 1")
----------------------------------------------------------------------
Ran 1 test in 1.266s
FAILED (failures=1)
Destroying test database...
}}}
If I do similar in the shell (against my own DB) and look at the query it
is:
{{{
>>> from crossword.models import Entries
>>> eq = Entries.objects.annotate(clue_count=Count('clues__ID'))
>>> eq[0]
Traceback (most recent call last):
File "<console>", line 1, in <module>
File "/home/kmt/tmp/django/trunk/django/db/models/query.py", line 239,
in __getitem__
return list(qs)[0]
File "/home/kmt/tmp/django/trunk/django/db/models/query.py", line 163,
in __len__
self._result_cache.extend(list(self._iter))
File "/home/kmt/tmp/django/trunk/django/db/models/query.py", line 281,
in iterator
for row in self.query.results_iter():
File "/home/kmt/tmp/django/trunk/django/db/models/sql/query.py", line
241, in results_iter
for rows in self.execute_sql(MULTI):
File "/home/kmt/tmp/django/trunk/django/db/models/sql/query.py", line
1974, in execute_sql
cursor.execute(sql, params)
File "/home/kmt/tmp/django/trunk/django/db/backends/util.py", line 19,
in execute
return self.cursor.execute(sql, params)
File "/home/kmt/tmp/django/trunk/django/db/backends/mysql/base.py", line
83, in execute
return self.cursor.execute(query, args)
File "/var/lib/python-support/python2.5/MySQLdb/cursors.py", line 166,
in execute
self.errorhandler(self, exc, value)
File "/var/lib/python-support/python2.5/MySQLdb/connections.py", line
35, in defaulterrorhandler
raise errorclass, errorvalue
ProgrammingError: (1064, "You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right syntax
to use near 'ID ORDER BY `Entries`.`Entry` ASC LIMIT 1' at line 1")
>>> connection.queries[-1]
{'time': '0.000', 'sql': u'SELECT `Entries`.`Entry ID`, `Entries`.`Entry`,
`Entries`.`Exclude`, COUNT(`Clues`.`ID`) AS `clue_count` FROM `Entries`
LEFT OUTER JOIN `Clues` ON (`Entries`.`Entry ID` = `Clues`.`Entry ID`)
GROUP BY Entries.Entry ID ORDER BY `Entries`.`Entry` ASC LIMIT 1'}
}}}
Seems like the "Entry ID" in the group by is not being quoted as it should
be? (This only fails on MySQL, works on sqlite and PostgreSQL, I didn't
test Oracle.)
--
Ticket URL: <http://code.djangoproject.com/ticket/10250>
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
-~----------~----~----~----~------~----~------~--~---
