Re: ON EMPTY clause for aggregate and window functions

Hackers,

Following up on this thread (and the related discussion on the PRODUCT()
thread
https://www.postgresql.org/message-id/flat/CAM2%2B6%3DWG8gpOti%2B1-N_ra0mNcNrdhMjSJcDvcA7wzjLD-qKH3g%40mail.gmail.com
):
I've reworked the ON EMPTY patch and would like feedback on the new
design before going further.

Thanks to Tom for pointing out the strict/non-strict distinction, and to Vik
for quoting the SQL committee's actual definition on the PRODUCT() thread:

PRODUCT(SQ VE, 1 ON EMPTY) => COALESCE(PRODUCT(SQ VE), 1)

i.e. the standard defines ON EMPTY, for the aggregates it actually
standardizes (SUM/PRODUCT), as nothing more than COALESCE over the whole
aggregate result. That prompted me to implement the general (PG extension)
version the same way.

What changed
---
ON EMPTY is now implemented as exactly:

agg(args, default ON EMPTY) == COALESCE(agg(args), default)

for both plain and window aggregates: compute the aggregate's ordinary
result exactly as without ON EMPTY, and substitute the default only if that
result is NULL. No new expression-evaluation step, no per-row bookkeeping,
and no restriction on partial/parallel aggregation.

The earlier design instead tracked, per group, whether any row was actually
fed to the transition function (to distinguish true emptiness from input
that merely computes to NULL). I dropped it: testing showed some built-in
aggregates use a differently-strict transition function for plain vs.
windowed use (e.g. sum(int4): int4_sum, non-strict, vs. int4_avg_accum,
strict), so the same clause over the same all-NULL data fired for a
windowed sum() but not a plain one. That's an implementation detail
leaking into user-visible behavior, not something worth preserving.

As a side benefit, the earlier restriction disabling partial/parallel
aggregation for ON EMPTY aggregates is also gone -- finalization always
happens centrally, so the COALESCE check works regardless of how the
transition state was combined.

The tradeoff, stated plainly
---
Because it's exactly COALESCE, ON EMPTY can't distinguish "zero rows" from
"rows were processed, but the result is legitimately NULL", for any
aggregate. For example, `stddev(x, -1 ON EMPTY)` over a single row returns
-1, even though that row was genuinely processed (stddev_samp of one value
is undefined and NULL by definition). Same story for percentile_cont over a
non-empty, all-NULL input.

I'd rather have a simple, uniform rule with a stated limitation than a
"smarter" rule that disagrees with itself depending on plan shape. Open to
recovering the stricter distinction for a subset of aggregates if there's
appetite for it, but I don't think it should be the default.

Patch and testing
---
Now a single patch (the earlier JIT-support patch is gone, since ON EMPTY no
longer needs a new expression step for the JIT compiler to support). Full
regression suite passes, including under a forced-JIT build.

Thanks,

On Sun, Jun 28, 2026 at 6:26 PM Jeevan Chalke <
jeevan(dot)chalke(at)enterprisedb(dot)com> wrote:

> Hello Isaac,
>
> Good points on both counts.
>
> Regarding INITCOND, you are correct that it only sets the initial state.
> I tried exploiting it in my very first attempt too. However, if the
> aggregate isn't invoked at all (zero input rows), INITCOND is never
> used to determine the final result, which is why we need this mechanism
> to return something at finalization when the input set was empty.
> The majority of the code changes here are to determine whether we've
> received any input or not.
>
> I'm open to renaming 'default_value' if a better term comes up.
> 'Value_at_empty_input' is certainly more precise than 'default',
> but it's long. I'll keep an eye out for other suggestions; I'm not
> attached to the current name and will be happy to rename it once we
> settle on one.
>
> Thanks for reviewing.
>
> On Fri, Jun 26, 2026 at 6:51 PM Isaac Morland <isaac(dot)morland(at)gmail(dot)com>
> wrote:
>
>> On Fri, 26 Jun 2026 at 05:12, Jeevan Chalke <
>> jeevan(dot)chalke(at)enterprisedb(dot)com> wrote:
>>
>>> 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:
>>>
>>
>> Is there any chance of storing a default default_value with the
>> aggregate? I ask because for most aggregate functions there is a specific
>> value for each function which is almost always what will be wanted, e.g., 0
>> for sum, -Infinity for max, +Infinity for min, 1 for multiplication (if the
>> other proposal for a multiplication aggregate is accepted), and so on,
>> generally characterizable as the identity element for the function in
>> question. Only in rare cases would one actually want to override the
>> identity and use some other specified value.
>>
>> I was going to suggest there would need to be an additional clause for
>> CREATE AGGREGATE, but I see there is already an INITCOND parameter which in
>> principle should already be doing the job (except that as I understand it
>> the aggregate isn't invoked at all for empty input?).
>>
>> Also I'm not entirely happy with the name "default_value". It's not
>> really a default, just the value of the aggregate at empty input.
>> Unfortunately, I don't have a better suggestion.
>>
>
>
> --
> *Jeevan Chalke*
> *Senior Principal Engineer, Engineering Manager*
> *Product Development*
>
> enterprisedb.com <https://www.enterprisedb.com>
>

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

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