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.)

[Russell Keith-Magee]

(In [10043]) Fixed #10250 -- Added a regression test to ensure that GROUP BY statements are correctly quoted under MySQL. This appears to have been corrected inadvertently since the original report, but the extra regression test will make sure it stays that way.

[Jacob]

Milestone 1.1 deleted