From 632be24be792c713b1575c906a1bbb2631026caf Mon Sep 17 00:00:00 2001 From: Henson Choi Date: Mon, 28 Sep 2026 15:53:21 +0900 Subject: [PATCH 01/10] Let a row pattern DEFINE clause take part in grouping This commit fixes two problems. 1. DEFINE over grouped input A DEFINE clause is the only part of a WindowClause that holds an expression tree of its own. parseCheckAggregates() rewrote only the target list and HAVING, so over grouped input a DEFINE clause kept plain relation Vars while the target list copies of the same columns became Vars of the RTE_GROUP RTE. Plain GROUP BY hides this, but a grouping set that nulls a column the DEFINE clause reads makes the two copies disagree in varnullingrels, and setrefs.c fails: 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 places the row pattern input table after GROUP BY, so this shape has to work. Carry defineClause through the same three steps as the target list: - parseCheckAggregates(): substitute_grouped_columns(), preceded by flatten_join_alias_for_parser() when the query has joins, so that the merged column of a FULL JOIN USING matches its grouping item. - subquery_planner(): flatten_group_exprs() with the root, so that the varnullingrels a grouping set attached survive onto the replacement. - get_query_def(): flatten_group_exprs(), so that a view over grouped input deparses to the columns the user wrote and re-parses. finalize_grouping_exprs() is not needed, because a DEFINE clause cannot contain GROUPING(). 2. Navigation arguments and join aliases A USING column whose two sides need an implicit cast resolves to the join's own alias Var. When the other side is a pulled-up one-row subquery, the Const substituted for it lands only in joinaliasvars, and flatten_join_alias_vars_mutator() copied it straight into a PREV() or NEXT() argument, where eval_const_expressions() evaluated it at plan time. DEFINE A AS PREV(k / 0) > 0 over such a join failed with "division by zero" before any row was read; it now evaluates per row. Give the mutator the in_rpr_nav_arg tracking that replace_rte_variables_mutator() has, flagging the argument but not the offsets, and wrap a replacement that is not a Var or PlaceHolderVar of the same level in a PlaceHolderVar, as pullup_replace_vars_callback() does. The wrapping needs a PlannerInfo, so flatten_join_alias_for_parser() is unaffected. rpr_base gains tests for navigation over USING columns of mismatched types, and for row patterns over grouped input: ROLLUP, CUBE, grouping set lists, the empty set, joins including FULL JOIN USING, and views. Author: jian he Author: Henson Choi --- src/backend/optimizer/plan/planner.c | 17 + src/backend/optimizer/util/var.c | 49 ++ src/backend/parser/parse_agg.c | 32 ++ src/backend/utils/adt/ruleutils.c | 16 + src/test/regress/expected/rpr_base.out | 696 +++++++++++++++++++++++++ src/test/regress/sql/rpr_base.sql | 458 ++++++++++++++++ 6 files changed, 1268 insertions(+) diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c index 510fe8249a2..145ffc76d40 100644 --- a/src/backend/optimizer/plan/planner.c +++ b/src/backend/optimizer/plan/planner.c @@ -1234,6 +1234,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/optimizer/util/var.c b/src/backend/optimizer/util/var.c index 5dfa2e52d84..ff308a9c7c5 100644 --- a/src/backend/optimizer/util/var.c +++ b/src/backend/optimizer/util/var.c @@ -68,6 +68,7 @@ typedef struct int sublevels_up; bool possible_sublink; /* could aliases include a SubLink? */ bool inserted_sublink; /* have we inserted a SubLink? */ + bool in_rpr_nav_arg; /* below a row pattern navigation argument? */ } flatten_join_alias_vars_context; static bool pull_varnos_walker(Node *node, @@ -796,6 +797,7 @@ flatten_join_alias_vars(PlannerInfo *root, Query *query, Node *node) context.possible_sublink = query->hasSubLinks; /* if hasSubLinks is already true, no need to work hard */ context.inserted_sublink = query->hasSubLinks; + context.in_rpr_nav_arg = false; return flatten_join_alias_vars_mutator(node, &context); } @@ -834,6 +836,7 @@ flatten_join_alias_for_parser(Query *query, Node *node, int sublevels_up) context.possible_sublink = query->hasSubLinks; /* if hasSubLinks is already true, no need to work hard */ context.inserted_sublink = query->hasSubLinks; + context.in_rpr_nav_arg = false; return flatten_join_alias_vars_mutator(node, &context); } @@ -927,9 +930,55 @@ flatten_join_alias_vars_mutator(Node *node, if (context->possible_sublink && !context->inserted_sublink) context->inserted_sublink = checkExprHasSubLink(newvar); + /* + * Below a navigation argument, wrap a non-Var/PHV replacement in a + * PlaceHolderVar so it can't be constant-folded away before the + * navigation runs (mirrors pullup_replace_vars_callback()). + */ + if (context->in_rpr_nav_arg && context->root != NULL && + !(IsA(newvar, Var) && ((Var *) newvar)->varlevelsup == var->varlevelsup) && + !(IsA(newvar, PlaceHolderVar) && ((PlaceHolderVar *) newvar)->phlevelsup == var->varlevelsup)) + { + Relids phrels = pull_varnos(context->root, newvar); + + if (bms_is_empty(phrels)) + { + phrels = get_relids_for_join(context->query, var->varno); + phrels = bms_del_member(phrels, var->varno); + } + newvar = (Node *) make_placeholder_expr(context->root, + (Expr *) newvar, + phrels); + } + /* Lastly, add any varnullingrels to the replacement expression */ return add_nullingrels_if_needed(context->root, newvar, var); } + if (IsA(node, RPRNavExpr)) + { + /* + * Flag the argument, but not the offsets, as a navigation argument + * (mirrors replace_rte_variables_mutator()'s handling). + */ + RPRNavExpr *nav = (RPRNavExpr *) node; + RPRNavExpr *newnode = makeNode(RPRNavExpr); + bool save_in_rpr_nav_arg = context->in_rpr_nav_arg; + + memcpy(newnode, nav, sizeof(RPRNavExpr)); + + context->in_rpr_nav_arg = true; + newnode->arg = (Expr *) + flatten_join_alias_vars_mutator((Node *) nav->arg, context); + context->in_rpr_nav_arg = save_in_rpr_nav_arg; + + newnode->offset_arg = (Expr *) + flatten_join_alias_vars_mutator((Node *) nav->offset_arg, context); + newnode->compound_offset_arg = (Expr *) + flatten_join_alias_vars_mutator((Node *) nav->compound_offset_arg, + context); + + return (Node *) newnode; + } if (IsA(node, PlaceHolderVar)) { PlaceHolderVar *phv = (PlaceHolderVar *) node; diff --git a/src/backend/parser/parse_agg.c b/src/backend/parser/parse_agg.c index 87f0bf65b93..bb380114ec3 100644 --- a/src/backend/parser/parse_agg.c +++ b/src/backend/parser/parse_agg.c @@ -1331,6 +1331,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/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index 5ac77b505b4..62a85c3d79a 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -5659,10 +5659,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/test/regress/expected/rpr_base.out b/src/test/regress/expected/rpr_base.out index ed93bda2cfc..3f38ae9cae6 100644 --- a/src/test/regress/expected/rpr_base.out +++ b/src/test/regress/expected/rpr_base.out @@ -7905,6 +7905,61 @@ ORDER BY id; (4 rows) DROP TABLE rpr_join1, rpr_join2; +-- A mismatched-type USING merge column used to let the other side's +-- pulled-up constant fold into a navigation argument at plan time. +CREATE TABLE rpr_join4 (k bigint); +INSERT INTO rpr_join4 VALUES (10); +SELECT count(*) OVER w AS cnt +FROM (SELECT 10 AS k) a LEFT JOIN rpr_join4 USING (k) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A) + DEFINE A AS PREV(k / 0) > 0 +); + cnt +----- + 0 +(1 row) + +-- Same, but RIGHT JOIN with the constant subquery on its preserved +-- (right) side. +SELECT count(*) OVER w AS cnt +FROM rpr_join4 RIGHT JOIN (SELECT 10 AS k) a USING (k) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A) + DEFINE A AS PREV(k / 0) > 0 +); + cnt +----- + 0 +(1 row) + +DROP TABLE rpr_join4; +-- Same shape with real per-row data, confirming navigation still sees +-- each row's own value. +CREATE TABLE rpr_join5 (k int, v int); +INSERT INTO rpr_join5 VALUES (1, 10), (2, 0), (3, 5); +CREATE TABLE rpr_join6 (k bigint, w int); +INSERT INTO rpr_join6 VALUES (1, 1), (2, 1), (3, 1); +SELECT k, v, cnt +FROM (SELECT k, v, count(*) OVER win AS cnt + FROM rpr_join5 LEFT JOIN rpr_join6 USING (k) + WINDOW win AS ( + ORDER BY k + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS PREV(v) IS NOT NULL + )) s +ORDER BY k; + k | v | cnt +---+----+----- + 1 | 10 | 0 + 2 | 0 | 2 + 3 | 5 | 0 +(3 rows) + +DROP TABLE rpr_join5, rpr_join6; -- ============================================================ -- Complex Expression Tests -- ============================================================ @@ -8240,6 +8295,647 @@ 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 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 361d0923c2a..e887537a4e8 100644 --- a/src/test/regress/sql/rpr_base.sql +++ b/src/test/regress/sql/rpr_base.sql @@ -4705,6 +4705,51 @@ ORDER BY id; DROP TABLE rpr_join1, rpr_join2; +-- A mismatched-type USING merge column used to let the other side's +-- pulled-up constant fold into a navigation argument at plan time. +CREATE TABLE rpr_join4 (k bigint); +INSERT INTO rpr_join4 VALUES (10); + +SELECT count(*) OVER w AS cnt +FROM (SELECT 10 AS k) a LEFT JOIN rpr_join4 USING (k) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A) + DEFINE A AS PREV(k / 0) > 0 +); + +-- Same, but RIGHT JOIN with the constant subquery on its preserved +-- (right) side. +SELECT count(*) OVER w AS cnt +FROM rpr_join4 RIGHT JOIN (SELECT 10 AS k) a USING (k) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A) + DEFINE A AS PREV(k / 0) > 0 +); + +DROP TABLE rpr_join4; + +-- Same shape with real per-row data, confirming navigation still sees +-- each row's own value. +CREATE TABLE rpr_join5 (k int, v int); +INSERT INTO rpr_join5 VALUES (1, 10), (2, 0), (3, 5); +CREATE TABLE rpr_join6 (k bigint, w int); +INSERT INTO rpr_join6 VALUES (1, 1), (2, 1), (3, 1); + +SELECT k, v, cnt +FROM (SELECT k, v, count(*) OVER win AS cnt + FROM rpr_join5 LEFT JOIN rpr_join6 USING (k) + WINDOW win AS ( + ORDER BY k + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS PREV(v) IS NOT NULL + )) s +ORDER BY k; + +DROP TABLE rpr_join5, rpr_join6; + -- ============================================================ -- Complex Expression Tests -- ============================================================ @@ -4971,6 +5016,419 @@ 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 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 -- 2.54.0 (Apple Git-157)