Seen in the Postgres test corpus:

select ten, sum(distinct four) filter (where four > 10) from onek a
group by ten
having exists (select 1 from onek b where sum(distinct a.four) = b.four);

The table here is onek, (read: "one K," because it has 1,000 rows,) and the plain text of the query says:

• scan onek as a

• aggregate it on column four with a filter, grouped by column ten

• with a having exists (semi-join) against onek as b, with an outer reference to an aggregation on a

• however, this aggregation is not the same one as the one originally declared on a, because it's unfiltered

The EXPLAIN query plan for this is very strange; it looks like it's using two subqueries even though the query only specifies one. Postgres query plans tend to be of mediocre quality, so I recreated this on SQL Server (it doesn't support filter syntax for aggregates but you can fake it easily enough with a case expression) and got this very clear query plan:

There are indeed two subqueries. It's processing a twice with the two different aggregate calculations, joining them together, and then computing the semi-join of that a-a joined set against b.

I'm trying to understand what's going on here. It's scanning a twice, applying group by ten twice, computing two different aggregates, and then joining the resulting sets. I haven't seen this duplication behavior in any other queries, and I'm struggling to understand the underlying rule.

What's causing the duplication? Is there any reason why it doesn't simply hoist the second agg onto the original a, given that it's using the same source and the same grouping key? Does anyone understand the rationale for this behavior in the database engine?

Background: I'm building a SQL database engine and using the Postgres test suite as one of my correctness tests. The problem I'm trying to solve is understanding the rule that causes two different database engines to do something that looks excessively complicated.