Re: Proposal: QUALIFY clause

From: Nico Williams <nico(at)cryptonector(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Merlin Moncure <mmoncure(at)gmail(dot)com>, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>, Vik Fearing <vik(at)postgresfriends(dot)org>, Matheus Alcantara <matheusssilv97(at)gmail(dot)com>, Pg Hackers <pgsql-hackers(at)postgresql(dot)org>, Peter Eisentraut <peter(at)eisentraut(dot)org>
Subject: Re: Proposal: QUALIFY clause
Date: 2025-07-22 15:07:47
Message-ID: aH+pQ5fvhvl8eLTP@ubby
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On Tue, Jul 22, 2025 at 01:14:20AM -0400, Tom Lane wrote:
> Nico Williams <nico(at)cryptonector(dot)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.

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Nico Williams 2025-07-22 15:14:04 Re: Proposal: QUALIFY clause
Previous Message Nico Williams 2025-07-22 15:04:01 Re: Proposal: QUALIFY clause