Re: Window function bug

Jeff Davis <pgsql(at)j-davis(dot)com> writes:
> In branch postgresql/master:
> SELECT SUM(SUM(a)) OVER ()
> FROM (SELECT NULL::int4 AS a WHERE FALSE) R;
> ERROR: XX000: cannot extract attribute from empty tuple slot

Huh, interesting.

> Honestly, I'm not sure what the semantics of that are supposed to be. Is
> it even allowed by the standard?

Yeah, I believe so. Aggregate calls within window function calls are
supposed to be legal. They're not terribly useful unless there's a
GROUP BY clause --- when there is, you get a row per group out of the
aggregates, and then it's sensible to apply windowing functions on that
rowset. This is a pretty degenerate case ... but it ought not fail.

After tracing through it, it seems the bug is that the planner generates
a targetlist for the Agg node containing "a, SUM(a)", and then when that
is evaluated for a case where no row was ever produced by the subquery,
the executor quite properly fails, since there's noplace to get a value
of "a" from. The targetlist is built by these statements in planner.c:

window_tlist = flatten_tlist(tlist);
if (parse->hasAggs)
window_tlist = add_to_flat_tlist(window_tlist,
pull_agg_clause((Node *) tlist));
window_tlist = add_volatile_sort_exprs(window_tlist, tlist,
activeWindows);

so I guess the answer is that this code ought to avoid adding Vars that
are only mentioned within aggregates. Perhaps also omit those only used
within volatile sort expressions, though I think that would just be an
efficiency issue not a correctness issue, and it may be unreasonably
expensive to determine that.

regards, tom lane