From 7534d699ac246f36c04cf3bf9c3396807799f99a Mon Sep 17 00:00:00 2001 From: Matheus Alcantara Date: Tue, 24 Mar 2026 16:10:43 -0300 Subject: [PATCH] Add QUALIFY clause The QUALIFY clause filters rows after window function evaluation, analogous to how HAVING filters after GROUP BY aggregation. It is evaluated in the order: FROM -> WHERE -> GROUP BY -> HAVING -> WINDOW -> SELECT -> QUALIFY -> DISTINCT -> ORDER BY -> LIMIT. This allows filtering on window function results without requiring a wrapping subquery. QUALIFY supports referencing SELECT list aliases, matching ORDER BY behavior. A pre-columnref hook resolves bare identifiers against the targetlist before normal column resolution, allowing window functions defined in SELECT to be referenced by name in QUALIFY. Two optimizations are implemented for QUALIFY predicates. First, quals referencing only PARTITION BY columns with no window functions can be pushed down to WHERE, since such predicates have constant values within each partition and can filter rows before the sort required for window evaluation. Second, monotonic window functions support run conditions that allow early termination of partition processing. Both optimizations reuse the infrastructure developed for subquery window function pushdown in allpaths.c. Window functions appearing only in QUALIFY (not in the SELECT list) are added to the targetlist as resjunk entries during planning. This ensures they are present in the plan's targetlist so that setrefs.c can resolve references when fixing up run condition expressions. If an equivalent window function already exists in the targetlist, no duplicate is added, avoiding redundant computation. --- doc/src/sgml/ref/select.sgml | 99 +++ src/backend/nodes/nodeFuncs.c | 4 + src/backend/optimizer/path/allpaths.c | 3 +- src/backend/optimizer/plan/planner.c | 431 +++++++++- src/backend/parser/analyze.c | 102 +++ src/backend/parser/gram.y | 16 +- src/backend/parser/parse_agg.c | 4 + src/backend/parser/parse_expr.c | 4 + src/backend/parser/parse_func.c | 3 + src/backend/parser/parse_relation.c | 24 + src/include/nodes/parsenodes.h | 3 + src/include/optimizer/paths.h | 2 + src/include/parser/kwlist.h | 1 + src/include/parser/parse_node.h | 1 + src/include/parser/parsetree.h | 1 + src/test/regress/expected/qualify.out | 1107 +++++++++++++++++++++++++ src/test/regress/parallel_schedule | 2 +- src/test/regress/sql/qualify.sql | 563 +++++++++++++ 18 files changed, 2354 insertions(+), 16 deletions(-) create mode 100644 src/test/regress/expected/qualify.out create mode 100644 src/test/regress/sql/qualify.sql diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml index 09b6ce809bb..7387dba5549 100644 --- a/doc/src/sgml/ref/select.sgml +++ b/doc/src/sgml/ref/select.sgml @@ -40,6 +40,7 @@ SELECT [ ALL | DISTINCT [ ON ( expressiongrouping_element [, ...] } ] [ HAVING condition ] [ WINDOW window_name AS ( window_definition ) [, ...] ] + [ QUALIFY condition ] [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ] [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ] [ LIMIT { count | ALL } ] @@ -146,6 +147,16 @@ TABLE [ ONLY ] table_name [ * ] + + + If the QUALIFY clause is specified, all rows + that do not satisfy the condition are eliminated from the output. + Because QUALIFY is applied after the output rows + have been computed, its condition can reference window functions + and output column names. (See below.) + + + SELECT DISTINCT eliminates duplicate rows from the result. SELECT DISTINCT ON eliminates rows that @@ -1242,6 +1253,83 @@ EXCLUDE NO OTHERS + + <literal>QUALIFY</literal> Clause + + + The optional QUALIFY clause has the general form + +QUALIFY condition + + where condition is + any expression that evaluates to a result of type + boolean, the same as specified for the + WHERE clause. + + + + QUALIFY eliminates rows that do not satisfy the + condition. It is applied after window functions have been computed, + so its condition may refer to the results of window functions. In + this respect QUALIFY is to window functions what + HAVING is to + aggregate functions: each filters the result of its corresponding + computation stage. Without QUALIFY, filtering on + the result of a window function requires wrapping the query in a + sub-query (or common table expression) and applying a + WHERE clause there, because window functions are + computed after WHERE and HAVING. + A row is retained only if the condition evaluates to true; rows for + which it evaluates to false or null are eliminated. + + + + The condition may contain window functions, whether or not the same + window functions also appear in the SELECT list. + These window functions may use an inline OVER + clause or refer to a window defined in the + WINDOW clause; a + WINDOW clause is not required. Because + QUALIFY is evaluated after the + SELECT list, the condition may also reference + output column names (column aliases defined in the + SELECT list), unlike WHERE and + HAVING. In case of ambiguity, a name in + QUALIFY is interpreted as an input-column name + rather than an output column name. + + + + QUALIFY does not require window functions to be + present; it is a general filter applied to the computed output rows. + A condition that does not involve a window function and refers only to + input columns produces the same result as if it had been written in + WHERE, though writing such conditions in + WHERE is preferred. Aggregate functions are + permitted in the condition under the same rules that apply to the + SELECT list, since aggregation has already occurred + by the time QUALIFY is evaluated. + + + + The following query returns, for each department, only the + highest-paid employees, filtering on the result of a window function + without a sub-query: + +SELECT depname, empno, salary, + rank() OVER (PARTITION BY depname ORDER BY salary DESC) AS pos +FROM empsalary +QUALIFY pos = 1; + + Here the output column name pos defined in the + SELECT list is referenced in + QUALIFY. The window function could equally be + spelled out in the QUALIFY condition itself, in + which case it need not appear in the SELECT list at + all. + + + <literal>DISTINCT</literal> Clause @@ -2045,6 +2133,17 @@ FROM manufacturers m LEFT JOIN LATERAL get_product_names(m.id) pname ON true; missing features. + + The <literal>QUALIFY</literal> Clause + + + The QUALIFY clause conforms to the SQL standard + (feature O001, QUALIFY clause). Note that + QUALIFY is a reserved word; existing applications + that use it as an identifier must quote it or choose another name. + + + Omitted <literal>FROM</literal> Clauses diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c index 2a2e00b372e..368109af706 100644 --- a/src/backend/nodes/nodeFuncs.c +++ b/src/backend/nodes/nodeFuncs.c @@ -2793,6 +2793,8 @@ query_tree_walker_impl(Query *query, return true; if (WALK(query->limitCount)) return true; + if (WALK(query->qualifyQual)) + return true; /* * Most callers aren't interested in SortGroupClause nodes since those @@ -4488,6 +4490,8 @@ raw_expression_tree_walker_impl(Node *node, return true; if (WALK(stmt->rarg)) return true; + if (WALK(stmt->qualifyClause)) + return true; } break; case T_PLAssignStmt: diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c index c134594a21a..c3aafdf9e47 100644 --- a/src/backend/optimizer/path/allpaths.c +++ b/src/backend/optimizer/path/allpaths.c @@ -158,7 +158,6 @@ static void check_output_expressions(Query *subquery, pushdown_safety_info *safetyInfo); static void compare_tlist_datatypes(List *tlist, List *colTypes, pushdown_safety_info *safetyInfo); -static bool targetIsInAllPartitionLists(TargetEntry *tle, Query *query); static pushdown_safe_type qual_is_pushdown_safe(Query *subquery, Index rti, RestrictInfo *rinfo, pushdown_safety_info *safetyInfo); @@ -4398,7 +4397,7 @@ compare_tlist_datatypes(List *tlist, List *colTypes, * unlikely to be useful to spend any extra cycles getting it, since * unreferenced window definitions are probably infrequent in practice. */ -static bool +bool targetIsInAllPartitionLists(TargetEntry *tle, Query *query) { ListCell *lc; diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c index 846bd7c1fbe..0ad82797e08 100644 --- a/src/backend/optimizer/plan/planner.c +++ b/src/backend/optimizer/plan/planner.c @@ -297,6 +297,7 @@ static void create_final_unique_paths(PlannerInfo *root, RelOptInfo *input_rel, static void create_partial_unique_paths(PlannerInfo *root, RelOptInfo *input_rel, List *sortPathkeys, List *groupClause, SpecialJoinInfo *sjinfo, RelOptInfo *unique_rel); +static void add_qualify_windowfuncs_to_tlist(PlannerInfo *root); /***************************************************************************** @@ -347,6 +348,355 @@ planner(Query *parse, const char *query_string, int cursorOptions, return result; } +/* + * Helper to check if an expression contains a window function. + */ +static bool +contain_windowfunc_clause_walker(Node *node, void *context) +{ + if (node == NULL) + return false; + + if (IsA(node, WindowFunc)) + return true; + + return expression_tree_walker(node, contain_windowfunc_clause_walker, context); +} + +static bool +contain_windowfunc_clause(Node *node) +{ + return contain_windowfunc_clause_walker(node, NULL); +} + +/* + * Check if all Var references in the qual are in all PARTITION BY lists. + * This allows pushing the qual to WHERE since it doesn't depend on window ordering. + */ +static bool +qual_is_in_partition_list_walker(Node *node, void *context) +{ + if (node == NULL) + return false; + + if (IsA(node, Var)) + { + Query *parse = (Query *) context; + Var *var = (Var *) node; + TargetEntry *tle; + + tle = get_tle_by_var(parse->targetList, var); + if (tle != NULL) + return targetIsInAllPartitionLists(tle, parse); + } + + return expression_tree_walker(node, qual_is_in_partition_list_walker, context); +} + +static bool +qual_is_in_partition_list(Query *parse, Node *qual) +{ + return qual_is_in_partition_list_walker(qual, parse); +} + +/* + * check_qualify_run_condition + * Check if a QUALIFY qual can be used as a window function run condition. + * + * If the qual is an OpExpr comparing a monotonic window function to a constant, + * we can use it as a run condition to stop processing early. + * + * wflists contains the WindowFunc objects that will actually be used for + * path creation. We add runCondition to those objects, not to the (possibly + * copied) WindowFunc in the qual. + * + * Returns true if the qual should be kept as a filter (in addition to or instead + * of being used as a run condition). Returns false if the run condition fully + * handles the filtering and the qual can be removed. + * + * XXX: Refactor to combine this with find_window_run_conditions() + */ +static bool +check_qualify_run_condition(Query *parse, Node *qual, WindowFuncLists *wflists) +{ + OpExpr *opexpr; + Node *wfunc_node; + Node *other_node; + WindowFunc *wfunc; + WindowFunc *target_wfunc; + bool wfunc_left; + Oid prosupport; + WindowClause *wclause; + SupportRequestWFuncMonotonic req; + SupportRequestWFuncMonotonic *res; + List *opinfos; + ListCell *lc; + Oid runoperator; + bool keep_original = true; + + /* We can only work with OpExprs with 2 operands */ + if (!IsA(qual, OpExpr)) + return true; + + opexpr = (OpExpr *) qual; + if (list_length(opexpr->args) != 2) + return true; + + /* The operator must be strict */ + set_opfuncid(opexpr); + if (!func_strict(opexpr->opfuncid)) + return true; + + /* Check which side has the WindowFunc */ + wfunc_node = linitial(opexpr->args); + other_node = lsecond(opexpr->args); + wfunc_left = true; + + /* Skip RelabelType wrappers */ + while (IsA(wfunc_node, RelabelType)) + wfunc_node = (Node *) ((RelabelType *) wfunc_node)->arg; + + if (!IsA(wfunc_node, WindowFunc)) + { + /* Try the other side */ + wfunc_node = lsecond(opexpr->args); + other_node = linitial(opexpr->args); + wfunc_left = false; + + while (IsA(wfunc_node, RelabelType)) + wfunc_node = (Node *) ((RelabelType *) wfunc_node)->arg; + + if (!IsA(wfunc_node, WindowFunc)) + return true; /* No WindowFunc in this OpExpr */ + } + + wfunc = (WindowFunc *) wfunc_node; + + /* Can't use it if there are subplans in the WindowFunc */ + if (contain_subplans((Node *) wfunc)) + return true; + + /* The other side must be a pseudo-constant */ + if (!is_pseudo_constant_clause(other_node)) + return true; + + /* Check if the window function has a support function */ + prosupport = get_func_support(wfunc->winfnoid); + if (!OidIsValid(prosupport)) + return true; + + /* + * Find the matching WindowFunc in wflists. The wfunc in the qual might + * be a copy (due to alias resolution), so we need to find the actual + * WindowFunc object that will be used for path creation. + */ + target_wfunc = NULL; + if (wfunc->winref <= wflists->maxWinRef) + { + foreach(lc, wflists->windowFuncs[wfunc->winref]) + { + WindowFunc *wf = (WindowFunc *) lfirst(lc); + + if (equal(wf, wfunc)) + { + target_wfunc = wf; + break; + } + } + } + + if (target_wfunc == NULL) + return true; /* Couldn't find matching WindowFunc */ + + /* Find the window clause for this window function */ + wclause = (WindowClause *) list_nth(parse->windowClause, wfunc->winref - 1); + + /* Call the support function to check monotonicity */ + req.type = T_SupportRequestWFuncMonotonic; + req.window_func = target_wfunc; + req.window_clause = wclause; + + res = (SupportRequestWFuncMonotonic *) + DatumGetPointer(OidFunctionCall1(prosupport, PointerGetDatum(&req))); + + if (res == NULL || res->monotonic == MONOTONICFUNC_NONE) + return true; + + /* Check if the operator is compatible with the monotonicity */ + runoperator = InvalidOid; + opinfos = get_op_index_interpretation(opexpr->opno); + + foreach(lc, opinfos) + { + OpIndexInterpretation *opinfo = (OpIndexInterpretation *) lfirst(lc); + CompareType cmptype = opinfo->cmptype; + + if (cmptype == COMPARE_LT || cmptype == COMPARE_LE) + { + if ((wfunc_left && (res->monotonic & MONOTONICFUNC_INCREASING)) || + (!wfunc_left && (res->monotonic & MONOTONICFUNC_DECREASING))) + { + keep_original = false; + runoperator = opexpr->opno; + } + break; + } + else if (cmptype == COMPARE_GT || cmptype == COMPARE_GE) + { + if ((wfunc_left && (res->monotonic & MONOTONICFUNC_DECREASING)) || + (!wfunc_left && (res->monotonic & MONOTONICFUNC_INCREASING))) + { + keep_original = false; + runoperator = opexpr->opno; + } + break; + } + else if (cmptype == COMPARE_EQ) + { + if ((res->monotonic & MONOTONICFUNC_BOTH) == MONOTONICFUNC_BOTH) + { + keep_original = false; + runoperator = opexpr->opno; + } + else + { + CompareType newcmptype; + + if (res->monotonic & MONOTONICFUNC_INCREASING) + newcmptype = wfunc_left ? COMPARE_LE : COMPARE_GE; + else + newcmptype = wfunc_left ? COMPARE_GE : COMPARE_LE; + + keep_original = true; + runoperator = get_opfamily_member_for_cmptype(opinfo->opfamily_id, + opinfo->oplefttype, + opinfo->oprighttype, + newcmptype); + } + break; + } + } + + /* If we found a suitable operator, add the run condition to target_wfunc */ + if (OidIsValid(runoperator)) + { + WindowFuncRunCondition *wfuncrc; + + wfuncrc = makeNode(WindowFuncRunCondition); + wfuncrc->opno = runoperator; + wfuncrc->inputcollid = opexpr->inputcollid; + wfuncrc->wfunc_left = wfunc_left; + wfuncrc->arg = copyObject(other_node); + + target_wfunc->runCondition = lappend(target_wfunc->runCondition, wfuncrc); + } + + return keep_original; +} + +/* + * add_qualify_windowfuncs_to_tlist + * Ensure WindowFuncs in QUALIFY clause are present in the targetlist. + * + * If a WindowFunc appears only in the QUALIFY clause (not in SELECT), it won't + * be in the targetlist. This causes problems in setrefs.c when fixing up run + * condition references, as it expects to find the WindowFunc in the plan's + * targetlist. + * + * We add any missing WindowFuncs as resjunk entries. If an equivalent + * WindowFunc already exists in the targetlist, we don't add a duplicate + * (tlist_member uses equal() for comparison). + * + * This must be called after preprocess_targetlist, so we update both + * parse->targetList and root->processed_tlist. + */ +static void +add_qualify_windowfuncs_to_tlist(PlannerInfo *root) +{ + Query *parse = root->parse; + List *wfuncs; + ListCell *lc; + + if (parse->qualifyQual == NULL) + return; + + /* + * Extract all WindowFuncs from QUALIFY clause. We use + * PVC_INCLUDE_WINDOWFUNCS to get the WindowFunc nodes themselves (not + * recurse into their arguments). + */ + wfuncs = pull_var_clause(parse->qualifyQual, + PVC_INCLUDE_WINDOWFUNCS | + PVC_RECURSE_AGGREGATES | + PVC_RECURSE_PLACEHOLDERS); + + foreach(lc, wfuncs) + { + Node *node = (Node *) lfirst(lc); + TargetEntry *tle; + + if (!IsA(node, WindowFunc)) + continue; + + /* + * Check if an equivalent WindowFunc already exists in + * processed_tlist. If not, add it as a resjunk entry so it will be + * computed but not included in the final output. + */ + if (tlist_member((Expr *) node, root->processed_tlist) != NULL) + continue; + + tle = makeTargetEntry((Expr *) copyObject(node), + list_length(root->processed_tlist) + 1, + NULL, + true); + root->processed_tlist = lappend(root->processed_tlist, tle); + + /* + * Also add to parse->targetList so that the final tlist decoration + * (resnames etc) can be transferred properly. + */ + tle = makeTargetEntry((Expr *) copyObject(node), + list_length(parse->targetList) + 1, + NULL, + true); + parse->targetList = lappend(parse->targetList, tle); + } +} + +/* + * process_qualify_run_conditions + * Process QUALIFY clause quals to detect window function run conditions. + * + * This must be called after find_window_functions so that wflists contains + * the actual WindowFunc objects that will be used for path creation. + * + * Quals that become run conditions (and don't need to be kept as filters) + * are removed from parse->qualifyQual. + */ +static void +process_qualify_run_conditions(Query *parse, WindowFuncLists *wflists) +{ + List *newQualify = NIL; + ListCell *lc; + + if (parse->qualifyQual == NULL) + return; + + foreach(lc, (List *) parse->qualifyQual) + { + Node *qual = (Node *) lfirst(lc); + bool keep_qual; + + keep_qual = check_qualify_run_condition(parse, qual, wflists); + + if (keep_qual) + newQualify = lappend(newQualify, qual); + } + + parse->qualifyQual = (Node *) newQualify; +} + PlannedStmt * standard_planner(Query *parse, const char *query_string, int cursorOptions, ParamListInfo boundParams, ExplainState *es) @@ -1362,6 +1712,39 @@ subquery_planner(PlannerGlobal *glob, Query *parse, char *plan_name, } parse->havingQual = (Node *) newHaving; + /* + * Process QUALIFY clause for pushdown. Quals that reference only + * PARTITION BY columns and don't contain window functions can be pushed + * to WHERE since they don't depend on window ordering. + */ + if (parse->qualifyQual != NULL) + { + List *newQualify = NIL; + ListCell *lc; + + parse->qualifyQual = preprocess_expression(root, parse->qualifyQual, + EXPRKIND_QUAL); + + foreach(lc, (List *) parse->qualifyQual) + { + Node *qual = (Node *) lfirst(lc); + + if (qual_is_in_partition_list(parse, qual) && + !contain_windowfunc_clause(qual)) + { + /* Push to WHERE */ + parse->jointree->quals = (Node *) + lappend((List *) parse->jointree->quals, qual); + } + else + { + /* Keep in QUALIFY - run condition detection happens later */ + newQualify = lappend(newQualify, qual); + } + } + parse->qualifyQual = (Node *) newQualify; + } + /* * If we have any outer joins, try to reduce them to plain inner joins. * This step is most easily done after we've done expression @@ -1919,6 +2302,15 @@ grouping_planner(PlannerInfo *root, double tuple_fraction, */ preprocess_targetlist(root); + /* + * If QUALIFY clause contains WindowFuncs that aren't in the SELECT + * list, add them to the targetlist as resjunk entries. This ensures + * they'll be in the plan's targetlist so setrefs.c can find them when + * fixing up run condition references. + */ + if (parse->hasWindowFuncs && parse->qualifyQual != NULL) + add_qualify_windowfuncs_to_tlist(root); + /* * Mark all the aggregates with resolved aggtranstypes, and detect * aggregates that are duplicates or can share transition state. We @@ -1932,11 +2324,15 @@ grouping_planner(PlannerInfo *root, double tuple_fraction, preprocess_aggrefs(root, (Node *) parse->havingQual); } + /* - * Locate any window functions in the tlist. (We don't need to look - * anywhere else, since expressions used in ORDER BY will be in there - * too.) Note that they could all have been eliminated by constant - * folding, in which case we don't need to do any more work. + * Locate any window functions in the tlist. (Expressions used in + * ORDER BY will be in the tlist too.) WindowFuncs appearing only in + * QUALIFY were added to the targetlist above by + * add_qualify_windowfuncs_to_tlist, so searching processed_tlist is + * sufficient. Note that all window functions could have been + * eliminated by constant folding, in which case we don't need to do + * any more work. */ if (parse->hasWindowFuncs) { @@ -1944,6 +2340,14 @@ grouping_planner(PlannerInfo *root, double tuple_fraction, list_length(parse->windowClause)); if (wflists->numWindowFuncs > 0) { + /* + * Process QUALIFY clause for run conditions. This must + * happen after find_window_functions so we can add + * runCondition to the actual WindowFunc objects that will be + * used for path creation. + */ + process_qualify_run_conditions(parse, wflists); + /* * See if any modifications can be made to each WindowClause * to allow the executor to execute the WindowFuncs more @@ -4974,6 +5378,7 @@ create_one_window_path(PlannerInfo *root, PathTarget *window_target; ListCell *l; List *topqual = NIL; + WindowAggPath *winpath; /* * Since each window clause could require a different sort order, we stack @@ -5111,11 +5516,19 @@ create_one_window_path(PlannerInfo *root, } } - path = (Path *) - create_windowagg_path(root, window_rel, path, window_target, - wflists->windowFuncs[wc->winref], - runcondition, wc, - topwindow ? topqual : NIL, topwindow); + + winpath = create_windowagg_path(root, window_rel, path, window_target, + wflists->windowFuncs[wc->winref], + runcondition, wc, + topwindow ? topqual : NIL, topwindow); + + /* Add QUALIFY clause quals to the topmost WindowAgg */ + if (topwindow && root->parse->qualifyQual != NULL) + winpath->qual = (List *) + make_and_qual((Node *) winpath->qual, + root->parse->qualifyQual); + + path = (Path *) winpath; } add_path(window_rel, path); diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c index 2932d17a107..0deed4a99c8 100644 --- a/src/backend/parser/analyze.c +++ b/src/backend/parser/analyze.c @@ -70,6 +70,14 @@ typedef struct SelectStmtPassthrough List *indirection; /* indirection yet to be applied to target */ } SelectStmtPassthrough; +/* Passthrough data for QUALIFY alias resolution hook */ +typedef struct QualifyHookState +{ + List *targetList; /* the SELECT target list */ + PreParseColumnRefHook prev_hook; /* previous hook, if any */ + void *prev_hook_state; /* previous hook state */ +} QualifyHookState; + /* Hook for plugins to get control at end of parse analysis */ post_parse_analyze_hook_type post_parse_analyze_hook = NULL; @@ -108,6 +116,7 @@ static Query *transformCallStmt(ParseState *pstate, CallStmt *stmt); static void transformLockingClause(ParseState *pstate, Query *qry, LockingClause *lc, bool pushedDown); +static Node *qualifyColumnRefHook(ParseState *pstate, ColumnRef *cref); #ifdef DEBUG_NODE_TESTS_ENABLED static bool test_raw_expression_coverage(Node *node, void *context); #endif @@ -1725,6 +1734,71 @@ count_rowexpr_columns(ParseState *pstate, Node *expr) } +/* + * qualifyColumnRefHook - + * Pre-columnref hook for QUALIFY clause alias resolution. + * + * This hook allows QUALIFY to reference SELECT list aliases, similar to how + * ORDER BY can reference output column names. If the ColumnRef is a bare + * identifier matching a SELECT alias, we return a copy of that target + * expression otherwise we return NULL to let normal resolution proceed. + */ +static Node * +qualifyColumnRefHook(ParseState *pstate, ColumnRef *cref) +{ + QualifyHookState *qstate = (QualifyHookState *) pstate->p_ref_hook_state; + char *name; + TargetEntry *matched = NULL; + Node *result = NULL; + ListCell *lc; + + /* + * Only handle single identifier column names. Otherwise, we skip straight + * to the fallback. + */ + if (list_length(cref->fields) == 1 && IsA(linitial(cref->fields), String)) + { + name = strVal(linitial(cref->fields)); + + /* Search for matching alias in target list */ + foreach(lc, qstate->targetList) + { + TargetEntry *tle = (TargetEntry *) lfirst(lc); + + if (!tle->resjunk && tle->resname != NULL && strcmp(tle->resname, name) == 0) + { + if (matched != NULL) + { + /* Check for ambiguous reference */ + if (!equal(matched->expr, tle->expr)) + ereport(ERROR, + (errcode(ERRCODE_AMBIGUOUS_COLUMN), + errmsg("QUALIFY \"%s\" is ambiguous", name), + parser_errposition(pstate, cref->location))); + } + else + { + matched = tle; + } + } + } + + /* If we found a unique match, return it immediately */ + if (matched != NULL) + return (Node *) copyObject(matched->expr); + } + + /* Try the previous hook if any */ + if (qstate->prev_hook != NULL) + { + pstate->p_ref_hook_state = qstate->prev_hook_state; + result = qstate->prev_hook(pstate, cref); + pstate->p_ref_hook_state = qstate; + } + + return result; +} + /* * transformSelectStmt - * transforms a Select Statement @@ -1853,6 +1927,34 @@ transformSelectStmt(ParseState *pstate, SelectStmt *stmt, stmt->limitOption); qry->limitOption = stmt->limitOption; + /* + * Transform QUALIFY clause . This must happen before + * transformWindowDefinitions so that any window functions appearing only + * in QUALIFY get their window clauses created. QUALIFY can reference + * SELECT list aliases, so we set up a pre-columnref hook to resolve them + * before normal column resolution. + */ + if (stmt->qualifyClause != NULL) + { + QualifyHookState qstate; + + /* Save previous hook state and install QUALIFY hook */ + qstate.targetList = qry->targetList; + qstate.prev_hook = pstate->p_pre_columnref_hook; + qstate.prev_hook_state = pstate->p_ref_hook_state; + pstate->p_pre_columnref_hook = qualifyColumnRefHook; + pstate->p_ref_hook_state = &qstate; + + qry->qualifyQual = transformWhereClause(pstate, + stmt->qualifyClause, + EXPR_KIND_QUALIFY, + "QUALIFY"); + + /* Restore previous hook state */ + pstate->p_pre_columnref_hook = qstate.prev_hook; + pstate->p_ref_hook_state = qstate.prev_hook_state; + } + /* transform window clauses after we have seen all window functions */ qry->windowClause = transformWindowDefinitions(pstate, pstate->p_windowdefs, diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index ff4e1388c55..942beb1ecb6 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -532,7 +532,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); %type TableElement TypedTableElement ConstraintElem DomainConstraintElem TableFuncElement %type columnDef columnOptions optionalPeriodName %type def_elem reloption_elem old_aggr_elem operator_def_elem -%type def_arg columnElem where_clause where_or_current_clause +%type 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 @@ -806,7 +806,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); POSITION PRECEDING PRECISION PRESERVE PREPARE PREPARED PRIMARY PRIOR PRIVILEGES PROCEDURAL PROCEDURE PROCEDURES PROGRAM PROPERTIES PROPERTY PUBLICATION - QUOTE QUOTES + QUALIFY QUOTE QUOTES RANGE READ REAL REASSIGN RECURSIVE REF_P REFERENCES REFERENCING REFRESH REINDEX RELATIONSHIP RELATIVE_P RELEASE RENAME REPACK REPEATABLE REPLACE REPLICA @@ -13741,7 +13741,7 @@ select_clause: simple_select: SELECT opt_all_clause opt_target_list into_clause from_clause where_clause - group_clause having_clause window_clause + group_clause having_clause window_clause qualify_clause { SelectStmt *n = makeNode(SelectStmt); @@ -13754,11 +13754,12 @@ simple_select: n->groupByAll = ($7)->all; n->havingClause = $8; n->windowClause = $9; + n->qualifyClause = $10; $$ = (Node *) n; } | SELECT distinct_clause target_list into_clause from_clause where_clause - group_clause having_clause window_clause + group_clause having_clause window_clause qualify_clause { SelectStmt *n = makeNode(SelectStmt); @@ -13772,6 +13773,7 @@ simple_select: n->groupByAll = ($7)->all; n->havingClause = $8; n->windowClause = $9; + n->qualifyClause = $10; $$ = (Node *) n; } | values_clause { $$ = $1; } @@ -14959,6 +14961,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; } @@ -19346,6 +19353,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 acb933392de..8153b074e55 100644 --- a/src/backend/parser/parse_agg.c +++ b/src/backend/parser/parse_agg.c @@ -407,6 +407,9 @@ check_agglevels_and_constraints(ParseState *pstate, Node *expr) case EXPR_KIND_WHERE: errkind = true; break; + case EXPR_KIND_QUALIFY: + /* okay */ + break; case EXPR_KIND_POLICY: if (isAgg) err = _("aggregate functions are not allowed in policy expressions"); @@ -964,6 +967,7 @@ transformWindowFuncCall(ParseState *pstate, WindowFunc *wfunc, err = _("window functions are not allowed in window definitions"); break; case EXPR_KIND_SELECT_TARGET: + case EXPR_KIND_QUALIFY: /* okay */ break; case EXPR_KIND_INSERT_TARGET: diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c index 9adc9d4c0f6..7633e32f915 100644 --- a/src/backend/parser/parse_expr.c +++ b/src/backend/parser/parse_expr.c @@ -579,6 +579,7 @@ transformColumnRef(ParseState *pstate, ColumnRef *cref) case EXPR_KIND_GENERATED_COLUMN: case EXPR_KIND_CYCLE_MARK: case EXPR_KIND_PROPGRAPH_PROPERTY: + case EXPR_KIND_QUALIFY: /* okay */ break; @@ -1820,6 +1821,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: @@ -3180,6 +3182,8 @@ ParseExprKindName(ParseExprKind exprKind) return "function in FROM"; case EXPR_KIND_WHERE: return "WHERE"; + case EXPR_KIND_QUALIFY: + return "QUALIFY"; case EXPR_KIND_POLICY: return "POLICY"; case EXPR_KIND_HAVING: diff --git a/src/backend/parser/parse_func.c b/src/backend/parser/parse_func.c index fb306c05112..0bb8371cdd9 100644 --- a/src/backend/parser/parse_func.c +++ b/src/backend/parser/parse_func.c @@ -2690,6 +2690,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 ced210cd206..00ed2ce0bfa 100644 --- a/src/backend/parser/parse_relation.c +++ b/src/backend/parser/parse_relation.c @@ -3673,6 +3673,30 @@ get_tle_by_resno(List *tlist, AttrNumber resno) return NULL; } +/* + * Find the TargetEntry in the query's targetlist that matches the given Var. + * Returns NULL if no matching TLE is found. + */ +TargetEntry * +get_tle_by_var(List *tlist, Var *var) +{ + ListCell *lc; + + foreach(lc, tlist) + { + TargetEntry *tle = (TargetEntry *) lfirst(lc); + + if (IsA((Node *) tle->expr, Var)) + { + Var *tlistVar = (Var *) tle->expr; + + if (var->varattno == tlistVar->varattno && var->varno == tlistVar->varno) + return tle; + } + } + return NULL; +} + /* * Given a Query and rangetable index, return relation's RowMarkClause if any * diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index 4133c404a6b..15d0656c7a7 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -228,6 +228,8 @@ typedef struct Query List *windowClause; /* a list of WindowClause's */ + Node *qualifyQual; /* qualifications applied to window functions */ + List *distinctClause; /* a list of SortGroupClause's */ List *sortClause; /* a list of SortGroupClause's */ @@ -2300,6 +2302,7 @@ typedef struct SelectStmt bool groupByAll; /* Is this GROUP BY ALL? */ Node *havingClause; /* HAVING conditional-expression */ List *windowClause; /* WINDOW window_name AS (...), ... */ + Node *qualifyClause; /* QUALIFY conditional-expression */ /* * In a "leaf" node representing a VALUES list, the above fields are all diff --git a/src/include/optimizer/paths.h b/src/include/optimizer/paths.h index 17f2099ec3b..d64ded02931 100644 --- a/src/include/optimizer/paths.h +++ b/src/include/optimizer/paths.h @@ -286,4 +286,6 @@ extern PathKey *make_canonical_pathkey(PlannerInfo *root, extern void add_paths_to_append_rel(PlannerInfo *root, RelOptInfo *rel, List *live_childrels); +extern bool targetIsInAllPartitionLists(TargetEntry *tle, Query *query); + #endif /* PATHS_H */ diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h index 51ead54f015..a83545931c1 100644 --- a/src/include/parser/kwlist.h +++ b/src/include/parser/kwlist.h @@ -370,6 +370,7 @@ PG_KEYWORD("program", PROGRAM, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("properties", PROPERTIES, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("property", PROPERTY, 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_node.h b/src/include/parser/parse_node.h index f7f4ba6c2a8..2443a1192d4 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 */ diff --git a/src/include/parser/parsetree.h b/src/include/parser/parsetree.h index f6ce9746a21..4677a9dd70b 100644 --- a/src/include/parser/parsetree.h +++ b/src/include/parser/parsetree.h @@ -50,6 +50,7 @@ extern bool get_rte_attribute_is_dropped(RangeTblEntry *rte, */ extern TargetEntry *get_tle_by_resno(List *tlist, AttrNumber resno); +extern TargetEntry *get_tle_by_var(List *tlist, Var *var); /* ---------------- * FOR UPDATE/SHARE info diff --git a/src/test/regress/expected/qualify.out b/src/test/regress/expected/qualify.out new file mode 100644 index 00000000000..16f5dadafb0 --- /dev/null +++ b/src/test/regress/expected/qualify.out @@ -0,0 +1,1107 @@ +-- +-- QUALIFY clause tests +-- +CREATE TEMPORARY TABLE empsalary ( + depname varchar, + empno bigint, + salary int, + enroll_date date +); +INSERT INTO empsalary VALUES +('develop', 10, 5200, '2007-08-01'), +('sales', 1, 5000, '2006-10-01'), +('personnel', 5, 3500, '2007-12-10'), +('sales', 4, 4800, '2007-08-08'), +('personnel', 2, 3900, '2006-12-23'), +('develop', 7, 4200, '2008-01-01'), +('develop', 9, 4500, '2008-01-01'), +('sales', 3, 4800, '2007-08-01'), +('develop', 8, 6000, '2006-10-01'), +('develop', 11, 5200, '2007-08-15'); +-- +-- Basic QUALIFY functionality +-- +-- Simple QUALIFY with inline window function +EXPLAIN (COSTS OFF) +SELECT depname, empno, salary, + rank() OVER (PARTITION BY depname ORDER BY salary DESC) +FROM empsalary +QUALIFY rank() OVER (PARTITION BY depname ORDER BY salary DESC) <= 2; + QUERY PLAN +--------------------------------------------------------------------------------- + WindowAgg + Window: w1 AS (PARTITION BY depname ORDER BY salary ROWS UNBOUNDED PRECEDING) + Run Condition: (rank() OVER w1 <= 2) + -> Sort + Sort Key: depname, salary DESC + -> Seq Scan on empsalary +(6 rows) + +SELECT depname, empno, salary, + rank() OVER (PARTITION BY depname ORDER BY salary DESC) +FROM empsalary +QUALIFY rank() OVER (PARTITION BY depname ORDER BY salary DESC) <= 2 +ORDER BY depname, salary DESC, empno; + depname | empno | salary | rank +-----------+-------+--------+------ + develop | 8 | 6000 | 1 + develop | 10 | 5200 | 2 + develop | 11 | 5200 | 2 + personnel | 2 | 3900 | 1 + personnel | 5 | 3500 | 2 + sales | 1 | 5000 | 1 + sales | 3 | 4800 | 2 + sales | 4 | 4800 | 2 +(8 rows) + +-- QUALIFY with named window +EXPLAIN (COSTS OFF) +SELECT depname, empno, salary, + rank() OVER w +FROM empsalary +WINDOW w AS (PARTITION BY depname ORDER BY salary DESC) +QUALIFY rank() OVER w <= 2; + QUERY PLAN +-------------------------------------------------------------------------------- + WindowAgg + Window: w AS (PARTITION BY depname ORDER BY salary ROWS UNBOUNDED PRECEDING) + Run Condition: (rank() OVER w <= 2) + -> Sort + Sort Key: depname, salary DESC + -> Seq Scan on empsalary +(6 rows) + +SELECT depname, empno, salary, + rank() OVER w +FROM empsalary +WINDOW w AS (PARTITION BY depname ORDER BY salary DESC) +QUALIFY rank() OVER w <= 2 +ORDER BY depname, salary DESC, empno; + depname | empno | salary | rank +-----------+-------+--------+------ + develop | 8 | 6000 | 1 + develop | 10 | 5200 | 2 + develop | 11 | 5200 | 2 + personnel | 2 | 3900 | 1 + personnel | 5 | 3500 | 2 + sales | 1 | 5000 | 1 + sales | 3 | 4800 | 2 + sales | 4 | 4800 | 2 +(8 rows) + +-- +-- Alias resolution tests +-- +-- Reference alias defined in SELECT +EXPLAIN (COSTS OFF) +SELECT depname, empno, salary, + rank() OVER (PARTITION BY depname ORDER BY salary DESC) AS rnk +FROM empsalary +QUALIFY rnk <= 2; + QUERY PLAN +--------------------------------------------------------------------------------- + WindowAgg + Window: w1 AS (PARTITION BY depname ORDER BY salary ROWS UNBOUNDED PRECEDING) + Run Condition: (rank() OVER w1 <= 2) + -> Sort + Sort Key: depname, salary DESC + -> Seq Scan on empsalary +(6 rows) + +SELECT depname, empno, salary, + rank() OVER (PARTITION BY depname ORDER BY salary DESC) AS rnk +FROM empsalary +QUALIFY rnk <= 2 +ORDER BY depname, salary DESC, empno; + depname | empno | salary | rnk +-----------+-------+--------+----- + develop | 8 | 6000 | 1 + develop | 10 | 5200 | 2 + develop | 11 | 5200 | 2 + personnel | 2 | 3900 | 1 + personnel | 5 | 3500 | 2 + sales | 1 | 5000 | 1 + sales | 3 | 4800 | 2 + sales | 4 | 4800 | 2 +(8 rows) + +-- Reference alias with row_number +EXPLAIN (COSTS OFF) +SELECT empno, row_number() OVER (ORDER BY empno) AS rn +FROM empsalary +QUALIFY rn < 3; + QUERY PLAN +----------------------------------------------------------- + WindowAgg + Window: w1 AS (ORDER BY empno ROWS UNBOUNDED PRECEDING) + Run Condition: (row_number() OVER w1 < 3) + -> Sort + Sort Key: empno + -> Seq Scan on empsalary +(6 rows) + +SELECT empno, row_number() OVER (ORDER BY empno) AS rn +FROM empsalary +QUALIFY rn < 3; + empno | rn +-------+---- + 1 | 1 + 2 | 2 +(2 rows) + +-- Multiple aliases +EXPLAIN (COSTS OFF) +SELECT depname, empno, salary, + row_number() OVER (PARTITION BY depname ORDER BY salary DESC) AS rn, + rank() OVER (PARTITION BY depname ORDER BY salary DESC) AS rnk +FROM empsalary +QUALIFY rn = 1 OR rnk <= 2; + QUERY PLAN +--------------------------------------------------------------------------------- + WindowAgg + Window: w1 AS (PARTITION BY depname ORDER BY salary ROWS UNBOUNDED PRECEDING) + Filter: ((row_number() OVER w1 = 1) OR (rank() OVER w1 <= 2)) + -> Sort + Sort Key: depname, salary DESC + -> Seq Scan on empsalary +(6 rows) + +SELECT depname, empno, salary, + row_number() OVER (PARTITION BY depname ORDER BY salary DESC) AS rn, + rank() OVER (PARTITION BY depname ORDER BY salary DESC) AS rnk +FROM empsalary +QUALIFY rn = 1 OR rnk <= 2 +ORDER BY depname, salary DESC; + depname | empno | salary | rn | rnk +-----------+-------+--------+----+----- + develop | 8 | 6000 | 1 | 1 + develop | 10 | 5200 | 2 | 2 + develop | 11 | 5200 | 3 | 2 + personnel | 2 | 3900 | 1 | 1 + personnel | 5 | 3500 | 2 | 2 + sales | 1 | 5000 | 1 | 1 + sales | 4 | 4800 | 2 | 2 + sales | 3 | 4800 | 3 | 2 +(8 rows) + +-- +-- Run condition optimization tests +-- +-- row_number with < operator (should use run condition) +EXPLAIN (COSTS OFF) +SELECT empno, row_number() OVER (ORDER BY empno) AS rn +FROM empsalary +QUALIFY rn < 3; + QUERY PLAN +----------------------------------------------------------- + WindowAgg + Window: w1 AS (ORDER BY empno ROWS UNBOUNDED PRECEDING) + Run Condition: (row_number() OVER w1 < 3) + -> Sort + Sort Key: empno + -> Seq Scan on empsalary +(6 rows) + +SELECT empno, row_number() OVER (ORDER BY empno) AS rn +FROM empsalary +QUALIFY rn < 3; + empno | rn +-------+---- + 1 | 1 + 2 | 2 +(2 rows) + +-- row_number with <= operator +EXPLAIN (COSTS OFF) +SELECT empno, row_number() OVER (ORDER BY empno) AS rn +FROM empsalary +QUALIFY rn <= 2; + QUERY PLAN +----------------------------------------------------------- + WindowAgg + Window: w1 AS (ORDER BY empno ROWS UNBOUNDED PRECEDING) + Run Condition: (row_number() OVER w1 <= 2) + -> Sort + Sort Key: empno + -> Seq Scan on empsalary +(6 rows) + +SELECT empno, row_number() OVER (ORDER BY empno) AS rn +FROM empsalary +QUALIFY rn <= 2; + empno | rn +-------+---- + 1 | 1 + 2 | 2 +(2 rows) + +-- rank with <= operator +EXPLAIN (COSTS OFF) +SELECT empno, salary, rank() OVER (ORDER BY salary DESC) AS r +FROM empsalary +QUALIFY r <= 3; + QUERY PLAN +------------------------------------------------------------ + WindowAgg + Window: w1 AS (ORDER BY salary ROWS UNBOUNDED PRECEDING) + Run Condition: (rank() OVER w1 <= 3) + -> Sort + Sort Key: salary DESC + -> Seq Scan on empsalary +(6 rows) + +SELECT empno, salary, rank() OVER (ORDER BY salary DESC) AS r +FROM empsalary +QUALIFY r <= 3; + empno | salary | r +-------+--------+--- + 8 | 6000 | 1 + 10 | 5200 | 2 + 11 | 5200 | 2 +(3 rows) + +-- dense_rank with = operator (should convert to <= for run condition) +EXPLAIN (COSTS OFF) +SELECT empno, salary, dense_rank() OVER (ORDER BY salary DESC) AS dr +FROM empsalary +QUALIFY dr = 1; + QUERY PLAN +------------------------------------------------------------ + WindowAgg + Window: w1 AS (ORDER BY salary ROWS UNBOUNDED PRECEDING) + Run Condition: (dense_rank() OVER w1 <= 1) + Filter: (dense_rank() OVER w1 = 1) + -> Sort + Sort Key: salary DESC + -> Seq Scan on empsalary +(7 rows) + +SELECT empno, salary, dense_rank() OVER (ORDER BY salary DESC) AS dr +FROM empsalary +QUALIFY dr = 1; + empno | salary | dr +-------+--------+---- + 8 | 6000 | 1 +(1 row) + +-- count(*) with <= operator +EXPLAIN (COSTS OFF) +SELECT empno, salary, count(*) OVER (ORDER BY salary DESC) AS c +FROM empsalary +QUALIFY c <= 3; + QUERY PLAN +------------------------------------------ + WindowAgg + Window: w1 AS (ORDER BY salary) + Run Condition: (count(*) OVER w1 <= 3) + -> Sort + Sort Key: salary DESC + -> Seq Scan on empsalary +(6 rows) + +SELECT empno, salary, count(*) OVER (ORDER BY salary DESC) AS c +FROM empsalary +QUALIFY c <= 3; + empno | salary | c +-------+--------+--- + 8 | 6000 | 1 + 10 | 5200 | 3 + 11 | 5200 | 3 +(3 rows) + +-- +-- WHERE pushdown tests +-- Quals on PARTITION BY columns without window functions can be pushed to WHERE +-- +-- depname is in PARTITION BY, should be pushed to WHERE (SeqScan filter) +EXPLAIN (COSTS OFF) +SELECT depname, empno, salary, + rank() OVER (PARTITION BY depname ORDER BY salary DESC) AS rnk +FROM empsalary +QUALIFY rnk <= 2 AND depname = 'develop'; + QUERY PLAN +------------------------------------------------------------ + WindowAgg + Window: w1 AS (ORDER BY salary ROWS UNBOUNDED PRECEDING) + Run Condition: (rank() OVER w1 <= 2) + -> Sort + Sort Key: salary DESC + -> Seq Scan on empsalary + Filter: ((depname)::text = 'develop'::text) +(7 rows) + +SELECT depname, empno, salary, + rank() OVER (PARTITION BY depname ORDER BY salary DESC) AS rnk +FROM empsalary +QUALIFY rnk <= 2 AND depname = 'develop' +ORDER BY salary DESC; + depname | empno | salary | rnk +---------+-------+--------+----- + develop | 8 | 6000 | 1 + develop | 10 | 5200 | 2 + develop | 11 | 5200 | 2 +(3 rows) + +-- Mixed: depname pushed to WHERE, rank condition stays as run condition +EXPLAIN (COSTS OFF) +SELECT depname, empno, salary, + rank() OVER (PARTITION BY depname ORDER BY salary DESC) AS rnk +FROM empsalary +QUALIFY depname = 'sales' AND rnk = 1; + QUERY PLAN +------------------------------------------------------------ + WindowAgg + Window: w1 AS (ORDER BY salary ROWS UNBOUNDED PRECEDING) + Run Condition: (rank() OVER w1 <= 1) + Filter: (rank() OVER w1 = 1) + -> Sort + Sort Key: salary DESC + -> Seq Scan on empsalary + Filter: ((depname)::text = 'sales'::text) +(8 rows) + +SELECT depname, empno, salary, + rank() OVER (PARTITION BY depname ORDER BY salary DESC) AS rnk +FROM empsalary +QUALIFY depname = 'sales' AND rnk = 1; + depname | empno | salary | rnk +---------+-------+--------+----- + sales | 1 | 5000 | 1 +(1 row) + +-- +-- QUALIFY without window function in SELECT list +-- The window function appears only in QUALIFY +-- +EXPLAIN (COSTS OFF) +SELECT depname, empno, salary +FROM empsalary +QUALIFY row_number() OVER (PARTITION BY depname ORDER BY salary DESC) = 1; + QUERY PLAN +--------------------------------------------------------------------------------- + WindowAgg + Window: w1 AS (PARTITION BY depname ORDER BY salary ROWS UNBOUNDED PRECEDING) + Run Condition: (row_number() OVER w1 <= 1) + Filter: (row_number() OVER w1 = 1) + -> Sort + Sort Key: depname, salary DESC + -> Seq Scan on empsalary +(7 rows) + +SELECT depname, empno, salary +FROM empsalary +QUALIFY row_number() OVER (PARTITION BY depname ORDER BY salary DESC) = 1 +ORDER BY depname; + depname | empno | salary +-----------+-------+-------- + develop | 8 | 6000 + personnel | 2 | 3900 + sales | 1 | 5000 +(3 rows) + +-- With PARTITION BY column filter pushed down +EXPLAIN (COSTS OFF) +SELECT depname, empno, salary +FROM empsalary +QUALIFY row_number() OVER (PARTITION BY depname ORDER BY salary DESC) = 1 + AND depname = 'develop'; + QUERY PLAN +------------------------------------------------------------ + WindowAgg + Window: w1 AS (ORDER BY salary ROWS UNBOUNDED PRECEDING) + Run Condition: (row_number() OVER w1 <= 1) + Filter: (row_number() OVER w1 = 1) + -> Sort + Sort Key: salary DESC + -> Seq Scan on empsalary + Filter: ((depname)::text = 'develop'::text) +(8 rows) + +SELECT depname, empno, salary +FROM empsalary +QUALIFY row_number() OVER (PARTITION BY depname ORDER BY salary DESC) = 1 + AND depname = 'develop'; + depname | empno | salary +---------+-------+-------- + develop | 8 | 6000 +(1 row) + +-- +-- Multiple window functions with different windows +-- +EXPLAIN (COSTS OFF) +SELECT depname, empno, salary, + row_number() OVER (PARTITION BY depname ORDER BY salary) AS rn_asc, + row_number() OVER (PARTITION BY depname ORDER BY salary DESC) AS rn_desc +FROM empsalary +QUALIFY rn_asc = 1 OR rn_desc = 1; + QUERY PLAN +--------------------------------------------------------------------------------------------- + WindowAgg + Window: w2 AS (PARTITION BY depname ORDER BY salary ROWS UNBOUNDED PRECEDING) + Filter: ((row_number() OVER w2 = 1) OR ((row_number() OVER w1) = 1)) + -> Incremental Sort + Sort Key: depname, salary + Presorted Key: depname + -> WindowAgg + Window: w1 AS (PARTITION BY depname ORDER BY salary ROWS UNBOUNDED PRECEDING) + -> Sort + Sort Key: depname, salary DESC + -> Seq Scan on empsalary +(11 rows) + +SELECT depname, empno, salary, + row_number() OVER (PARTITION BY depname ORDER BY salary) AS rn_asc, + row_number() OVER (PARTITION BY depname ORDER BY salary DESC) AS rn_desc +FROM empsalary +QUALIFY rn_asc = 1 OR rn_desc = 1 +ORDER BY depname, empno; + depname | empno | salary | rn_asc | rn_desc +-----------+-------+--------+--------+--------- + develop | 7 | 4200 | 1 | 5 + develop | 8 | 6000 | 5 | 1 + personnel | 2 | 3900 | 2 | 1 + personnel | 5 | 3500 | 1 | 2 + sales | 1 | 5000 | 3 | 1 + sales | 4 | 4800 | 1 | 2 +(6 rows) + +-- +-- QUALIFY with aggregate and window functions +-- +EXPLAIN (COSTS OFF) +SELECT depname, sum(salary) AS total, + rank() OVER (ORDER BY sum(salary) DESC) AS rnk +FROM empsalary +GROUP BY depname +QUALIFY rnk <= 2; + QUERY PLAN +------------------------------------------------------------------- + WindowAgg + Window: w1 AS (ORDER BY (sum(salary)) ROWS UNBOUNDED PRECEDING) + Run Condition: (rank() OVER w1 <= 2) + -> Sort + Sort Key: (sum(salary)) DESC + -> HashAggregate + Group Key: depname + -> Seq Scan on empsalary +(8 rows) + +SELECT depname, sum(salary) AS total, + rank() OVER (ORDER BY sum(salary) DESC) AS rnk +FROM empsalary +GROUP BY depname +QUALIFY rnk <= 2; + depname | total | rnk +---------+-------+----- + develop | 25100 | 1 + sales | 14600 | 2 +(2 rows) + +-- +-- QUALIFY in a grouped query without window functions. +-- The condition is validated like a SELECT-list item, so aggregates and +-- grouping columns are allowed (and behave as HAVING would). +-- +-- Aggregate used directly in QUALIFY filters groups like HAVING +SELECT depname, count(*) AS c +FROM empsalary +GROUP BY depname +QUALIFY count(*) > 2 +ORDER BY depname; + depname | c +---------+--- + develop | 5 + sales | 3 +(2 rows) + +-- A grouping column may be referenced directly in QUALIFY +SELECT depname, count(*) AS c +FROM empsalary +GROUP BY depname +QUALIFY depname <> 'sales' +ORDER BY depname; + depname | c +-----------+--- + develop | 5 + personnel | 2 +(2 rows) + +-- +-- Comparison with equivalent subquery (results should match) +-- +-- Subquery version +EXPLAIN (COSTS OFF) +SELECT * FROM + (SELECT empno, salary, rank() OVER (ORDER BY salary DESC) AS r + FROM empsalary) emp +WHERE r <= 3; + QUERY PLAN +---------------------------------------------------------------------- + WindowAgg + Window: w1 AS (ORDER BY empsalary.salary ROWS UNBOUNDED PRECEDING) + Run Condition: (rank() OVER w1 <= 3) + -> Sort + Sort Key: empsalary.salary DESC + -> Seq Scan on empsalary +(6 rows) + +SELECT * FROM + (SELECT empno, salary, rank() OVER (ORDER BY salary DESC) AS r + FROM empsalary) emp +WHERE r <= 3 +ORDER BY salary DESC, empno; + empno | salary | r +-------+--------+--- + 8 | 6000 | 1 + 10 | 5200 | 2 + 11 | 5200 | 2 +(3 rows) + +-- QUALIFY version (should produce same results) +EXPLAIN (COSTS OFF) +SELECT empno, salary, rank() OVER (ORDER BY salary DESC) AS r +FROM empsalary +QUALIFY r <= 3; + QUERY PLAN +------------------------------------------------------------ + WindowAgg + Window: w1 AS (ORDER BY salary ROWS UNBOUNDED PRECEDING) + Run Condition: (rank() OVER w1 <= 3) + -> Sort + Sort Key: salary DESC + -> Seq Scan on empsalary +(6 rows) + +SELECT empno, salary, rank() OVER (ORDER BY salary DESC) AS r +FROM empsalary +QUALIFY r <= 3 +ORDER BY salary DESC, empno; + empno | salary | r +-------+--------+--- + 8 | 6000 | 1 + 10 | 5200 | 2 + 11 | 5200 | 2 +(3 rows) + +-- +-- QUALIFY with different comparison operators +-- +-- Greater than (no run condition optimization for increasing functions) +EXPLAIN (COSTS OFF) +SELECT empno, row_number() OVER (ORDER BY empno) AS rn +FROM empsalary +QUALIFY rn > 7; + QUERY PLAN +----------------------------------------------------------- + WindowAgg + Window: w1 AS (ORDER BY empno ROWS UNBOUNDED PRECEDING) + Filter: (row_number() OVER w1 > 7) + -> Sort + Sort Key: empno + -> Seq Scan on empsalary +(6 rows) + +SELECT empno, row_number() OVER (ORDER BY empno) AS rn +FROM empsalary +QUALIFY rn > 7 +ORDER BY empno; + empno | rn +-------+---- + 9 | 8 + 10 | 9 + 11 | 10 +(3 rows) + +-- Equality +EXPLAIN (COSTS OFF) +SELECT empno, row_number() OVER (ORDER BY empno) AS rn +FROM empsalary +QUALIFY rn = 5; + QUERY PLAN +----------------------------------------------------------- + WindowAgg + Window: w1 AS (ORDER BY empno ROWS UNBOUNDED PRECEDING) + Run Condition: (row_number() OVER w1 <= 5) + Filter: (row_number() OVER w1 = 5) + -> Sort + Sort Key: empno + -> Seq Scan on empsalary +(7 rows) + +SELECT empno, row_number() OVER (ORDER BY empno) AS rn +FROM empsalary +QUALIFY rn = 5; + empno | rn +-------+---- + 5 | 5 +(1 row) + +-- BETWEEN (should keep as filter) +EXPLAIN (COSTS OFF) +SELECT empno, row_number() OVER (ORDER BY empno) AS rn +FROM empsalary +QUALIFY rn BETWEEN 3 AND 5; + QUERY PLAN +----------------------------------------------------------- + WindowAgg + Window: w1 AS (ORDER BY empno ROWS UNBOUNDED PRECEDING) + Run Condition: (row_number() OVER w1 <= 5) + Filter: (row_number() OVER w1 >= 3) + -> Sort + Sort Key: empno + -> Seq Scan on empsalary +(7 rows) + +SELECT empno, row_number() OVER (ORDER BY empno) AS rn +FROM empsalary +QUALIFY rn BETWEEN 3 AND 5 +ORDER BY empno; + empno | rn +-------+---- + 3 | 3 + 4 | 4 + 5 | 5 +(3 rows) + +-- IN list +EXPLAIN (COSTS OFF) +SELECT empno, row_number() OVER (ORDER BY empno) AS rn +FROM empsalary +QUALIFY rn IN (1, 3, 5); + QUERY PLAN +-------------------------------------------------------------- + WindowAgg + Window: w1 AS (ORDER BY empno ROWS UNBOUNDED PRECEDING) + Filter: (row_number() OVER w1 = ANY ('{1,3,5}'::bigint[])) + -> Sort + Sort Key: empno + -> Seq Scan on empsalary +(6 rows) + +SELECT empno, row_number() OVER (ORDER BY empno) AS rn +FROM empsalary +QUALIFY rn IN (1, 3, 5) +ORDER BY empno; + empno | rn +-------+---- + 1 | 1 + 3 | 3 + 5 | 5 +(3 rows) + +-- +-- QUALIFY with expressions +-- +EXPLAIN (COSTS OFF) +SELECT empno, salary, + row_number() OVER (ORDER BY salary DESC) AS rn +FROM empsalary +QUALIFY rn <= 10 / 2; + QUERY PLAN +------------------------------------------------------------ + WindowAgg + Window: w1 AS (ORDER BY salary ROWS UNBOUNDED PRECEDING) + Run Condition: (row_number() OVER w1 <= 5) + -> Sort + Sort Key: salary DESC + -> Seq Scan on empsalary +(6 rows) + +SELECT empno, salary, + row_number() OVER (ORDER BY salary DESC) AS rn +FROM empsalary +QUALIFY rn <= 10 / 2; + empno | salary | rn +-------+--------+---- + 8 | 6000 | 1 + 10 | 5200 | 2 + 11 | 5200 | 3 + 1 | 5000 | 4 + 4 | 4800 | 5 +(5 rows) + +-- +-- Error cases +-- +-- Aggregate in QUALIFY without GROUP BY: just as in the SELECT list, the +-- non-aggregated output columns make this an error (must appear in GROUP BY). +SELECT depname, empno, salary +FROM empsalary +QUALIFY sum(salary) > 5000; +ERROR: column "empsalary.depname" must appear in the GROUP BY clause or be used in an aggregate function +LINE 1: SELECT depname, empno, salary + ^ +-- QUALIFY in a grouped query referencing an ungrouped column (should error, +-- just as such a reference would in the SELECT list) +SELECT depname, count(*) AS c +FROM empsalary +GROUP BY depname +QUALIFY salary > 100; +ERROR: column "empsalary.salary" must appear in the GROUP BY clause or be used in an aggregate function +LINE 4: QUALIFY salary > 100; + ^ +-- QUALIFY with set-returning function (should error) +SELECT empno, salary +FROM empsalary +QUALIFY generate_series(1, 3) > 1; +ERROR: set-returning functions are not allowed in QUALIFY +LINE 3: QUALIFY generate_series(1, 3) > 1; + ^ +-- +-- QUALIFY in different query structures +-- +-- With DISTINCT +EXPLAIN (COSTS OFF) +SELECT DISTINCT depname, + first_value(empno) OVER (PARTITION BY depname ORDER BY salary DESC) AS top_emp +FROM empsalary +QUALIFY row_number() OVER (PARTITION BY depname ORDER BY salary DESC) = 1; + QUERY PLAN +-------------------------------------------------------------- + HashAggregate + Group Key: depname, first_value(empno) OVER w1 + -> WindowAgg + Window: w1 AS (PARTITION BY depname ORDER BY salary) + Run Condition: (row_number() OVER w1 <= 1) + Filter: (row_number() OVER w1 = 1) + -> Sort + Sort Key: depname, salary DESC + -> Seq Scan on empsalary +(9 rows) + +SELECT DISTINCT depname, + first_value(empno) OVER (PARTITION BY depname ORDER BY salary DESC) AS top_emp +FROM empsalary +QUALIFY row_number() OVER (PARTITION BY depname ORDER BY salary DESC) = 1 +ORDER BY depname; + depname | top_emp +-----------+--------- + develop | 8 + personnel | 2 + sales | 1 +(3 rows) + +-- With ORDER BY +EXPLAIN (COSTS OFF) +SELECT empno, salary, + rank() OVER (ORDER BY salary DESC) AS rnk +FROM empsalary +QUALIFY rnk <= 3; + QUERY PLAN +------------------------------------------------------------ + WindowAgg + Window: w1 AS (ORDER BY salary ROWS UNBOUNDED PRECEDING) + Run Condition: (rank() OVER w1 <= 3) + -> Sort + Sort Key: salary DESC + -> Seq Scan on empsalary +(6 rows) + +SELECT empno, salary, + rank() OVER (ORDER BY salary DESC) AS rnk +FROM empsalary +QUALIFY rnk <= 3 +ORDER BY salary DESC, empno; + empno | salary | rnk +-------+--------+----- + 8 | 6000 | 1 + 10 | 5200 | 2 + 11 | 5200 | 2 +(3 rows) + +-- With LIMIT (QUALIFY is evaluated before LIMIT) +EXPLAIN (COSTS OFF) +SELECT empno, salary, + row_number() OVER (ORDER BY salary DESC) AS rn +FROM empsalary +QUALIFY rn <= 5 +LIMIT 3; + QUERY PLAN +------------------------------------------------------------------ + Limit + -> WindowAgg + Window: w1 AS (ORDER BY salary ROWS UNBOUNDED PRECEDING) + Run Condition: (row_number() OVER w1 <= 5) + -> Sort + Sort Key: salary DESC + -> Seq Scan on empsalary +(7 rows) + +SELECT empno, salary, + row_number() OVER (ORDER BY salary DESC) AS rn +FROM empsalary +QUALIFY rn <= 5 +ORDER BY salary DESC +LIMIT 3; + empno | salary | rn +-------+--------+---- + 8 | 6000 | 1 + 10 | 5200 | 2 + 11 | 5200 | 3 +(3 rows) + +-- +-- Verify evaluation order: QUALIFY happens after window functions, before DISTINCT +-- +-- This should first compute row_number, then filter, then apply DISTINCT +EXPLAIN (COSTS OFF) +SELECT DISTINCT salary, + row_number() OVER (ORDER BY salary DESC) AS rn +FROM empsalary +QUALIFY rn <= 3; + QUERY PLAN +------------------------------------------------------------------ + HashAggregate + Group Key: salary, row_number() OVER w1 + -> WindowAgg + Window: w1 AS (ORDER BY salary ROWS UNBOUNDED PRECEDING) + Run Condition: (row_number() OVER w1 <= 3) + -> Sort + Sort Key: salary DESC + -> Seq Scan on empsalary +(8 rows) + +SELECT DISTINCT salary, + row_number() OVER (ORDER BY salary DESC) AS rn +FROM empsalary +QUALIFY rn <= 3; + salary | rn +--------+---- + 5200 | 2 + 5200 | 3 + 6000 | 1 +(3 rows) + +-- QUALIFY with FILTERS over window function +EXPLAIN(COSTS OFF) SELECT sum(salary), row_number() OVER (ORDER BY depname) as rank, sum( + sum(salary) FILTER (WHERE enroll_date > '2007-01-01') +) FILTER (WHERE depname <> 'sales') OVER (ORDER BY depname DESC) AS "filtered_sum", + depname +FROM empsalary GROUP BY depname QUALIFY rank = 1; + QUERY PLAN +------------------------------------------------------------- + WindowAgg + Window: w2 AS (ORDER BY depname ROWS UNBOUNDED PRECEDING) + Run Condition: (row_number() OVER w2 <= 1) + Filter: (row_number() OVER w2 = 1) + -> Sort + Sort Key: depname + -> WindowAgg + Window: w1 AS (ORDER BY depname) + -> Sort + Sort Key: depname DESC + -> HashAggregate + Group Key: depname + -> Seq Scan on empsalary +(13 rows) + +SELECT sum(salary), row_number() OVER (ORDER BY depname) as rank, sum( + sum(salary) FILTER (WHERE enroll_date > '2007-01-01') +) FILTER (WHERE depname <> 'sales') OVER (ORDER BY depname DESC) AS "filtered_sum", + depname +FROM empsalary GROUP BY depname QUALIFY rank = 1; + sum | rank | filtered_sum | depname +-------+------+--------------+--------- + 25100 | 1 | 22600 | develop +(1 row) + +-- +-- Aggregate functions directly in QUALIFY +-- (allowed in a grouped query, the same as in the SELECT list / HAVING) +-- +-- Aggregate condition over the group +EXPLAIN (COSTS OFF) +SELECT depname, sum(salary) AS total +FROM empsalary +GROUP BY depname +QUALIFY sum(salary) > 15000; + QUERY PLAN +------------------------------------------------- + Subquery Scan on pg_qualify + -> HashAggregate + Group Key: empsalary.depname + Filter: (sum(empsalary.salary) > 15000) + -> Seq Scan on empsalary +(5 rows) + +SELECT depname, sum(salary) AS total +FROM empsalary +GROUP BY depname +QUALIFY sum(salary) > 15000 +ORDER BY depname; + depname | total +---------+------- + develop | 25100 +(1 row) + +-- Aggregate and window function combined in QUALIFY +SELECT depname, sum(salary) AS total +FROM empsalary +GROUP BY depname +QUALIFY rank() OVER (ORDER BY sum(salary) DESC) = 1 +ORDER BY depname; + depname | total +---------+------- + develop | 25100 +(1 row) + +-- Whole-table aggregate (single group) +SELECT sum(salary) AS s +FROM empsalary +QUALIFY sum(salary) > 1000; + s +------- + 47100 +(1 row) + +-- +-- Name resolution tests +-- +-- An input column takes precedence over a same-named select-list alias +-- "salary" in QUALIFY is the input column (> 4000 keeps most rows), not the +-- alias (which is 0 and would keep none) +SELECT empno, (empno * 0) AS salary +FROM empsalary +QUALIFY salary > 4000 +ORDER BY empno; + empno | salary +-------+-------- + 1 | 0 + 3 | 0 + 4 | 0 + 7 | 0 + 8 | 0 + 9 | 0 + 10 | 0 + 11 | 0 +(8 rows) + +-- QUALIFY referecing columns that not on output result +-- Same column +SELECT depname FROM empsalary QUALIFY salary > 0 ORDER BY salary; + depname +----------- + personnel + personnel + develop + develop + sales + sales + sales + develop + develop + develop +(10 rows) + +-- Different column +SELECT depname FROM empsalary QUALIFY salary > 0 ORDER BY enroll_date; + depname +----------- + sales + develop + personnel + sales + develop + sales + develop + personnel + develop + develop +(10 rows) + +-- +-- Name resolution error cases +-- +-- A non-deterministic alias expression cannot be referenced from QUALIFY +SELECT empno, (random() < 2)::int AS r +FROM empsalary +QUALIFY r = 1; +ERROR: QUALIFY cannot reference output column "r" whose expression is non-deterministic +LINE 3: QUALIFY r = 1; + ^ +-- An ambiguous alias reference is rejected +SELECT empno AS x, salary AS x, + row_number() OVER (ORDER BY empno) AS rn +FROM empsalary +QUALIFY x > 0; +ERROR: column reference "x" is ambiguous +LINE 4: QUALIFY x > 0; + ^ +-- A name that is neither an input column nor a select-list alias +SELECT empno +FROM empsalary +QUALIFY nosuchcol > row_number() OVER (ORDER BY empno); +ERROR: column "nosuchcol" does not exist +LINE 3: QUALIFY nosuchcol > row_number() OVER (ORDER BY empno); + ^ +-- +-- QUALIFY without any window function: a general post-SELECT filter +-- +EXPLAIN (COSTS OFF) +SELECT empno, salary, salary * 2 as new_salary +FROM empsalary +QUALIFY new_salary > 10000; + QUERY PLAN +---------------------------------- + Seq Scan on empsalary + Filter: ((salary * 2) > 10000) +(2 rows) + +SELECT empno, salary, salary * 2 as new_salary +FROM empsalary +QUALIFY new_salary > 10000 +ORDER BY empno; + empno | salary | new_salary +-------+--------+------------ + 8 | 6000 | 12000 + 10 | 5200 | 10400 + 11 | 5200 | 10400 +(3 rows) + +-- +-- QUALIFY in other query structures +-- +-- In the arms of a set operation +SELECT empno FROM empsalary +QUALIFY row_number() OVER (ORDER BY salary) = 1 +UNION ALL +SELECT empno FROM empsalary +QUALIFY row_number() OVER (ORDER BY salary DESC) = 1 +ORDER BY empno; + empno +------- + 5 + 8 +(2 rows) + +-- In a subquery that carries an outer reference +SELECT depname, + (SELECT empno FROM empsalary e2 + WHERE e2.depname = e1.depname + QUALIFY row_number() OVER (ORDER BY salary DESC) = 1) AS top_emp +FROM (SELECT DISTINCT depname FROM empsalary) e1 +ORDER BY depname; + depname | top_emp +-----------+--------- + develop | 8 + personnel | 2 + sales | 1 +(3 rows) + +-- Combined with row-level locking (the lock applies to the base table) +SELECT empno FROM empsalary +QUALIFY salary > 5000 +ORDER BY empno +FOR UPDATE; + empno +------- + 8 + 10 + 11 +(3 rows) + +-- +-- Cleanup +-- +DROP TABLE empsalary; diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index 8fa0a6c47fb..278554a7307 100644 --- a/src/test/regress/parallel_schedule +++ b/src/test/regress/parallel_schedule @@ -102,7 +102,7 @@ test: publication subscription # Another group of parallel tests # select_views depends on create_view # ---------- -test: select_views portals_p2 foreign_key dependency guc bitmapops combocid tsearch tsdicts foreign_data window xmlmap functional_deps advisory_lock indirect_toast equivclass stats_rewrite graph_table +test: select_views portals_p2 foreign_key dependency guc bitmapops combocid tsearch tsdicts foreign_data window qualify xmlmap functional_deps advisory_lock indirect_toast equivclass stats_rewrite graph_table # ---------- # Another group of parallel tests (JSON related) diff --git a/src/test/regress/sql/qualify.sql b/src/test/regress/sql/qualify.sql new file mode 100644 index 00000000000..c9bf99e23fe --- /dev/null +++ b/src/test/regress/sql/qualify.sql @@ -0,0 +1,563 @@ +-- +-- QUALIFY clause tests +-- + +CREATE TEMPORARY TABLE empsalary ( + depname varchar, + empno bigint, + salary int, + enroll_date date +); + +INSERT INTO empsalary VALUES +('develop', 10, 5200, '2007-08-01'), +('sales', 1, 5000, '2006-10-01'), +('personnel', 5, 3500, '2007-12-10'), +('sales', 4, 4800, '2007-08-08'), +('personnel', 2, 3900, '2006-12-23'), +('develop', 7, 4200, '2008-01-01'), +('develop', 9, 4500, '2008-01-01'), +('sales', 3, 4800, '2007-08-01'), +('develop', 8, 6000, '2006-10-01'), +('develop', 11, 5200, '2007-08-15'); + +-- +-- Basic QUALIFY functionality +-- + +-- Simple QUALIFY with inline window function +EXPLAIN (COSTS OFF) +SELECT depname, empno, salary, + rank() OVER (PARTITION BY depname ORDER BY salary DESC) +FROM empsalary +QUALIFY rank() OVER (PARTITION BY depname ORDER BY salary DESC) <= 2; + +SELECT depname, empno, salary, + rank() OVER (PARTITION BY depname ORDER BY salary DESC) +FROM empsalary +QUALIFY rank() OVER (PARTITION BY depname ORDER BY salary DESC) <= 2 +ORDER BY depname, salary DESC, empno; + +-- QUALIFY with named window +EXPLAIN (COSTS OFF) +SELECT depname, empno, salary, + rank() OVER w +FROM empsalary +WINDOW w AS (PARTITION BY depname ORDER BY salary DESC) +QUALIFY rank() OVER w <= 2; + +SELECT depname, empno, salary, + rank() OVER w +FROM empsalary +WINDOW w AS (PARTITION BY depname ORDER BY salary DESC) +QUALIFY rank() OVER w <= 2 +ORDER BY depname, salary DESC, empno; + +-- +-- Alias resolution tests +-- + +-- Reference alias defined in SELECT +EXPLAIN (COSTS OFF) +SELECT depname, empno, salary, + rank() OVER (PARTITION BY depname ORDER BY salary DESC) AS rnk +FROM empsalary +QUALIFY rnk <= 2; + +SELECT depname, empno, salary, + rank() OVER (PARTITION BY depname ORDER BY salary DESC) AS rnk +FROM empsalary +QUALIFY rnk <= 2 +ORDER BY depname, salary DESC, empno; + +-- Reference alias with row_number +EXPLAIN (COSTS OFF) +SELECT empno, row_number() OVER (ORDER BY empno) AS rn +FROM empsalary +QUALIFY rn < 3; + +SELECT empno, row_number() OVER (ORDER BY empno) AS rn +FROM empsalary +QUALIFY rn < 3; + +-- Multiple aliases +EXPLAIN (COSTS OFF) +SELECT depname, empno, salary, + row_number() OVER (PARTITION BY depname ORDER BY salary DESC) AS rn, + rank() OVER (PARTITION BY depname ORDER BY salary DESC) AS rnk +FROM empsalary +QUALIFY rn = 1 OR rnk <= 2; + +SELECT depname, empno, salary, + row_number() OVER (PARTITION BY depname ORDER BY salary DESC) AS rn, + rank() OVER (PARTITION BY depname ORDER BY salary DESC) AS rnk +FROM empsalary +QUALIFY rn = 1 OR rnk <= 2 +ORDER BY depname, salary DESC; + +-- +-- Run condition optimization tests +-- + +-- row_number with < operator (should use run condition) +EXPLAIN (COSTS OFF) +SELECT empno, row_number() OVER (ORDER BY empno) AS rn +FROM empsalary +QUALIFY rn < 3; + +SELECT empno, row_number() OVER (ORDER BY empno) AS rn +FROM empsalary +QUALIFY rn < 3; + +-- row_number with <= operator +EXPLAIN (COSTS OFF) +SELECT empno, row_number() OVER (ORDER BY empno) AS rn +FROM empsalary +QUALIFY rn <= 2; + +SELECT empno, row_number() OVER (ORDER BY empno) AS rn +FROM empsalary +QUALIFY rn <= 2; + +-- rank with <= operator +EXPLAIN (COSTS OFF) +SELECT empno, salary, rank() OVER (ORDER BY salary DESC) AS r +FROM empsalary +QUALIFY r <= 3; + +SELECT empno, salary, rank() OVER (ORDER BY salary DESC) AS r +FROM empsalary +QUALIFY r <= 3; + +-- dense_rank with = operator (should convert to <= for run condition) +EXPLAIN (COSTS OFF) +SELECT empno, salary, dense_rank() OVER (ORDER BY salary DESC) AS dr +FROM empsalary +QUALIFY dr = 1; + +SELECT empno, salary, dense_rank() OVER (ORDER BY salary DESC) AS dr +FROM empsalary +QUALIFY dr = 1; + +-- count(*) with <= operator +EXPLAIN (COSTS OFF) +SELECT empno, salary, count(*) OVER (ORDER BY salary DESC) AS c +FROM empsalary +QUALIFY c <= 3; + +SELECT empno, salary, count(*) OVER (ORDER BY salary DESC) AS c +FROM empsalary +QUALIFY c <= 3; + +-- +-- WHERE pushdown tests +-- Quals on PARTITION BY columns without window functions can be pushed to WHERE +-- + +-- depname is in PARTITION BY, should be pushed to WHERE (SeqScan filter) +EXPLAIN (COSTS OFF) +SELECT depname, empno, salary, + rank() OVER (PARTITION BY depname ORDER BY salary DESC) AS rnk +FROM empsalary +QUALIFY rnk <= 2 AND depname = 'develop'; + +SELECT depname, empno, salary, + rank() OVER (PARTITION BY depname ORDER BY salary DESC) AS rnk +FROM empsalary +QUALIFY rnk <= 2 AND depname = 'develop' +ORDER BY salary DESC; + +-- Mixed: depname pushed to WHERE, rank condition stays as run condition +EXPLAIN (COSTS OFF) +SELECT depname, empno, salary, + rank() OVER (PARTITION BY depname ORDER BY salary DESC) AS rnk +FROM empsalary +QUALIFY depname = 'sales' AND rnk = 1; + +SELECT depname, empno, salary, + rank() OVER (PARTITION BY depname ORDER BY salary DESC) AS rnk +FROM empsalary +QUALIFY depname = 'sales' AND rnk = 1; + +-- +-- QUALIFY without window function in SELECT list +-- The window function appears only in QUALIFY +-- + +EXPLAIN (COSTS OFF) +SELECT depname, empno, salary +FROM empsalary +QUALIFY row_number() OVER (PARTITION BY depname ORDER BY salary DESC) = 1; + +SELECT depname, empno, salary +FROM empsalary +QUALIFY row_number() OVER (PARTITION BY depname ORDER BY salary DESC) = 1 +ORDER BY depname; + +-- With PARTITION BY column filter pushed down +EXPLAIN (COSTS OFF) +SELECT depname, empno, salary +FROM empsalary +QUALIFY row_number() OVER (PARTITION BY depname ORDER BY salary DESC) = 1 + AND depname = 'develop'; + +SELECT depname, empno, salary +FROM empsalary +QUALIFY row_number() OVER (PARTITION BY depname ORDER BY salary DESC) = 1 + AND depname = 'develop'; + +-- +-- Multiple window functions with different windows +-- + +EXPLAIN (COSTS OFF) +SELECT depname, empno, salary, + row_number() OVER (PARTITION BY depname ORDER BY salary) AS rn_asc, + row_number() OVER (PARTITION BY depname ORDER BY salary DESC) AS rn_desc +FROM empsalary +QUALIFY rn_asc = 1 OR rn_desc = 1; + +SELECT depname, empno, salary, + row_number() OVER (PARTITION BY depname ORDER BY salary) AS rn_asc, + row_number() OVER (PARTITION BY depname ORDER BY salary DESC) AS rn_desc +FROM empsalary +QUALIFY rn_asc = 1 OR rn_desc = 1 +ORDER BY depname, empno; + +-- +-- QUALIFY with aggregate and window functions +-- + +EXPLAIN (COSTS OFF) +SELECT depname, sum(salary) AS total, + rank() OVER (ORDER BY sum(salary) DESC) AS rnk +FROM empsalary +GROUP BY depname +QUALIFY rnk <= 2; + +SELECT depname, sum(salary) AS total, + rank() OVER (ORDER BY sum(salary) DESC) AS rnk +FROM empsalary +GROUP BY depname +QUALIFY rnk <= 2; + +-- +-- QUALIFY in a grouped query without window functions. +-- The condition is validated like a SELECT-list item, so aggregates and +-- grouping columns are allowed (and behave as HAVING would). +-- + +-- Aggregate used directly in QUALIFY filters groups like HAVING +SELECT depname, count(*) AS c +FROM empsalary +GROUP BY depname +QUALIFY count(*) > 2 +ORDER BY depname; + +-- A grouping column may be referenced directly in QUALIFY +SELECT depname, count(*) AS c +FROM empsalary +GROUP BY depname +QUALIFY depname <> 'sales' +ORDER BY depname; + +-- +-- Comparison with equivalent subquery (results should match) +-- + +-- Subquery version +EXPLAIN (COSTS OFF) +SELECT * FROM + (SELECT empno, salary, rank() OVER (ORDER BY salary DESC) AS r + FROM empsalary) emp +WHERE r <= 3; + +SELECT * FROM + (SELECT empno, salary, rank() OVER (ORDER BY salary DESC) AS r + FROM empsalary) emp +WHERE r <= 3 +ORDER BY salary DESC, empno; + +-- QUALIFY version (should produce same results) +EXPLAIN (COSTS OFF) +SELECT empno, salary, rank() OVER (ORDER BY salary DESC) AS r +FROM empsalary +QUALIFY r <= 3; + +SELECT empno, salary, rank() OVER (ORDER BY salary DESC) AS r +FROM empsalary +QUALIFY r <= 3 +ORDER BY salary DESC, empno; + +-- +-- QUALIFY with different comparison operators +-- + +-- Greater than (no run condition optimization for increasing functions) +EXPLAIN (COSTS OFF) +SELECT empno, row_number() OVER (ORDER BY empno) AS rn +FROM empsalary +QUALIFY rn > 7; + +SELECT empno, row_number() OVER (ORDER BY empno) AS rn +FROM empsalary +QUALIFY rn > 7 +ORDER BY empno; + +-- Equality +EXPLAIN (COSTS OFF) +SELECT empno, row_number() OVER (ORDER BY empno) AS rn +FROM empsalary +QUALIFY rn = 5; + +SELECT empno, row_number() OVER (ORDER BY empno) AS rn +FROM empsalary +QUALIFY rn = 5; + +-- BETWEEN (should keep as filter) +EXPLAIN (COSTS OFF) +SELECT empno, row_number() OVER (ORDER BY empno) AS rn +FROM empsalary +QUALIFY rn BETWEEN 3 AND 5; + +SELECT empno, row_number() OVER (ORDER BY empno) AS rn +FROM empsalary +QUALIFY rn BETWEEN 3 AND 5 +ORDER BY empno; + +-- IN list +EXPLAIN (COSTS OFF) +SELECT empno, row_number() OVER (ORDER BY empno) AS rn +FROM empsalary +QUALIFY rn IN (1, 3, 5); + +SELECT empno, row_number() OVER (ORDER BY empno) AS rn +FROM empsalary +QUALIFY rn IN (1, 3, 5) +ORDER BY empno; + +-- +-- QUALIFY with expressions +-- + +EXPLAIN (COSTS OFF) +SELECT empno, salary, + row_number() OVER (ORDER BY salary DESC) AS rn +FROM empsalary +QUALIFY rn <= 10 / 2; + +SELECT empno, salary, + row_number() OVER (ORDER BY salary DESC) AS rn +FROM empsalary +QUALIFY rn <= 10 / 2; + +-- +-- Error cases +-- + +-- Aggregate in QUALIFY without GROUP BY: just as in the SELECT list, the +-- non-aggregated output columns make this an error (must appear in GROUP BY). +SELECT depname, empno, salary +FROM empsalary +QUALIFY sum(salary) > 5000; + +-- QUALIFY in a grouped query referencing an ungrouped column (should error, +-- just as such a reference would in the SELECT list) +SELECT depname, count(*) AS c +FROM empsalary +GROUP BY depname +QUALIFY salary > 100; + +-- QUALIFY with set-returning function (should error) +SELECT empno, salary +FROM empsalary +QUALIFY generate_series(1, 3) > 1; + +-- +-- QUALIFY in different query structures +-- + +-- With DISTINCT +EXPLAIN (COSTS OFF) +SELECT DISTINCT depname, + first_value(empno) OVER (PARTITION BY depname ORDER BY salary DESC) AS top_emp +FROM empsalary +QUALIFY row_number() OVER (PARTITION BY depname ORDER BY salary DESC) = 1; + +SELECT DISTINCT depname, + first_value(empno) OVER (PARTITION BY depname ORDER BY salary DESC) AS top_emp +FROM empsalary +QUALIFY row_number() OVER (PARTITION BY depname ORDER BY salary DESC) = 1 +ORDER BY depname; + +-- With ORDER BY +EXPLAIN (COSTS OFF) +SELECT empno, salary, + rank() OVER (ORDER BY salary DESC) AS rnk +FROM empsalary +QUALIFY rnk <= 3; + +SELECT empno, salary, + rank() OVER (ORDER BY salary DESC) AS rnk +FROM empsalary +QUALIFY rnk <= 3 +ORDER BY salary DESC, empno; + +-- With LIMIT (QUALIFY is evaluated before LIMIT) +EXPLAIN (COSTS OFF) +SELECT empno, salary, + row_number() OVER (ORDER BY salary DESC) AS rn +FROM empsalary +QUALIFY rn <= 5 +LIMIT 3; + +SELECT empno, salary, + row_number() OVER (ORDER BY salary DESC) AS rn +FROM empsalary +QUALIFY rn <= 5 +ORDER BY salary DESC +LIMIT 3; + +-- +-- Verify evaluation order: QUALIFY happens after window functions, before DISTINCT +-- + +-- This should first compute row_number, then filter, then apply DISTINCT +EXPLAIN (COSTS OFF) +SELECT DISTINCT salary, + row_number() OVER (ORDER BY salary DESC) AS rn +FROM empsalary +QUALIFY rn <= 3; + +SELECT DISTINCT salary, + row_number() OVER (ORDER BY salary DESC) AS rn +FROM empsalary +QUALIFY rn <= 3; + +-- QUALIFY with FILTERS over window function +EXPLAIN(COSTS OFF) SELECT sum(salary), row_number() OVER (ORDER BY depname) as rank, sum( + sum(salary) FILTER (WHERE enroll_date > '2007-01-01') +) FILTER (WHERE depname <> 'sales') OVER (ORDER BY depname DESC) AS "filtered_sum", + depname +FROM empsalary GROUP BY depname QUALIFY rank = 1; + +SELECT sum(salary), row_number() OVER (ORDER BY depname) as rank, sum( + sum(salary) FILTER (WHERE enroll_date > '2007-01-01') +) FILTER (WHERE depname <> 'sales') OVER (ORDER BY depname DESC) AS "filtered_sum", + depname +FROM empsalary GROUP BY depname QUALIFY rank = 1; + +-- +-- Aggregate functions directly in QUALIFY +-- (allowed in a grouped query, the same as in the SELECT list / HAVING) +-- + +-- Aggregate condition over the group +EXPLAIN (COSTS OFF) +SELECT depname, sum(salary) AS total +FROM empsalary +GROUP BY depname +QUALIFY sum(salary) > 15000; + +SELECT depname, sum(salary) AS total +FROM empsalary +GROUP BY depname +QUALIFY sum(salary) > 15000 +ORDER BY depname; + +-- Aggregate and window function combined in QUALIFY +SELECT depname, sum(salary) AS total +FROM empsalary +GROUP BY depname +QUALIFY rank() OVER (ORDER BY sum(salary) DESC) = 1 +ORDER BY depname; + +-- Whole-table aggregate (single group) +SELECT sum(salary) AS s +FROM empsalary +QUALIFY sum(salary) > 1000; + +-- +-- Name resolution tests +-- + +-- An input column takes precedence over a same-named select-list alias +-- "salary" in QUALIFY is the input column (> 4000 keeps most rows), not the +-- alias (which is 0 and would keep none) +SELECT empno, (empno * 0) AS salary +FROM empsalary +QUALIFY salary > 4000 +ORDER BY empno; + +-- QUALIFY referecing columns that not on output result + +-- Same column +SELECT depname FROM empsalary QUALIFY salary > 0 ORDER BY salary; + +-- Different column +SELECT depname FROM empsalary QUALIFY salary > 0 ORDER BY enroll_date; + +-- +-- Name resolution error cases +-- + +-- A non-deterministic alias expression cannot be referenced from QUALIFY +SELECT empno, (random() < 2)::int AS r +FROM empsalary +QUALIFY r = 1; + +-- An ambiguous alias reference is rejected +SELECT empno AS x, salary AS x, + row_number() OVER (ORDER BY empno) AS rn +FROM empsalary +QUALIFY x > 0; + +-- A name that is neither an input column nor a select-list alias +SELECT empno +FROM empsalary +QUALIFY nosuchcol > row_number() OVER (ORDER BY empno); + +-- +-- QUALIFY without any window function: a general post-SELECT filter +-- + +EXPLAIN (COSTS OFF) +SELECT empno, salary, salary * 2 as new_salary +FROM empsalary +QUALIFY new_salary > 10000; + +SELECT empno, salary, salary * 2 as new_salary +FROM empsalary +QUALIFY new_salary > 10000 +ORDER BY empno; + +-- +-- QUALIFY in other query structures +-- + +-- In the arms of a set operation +SELECT empno FROM empsalary +QUALIFY row_number() OVER (ORDER BY salary) = 1 +UNION ALL +SELECT empno FROM empsalary +QUALIFY row_number() OVER (ORDER BY salary DESC) = 1 +ORDER BY empno; + +-- In a subquery that carries an outer reference +SELECT depname, + (SELECT empno FROM empsalary e2 + WHERE e2.depname = e1.depname + QUALIFY row_number() OVER (ORDER BY salary DESC) = 1) AS top_emp +FROM (SELECT DISTINCT depname FROM empsalary) e1 +ORDER BY depname; + +-- Combined with row-level locking (the lock applies to the base table) +SELECT empno FROM empsalary +QUALIFY salary > 5000 +ORDER BY empno +FOR UPDATE; + +-- +-- Cleanup +-- +DROP TABLE empsalary; -- 2.50.1 (Apple Git-155)