Re: Add PRODUCT() aggregate function - Mailing list pgsql-hackers

Jeevan Chalke <jeevan.chalke@enterprisedb.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

                        regards, tom lane