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
10250	MySQL quoting not happening for aggregation group by?	Karen Tracey		"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.)"		closed	Database layer (models, ORM)	1.0		fixed			Accepted	0	0	0	0	0	0