ON EMPTY clause for aggregate and window functions

Hello Hackers,

Here is a patch set adding an optional ON EMPTY clause to aggregate (and
aggregate-as-window-function) calls. It supplies a value to return when the
aggregate processes no input rows at all:

agg_function(args, default_value ON EMPTY)

For example:

SELECT sum(i, -1 ON EMPTY)
FROM generate_series(1,10) AS s(i) WHERE i > 100;
sum
-----
-1
(1 row)

ON EMPTY is triggered only by an empty input set, not by NULL inputs that
are
ignored during aggregation. A FILTER that removes all rows makes the input
set empty, so the default applies in that case too. Because a grouped query
never produces empty groups, ON EMPTY takes effect for an ungrouped
aggregate
over zero rows, or for a group whose rows are all removed by FILTER. For an
aggregate used as a window function, the default is returned for any row
whose
frame contains no rows. It also works with an ordered-set aggregate, with
the
default written before WITHIN GROUP:

percentile_cont(0.5, -7 ON EMPTY) WITHIN GROUP (ORDER BY v)

ON EMPTY for aggregates is understood to be part of an upcoming revision of
the SQL standard.

The default_value must be a constant-like expression coercible to the
aggregate's result type: no column references (at any query level),
aggregates, window functions, subqueries, or volatile functions. It cannot
be combined with DISTINCT, and is rejected for non-aggregate window
functions.

*Implementation*: detection uses a per-group "input received" flag, set by a
dedicated expression step emitted after any FILTER but before the
strict-input
NULL check, so a NULL input still counts as input (a strict transition
function would otherwise skip it and an all-NULL group would be misread as
empty). At finalization, if the flag is unset, the default is evaluated and
returned in place of the normal result. Such aggregates are not partially
aggregated, since a parallel leader sees only combined transition states,
not
the original rows. The commit messages have the details.

*Patches*:
0001 - feature: grammar, parser, node support, planner, deparse, executor
(interpreter), documentation, and regression tests. Functional
without LLVM.
0002 - JIT support for the new expression step. 0001 and 0002 should be
applied together on --with-llvm builds; the split is only to make
the JIT part easy to review, and I'm happy to squash them. I have
limited expertise with the JIT code, so I'd appreciate help if
something
here is wrong.

make installcheck passes in both a --without-llvm build (interpreter) and a
--with-llvm build with jit = on (LLVM 17).

The current syntax is quite limited. Since there's no SQL standard for this
yet,
any feedback or suggestions on the syntax/grammar are very welcome.

Thanks,

--
*Jeevan Chalke*
*Senior Principal Engineer, Engineering Manager*
*Product Development*

enterprisedb.com <https://www.enterprisedb.com>