On Wed, Feb 18, 2009 at 8:05 AM, Ian Kelly <[email protected]> wrote: > > I'd like to request some extra sets of eyes for the patch in ticket #10290. > > The summary: from r9838 on, I'm getting aggregation_regress test > failures in Oracle, apparently because annotations with extra selects > are adding the extra select aliases into the group by list. This > produces invalid SQL (at least in Oracle), since you can't use column > aliases defined at the same level within a group by clause.
Sigh. You know, it's times like this that I'm really glad that we have a standard language that is common across all relational database implementations.... > 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. We have to do a similar thing for the aggregates themselves; Postgres can't use an alias in the HAVING clause. However, Postgres doesn't seem to care about using aliases in the GROUP BY. In that case I erred on the side of caution and just made all backends use the full clause. If allowed alias usage is significantly different between backends (as it appears to be), it may be worth refactoring this. Out of interest - where does Oracle fall on this one? Can you use aliases in a HAVING or ORDER BY? > If there are no concerns or potential gotchas with this approach, then > I'll go ahead and commit this in a few days. My only major concern would be any potential performance hit from double-computing the extra clause. Alex's suggestion to parameterize this in the connection ops backend sounds like a good idea to me. However, rather than just defining a boolean feature flag, my preference would be to define a 'expand_extra()' function (or similar - bikeshed builder gets to choose the colour) on the ops backend which just returns the alias in the default case, but returns the full expression under Oracle. I should probably do the same for alias usage in HAVING/ORDER_BY clauses. Yours, Russ Magee %-) --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
