From 41419d1ef7cf694b48101100f7bf3b5c16097159 Mon Sep 17 00:00:00 2001 From: Henson Choi Date: Fri, 18 Sep 2026 14:13:52 +0900 Subject: [PATCH] Keep a row pattern navigation offset out of the window input A navigation offset is a run-time constant the executor resolves once, at the top of the scan, before any input row has been read. But set_upper_references() handed the whole DEFINE expression to fix_upper_expr(), which matches any subexpression against the subplan's targetlist. An offset spelled the same as something already in the window input -- a window ORDER BY key, or a GROUP BY expression -- was replaced with a Var(OUTER_VAR) referencing it, and resolve_nav_offsets() then read that Var from an outer slot that is not set yet, dereferencing a null TupleTableSlot. Only the navigated argument is read from the input, one row at a time. Give RPRNavExpr its own case in fix_upper_expr_mutator(): recurse into arg as before, and hand the two offsets to fix_scan_expr(), which is what set_plan_refs() already does with the WindowAgg frame offsets for the same reason. A constant offset was never affected, since search_indexed_tlist_for_non_var() declines to match a Const. The tests therefore use an offset that survives constant folding, once against a window ORDER BY key and once against a GROUP BY expression. --- src/backend/optimizer/plan/setrefs.c | 22 ++++++++++ src/test/regress/expected/rpr_base.out | 61 ++++++++++++++++++++++++++ src/test/regress/sql/rpr_base.sql | 34 ++++++++++++++ 3 files changed, 117 insertions(+) diff --git a/src/backend/optimizer/plan/setrefs.c b/src/backend/optimizer/plan/setrefs.c index 9dc191ad68e..2ee3c8baac2 100644 --- a/src/backend/optimizer/plan/setrefs.c +++ b/src/backend/optimizer/plan/setrefs.c @@ -3403,6 +3403,28 @@ fix_upper_expr_mutator(Node *node, fix_upper_expr_context *context) /* XXX can we assert something about phnullingrels? */ return fix_upper_expr_mutator((Node *) phv->phexpr, context); } + if (IsA(node, RPRNavExpr)) + { + RPRNavExpr *nav = (RPRNavExpr *) node; + RPRNavExpr *newnav = makeNode(RPRNavExpr); + + memcpy(newnav, nav, sizeof(RPRNavExpr)); + + /* + * The offsets are resolved once per scan, before the outer slot is + * set, so they cannot reference it the way arg does. Same treatment + * as the WindowAgg frame offsets. + */ + newnav->arg = (Expr *) + fix_upper_expr_mutator((Node *) nav->arg, context); + newnav->offset_arg = (Expr *) + fix_scan_expr(context->root, (Node *) nav->offset_arg, + context->rtoffset, context->num_exec); + newnav->compound_offset_arg = (Expr *) + fix_scan_expr(context->root, (Node *) nav->compound_offset_arg, + context->rtoffset, context->num_exec); + return (Node *) newnav; + } /* Try matching more complex expressions too, if tlist has any */ if (context->subplan_itlist->has_non_vars) { diff --git a/src/test/regress/expected/rpr_base.out b/src/test/regress/expected/rpr_base.out index bda37b4fc5f..f0d6cf1b924 100644 --- a/src/test/regress/expected/rpr_base.out +++ b/src/test/regress/expected/rpr_base.out @@ -2268,6 +2268,67 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING DROP FUNCTION rpr_nav_dflt(int, int); DROP FUNCTION rpr_nav_named(int, int); DROP TABLE rpr_nav_txt; +-- A navigation offset is resolved once at the top of the scan, before any +-- input row has been read, so it must not be matched to the window input the +-- way the navigated argument is. These two spell the offset the same as a +-- window ORDER BY key and as a GROUP BY expression, which is what makes the +-- match available. +CREATE TABLE rpr_navoff (id int, val int); +INSERT INTO rpr_navoff VALUES (1, 10), (2, 20), (3, 15), (4, 30), (5, 5); +SELECT id, val, count(*) OVER w AS cnt +FROM rpr_navoff +WINDOW w AS (ORDER BY (extract(hour from localtimestamp)::int * 0 + 1), id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS val > PREV(val, (extract(hour from localtimestamp)::int * 0 + 1))); + id | val | cnt +----+-----+----- + 1 | 10 | 2 + 2 | 20 | 0 + 3 | 15 | 2 + 4 | 30 | 0 + 5 | 5 | 0 +(5 rows) + +-- Control: an offset that matches nothing in the window input. +SELECT id, val, count(*) OVER w AS cnt +FROM rpr_navoff +WINDOW w AS (ORDER BY (extract(hour from localtimestamp)::int * 0 + 1), id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS val > PREV(val, (extract(hour from localtimestamp)::int * 0 + 2))); + id | val | cnt +----+-----+----- + 1 | 10 | 0 + 2 | 20 | 3 + 3 | 15 | 0 + 4 | 30 | 0 + 5 | 5 | 0 +(5 rows) + +SELECT id, val, count(*) OVER w AS cnt +FROM rpr_navoff +GROUP BY GROUPING SETS ((id, val, ((random() * 0)::bigint + 1)), + (id, ((random() * 0)::bigint + 1))) +WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS val > PREV(val, (random() * 0)::bigint + 1)) +ORDER BY id, val; + id | val | cnt +----+-----+----- + 1 | 10 | 0 + 1 | | 0 + 2 | 20 | 0 + 2 | | 0 + 3 | 15 | 0 + 3 | | 0 + 4 | 30 | 0 + 4 | | 0 + 5 | 5 | 0 + 5 | | 0 +(10 rows) + +DROP TABLE rpr_navoff; -- PREV function - reference previous row in pattern SELECT id, val, COUNT(*) OVER w as cnt FROM rpr_nav diff --git a/src/test/regress/sql/rpr_base.sql b/src/test/regress/sql/rpr_base.sql index 60625f6e6eb..1bf97a846b4 100644 --- a/src/test/regress/sql/rpr_base.sql +++ b/src/test/regress/sql/rpr_base.sql @@ -1611,6 +1611,40 @@ DROP FUNCTION rpr_nav_dflt(int, int); DROP FUNCTION rpr_nav_named(int, int); DROP TABLE rpr_nav_txt; +-- A navigation offset is resolved once at the top of the scan, before any +-- input row has been read, so it must not be matched to the window input the +-- way the navigated argument is. These two spell the offset the same as a +-- window ORDER BY key and as a GROUP BY expression, which is what makes the +-- match available. +CREATE TABLE rpr_navoff (id int, val int); +INSERT INTO rpr_navoff VALUES (1, 10), (2, 20), (3, 15), (4, 30), (5, 5); + +SELECT id, val, count(*) OVER w AS cnt +FROM rpr_navoff +WINDOW w AS (ORDER BY (extract(hour from localtimestamp)::int * 0 + 1), id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS val > PREV(val, (extract(hour from localtimestamp)::int * 0 + 1))); + +-- Control: an offset that matches nothing in the window input. +SELECT id, val, count(*) OVER w AS cnt +FROM rpr_navoff +WINDOW w AS (ORDER BY (extract(hour from localtimestamp)::int * 0 + 1), id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS val > PREV(val, (extract(hour from localtimestamp)::int * 0 + 2))); + +SELECT id, val, count(*) OVER w AS cnt +FROM rpr_navoff +GROUP BY GROUPING SETS ((id, val, ((random() * 0)::bigint + 1)), + (id, ((random() * 0)::bigint + 1))) +WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS val > PREV(val, (random() * 0)::bigint + 1)) +ORDER BY id, val; + +DROP TABLE rpr_navoff; + -- PREV function - reference previous row in pattern SELECT id, val, COUNT(*) OVER w as cnt FROM rpr_nav -- 2.54.0 (Apple Git-157)