• Tom Lane's avatar
    Fix window functions that sort by expressions involving aggregates. · 269c5dd2
    Tom Lane authored
    In commit c1d9579d, I changed things so
    that the output of the Agg node that feeds the window functions would not
    list any ungrouped Vars directly.  Formerly, for example, the Agg tlist
    might have included both "x" and "sum(x)", which is not really valid if
    "x" isn't a grouping column.  If we then had a window function ordering on
    something like "sum(x) + 1", prepare_sort_from_pathkeys would find no exact
    match for this in the Agg tlist, and would conclude that it must recompute
    the expression.  But it would break the expression down to just the Var
    "x", which it would find in the tlist, and then rebuild the ORDER BY
    expression using a reference to the subplan's "x" output.  Now, after the
    above-referenced changes, "x" isn't in the Agg tlist if it's not a grouping
    column, so that prepare_sort_from_pathkeys fails with "could not find
    pathkey item to sort", as reported by Bricklen Anderson.
    
    The fix is to not break down Aggrefs into their component parts, but just
    treat them as irreducible expressions to be sought in the subplan tlist.
    This is definitely OK for the use with respect to window functions in
    grouping_planner, since it just built the tlist being used on the same
    basis.  AFAICT it is safe for other uses too; most of the other call sites
    couldn't encounter Aggrefs anyway.
    269c5dd2
window.sql 9.73 KB