Re: Proposal: QUALIFY clause

On Mon, Jul 21, 2025 at 11:02:36PM -0600, Merlin Moncure wrote:
> On Mon, Jul 21, 2025 at 10:08 PM Nico Williams <nico@cryptonector.com>
> wrote:
> > I would have a HAVING clause that comes _before_ GROUP BY apply to
> > window functions and a second one that comes _after_ GROUP BY apply to
> > the grouping.
>
> I don't know...consider:
> #1 SELECT lag(1) OVER(), max(v) FROM (SELECT 1 AS v) q HAVING true ;
> ...
> #2 SELECT lag(1) OVER(), max(v) FROM (SELECT 1 AS v) q HAVING true HAVING
> lag(1) OVER() IS NULL;
>
> What does the HAVING clause apply to in #1?  I think you might be in
> trouble with the standard here.  2nd clause doesn't feel right in #2.  The
> basic problem is that HAVING does more than just 'syntax sugar subquery /
> WHERE' and it just can't be hijacked to do something else IMO.

#2 would be a syntax error because the second HAVING did not come after
a GROUP BY.  #1 would not be a syntax error only because of the use of
window functions before the HAVING.

> Syntax simplifying
> SELECT * FROM (<window function query>) WHERE col = x

Yes.  I'd rather that than QUALIFY.  QUALIFY only makes sense because so
many other RDBMSes have it and it's likely to get standardized.

Nico
--