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.
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 |