On Tue, 2009-02-17 at 18:15 -0700, Ian Kelly wrote: > On Tue, Feb 17, 2009 at 5:54 PM, Malcolm Tredinnick > <[email protected]> wrote: > > > > Hi Ian, > > > > On Tue, 2009-02-17 at 16:05 -0700, Ian Kelly wrote: > > [...] > >> The solution that I'm proposing is to use the extra select expression > >> itself in the group by, rather than the alias. This passes the tests > >> across all four included backends, and seems to work in general as > >> long as the expression is not a scalar subquery (in which case I think > >> a general solution would require advanced inspection of the > >> expression). But it bothers me a bit because it just feels fragile. > > > > I'm pretty tired today and under a pile of past-urgent things to do, so > > excuse the request for training wheels on this one... can you give a > > short SQL example of what you're talking about here (before vs. after). > > No problem. Take the queryset > `Author.objects.extra(select={'sqrt_age': > 'sqrt(age)'}).values('sqrt_age').annotate(Count('book'))` > > Current SQL: SELECT (sqrt(age)) AS "SQRT_AGE", > COUNT("AGGREGATION_REGRESS_BOOK_AC81D"."BOOK_ID") AS "BOOK__COUNT" > FROM "AGGREGATION_REGRESS_AUTHOR" LEFT OUTER JOIN > "AGGREGATION_REGRESS_BOOK_AC81D" ON ("AGGREGATION_REGRESS_AUTHOR"."ID" > = "AGGREGATION_REGRESS_BOOK_AC81D"."AUTHOR_ID") GROUP BY "SQRT_AGE" > > Proposed SQL: SELECT (sqrt(age)) AS "SQRT_AGE", > COUNT("AGGREGATION_REGRESS_BOOK_AC81D"."BOOK_ID") AS "BOOK__COUNT" > FROM "AGGREGATION_REGRESS_AUTHOR" LEFT OUTER JOIN > "AGGREGATION_REGRESS_BOOK_AC81D" ON ("AGGREGATION_REGRESS_AUTHOR"."ID" > = "AGGREGATION_REGRESS_BOOK_AC81D"."AUTHOR_ID") GROUP BY (sqrt(age))
Ah, ok, I understand what you're talking about now. I don't see anything horribly bad about this change. To be SQL spec-compliant, we would have to do this. In addition to Oracle, SQL Server can't handle aliases in GROUP BY. PostgreSQL can only do it sometimes (see [1], which it turns out I'd bookmarked a couple of years ago) and acknowledge it's a bit fragile to use aliases. I'm not particularly worried about Alex's concern of backend optimisers that for some reason support aliases, but can't do common sub-expression optimisation in exactly the same case. They're just broken, if they exist at all. So lacking concrete evidence, I'd choose not to be scared by that right now (and keep the internals as simple as possible). Any particular grounds for doing something spec-compliant feeling fragile to you, Ian? Having let this bounce around for 24 hours, I can't think of a strong reason not to do it. [1] http://archives.postgresql.org/pgsql-sql/2004-02/msg00143.php Regards, Malcolm --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Django developers" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/django-developers?hl=en -~----------~----~----~----~------~----~------~--~---
