From 7d66fea063d58c4fdb7015512d02e8def75e156d Mon Sep 17 00:00:00 2001 From: Henson Choi Date: Tue, 7 Jul 2026 11:47:43 +0900 Subject: [PATCH] Expand row pattern recognition regression tests Add and improve several row pattern recognition test cases, all in the regression suite: - Cover a user-defined aggregate over an RPR reduced frame. The aggregate logs how its transition functions are called, so the output shows that the moving-aggregate inverse transition is never used and which rows each reduced frame feeds the aggregate. - Cover a whole-row Var in a DEFINE clause. Written as a bare relation name it resolves to a whole-row Var, and remove_unused_subquery_outputs() must retain it just as it retains a scalar DEFINE column, so the pattern still matches when the outer query references no column. - Cover the rejection of a two-part table-qualified whole-row reference (rel.*) in DEFINE, which takes a different path from the schema-qualified form already tested. - Expand the worked examples on the PREV(LAST(...)) and NEXT(LAST(...)) compound navigation cases to match the density of the neighboring FIRST-based examples. Suggested-by: Jian He --- src/test/regress/expected/rpr.out | 30 +++++- src/test/regress/expected/rpr_integration.out | 94 +++++++++++++++++++ src/test/regress/sql/rpr.sql | 27 +++++- src/test/regress/sql/rpr_integration.sql | 68 ++++++++++++++ 4 files changed, 211 insertions(+), 8 deletions(-) diff --git a/src/test/regress/expected/rpr.out b/src/test/regress/expected/rpr.out index 52be54039e7..6080398f583 100644 --- a/src/test/regress/expected/rpr.out +++ b/src/test/regress/expected/rpr.out @@ -1361,6 +1361,21 @@ WINDOW w AS ( ERROR: qualified expression "pg_temp.stock.*" is not allowed in DEFINE clause LINE 7: DEFINE A AS (pg_temp.stock.*) IS NOT NULL ^ +-- A two-part table-qualified whole-row reference is rejected as well, through +-- a separate range-variable check (a bare relation name is instead accepted +-- as a whole-row Var). +-- 2-part (table.*): +SELECT price FROM stock +WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (A) + DEFINE A AS (stock.*) IS NOT NULL +); +ERROR: range variable qualified expression "stock.*" is not allowed in DEFINE clause +LINE 7: DEFINE A AS (stock.*) IS NOT NULL + ^ -- -- 2-arg PREV/NEXT: functional tests -- @@ -2176,8 +2191,11 @@ FROM rpr_nav WINDOW w AS ( 6 | 10 | | 0 (6 rows) --- PREV(LAST(val), 2): target = currentpos - 0 - 2 = currentpos - 2 --- Same backward reach as PREV(val, 2) +-- PREV(LAST(val), 2): LAST(val) is the current row (inner offset 0), so +-- target = currentpos - 0 - 2 = currentpos - 2. Same backward reach as PREV(val, 2). +-- At currentpos=2 (start id=1): target=0 -> out of range -> NULL -> B fails. +-- At currentpos=3 (start id=2): target=1(val=10) -> in range -> B runs on id3..id6, +-- so the match is id2..id6. SELECT id, val, first_value(id) OVER w AS mf, count(*) OVER w AS cnt FROM rpr_nav WINDOW w AS ( ORDER BY id @@ -2196,8 +2214,12 @@ FROM rpr_nav WINDOW w AS ( 6 | 10 | | 0 (6 rows) --- NEXT(LAST(val, 1), 2): target = currentpos - 1 + 2 = currentpos + 1 --- Looks 1 row ahead: same as NEXT(val, 1) +-- NEXT(LAST(val, 1), 2): LAST(val, 1) is one row back (inner offset 1), then +-- NEXT adds 2, so target = currentpos - 1 + 2 = currentpos + 1. Looks one row +-- ahead: same as NEXT(val, 1). +-- At currentpos=2 (start id=1): target=3(val=30) -> in range -> B true. +-- B stays true through id5 (target=6); at id6 target=7 -> out of range -> NULL, +-- so the match is id1..id5. SELECT id, val, first_value(id) OVER w AS mf, count(*) OVER w AS cnt FROM rpr_nav WINDOW w AS ( ORDER BY id diff --git a/src/test/regress/expected/rpr_integration.out b/src/test/regress/expected/rpr_integration.out index 541ad35de4f..d0b5cc5a44f 100644 --- a/src/test/regress/expected/rpr_integration.out +++ b/src/test/regress/expected/rpr_integration.out @@ -469,6 +469,48 @@ SELECT count(*) FROM ( 10 (1 row) +-- The same guard must also cover a whole-row Var. Writing the bare +-- relation name (rpr_integ) in DEFINE resolves to a whole-row Var, whose +-- attribute number is 0. remove_unused_subquery_outputs() matches the +-- guard on attribute number, so the whole-row Var is retained as a single +-- entry while the unused scalar "val" output is still replaced with NULL; +-- DEFINE evaluates against the intact row, and the match is unchanged. +EXPLAIN (VERBOSE, COSTS OFF) +SELECT sum(c) FROM ( + SELECT val, count(*) OVER w AS c FROM rpr_integ + WINDOW w AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS rpr_integ IS NOT NULL) +) t; + QUERY PLAN +----------------------------------------------------------------------------------------------- + Aggregate + Output: sum((count(*) OVER w)) + -> WindowAgg + Output: NULL::integer, count(*) OVER w, rpr_integ.id, rpr_integ.* + Window: w AS (ORDER BY rpr_integ.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) + Pattern: a b+ + Nav Mark Lookback: 0 + -> Sort + Output: rpr_integ.id, rpr_integ.* + Sort Key: rpr_integ.id + -> Seq Scan on public.rpr_integ + Output: rpr_integ.id, rpr_integ.* +(12 rows) + +SELECT sum(c) FROM ( + SELECT val, count(*) OVER w AS c FROM rpr_integ + WINDOW w AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS rpr_integ IS NOT NULL) +) t; + sum +----- + 10 +(1 row) + -- ============================================================ -- A6. Inverse transition bypass -- ============================================================ @@ -511,6 +553,58 @@ ORDER BY id; 10 | 45 | (10 rows) +-- A custom aggregate logs how its transition functions are called: msfunc +-- appends '+' for a forward transition, minvfunc appends '-' for an inverse +-- transition. Over an RPR reduced frame only '+' appears, confirming the +-- inverse-transition path is not used, and the logged values show which rows +-- each frame feeds the aggregate. +CREATE FUNCTION rpr_logging_sfunc(text, anyelement) RETURNS text AS +$$ SELECT COALESCE($1, '') || '*' || quote_nullable($2) $$ LANGUAGE SQL IMMUTABLE; +CREATE FUNCTION rpr_logging_msfunc(text, anyelement) RETURNS text AS +$$ SELECT COALESCE($1, '') || '+' || quote_nullable($2) $$ LANGUAGE SQL IMMUTABLE; +CREATE FUNCTION rpr_logging_minvfunc(text, anyelement) RETURNS text AS +$$ SELECT $1 || '-' || quote_nullable($2) $$ LANGUAGE SQL IMMUTABLE; +CREATE AGGREGATE rpr_logging_agg (anyelement) +( + stype = text, + sfunc = rpr_logging_sfunc, + mstype = text, + msfunc = rpr_logging_msfunc, + minvfunc = rpr_logging_minvfunc +); +-- Match 1 is id1..id3 (v = NULL,'a','b') and match 2 is id4..id6 +-- (v = NULL,'c','d'); B extends the match while k increases. Every frame +-- shows '+' only (no '-'), and the two matches never share state across their +-- boundary. +SELECT id, v, + rpr_logging_agg(v) OVER w AS logged +FROM (VALUES + (1, 1, NULL), + (2, 3, 'a'), + (3, 5, 'b'), + (4, 2, NULL), + (5, 4, 'c'), + (6, 6, 'd') +) AS t(id, k, v) +WINDOW w AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE A AS TRUE, B AS k > PREV(k)) +ORDER BY id; + id | v | logged +----+---+--------------- + 1 | | +NULL+'a'+'b' + 2 | a | + 3 | b | + 4 | | +NULL+'c'+'d' + 5 | c | + 6 | d | +(6 rows) + +DROP AGGREGATE rpr_logging_agg(anyelement); +DROP FUNCTION rpr_logging_sfunc(text, anyelement); +DROP FUNCTION rpr_logging_msfunc(text, anyelement); +DROP FUNCTION rpr_logging_minvfunc(text, anyelement); -- ============================================================ -- A7. Cost estimation RPR awareness -- ============================================================ diff --git a/src/test/regress/sql/rpr.sql b/src/test/regress/sql/rpr.sql index 2013f399b32..4796d42d9c2 100644 --- a/src/test/regress/sql/rpr.sql +++ b/src/test/regress/sql/rpr.sql @@ -723,6 +723,18 @@ WINDOW w AS ( PATTERN (A) DEFINE A AS (pg_temp.stock.*) IS NOT NULL ); +-- A two-part table-qualified whole-row reference is rejected as well, through +-- a separate range-variable check (a bare relation name is instead accepted +-- as a whole-row Var). +-- 2-part (table.*): +SELECT price FROM stock +WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (A) + DEFINE A AS (stock.*) IS NOT NULL +); -- -- 2-arg PREV/NEXT: functional tests @@ -1147,8 +1159,11 @@ FROM rpr_nav WINDOW w AS ( DEFINE A AS TRUE, B AS NEXT(FIRST(val, 1), 1) > 0 ); --- PREV(LAST(val), 2): target = currentpos - 0 - 2 = currentpos - 2 --- Same backward reach as PREV(val, 2) +-- PREV(LAST(val), 2): LAST(val) is the current row (inner offset 0), so +-- target = currentpos - 0 - 2 = currentpos - 2. Same backward reach as PREV(val, 2). +-- At currentpos=2 (start id=1): target=0 -> out of range -> NULL -> B fails. +-- At currentpos=3 (start id=2): target=1(val=10) -> in range -> B runs on id3..id6, +-- so the match is id2..id6. SELECT id, val, first_value(id) OVER w AS mf, count(*) OVER w AS cnt FROM rpr_nav WINDOW w AS ( ORDER BY id @@ -1158,8 +1173,12 @@ FROM rpr_nav WINDOW w AS ( DEFINE A AS TRUE, B AS PREV(LAST(val), 2) IS NOT NULL ); --- NEXT(LAST(val, 1), 2): target = currentpos - 1 + 2 = currentpos + 1 --- Looks 1 row ahead: same as NEXT(val, 1) +-- NEXT(LAST(val, 1), 2): LAST(val, 1) is one row back (inner offset 1), then +-- NEXT adds 2, so target = currentpos - 1 + 2 = currentpos + 1. Looks one row +-- ahead: same as NEXT(val, 1). +-- At currentpos=2 (start id=1): target=3(val=30) -> in range -> B true. +-- B stays true through id5 (target=6); at id6 target=7 -> out of range -> NULL, +-- so the match is id1..id5. SELECT id, val, first_value(id) OVER w AS mf, count(*) OVER w AS cnt FROM rpr_nav WINDOW w AS ( ORDER BY id diff --git a/src/test/regress/sql/rpr_integration.sql b/src/test/regress/sql/rpr_integration.sql index 23164473d0b..c5f5e850925 100644 --- a/src/test/regress/sql/rpr_integration.sql +++ b/src/test/regress/sql/rpr_integration.sql @@ -304,6 +304,29 @@ SELECT count(*) FROM ( DEFINE B AS val > PREV(val)) ) t; +-- The same guard must also cover a whole-row Var. Writing the bare +-- relation name (rpr_integ) in DEFINE resolves to a whole-row Var, whose +-- attribute number is 0. remove_unused_subquery_outputs() matches the +-- guard on attribute number, so the whole-row Var is retained as a single +-- entry while the unused scalar "val" output is still replaced with NULL; +-- DEFINE evaluates against the intact row, and the match is unchanged. +EXPLAIN (VERBOSE, COSTS OFF) +SELECT sum(c) FROM ( + SELECT val, count(*) OVER w AS c FROM rpr_integ + WINDOW w AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS rpr_integ IS NOT NULL) +) t; + +SELECT sum(c) FROM ( + SELECT val, count(*) OVER w AS c FROM rpr_integ + WINDOW w AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS rpr_integ IS NOT NULL) +) t; + -- ============================================================ -- A6. Inverse transition bypass -- ============================================================ @@ -334,6 +357,51 @@ WINDOW w AS (ORDER BY id DEFINE B AS val > PREV(val)) ORDER BY id; +-- A custom aggregate logs how its transition functions are called: msfunc +-- appends '+' for a forward transition, minvfunc appends '-' for an inverse +-- transition. Over an RPR reduced frame only '+' appears, confirming the +-- inverse-transition path is not used, and the logged values show which rows +-- each frame feeds the aggregate. +CREATE FUNCTION rpr_logging_sfunc(text, anyelement) RETURNS text AS +$$ SELECT COALESCE($1, '') || '*' || quote_nullable($2) $$ LANGUAGE SQL IMMUTABLE; +CREATE FUNCTION rpr_logging_msfunc(text, anyelement) RETURNS text AS +$$ SELECT COALESCE($1, '') || '+' || quote_nullable($2) $$ LANGUAGE SQL IMMUTABLE; +CREATE FUNCTION rpr_logging_minvfunc(text, anyelement) RETURNS text AS +$$ SELECT $1 || '-' || quote_nullable($2) $$ LANGUAGE SQL IMMUTABLE; +CREATE AGGREGATE rpr_logging_agg (anyelement) +( + stype = text, + sfunc = rpr_logging_sfunc, + mstype = text, + msfunc = rpr_logging_msfunc, + minvfunc = rpr_logging_minvfunc +); + +-- Match 1 is id1..id3 (v = NULL,'a','b') and match 2 is id4..id6 +-- (v = NULL,'c','d'); B extends the match while k increases. Every frame +-- shows '+' only (no '-'), and the two matches never share state across their +-- boundary. +SELECT id, v, + rpr_logging_agg(v) OVER w AS logged +FROM (VALUES + (1, 1, NULL), + (2, 3, 'a'), + (3, 5, 'b'), + (4, 2, NULL), + (5, 4, 'c'), + (6, 6, 'd') +) AS t(id, k, v) +WINDOW w AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE A AS TRUE, B AS k > PREV(k)) +ORDER BY id; + +DROP AGGREGATE rpr_logging_agg(anyelement); +DROP FUNCTION rpr_logging_sfunc(text, anyelement); +DROP FUNCTION rpr_logging_msfunc(text, anyelement); +DROP FUNCTION rpr_logging_minvfunc(text, anyelement); + -- ============================================================ -- A7. Cost estimation RPR awareness -- ============================================================