annotate() + order_by() == aborted transaction?

[Don Spaulding]

Hi all,

I've got a quick use case that I think should work according to the
docs, but it's not. In this case, Domain objects have a reverse fkey
relation named "reports", and the Report model has an "updated"
field. I'd like to annotate and order Domains by the max value of
"updated" for all related reports. The following interactive session
shows the behavior pretty well.

>>> qs = Domain.objects.annotate(last_updated=Max('reports__updated')).order_by('last_updated')
>>> qs.count()
1577
>>> print qs[0].last_updated
IndexError: list index out of range
>>> qs.count()
InternalError: current transaction is aborted, commands ignored until
end of transaction block
>>> connection._rollback()

If I drop off the order_by call, things appear to work again.

>>> qs = Domain.objects.annotate(last_updated=Max('reports__updated'))
>>> qs.count()
1577
>>> print qs[0].last_updated
2009-05-28 13:25:55.027600

What am I missing here (besides a thorough understanding of
aggregation)?

[Don Spaulding]

bump.

Can anyone tell me if this looks like a bug in Django?

[Alex Gaynor]

It looks like a bug in django at first glance.  Out of curiosity what version of Django is it, since it looks a tiny bit like an old bug with queryset chaining, but I think that was fixed even before the first beta.

Alex

--
"I disapprove of what you say, but I will defend to the death your right to say it." --Voltaire
"The people's good is the highest law."--Cicero

[Don Spaulding]

On Jun 3, 3:05 pm, Alex Gaynor <alex.gay...@gmail.com> wrote:

>>> django.get_version()
u'1.1 beta 1 SVN-10916'

[Alex Gaynor]

Ok, based on what you pasted it's almost certainly a bug in Django.  Please file a bug, and if you can try to write up a testcase within the Django test framework, but if not try to provide a minimal set of Models and code to reproduce the bug.

[Don Spaulding]

Hmm, after updating to u'1.1 beta 1 SVN-10921' I'm now getting a
psycopg2.ProgrammingError: ORDER BY "last_updated" is ambiguous.

I've put the SQL it generates (I've only sanitized the two table
names) up at http://dpaste.com/51213/

[Don Spaulding]

And of course, therein lies my problem, I've gone and tried to
annotate each record with a 'last_updated' calculation, and the model
already has an actual 'last_updated' field. The error only shows up
when django tries to tell the SQL engine to order_by something that's
been defined twice in the query.

I'll raise a ticket on the tracker about possibly raising an exception
instead of allowing users (stupid ones like me) to clobber their own
namespaces.

[Don Spaulding]

Ticket filed at: http://code.djangoproject.com/ticket/11256