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