On Tue, Feb 16, 2010 at 4:43 AM, Greg Brown <gregplaysgui...@gmail.com> wrote:
> Ok, here goes:
>
> First, some background - UserProfile is the
> settings.AUTH_PROFILE_MODULE and the Application model has a
> ForeignKey to User. There's ~90,000 users/userprofiles, ~110,000
> applications (most have only one application, some have 2). The
> database is mysql. If that's not enough info, let me know and I'll see
> if I can put together a simple reproduce-able test case.
>
> The queryset giving the problem is
>
> UserProfile.objects.values(
> 'ird_number', 'user__first_name', 'user__last_name', 'user__pk',
> ).annotate(
> Max('user__application__creation_date'),
> ).order_by('-user__application__creation_date__max')
>
> I'm beginning to think it might be a bug - because UserProfile and
> User are joined by a one-to-one relationship, it's sufficient to group
> by one of their primary keys, and I think the ORM should know this.
This is only true for MySQL. Every other database that Django supports
requires a complete list of non-aggregate fields in the GROUP BY
clause - which is the behaviour you are seeing.
> Should I open a ticket for this?
It's probably worth opening a ticket, but not because it's a bug. Like
I said, the 'group by pk' think is a MySQL eccentricity; the SQL you
describe is completely normal for other databases (mod the fact that
your values() clause has a lot less fields than the SQL sample you
gave, but I take it that this is was just truncated for clarity). The
query is that is being issued is *correct*, it's just not *optimal*.
However, there might be an improved optimization to be had. My first
impression is that we might be able to allow the 'group by pk'
optimization as long as the pk is included in the list of fields in a
values query. The current code only allows the optimization in the
case that the list of selected columns is the same as the list of
fields on the requesting model (i.e., the non-values() case). Beefing
up the optimization like this will require a bit of thought to make
sure it isn't catching cases it shouldn't (i.e., is there a query that
includes the PK for which the 'group by pk' optimization isn't valid),
but providing we aren't going to break anything,
Yours,
Russ Magee %-)
--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To post to this group, send email to django-us...@googlegroups.com.
To unsubscribe from this group, send email to
django-users+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en.
