Re: Proposal: QUALIFY clause

Hi all,

It's been a while, but I'd like to revisit this thread now that the
QUALIFY proposal has been accepted by the SQL standard, so I'd like to
pick the work back up.

TLDR: QUALIFY clause is a general post select filter and it can be used
to filters rows on the result of a window function without wrapping the
query in a subquery or CTE. It is to window functions what HAVING is to
aggregates — a post-SELECT filter, evaluated after the window functions
are computed but before DISTINCT/ORDER BY/LIMIT. Note that QUALIFY
clause does not require a window function to be defined, it is a general
post select filter so it also allow to filter rows based on computed
column alias.

The attached patch include documentation and regression tests that
implement the expected behavior as defined by the accepted standard
proposal.

Implementation:

The standard defines QUALIFY by a syntactic transformation into a
subquery, and the attached patch implements exactly that, as a query
rewrite performed after parse analysis (in analyze.c): the table
expression and select list become an inner subquery, and the QUALIFY
condition becomes the WHERE clause of an outer query, with ORDER
BY/DISTINCT/LIMIT lifted to that outer query.

I first tried doing this rewrite before parse analysis, by building the
nested SelectStmt and letting normal analysis take over. That doesn't
work, for two reasons:

- The QUALIFY condition has to be resolved in the scope of the table
expression. With a pre-analysis rewrite it becomes the outer query's
WHERE, where only the subquery's output columns are visible. So a
QUALIFY reference to a non-selected table column can't be resolved at
all, and the alias-vs-column precedence inverts (the alias would win,
contrary to the standard).

- A window function in QUALIFY would land in the outer WHERE, which is
illegal — window functions aren't allowed there. To be legal it must
be computed in the subquery and referenced from outside, but
identifying the window functions (and aggregates, and which columns to
project) requires the analyzed tree. So the rewrite has to happen
after analysis.

The big win of the rewrite approach is that the planner already
optimizes window functions sitting inside a subquery, so QUALIFY gets
those optimizations (window run conditions and pushing quals down below
the WindowAgg) for free, with no new planner code. The plans come out
identical to the equivalent hand-written subquery.

The main downside is view deparse: because the stored query is the
rewritten subquery, pg_get_viewdef() of a view defined with QUALIFY
shows the subquery form rather than the QUALIFY keyword.

An alternative:

Another approach is to teach the planner about QUALIFY directly and
apply the same window optimizations to its quals. I'm attaching a second
patch (v2-0001-Add-QUALIFY-clause.patch.nocfbot) that does this, but
only for the window-function case. Since QUALIFY is a general
post-SELECT filter, a condition that doesn't reference a window function
would, in this approach, need a gating Result node carrying the QUALIFY
filter on top of the plan tree; that part is not implemented in this
patch. This approach keeps QUALIFY first-class (clean view deparse, no
synthetic subquery), at the cost of duplicating the run-condition and
qual-pushdown logic that today lives at the subquery boundary.

So there are two patches attached:

- v2-0001-Add-QUALIFY-clause.patch: the query-rewrite approach, complete (window and
non-window QUALIFY), with docs and tests.

- v2-0001-Add-QUALIFY-clause.patch.nocfbot: the planner-based approach,
window-functions only. Note that there is some test cases failing due
to WIP state.

Open question:

I'm not sure which architecture is the right long-term direction, and
I'd like feedback before investing further. I lean slightly toward the
planner-infrastructure approach for the cleaner representation (no
subquery wrapping, QUALIFY preserved in views), even though it currently
duplicates the run-condition and qual-pushdown code. We could refactor
find_window_run_conditions()/check_and_push_window_quals() and the
pushdown helpers to be reusable outside set_subquery_pathlist() so
QUALIFY can share them — but I'd rather hear opinions on the overall
direction first.

Thoughts on which approach to pursue would be very welcome.

Thanks

--
Matheus Alcantara
EDB: https://www.enterprisedb.com