On Sat, 6 Jan 2024, 19:49 Tom Lane, <t...@sss.pgh.pa.us> wrote: > "David G. Johnston" <david.g.johns...@gmail.com> writes: > > Something does seem off here with the interaction between grouping sets > and > > order by. > > Yeah. I think Geoff is correct to identify the use of subqueries in > the grouping sets as the triggering factor.
[snip] > I think this particular symptom might be new, but we've definitely > seen related trouble reports before. I'm inclined to think that the > right fix will require making the parser actually replace such > expressions with Vars referencing a notional grouping output relation, > so that there's not multiple instances of the sub-query in the parser > output in the first place. Well yes. I assumed that since it's required that a group expression is in the query itself that the grouping values were taken from the result set, I have to admit to some surprise that they're calculated twice (three times?). That's a fairly big job and nobody's > tackled it yet. For what it's worth, as a user if we could reference a column alias in the GROUP and ORDER sections, rather than having to respecify the expression each time, that would be a far more friendly solution. Not sure it makes the work any less difficult though. In the meantime, what I'd suggest as a workaround is to put those > subexpressions into a sub-select with an optimization fence (you > could use OFFSET 0 or a materialized CTE), so that the grouping > sets list in the outer query just has simple Vars as elements. > Not possible in our case, sadly - at least not without a complete redesign of our SQL-generating code. It would be (much) easier to add a sort to the output stage, tbh, and stop lazily relying on the output being sorted for us; I guess that's the route we'll have to take. Thanks all for taking the time to look at it. Geoff
