Search code examples
postgresqlaggregate-functions

Why does one aggregate query work and the other raise an error?

I just found something very strange in the Postgres test suite.

-- test for outer-level aggregates

-- this should work
select ten, sum(distinct four) from onek a
group by ten
having exists (select 1 from onek b where sum(distinct a.four) = b.four);

-- this should fail because subquery has an agg of its own in WHERE
select ten, sum(distinct four) from onek a
group by ten
having exists (select 1 from onek b where sum(distinct a.four + b.four) = b.four);

The second query errors out with "ERROR: aggregate functions are not allowed in WHERE". But as you can clearly see, the first query has an aggregate function in the WHERE clause as well. The two are identical, except for the value being passed into the sum call.

Why is the one query permissible and the other fails, with an error message suggesting that the first one should have failed too?

Solution

  • It is fine to reference aggregates on the table being aggregated in a HAVING clause.

    e.g.

    SELECT Grp, SUM(DISTINCT A.Num)
FROM (VALUES('G1',1),('G1',2)) a(Grp, Num)
GROUP BY Grp  
HAVING SUM(DISTINCT A.Num) = 3

    is allowed as SUM(DISTINCT A.Num) just returns a single value per group.

    It is also fine to reference that expression in a sub query.

    SELECT Grp, SUM(DISTINCT A.Num)
FROM (VALUES('G1',1),('G1',2)) a(Grp, Num)
GROUP BY Grp  
HAVING EXISTS (SELECT 1 FROM (VALUES(3)) B(Num) WHERE SUM(DISTINCT A.Num) = b.Num);

    Is just the same as

    SELECT Grp, SUM(DISTINCT A.Num)
FROM (VALUES('G1',1),('G1',2)) a(Grp, Num)
GROUP BY Grp  
HAVING EXISTS (SELECT 1 FROM (VALUES(3)) B(Num) WHERE expression_from_outer_aggregate = b.Num);

    When you use SUM(DISTINCT A.Num + b.Num) this no longer is just using that single aggregated value per group from the outer reference but looks like you are trying to compute a new aggregate referencing the inner table. Hence presumably the "aggregate functions are not allowed in WHERE" error kicks in.

    And whilst you are right that the initial example in the test suite does contradict that presumably this is a generic error message that doesn't bother listing the edge case " except in the case that the aggregate function is from an outer level aggregate"