Re: Add PRODUCT() aggregate function

On 11/09/2026 05:54, Jeevan Chalke wrote:
>
>
> 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.

Its definition in the standard is exactly:

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

where SQ is ALL/DISTINCT and VE is the value expression.

--

Vik Fearing