On 11/09/2026 05:54, Jeevan Chalke wrote:
On Fri, Sep 11, 2026 at 8:01 AM Tom Lane <[email protected]> wrote:
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".
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
