v0-0001-QUALIFY-clause.patch

From cc323df48e7ccd50dae97419430741acd6eba24d Mon Sep 17 00:00:00 2001
From: Matheus Alcantara <mths.dev@pm.me>
Date: Wed, 4 Jun 2025 15:56:59 -0300
Subject: [PATCH v0] QUALIFY clause

---
 src/backend/optimizer/plan/planner.c |   5 ++
 src/backend/parser/analyze.c         |   8 +-
 src/backend/parser/gram.y            |  21 +++--
 src/backend/parser/parse_agg.c       |   7 +-
 src/backend/parser/parse_clause.c    |  57 +++++++++++-
 src/backend/parser/parse_expr.c      |   4 +
 src/backend/parser/parse_func.c      |   3 +
 src/backend/parser/parse_relation.c  |  18 ++++
 src/include/nodes/parsenodes.h       |   5 ++
 src/include/parser/kwlist.h          |   1 +
 src/include/parser/parse_clause.h    |   5 +-
 src/include/parser/parse_node.h      |   3 +
 src/test/regress/expected/window.out | 125 +++++++++++++++++++++++++++
 src/test/regress/sql/window.sql      |  51 +++++++++++
 14 files changed, 300 insertions(+), 13 deletions(-)

diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index 549aedcfa99..1a8fb387e47 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -4710,6 +4710,11 @@ create_one_window_path(PlannerInfo *root,
 				if (!topwindow)
 					topqual = lappend(topqual, opexpr);
 			}
+
+			/*  Add QUALIFY qual */
+			if (wc->qualifyQual != NULL)
+				topqual = lappend(topqual, (Expr *) wc->qualifyQual);
+
 		}
 
 		path = (Path *)
diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
index 34f7c17f576..dd27fd3730b 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -1481,9 +1481,12 @@ transformSelectStmt(ParseState *pstate, SelectStmt *stmt)
 	qry->limitOption = stmt->limitOption;
 
 	/* transform window clauses after we have seen all window functions */
+	pstate->p_targetList = qry->targetList;
 	qry->windowClause = transformWindowDefinitions(pstate,
 												   pstate->p_windowdefs,
-												   &qry->targetList);
+												   &qry->targetList,
+												   stmt->qualifyClause);
+
 
 	/* resolve any still-unresolved output columns as being type text */
 	if (pstate->p_resolve_unknowns)
@@ -2975,7 +2978,8 @@ transformPLAssignStmt(ParseState *pstate, PLAssignStmt *stmt)
 	/* transform window clauses after we have seen all window functions */
 	qry->windowClause = transformWindowDefinitions(pstate,
 												   pstate->p_windowdefs,
-												   &qry->targetList);
+												   &qry->targetList,
+												   NULL);	/* FIXME(matheus) */
 
 	qry->rtable = pstate->p_rtable;
 	qry->rteperminfos = pstate->p_rteperminfos;
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 73345bb3c70..63c0da99256 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -520,7 +520,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 %type <node>	TableElement TypedTableElement ConstraintElem DomainConstraintElem TableFuncElement
 %type <node>	columnDef columnOptions optionalPeriodName
 %type <defelt>	def_elem reloption_elem old_aggr_elem operator_def_elem
-%type <node>	def_arg columnElem where_clause where_or_current_clause
+%type <node>	def_arg columnElem where_clause qualify_clause where_or_current_clause
 				a_expr b_expr c_expr AexprConst indirection_el opt_slice_bound
 				columnref having_clause func_table xmltable array_expr
 				OptWhereClause operator_def_arg
@@ -760,7 +760,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 	POSITION PRECEDING PRECISION PRESERVE PREPARE PREPARED PRIMARY
 	PRIOR PRIVILEGES PROCEDURAL PROCEDURE PROCEDURES PROGRAM PUBLICATION
 
-	QUOTE QUOTES
+	QUALIFY QUOTE QUOTES
 
 	RANGE READ REAL REASSIGN RECURSIVE REF_P REFERENCES REFERENCING
 	REFRESH REINDEX RELATIVE_P RELEASE RENAME REPEATABLE REPLACE REPLICA
@@ -12993,7 +12993,7 @@ select_clause:
  */
 simple_select:
 			SELECT opt_all_clause opt_target_list
-			into_clause from_clause where_clause
+			into_clause from_clause where_clause qualify_clause
 			group_clause having_clause window_clause
 				{
 					SelectStmt *n = makeNode(SelectStmt);
@@ -13002,10 +13002,11 @@ simple_select:
 					n->intoClause = $4;
 					n->fromClause = $5;
 					n->whereClause = $6;
-					n->groupClause = ($7)->list;
-					n->groupDistinct = ($7)->distinct;
-					n->havingClause = $8;
-					n->windowClause = $9;
+					n->qualifyClause = $7;
+					n->groupClause = ($8)->list;
+					n->groupDistinct = ($8)->distinct;
+					n->havingClause = $9;
+					n->windowClause = $10;
 					$$ = (Node *) n;
 				}
 			| SELECT distinct_clause target_list
@@ -14135,6 +14136,11 @@ where_clause:
 			| /*EMPTY*/								{ $$ = NULL; }
 		;
 
+qualify_clause:
+			QUALIFY a_expr							{ $$ = $2; }
+			| /*EMPTY*/								{ $$ = NULL; }
+		;
+
 /* variant for UPDATE and DELETE */
 where_or_current_clause:
 			WHERE a_expr							{ $$ = $2; }
@@ -18250,6 +18256,7 @@ reserved_keyword:
 			| ORDER
 			| PLACING
 			| PRIMARY
+			| QUALIFY
 			| REFERENCES
 			| RETURNING
 			| SELECT
diff --git a/src/backend/parser/parse_agg.c b/src/backend/parser/parse_agg.c
index 0ac8966e30f..deeb3584f97 100644
--- a/src/backend/parser/parse_agg.c
+++ b/src/backend/parser/parse_agg.c
@@ -402,6 +402,9 @@ check_agglevels_and_constraints(ParseState *pstate, Node *expr)
 		case EXPR_KIND_WHERE:
 			errkind = true;
 			break;
+		case EXPR_KIND_QUALIFY:
+			errkind = true;
+			break;
 		case EXPR_KIND_POLICY:
 			if (isAgg)
 				err = _("aggregate functions are not allowed in policy expressions");
@@ -878,8 +881,6 @@ transformWindowFuncCall(ParseState *pstate, WindowFunc *wfunc,
 		case EXPR_KIND_WHERE:
 			errkind = true;
 			break;
-		case EXPR_KIND_POLICY:
-			err = _("window functions are not allowed in policy expressions");
 			break;
 		case EXPR_KIND_HAVING:
 			errkind = true;
@@ -895,6 +896,8 @@ transformWindowFuncCall(ParseState *pstate, WindowFunc *wfunc,
 			err = _("window functions are not allowed in window definitions");
 			break;
 		case EXPR_KIND_SELECT_TARGET:
+		case EXPR_KIND_POLICY:
+		case EXPR_KIND_QUALIFY:
 			/* okay */
 			break;
 		case EXPR_KIND_INSERT_TARGET:
diff --git a/src/backend/parser/parse_clause.c b/src/backend/parser/parse_clause.c
index 9f20a70ce13..75f5ab727f9 100644
--- a/src/backend/parser/parse_clause.c
+++ b/src/backend/parser/parse_clause.c
@@ -1842,6 +1842,51 @@ setNamespaceLateralState(List *namespace, bool lateral_only, bool lateral_ok)
 	}
 }
 
+Node *
+transformQualifyClause(ParseState *pstate, List *targetlist, Node *qualify)
+{
+	Node	   *where;
+
+	if (qualify == NULL)
+		return NULL;
+
+	where = transformWhereClause(pstate,
+								 qualify,
+								 EXPR_KIND_QUALIFY,
+								 "QUALIFY");
+
+	/*
+	 *  Transform any Var referencing a WindowFunc into a real WindowFunc de
+	 * fact.
+	 */
+	if (IsA(where, OpExpr))
+	{
+		ListCell   *lc;
+		OpExpr	   *op = (OpExpr *) where;
+		List	   *newArgs = NIL;
+
+		foreach(lc, op->args)
+		{
+			Node	   *node = lfirst(lc);
+
+			if (IsA(node, Var))
+			{
+				Var		   *var = (Var *) node;
+				TargetEntry *tle = (TargetEntry *) lfirst(&targetlist->elements[var->varattno - 1]);
+
+
+				Assert(IsA(tle->expr, WindowFunc));
+				newArgs = lappend(newArgs, (Node *) tle->expr);
+			}
+			else
+				newArgs = lappend(newArgs, node);
+		}
+		op->args = newArgs;
+	}
+
+	return where;
+}
+
 
 /*
  * transformWhereClause -
@@ -2764,7 +2809,8 @@ transformSortClause(ParseState *pstate,
 List *
 transformWindowDefinitions(ParseState *pstate,
 						   List *windowdefs,
-						   List **targetlist)
+						   List **targetlist,
+						   Node *qualify)
 {
 	List	   *result = NIL;
 	Index		winref = 0;
@@ -2776,6 +2822,7 @@ transformWindowDefinitions(ParseState *pstate,
 		WindowClause *refwc = NULL;
 		List	   *partitionClause;
 		List	   *orderClause;
+		Node	   *qualifyClause;
 		Oid			rangeopfamily = InvalidOid;
 		Oid			rangeopcintype = InvalidOid;
 		WindowClause *wc;
@@ -2824,12 +2871,20 @@ transformWindowDefinitions(ParseState *pstate,
 											   EXPR_KIND_WINDOW_PARTITION,
 											   true /* force SQL99 rules */ );
 
+		/*
+		 * transform QUALIFY. targetlist is used find the window function
+		 * reference.
+		 *
+		 */
+		qualifyClause = transformQualifyClause(pstate, *targetlist, qualify);
+
 		/*
 		 * And prepare the new WindowClause.
 		 */
 		wc = makeNode(WindowClause);
 		wc->name = windef->name;
 		wc->refname = windef->refname;
+		wc->qualifyQual = qualifyClause;
 
 		/*
 		 * Per spec, a windowdef that references a previous one copies the
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index d66276801c6..d30661e8da5 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -575,6 +575,7 @@ transformColumnRef(ParseState *pstate, ColumnRef *cref)
 		case EXPR_KIND_COPY_WHERE:
 		case EXPR_KIND_GENERATED_COLUMN:
 		case EXPR_KIND_CYCLE_MARK:
+		case EXPR_KIND_QUALIFY:
 			/* okay */
 			break;
 
@@ -1794,6 +1795,7 @@ transformSubLink(ParseState *pstate, SubLink *sublink)
 		case EXPR_KIND_FROM_SUBSELECT:
 		case EXPR_KIND_FROM_FUNCTION:
 		case EXPR_KIND_WHERE:
+		case EXPR_KIND_QUALIFY:
 		case EXPR_KIND_POLICY:
 		case EXPR_KIND_HAVING:
 		case EXPR_KIND_FILTER:
@@ -3219,6 +3221,8 @@ ParseExprKindName(ParseExprKind exprKind)
 			return "GENERATED AS";
 		case EXPR_KIND_CYCLE_MARK:
 			return "CYCLE";
+		case EXPR_KIND_QUALIFY:
+			return "QUALIFY";
 
 			/*
 			 * There is intentionally no default: case here, so that the
diff --git a/src/backend/parser/parse_func.c b/src/backend/parser/parse_func.c
index 583bbbf232f..4e202949bfd 100644
--- a/src/backend/parser/parse_func.c
+++ b/src/backend/parser/parse_func.c
@@ -2557,6 +2557,9 @@ check_srf_call_placement(ParseState *pstate, Node *last_srf, int location)
 		case EXPR_KIND_WHERE:
 			errkind = true;
 			break;
+		case EXPR_KIND_QUALIFY:
+			errkind = true;
+			break;
 		case EXPR_KIND_POLICY:
 			err = _("set-returning functions are not allowed in policy expressions");
 			break;
diff --git a/src/backend/parser/parse_relation.c b/src/backend/parser/parse_relation.c
index 04ecf64b1fc..d3bb9346dec 100644
--- a/src/backend/parser/parse_relation.c
+++ b/src/backend/parser/parse_relation.c
@@ -922,6 +922,24 @@ colNameToVar(ParseState *pstate, const char *colname, bool localonly,
 			newresult = scanNSItemForColumn(orig_pstate, nsitem, sublevels_up,
 											colname, location);
 
+			/*
+			 * If we are parsing a QUALIFY expression try to search the window
+			 * function reference on target list
+			 */
+			if (newresult == NULL && orig_pstate->p_hasWindowFuncs
+				&& pstate->p_expr_kind == EXPR_KIND_QUALIFY)
+			{
+				ListCell   *lc;
+
+				foreach(lc, pstate->p_targetList)
+				{
+					TargetEntry *tle = (TargetEntry *) lfirst(lc);
+
+					if (strcmp(tle->resname, colname) == 0)
+						newresult = (Node *) makeVarFromTargetEntry(OUTER_VAR, tle);
+				}
+			}
+
 			if (newresult)
 			{
 				if (result)
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 86a236bd58b..824b7238ca9 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -220,6 +220,8 @@ typedef struct Query
 
 	Node	   *havingQual;		/* qualifications applied to groups */
 
+	Node 		*qualifyQual;	/* qualifications applied to window functions */
+
 	List	   *windowClause;	/* a list of WindowClause's */
 
 	List	   *distinctClause; /* a list of SortGroupClause's */
@@ -1575,6 +1577,8 @@ typedef struct WindowClause
 	int			frameOptions;	/* frame_clause options, see WindowDef */
 	Node	   *startOffset;	/* expression for starting bound, if any */
 	Node	   *endOffset;		/* expression for ending bound, if any */
+	/* QUALIFY clause */
+	Node 	   *qualifyQual;
 	/* in_range function for startOffset */
 	Oid			startInRangeFunc pg_node_attr(query_jumble_ignore);
 	/* in_range function for endOffset */
@@ -2190,6 +2194,7 @@ typedef struct SelectStmt
 	List	   *targetList;		/* the target list (of ResTarget) */
 	List	   *fromClause;		/* the FROM clause */
 	Node	   *whereClause;	/* WHERE qualification */
+	Node       *qualifyClause;	/* QUALIFY qualification */
 	List	   *groupClause;	/* GROUP BY clauses */
 	bool		groupDistinct;	/* Is this GROUP BY DISTINCT? */
 	Node	   *havingClause;	/* HAVING conditional-expression */
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index a4af3f717a1..f7e267dd241 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -359,6 +359,7 @@ PG_KEYWORD("procedure", PROCEDURE, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("procedures", PROCEDURES, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("program", PROGRAM, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("publication", PUBLICATION, UNRESERVED_KEYWORD, BARE_LABEL)
+PG_KEYWORD("qualify", QUALIFY, RESERVED_KEYWORD, AS_LABEL)
 PG_KEYWORD("quote", QUOTE, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("quotes", QUOTES, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("range", RANGE, UNRESERVED_KEYWORD, BARE_LABEL)
diff --git a/src/include/parser/parse_clause.h b/src/include/parser/parse_clause.h
index 3e9894926de..5580842ee33 100644
--- a/src/include/parser/parse_clause.h
+++ b/src/include/parser/parse_clause.h
@@ -20,6 +20,8 @@ extern void transformFromClause(ParseState *pstate, List *frmList);
 extern int	setTargetTable(ParseState *pstate, RangeVar *relation,
 						   bool inh, bool alsoSource, AclMode requiredPerms);
 
+
+extern Node * transformQualifyClause(ParseState *pstate, List *targetlist, Node *qualify);
 extern Node *transformWhereClause(ParseState *pstate, Node *clause,
 								  ParseExprKind exprKind, const char *constructName);
 extern Node *transformLimitClause(ParseState *pstate, Node *clause,
@@ -35,7 +37,8 @@ extern List *transformSortClause(ParseState *pstate, List *orderlist,
 
 extern List *transformWindowDefinitions(ParseState *pstate,
 										List *windowdefs,
-										List **targetlist);
+										List **targetlist,
+										Node *qualify);
 
 extern List *transformDistinctClause(ParseState *pstate,
 									 List **targetlist, List *sortClause, bool is_agg);
diff --git a/src/include/parser/parse_node.h b/src/include/parser/parse_node.h
index f7d07c84542..05f0f17ba3d 100644
--- a/src/include/parser/parse_node.h
+++ b/src/include/parser/parse_node.h
@@ -45,6 +45,7 @@ typedef enum ParseExprKind
 	EXPR_KIND_FROM_FUNCTION,	/* function in FROM clause */
 	EXPR_KIND_WHERE,			/* WHERE */
 	EXPR_KIND_HAVING,			/* HAVING */
+	EXPR_KIND_QUALIFY,			/* QUALIFY */
 	EXPR_KIND_FILTER,			/* FILTER */
 	EXPR_KIND_WINDOW_PARTITION, /* window definition PARTITION BY */
 	EXPR_KIND_WINDOW_ORDER,		/* window definition ORDER BY */
@@ -231,6 +232,8 @@ struct ParseState
 
 	Node	   *p_last_srf;		/* most recent set-returning func/op found */
 
+	List	   *p_targetList;		/* target list (of TargetEntry) */
+
 	/*
 	 * Optional hook functions for parser callbacks.  These are null unless
 	 * set up by the caller of make_parsestate.
diff --git a/src/test/regress/expected/window.out b/src/test/regress/expected/window.out
index b86b668f433..5c886c06141 100644
--- a/src/test/regress/expected/window.out
+++ b/src/test/regress/expected/window.out
@@ -4537,6 +4537,131 @@ WHERE first_emp = 1 OR last_emp = 1;
  sales     |     4 |   4800 | 08-08-2007  |         3 |        1
 (6 rows)
 
+-- Test QUALIFY clause
+SELECT *,
+       RANK() OVER (PARTITION BY depname ORDER BY salary DESC)
+FROM empsalary
+QUALIFY RANK() OVER (PARTITION BY depname ORDER BY salary DESC) <= 2;
+  depname  | empno | salary | enroll_date | rank 
+-----------+-------+--------+-------------+------
+ develop   |     8 |   6000 | 10-01-2006  |    1
+ develop   |    10 |   5200 | 08-01-2007  |    2
+ develop   |    11 |   5200 | 08-15-2007  |    2
+ personnel |     2 |   3900 | 12-23-2006  |    1
+ personnel |     5 |   3500 | 12-10-2007  |    2
+ sales     |     1 |   5000 | 10-01-2006  |    1
+ sales     |     4 |   4800 | 08-08-2007  |    2
+ sales     |     3 |   4800 | 08-01-2007  |    2
+(8 rows)
+
+SELECT *,
+       ROW_NUMBER() OVER (PARTITION BY depname ORDER BY enroll_date DESC)
+FROM empsalary
+QUALIFY ROW_NUMBER() OVER (PARTITION BY depname ORDER BY enroll_date DESC) = 1;
+  depname  | empno | salary | enroll_date | row_number 
+-----------+-------+--------+-------------+------------
+ develop   |     7 |   4200 | 01-01-2008  |          1
+ personnel |     5 |   3500 | 12-10-2007  |          1
+ sales     |     4 |   4800 | 08-08-2007  |          1
+(3 rows)
+
+SELECT *,
+       AVG(salary) OVER (PARTITION BY depname) AS avg_salary
+FROM empsalary
+QUALIFY salary > avg_salary;
+  depname  | empno | salary | enroll_date |      avg_salary       
+-----------+-------+--------+-------------+-----------------------
+ develop   |    11 |   5200 | 08-15-2007  | 5020.0000000000000000
+ develop   |     8 |   6000 | 10-01-2006  | 5020.0000000000000000
+ develop   |    10 |   5200 | 08-01-2007  | 5020.0000000000000000
+ personnel |     2 |   3900 | 12-23-2006  | 3700.0000000000000000
+ sales     |     1 |   5000 | 10-01-2006  | 4866.6666666666666667
+(5 rows)
+
+SELECT *,
+       COUNT(*) OVER (PARTITION BY depname, salary)
+FROM empsalary
+QUALIFY COUNT(*) OVER (PARTITION BY depname, salary) = 1;
+  depname  | empno | salary | enroll_date | count 
+-----------+-------+--------+-------------+-------
+ develop   |     7 |   4200 | 01-01-2008  |     1
+ develop   |     9 |   4500 | 01-01-2008  |     1
+ develop   |     8 |   6000 | 10-01-2006  |     1
+ personnel |     5 |   3500 | 12-10-2007  |     1
+ personnel |     2 |   3900 | 12-23-2006  |     1
+ sales     |     1 |   5000 | 10-01-2006  |     1
+(6 rows)
+
+SELECT *,
+       RANK() OVER (ORDER BY salary DESC) as rank
+FROM empsalary
+QUALIFY rank = 2;
+ depname | empno | salary | enroll_date | rank 
+---------+-------+--------+-------------+------
+ develop |    10 |   5200 | 08-01-2007  |    2
+ develop |    11 |   5200 | 08-15-2007  |    2
+(2 rows)
+
+SELECT *,
+       ROW_NUMBER() OVER (PARTITION BY depname ORDER BY enroll_date)
+FROM empsalary
+QUALIFY ROW_NUMBER() OVER (PARTITION BY depname ORDER BY enroll_date) <= 3;
+  depname  | empno | salary | enroll_date | row_number 
+-----------+-------+--------+-------------+------------
+ develop   |     8 |   6000 | 10-01-2006  |          1
+ develop   |    10 |   5200 | 08-01-2007  |          2
+ develop   |    11 |   5200 | 08-15-2007  |          3
+ personnel |     2 |   3900 | 12-23-2006  |          1
+ personnel |     5 |   3500 | 12-10-2007  |          2
+ sales     |     1 |   5000 | 10-01-2006  |          1
+ sales     |     3 |   4800 | 08-01-2007  |          2
+ sales     |     4 |   4800 | 08-08-2007  |          3
+(8 rows)
+
+SELECT *,
+       ROW_NUMBER() OVER (PARTITION BY depname ORDER BY salary DESC)
+FROM empsalary
+QUALIFY ROW_NUMBER() OVER (PARTITION BY depname ORDER BY salary DESC) <= 2
+ORDER BY depname, salary DESC;
+  depname  | empno | salary | enroll_date | row_number 
+-----------+-------+--------+-------------+------------
+ develop   |     8 |   6000 | 10-01-2006  |          1
+ develop   |    10 |   5200 | 08-01-2007  |          2
+ personnel |     2 |   3900 | 12-23-2006  |          1
+ personnel |     5 |   3500 | 12-10-2007  |          2
+ sales     |     1 |   5000 | 10-01-2006  |          1
+ sales     |     4 |   4800 | 08-08-2007  |          2
+(6 rows)
+
+SELECT *,
+       RANK() OVER (PARTITION BY depname ORDER BY salary DESC)
+FROM empsalary
+QUALIFY
+    RANK() OVER (PARTITION BY depname ORDER BY salary DESC) <= 2
+    AND enroll_date > DATE '2007-01-01';
+  depname  | empno | salary | enroll_date | rank 
+-----------+-------+--------+-------------+------
+ develop   |    10 |   5200 | 08-01-2007  |    2
+ develop   |    11 |   5200 | 08-15-2007  |    2
+ personnel |     5 |   3500 | 12-10-2007  |    2
+ sales     |     4 |   4800 | 08-08-2007  |    2
+ sales     |     3 |   4800 | 08-01-2007  |    2
+(5 rows)
+
+SELECT *,
+       RANK() OVER (PARTITION BY depname ORDER BY salary DESC)
+FROM empsalary
+QUALIFY
+    RANK() OVER (PARTITION BY depname ORDER BY salary DESC) = 1
+    OR salary < 4000;
+  depname  | empno | salary | enroll_date | rank 
+-----------+-------+--------+-------------+------
+ develop   |     8 |   6000 | 10-01-2006  |    1
+ personnel |     2 |   3900 | 12-23-2006  |    1
+ personnel |     5 |   3500 | 12-10-2007  |    2
+ sales     |     1 |   5000 | 10-01-2006  |    1
+(4 rows)
+
 -- cleanup
 DROP TABLE empsalary;
 -- test user-defined window function with named args and default args
diff --git a/src/test/regress/sql/window.sql b/src/test/regress/sql/window.sql
index 02f105f070e..4f376d1b459 100644
--- a/src/test/regress/sql/window.sql
+++ b/src/test/regress/sql/window.sql
@@ -1522,6 +1522,57 @@ SELECT * FROM
    FROM empsalary) emp
 WHERE first_emp = 1 OR last_emp = 1;
 
+-- Test QUALIFY clause
+SELECT *,
+       RANK() OVER (PARTITION BY depname ORDER BY salary DESC)
+FROM empsalary
+QUALIFY RANK() OVER (PARTITION BY depname ORDER BY salary DESC) <= 2;
+
+SELECT *,
+       ROW_NUMBER() OVER (PARTITION BY depname ORDER BY enroll_date DESC)
+FROM empsalary
+QUALIFY ROW_NUMBER() OVER (PARTITION BY depname ORDER BY enroll_date DESC) = 1;
+
+SELECT *,
+       AVG(salary) OVER (PARTITION BY depname) AS avg_salary
+FROM empsalary
+QUALIFY salary > avg_salary;
+
+SELECT *,
+       COUNT(*) OVER (PARTITION BY depname, salary)
+FROM empsalary
+QUALIFY COUNT(*) OVER (PARTITION BY depname, salary) = 1;
+
+SELECT *,
+       RANK() OVER (ORDER BY salary DESC) as rank
+FROM empsalary
+QUALIFY rank = 2;
+
+SELECT *,
+       ROW_NUMBER() OVER (PARTITION BY depname ORDER BY enroll_date)
+FROM empsalary
+QUALIFY ROW_NUMBER() OVER (PARTITION BY depname ORDER BY enroll_date) <= 3;
+
+SELECT *,
+       ROW_NUMBER() OVER (PARTITION BY depname ORDER BY salary DESC)
+FROM empsalary
+QUALIFY ROW_NUMBER() OVER (PARTITION BY depname ORDER BY salary DESC) <= 2
+ORDER BY depname, salary DESC;
+
+SELECT *,
+       RANK() OVER (PARTITION BY depname ORDER BY salary DESC)
+FROM empsalary
+QUALIFY
+    RANK() OVER (PARTITION BY depname ORDER BY salary DESC) <= 2
+    AND enroll_date > DATE '2007-01-01';
+
+SELECT *,
+       RANK() OVER (PARTITION BY depname ORDER BY salary DESC)
+FROM empsalary
+QUALIFY
+    RANK() OVER (PARTITION BY depname ORDER BY salary DESC) = 1
+    OR salary < 4000;
+
 -- cleanup
 DROP TABLE empsalary;
 
-- 
2.39.5 (Apple Git-154)