On Mon Jul 21, 2025 at 7:11 PM -03, Vik Fearing wrote:
> That is my preferred grammar, thank you.
>
Thanks for confirming!
> I have not looked at the C code by this can be obtained with a syntax
> transformation. To wit:
> SELECT a, b, c
> FROM tab
> QUALIFY wf() OVER () = ?
>
>
> can be rewritten as:
>
>
> SELECT a, b, c
> FROM (
> SELECT a, b, c, wf() OVER () = ? AS qc
> FROM tab
> ) AS q
> WHERE qc
>
>
> and then let the optimizer take over. The standard does this kind of
> thing all over the place; I don't know what the postgres project's
> position on doing things like this are.
>
The current patch supports the following syntaxes:
SELECT a, b, c
wf() OVER () as d
FROM tab
QUALIFY d = 1
and
SELECT a, b, c
wf() OVER ()
FROM tab
QUALIFY wf() OVER () = 1
When using the "QUALIFY d = 1" form, I currently rewrite the expression
as "wf() OVER () = 1" by searching the targetlist for the matching
alias, replacing the Var with the corresponding WindowFunc. Then I
append this clause to the topqual, which is later assigned to
WindowAggPath.plan->qual in create_one_window_path().
Besides this approach works I'm almost sure that this is not correct
because searching the window function on targetlist doesen't seems
correct to me. Tom also pointed out that this design could be confusing,
which reinforces the need to rethink it.
This transformation that you've suggested seems a better approach to
handle the QUALIFY clause to me as well. Unless anyone objects, I'll
prepare the next patch version based on that strategy.
Thanks very much for the comments!
--
Matheus Alcantara
