Re: Proposal: QUALIFY clause

On Tue, Jul 22, 2025 at 01:14:20AM -0400, Tom Lane wrote:
> Nico Williams <nico@cryptonector.com> writes:
> > On Mon, Jul 21, 2025 at 09:43:15PM -0600, Merlin Moncure wrote:
> >> Hm, HAVING requires to apply 'group by' which windows functions do not
> >> require (unlike aggregates).
> 
> > Pavel's point is precisely to allow HAVING w/o a GROUP BY when there are
> > window functions since window functions are "+/-" ("more or less")
> > aggregate functions.  That makes sense to me.
> 
> No, it's really quite wrong.  Aggregate functions are not equivalent
> to window functions: if you have both in a query, they execute in
> separate passes, with the window functions operating on the grouped
> rows output by the aggregation step (and then filtered by HAVING,
> if any).

Pavel doesn't say that window functions are aggregate functions.  Pavel
said they are +/- (more or less, really, just similar to) aggregate
functions.  There is a similarity.  But I appreciate the point about
which passes get which, and that definitely makes the two-HAVING-
clauses concept much more unwieldy.

> If we're going to support this, it does need to be its own clause.

I agree that its own clause is best; I just greatly dislike QUALIFY.