single table annotation fails while using COUNT

[GoogleCodeExporter]

What steps will reproduce the problem?

I saw this in the unit tests (this works for me as well when i do cross
relationship):

Book.objects.all().values('rating').annotate(n_authors=Count('authors__id'), 
mean_age=Avg('authors__age')).order_by('rating')

I have a similar construct, except the COUNT is not being done across a
relationship, but rather on itself, such as (give me the number of patients
per zipcode):

Patient.objects.values('zipcode').annotate(cnt=Count('id'))

SQL generated (right before being executed):
SELECT * 
FROM (
    SELECT [staging_stagingpatient].[zipcode] AS [zipcode], 
        COUNT([staging_stagingpatient].[id]) AS [cnt], 
        (ROW_NUMBER() OVER (ORDER BY [staging_stagingpatient].[id] ASC)) AS
[rn] 
    FROM [staging_stagingpatient] 
    GROUP BY [staging_stagingpatient].[zipcode]) 
AS X WHERE X.rn BETWEEN 1 AND 21

Error: Column "staging_stagingpatient.id" is invalid in the select list
because it is not contained in either an aggregate function or the GROUP BY
clause.

This seems to be due to the [rn] clause since I am requesting only
"zipcode" and "cnt" and the GROUP BY requires use of the [id] column, but I
don't want need or want it.

(And on a side note, why is my query being arbitrarily limited to 20 rows?
I didn't set a limit in the query.)

This is the query printed before being executed by foo.query.as_sql():

SELECT [staging_stagingpatient].[zipcode], 
    COUNT([staging_stagingpatient].[id]) AS [cnt] 
FROM [staging_stagingpatient] 
GROUP BY [staging_stagingpatient].[zipcode]

This is dead on and exactly what i would expect. Is there a reason why
extra stuff is being added to the query?

What version of the product are you using? On what operating system?

django-pyodbc rev 171, OS X 10.5

Original issue reported on code.google.com by bjr...@gmail.com on 23 Jul 2009 at 9:25

[GoogleCodeExporter]

It's __repr__ issue. If you print a queryset, will only print 
[:REPR_OUTPUT_SIZE+1] 
row. REPR_OUTPUT_SIZE default is 20.
(see django\db\models\query.py line 67)

Original comment by vcc.ch...@gmail.com on 25 Jul 2009 at 1:38

• Changed state: Invalid

[GoogleCodeExporter]

Excellent. Good to know.

Original comment by bjr...@gmail.com on 27 Jul 2009 at 2:49