Re: Proposal: QUALIFY clause

On Tue, 29 Jul 2025 at 12:11, Matheus Alcantara
<matheusssilv97(at)gmail(dot)com> wrote:
> By "pushdowns" you mean missing the Run Conditions on the QUALIFY
> example? IIUC the Run Condition is only created if it's a subquery. I've
> checked this on set_rel_size() -> set_subquery_pathlist() ->
> check_and_push_window_quals().

Yes, but not only Run Conditions, it's subquery pushdown quals in
general. There are various rules to what is allowed and what must be
disallowed. See check_output_expressions().

You should be pushing the qual to the lowest level that it's valid to
evaluate it at. We do this already for HAVING quals where those will
effectively be "transferred" to the WHERE clause when it's valid to do
so. I'd expect the same for QUALIFY. I'm unsure which parts of
subquery pushdown could be made more reusable to help you with this.
Ideally we'd not have to duplicate lots of logic in various places. If
you do manage to get around the whole SQL standard issue around
QUALIFY, then a large portion of a patch like this being acceptable
will largely depend on how much code gets reused vs how much you have
to rewrite from scratch. It's not that fun to have to duplicate logic
in multiple places when new optimisations are added. See d222585a9 for
an example of an optimisation that would likely have to be duplicated
if QUALIFY existed.

David