Re: ON EMPTY clause for aggregate and window functions - Mailing list pgsql-hackers
Hi Vik,
Thanks for testing this so carefully. All three are fixed in the updated
patch:
1. Correlated outer column as default: fixed. The constant-check now only
rejects a column of the aggregate's own query level. A reference to an
outer query's column is fine now, since it can't vary across the rows
being aggregated, same as a Param. Your example now works.
2. MIN/MAX index-scan plan: fixed. That rewrite bypasses the normal
aggregate finalization, so I now exclude any aggregate with an ON
EMPTY default from it. It falls back to plain aggregation, where ON
EMPTY is applied correctly, whether or not an index exists.
3. ALL/DISTINCT syntax: fixed. Both now parse. And thinking about it more,
I could not find a real reason to disallow DISTINCT with ON EMPTY, so
it is now fully supported too, not just accepted and rejected. Dedup
Thanks for testing this so carefully. All three are fixed in the updated
patch:
1. Correlated outer column as default: fixed. The constant-check now only
rejects a column of the aggregate's own query level. A reference to an
outer query's column is fine now, since it can't vary across the rows
being aggregated, same as a Param. Your example now works.
2. MIN/MAX index-scan plan: fixed. That rewrite bypasses the normal
aggregate finalization, so I now exclude any aggregate with an ON
EMPTY default from it. It falls back to plain aggregation, where ON
EMPTY is applied correctly, whether or not an index exists.
3. ALL/DISTINCT syntax: fixed. Both now parse. And thinking about it more,
I could not find a real reason to disallow DISTINCT with ON EMPTY, so
it is now fully supported too, not just accepted and rejected. Dedup
happens before ON EMPTY is even considered, so they do not interfere.
Updated patch attached. Full regression suite passes, and I re-ran all
three of your examples directly against the fix.
Thanks again,
Updated patch attached. Full regression suite passes, and I re-ran all
three of your examples directly against the fix.
Thanks again,
On Thu, Sep 17, 2026 at 5:34 PM Vik Fearing <vik@postgresfriends.org> wrote:
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
Attachment
pgsql-hackers by date: