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
