Hi Tom, Thanks for the feedback.
If the syntax is like WHERE, there will be no way to do it without making QUALIFY a fully-reserved word. That will inevitably break more than zero applications. You are right that QUALIFY functioning as a clause at the same level as WHERE / HAVING creates grammar conflicts if not reserved, particularly with column aliases. However, I'd like to argue that the utility of QUALIFY justifies this cost, and its status as a de-facto industry standard mitigates the lack of formal standardization. 1. De-facto Standardization & Ergonomics While QUALIFY is not in the ISO SQL standard, it has been adopted by major analytical databases including Snowflake, BigQuery, Databricks, and Teradata. Users migrating from or working across these systems expect this functionality. The ergonomic benefit is significant: it removes the need for a full subquery wrapper just to filter on window functions, which is a very common pattern in analytical queries. This improves query readability and writability substantially. 2. Keyword Reservation Consistency Similar clauses like WINDOW and HAVING are also fully reserved keywords in PostgreSQL to avoid similar ambiguity. Treating QUALIFY consistently with these clauses seems logical from a language design perspective, even if it adds one more reserved word. 3. Implementation & Testing details I have implemented QUALIFY as a new clause processed after WINDOW but before DISTINCT and ORDER BY, effectively acting as a filter on the result of window functions. Key implementation details: * Execution Order: The clause is evaluated after window functions are computed, allowing predicates on window function results without subqueries. * Alias Support: Access to output column aliases (e.g. SELECT ... AS alias ... QUALIFY alias > 10) is supported, resolving a major pain point. Testing coverage (src/test/regress/sql/qualify.sql): * Basic filtering on window functions. * Integration with WHERE, GROUP BY, and HAVING clauses. * Support for window aliases (WINDOW w AS ...). * Verify interactions with aggregates (allowed if logically consistent with grouping). * Resolution of ambiguous aliases. 1. Path Forward If the community feels strongly that a full reservation is a blocker, I am open to exploring if we can make it unreserved or col_name_keyword with some grammar adjustments (e.g. relying on position after WINDOW/HAVING), though as you noted, this risks edge-case ambiguities. Given its widespread adoption elsewhere, I believe QUALIFY is the "correct" keyword for this feature rather than a new term like REFINE. Thoughts? Please find attached a functional patch to implement the qualify clause. Best regards, Yannick -- Yannick Goetschel Head of Data Engineering Compagnie Financière Tradition D: +41 (0)21 343 52 52 E: [email protected]<mailto:[email protected]> ------------------------------------------------------------------------------ This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. [email protected]
qualify_v1.patch
Description: qualify_v1.patch
