From 4e855b7287a3d9973e72d1f0708093ce9916bbe6 Mon Sep 17 00:00:00 2001 From: jian he Date: Wed, 2 Sep 2026 14:25:38 +0900 Subject: [PATCH] Let a row pattern DEFINE clause take part in grouping parseCheckAggregates() rewrote only qry->targetList and qry->havingQual. That is enough for the rest of a WindowClause -- partitionClause and orderClause carry a sortgroupref into the target list, and frame offsets are Var-free -- but defineClause holds an expression tree of its own, so its Vars stayed plain relation Vars while the target list copies became Vars of the RTE_GROUP RTE. Plain GROUP BY hid it: the group Var carries no varnullingrels there, and flatten_group_exprs() turns it back into the bare Var the DEFINE copy already held. Add a grouping set that can null the column and the two stop matching: SELECT category, count(*) OVER w FROM t GROUP BY ROLLUP(category) WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING PATTERN (A) DEFINE A AS category IS NOT NULL); ERROR: wrong varnullingrels (b) (expected (b 2)) for Var 1/1 ISO/IEC 19075-5 6.4 puts the row pattern input table after GROUP BY, so this is a shape the feature owes rather than one it may decline. Carry defineClause through the same three steps the target list takes: substitute_grouped_columns() in parseCheckAggregates(), flatten_group_exprs() in subquery_planner(), and flatten_group_exprs() again in get_query_def(), so a view over grouped input still deparses to text that re-parses. The first takes the join alias flattening that goes with it, or a DEFINE clause naming the merged column of a FULL JOIN keeps an alias Var that matches no grouping item. Planting needs the same widening. transformDefineClause() put what a DEFINE expression reads into the target list as bare Vars, which offers a grouped expression's columns to the grouping logic on their own and gets them reported as ungrouped. Stop the walk at a subexpression GROUP BY computes: the substitution replaces it on both sides, and the target list entry holding it bears a sortgroupref that keeps the column alive. The stop reads groupClause rather than a sortgroupref, or the window's own ORDER BY would trip it in a query that does no grouping at all. rpr_base gains a section for row patterns over grouped input, covering the grouping shapes that reach the substitution, the planting stop against each kind of grouped expression, and views over grouped input, since get_query_def() is the third of the three steps. --- src/backend/optimizer/plan/planner.c | 17 + src/backend/parser/analyze.c | 3 +- src/backend/parser/parse_agg.c | 32 ++ src/backend/parser/parse_clause.c | 5 +- src/backend/parser/parse_rpr.c | 121 +++-- src/backend/utils/adt/ruleutils.c | 16 + src/include/parser/parse_clause.h | 3 +- src/include/parser/parse_rpr.h | 3 +- src/test/regress/expected/rpr_base.out | 724 +++++++++++++++++++++++++ src/test/regress/sql/rpr_base.sql | 470 ++++++++++++++++ src/tools/pgindent/typedefs.list | 1 + 11 files changed, 1359 insertions(+), 36 deletions(-) diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c index 4199b657afc..825a4ef1e3e 100644 --- a/src/backend/optimizer/plan/planner.c +++ b/src/backend/optimizer/plan/planner.c @@ -1256,6 +1256,23 @@ subquery_planner(PlannerGlobal *glob, Query *parse, char *plan_name, flatten_group_exprs(root, root->parse, (Node *) parse->targetList); parse->havingQual = flatten_group_exprs(root, root->parse, parse->havingQual); + + /* + * A row pattern DEFINE clause holds an expression tree of its own, so + * parseCheckAggregates() put GROUP Vars into it as well. Expand them + * here too, and with the root, so that the varnullingrels a grouping + * set attached survive onto the replacement -- setrefs.c matches the + * DEFINE copy against the target list copy and insists they agree. + */ + foreach(l, parse->windowClause) + { + WindowClause *wc = lfirst_node(WindowClause, l); + + if (wc->defineClause != NIL) + wc->defineClause = (List *) + flatten_group_exprs(root, root->parse, + (Node *) wc->defineClause); + } } /* Constant-folding might have removed all set-returning functions */ diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c index 263d1b6e1cc..c71e45a094f 100644 --- a/src/backend/parser/analyze.c +++ b/src/backend/parser/analyze.c @@ -1853,7 +1853,8 @@ transformSelectStmt(ParseState *pstate, SelectStmt *stmt, /* transform window clauses after we have seen all window functions */ qry->windowClause = transformWindowDefinitions(pstate, pstate->p_windowdefs, - &qry->targetList); + &qry->targetList, + qry->groupClause); /* resolve any still-unresolved output columns as being type text */ if (pstate->p_resolve_unknowns) diff --git a/src/backend/parser/parse_agg.c b/src/backend/parser/parse_agg.c index 34797048253..e607ee562da 100644 --- a/src/backend/parser/parse_agg.c +++ b/src/backend/parser/parse_agg.c @@ -1351,6 +1351,38 @@ parseCheckAggregates(ParseState *pstate, Query *qry) have_non_var_grouping, &func_grouped_rels); + /* + * A row pattern DEFINE clause is the one part of a WindowClause holding + * an expression tree of its own, so it needs the substitution too: + * partitionClause and orderClause carry just a sortgroupref into the + * target list, and the frame offsets are checked to be Var-free. Without + * this its Vars would stay plain relation Vars while the target list + * copies of the same columns become Vars of the RTE_GROUP RTE, and a + * grouping set that nulls one of those columns would make the two copies + * disagree in varnullingrels, which setrefs.c reports as an internal + * error. + * + * No finalize_grouping_exprs() goes with it. That call finalizes + * GROUPING expressions, and a DEFINE clause cannot hold one -- + * transformExpr() rejects a GroupingFunc under EXPR_KIND_RPR_DEFINE + * before we get here. + */ + foreach_node(WindowClause, wc, qry->windowClause) + { + if (wc->defineClause == NIL) + continue; + + clause = (Node *) wc->defineClause; + if (hasJoinRTEs) + clause = flatten_join_alias_for_parser(qry, clause, 0); + wc->defineClause = (List *) + substitute_grouped_columns(clause, pstate, qry, + groupClauses, groupClauseCommonVars, + gset_common, + have_non_var_grouping, + &func_grouped_rels); + } + /* * Per spec, aggregates can't appear in a recursive term. */ diff --git a/src/backend/parser/parse_clause.c b/src/backend/parser/parse_clause.c index 6b02cb531a1..bc15315ebef 100644 --- a/src/backend/parser/parse_clause.c +++ b/src/backend/parser/parse_clause.c @@ -2942,7 +2942,8 @@ transformSortClause(ParseState *pstate, List * transformWindowDefinitions(ParseState *pstate, List *windowdefs, - List **targetlist) + List **targetlist, + List *groupClause) { List *result = NIL; Index winref = 0; @@ -3138,7 +3139,7 @@ transformWindowDefinitions(ParseState *pstate, windef->endOffset); /* Process Row Pattern Recognition related clauses */ - transformRPR(pstate, wc, windef, targetlist); + transformRPR(pstate, wc, windef, targetlist, groupClause); wc->winref = winref; diff --git a/src/backend/parser/parse_rpr.c b/src/backend/parser/parse_rpr.c index e3aa8598f58..0873875c24a 100644 --- a/src/backend/parser/parse_rpr.c +++ b/src/backend/parser/parse_rpr.c @@ -52,11 +52,20 @@ typedef struct RPRNavKind inner_kind; /* kind of first nested nav in current arg */ } DefineWalkCtx; +/* Target list planting walker context -- see define_plant_walker. */ +typedef struct +{ + ParseState *pstate; + List **targetlist; + List *groupExprs; /* expressions GROUP BY computes */ +} DefinePlantCtx; + /* Forward declarations */ static void validateRPRPatternVarCount(ParseState *pstate, RPRPatternNode *node, List **varNames); static List *transformDefineClause(ParseState *pstate, WindowDef *windef, - List **targetlist); + List **targetlist, List *groupClause); +static bool define_plant_walker(Node *node, void *context); static bool define_walker(Node *node, void *context); static bool rpr_frame_is_supported(int frameOptions); @@ -75,7 +84,7 @@ static bool rpr_frame_is_supported(int frameOptions); */ void transformRPR(ParseState *pstate, WindowClause *wc, WindowDef *windef, - List **targetlist) + List **targetlist, List *groupClause) { /* Window definition must exist when called */ Assert(windef != NULL); @@ -119,7 +128,8 @@ transformRPR(ParseState *pstate, WindowClause *wc, WindowDef *windef, wc->rpSkipTo = windef->rpCommonSyntax->rpSkipTo; /* Transform DEFINE clause into list of TargetEntry's */ - wc->defineClause = transformDefineClause(pstate, windef, targetlist); + wc->defineClause = transformDefineClause(pstate, windef, targetlist, + groupClause); /* Store PATTERN parse tree for deparsing */ wc->rpPattern = windef->rpCommonSyntax->rpPattern; @@ -238,10 +248,23 @@ validateRPRPatternVarCount(ParseState *pstate, RPRPatternNode *node, */ static List * transformDefineClause(ParseState *pstate, WindowDef *windef, - List **targetlist) + List **targetlist, List *groupClause) { List *defineClause = NIL; List *patternVarNames = NIL; + List *groupExprs = NIL; + + /* + * Collect what GROUP BY computes, so that the planting below can stop at + * one. Taken before any planting, since the entries planted are not + * grouping columns and carry no sortgroupref. + */ + foreach_node(SortGroupClause, sgc, groupClause) + { + TargetEntry *tle = get_sortgroupclause_tle(sgc, *targetlist); + + groupExprs = lappend(groupExprs, tle->expr); + } /* * The grammar builds an RPCommonSyntax only for a window specification @@ -307,14 +330,14 @@ transformDefineClause(ParseState *pstate, WindowDef *windef, { TargetEntry *teDefine; Node *expr; - List *vars; + DefinePlantCtx ctx; /* * Transform the DEFINE expression and coerce it to boolean. We must * NOT add the whole expression to the query targetlist, because it * may contain RPRNavExpr nodes (PREV/NEXT/FIRST/LAST) that can only * be evaluated inside the owning WindowAgg. Coercing here, before - * pull_var_clause, keeps pull_var_clause operating on the final + * define_plant_walker() runs below, keeps that walk on the final * expression form and surfaces a type mismatch before the targetlist * is touched. */ @@ -336,32 +359,23 @@ transformDefineClause(ParseState *pstate, WindowDef *windef, * one is present in the targetlist. This is needed so the planner * propagates the referenced columns through the plan tree, making * them available to the WindowAgg's DEFINE evaluation. + * + * The walk stops at a subexpression GROUP BY computes and plants + * nothing for it. parseCheckAggregates() replaces such a + * subexpression with the grouping step's Var on both sides -- here + * and in the target list entry holding the same expression -- so the + * two copies still meet, and that entry bears a sortgroupref, which + * is enough to keep its column alive. Planting the columns + * underneath it instead would offer them to the grouping logic on + * their own, which does not make them available that way and reports + * them as ungrouped. The stop reads groupClause rather than a + * sortgroupref, or the window's own ORDER BY would trip it in a query + * that does no grouping at all. */ - vars = pull_var_clause(expr, 0); - foreach_node(Var, var, vars) - { - bool found = false; - - foreach_node(TargetEntry, tle, *targetlist) - { - if (equal(tle->expr, var)) - { - found = true; - break; - } - } - if (!found) - { - TargetEntry *newtle; - - newtle = makeTargetEntry((Expr *) copyObject(var), - (AttrNumber) pstate->p_next_resno++, - NULL, - true); - *targetlist = lappend(*targetlist, newtle); - } - } - list_free(vars); + ctx.pstate = pstate; + ctx.targetlist = targetlist; + ctx.groupExprs = groupExprs; + (void) define_plant_walker(expr, &ctx); } pstate->p_rpr_pattern_vars = NIL; @@ -390,6 +404,51 @@ transformDefineClause(ParseState *pstate, WindowDef *windef, return defineClause; } +/* + * define_plant_walker + * Plant in the target list what a DEFINE expression reads. + * + * Vars are planted one at a time as resjunk entries, except under a + * subexpression GROUP BY computes, where the walk stops and plants nothing -- + * see the planting comment in transformDefineClause() for why. + */ +static bool +define_plant_walker(Node *node, void *context) +{ + DefinePlantCtx *ctx = (DefinePlantCtx *) context; + + if (node == NULL) + return false; + + /* A subexpression GROUP BY computes needs nothing planted for it. */ + foreach_ptr(Node, gexpr, ctx->groupExprs) + { + if (equal(node, gexpr)) + return false; + } + + if (IsA(node, Var)) + { + Var *var = (Var *) node; + + foreach_node(TargetEntry, tle, *ctx->targetlist) + { + if (equal(tle->expr, var)) + return false; + } + + *ctx->targetlist = + lappend(*ctx->targetlist, + makeTargetEntry((Expr *) copyObject(var), + (AttrNumber) ctx->pstate->p_next_resno++, + NULL, + true)); + return false; + } + + return expression_tree_walker(node, define_plant_walker, ctx); +} + /* * define_walker * Single-pass DEFINE clause validator. At each node, enforces: diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index 9da1b433e0b..c34243df975 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -6166,10 +6166,26 @@ get_query_def(Query *query, StringInfo buf, List *parentnamespace, */ if (query->hasGroupRTE) { + ListCell *lc; + query->targetList = (List *) flatten_group_exprs(NULL, query, (Node *) query->targetList); query->havingQual = flatten_group_exprs(NULL, query, query->havingQual); + + /* + * A row pattern DEFINE clause carries GROUP Vars of its own; expand + * them, or the deparsed text would name the grouping step rather than + * the expression the user wrote, and the view would not re-parse. + */ + foreach(lc, query->windowClause) + { + WindowClause *wc = lfirst_node(WindowClause, lc); + + if (wc->defineClause != NIL) + wc->defineClause = (List *) + flatten_group_exprs(NULL, query, (Node *) wc->defineClause); + } } /* diff --git a/src/include/parser/parse_clause.h b/src/include/parser/parse_clause.h index ca815a9d1bb..d8efc3a79ce 100644 --- a/src/include/parser/parse_clause.h +++ b/src/include/parser/parse_clause.h @@ -35,7 +35,8 @@ extern List *transformSortClause(ParseState *pstate, List *orderlist, extern List *transformWindowDefinitions(ParseState *pstate, List *windowdefs, - List **targetlist); + List **targetlist, + List *groupClause); extern List *transformDistinctClause(ParseState *pstate, List **targetlist, List *sortClause, bool is_agg); diff --git a/src/include/parser/parse_rpr.h b/src/include/parser/parse_rpr.h index 7fab6f292aa..958bc229956 100644 --- a/src/include/parser/parse_rpr.h +++ b/src/include/parser/parse_rpr.h @@ -17,6 +17,7 @@ #include "parser/parse_node.h" extern void transformRPR(ParseState *pstate, WindowClause *wc, - WindowDef *windef, List **targetlist); + WindowDef *windef, List **targetlist, + List *groupClause); #endif /* PARSE_RPR_H */ diff --git a/src/test/regress/expected/rpr_base.out b/src/test/regress/expected/rpr_base.out index b8d66fad729..179817e9952 100644 --- a/src/test/regress/expected/rpr_base.out +++ b/src/test/regress/expected/rpr_base.out @@ -8498,6 +8498,730 @@ LIMIT 3 OFFSET 1; 4 | B | 40 | 0 (3 rows) +-- ------------------------------------------------------------ +-- RPR over grouped input +-- ------------------------------------------------------------ +-- A DEFINE clause is the only part of a WindowClause that holds an +-- expression tree of its own, so parseCheckAggregates() has to substitute +-- its grouped columns separately from the target list's. These pin the +-- grouping shapes that reach that substitution, and what each returns once +-- a grouping set nulls a column the pattern reads. +CREATE TABLE rpr_grp (id int PRIMARY KEY, category text, val int); +INSERT INTO rpr_grp VALUES (1, 'A', 10), (2, 'B', 20); +-- Grouped input works; the pattern matches over the grouped rows +SELECT category, sum(val) AS total, count(*) OVER w AS cnt +FROM rpr_sort +GROUP BY category +WINDOW w AS ( + ORDER BY category + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS category IS NOT NULL) +ORDER BY category; + category | total | cnt +----------+-------+----- + A | 90 | 2 + B | 120 | 0 +(2 rows) + +-- Navigation over a grouping column +SELECT category, count(*) OVER w AS cnt +FROM rpr_sort +GROUP BY category +WINDOW w AS ( + ORDER BY category + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B*) + DEFINE B AS category > PREV(category)) +ORDER BY category; + category | cnt +----------+----- + A | 2 + B | 0 +(2 rows) + +-- GROUP BY () builds no RTE_GROUP, so there is no grouped column for a +-- DEFINE clause to name and nothing that could diverge +SELECT count(*) OVER w AS cnt +FROM rpr_sort +GROUP BY () +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A) + DEFINE A AS true); + cnt +----- + 1 +(1 row) + +-- A single grouping set collapses to a plain GROUP BY and cannot null the +-- column +SELECT category, count(*) OVER w AS cnt +FROM rpr_sort +GROUP BY GROUPING SETS ((category)) +WINDOW w AS ( + ORDER BY category + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A) + DEFINE A AS category IS NOT NULL) +ORDER BY category; + category | cnt +----------+----- + A | 1 + B | 1 +(2 rows) + +-- Duplicated sets leave the column in every set, so it is never nulled +SELECT category, count(*) OVER w AS cnt +FROM rpr_sort +GROUP BY GROUPING SETS ((category), (category)) +WINDOW w AS ( + ORDER BY category + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A) + DEFINE A AS category IS NOT NULL) +ORDER BY category; + category | cnt +----------+----- + A | 1 + A | 1 + B | 1 + B | 1 +(4 rows) + +-- ROLLUP with a DEFINE clause that holds no column reference at all +SELECT category, count(*) OVER w AS cnt +FROM rpr_sort +GROUP BY ROLLUP(category) +WINDOW w AS ( + ORDER BY category + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A) + DEFINE A AS true) +ORDER BY category NULLS LAST; + category | cnt +----------+----- + A | 1 + B | 1 + | 1 +(3 rows) + +-- The DEFINE clause names only a column that every grouping set contains, +-- so gset_common covers it and no varnullingrels are attached +SELECT category, val, count(*) OVER w AS cnt +FROM rpr_sort +WHERE val < 30 +GROUP BY category, ROLLUP(val) +WINDOW w AS ( + ORDER BY category, val + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A) + DEFINE A AS category IS NOT NULL) +ORDER BY category, val NULLS LAST; + category | val | cnt +----------+-----+----- + A | 10 | 1 + A | | 1 + B | 20 | 1 + B | | 1 +(4 rows) + +-- The window's own PARTITION BY and ORDER BY reference the target list, so +-- a nullable grouping column reaches them unharmed +SELECT category, count(*) OVER w AS cnt +FROM rpr_sort +GROUP BY ROLLUP(category) +WINDOW w AS ( + PARTITION BY category + ORDER BY category + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A) + DEFINE A AS true) +ORDER BY category NULLS LAST; + category | cnt +----------+----- + A | 1 + B | 1 + | 1 +(3 rows) + +-- An aggregate query without GROUP BY produces one grouped row +SELECT count(*) OVER w AS cnt +FROM rpr_sort +HAVING count(*) > 0 +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS true); + cnt +----- + 1 +(1 row) + +SELECT count(*) OVER w AS cnt, sum(val) AS total +FROM rpr_sort +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS true); + cnt | total +-----+------- + 1 | 210 +(1 row) + +-- GROUP BY together with HAVING +SELECT category, count(*) OVER w AS cnt +FROM rpr_sort +GROUP BY category +HAVING count(*) > 1 +WINDOW w AS ( + ORDER BY category + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A) + DEFINE A AS category IS NOT NULL) +ORDER BY category; + category | cnt +----------+----- + A | 1 + B | 1 +(2 rows) + +-- A grouping key that is not a plain Var does not stand in the way of a +-- DEFINE clause that names a plain-Var grouping key +SELECT category, val + 1 AS bumped, count(*) OVER w AS cnt +FROM rpr_grp +GROUP BY val + 1, category +WINDOW w AS ( + ORDER BY category + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A) + DEFINE A AS category IS NOT NULL) +ORDER BY category; + category | bumped | cnt +----------+--------+----- + A | 11 | 1 + B | 21 | 1 +(2 rows) + +-- Grouping by the primary key exposes the dependent columns +SELECT id, count(*) OVER w AS cnt +FROM rpr_grp +GROUP BY id +WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A) + DEFINE A AS val > 0) +ORDER BY id; + id | cnt +----+----- + 1 | 1 + 2 | 1 +(2 rows) + +-- An aggregate is available to the window's ORDER BY, and it is the ordering +-- the pattern runs over: sum(val) descending puts B first, so the greedy +-- match starts there. Ordering by category instead would start at A. +SELECT category, count(*) OVER w AS cnt +FROM rpr_sort +GROUP BY category +WINDOW w AS ( + ORDER BY sum(val) DESC + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS category IS NOT NULL) +ORDER BY category; + category | cnt +----------+----- + A | 0 + B | 2 +(2 rows) + +-- Grouping in a subquery leaves the outer RPR window alone +SELECT category, total, count(*) OVER w AS cnt +FROM (SELECT category, sum(val) AS total FROM rpr_sort GROUP BY category) s +WINDOW w AS ( + ORDER BY category + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B*) + DEFINE B AS total > PREV(total)) +ORDER BY category; + category | total | cnt +----------+-------+----- + A | 90 | 2 + B | 120 | 0 +(2 rows) + +-- A view over grouped input round-trips +CREATE VIEW rpr_grp_v AS +SELECT category, count(*) OVER w AS cnt +FROM rpr_sort +GROUP BY category +WINDOW w AS ( + ORDER BY category + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS category IS NOT NULL); +SELECT pg_get_viewdef('rpr_grp_v'::regclass, true); + pg_get_viewdef +----------------------------------------------------------------------------------- + SELECT category, + + count(*) OVER w AS cnt + + FROM rpr_sort + + GROUP BY category + + WINDOW w AS (ORDER BY category ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ + AFTER MATCH SKIP PAST LAST ROW + + INITIAL + + PATTERN (a+) + + DEFINE + + a AS category IS NOT NULL); +(1 row) + +SELECT * FROM rpr_grp_v ORDER BY category; + category | cnt +----------+----- + A | 2 + B | 0 +(2 rows) + +DROP VIEW rpr_grp_v; +-- ROLLUP, with a DEFINE clause naming a column it can null. The grouping +-- step's NULL reaches the predicate, which is then unknown, so the total row +-- is unmatched. +SELECT category, count(*) OVER w AS cnt +FROM rpr_sort +GROUP BY ROLLUP(category) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A) + DEFINE A AS category IS NOT NULL); + category | cnt +----------+----- + | 0 + B | 1 + A | 1 +(3 rows) + +-- A match that spans several grouped rows, so the reduced frame is not just +-- the current row: A+ is greedy and stops at the row ROLLUP nulled. +SELECT category, count(*) OVER w AS cnt +FROM rpr_sort +GROUP BY ROLLUP(category) +WINDOW w AS ( + ORDER BY category + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS category >= 'A') +ORDER BY category NULLS LAST; + category | cnt +----------+----- + A | 2 + B | 0 + | 0 +(3 rows) + +-- The same for CUBE +SELECT category, count(*) OVER w AS cnt +FROM rpr_sort +GROUP BY CUBE(category) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A) + DEFINE A AS category IS NOT NULL); + category | cnt +----------+----- + | 0 + B | 1 + A | 1 +(3 rows) + +-- The same for an explicit set list containing the empty set +SELECT category, count(*) OVER w AS cnt +FROM rpr_sort +GROUP BY GROUPING SETS ((category), ()) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A) + DEFINE A AS category IS NOT NULL); + category | cnt +----------+----- + | 0 + B | 1 + A | 1 +(3 rows) + +-- The same when a second set simply omits the column +SELECT category, count(*) OVER w AS cnt +FROM rpr_sort +GROUP BY GROUPING SETS ((category), (val)) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A) + DEFINE A AS category IS NOT NULL); + category | cnt +----------+----- + B | 1 + A | 1 + | 0 + | 0 + | 0 + | 0 + | 0 + | 0 +(8 rows) + +-- Naming val, which ROLLUP can null, works the same way as naming category +-- above: the rows where it is nulled do not match +SELECT category, val, count(*) OVER w AS cnt +FROM rpr_sort +GROUP BY category, ROLLUP(val) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A) + DEFINE A AS val > 0); + category | val | cnt +----------+-----+----- + B | 40 | 1 + A | 10 | 1 + B | 60 | 1 + A | 30 | 1 + B | 20 | 1 + A | 50 | 1 + B | | 0 + A | | 0 +(8 rows) + +-- A navigation in the DEFINE clause reads the grouped column the same way +SELECT category, count(*) OVER w AS cnt +FROM rpr_sort +GROUP BY ROLLUP(category) +WINDOW w AS ( + ORDER BY category + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B*) + DEFINE B AS PREV(category) IS NOT NULL); + category | cnt +----------+----- + A | 3 + B | 0 + | 0 +(3 rows) + +-- The same for a compound navigation +SELECT category, count(*) OVER w AS cnt +FROM rpr_sort +GROUP BY ROLLUP(category) +WINDOW w AS ( + ORDER BY category + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B*) + DEFINE B AS PREV(LAST(category)) IS NOT NULL); + category | cnt +----------+----- + A | 3 + B | 0 + | 0 +(3 rows) + +-- The same one query level down +SELECT * FROM ( + SELECT category, count(*) OVER w AS cnt + FROM rpr_sort + GROUP BY ROLLUP(category) + WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A) + DEFINE A AS category IS NOT NULL)) s; + category | cnt +----------+----- + | 0 + B | 1 + A | 1 +(3 rows) + +-- And in a view definition. get_query_def() expands the DEFINE clause's +-- GROUP Vars like the target list's, so the deparsed text names the column +-- rather than the grouping step and the view re-parses. +CREATE VIEW rpr_grp_v2 AS +SELECT category, count(*) OVER w AS cnt +FROM rpr_sort +GROUP BY ROLLUP(category) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A) + DEFINE A AS category IS NOT NULL); +SELECT pg_get_viewdef('rpr_grp_v2'::regclass, true); + pg_get_viewdef +----------------------------------------------------------------- + SELECT category, + + count(*) OVER w AS cnt + + FROM rpr_sort + + GROUP BY ROLLUP(category) + + WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ + AFTER MATCH SKIP PAST LAST ROW + + INITIAL + + PATTERN (a) + + DEFINE + + a AS category IS NOT NULL); +(1 row) + +SELECT * FROM rpr_grp_v2 ORDER BY category NULLS LAST; + category | cnt +----------+----- + A | 1 + B | 1 + | 0 +(3 rows) + +DROP VIEW rpr_grp_v2; +-- A DEFINE clause may spell a GROUP BY expression. Planting stops at one +-- rather than offering the columns underneath it to the grouping logic on +-- their own, which is not how grouping makes them available; the target list +-- entry holding the same expression is what both copies end up naming. +SELECT val + 1 AS bumped, count(*) OVER w AS cnt +FROM rpr_grp +GROUP BY val + 1 +WINDOW w AS ( + ORDER BY val + 1 + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A) + DEFINE A AS val + 1 > 0) +ORDER BY bumped; + bumped | cnt +--------+----- + 11 | 1 + 21 | 1 +(2 rows) + +-- The same for a function call +SELECT upper(category) AS u, count(*) OVER w AS cnt +FROM rpr_grp +GROUP BY upper(category) +WINDOW w AS ( + ORDER BY upper(category) + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A) + DEFINE A AS upper(category) = 'A') +ORDER BY u; + u | cnt +---+----- + A | 1 + B | 0 +(2 rows) + +-- The same for a cast +SELECT val::text AS t, count(*) OVER w AS cnt +FROM rpr_grp +GROUP BY val::text +WINDOW w AS ( + ORDER BY val::text + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A) + DEFINE A AS val::text > '0') +ORDER BY t; + t | cnt +----+----- + 10 | 1 + 20 | 1 +(2 rows) + +-- And through a navigation operation, whose argument is read the same way +SELECT val + 1 AS bumped, count(*) OVER w AS cnt +FROM rpr_grp +GROUP BY val + 1 +WINDOW w AS ( + ORDER BY val + 1 + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B*) + DEFINE B AS PREV(val + 1) > 0) +ORDER BY bumped; + bumped | cnt +--------+----- + 11 | 2 + 21 | 0 +(2 rows) + +-- The same under a grouping set, where the row the set nulls leaves the +-- predicate unknown and so unmatched. +SELECT val + 1 AS bumped, count(*) OVER w AS cnt +FROM rpr_grp +GROUP BY ROLLUP(val + 1) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A) + DEFINE A AS val + 1 > 0); + bumped | cnt +--------+----- + | 0 + 11 | 1 + 21 | 1 +(3 rows) + +-- A DEFINE clause may repeat an expression the window itself orders by, with +-- no grouping in sight. Planting bare Vars is what makes this hold: the +-- DEFINE copy of ROW(val, 1) IS NOT NULL is broken into per field tests before +-- the plan is built, and the bare val the break leaves behind is already in +-- the input. +SELECT id, count(*) OVER w AS cnt +FROM rpr_grp +WINDOW w AS ( + ORDER BY ROW(val, 1) + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS ROW(val, 1) IS NOT NULL) +ORDER BY id; + id | cnt +----+----- + 1 | 2 + 2 | 0 +(2 rows) + +-- ERROR: the bare column is another matter; grouping by an expression does +-- not make the columns inside it available on their own +SELECT val + 1 AS bumped, count(*) OVER w AS cnt +FROM rpr_grp +GROUP BY val + 1 +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A) + DEFINE A AS val > 0); +ERROR: column "rpr_grp.val" must appear in the GROUP BY clause or be used in an aggregate function +LINE 7: DEFINE A AS val > 0); + ^ +-- ERROR: a column that was never grouped is still reported as one +SELECT category, count(*) OVER w AS cnt +FROM rpr_grp +GROUP BY category +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A) + DEFINE A AS val > 0); +ERROR: column "rpr_grp.val" must appear in the GROUP BY clause or be used in an aggregate function +LINE 7: DEFINE A AS val > 0); + ^ +-- An inline OVER (...) carries a window clause of its own, and the +-- substitution reaches it the same way +SELECT category, + count(*) OVER (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A) + DEFINE A AS category IS NOT NULL) AS cnt +FROM rpr_sort +GROUP BY ROLLUP(category); + category | cnt +----------+----- + | 0 + B | 1 + A | 1 +(3 rows) + +-- The substitution visits every window clause, not just the first. Here +-- the first window is a plain one and the row pattern is on the second. +SELECT category, count(*) OVER w1 AS plain, count(*) OVER w2 AS rpr +FROM rpr_sort +GROUP BY ROLLUP(category) +WINDOW w1 AS (ORDER BY category), + w2 AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A) + DEFINE A AS category IS NOT NULL); + category | plain | rpr +----------+-------+----- + A | 1 | 1 + B | 2 | 1 + | 3 | 0 +(3 rows) + +-- An unreferenced window is substituted like any other +SELECT category +FROM rpr_sort +GROUP BY ROLLUP(category) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A) + DEFINE A AS category IS NOT NULL); + category +---------- + + B + A +(3 rows) + +-- A join turns the DEFINE clause's Vars into join alias Vars. Plain +-- grouping still resolves them, so the column USING merges reaches the +-- pattern unharmed. +SELECT id, count(*) OVER w AS cnt +FROM rpr_grp JOIN rpr_sort USING (id) +GROUP BY id +WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS id > 0) +ORDER BY id; + id | cnt +----+----- + 1 | 2 + 2 | 0 +(2 rows) + +-- The same query through the join, with a grouping set that can null the +-- column the pattern reads +SELECT id, count(*) OVER w AS cnt +FROM rpr_grp JOIN rpr_sort USING (id) +GROUP BY ROLLUP(id) +WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS id > 0) +ORDER BY id; + id | cnt +----+----- + 1 | 2 + 2 | 0 + | 0 +(3 rows) + +-- A FULL JOIN's USING column is a merged column -- a COALESCE over the two +-- sides rather than either one -- so a DEFINE clause naming it holds a join +-- alias Var, and only flatten_join_alias_for_parser() turns that into +-- something the grouped target list can be matched against. The inner joins +-- above reach the pattern without that step. +SELECT id, count(*) OVER w AS cnt +FROM rpr_grp FULL JOIN rpr_sort USING (id) +GROUP BY ROLLUP(id) +WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS id > 0) +ORDER BY id NULLS LAST; + id | cnt +----+----- + 1 | 6 + 2 | 0 + 3 | 0 + 4 | 0 + 5 | 0 + 6 | 0 + | 0 +(7 rows) + +-- A grouping set list holding only the empty set builds no RTE_GROUP +-- either, just like GROUP BY () +SELECT count(*) OVER w AS cnt +FROM rpr_grp +GROUP BY GROUPING SETS (()) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A) + DEFINE A AS true); + cnt +----- + 1 +(1 row) + +DROP TABLE rpr_grp; DROP TABLE rpr_sort; -- SQL function inlining: $1 in DEFINE must be substituted by -- substitute_actual_parameters_in_from via query_tree_mutator. diff --git a/src/test/regress/sql/rpr_base.sql b/src/test/regress/sql/rpr_base.sql index 807577bfe52..54bfee5534f 100644 --- a/src/test/regress/sql/rpr_base.sql +++ b/src/test/regress/sql/rpr_base.sql @@ -5137,6 +5137,476 @@ WINDOW w AS ( ORDER BY id LIMIT 3 OFFSET 1; +-- ------------------------------------------------------------ +-- RPR over grouped input +-- ------------------------------------------------------------ +-- A DEFINE clause is the only part of a WindowClause that holds an +-- expression tree of its own, so parseCheckAggregates() has to substitute +-- its grouped columns separately from the target list's. These pin the +-- grouping shapes that reach that substitution, and what each returns once +-- a grouping set nulls a column the pattern reads. + +CREATE TABLE rpr_grp (id int PRIMARY KEY, category text, val int); +INSERT INTO rpr_grp VALUES (1, 'A', 10), (2, 'B', 20); + +-- Grouped input works; the pattern matches over the grouped rows +SELECT category, sum(val) AS total, count(*) OVER w AS cnt +FROM rpr_sort +GROUP BY category +WINDOW w AS ( + ORDER BY category + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS category IS NOT NULL) +ORDER BY category; + +-- Navigation over a grouping column +SELECT category, count(*) OVER w AS cnt +FROM rpr_sort +GROUP BY category +WINDOW w AS ( + ORDER BY category + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B*) + DEFINE B AS category > PREV(category)) +ORDER BY category; + +-- GROUP BY () builds no RTE_GROUP, so there is no grouped column for a +-- DEFINE clause to name and nothing that could diverge +SELECT count(*) OVER w AS cnt +FROM rpr_sort +GROUP BY () +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A) + DEFINE A AS true); + +-- A single grouping set collapses to a plain GROUP BY and cannot null the +-- column +SELECT category, count(*) OVER w AS cnt +FROM rpr_sort +GROUP BY GROUPING SETS ((category)) +WINDOW w AS ( + ORDER BY category + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A) + DEFINE A AS category IS NOT NULL) +ORDER BY category; + +-- Duplicated sets leave the column in every set, so it is never nulled +SELECT category, count(*) OVER w AS cnt +FROM rpr_sort +GROUP BY GROUPING SETS ((category), (category)) +WINDOW w AS ( + ORDER BY category + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A) + DEFINE A AS category IS NOT NULL) +ORDER BY category; + +-- ROLLUP with a DEFINE clause that holds no column reference at all +SELECT category, count(*) OVER w AS cnt +FROM rpr_sort +GROUP BY ROLLUP(category) +WINDOW w AS ( + ORDER BY category + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A) + DEFINE A AS true) +ORDER BY category NULLS LAST; + +-- The DEFINE clause names only a column that every grouping set contains, +-- so gset_common covers it and no varnullingrels are attached +SELECT category, val, count(*) OVER w AS cnt +FROM rpr_sort +WHERE val < 30 +GROUP BY category, ROLLUP(val) +WINDOW w AS ( + ORDER BY category, val + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A) + DEFINE A AS category IS NOT NULL) +ORDER BY category, val NULLS LAST; + +-- The window's own PARTITION BY and ORDER BY reference the target list, so +-- a nullable grouping column reaches them unharmed +SELECT category, count(*) OVER w AS cnt +FROM rpr_sort +GROUP BY ROLLUP(category) +WINDOW w AS ( + PARTITION BY category + ORDER BY category + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A) + DEFINE A AS true) +ORDER BY category NULLS LAST; + +-- An aggregate query without GROUP BY produces one grouped row +SELECT count(*) OVER w AS cnt +FROM rpr_sort +HAVING count(*) > 0 +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS true); + +SELECT count(*) OVER w AS cnt, sum(val) AS total +FROM rpr_sort +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS true); + +-- GROUP BY together with HAVING +SELECT category, count(*) OVER w AS cnt +FROM rpr_sort +GROUP BY category +HAVING count(*) > 1 +WINDOW w AS ( + ORDER BY category + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A) + DEFINE A AS category IS NOT NULL) +ORDER BY category; + +-- A grouping key that is not a plain Var does not stand in the way of a +-- DEFINE clause that names a plain-Var grouping key +SELECT category, val + 1 AS bumped, count(*) OVER w AS cnt +FROM rpr_grp +GROUP BY val + 1, category +WINDOW w AS ( + ORDER BY category + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A) + DEFINE A AS category IS NOT NULL) +ORDER BY category; + +-- Grouping by the primary key exposes the dependent columns +SELECT id, count(*) OVER w AS cnt +FROM rpr_grp +GROUP BY id +WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A) + DEFINE A AS val > 0) +ORDER BY id; + +-- An aggregate is available to the window's ORDER BY, and it is the ordering +-- the pattern runs over: sum(val) descending puts B first, so the greedy +-- match starts there. Ordering by category instead would start at A. +SELECT category, count(*) OVER w AS cnt +FROM rpr_sort +GROUP BY category +WINDOW w AS ( + ORDER BY sum(val) DESC + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS category IS NOT NULL) +ORDER BY category; + +-- Grouping in a subquery leaves the outer RPR window alone +SELECT category, total, count(*) OVER w AS cnt +FROM (SELECT category, sum(val) AS total FROM rpr_sort GROUP BY category) s +WINDOW w AS ( + ORDER BY category + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B*) + DEFINE B AS total > PREV(total)) +ORDER BY category; + +-- A view over grouped input round-trips +CREATE VIEW rpr_grp_v AS +SELECT category, count(*) OVER w AS cnt +FROM rpr_sort +GROUP BY category +WINDOW w AS ( + ORDER BY category + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS category IS NOT NULL); + +SELECT pg_get_viewdef('rpr_grp_v'::regclass, true); +SELECT * FROM rpr_grp_v ORDER BY category; +DROP VIEW rpr_grp_v; + +-- ROLLUP, with a DEFINE clause naming a column it can null. The grouping +-- step's NULL reaches the predicate, which is then unknown, so the total row +-- is unmatched. +SELECT category, count(*) OVER w AS cnt +FROM rpr_sort +GROUP BY ROLLUP(category) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A) + DEFINE A AS category IS NOT NULL); + +-- A match that spans several grouped rows, so the reduced frame is not just +-- the current row: A+ is greedy and stops at the row ROLLUP nulled. +SELECT category, count(*) OVER w AS cnt +FROM rpr_sort +GROUP BY ROLLUP(category) +WINDOW w AS ( + ORDER BY category + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS category >= 'A') +ORDER BY category NULLS LAST; + +-- The same for CUBE +SELECT category, count(*) OVER w AS cnt +FROM rpr_sort +GROUP BY CUBE(category) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A) + DEFINE A AS category IS NOT NULL); + +-- The same for an explicit set list containing the empty set +SELECT category, count(*) OVER w AS cnt +FROM rpr_sort +GROUP BY GROUPING SETS ((category), ()) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A) + DEFINE A AS category IS NOT NULL); + +-- The same when a second set simply omits the column +SELECT category, count(*) OVER w AS cnt +FROM rpr_sort +GROUP BY GROUPING SETS ((category), (val)) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A) + DEFINE A AS category IS NOT NULL); + +-- Naming val, which ROLLUP can null, works the same way as naming category +-- above: the rows where it is nulled do not match +SELECT category, val, count(*) OVER w AS cnt +FROM rpr_sort +GROUP BY category, ROLLUP(val) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A) + DEFINE A AS val > 0); + +-- A navigation in the DEFINE clause reads the grouped column the same way +SELECT category, count(*) OVER w AS cnt +FROM rpr_sort +GROUP BY ROLLUP(category) +WINDOW w AS ( + ORDER BY category + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B*) + DEFINE B AS PREV(category) IS NOT NULL); + +-- The same for a compound navigation +SELECT category, count(*) OVER w AS cnt +FROM rpr_sort +GROUP BY ROLLUP(category) +WINDOW w AS ( + ORDER BY category + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B*) + DEFINE B AS PREV(LAST(category)) IS NOT NULL); + +-- The same one query level down +SELECT * FROM ( + SELECT category, count(*) OVER w AS cnt + FROM rpr_sort + GROUP BY ROLLUP(category) + WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A) + DEFINE A AS category IS NOT NULL)) s; + +-- And in a view definition. get_query_def() expands the DEFINE clause's +-- GROUP Vars like the target list's, so the deparsed text names the column +-- rather than the grouping step and the view re-parses. +CREATE VIEW rpr_grp_v2 AS +SELECT category, count(*) OVER w AS cnt +FROM rpr_sort +GROUP BY ROLLUP(category) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A) + DEFINE A AS category IS NOT NULL); + +SELECT pg_get_viewdef('rpr_grp_v2'::regclass, true); +SELECT * FROM rpr_grp_v2 ORDER BY category NULLS LAST; +DROP VIEW rpr_grp_v2; + +-- A DEFINE clause may spell a GROUP BY expression. Planting stops at one +-- rather than offering the columns underneath it to the grouping logic on +-- their own, which is not how grouping makes them available; the target list +-- entry holding the same expression is what both copies end up naming. +SELECT val + 1 AS bumped, count(*) OVER w AS cnt +FROM rpr_grp +GROUP BY val + 1 +WINDOW w AS ( + ORDER BY val + 1 + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A) + DEFINE A AS val + 1 > 0) +ORDER BY bumped; + +-- The same for a function call +SELECT upper(category) AS u, count(*) OVER w AS cnt +FROM rpr_grp +GROUP BY upper(category) +WINDOW w AS ( + ORDER BY upper(category) + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A) + DEFINE A AS upper(category) = 'A') +ORDER BY u; + +-- The same for a cast +SELECT val::text AS t, count(*) OVER w AS cnt +FROM rpr_grp +GROUP BY val::text +WINDOW w AS ( + ORDER BY val::text + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A) + DEFINE A AS val::text > '0') +ORDER BY t; + +-- And through a navigation operation, whose argument is read the same way +SELECT val + 1 AS bumped, count(*) OVER w AS cnt +FROM rpr_grp +GROUP BY val + 1 +WINDOW w AS ( + ORDER BY val + 1 + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B*) + DEFINE B AS PREV(val + 1) > 0) +ORDER BY bumped; + +-- The same under a grouping set, where the row the set nulls leaves the +-- predicate unknown and so unmatched. +SELECT val + 1 AS bumped, count(*) OVER w AS cnt +FROM rpr_grp +GROUP BY ROLLUP(val + 1) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A) + DEFINE A AS val + 1 > 0); + +-- A DEFINE clause may repeat an expression the window itself orders by, with +-- no grouping in sight. Planting bare Vars is what makes this hold: the +-- DEFINE copy of ROW(val, 1) IS NOT NULL is broken into per field tests before +-- the plan is built, and the bare val the break leaves behind is already in +-- the input. +SELECT id, count(*) OVER w AS cnt +FROM rpr_grp +WINDOW w AS ( + ORDER BY ROW(val, 1) + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS ROW(val, 1) IS NOT NULL) +ORDER BY id; + +-- ERROR: the bare column is another matter; grouping by an expression does +-- not make the columns inside it available on their own +SELECT val + 1 AS bumped, count(*) OVER w AS cnt +FROM rpr_grp +GROUP BY val + 1 +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A) + DEFINE A AS val > 0); + +-- ERROR: a column that was never grouped is still reported as one +SELECT category, count(*) OVER w AS cnt +FROM rpr_grp +GROUP BY category +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A) + DEFINE A AS val > 0); + +-- An inline OVER (...) carries a window clause of its own, and the +-- substitution reaches it the same way +SELECT category, + count(*) OVER (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A) + DEFINE A AS category IS NOT NULL) AS cnt +FROM rpr_sort +GROUP BY ROLLUP(category); + +-- The substitution visits every window clause, not just the first. Here +-- the first window is a plain one and the row pattern is on the second. +SELECT category, count(*) OVER w1 AS plain, count(*) OVER w2 AS rpr +FROM rpr_sort +GROUP BY ROLLUP(category) +WINDOW w1 AS (ORDER BY category), + w2 AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A) + DEFINE A AS category IS NOT NULL); + +-- An unreferenced window is substituted like any other +SELECT category +FROM rpr_sort +GROUP BY ROLLUP(category) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A) + DEFINE A AS category IS NOT NULL); + +-- A join turns the DEFINE clause's Vars into join alias Vars. Plain +-- grouping still resolves them, so the column USING merges reaches the +-- pattern unharmed. +SELECT id, count(*) OVER w AS cnt +FROM rpr_grp JOIN rpr_sort USING (id) +GROUP BY id +WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS id > 0) +ORDER BY id; + +-- The same query through the join, with a grouping set that can null the +-- column the pattern reads +SELECT id, count(*) OVER w AS cnt +FROM rpr_grp JOIN rpr_sort USING (id) +GROUP BY ROLLUP(id) +WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS id > 0) +ORDER BY id; + +-- A FULL JOIN's USING column is a merged column -- a COALESCE over the two +-- sides rather than either one -- so a DEFINE clause naming it holds a join +-- alias Var, and only flatten_join_alias_for_parser() turns that into +-- something the grouped target list can be matched against. The inner joins +-- above reach the pattern without that step. +SELECT id, count(*) OVER w AS cnt +FROM rpr_grp FULL JOIN rpr_sort USING (id) +GROUP BY ROLLUP(id) +WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS id > 0) +ORDER BY id NULLS LAST; + +-- A grouping set list holding only the empty set builds no RTE_GROUP +-- either, just like GROUP BY () +SELECT count(*) OVER w AS cnt +FROM rpr_grp +GROUP BY GROUPING SETS (()) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A) + DEFINE A AS true); + +DROP TABLE rpr_grp; + DROP TABLE rpr_sort; -- SQL function inlining: $1 in DEFINE must be substituted by diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list index 8dc778605a9..42dde1449df 100644 --- a/src/tools/pgindent/typedefs.list +++ b/src/tools/pgindent/typedefs.list @@ -669,6 +669,7 @@ DefElemAction DefaultACLInfo DefineMetadataContext DefinePhase +DefinePlantCtx DefineStmt DefineWalkCtx DefnDumperPtr