Possible bug with django 1.2, postgresql and aggregates?

[Mathieu Pillard]

Hi,

I have been testing the 1.2 beta1 and think I found a bug, but since
the query I'm using is a bit complicated I wanted to run it through
the list first.

The model I'm using:

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)

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. The following code works in 1.1:

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

However, 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:
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:
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")

The only difference is in the MAX() clause. Anyone can enlighten me
about what's happening ? Is that a (known?) django 1.2 bug or am I
pushing the ORM a little too far? It looks like sqlite doesn't
complain with the same code, but I didn't test if the results were
right.

Thanks

[Brian]

It may indeed be that the MAX clause is using "mat_foo" instead of the
assigned correlation U0. What happens if you paste the two SQLs into a
query window in PgAdmin 3 and execute them. If the 1.2 query fails
with the same error message, I'd report a bug.

If it doesn't fail, it's still a Django issue, but I have no idea what
it could be. I'm very new to Django; not new to databases.

Brian

[Mathieu Pillard]

> It may indeed be that the MAX clause is using "mat_foo" instead of the
> assigned correlation U0. What happens if you paste the two SQLs into a
> query window in PgAdmin 3 and execute them. If the 1.2 query fails
> with the same error message, I'd report a bug.
>
> If it doesn't fail, it's still a Django issue, but I have no idea what
> it could be. I'm very new to Django; not new to databases.

Ah, I forgot to mention: it does fail when copy/pasting the query
django 1.2 generated in psql/pgadmin.

I'm not very familiar with postgres and complex queries like this (and
I suspect there is a better way of doing what I'm doing) ; it's
obvious the difference between the 2 queries is what's causing my
problem, but I have no idea *why*, therefore I haven't reported the
bug yet. An explanation from a django wizard or a psql ninja would be
great :-)

[Russell Keith-Magee]

From a Django API perspective, I can't see anything obviously wrong
with your query. Looks like you've found a bug. The fact that the
query rolls out differently between 1.1 and 1.2 is cause for concern.

I have a vague recollection that I've seen something simliar to this
reported recently in Trac, but I can't put my finger on the ticket
number. Please have a quick search around Trac to see if you can find
anything (the ticket should be in the 11000+ range). If you can't,
please open a new ticket and mark it for Milestone 1.2, ORM component.

Thanks,
Russ Magee %-)

[Mathieu Pillard]

I searched for an existing ticket, couldn't find any. I created
http://code.djangoproject.com/ticket/12822