Code

Opened 5 years ago

Closed 5 years ago

Last modified 17 months ago

#10250 closed (fixed)

MySQL quoting not happening for aggregation group by?

Reported by: kmtracey Owned by:
Component: Database layer (models, ORM) Version: 1.0
Severity: Keywords:
Cc: Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: UI/UX:

Description

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

Attachments (0)

Change History (4)

comment:1 Changed 5 years ago by jacob

  • milestone set to 1.1
  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Triage Stage changed from Unreviewed to Accepted

comment:2 Changed 5 years ago by russellm

  • Resolution set to fixed
  • Status changed from new to closed

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

comment:3 Changed 3 years ago by jacob

  • milestone 1.1 deleted

Milestone 1.1 deleted

comment:4 Changed 17 months ago by akaariai

  • Component changed from ORM aggregation to Database layer (models, ORM)

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as closed
as The resolution will be set. Next status will be 'closed'
The resolution will be deleted. Next status will be 'new'
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.