Re: Add PRODUCT() aggregate function

On Fri, Sep 11, 2026 at 8:01 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Jeevan Chalke <jeevan(dot)chalke(at)enterprisedb(dot)com> writes:
> > In my currently proposed patch (
> >
> https://www.postgresql.org/message-id/CAM2+6=VS=fSKxfimW6Th9iu_xjbxOEAKg4eYwaa=SMg3X8pHaQ@mail.gmail.com
> ),
> > the ON EMPTY value is strictly returned only when there are zero input
> > rows. Rows containing NULL are treated as valid rows and do not trigger
> the ON
> > EMPTY clause.
>
> [ ... not having read the patch ... ] There is a critical distinction
> here between strict and non-strict aggregates. My interpretation of
> how this should work is that ON EMPTY should trigger if zero rows were
> fed to the aggregate's transition function. A row containing NULL is
> valid input if the transition function is non-strict, otherwise it is
> not.
>
> What I gather from Vik's comments is that the SQL committee only
> formalized the behavior for strict aggregates (since both PRODUCT
> and SUM ignore nulls). So we're somewhat out on a limb here for
> the non-strict case, but I think we have to define that one as
> being "null inputs count as inputs".
>

Agree with the strict/non-strict point. But the SQL standard text for this
is
not available yet, so I am not sure what exact behaviour we should follow
here.

Do you or Vik have more details on what the committee is going with? That
will
help us decide the correct semantics instead of guessing.

Thanks

> regards, tom lane
>

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

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