Jeevan Chalke <[email protected]> writes:
> In my currently proposed patch (
> https://www.postgresql.org/message-id/CAM2+6=VS=fSKxfimW6Th9iu_xjbxOEAKg4eYwaa=smg3x8p...@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
