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
12822	DatabaseError: aggregates not allowed in WHERE clause	Mathieu Pillard	nobody	"From [http://groups.google.com/group/django-users/browse_thread/thread/2fbd258cd90bc29c# django-users] ; I have been searching for an existing bug and couldn't find any in the ORM/ORM Aggregation component.

The following code works with django 1.1, and returns the expected results, but fails with 1.2 beta 1. It's a basic messaging system, in which you can group messages by
conversations : when saving a new Foo object, you can give it an existing Foo id to form a conversation. I want to display the ""inbox""
for a user, which should be a list with the last message from each conversation.

{{{
#!python
from django.db import models
from django.contrib.auth.models import User

class Foo(models.Model):
   subject = models.CharField(max_length=120)
   sender = models.ForeignKey(User, related_name='sent_foo')
   recipient = models.ForeignKey(User, related_name='received_foo')
   conversation = models.ForeignKey('self', null=True, blank=True)

from django.db.models import Max

def conversations(self, user):
   tmp = Foo.objects.values('conversation').annotate(Max('id')).values_list('id__max', flat=True).order_by( 'conversation')
   return Foo.objects.filter(id__in=tmp.filter(recipient=user))
}}}

In 1.2 beta 1, with postgresql_psycopg2, it fails with:
{{{
DatabaseError: aggregates not allowed in WHERE clause
LINE 1: ...d"" FROM ""mat_foo"" WHERE ""mat_foo"".""id"" IN (SELECT MAX(""mat_f...
}}}

The generated SQL queries are a bit different. 
Here is django 1.2:
{{{
#!sql
SELECT ""mat_foo"".""id"", ""mat_foo"".""subject"", ""mat_foo"".""sender_id"", ""mat_foo"".""recipient_id"", ""mat_foo"".""conversation_id"" FROM ""mat_foo""
WHERE ""mat_foo"".""id"" IN (SELECT MAX(""mat_foo"".""id"") AS ""id__max"" FROM ""mat_foo"" U0 WHERE U0.""recipient_id"" = 1  GROUP BY U0.""conversation_id"")
}}}

And here is django 1.1 (which works):
{{{
#!sql
SELECT ""mat_foo"".""id"", ""mat_foo"".""subject"", ""mat_foo"".""sender_id"", ""mat_foo"".""recipient_id"", ""mat_foo"".""conversation_id"" FROM ""mat_foo""
WHERE ""mat_foo"".""id"" IN (SELECT MAX(U0.""id"") AS ""id__max"" FROM ""mat_foo"" U0 WHERE U0.""recipient_id"" = 1  GROUP BY U0.""conversation_id"")
}}}

Note the difference in the MAX() clause. For what it's worth, it does work with sqlite. Didn't test other databases. I suspect there is a better way to write my query, but in any case, I don't think it should fail like that, especially since it was working with 1.1."		closed	Database layer (models, ORM)	dev		fixed			Ready for checkin	1	0	0	0	0	0