#12822 closed (fixed)
DatabaseError: aggregates not allowed in WHERE clause
Reported by: | Mathieu Pillard | Owned by: | nobody |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | dev |
Severity: | Keywords: | ||
Cc: | Triage Stage: | Ready for checkin | |
Has patch: | yes | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description
From 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.
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:
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):
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.
Attachments (3)
Change History (15)
comment:1 by , 15 years ago
Triage Stage: | Unreviewed → Accepted |
---|
by , 15 years ago
Attachment: | more-aggregation-unittests.diff added |
---|
comment:2 by , 15 years ago
I've tried to come up with a unit test for this bug and found surprising results. I've attached a patch adding my tests against the latest svn and it fails with sqlite too, but not with a DatabaseError
: the results seem simply wrong.
Should I open another bug ? Either I'm completely wrong or it's a serious aggregation bug ; It passes correctly with django 1.1.
Note that with postgresql_psycopg2
the test does fail with the DatabaseError
, so the original bug is still present.
comment:3 by , 15 years ago
Version: | 1.2-beta → SVN |
---|
comment:4 by , 15 years ago
Owner: | set to |
---|---|
Status: | new → assigned |
comment:5 by , 15 years ago
Okay.. so the issue is more complex than originally thought. It does exist in 1.1, but was hidden in certain cases (namely, when the queryset to be used as a subquery was not evaluated prior to being used as a subquery). Attaching a patch with two tests demonstrating the issue. In 1.1, the first test passes, while the second one fails. In 1.2, both tests fail.
by , 15 years ago
Attachment: | ticket_12822_test.diff added |
---|
updated tests demonstrating the actual issue
comment:6 by , 15 years ago
Has patch: | set |
---|---|
Owner: | changed from | to
Status: | assigned → new |
Triage Stage: | Accepted → Ready for checkin |
comment:7 by , 15 years ago
Owner: | changed from | to
---|---|
Status: | new → assigned |
Whoops, need a 1.1 fix as well...
comment:8 by , 15 years ago
Owner: | changed from | to
---|---|
Status: | assigned → new |
same patch applies to 1.1.X branch and fixes the issue there as well
comment:9 by , 15 years ago
Resolution: | → fixed |
---|---|
Status: | new → closed |
comment:10 by , 15 years ago
comment:12 by , 12 years ago
Component: | ORM aggregation → Database layer (models, ORM) |
---|
Unit test (without the hardcoded id this time)