Re: Add PRODUCT() aggregate function

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".

regards, tom lane