Opened 6 years ago

Closed 6 years ago

Last modified 2 years ago

#10425 closed (fixed)

Bad SQL generated for Book.objects.values('author').annotate(Count('author')).count()

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

Description

Observed with SVN revision 9984:

Model:

class Book(models.Model):
    title = models.CharField(max_length=200)
    author = models.CharField(max_length=200)

When using values() with annotate(), the count() method generates bad SQL:

$ python manage.py shell
Python 2.6.1 (r261:67515, Dec  6 2008, 16:42:21) 
[GCC 4.0.1 (Apple Computer, Inc. build 5370)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
(InteractiveConsole)
>>> from example.models import *
>>> from django.db.models import Count
>>> qs = Book.objects.values('author').annotate(Count('author'))
>>> qs.count()
Traceback (most recent call last):
  File "<console>", line 1, in <module>
  File "/Users/kevin/django/bug/annotate_count/django/db/models/query.py", line 329, in count
    return self.query.get_count()
  File "/Users/kevin/django/bug/annotate_count/django/db/models/sql/query.py", line 345, in get_count
    number = obj.get_aggregation()[None]
  File "/Users/kevin/django/bug/annotate_count/django/db/models/sql/query.py", line 317, in get_aggregation
    result = query.execute_sql(SINGLE)
  File "/Users/kevin/django/bug/annotate_count/django/db/models/sql/query.py", line 2097, in execute_sql
    cursor.execute(sql, params)
  File "/Users/kevin/django/bug/annotate_count/django/db/backends/util.py", line 19, in execute
    return self.cursor.execute(sql, params)
  File "/Users/kevin/django/bug/annotate_count/django/db/backends/sqlite3/base.py", line 190, in execute
    return Database.Cursor.execute(self, query, params)
OperationalError: near "FROM": syntax error


The SQL that gets generated is malformed:

SELECT  FROM (SELECT "example_book"."author" AS author, COUNT("example_book"."author") FROM "example_book" GROUP BY "example_book"."author") subquery

Change History (5)

comment:1 Changed 6 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 6 years ago by alen__ribic

Has this been fixed? I can't simulate the problem my end. My qs.count() returns a correct value with no exception.

-Alen

comment:3 Changed 6 years ago by russellm

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

(In [10053]) Fixed #10425 -- Corrected the interaction of .count() with .annotate() when .values() is also involved. Thanks to kmassey for the report.

comment:4 Changed 3 years ago by jacob

  • milestone 1.1 deleted

Milestone 1.1 deleted

comment:5 Changed 2 years ago by akaariai

  • Component changed from ORM aggregation to Database layer (models, ORM)
Note: See TracTickets for help on using tickets.
Back to Top