Re: ON EMPTY clause for aggregate and window functions

Re: ON EMPTY clause for aggregate and window functions

Robert Haas  writes:
> This seems pretty useless -- the new syntax is more work for us to
> maintain, and doesn't really add any value over just using COALESCE.

It could add value, in scenarios where substitute-for-NULL doesn't
give quite the behavior you want.  But that's not so for the two
aggregates the spec has bothered to define this for, and I'm having
a hard time coming up with an aggregate for which it would be so.

If somebody actually did have an aggregate that could return null for
more than zero input rows, they could always do

    CASE WHEN count(x) > 0 THEN frobnitz(x) ELSE value_for_zero_rows END

which has the extra benefit that you can choose "count(x)" or
"count(*)" depending on your desires for what to do with null
inputs.

> To be clear, I expect to lose this argument on the grounds that
> apparently this syntax is in the spec and therefore we ought to
> support it. But I don't understand why the spec -- or we -- should
> spend time inventing new ways to spell existing behaviors. That just
> seems confusing (and this particular choice of syntax seems
> extra-confusing).

Yes, this choice of syntax sucks pretty badly.  In the COALESCE
spelling, it's crystal clear that the substitute value is not an
aggregate argument and so is evaluated at most once (per group);
there's no need for gamesmanship around restricting it to be a
constant.

I'd be totally fine with rejecting this as a frammish we do
not care to support.

			regards, tom lane

Re: ON EMPTY clause for aggregate and window functions

Vik Fearing  writes:
> On 17/09/2026 15:31, Tom Lane wrote:
>> I'd be totally fine with rejecting this as a frammish we do
>> not care to support.

> I am not the most unbiased person, but I would like us to implement it.
> If for nothing else, then for helping people convert from database 
> implementations that do have it.

It's not zero cost.  Robert already mentioned the
development/maintenance effort involved, and it also bloats the Bison
grammar rules, creating some incremental penalty on parsing speed.
Admittedly these costs aren't large, but neither is the benefit
of supporting it.

			regards, tom lane

Re: BUG #19638: Planner chooses an index-only scan for an index AM without amcanreturn, and execution fails

Re: BUG #19638: Planner chooses an index-only scan for an index AM without amcanreturn, and execution fails

Re: ON EMPTY clause for aggregate and window functions

On Sat, Sep 12, 2026 at 9:19 AM Jeevan Chalke
 wrote:
> ON EMPTY is now implemented as exactly:
>
>     agg(args, default ON EMPTY)  ==  COALESCE(agg(args), default)
>
> for both plain and window aggregates: compute the aggregate's ordinary
> result exactly as without ON EMPTY, and substitute the default only if that
> result is NULL. No new expression-evaluation step, no per-row bookkeeping,
> and no restriction on partial/parallel aggregation.

This seems pretty useless -- the new syntax is more work for us to
maintain, and doesn't really add any value over just using COALESCE.

To be clear, I expect to lose this argument on the grounds that
apparently this syntax is in the spec and therefore we ought to
support it. But I don't understand why the spec -- or we -- should
spend time inventing new ways to spell existing behaviors. That just
seems confusing (and this particular choice of syntax seems
extra-confusing).

-- 
Robert Haas

Re: BUG #19638: Planner chooses an index-only scan for an index AM without amcanreturn, and execution fails

Re: BUG #19638: Planner chooses an index-only scan for an index AM without amcanreturn, and execution fails

Re: ON EMPTY clause for aggregate and window functions

Re: ON EMPTY clause for aggregate and window functions

Re: ON EMPTY clause for aggregate and window functions

On 17/09/2026 15:31, Tom Lane wrote:
> Yes, this choice of syntax sucks pretty badly.


I agree. The next edition isn't out yet, if we would like to suggest 
something better.


> I'd be totally fine with rejecting this as a frammish we do
> not care to support.


I am not the most unbiased person, but I would like us to implement it.  
If for nothing else, then for helping people convert from database 
implementations that do have it.

-- 

Vik Fearing

Re: ON EMPTY clause for aggregate and window functions

On 12/09/2026 15:18, Jeevan Chalke wrote:
>
> ON EMPTY is now implemented as exactly:
>
> agg(args, default ON EMPTY)  ==  COALESCE(agg(args), default)


I've taken a quick look at this, and I found a few bugs.


1) The first one is that the constant requirement only looks for actual 
constants and not scoped constants.  For example:


CREATE TABLE cust (id INTEGER, name text, def_amount PRIMARY KEY (id));
CREATE TABLE ord (id INTEGER, custid INTEGER, amount INTEGER);
INSERT INTO cust SELECT g, 'c' || g, g FROM generate_series(1, 4) AS g (g);
INSERT INTO ord VALUES (1,1,100), (2,1,50), (3,3,7);

-- rejected: "ON EMPTY expression must be a constant value"
SELECT c.id,
        (SELECT SUM(o.amount, c.def_amount ON EMPTY)
         FROM ord AS o
         WHERE o.custid = c.id)
FROM cust AS c;


Here, the c.def_amount is constant for the subquery and should be 
accepted.  The example is perhaps a bit contrived, but the logic is sound.


2) Another bug I found is this:


CREATE TABLE mm (a INTEGER);
INSERT INTO mm SELECT g FROM generate_series(1, 10_000) AS g (g);
ANALYZE mm;

SELECT COALESCE(MAX(a), -1) FROM mm WHERE a > 100_000;  --  -1
SELECT MAX(a, -1 ON EMPTY)  FROM mm WHERE a > 100_000;  --  -1

CREATE INDEX ON mm (a);

SELECT COALESCE(MAX(a), -1) FROM mm WHERE a > 100_000;  -- -1
SELECT MAX(a, -1 ON EMPTY)  FROM mm WHERE a > 100_000;  --  NULL

When MAX and MIN get optimized with an index, the ON EMPTY seems to be 
dropped.


3) The set quantifier is not recognized.


SELECT SUM(ALL      a, 0 ON EMPTY) FROM t;
SELECT SUM(DISTINCT a, 0 ON EMPTY) FROM t;


Neither of those parse.


I will keep reviewing this feature.

-- 

Vik Fearing