From af7a8959a0ac11ab68f4b2feba9bbc18274581c4 Mon Sep 17 00:00:00 2001 From: jian he Date: Tue, 28 Jul 2026 13:55:37 +0900 Subject: [PATCH] Reject row pattern recognition combined with GROUP BY Window functions are evaluated after grouping, so an RPR window could pattern-match over the grouped output. The implementation does not survive that today: with more than one grouping set the query failed with an internal consistency error, and a DEFINE spelling the GROUP BY expression was rejected outright. Refuse the combination in transformSelectStmt() with ERRCODE_FEATURE_NOT_SUPPORTED rather than let those reach the user. This is a stopgap, and it has three problems of its own. The standard puts grouped input inside the feature. ISO/IEC 19075-5 6.4 says the row pattern input table "is the result of the FROM, WHERE, GROUP BY, and HAVING clauses that precede the WINDOW clause", so R020 requires what this rejects. The restriction has to come out again once the combination works; it does not describe a boundary of the feature. The gate is drawn in the wrong place. It tests groupClause and groupingSets only, but a query with HAVING and no GROUP BY is an aggregate query producing grouped output just the same, and it still runs: SELECT count(*) OVER w FROM t_gs HAVING count(*) > 0 WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING PATTERN (A+) DEFINE A AS true); So the restriction is not consistent even with itself. It hides two defects rather than fixing them. ROLLUP, CUBE or two or more GROUPING SETS with an RPR window failed with "wrong varnullingrels ... for Var", because substitute_grouped_columns() is applied to the target list and havingQual but never to wc->defineClause. Separately, a DEFINE using exactly the GROUP BY expression was rejected with "must appear in the GROUP BY clause", although the same expression is accepted in a plain window's ORDER BY. Both are now unreachable, so the errors are masked, not resolved. --- doc/src/sgml/ref/select.sgml | 6 +++++- src/backend/parser/analyze.c | 17 ++++++++++++++++ src/test/regress/expected/rpr_base.out | 27 ++++++++++++++++++++++++++ src/test/regress/sql/rpr_base.sql | 27 ++++++++++++++++++++++++++ 4 files changed, 76 insertions(+), 1 deletion(-) diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml index b8cd48c4727..40d185135a9 100644 --- a/doc/src/sgml/ref/select.sgml +++ b/doc/src/sgml/ref/select.sgml @@ -1221,7 +1221,11 @@ DEFINE definition_variable_name AS - Row pattern recognition cannot appear anywhere in a common table + Row pattern recognition is not supported in a query that + has GROUP BY, including the grouping-set forms + ROLLUP, CUBE + and GROUPING SETS. + It also cannot appear anywhere in a common table expression that belongs to a WITH RECURSIVE clause; such a query is rejected with an error. The same restriction reaches CREATE RECURSIVE VIEW, which is rewritten diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c index 581457c69c9..da7d69e22f7 100644 --- a/src/backend/parser/analyze.c +++ b/src/backend/parser/analyze.c @@ -1855,6 +1855,23 @@ transformSelectStmt(ParseState *pstate, SelectStmt *stmt, pstate->p_windowdefs, &qry->targetList); + /* + * Window functions (an RPR window included) are evaluated after grouping, + * so an RPR window could in principle pattern-match over the grouped + * output. Supporting it, though, needs additional work we haven't done + * or tested. + */ + if (qry->groupClause || qry->groupingSets) + { + foreach_node(WindowClause, wc, qry->windowClause) + { + if (wc->rpPattern != NULL) + ereport(ERROR, + errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("row pattern recognition is not supported with GROUP BY")); + } + } + /* resolve any still-unresolved output columns as being type text */ if (pstate->p_resolve_unknowns) resolveTargetListUnknowns(pstate, qry->targetList); diff --git a/src/test/regress/expected/rpr_base.out b/src/test/regress/expected/rpr_base.out index 28f05d25f1c..1e3cb8dca3f 100644 --- a/src/test/regress/expected/rpr_base.out +++ b/src/test/regress/expected/rpr_base.out @@ -78,6 +78,33 @@ INSERT INTO stock_price VALUES ('2024-01-03', 'AAPL', 152, 900), ('2024-01-04', 'AAPL', 160, 1500), ('2024-01-05', 'AAPL', 158, 1100); +-- ERROR: row pattern recognition is not supported with GROUP BY +SELECT symbol, count(*) OVER w +FROM stock_price +GROUP BY symbol +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A) + DEFINE A AS symbol IS NOT NULL); +ERROR: row pattern recognition is not supported with GROUP BY +-- ERROR: the same for a grouping set written as ROLLUP +SELECT symbol, count(*) OVER w +FROM stock_price +GROUP BY ROLLUP(dt) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A) + DEFINE A AS symbol IS NOT NULL); +ERROR: row pattern recognition is not supported with GROUP BY +-- ERROR: the same for an explicit GROUPING SETS +SELECT symbol, count(*) OVER w +FROM stock_price +GROUP BY GROUPING SETS ((symbol)) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A) + DEFINE A AS symbol IS NOT NULL); +ERROR: row pattern recognition is not supported with GROUP BY -- Simple column reference SELECT dt, price, COUNT(*) OVER w as cnt FROM stock_price diff --git a/src/test/regress/sql/rpr_base.sql b/src/test/regress/sql/rpr_base.sql index ed197909f95..28c8d96f282 100644 --- a/src/test/regress/sql/rpr_base.sql +++ b/src/test/regress/sql/rpr_base.sql @@ -84,6 +84,33 @@ INSERT INTO stock_price VALUES ('2024-01-04', 'AAPL', 160, 1500), ('2024-01-05', 'AAPL', 158, 1100); +-- ERROR: row pattern recognition is not supported with GROUP BY +SELECT symbol, count(*) OVER w +FROM stock_price +GROUP BY symbol +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A) + DEFINE A AS symbol IS NOT NULL); + +-- ERROR: the same for a grouping set written as ROLLUP +SELECT symbol, count(*) OVER w +FROM stock_price +GROUP BY ROLLUP(dt) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A) + DEFINE A AS symbol IS NOT NULL); + +-- ERROR: the same for an explicit GROUPING SETS +SELECT symbol, count(*) OVER w +FROM stock_price +GROUP BY GROUPING SETS ((symbol)) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A) + DEFINE A AS symbol IS NOT NULL); + -- Simple column reference SELECT dt, price, COUNT(*) OVER w as cnt FROM stock_price