From 9d9146d83248877f2a50fdfb0c7756e8f1a1811c Mon Sep 17 00:00:00 2001 From: Henson Choi Date: Wed, 12 Aug 2026 00:31:29 +0900 Subject: [PATCH] Correct stale RPR test comments and fill gaps in the RPR documentation An audit of the row pattern recognition tests and user documentation against the code found prose that no longer describes what the code does. In the tests, correct the comments the expected output contradicts: a DEFINE variable that PATTERN does not use was said to be ignored where the query is rejected, a match was attributed to ONE ROW PER MATCH, which this feature does not implement, the reduced frame of the last_value test was said to hold only NULLs, the reluctant-quantifier comparison named the wrong column as the outlier, and several EXPLAIN tests claimed absorption, state merging or a skip count the recorded plan does not show. Drop the names that do not exist -- null_slot, altPriority, deparse_rpr_group, deparse_rpr_var, and a rule label "(H-1)" carried by no header or README -- place the NFA engine in execRPR.c, where it lives, rather than in nodeWindowAgg.c, credit the parser rather than the planner with merging two identical inline window specifications, and list the standard-clause section in the rpr_nfa.sql contents, which stopped one section short. Remove the development history: a reviewer credited by name, a rule flagged as completely untested where the test below it exercises it, an opaque "(0059)" reference, a pointer to a future patch, and three comments narrating bugs that were fixed. Remove the internal C identifiers a test comment should not need, including the "Files:" banners naming the source each block exercises. Delete the expected results spelled out below a query and the per-row result enumerations; the expected output records both in full. One-line labels above a query stay as they are, including those naming the expected error. Make the comment labels consistent: drop the "Test:" prefix, which only rpr_base.sql used and which says nothing -- every query there is a test -- spell "ERROR:" the way the rest of the file does, and drop the repeated "Reluctant quantifier: prefer shortest match" and "NFA reports 3 length-0 matches" lines, whose tests already say what they are. In the documentation, the tutorial said every window function reads as NULL on a non-starting row, where the ones that do not depend on the frame, such as row_number(), are unaffected; it described what DEFINE accepts without saying that volatile functions and set-returning functions are rejected; and it still cited the nesting depth limit as 253. The SELECT reference described row_pattern_common_syntax without saying which frame it requires: state that it must be ROWS mode, start at CURRENT ROW, and end at UNBOUNDED FOLLOWING or a positive offset FOLLOWING. Fill the remaining gaps the same audit found. The SELECT reference did not say that DEFINE is required, that an alternation costs a nesting level of its own -- so a pattern that nests alternations reaches the depth limit at about half as many parentheses -- or that row pattern recognition cannot appear in a common table expression of a WITH RECURSIVE query. It called an empty PATTERN() and the exclusion "not supported" where both are plain syntax errors, unlike PERMUTE, which is rejected as an unsupported feature. Its compatibility list named only the WINDOW clause, though an inline OVER ( ... ) takes the same syntax, and it omitted the largest divergence from the standard: a column reference qualified by a pattern variable, the spelling the standard's own DEFINE examples use, is not implemented. The tutorial called the common syntax two subclauses where it is four, in a fixed order. One test comment hedged that the lexer might tokenize a space between a quantifier and its "?" differently, where the recorded output shows each separated form returning exactly what its unseparated counterpart returns. No code changes; the expected files change only where a comment does. --- doc/src/sgml/advanced.sgml | 30 +-- doc/src/sgml/ref/select.sgml | 54 +++++- src/test/regress/expected/rpr.out | 41 ++-- src/test/regress/expected/rpr_base.out | 183 ++++++------------ src/test/regress/expected/rpr_explain.out | 28 +-- src/test/regress/expected/rpr_integration.out | 2 +- src/test/regress/expected/rpr_nfa.out | 39 ++-- src/test/regress/sql/rpr.sql | 41 ++-- src/test/regress/sql/rpr_base.sql | 181 ++++++----------- src/test/regress/sql/rpr_explain.sql | 28 +-- src/test/regress/sql/rpr_integration.sql | 2 +- src/test/regress/sql/rpr_nfa.sql | 39 ++-- 12 files changed, 274 insertions(+), 394 deletions(-) diff --git a/doc/src/sgml/advanced.sgml b/doc/src/sgml/advanced.sgml index c9d4e2ffc64..b0f929266ff 100644 --- a/doc/src/sgml/advanced.sgml +++ b/doc/src/sgml/advanced.sgml @@ -554,15 +554,19 @@ WHERE pos < 3; Row Pattern Common Syntax can be used to perform Row Pattern Recognition - in a query. The Row Pattern Common Syntax includes two sub - clauses: DEFINE - and PATTERN. DEFINE defines - row pattern variables along with an expression. The expression must be a - logical expression, which means it must - return TRUE, FALSE + in a query. The Row Pattern Common Syntax includes four sub clauses, + which must be written in this order: + AFTER MATCH SKIP, INITIAL + or SEEK, PATTERN + and DEFINE. The first two are optional, and the + complete example at the end of this section uses both. + DEFINE defines row pattern variables along with an + expression. The expression must be a logical expression, which means + it must return TRUE, FALSE or NULL. The expression may comprise column references - and functions. Window functions, aggregate functions and subqueries are - not allowed. An example of DEFINE is as follows. + and non-volatile functions. Window functions, aggregate functions, + set-returning functions and subqueries are not allowed. An example + of DEFINE is as follows. DEFINE @@ -597,11 +601,13 @@ DEFINE rows which satisfies the PATTERN is found, in the starting row all columns or functions are shown in the target list. Note that aggregations only look into the matched rows, rather than the whole frame. On the second or - subsequent rows all window functions are shown as NULL. Aggregates on + subsequent rows the window functions that read the frame, such + as first_value(), are shown as NULL, while functions + that do not depend on the frame, such + as row_number(), are unaffected. Aggregates on non-starting rows return their initial value: for example, count() returns 0 and sum() - returns NULL. For rows that do not match the PATTERN, window functions - are likewise shown as NULL and aggregates return their initial value. + returns NULL. Rows that do not match the PATTERN behave the same way. Example of a SELECT using the DEFINE and PATTERN clause is as follows. @@ -661,7 +667,7 @@ FROM stock and simplifying nested quantifiers (e.g., (A*)* becomes A*). These optimizations reduce pattern complexity and also decrease - nesting depth, making the 253-level depth limit rarely encountered. + nesting depth, making the 254-level depth limit rarely encountered. They are applied transparently and can be observed in EXPLAIN output. diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml index 6ab970f77eb..b8cd48c4727 100644 --- a/doc/src/sgml/ref/select.sgml +++ b/doc/src/sgml/ref/select.sgml @@ -1153,8 +1153,8 @@ DEFINE definition_variable_name AS expression. PATTERN defines a sequence of rows that satisfies certain conditions using variables defined in the DEFINE clause (an empty PATTERN() - is not supported). Each pattern variable can be followed by a quantifier - to specify how many times it should match: + is not accepted by the syntax). Each pattern variable can be followed + by a quantifier to specify how many times it should match: * (zero or more), + (one or more), ? (zero or one), @@ -1166,8 +1166,9 @@ DEFINE definition_variable_name AS Reluctant quantifiers (e.g., *?, +?, ??, {n,m}?) are supported. - The exclusion ({- and -}) - and the permutation (PERMUTE) are not supported. + The exclusion ({- and -}) is not + accepted by the syntax, and the permutation + (PERMUTE) is rejected as an unsupported feature. PERMUTE is recognized wherever a ( follows it, so a pattern variable of that name has to be written "permute" in that position. @@ -1188,7 +1189,22 @@ DEFINE definition_variable_name AS Conversely, variables defined in the DEFINE clause but not used in the PATTERN clause are rejected - with an error. + with an error. The DEFINE clause itself is not + optional: leaving it out is a syntax error, even where every pattern + variable would evaluate as TRUE. + + + + Row pattern recognition constrains the frame clause it is attached to. + The frame must be written in ROWS mode + as BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING, or + as BETWEEN CURRENT ROW + AND offset FOLLOWING with + a positive offset. + RANGE and GROUPS mode, any other + frame start or end, and + any frame_exclusion option other + than EXCLUDE NO OTHERS are rejected with an error. @@ -1196,12 +1212,22 @@ DEFINE definition_variable_name AS used in the PATTERN clause is 240. If this limit is exceeded, an error will be raised. Additionally, the maximum nesting depth of pattern groups - (parentheses) is 254 levels. + (parentheses) is 254 levels. An alternation costs a level of its own, + so a pattern that nests alternations reaches the limit at about half + as many parentheses. However, pattern optimizations such as flattening nested sequences and simplifying nested quantifiers may reduce the effective depth, so this limit is rarely reached in practice. + + Row pattern recognition 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 + to WITH RECURSIVE. + + The purpose of a WINDOW clause is to specify the behavior of window functions appearing in the query's @@ -2320,12 +2346,24 @@ SELECT 2+2; PostgreSQL supports row pattern recognition - within the WINDOW clause. The SQL standard defines - more subclauses: MEASURES and + both in a WINDOW clause entry and in an + inline OVER ( ... ) specification. The SQL standard + defines more subclauses: MEASURES and SUBSET. They are not currently supported in PostgreSQL. Also in the standard there are more variations in the AFTER MATCH clause. + + + The largest difference is in DEFINE: a column + reference qualified by a pattern variable, such + as A.price, is not implemented and is rejected as an + unsupported feature, even though the standard writes + its DEFINE examples that way. An unqualified column + reference in a DEFINE expression therefore always + reads the row being tested, and other rows of the match are reachable + only through the navigation operations. + diff --git a/src/test/regress/expected/rpr.out b/src/test/regress/expected/rpr.out index bb7b2f44929..c4958c1b8d8 100644 --- a/src/test/regress/expected/rpr.out +++ b/src/test/regress/expected/rpr.out @@ -835,7 +835,7 @@ SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER company2 | 07-10-2023 | 1300 | | (20 rows) --- PREV returns NULL at partition's first row (null_slot path) +-- PREV returns NULL at the partition's first row (no earlier row to fetch) SELECT company, tdate, price, count(*) OVER w FROM stock WINDOW w AS ( @@ -871,7 +871,7 @@ WINDOW w AS ( company2 | 07-10-2023 | 1300 | 0 (20 rows) --- NEXT returns NULL at partition's last row (null_slot path) +-- NEXT returns NULL at the partition's last row (no later row to fetch) SELECT company, tdate, price, count(*) OVER w FROM stock WINDOW w AS ( @@ -1265,8 +1265,8 @@ WINDOW w AS ( ); ERROR: DEFINE clause cannot contain volatile functions DROP SEQUENCE rpr_seq; --- A volatile DEFINE is now rejected in the planner, not at parse time, so a --- view that hides one is created successfully and only errors when read. +-- A volatile DEFINE is rejected in the planner, so a view that hides one is +-- created successfully and errors only when read. CREATE TEMP VIEW rpr_volatile_view AS SELECT company, tdate, price, count(*) OVER w FROM stock @@ -1643,8 +1643,8 @@ EXECUTE test_prev_offset(NULL); ERROR: row pattern navigation offset must not be null DEALLOCATE test_prev_offset; -- 2-arg PREV/NEXT: host variable with positive value. A generic plan keeps --- the parameter as a Param, which is what reaches the RPR_NAV_OFFSET_NEEDS_EVAL --- path; a custom plan would fold it to a Const and settle the reach at init. +-- the parameter as a Param, so the offset is resolved at run time; a custom +-- plan would fold it to a constant and settle the reach at init. SET plan_cache_mode = force_generic_plan; PREPARE test_prev_offset(int8) AS SELECT company, tdate, price, first_value(price) OVER w, count(*) OVER w @@ -1782,12 +1782,9 @@ WINDOW w AS ( company2 | 07-10-2023 | 1300 | | | 0 (20 rows) --- Pass-by-ref types: two PREV calls targeting different positions. --- Verifies that datumCopy in RESTORE prevents dangling pointers when --- nav_slot is re-fetched for the second navigation. --- tdate::text gives distinct text values per row (e.g. '07-01-2023'). --- B matches when 1-back date text > 2-back date text (always true for --- ascending dates), so B+ extends the full partition after A. +-- Pass-by-ref types: two PREV calls targeting different positions, so the +-- first navigation result must survive the second fetch. B compares the +-- 1-back and 2-back tdate text. SELECT company, tdate, tdate::text AS tdate_text, first_value(tdate::text) OVER w, last_value(tdate::text) OVER w, count(*) OVER w FROM stock @@ -1978,7 +1975,7 @@ FROM rpr_nav WINDOW w AS ( (6 rows) -- SKIP TO NEXT ROW with FIRST(val) = LAST(val): overlapping match attempts. --- With ONE ROW PER MATCH, each row shows only its first match result. +-- Each row reports only the match that starts at it. SELECT id, val, first_value(id) OVER w AS mf, last_value(id) OVER w AS ml FROM rpr_nav WINDOW w AS ( ORDER BY id @@ -2146,8 +2143,6 @@ DROP TABLE rpr_names; -- Compound navigation: PREV(FIRST(val), M) -- rpr_nav: (1,10),(2,20),(3,30),(4,10),(5,50),(6,10) -- PREV(FIRST(val), 1): target = match_start + 0 - 1 = match_start - 1 --- At match_start=1: target=0 -> out of range -> NULL --- At match_start=3: target=2(val=20) -> 20 > 0 -> true SELECT id, val, first_value(id) OVER w AS mf, count(*) OVER w AS cnt FROM rpr_nav WINDOW w AS ( ORDER BY id @@ -2693,7 +2688,6 @@ SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER (20 rows) -- nth_value beyond reduced frame (no IGNORE NULLS) --- Tests WinGetSlotInFrame/WinGetFuncArgInFrame out-of-frame with RPR SELECT company, tdate, price, nth_value(price, 5) OVER w AS nth_5 FROM stock @@ -2810,7 +2804,7 @@ SELECT company, tdate, price, first_value(tdate) OVER w, last_value(tdate) OVER company2 | 07-10-2023 | 1300 | | (20 rows) --- SKIP TO NEXT ROW with limited frame (Ishii-san's test case) +-- SKIP TO NEXT ROW with limited frame -- Each row should produce its own match within its frame WITH data AS ( SELECT * FROM (VALUES @@ -2836,8 +2830,6 @@ WINDOW w AS ( (4 rows) -- Limited frame with absorption test --- Row 0: frame [0,2], can't see B at row 3 -> no match --- Row 1: frame [1,3], can see A A B -> should match rows 1-3 WITH frame_absorb_test AS ( SELECT * FROM (VALUES (0, 'A'), (1, 'A'), (2, 'A'), (3, 'B') @@ -3113,7 +3105,6 @@ count(*) OVER w (14 rows) -- ReScan test: LATERAL join forces WindowAgg rescan with RPR --- Tests ExecReScanWindowAgg clearing nav_slot SELECT g.x, sub.* FROM generate_series(1, 2) g(x), LATERAL ( @@ -3240,7 +3231,7 @@ SELECT match_first, match_last, match_len FROM result WHERE match_len > 0; -- JIT PREV/NEXT navigation test: 100K rows with PREV in DEFINE. -- Exercises EEOP_RPR_NAV_SET/RESTORE JIT code paths (has_rpr_nav reload) --- at scale. V-shape: price rises then falls, repeated across partition. +-- at scale. A single V: price falls to zero at the midpoint, then rises. SET jit = on; SET jit_above_cost = 0; WITH data AS ( @@ -3384,7 +3375,6 @@ WITH data AS ( (4 rows) -- nth_value beyond reduced frame with IGNORE NULLS --- Tests ignorenulls_getfuncarginframe early out-of-frame check SELECT company, tdate, price, nth_value(price, 5) IGNORE NULLS OVER w AS nth_5_in FROM stock @@ -3472,9 +3462,8 @@ WINDOW w AS ( (3 rows) -- --- last_value IGNORE NULLS with reduced frame containing all NULLs --- Exercises ignorenulls_getfuncarginframe SEEK_TAIL out-of-frame path --- when notnull_relpos >= num_reduced_frame. +-- last_value IGNORE NULLS when the reduced frame ends with NULLs +-- The search for a non-NULL value runs past the end of the reduced frame. -- CREATE TEMP TABLE rpr_nullval (id INT, val INT); INSERT INTO rpr_nullval VALUES (1, 10), (2, NULL), (3, NULL), (4, 20); @@ -3678,7 +3667,7 @@ PREV(price) CREATE TEMP TABLE rpr_consec_null (id INT, val INT); INSERT INTO rpr_consec_null VALUES (1, 100), (2, NULL), (3, NULL), (4, NULL), (5, 200), (6, 300); --- PREV(val) IS NULL succeeds for both null_slot (first row) and actual NULL +-- PREV(val) IS NULL is true for a genuine NULL in the previous row SELECT id, val, count(*) OVER w AS cnt FROM rpr_consec_null WINDOW w AS ( diff --git a/src/test/regress/expected/rpr_base.out b/src/test/regress/expected/rpr_base.out index 7af2c0b7da6..3186ceae442 100644 --- a/src/test/regress/expected/rpr_base.out +++ b/src/test/regress/expected/rpr_base.out @@ -160,10 +160,10 @@ ORDER BY dt; (5 rows) DROP TABLE stock_price; --- Auto-generated DEFINE +-- Pattern variables with no DEFINE entry CREATE TABLE rpr_auto (id INT, val INT); INSERT INTO rpr_auto VALUES (1, 10), (2, 20), (3, 30), (4, 15); --- One variable undefined (B auto-generated as "B IS TRUE") +-- B has no DEFINE entry, so it matches every row SELECT id, val, COUNT(*) OVER w as cnt FROM rpr_auto WINDOW w AS ( @@ -189,7 +189,7 @@ WINDOW w AS ( ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING PATTERN (A B C) DEFINE A AS val > 0 - -- B and C auto-generated as "B IS TRUE", "C IS TRUE" + -- B and C have no DEFINE entry, so they match every row ) ORDER BY id; id | val | cnt @@ -416,7 +416,7 @@ ORDER BY id; (3 rows) DROP TABLE rpr_complex; --- Pattern variable not in PATTERN (should be ignored) +-- Extra DEFINE variable not present in PATTERN CREATE TABLE rpr_unused (id INT); INSERT INTO rpr_unused VALUES (1), (2); -- Extra DEFINE variable @@ -465,11 +465,6 @@ INSERT INTO rpr_frame VALUES (6, 30); -- Valid frame options -- ROWS: counts physical rows (1 FOLLOWING = next 1 physical row) --- Expected result: Each row can see 1 physical row ahead --- id=1,2,3 (val=10): can see next row -> cnt=2 --- id=4,5 (val=20): can see next row -> cnt=2 --- id=6 (val=30): no next row -> cnt=1 --- Result: [2,2,2,2,2,1] SELECT id, val, COUNT(*) OVER w as cnt FROM rpr_frame WINDOW w AS ( @@ -657,8 +652,8 @@ WINDOW w AS ( ) ORDER BY id; ERROR: frame ending offset must be positive with row pattern recognition --- A non-constant frame end offset is allowed; a zero value is still rejected, --- this time at execution time (a literal cannot exercise that path). +-- A non-constant frame end offset is allowed; a zero value is rejected by the +-- same execution-time check the literal 0 above reaches. PREPARE rpr_end_offset(int8) AS SELECT id, val, COUNT(*) OVER w as cnt FROM rpr_frame @@ -812,7 +807,6 @@ ORDER BY id; 6 | 2 | 35 | 0 (6 rows) --- Expected: Pattern matching should reset for each partition -- PARTITION BY with RANGE frame SELECT id, grp, val, COUNT(*) OVER w as cnt FROM rpr_partition @@ -1264,7 +1258,6 @@ WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING PATTERN (A? A?? A) (3 rows) -- *? (zero or more, reluctant) --- Reluctant quantifier: prefer shortest match SELECT COUNT(*) OVER w FROM rpr_reluctant WINDOW w AS ( @@ -1281,7 +1274,6 @@ WINDOW w AS ( (3 rows) -- +? (one or more, reluctant) --- Reluctant quantifier: prefer shortest match SELECT COUNT(*) OVER w FROM rpr_reluctant WINDOW w AS ( @@ -1298,7 +1290,6 @@ WINDOW w AS ( (3 rows) -- ?? (zero or one, reluctant) --- Reluctant quantifier: prefer shortest match SELECT COUNT(*) OVER w FROM rpr_reluctant WINDOW w AS ( @@ -1315,7 +1306,6 @@ WINDOW w AS ( (3 rows) -- {n,}? (n or more, reluctant) --- Reluctant quantifier: prefer shortest match SELECT COUNT(*) OVER w FROM rpr_reluctant WINDOW w AS ( @@ -1332,7 +1322,6 @@ WINDOW w AS ( (3 rows) -- {n,m}? (n to m, reluctant) --- Reluctant quantifier: prefer shortest match SELECT COUNT(*) OVER w FROM rpr_reluctant WINDOW w AS ( @@ -1366,8 +1355,7 @@ WINDOW w AS ( 0 (3 rows) --- {,m}? (up to m, reluctant) - COMPLETELY UNTESTED RULE! --- Reluctant quantifier: prefer shortest match +-- {,m}? (up to m, reluctant) SELECT COUNT(*) OVER w FROM rpr_reluctant WINDOW w AS ( @@ -1541,9 +1529,9 @@ ERROR: quantifier minimum bound must not exceed maximum LINE 6: PATTERN (A{5,3}?) ^ -- Token-separated reluctant quantifiers (space between quantifier and ?) --- These may be tokenized differently by the lexer +-- Whitespace between the quantifier and "?" is insignificant: each form below +-- returns exactly what its unseparated counterpart above returns. -- * ? (token separated) --- Reluctant quantifier: prefer shortest match SELECT COUNT(*) OVER w FROM rpr_reluctant WINDOW w AS ( @@ -1560,7 +1548,6 @@ WINDOW w AS ( (3 rows) -- + ? (token separated) --- Reluctant quantifier: prefer shortest match SELECT COUNT(*) OVER w FROM rpr_reluctant WINDOW w AS ( @@ -1577,7 +1564,6 @@ WINDOW w AS ( (3 rows) -- {2,} ? (token separated) --- Reluctant quantifier: prefer shortest match SELECT COUNT(*) OVER w FROM rpr_reluctant WINDOW w AS ( @@ -1618,7 +1604,6 @@ ERROR: syntax error at or near "*" LINE 6: PATTERN (A+ *) ^ -- ? ? (parsed as ?? reluctant quantifier) --- Reluctant quantifier: prefer shortest match SELECT COUNT(*) OVER w FROM rpr_reluctant WINDOW w AS ( @@ -1828,9 +1813,9 @@ ERROR: quantifier bound must be between 1 and 2147483646 LINE 6: PATTERN (A{,2147483647}) ^ DROP TABLE rpr_bounds; --- Pattern element-count boundary at RPR_ELEMIDX_MAX (32767). Alternating --- distinct variables stop the optimizer from merging consecutive elements, so --- each "A B" pair contributes two elements; scanRPRPattern adds one FIN marker. +-- Pattern element-count boundary (maximum 32767 elements, the FIN marker +-- included). Alternating distinct variables stop the optimizer from merging +-- consecutive elements, so each "A B" pair contributes two elements. -- ECHO is silenced so the generated multi-thousand-token patterns do not flood -- the expected output. -- 16383 pairs -> 32766 + 1 FIN = 32767 = maximum, accepted. @@ -2058,7 +2043,7 @@ FROM rpr_nav t WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING PATTERN (A) DEFINE A AS NEXT(val / 0) > 0); ERROR: division by zero -- Here the null reaches the DEFINE predicate itself instead of an IS NULL --- test: an all-NULL target row would have made v IS NULL true and matched the +-- An all-NULL target row would have made v IS NULL true and matched the -- first row, so this pins the predicate side of the same behaviour. WITH t(id, v) AS (VALUES (1, 10), (2, 20)) SELECT id, count(*) OVER w AS cnt @@ -2084,7 +2069,7 @@ WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING PATTERN (A) DEFINE -- XXX Folding evaluates the argument while planning, with the current row's -- value standing in for the target row's, so this divides by zero even though --- PREV has no row to navigate to. A separate patch will deal with it. +-- PREV has no row to navigate to. WITH t(id, v) AS (VALUES (1, 10)) SELECT id, count(*) OVER w AS cnt FROM t @@ -2363,7 +2348,7 @@ SELECT id, val, count(*) OVER w AS cnt, last_value(id) OVER w AS last_id DEFINE A AS rpr_navns.prev(val) = -999) ORDER BY id; ERROR: DEFINE clause cannot contain volatile functions --- accepted: the SQL body inlines and folds to a constant, so no volatile call +-- The SQL body inlines and folds to a constant, so no volatile call -- is left for the check to find CREATE OR REPLACE FUNCTION prev(integer) RETURNS integer AS 'SELECT -999' LANGUAGE sql VOLATILE; @@ -2383,8 +2368,8 @@ SELECT id, val, count(*) OVER w AS cnt, last_value(id) OVER w AS last_id 5 | 150 | 0 | (5 rows) --- accepted: no OVER references the window, so flattening the subquery drops --- it before the check runs, the same way an unreferenced CTE is never planned +-- No OVER references the window, so flattening the subquery drops it +-- before the check runs, the same way an unreferenced CTE is never planned SELECT id FROM ( SELECT id FROM nt WINDOW w AS ( @@ -2400,15 +2385,15 @@ ORDER BY id; 5 (5 rows) --- error: OFFSET 0 keeps the subquery, so its DEFINE is checked +-- ERROR: OFFSET 0 keeps the subquery, so its DEFINE is checked SELECT id FROM ( SELECT id FROM nt WINDOW w AS ( ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING PATTERN (A+) DEFINE A AS random() > 0.5) OFFSET 0) sub; ERROR: DEFINE clause cannot contain volatile functions --- accepted: WHERE false makes the subquery rel dummy, so the planner never --- plans it and nothing looks at its DEFINE +-- WHERE false makes the subquery rel dummy, so the planner never plans it +-- and nothing looks at its DEFINE SELECT id FROM ( SELECT id FROM nt WINDOW w AS ( @@ -2419,7 +2404,7 @@ WHERE false; ---- (0 rows) --- accepted: the volatile is in a dead CASE arm that folds away, so nothing +-- The volatile is in a dead CASE arm that folds away, so nothing -- volatile is left for the check to find SELECT id FROM ( SELECT id FROM nt @@ -2437,7 +2422,7 @@ ORDER BY id; 5 (5 rows) --- error: folding can splice in a volatile that parse analysis never saw -- a +-- ERROR: folding can splice in a volatile that parse analysis never saw -- a -- STABLE function whose default argument is volatile -- and the check runs late -- enough to catch it CREATE FUNCTION rpr_off_leak(n bigint DEFAULT (random() * 5)::bigint) @@ -2447,7 +2432,7 @@ SELECT count(*) OVER w FROM generate_series(1, 100) g(v) PATTERN (A+) DEFINE A AS v > PREV(v, rpr_off_leak())); ERROR: DEFINE clause cannot contain volatile functions DROP FUNCTION rpr_off_leak(bigint); --- accepted: a UNION ALL leaf is flattened like any other subquery, so its +-- A UNION ALL leaf is flattened like any other subquery, so its -- unreferenced window goes the same way SELECT id FROM ( SELECT id FROM nt @@ -2470,7 +2455,7 @@ SELECT id FROM ( 5 (10 rows) --- accepted: an unreferenced CTE is never planned, so nothing looks at its +-- An unreferenced CTE is never planned, so nothing looks at its -- DEFINE WITH unused AS ( SELECT id FROM nt @@ -2483,7 +2468,7 @@ SELECT 1; 1 (1 row) --- error: referencing it plans the CTE, and the check reaches the DEFINE there +-- ERROR: referencing it plans the CTE, and the check reaches the DEFINE there WITH used AS ( SELECT id FROM nt WINDOW w AS ( @@ -4184,11 +4169,9 @@ SELECT pg_get_viewdef('rpr_quant_n_plus_v'::regclass); -- ============================================================ CREATE TABLE rpr_glue (id INT, val INT); INSERT INTO rpr_glue VALUES (1, 5), (2, 8), (3, 9), (4, -1), (5, 6), (6, -2); --- Quantifier glued to the alternation operator '|' without a space (0059). --- The lexer glues the trailing '|' into one Op token; the grammar reattaches it --- as the lowest-precedence alternation once the surrounding sequence is built. --- Deparse is canonical, so the glued, spaced, and mixed-spacing forms all --- reduce to the same PATTERN -- one deparse per shape proves the parse tree. +-- Quantifier glued to the alternation operator '|' without a space. The +-- lexer glues the trailing '|' into one Op token; the grammar reattaches it as +-- the lowest-precedence alternation once the surrounding sequence is built. -- Op-char quantifiers (*, +, ?, *?, +?, ??) glued to '|'. CREATE VIEW rpr_dp_op AS SELECT count(*) OVER w1 AS w1, count(*) OVER w2 AS w2, count(*) OVER w3 AS w3, @@ -4552,7 +4535,7 @@ WINDOW w AS ( ERROR: pattern variable qualified expression "b.val" is not supported in DEFINE clause LINE 7: DEFINE A AS B.val > 0 ^ --- DEFINE-only variable qualified name: still a pattern variable, not a range variable +-- DEFINE-only variable used as a qualifier SELECT COUNT(*) OVER w FROM rpr_err WINDOW w AS ( @@ -4611,7 +4594,6 @@ WINDOW w AS ( 3 | 25 | 1 (3 rows) --- Expected: rows where (items).amount > 10 form matches; counts reflect frame size -- Composite type field selection (qualified forms): the ColumnRef portion ("A.items" or -- "rpr_composite.items") is what gets quoted; the trailing ".amount" lives in -- the surrounding A_Indirection node and is not visible to the pre-check. @@ -4699,7 +4681,7 @@ WINDOW w AS ( ERROR: cannot use subquery in DEFINE expression LINE 7: DEFINE A AS val > (SELECT max(val) FROM rpr_err) ^ --- Pattern variable not used (should work, extra vars ignored) +-- DEFINE variables that do not appear in PATTERN SELECT id, val, COUNT(*) OVER w as cnt FROM rpr_err WINDOW w AS ( @@ -6210,7 +6192,6 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING -- ============================================================ -- Tests absorption marker display in EXPLAIN output -- Markers: ~ = branch element, # = comparison point --- Files: explain.c (append_rpr_quantifier, deparse_rpr_pattern) -- Simple VAR: A+ -> a+# (comparison point) EXPLAIN (COSTS OFF) SELECT COUNT(*) OVER w FROM rpr_plan @@ -6548,7 +6529,6 @@ WINDOW w AS ( -- Absorption Analysis Tests -- ============================================================ -- Tests context absorption optimization (O(n^2) -> O(n)) --- Files: rpr.c (computeAbsorbability) -- Simple Absorbable Pattern: A+ B -- Pattern starts with unbounded VAR SELECT id, val, COUNT(*) OVER w as cnt @@ -7103,7 +7083,7 @@ ORDER BY id; -- Tests for optimization edge cases and fallback behavior CREATE TABLE rpr_fallback (id INT, val INT); INSERT INTO rpr_fallback VALUES (1, 10), (2, 20); --- Test: min quantifier overflow causes optimization fallback (min == max case) +-- Min quantifier overflow causes optimization fallback (min == max case) EXPLAIN (COSTS OFF) SELECT COUNT(*) OVER w FROM rpr_fallback WINDOW w AS ( @@ -7122,8 +7102,7 @@ WINDOW w AS ( -> Seq Scan on rpr_fallback (6 rows) --- Expected: Fallback - pattern not merged due to min overflow (4000000000 > INT32_MAX) --- Test: max-only quantifier overflow causes optimization fallback +-- Max-only quantifier overflow causes optimization fallback EXPLAIN (COSTS OFF) SELECT COUNT(*) OVER w FROM rpr_fallback WINDOW w AS ( @@ -7142,8 +7121,7 @@ WINDOW w AS ( -> Seq Scan on rpr_fallback (6 rows) --- Expected: Fallback - min OK (2*1=2), but max overflow (2*2000000000 > INT32_MAX) --- Test: max quantifier exceeds valid range (2147483647 = INT_MAX, limit is 2147483646) +-- Max quantifier exceeds valid range (2147483647 = INT_MAX, limit is 2147483646) EXPLAIN (COSTS OFF) SELECT COUNT(*) OVER w FROM rpr_fallback WINDOW w AS ( @@ -7155,7 +7133,7 @@ WINDOW w AS ( ERROR: quantifier bound must be between 1 and 2147483646 LINE 6: PATTERN ((A{2000000000,2147483647}){2}) ^ --- Test: nested unbounded with large min causes overflow fallback +-- Nested unbounded with large min causes overflow fallback EXPLAIN (COSTS OFF) SELECT COUNT(*) OVER w FROM rpr_fallback WINDOW w AS ( @@ -7174,8 +7152,7 @@ WINDOW w AS ( -> Seq Scan on rpr_fallback (6 rows) --- Expected: Fallback - min overflow (2000000000 * 2000000000 > INT32_MAX) --- Test: prefix mismatch causes optimization fallback +-- Prefix mismatch causes optimization fallback EXPLAIN (COSTS OFF) SELECT COUNT(*) OVER w FROM rpr_fallback WINDOW w AS ( @@ -7194,8 +7171,7 @@ WINDOW w AS ( -> Seq Scan on rpr_fallback (6 rows) --- Expected: Fallback - prefix elements don't match GROUP content --- Test: consecutive VAR merge whose min sum is exactly INF causes fallback. +-- Consecutive VAR merge whose min sum is exactly INF causes fallback. -- 1073741824 + 1073741823 = 2147483647 = INT32_MAX = RPR_QUANTITY_INF. -- Merging would yield a VAR with min == INF, so the merge must fall back and -- leave the two VARs unmerged (mirrors the multiply path's >= INF guard). @@ -7217,8 +7193,7 @@ WINDOW w AS ( -> Seq Scan on rpr_fallback (6 rows) --- Expected: Fallback - VARs not merged (min sum 2147483647 == INF) --- Test: one more than that sum does not fit in int32. The fallback looks the +-- One more than that sum does not fit in int32. The fallback looks the -- same as the case above; this one reaches the overflow check instead of the -- >= INF comparison. EXPLAIN (COSTS OFF) @@ -7239,7 +7214,7 @@ WINDOW w AS ( -> Seq Scan on rpr_fallback (6 rows) --- Test: VAR merge falls back when the max sum lands exactly on INF. +-- VAR merge falls back when the max sum lands exactly on INF. EXPLAIN (COSTS OFF) SELECT COUNT(*) OVER w FROM rpr_fallback WINDOW w AS ( @@ -7258,7 +7233,7 @@ WINDOW w AS ( -> Seq Scan on rpr_fallback (6 rows) --- Test: one below that sum is the largest max the merge may keep. +-- One below that sum is the largest max the merge may keep. EXPLAIN (COSTS OFF) SELECT COUNT(*) OVER w FROM rpr_fallback WINDOW w AS ( @@ -7277,7 +7252,7 @@ WINDOW w AS ( -> Seq Scan on rpr_fallback (6 rows) --- Test: one above that does not fit in int32; the overflow check rejects it. +-- One above that does not fit in int32; the overflow check rejects it. EXPLAIN (COSTS OFF) SELECT COUNT(*) OVER w FROM rpr_fallback WINDOW w AS ( @@ -7296,7 +7271,7 @@ WINDOW w AS ( -> Seq Scan on rpr_fallback (6 rows) --- Test: an operand that is already unbounded still merges. +-- An operand that is already unbounded still merges. EXPLAIN (COSTS OFF) SELECT COUNT(*) OVER w FROM rpr_fallback WINDOW w AS ( @@ -7315,7 +7290,7 @@ WINDOW w AS ( -> Seq Scan on rpr_fallback (6 rows) --- Test: consecutive GROUP merge whose min sum is exactly INF causes fallback. +-- Consecutive GROUP merge whose min sum is exactly INF causes fallback. EXPLAIN (COSTS OFF) SELECT COUNT(*) OVER w FROM rpr_fallback WINDOW w AS ( @@ -7334,8 +7309,7 @@ WINDOW w AS ( -> Seq Scan on rpr_fallback (6 rows) --- Expected: Fallback - GROUPs not merged (min sum 2147483647 == INF) --- Test: consecutive GROUP merge whose max sum is exactly INF causes fallback, +-- Consecutive GROUP merge whose max sum is exactly INF causes fallback, -- where one less merges. Without the guard the merged max would alias INF and -- a bounded pattern would become unbounded. EXPLAIN (COSTS OFF) @@ -7374,7 +7348,7 @@ WINDOW w AS ( -> Seq Scan on rpr_fallback (6 rows) --- Test: the prefix merge adds one iteration, so it declines a min already at +-- The prefix merge adds one iteration, so it declines a min already at -- INF - 1 and a max already at INF - 1; one less than either merges. EXPLAIN (COSTS OFF) SELECT COUNT(*) OVER w FROM rpr_fallback @@ -7448,7 +7422,7 @@ WINDOW w AS ( -> Seq Scan on rpr_fallback (6 rows) --- Test: the suffix merge has the same boundary as the prefix merge. +-- The suffix merge has the same boundary as the prefix merge. EXPLAIN (COSTS OFF) SELECT COUNT(*) OVER w FROM rpr_fallback WINDOW w AS ( @@ -7490,7 +7464,6 @@ DROP TABLE rpr_fallback; -- Planner Integration Tests -- ============================================================ -- Tests full planning pipeline and WindowAgg plan node creation --- Files: planner.c, createplan.c CREATE TABLE rpr_planner (id INT, category VARCHAR(10), val INT); INSERT INTO rpr_planner VALUES (1, 'A', 10), (2, 'A', 20), (3, 'A', 30), @@ -7624,27 +7597,8 @@ ORDER BY id; 9 | C | 90 | 0 (9 rows) --- Window with Aggregate Functions -SELECT category, - COUNT(*) OVER w as window_cnt, - COUNT(*) as agg_cnt -FROM rpr_planner -WINDOW w AS ( - PARTITION BY category - ORDER BY id - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - PATTERN (A+) - DEFINE A AS val > 0 -) -GROUP BY category -ORDER BY category; -ERROR: syntax error at or near "GROUP" -LINE 12: GROUP BY category - ^ --- (GROUP BY after WINDOW clause is not valid SQL syntax) -- ============================================================ -- Subquery and CTE Tests --- Files: planner.c, prepjointree.c -- ============================================================ -- Tests RPR with subqueries and CTEs -- RPR in Subquery (FROM clause) @@ -7761,7 +7715,6 @@ SELECT * FROM cte2 ORDER BY id; -- ============================================================ -- JOIN Tests --- Files: prepjointree.c, setrefs.c -- ============================================================ -- Tests RPR with JOINs and multiple table references CREATE TABLE rpr_join1 (id INT, val1 INT); @@ -7877,7 +7830,6 @@ ORDER BY id; DROP TABLE rpr_join1, rpr_join2; -- ============================================================ -- Complex Expression Tests --- Files: createplan.c, setrefs.c -- ============================================================ -- Tests complex target list expressions CREATE TABLE rpr_target (id INT, val INT); @@ -7996,7 +7948,6 @@ ORDER BY row_id; DROP TABLE rpr_target; -- ============================================================ -- Set Operations Tests --- Files: planner.c -- ============================================================ -- Tests RPR with UNION, INTERSECT, EXCEPT CREATE TABLE rpr_set1 (id INT, val INT); @@ -8112,7 +8063,6 @@ ORDER BY id; DROP TABLE rpr_set1, rpr_set2; -- ============================================================ -- Sorting and Grouping Tests --- Files: planner.c, createplan.c -- ============================================================ -- Tests RPR interaction with sorting and grouping CREATE TABLE rpr_sort (id INT, category VARCHAR(10), val INT); @@ -8397,7 +8347,7 @@ DROP TABLE rpr_stress; -- Tests for error conditions in rpr.c CREATE TABLE rpr_errors (id INT, val INT); INSERT INTO rpr_errors VALUES (1, 10), (2, 20); --- Test: DEFINE variable not in PATTERN (error) +-- DEFINE variable not in PATTERN (error) SELECT id, val, COUNT(*) OVER w FROM rpr_errors WINDOW w AS ( ORDER BY id @@ -8409,8 +8359,7 @@ WINDOW w AS ( ERROR: DEFINE variable "b" is not used in PATTERN LINE 7: B AS TRUE ^ --- Expected: Error - B is not used in PATTERN --- Test: 240 variables in PATTERN and DEFINE (boundary - should succeed) +-- 240 variables in PATTERN and DEFINE (boundary - should succeed) SELECT COUNT(*) OVER w FROM rpr_errors WINDOW w AS ( ORDER BY id @@ -8459,7 +8408,6 @@ WINDOW w AS ( 0 (2 rows) --- Expected: Success - exactly at RPR_VARID_MAX boundary -- ERROR: 241 variables in PATTERN, 240 in DEFINE (exceeds limit with implicit TRUE) SELECT COUNT(*) OVER w FROM rpr_errors WINDOW w AS ( @@ -8508,8 +8456,7 @@ ERROR: too many row pattern variables LINE 17: V241) ^ DETAIL: The maximum number of row pattern variables is 240. --- Pattern nesting-depth boundary at RPR_DEPTH_MAX (255; effective maximum --- 254, since scanRPRPatternRecursive() rejects depth >= RPR_DEPTH_MAX). +-- Pattern nesting-depth boundary: 254 levels are accepted, 255 rejected. -- Reluctant quantifiers are not subject to quantifier multiplication, so the -- nesting survives optimization and still reaches the depth check. -- ECHO is silenced so the generated deeply nested patterns do not flood the @@ -8529,7 +8476,7 @@ DROP TABLE rpr_errors; -- ============================================================ -- Basic Pattern Matching -- ============================================================ --- Test: A? (optional, greedy) +-- A? (optional, greedy) SELECT id, val, count(*) OVER w AS c FROM rpr_plan WINDOW w AS ( @@ -8553,7 +8500,7 @@ WINDOW w AS ( 10 | 100 | 1 (10 rows) --- Test: A{2} (exact count) +-- A{2} (exact count) SELECT id, val, count(*) OVER w AS c FROM rpr_plan WINDOW w AS ( @@ -8577,7 +8524,7 @@ WINDOW w AS ( 10 | 100 | 0 (10 rows) --- Test: A{1,3} (bounded range, greedy) +-- A{1,3} (bounded range, greedy) SELECT id, val, count(*) OVER w AS c FROM rpr_plan WINDOW w AS ( @@ -8601,7 +8548,7 @@ WINDOW w AS ( 10 | 100 | 0 (10 rows) --- Test: A | B (simple alternation) +-- A | B (simple alternation) SELECT id, val, count(*) OVER w AS c FROM rpr_plan WINDOW w AS ( @@ -8625,7 +8572,7 @@ WINDOW w AS ( 10 | 100 | 1 (10 rows) --- Test: A | B | C (three-way alternation) +-- A | B | C (three-way alternation) SELECT id, val, count(*) OVER w AS c FROM rpr_plan WINDOW w AS ( @@ -8649,7 +8596,7 @@ WINDOW w AS ( 10 | 100 | 1 (10 rows) --- Test: A B C (concatenation) +-- A B C (concatenation) SELECT id, val, count(*) OVER w AS c FROM rpr_plan WINDOW w AS ( @@ -8673,7 +8620,7 @@ WINDOW w AS ( 10 | 100 | 0 (10 rows) --- Test: A B? C (optional middle) +-- A B? C (optional middle) SELECT id, val, count(*) OVER w AS c FROM rpr_plan WINDOW w AS ( @@ -8697,7 +8644,7 @@ WINDOW w AS ( 10 | 100 | 0 (10 rows) --- Test: (A B)+ (grouped quantifier) +-- (A B)+ (grouped quantifier) SELECT id, val, count(*) OVER w AS c FROM rpr_plan WINDOW w AS ( @@ -8721,7 +8668,7 @@ WINDOW w AS ( 10 | 100 | 0 (10 rows) --- Test: (A | B)+ C (alternation with quantifier) +-- (A | B)+ C (alternation with quantifier) SELECT id, val, count(*) OVER w AS c FROM rpr_plan WINDOW w AS ( @@ -8745,9 +8692,7 @@ WINDOW w AS ( 10 | 100 | 0 (10 rows) --- Test: (A+ | (A | B)+)* - nested alternation inside quantified group --- Previously caused infinite recursion in alternation handling when the inner --- BEGIN(+)'s skip jump was followed as an ALT branch pointer. +-- (A+ | (A | B)+)* - nested alternation inside quantified group SELECT id, flags, first_value(id) OVER w AS match_start, last_value(id) OVER w AS match_end FROM (VALUES (1, ARRAY['A', 'B']), @@ -8773,8 +8718,8 @@ WINDOW w AS ( -- ============================================================ -- Pathological Patterns -- ============================================================ --- These patterns previously caused issues. Now optimized or handled safely. --- Test: (A*)* - nested unbounded (optimized to A*) +-- Nested unbounded quantifiers that the optimizer collapses. +-- (A*)* - nested unbounded (optimized to A*) SELECT v, count(*) OVER w AS c FROM (SELECT generate_series(1, 5) v) WINDOW w AS ( @@ -8793,7 +8738,7 @@ WINDOW w AS ( 5 | 0 (5 rows) --- Test: (A*)+ - inner nullable (optimized to A*) +-- (A*)+ - inner nullable (optimized to A*) SELECT v, count(*) OVER w AS c FROM (SELECT generate_series(1, 5) v) WINDOW w AS ( @@ -8812,7 +8757,7 @@ WINDOW w AS ( 5 | 0 (5 rows) --- Test: (A+)* - outer nullable (optimized to A*) +-- (A+)* - outer nullable (optimized to A*) SELECT v, count(*) OVER w AS c FROM (SELECT generate_series(1, 5) v) WINDOW w AS ( @@ -8831,7 +8776,7 @@ WINDOW w AS ( 5 | 0 (5 rows) --- Test: (A+)+ - both require match (optimized to A+) +-- (A+)+ - both require match (optimized to A+) SELECT v, count(*) OVER w AS c FROM (SELECT generate_series(1, 5) v) WINDOW w AS ( @@ -8850,7 +8795,7 @@ WINDOW w AS ( 5 | 0 (5 rows) --- Test: (((A)*)*)* - triple nested (optimized to A*) +-- (((A)*)*)* - triple nested (optimized to A*) SELECT v, count(*) OVER w AS c FROM (SELECT generate_series(1, 3) v) WINDOW w AS ( diff --git a/src/test/regress/expected/rpr_explain.out b/src/test/regress/expected/rpr_explain.out index 00056912e6c..19025127b8d 100644 --- a/src/test/regress/expected/rpr_explain.out +++ b/src/test/regress/expected/rpr_explain.out @@ -264,9 +264,8 @@ WINDOW w AS ( -> Function Scan on generate_series s (actual rows=20.00 loops=1) (9 rows) --- Regression test: Sequential alternations at same depth +-- Sequential alternations at the same depth -- Verifies that "((B | C) (D | E))" correctly outputs as "(b | c) (d | e)" --- Previously failed due to missing parentheses on ALT depth decrease CREATE VIEW rpr_ev_basic_deparse_seqalt AS SELECT count(*) OVER w FROM generate_series(1, 30) AS s(v) @@ -447,7 +446,7 @@ WINDOW w AS ( -> Seq Scan on rpr_nfa_test (actual rows=100.00 loops=1) (9 rows) --- Complex pattern with high state count +-- Complex pattern: quantified sequence with a star element CREATE VIEW rpr_ev_state_complex AS SELECT count(*) OVER w FROM generate_series(1, 100) AS s(v) @@ -532,7 +531,6 @@ WINDOW w AS ( (9 rows) -- State explosion pattern - many alternations --- Pattern (A|B)(A|B)(A|B)(A|B) can create many parallel states CREATE VIEW rpr_ev_state_explosion AS SELECT count(*) OVER w FROM generate_series(1, 100) AS s(v) @@ -910,7 +908,7 @@ WINDOW w AS ( -> Function Scan on generate_series s (actual rows=1000.00 loops=1) (9 rows) --- (A{2,})* must NOT flatten to a* (H-1): counts {0} UNION [2, INF) leave 1 +-- (A{2,})* must NOT flatten to a*: counts {0} UNION [2, INF) leave 1 -- unreachable. The planner keeps it as (a{2,})*, not a*. CREATE VIEW rpr_ev_nested_quant_no_flatten AS SELECT count(*) OVER w @@ -1191,7 +1189,7 @@ WINDOW w AS ( -> Function Scan on generate_series s (actual rows=100.00 loops=1) (9 rows) --- High context absorption - unbounded group +-- Unbounded group followed by a trailing element CREATE VIEW rpr_ev_ctx_absorb_group AS SELECT count(*) OVER w FROM generate_series(1, 100) AS s(v) @@ -1845,7 +1843,7 @@ WINDOW w AS ( -> Seq Scan on rpr_nfa_test (actual rows=100.00 loops=1) (9 rows) --- Variable length matches - min/max/avg differ +-- Unbounded quantifier over a repeating cycle CREATE VIEW rpr_ev_mlen_variable AS SELECT count(*) OVER w FROM generate_series(1, 100) AS s(v) @@ -3517,7 +3515,7 @@ WINDOW w AS ( -> Function Scan on generate_series s (actual rows=1000.00 loops=1) (9 rows) --- High state merge ratio +-- Alternation with plus quantifier over 500 rows CREATE VIEW rpr_ev_perf_high_merge AS SELECT count(*) OVER w FROM generate_series(1, 500) AS s(v) @@ -4719,7 +4717,8 @@ WINDOW w AS ( (8 rows) -- Unit (1,1) group as an alternation branch (emits no BEGIN/END) --- Pattern: ((A B) | C) - control: takes the variable path, not deparse_rpr_group +-- Pattern: ((A B) | C) - control: a {1,1} group emits no BEGIN/END, so the +-- branch deparses as a plain sequence CREATE VIEW rpr_ev_alt_grp_unit AS SELECT count(*) OVER w FROM generate_series(1, 20) AS s(v) @@ -4757,7 +4756,8 @@ WINDOW w AS ( (9 rows) -- Quantified variable as the first alternation branch --- Pattern: (A+ | C) - control: deparse_rpr_var already opens the leading paren +-- Pattern: (A+ | C) - control: a quantified variable as the leading branch; +-- the ALT supplies the enclosing parentheses CREATE VIEW rpr_ev_alt_var_first AS SELECT count(*) OVER w FROM generate_series(1, 20) AS s(v) @@ -6034,7 +6034,7 @@ WINDOW w AS ( -> Function Scan on generate_series s (actual rows=500.00 loops=1) (9 rows) --- High skip count scenario +-- Sparse five-element pattern over 500 rows CREATE VIEW rpr_ev_scale_high_skip AS SELECT count(*) OVER w FROM generate_series(1, 500) AS s(v) @@ -6247,7 +6247,7 @@ WINDOW w AS ( PATTERN (A+) DEFINE A AS v > PREV(v, $1) ); --- No navigation function: offset 0 +-- No navigation function EXPLAIN (COSTS OFF) SELECT count(*) OVER w FROM generate_series(1,10) s(v) WINDOW w AS ( @@ -6263,7 +6263,7 @@ WINDOW w AS ( -> Function Scan on generate_series s (4 rows) --- NEXT only: no backward navigation, offset 0 +-- NEXT only: no backward navigation EXPLAIN (COSTS OFF) SELECT count(*) OVER w FROM generate_series(1,10) s(v) WINDOW w AS ( @@ -6393,7 +6393,7 @@ WINDOW w AS ( -> Function Scan on generate_series s (5 rows) --- FIRST(v): retain all (references match_start row) +-- FIRST(v) with no offset (references the match start row) EXPLAIN (COSTS OFF) SELECT count(*) OVER w FROM generate_series(1,10) s(v) WINDOW w AS ( diff --git a/src/test/regress/expected/rpr_integration.out b/src/test/regress/expected/rpr_integration.out index e7639273a21..d8ccf291060 100644 --- a/src/test/regress/expected/rpr_integration.out +++ b/src/test/regress/expected/rpr_integration.out @@ -162,7 +162,7 @@ ORDER BY id; -- are subject to the dedup path; distinct named windows are always -- kept separate regardless of equivalence. -- Non-RPR baseline: two inline windows with identical spec are --- deduped by the planner into a single WindowAgg node, confirming +-- deduped by the parser into a single WindowAgg node, confirming -- that the dedup path is active for non-RPR windows. EXPLAIN (COSTS OFF) SELECT diff --git a/src/test/regress/expected/rpr_nfa.out b/src/test/regress/expected/rpr_nfa.out index f49d7e4963a..b133a0c99f6 100644 --- a/src/test/regress/expected/rpr_nfa.out +++ b/src/test/regress/expected/rpr_nfa.out @@ -4,8 +4,8 @@ -- ============================================================ -- -- This test suite validates the NFA (Non-deterministic Finite --- Automaton) runtime execution engine in nodeWindowAgg.c, --- focusing on update_reduced_frame and related functions. +-- Automaton) runtime execution engine in execRPR.c, driven by +-- update_reduced_frame() in nodeWindowAgg.c. -- -- Test Strategy: -- Diagonal pattern style using ARRAY flags to explicitly @@ -31,6 +31,7 @@ -- DEFINE Special Cases -- Absorption Dynamic Flags -- Zero-Consumption Cycle Detection +-- Standard Clause 7: Formal Pattern Matching Rules -- -- Responsibility: -- - NFA runtime execution paths @@ -1259,8 +1260,10 @@ WINDOW w AS ( -- the engine must prefer the fast-forward (exit) path for reluctant -- groups and suppress longer matches once exit reaches FIN, mirroring the -- sibling min<=count=2 boundary and single-quantifier controls localize the behaviour: only --- the all-reluctant case (rr) should differ. +-- min>=2 boundary and single-quantifier controls localize the behaviour: the +-- inner quantifier decides whether a row is consumed, so every column whose +-- body is reluctant stays at zero, and the two with a greedy body differ by +-- their outer quantifier -- gg takes the longest match, rg one row. WITH t(id, isa) AS (VALUES (1, true), (2, true), (3, true), (4, false)) SELECT id, count(*) OVER gg AS gg, -- (A?)+ greedy / greedy @@ -2378,8 +2381,7 @@ WINDOW w AS ( (4 rows) -- A+? B (reluctant plus): exits A at first B availability --- (Same scenario as greedy-vs-reluctant comparison above; retained for --- standalone quantifier coverage alongside A{1,3}? and A{2,3}? below) +-- (Standalone reluctant-plus case; compare with A{1,3}? and A{3,5}? below) WITH test_reluctant_plus AS ( SELECT * FROM (VALUES (1, ARRAY['A','_']), @@ -2473,7 +2475,7 @@ WINDOW w AS ( 6 | {B} | | (6 rows) --- Nested quantifier flattening must not widen the matching language (H-1). +-- Nested quantifier flattening must not widen the matching language. -- (A{k,})* with k >= 2 reaches repetition counts {0} UNION [k, INF); the gap -- 1..k-1 is unreachable, so it must NOT collapse to A*. An isolated single A -- must yield an EMPTY match (count 0), not a length-1 match. @@ -2998,9 +3000,8 @@ WINDOW w AS ( 9 | {C} | 9 | 9 (9 rows) --- altPriority replacement (A B C | D) --- D branch (higher altPriority) matches first at row 1, --- then A B C branch (lower altPriority) replaces it at row 3. +-- Branch preference (A B C | D): D completes first at row 1, but the +-- earlier-written A B C branch is preferred and replaces the match at row 3. WITH test_alt_replace AS ( SELECT * FROM (VALUES (1, ARRAY['A', 'D']), @@ -4985,10 +4986,6 @@ WINDOW w AS ( (5 rows) -- N FOLLOWING + SKIP TO NEXT ROW: overlapping matches bounded by frame --- Row 1: frame [1,4], A(1-3) B(4) -> match --- Row 2: frame [2,5], A(2-3) B(4) -> match --- Row 3: frame [3,6], A(3) B(4) -> match --- Row 5: frame [5,6], A(5) B(6) -> match WITH test_n_skip_next AS ( SELECT * FROM (VALUES (1, ARRAY['A']), @@ -6136,7 +6133,6 @@ WINDOW w AS ( -- ((A|B){1,2}) greedy: lexicographic > length -- Standard example: preferment AA, AB, A, BA, BB, B --- Single A preferred over B-starting longer match WITH test_quant_lex_greedy AS ( SELECT * FROM (VALUES (1, ARRAY['A','B']), @@ -6447,8 +6443,6 @@ WINDOW w AS ( -- (A?){0,3}: min=0, nullable inner. -- A never matches but A? matches empty, satisfying min=0 immediately. --- NFA reports 3 length-0 matches (one per row); first_value / last_value --- are NULL because the window frame for an empty match has no rows. WITH test_728_min0 AS ( SELECT * FROM (VALUES (1, ARRAY['B']), @@ -6477,7 +6471,6 @@ WINDOW w AS ( -- (A?){1,3}: min=1, nullable inner. -- A never matches; one empty iteration satisfies min=1. --- NFA reports 3 length-0 matches; first/last_value NULL over empty frame. WITH test_728_min1 AS ( SELECT * FROM (VALUES (1, ARRAY['B']), @@ -6506,7 +6499,6 @@ WINDOW w AS ( -- (A?){2,3}: min=2, nullable inner. Per ISO/IEC 19075-5 7.2.8 STR06 = (STRE STRE) -- is valid: two empty iterations satisfy min=2. --- NFA reports 3 length-0 matches; first/last_value NULL over empty frame. WITH test_728_min2 AS ( SELECT * FROM (VALUES (1, ARRAY['B']), @@ -6534,9 +6526,6 @@ WINDOW w AS ( (3 rows) -- (A?){2,3} mixed: some rows match A, some don't --- Rows 1-2: A matches, greedy takes 2 -> min satisfied (real match) --- Row 3: A doesn't match, two empty iterations satisfy min=2 (length-0 match) --- Row 4: A matches 1 real iter + 1 ff empty exit -> match 4-4 WITH test_728_min2_mixed AS ( SELECT * FROM (VALUES (1, ARRAY['A']), @@ -6635,8 +6624,6 @@ WINDOW w AS ( -- fires as a parallel exit path (EXIT ONLY, no greedy/reluctant loop). -- Data: alternating A, B rows (6 rows) -- Greedy: each row gets the longest match from its starting position. --- Row 1: 3 iters (A@1,B@2)(A@3,B@4)(A@5,B@6) -> 1-6 --- Row 5: 1 real iter + 1 ff empty exit -> 5-6 WITH test_728_multi_body AS ( SELECT * FROM (VALUES (1, ARRAY['A']), @@ -6671,7 +6658,6 @@ WINDOW w AS ( (6 rows) -- (A? B?){2,3}: pure empty body (nothing matches A or B). --- NFA reports 3 length-0 matches; first/last_value NULL over empty frame. WITH test_728_multi_empty AS ( SELECT * FROM (VALUES (1, ARRAY['C']), @@ -6700,9 +6686,6 @@ WINDOW w AS ( (3 rows) -- (A? B?){2,3}: mixed real and empty iterations --- Row 1: iter1 real (A@1,B@2), iter2 at row 3 empty -> ff exit, match 1-2 --- Row 3: C doesn't match A or B -> NULL --- Row 4: iter1 real (A@4,B@5), iter2 at end empty -> ff exit, match 4-5 WITH test_728_multi_mixed AS ( SELECT * FROM (VALUES (1, ARRAY['A']), diff --git a/src/test/regress/sql/rpr.sql b/src/test/regress/sql/rpr.sql index d2b858ca3a3..6bb4adfe320 100644 --- a/src/test/regress/sql/rpr.sql +++ b/src/test/regress/sql/rpr.sql @@ -346,7 +346,7 @@ SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER UPDOWN AS price > PREV(price) AND price > NEXT(price) ); --- PREV returns NULL at partition's first row (null_slot path) +-- PREV returns NULL at the partition's first row (no earlier row to fetch) SELECT company, tdate, price, count(*) OVER w FROM stock WINDOW w AS ( @@ -359,7 +359,7 @@ WINDOW w AS ( REST AS PREV(price) IS NOT NULL ); --- NEXT returns NULL at partition's last row (null_slot path) +-- NEXT returns NULL at the partition's last row (no later row to fetch) SELECT company, tdate, price, count(*) OVER w FROM stock WINDOW w AS ( @@ -647,8 +647,8 @@ WINDOW w AS ( ); DROP SEQUENCE rpr_seq; --- A volatile DEFINE is now rejected in the planner, not at parse time, so a --- view that hides one is created successfully and only errors when read. +-- A volatile DEFINE is rejected in the planner, so a view that hides one is +-- created successfully and errors only when read. CREATE TEMP VIEW rpr_volatile_view AS SELECT company, tdate, price, count(*) OVER w FROM stock @@ -881,8 +881,8 @@ EXECUTE test_prev_offset(NULL); DEALLOCATE test_prev_offset; -- 2-arg PREV/NEXT: host variable with positive value. A generic plan keeps --- the parameter as a Param, which is what reaches the RPR_NAV_OFFSET_NEEDS_EVAL --- path; a custom plan would fold it to a Const and settle the reach at init. +-- the parameter as a Param, so the offset is resolved at run time; a custom +-- plan would fold it to a constant and settle the reach at init. SET plan_cache_mode = force_generic_plan; PREPARE test_prev_offset(int8) AS SELECT company, tdate, price, first_value(price) OVER w, count(*) OVER w @@ -927,12 +927,9 @@ WINDOW w AS ( DEFINE A AS price > PREV(price, 1) AND price < NEXT(price, 1) ); --- Pass-by-ref types: two PREV calls targeting different positions. --- Verifies that datumCopy in RESTORE prevents dangling pointers when --- nav_slot is re-fetched for the second navigation. --- tdate::text gives distinct text values per row (e.g. '07-01-2023'). --- B matches when 1-back date text > 2-back date text (always true for --- ascending dates), so B+ extends the full partition after A. +-- Pass-by-ref types: two PREV calls targeting different positions, so the +-- first navigation result must survive the second fetch. B compares the +-- 1-back and 2-back tdate text. SELECT company, tdate, tdate::text AS tdate_text, first_value(tdate::text) OVER w, last_value(tdate::text) OVER w, count(*) OVER w FROM stock @@ -1037,7 +1034,7 @@ FROM rpr_nav WINDOW w AS ( ); -- SKIP TO NEXT ROW with FIRST(val) = LAST(val): overlapping match attempts. --- With ONE ROW PER MATCH, each row shows only its first match result. +-- Each row reports only the match that starts at it. SELECT id, val, first_value(id) OVER w AS mf, last_value(id) OVER w AS ml FROM rpr_nav WINDOW w AS ( ORDER BY id @@ -1138,8 +1135,6 @@ DROP TABLE rpr_names; -- Compound navigation: PREV(FIRST(val), M) -- rpr_nav: (1,10),(2,20),(3,30),(4,10),(5,50),(6,10) -- PREV(FIRST(val), 1): target = match_start + 0 - 1 = match_start - 1 --- At match_start=1: target=0 -> out of range -> NULL --- At match_start=3: target=2(val=20) -> 20 > 0 -> true SELECT id, val, first_value(id) OVER w AS mf, count(*) OVER w AS cnt FROM rpr_nav WINDOW w AS ( ORDER BY id @@ -1466,7 +1461,6 @@ SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER ); -- nth_value beyond reduced frame (no IGNORE NULLS) --- Tests WinGetSlotInFrame/WinGetFuncArgInFrame out-of-frame with RPR SELECT company, tdate, price, nth_value(price, 5) OVER w AS nth_5 FROM stock @@ -1514,7 +1508,7 @@ SELECT company, tdate, price, first_value(tdate) OVER w, last_value(tdate) OVER B AS price > 100 ); --- SKIP TO NEXT ROW with limited frame (Ishii-san's test case) +-- SKIP TO NEXT ROW with limited frame -- Each row should produce its own match within its frame WITH data AS ( SELECT * FROM (VALUES @@ -1533,8 +1527,6 @@ WINDOW w AS ( ); -- Limited frame with absorption test --- Row 0: frame [0,2], can't see B at row 3 -> no match --- Row 1: frame [1,3], can see A A B -> should match rows 1-3 WITH frame_absorb_test AS ( SELECT * FROM (VALUES (0, 'A'), (1, 'A'), (2, 'A'), (3, 'B') @@ -1678,7 +1670,6 @@ count(*) OVER w ); -- ReScan test: LATERAL join forces WindowAgg rescan with RPR --- Tests ExecReScanWindowAgg clearing nav_slot SELECT g.x, sub.* FROM generate_series(1, 2) g(x), LATERAL ( @@ -1773,7 +1764,7 @@ SELECT match_first, match_last, match_len FROM result WHERE match_len > 0; -- JIT PREV/NEXT navigation test: 100K rows with PREV in DEFINE. -- Exercises EEOP_RPR_NAV_SET/RESTORE JIT code paths (has_rpr_nav reload) --- at scale. V-shape: price rises then falls, repeated across partition. +-- at scale. A single V: price falls to zero at the midpoint, then rises. SET jit = on; SET jit_above_cost = 0; WITH data AS ( @@ -1873,7 +1864,6 @@ WITH data AS ( ); -- nth_value beyond reduced frame with IGNORE NULLS --- Tests ignorenulls_getfuncarginframe early out-of-frame check SELECT company, tdate, price, nth_value(price, 5) IGNORE NULLS OVER w AS nth_5_in FROM stock @@ -1925,9 +1915,8 @@ WINDOW w AS ( ); -- --- last_value IGNORE NULLS with reduced frame containing all NULLs --- Exercises ignorenulls_getfuncarginframe SEEK_TAIL out-of-frame path --- when notnull_relpos >= num_reduced_frame. +-- last_value IGNORE NULLS when the reduced frame ends with NULLs +-- The search for a non-NULL value runs past the end of the reduced frame. -- CREATE TEMP TABLE rpr_nullval (id INT, val INT); INSERT INTO rpr_nullval VALUES (1, 10), (2, NULL), (3, NULL), (4, 20); @@ -2047,7 +2036,7 @@ CREATE TEMP TABLE rpr_consec_null (id INT, val INT); INSERT INTO rpr_consec_null VALUES (1, 100), (2, NULL), (3, NULL), (4, NULL), (5, 200), (6, 300); --- PREV(val) IS NULL succeeds for both null_slot (first row) and actual NULL +-- PREV(val) IS NULL is true for a genuine NULL in the previous row SELECT id, val, count(*) OVER w AS cnt FROM rpr_consec_null WINDOW w AS ( diff --git a/src/test/regress/sql/rpr_base.sql b/src/test/regress/sql/rpr_base.sql index d8f55cb3e0f..4bf25eabd17 100644 --- a/src/test/regress/sql/rpr_base.sql +++ b/src/test/regress/sql/rpr_base.sql @@ -135,11 +135,11 @@ ORDER BY dt; DROP TABLE stock_price; --- Auto-generated DEFINE +-- Pattern variables with no DEFINE entry CREATE TABLE rpr_auto (id INT, val INT); INSERT INTO rpr_auto VALUES (1, 10), (2, 20), (3, 30), (4, 15); --- One variable undefined (B auto-generated as "B IS TRUE") +-- B has no DEFINE entry, so it matches every row SELECT id, val, COUNT(*) OVER w as cnt FROM rpr_auto WINDOW w AS ( @@ -158,7 +158,7 @@ WINDOW w AS ( ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING PATTERN (A B C) DEFINE A AS val > 0 - -- B and C auto-generated as "B IS TRUE", "C IS TRUE" + -- B and C have no DEFINE entry, so they match every row ) ORDER BY id; @@ -337,7 +337,7 @@ ORDER BY id; DROP TABLE rpr_complex; --- Pattern variable not in PATTERN (should be ignored) +-- Extra DEFINE variable not present in PATTERN CREATE TABLE rpr_unused (id INT); INSERT INTO rpr_unused VALUES (1), (2); @@ -384,11 +384,6 @@ INSERT INTO rpr_frame VALUES -- Valid frame options -- ROWS: counts physical rows (1 FOLLOWING = next 1 physical row) --- Expected result: Each row can see 1 physical row ahead --- id=1,2,3 (val=10): can see next row -> cnt=2 --- id=4,5 (val=20): can see next row -> cnt=2 --- id=6 (val=30): no next row -> cnt=1 --- Result: [2,2,2,2,2,1] SELECT id, val, COUNT(*) OVER w as cnt FROM rpr_frame WINDOW w AS ( @@ -531,8 +526,8 @@ WINDOW w AS ( ) ORDER BY id; --- A non-constant frame end offset is allowed; a zero value is still rejected, --- this time at execution time (a literal cannot exercise that path). +-- A non-constant frame end offset is allowed; a zero value is rejected by the +-- same execution-time check the literal 0 above reaches. PREPARE rpr_end_offset(int8) AS SELECT id, val, COUNT(*) OVER w as cnt FROM rpr_frame @@ -636,7 +631,6 @@ WINDOW w AS ( DEFINE A AS val >= 10, B AS val > 15 ) ORDER BY id; --- Expected: Pattern matching should reset for each partition -- PARTITION BY with RANGE frame SELECT id, grp, val, COUNT(*) OVER w as cnt @@ -900,7 +894,6 @@ SELECT id, count(*) OVER w FROM rpr_reluctant WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING PATTERN (A? A?? A) DEFINE A AS TRUE); -- *? (zero or more, reluctant) --- Reluctant quantifier: prefer shortest match SELECT COUNT(*) OVER w FROM rpr_reluctant WINDOW w AS ( @@ -911,7 +904,6 @@ WINDOW w AS ( ); -- +? (one or more, reluctant) --- Reluctant quantifier: prefer shortest match SELECT COUNT(*) OVER w FROM rpr_reluctant WINDOW w AS ( @@ -922,7 +914,6 @@ WINDOW w AS ( ); -- ?? (zero or one, reluctant) --- Reluctant quantifier: prefer shortest match SELECT COUNT(*) OVER w FROM rpr_reluctant WINDOW w AS ( @@ -933,7 +924,6 @@ WINDOW w AS ( ); -- {n,}? (n or more, reluctant) --- Reluctant quantifier: prefer shortest match SELECT COUNT(*) OVER w FROM rpr_reluctant WINDOW w AS ( @@ -944,7 +934,6 @@ WINDOW w AS ( ); -- {n,m}? (n to m, reluctant) --- Reluctant quantifier: prefer shortest match SELECT COUNT(*) OVER w FROM rpr_reluctant WINDOW w AS ( @@ -966,8 +955,7 @@ WINDOW w AS ( DEFINE A AS val > 0 ); --- {,m}? (up to m, reluctant) - COMPLETELY UNTESTED RULE! --- Reluctant quantifier: prefer shortest match +-- {,m}? (up to m, reluctant) SELECT COUNT(*) OVER w FROM rpr_reluctant WINDOW w AS ( @@ -1110,10 +1098,10 @@ WINDOW w AS ( ); -- Token-separated reluctant quantifiers (space between quantifier and ?) --- These may be tokenized differently by the lexer +-- Whitespace between the quantifier and "?" is insignificant: each form below +-- returns exactly what its unseparated counterpart above returns. -- * ? (token separated) --- Reluctant quantifier: prefer shortest match SELECT COUNT(*) OVER w FROM rpr_reluctant WINDOW w AS ( @@ -1124,7 +1112,6 @@ WINDOW w AS ( ); -- + ? (token separated) --- Reluctant quantifier: prefer shortest match SELECT COUNT(*) OVER w FROM rpr_reluctant WINDOW w AS ( @@ -1135,7 +1122,6 @@ WINDOW w AS ( ); -- {2,} ? (token separated) --- Reluctant quantifier: prefer shortest match SELECT COUNT(*) OVER w FROM rpr_reluctant WINDOW w AS ( @@ -1166,7 +1152,6 @@ WINDOW w AS ( ); -- ? ? (parsed as ?? reluctant quantifier) --- Reluctant quantifier: prefer shortest match SELECT COUNT(*) OVER w FROM rpr_reluctant WINDOW w AS ( @@ -1334,9 +1319,9 @@ WINDOW w AS ( DROP TABLE rpr_bounds; --- Pattern element-count boundary at RPR_ELEMIDX_MAX (32767). Alternating --- distinct variables stop the optimizer from merging consecutive elements, so --- each "A B" pair contributes two elements; scanRPRPattern adds one FIN marker. +-- Pattern element-count boundary (maximum 32767 elements, the FIN marker +-- included). Alternating distinct variables stop the optimizer from merging +-- consecutive elements, so each "A B" pair contributes two elements. -- ECHO is silenced so the generated multi-thousand-token patterns do not flood -- the expected output. -- 16383 pairs -> 32766 + 1 FIN = 32767 = maximum, accepted. @@ -1477,7 +1462,7 @@ FROM rpr_nav t WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING PATTERN (A) DEFINE A AS NEXT(val / 0) > 0); -- Here the null reaches the DEFINE predicate itself instead of an IS NULL --- test: an all-NULL target row would have made v IS NULL true and matched the +-- An all-NULL target row would have made v IS NULL true and matched the -- first row, so this pins the predicate side of the same behaviour. WITH t(id, v) AS (VALUES (1, 10), (2, 20)) SELECT id, count(*) OVER w AS cnt @@ -1494,7 +1479,7 @@ WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING PATTERN (A) DEFINE -- XXX Folding evaluates the argument while planning, with the current row's -- value standing in for the target row's, so this divides by zero even though --- PREV has no row to navigate to. A separate patch will deal with it. +-- PREV has no row to navigate to. WITH t(id, v) AS (VALUES (1, 10)) SELECT id, count(*) OVER w AS cnt FROM t @@ -1663,7 +1648,7 @@ SELECT id, val, count(*) OVER w AS cnt, last_value(id) OVER w AS last_id PATTERN (A+) DEFINE A AS rpr_navns.prev(val) = -999) ORDER BY id; --- accepted: the SQL body inlines and folds to a constant, so no volatile call +-- The SQL body inlines and folds to a constant, so no volatile call -- is left for the check to find CREATE OR REPLACE FUNCTION prev(integer) RETURNS integer AS 'SELECT -999' LANGUAGE sql VOLATILE; @@ -1675,8 +1660,8 @@ SELECT id, val, count(*) OVER w AS cnt, last_value(id) OVER w AS last_id DEFINE A AS rpr_navns.prev(val) = -999) ORDER BY id; --- accepted: no OVER references the window, so flattening the subquery drops --- it before the check runs, the same way an unreferenced CTE is never planned +-- No OVER references the window, so flattening the subquery drops it +-- before the check runs, the same way an unreferenced CTE is never planned SELECT id FROM ( SELECT id FROM nt WINDOW w AS ( @@ -1684,15 +1669,15 @@ SELECT id FROM ( PATTERN (A+) DEFINE A AS random() > 0.5)) s ORDER BY id; --- error: OFFSET 0 keeps the subquery, so its DEFINE is checked +-- ERROR: OFFSET 0 keeps the subquery, so its DEFINE is checked SELECT id FROM ( SELECT id FROM nt WINDOW w AS ( ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING PATTERN (A+) DEFINE A AS random() > 0.5) OFFSET 0) sub; --- accepted: WHERE false makes the subquery rel dummy, so the planner never --- plans it and nothing looks at its DEFINE +-- WHERE false makes the subquery rel dummy, so the planner never plans it +-- and nothing looks at its DEFINE SELECT id FROM ( SELECT id FROM nt WINDOW w AS ( @@ -1700,7 +1685,7 @@ SELECT id FROM ( PATTERN (A+) DEFINE A AS random() > 0.5) OFFSET 0) sub WHERE false; --- accepted: the volatile is in a dead CASE arm that folds away, so nothing +-- The volatile is in a dead CASE arm that folds away, so nothing -- volatile is left for the check to find SELECT id FROM ( SELECT id FROM nt @@ -1710,7 +1695,7 @@ SELECT id FROM ( ELSE val > 5 END)) s ORDER BY id; --- error: folding can splice in a volatile that parse analysis never saw -- a +-- ERROR: folding can splice in a volatile that parse analysis never saw -- a -- STABLE function whose default argument is volatile -- and the check runs late -- enough to catch it CREATE FUNCTION rpr_off_leak(n bigint DEFAULT (random() * 5)::bigint) @@ -1721,7 +1706,7 @@ SELECT count(*) OVER w FROM generate_series(1, 100) g(v) DROP FUNCTION rpr_off_leak(bigint); --- accepted: a UNION ALL leaf is flattened like any other subquery, so its +-- A UNION ALL leaf is flattened like any other subquery, so its -- unreferenced window goes the same way SELECT id FROM ( SELECT id FROM nt @@ -1731,7 +1716,7 @@ SELECT id FROM ( UNION ALL SELECT id FROM nt) s; --- accepted: an unreferenced CTE is never planned, so nothing looks at its +-- An unreferenced CTE is never planned, so nothing looks at its -- DEFINE WITH unused AS ( SELECT id FROM nt @@ -1740,7 +1725,7 @@ WITH unused AS ( PATTERN (A+) DEFINE A AS random() > 0.5)) SELECT 1; --- error: referencing it plans the CTE, and the check reaches the DEFINE there +-- ERROR: referencing it plans the CTE, and the check reaches the DEFINE there WITH used AS ( SELECT id FROM nt WINDOW w AS ( @@ -2721,11 +2706,9 @@ SELECT pg_get_viewdef('rpr_quant_n_plus_v'::regclass); -- ============================================================ CREATE TABLE rpr_glue (id INT, val INT); INSERT INTO rpr_glue VALUES (1, 5), (2, 8), (3, 9), (4, -1), (5, 6), (6, -2); --- Quantifier glued to the alternation operator '|' without a space (0059). --- The lexer glues the trailing '|' into one Op token; the grammar reattaches it --- as the lowest-precedence alternation once the surrounding sequence is built. --- Deparse is canonical, so the glued, spaced, and mixed-spacing forms all --- reduce to the same PATTERN -- one deparse per shape proves the parse tree. +-- Quantifier glued to the alternation operator '|' without a space. The +-- lexer glues the trailing '|' into one Op token; the grammar reattaches it as +-- the lowest-precedence alternation once the surrounding sequence is built. -- Op-char quantifiers (*, +, ?, *?, +?, ??) glued to '|'. CREATE VIEW rpr_dp_op AS SELECT @@ -2926,7 +2909,7 @@ WINDOW w AS ( DEFINE A AS B.val > 0 ); --- DEFINE-only variable qualified name: still a pattern variable, not a range variable +-- DEFINE-only variable used as a qualifier SELECT COUNT(*) OVER w FROM rpr_err WINDOW w AS ( @@ -2972,7 +2955,6 @@ WINDOW w AS ( PATTERN (A+) DEFINE A AS (items).amount > 10 ); --- Expected: rows where (items).amount > 10 form matches; counts reflect frame size -- Composite type field selection (qualified forms): the ColumnRef portion ("A.items" or -- "rpr_composite.items") is what gets quoted; the trailing ".amount" lives in @@ -3044,7 +3026,7 @@ WINDOW w AS ( DEFINE A AS val > (SELECT max(val) FROM rpr_err) ); --- Pattern variable not used (should work, extra vars ignored) +-- DEFINE variables that do not appear in PATTERN SELECT id, val, COUNT(*) OVER w as cnt FROM rpr_err WINDOW w AS ( @@ -3765,7 +3747,6 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING -- ============================================================ -- Tests absorption marker display in EXPLAIN output -- Markers: ~ = branch element, # = comparison point --- Files: explain.c (append_rpr_quantifier, deparse_rpr_pattern) -- Simple VAR: A+ -> a+# (comparison point) EXPLAIN (COSTS OFF) @@ -3915,7 +3896,6 @@ WINDOW w AS ( -- Absorption Analysis Tests -- ============================================================ -- Tests context absorption optimization (O(n^2) -> O(n)) --- Files: rpr.c (computeAbsorbability) -- Simple Absorbable Pattern: A+ B -- Pattern starts with unbounded VAR @@ -4238,7 +4218,7 @@ ORDER BY id; CREATE TABLE rpr_fallback (id INT, val INT); INSERT INTO rpr_fallback VALUES (1, 10), (2, 20); --- Test: min quantifier overflow causes optimization fallback (min == max case) +-- Min quantifier overflow causes optimization fallback (min == max case) EXPLAIN (COSTS OFF) SELECT COUNT(*) OVER w FROM rpr_fallback WINDOW w AS ( @@ -4247,9 +4227,8 @@ WINDOW w AS ( PATTERN ((A{2000000000}){2}) DEFINE A AS val > 0 ); --- Expected: Fallback - pattern not merged due to min overflow (4000000000 > INT32_MAX) --- Test: max-only quantifier overflow causes optimization fallback +-- Max-only quantifier overflow causes optimization fallback EXPLAIN (COSTS OFF) SELECT COUNT(*) OVER w FROM rpr_fallback WINDOW w AS ( @@ -4258,9 +4237,8 @@ WINDOW w AS ( PATTERN ((A{1,2000000000}){2}) DEFINE A AS val > 0 ); --- Expected: Fallback - min OK (2*1=2), but max overflow (2*2000000000 > INT32_MAX) --- Test: max quantifier exceeds valid range (2147483647 = INT_MAX, limit is 2147483646) +-- Max quantifier exceeds valid range (2147483647 = INT_MAX, limit is 2147483646) EXPLAIN (COSTS OFF) SELECT COUNT(*) OVER w FROM rpr_fallback WINDOW w AS ( @@ -4270,7 +4248,7 @@ WINDOW w AS ( DEFINE A AS val > 0 ); --- Test: nested unbounded with large min causes overflow fallback +-- Nested unbounded with large min causes overflow fallback EXPLAIN (COSTS OFF) SELECT COUNT(*) OVER w FROM rpr_fallback WINDOW w AS ( @@ -4279,9 +4257,8 @@ WINDOW w AS ( PATTERN ((A{2000000000,}){2000000000,}) DEFINE A AS val > 0 ); --- Expected: Fallback - min overflow (2000000000 * 2000000000 > INT32_MAX) --- Test: prefix mismatch causes optimization fallback +-- Prefix mismatch causes optimization fallback EXPLAIN (COSTS OFF) SELECT COUNT(*) OVER w FROM rpr_fallback WINDOW w AS ( @@ -4290,9 +4267,8 @@ WINDOW w AS ( PATTERN (A B (C D)+) DEFINE A AS val > 0, B AS val > 5, C AS val > 10, D AS val > 15 ); --- Expected: Fallback - prefix elements don't match GROUP content --- Test: consecutive VAR merge whose min sum is exactly INF causes fallback. +-- Consecutive VAR merge whose min sum is exactly INF causes fallback. -- 1073741824 + 1073741823 = 2147483647 = INT32_MAX = RPR_QUANTITY_INF. -- Merging would yield a VAR with min == INF, so the merge must fall back and -- leave the two VARs unmerged (mirrors the multiply path's >= INF guard). @@ -4304,9 +4280,8 @@ WINDOW w AS ( PATTERN (A{1073741824,} A{1073741823,}) DEFINE A AS val > 0 ); --- Expected: Fallback - VARs not merged (min sum 2147483647 == INF) --- Test: one more than that sum does not fit in int32. The fallback looks the +-- One more than that sum does not fit in int32. The fallback looks the -- same as the case above; this one reaches the overflow check instead of the -- >= INF comparison. EXPLAIN (COSTS OFF) @@ -4318,7 +4293,7 @@ WINDOW w AS ( DEFINE A AS val > 0 ); --- Test: VAR merge falls back when the max sum lands exactly on INF. +-- VAR merge falls back when the max sum lands exactly on INF. EXPLAIN (COSTS OFF) SELECT COUNT(*) OVER w FROM rpr_fallback WINDOW w AS ( @@ -4327,7 +4302,7 @@ WINDOW w AS ( PATTERN (A{1,1073741823} A{1,1073741824}) DEFINE A AS val > 0 ); --- Test: one below that sum is the largest max the merge may keep. +-- One below that sum is the largest max the merge may keep. EXPLAIN (COSTS OFF) SELECT COUNT(*) OVER w FROM rpr_fallback WINDOW w AS ( @@ -4336,7 +4311,7 @@ WINDOW w AS ( PATTERN (A{1,1073741822} A{1,1073741824}) DEFINE A AS val > 0 ); --- Test: one above that does not fit in int32; the overflow check rejects it. +-- One above that does not fit in int32; the overflow check rejects it. EXPLAIN (COSTS OFF) SELECT COUNT(*) OVER w FROM rpr_fallback WINDOW w AS ( @@ -4345,7 +4320,7 @@ WINDOW w AS ( PATTERN (A{1,1073741824} A{1,1073741824}) DEFINE A AS val > 0 ); --- Test: an operand that is already unbounded still merges. +-- An operand that is already unbounded still merges. EXPLAIN (COSTS OFF) SELECT COUNT(*) OVER w FROM rpr_fallback WINDOW w AS ( @@ -4354,7 +4329,7 @@ WINDOW w AS ( PATTERN (A{1,1073741823} A{1,}) DEFINE A AS val > 0 ); --- Test: consecutive GROUP merge whose min sum is exactly INF causes fallback. +-- Consecutive GROUP merge whose min sum is exactly INF causes fallback. EXPLAIN (COSTS OFF) SELECT COUNT(*) OVER w FROM rpr_fallback WINDOW w AS ( @@ -4363,9 +4338,8 @@ WINDOW w AS ( PATTERN ((A B){1073741824,} (A B){1073741823,}) DEFINE A AS val > 0, B AS val > 5 ); --- Expected: Fallback - GROUPs not merged (min sum 2147483647 == INF) --- Test: consecutive GROUP merge whose max sum is exactly INF causes fallback, +-- Consecutive GROUP merge whose max sum is exactly INF causes fallback, -- where one less merges. Without the guard the merged max would alias INF and -- a bounded pattern would become unbounded. EXPLAIN (COSTS OFF) @@ -4385,7 +4359,7 @@ WINDOW w AS ( DEFINE A AS val > 0, B AS val > 5 ); --- Test: the prefix merge adds one iteration, so it declines a min already at +-- The prefix merge adds one iteration, so it declines a min already at -- INF - 1 and a max already at INF - 1; one less than either merges. EXPLAIN (COSTS OFF) SELECT COUNT(*) OVER w FROM rpr_fallback @@ -4420,7 +4394,7 @@ WINDOW w AS ( DEFINE A AS val > 0, B AS val > 5 ); --- Test: the suffix merge has the same boundary as the prefix merge. +-- The suffix merge has the same boundary as the prefix merge. EXPLAIN (COSTS OFF) SELECT COUNT(*) OVER w FROM rpr_fallback WINDOW w AS ( @@ -4444,7 +4418,6 @@ DROP TABLE rpr_fallback; -- Planner Integration Tests -- ============================================================ -- Tests full planning pipeline and WindowAgg plan node creation --- Files: planner.c, createplan.c CREATE TABLE rpr_planner (id INT, category VARCHAR(10), val INT); INSERT INTO rpr_planner VALUES @@ -4524,25 +4497,8 @@ SELECT id, category, val, FROM rpr_planner ORDER BY id; --- Window with Aggregate Functions -SELECT category, - COUNT(*) OVER w as window_cnt, - COUNT(*) as agg_cnt -FROM rpr_planner -WINDOW w AS ( - PARTITION BY category - ORDER BY id - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - PATTERN (A+) - DEFINE A AS val > 0 -) -GROUP BY category -ORDER BY category; --- (GROUP BY after WINDOW clause is not valid SQL syntax) - -- ============================================================ -- Subquery and CTE Tests --- Files: planner.c, prepjointree.c -- ============================================================ -- Tests RPR with subqueries and CTEs @@ -4629,7 +4585,6 @@ SELECT * FROM cte2 ORDER BY id; -- ============================================================ -- JOIN Tests --- Files: prepjointree.c, setrefs.c -- ============================================================ -- Tests RPR with JOINs and multiple table references @@ -4716,7 +4671,6 @@ DROP TABLE rpr_join1, rpr_join2; -- ============================================================ -- Complex Expression Tests --- Files: createplan.c, setrefs.c -- ============================================================ -- Tests complex target list expressions @@ -4803,7 +4757,6 @@ DROP TABLE rpr_target; -- ============================================================ -- Set Operations Tests --- Files: planner.c -- ============================================================ -- Tests RPR with UNION, INTERSECT, EXCEPT @@ -4901,7 +4854,6 @@ DROP TABLE rpr_set1, rpr_set2; -- ============================================================ -- Sorting and Grouping Tests --- Files: planner.c, createplan.c -- ============================================================ -- Tests RPR interaction with sorting and grouping @@ -5116,7 +5068,7 @@ DROP TABLE rpr_stress; CREATE TABLE rpr_errors (id INT, val INT); INSERT INTO rpr_errors VALUES (1, 10), (2, 20); --- Test: DEFINE variable not in PATTERN (error) +-- DEFINE variable not in PATTERN (error) SELECT id, val, COUNT(*) OVER w FROM rpr_errors WINDOW w AS ( ORDER BY id @@ -5125,9 +5077,8 @@ WINDOW w AS ( DEFINE B AS TRUE ); --- Expected: Error - B is not used in PATTERN --- Test: 240 variables in PATTERN and DEFINE (boundary - should succeed) +-- 240 variables in PATTERN and DEFINE (boundary - should succeed) SELECT COUNT(*) OVER w FROM rpr_errors WINDOW w AS ( ORDER BY id @@ -5170,7 +5121,6 @@ WINDOW w AS ( V221 AS val > 0, V222 AS val > 0, V223 AS val > 0, V224 AS val > 0, V225 AS val > 0, V226 AS val > 0, V227 AS val > 0, V228 AS val > 0, V229 AS val > 0, V230 AS val > 0, V231 AS val > 0, V232 AS val > 0, V233 AS val > 0, V234 AS val > 0, V235 AS val > 0, V236 AS val > 0, V237 AS val > 0, V238 AS val > 0, V239 AS val > 0, V240 AS val > 0 ); --- Expected: Success - exactly at RPR_VARID_MAX boundary -- ERROR: 241 variables in PATTERN, 240 in DEFINE (exceeds limit with implicit TRUE) SELECT COUNT(*) OVER w FROM rpr_errors @@ -5217,8 +5167,7 @@ WINDOW w AS ( V231 AS val > 0, V232 AS val > 0, V233 AS val > 0, V234 AS val > 0, V235 AS val > 0, V236 AS val > 0, V237 AS val > 0, V238 AS val > 0, V239 AS val > 0, V240 AS val > 0 ); --- Pattern nesting-depth boundary at RPR_DEPTH_MAX (255; effective maximum --- 254, since scanRPRPatternRecursive() rejects depth >= RPR_DEPTH_MAX). +-- Pattern nesting-depth boundary: 254 levels are accepted, 255 rejected. -- Reluctant quantifiers are not subject to quantifier multiplication, so the -- nesting survives optimization and still reaches the depth check. -- ECHO is silenced so the generated deeply nested patterns do not flood the @@ -5242,7 +5191,7 @@ DROP TABLE rpr_errors; -- Basic Pattern Matching -- ============================================================ --- Test: A? (optional, greedy) +-- A? (optional, greedy) SELECT id, val, count(*) OVER w AS c FROM rpr_plan WINDOW w AS ( @@ -5253,7 +5202,7 @@ WINDOW w AS ( DEFINE A AS val > 50 ); --- Test: A{2} (exact count) +-- A{2} (exact count) SELECT id, val, count(*) OVER w AS c FROM rpr_plan WINDOW w AS ( @@ -5264,7 +5213,7 @@ WINDOW w AS ( DEFINE A AS val <= 50 ); --- Test: A{1,3} (bounded range, greedy) +-- A{1,3} (bounded range, greedy) SELECT id, val, count(*) OVER w AS c FROM rpr_plan WINDOW w AS ( @@ -5275,7 +5224,7 @@ WINDOW w AS ( DEFINE A AS val <= 50 ); --- Test: A | B (simple alternation) +-- A | B (simple alternation) SELECT id, val, count(*) OVER w AS c FROM rpr_plan WINDOW w AS ( @@ -5286,7 +5235,7 @@ WINDOW w AS ( DEFINE A AS val <= 30, B AS val > 70 ); --- Test: A | B | C (three-way alternation) +-- A | B | C (three-way alternation) SELECT id, val, count(*) OVER w AS c FROM rpr_plan WINDOW w AS ( @@ -5297,7 +5246,7 @@ WINDOW w AS ( DEFINE A AS val <= 20, B AS val BETWEEN 40 AND 60, C AS val > 80 ); --- Test: A B C (concatenation) +-- A B C (concatenation) SELECT id, val, count(*) OVER w AS c FROM rpr_plan WINDOW w AS ( @@ -5308,7 +5257,7 @@ WINDOW w AS ( DEFINE A AS val <= 30, B AS val BETWEEN 31 AND 60, C AS val > 60 ); --- Test: A B? C (optional middle) +-- A B? C (optional middle) SELECT id, val, count(*) OVER w AS c FROM rpr_plan WINDOW w AS ( @@ -5319,7 +5268,7 @@ WINDOW w AS ( DEFINE A AS val <= 30, B AS val BETWEEN 31 AND 60, C AS val > 60 ); --- Test: (A B)+ (grouped quantifier) +-- (A B)+ (grouped quantifier) SELECT id, val, count(*) OVER w AS c FROM rpr_plan WINDOW w AS ( @@ -5330,7 +5279,7 @@ WINDOW w AS ( DEFINE A AS val <= 50, B AS val > 50 ); --- Test: (A | B)+ C (alternation with quantifier) +-- (A | B)+ C (alternation with quantifier) SELECT id, val, count(*) OVER w AS c FROM rpr_plan WINDOW w AS ( @@ -5341,9 +5290,7 @@ WINDOW w AS ( DEFINE A AS val <= 30, B AS val BETWEEN 31 AND 60, C AS val > 80 ); --- Test: (A+ | (A | B)+)* - nested alternation inside quantified group --- Previously caused infinite recursion in alternation handling when the inner --- BEGIN(+)'s skip jump was followed as an ALT branch pointer. +-- (A+ | (A | B)+)* - nested alternation inside quantified group SELECT id, flags, first_value(id) OVER w AS match_start, last_value(id) OVER w AS match_end FROM (VALUES (1, ARRAY['A', 'B']), @@ -5363,9 +5310,9 @@ WINDOW w AS ( -- ============================================================ -- Pathological Patterns -- ============================================================ --- These patterns previously caused issues. Now optimized or handled safely. +-- Nested unbounded quantifiers that the optimizer collapses. --- Test: (A*)* - nested unbounded (optimized to A*) +-- (A*)* - nested unbounded (optimized to A*) SELECT v, count(*) OVER w AS c FROM (SELECT generate_series(1, 5) v) WINDOW w AS ( @@ -5376,7 +5323,7 @@ WINDOW w AS ( DEFINE A AS TRUE ); --- Test: (A*)+ - inner nullable (optimized to A*) +-- (A*)+ - inner nullable (optimized to A*) SELECT v, count(*) OVER w AS c FROM (SELECT generate_series(1, 5) v) WINDOW w AS ( @@ -5387,7 +5334,7 @@ WINDOW w AS ( DEFINE A AS TRUE ); --- Test: (A+)* - outer nullable (optimized to A*) +-- (A+)* - outer nullable (optimized to A*) SELECT v, count(*) OVER w AS c FROM (SELECT generate_series(1, 5) v) WINDOW w AS ( @@ -5398,7 +5345,7 @@ WINDOW w AS ( DEFINE A AS TRUE ); --- Test: (A+)+ - both require match (optimized to A+) +-- (A+)+ - both require match (optimized to A+) SELECT v, count(*) OVER w AS c FROM (SELECT generate_series(1, 5) v) WINDOW w AS ( @@ -5409,7 +5356,7 @@ WINDOW w AS ( DEFINE A AS TRUE ); --- Test: (((A)*)*)* - triple nested (optimized to A*) +-- (((A)*)*)* - triple nested (optimized to A*) SELECT v, count(*) OVER w AS c FROM (SELECT generate_series(1, 3) v) WINDOW w AS ( diff --git a/src/test/regress/sql/rpr_explain.sql b/src/test/regress/sql/rpr_explain.sql index da11881e06c..6bda8db16f1 100644 --- a/src/test/regress/sql/rpr_explain.sql +++ b/src/test/regress/sql/rpr_explain.sql @@ -205,9 +205,8 @@ WINDOW w AS ( DEFINE A AS v % 3 = 1, B AS v % 3 = 2, C AS v % 3 = 0 );'); --- Regression test: Sequential alternations at same depth +-- Sequential alternations at the same depth -- Verifies that "((B | C) (D | E))" correctly outputs as "(b | c) (d | e)" --- Previously failed due to missing parentheses on ALT depth decrease CREATE VIEW rpr_ev_basic_deparse_seqalt AS SELECT count(*) OVER w FROM generate_series(1, 30) AS s(v) @@ -315,7 +314,7 @@ WINDOW w AS ( D AS cat = ''D'', E AS cat = ''E'' );'); --- Complex pattern with high state count +-- Complex pattern: quantified sequence with a star element CREATE VIEW rpr_ev_state_complex AS SELECT count(*) OVER w FROM generate_series(1, 100) AS s(v) @@ -366,7 +365,6 @@ WINDOW w AS ( );'); -- State explosion pattern - many alternations --- Pattern (A|B)(A|B)(A|B)(A|B) can create many parallel states CREATE VIEW rpr_ev_state_explosion AS SELECT count(*) OVER w FROM generate_series(1, 100) AS s(v) @@ -578,7 +576,7 @@ WINDOW w AS ( DEFINE A AS v % 3 = 1, B AS v % 3 = 2 );'); --- (A{2,})* must NOT flatten to a* (H-1): counts {0} UNION [2, INF) leave 1 +-- (A{2,})* must NOT flatten to a*: counts {0} UNION [2, INF) leave 1 -- unreachable. The planner keeps it as (a{2,})*, not a*. CREATE VIEW rpr_ev_nested_quant_no_flatten AS SELECT count(*) OVER w @@ -741,7 +739,7 @@ WINDOW w AS ( DEFINE A AS v % 10 = 1, B AS v % 10 = 2, C AS v % 10 = 3 );'); --- High context absorption - unbounded group +-- Unbounded group followed by a trailing element CREATE VIEW rpr_ev_ctx_absorb_group AS SELECT count(*) OVER w FROM generate_series(1, 100) AS s(v) @@ -1137,7 +1135,7 @@ WINDOW w AS ( D AS cat = ''D'', E AS cat = ''E'' );'); --- Variable length matches - min/max/avg differ +-- Unbounded quantifier over a repeating cycle CREATE VIEW rpr_ev_mlen_variable AS SELECT count(*) OVER w FROM generate_series(1, 100) AS s(v) @@ -1979,7 +1977,7 @@ WINDOW w AS ( DEFINE A AS v % 100 <> 0, B AS v % 100 = 0 );'); --- High state merge ratio +-- Alternation with plus quantifier over 500 rows CREATE VIEW rpr_ev_perf_high_merge AS SELECT count(*) OVER w FROM generate_series(1, 500) AS s(v) @@ -2664,7 +2662,8 @@ WINDOW w AS ( );'); -- Unit (1,1) group as an alternation branch (emits no BEGIN/END) --- Pattern: ((A B) | C) - control: takes the variable path, not deparse_rpr_group +-- Pattern: ((A B) | C) - control: a {1,1} group emits no BEGIN/END, so the +-- branch deparses as a plain sequence CREATE VIEW rpr_ev_alt_grp_unit AS SELECT count(*) OVER w FROM generate_series(1, 20) AS s(v) @@ -2685,7 +2684,8 @@ WINDOW w AS ( );'); -- Quantified variable as the first alternation branch --- Pattern: (A+ | C) - control: deparse_rpr_var already opens the leading paren +-- Pattern: (A+ | C) - control: a quantified variable as the leading branch; +-- the ALT supplies the enclosing parentheses CREATE VIEW rpr_ev_alt_var_first AS SELECT count(*) OVER w FROM generate_series(1, 20) AS s(v) @@ -3435,7 +3435,7 @@ WINDOW w AS ( DEFINE A AS v % 2 = 1, B AS v % 2 = 0 );'); --- High skip count scenario +-- Sparse five-element pattern over 500 rows CREATE VIEW rpr_ev_scale_high_skip AS SELECT count(*) OVER w FROM generate_series(1, 500) AS s(v) @@ -3584,7 +3584,7 @@ WINDOW w AS ( DEFINE A AS v > PREV(v, $1) ); --- No navigation function: offset 0 +-- No navigation function EXPLAIN (COSTS OFF) SELECT count(*) OVER w FROM generate_series(1,10) s(v) WINDOW w AS ( @@ -3593,7 +3593,7 @@ WINDOW w AS ( DEFINE A AS v > 0 ); --- NEXT only: no backward navigation, offset 0 +-- NEXT only: no backward navigation EXPLAIN (COSTS OFF) SELECT count(*) OVER w FROM generate_series(1,10) s(v) WINDOW w AS ( @@ -3660,7 +3660,7 @@ WINDOW w AS ( DEFINE A AS v > PREV(v, $1) ); --- FIRST(v): retain all (references match_start row) +-- FIRST(v) with no offset (references the match start row) EXPLAIN (COSTS OFF) SELECT count(*) OVER w FROM generate_series(1,10) s(v) WINDOW w AS ( diff --git a/src/test/regress/sql/rpr_integration.sql b/src/test/regress/sql/rpr_integration.sql index ae437eb356d..b0f9322ba62 100644 --- a/src/test/regress/sql/rpr_integration.sql +++ b/src/test/regress/sql/rpr_integration.sql @@ -120,7 +120,7 @@ ORDER BY id; -- kept separate regardless of equivalence. -- Non-RPR baseline: two inline windows with identical spec are --- deduped by the planner into a single WindowAgg node, confirming +-- deduped by the parser into a single WindowAgg node, confirming -- that the dedup path is active for non-RPR windows. EXPLAIN (COSTS OFF) SELECT diff --git a/src/test/regress/sql/rpr_nfa.sql b/src/test/regress/sql/rpr_nfa.sql index 936e7f86d4b..6c2cb3822e0 100644 --- a/src/test/regress/sql/rpr_nfa.sql +++ b/src/test/regress/sql/rpr_nfa.sql @@ -4,8 +4,8 @@ -- ============================================================ -- -- This test suite validates the NFA (Non-deterministic Finite --- Automaton) runtime execution engine in nodeWindowAgg.c, --- focusing on update_reduced_frame and related functions. +-- Automaton) runtime execution engine in execRPR.c, driven by +-- update_reduced_frame() in nodeWindowAgg.c. -- -- Test Strategy: -- Diagonal pattern style using ARRAY flags to explicitly @@ -31,6 +31,7 @@ -- DEFINE Special Cases -- Absorption Dynamic Flags -- Zero-Consumption Cycle Detection +-- Standard Clause 7: Formal Pattern Matching Rules -- -- Responsibility: -- - NFA runtime execution paths @@ -891,8 +892,10 @@ WINDOW w AS ( -- the engine must prefer the fast-forward (exit) path for reluctant -- groups and suppress longer matches once exit reaches FIN, mirroring the -- sibling min<=count=2 boundary and single-quantifier controls localize the behaviour: only --- the all-reluctant case (rr) should differ. +-- min>=2 boundary and single-quantifier controls localize the behaviour: the +-- inner quantifier decides whether a row is consumed, so every column whose +-- body is reluctant stays at zero, and the two with a greedy body differ by +-- their outer quantifier -- gg takes the longest match, rg one row. WITH t(id, isa) AS (VALUES (1, true), (2, true), (3, true), (4, false)) SELECT id, count(*) OVER gg AS gg, -- (A?)+ greedy / greedy @@ -1665,8 +1668,7 @@ WINDOW w AS ( ); -- A+? B (reluctant plus): exits A at first B availability --- (Same scenario as greedy-vs-reluctant comparison above; retained for --- standalone quantifier coverage alongside A{1,3}? and A{2,3}? below) +-- (Standalone reluctant-plus case; compare with A{1,3}? and A{3,5}? below) WITH test_reluctant_plus AS ( SELECT * FROM (VALUES (1, ARRAY['A','_']), @@ -1737,7 +1739,7 @@ WINDOW w AS ( B AS 'B' = ANY(flags) ); --- Nested quantifier flattening must not widen the matching language (H-1). +-- Nested quantifier flattening must not widen the matching language. -- (A{k,})* with k >= 2 reaches repetition counts {0} UNION [k, INF); the gap -- 1..k-1 is unreachable, so it must NOT collapse to A*. An isolated single A -- must yield an EMPTY match (count 0), not a length-1 match. @@ -2136,9 +2138,8 @@ WINDOW w AS ( C AS 'C' = ANY(flags) ); --- altPriority replacement (A B C | D) --- D branch (higher altPriority) matches first at row 1, --- then A B C branch (lower altPriority) replaces it at row 3. +-- Branch preference (A B C | D): D completes first at row 1, but the +-- earlier-written A B C branch is preferred and replaces the match at row 3. WITH test_alt_replace AS ( SELECT * FROM (VALUES (1, ARRAY['A', 'D']), @@ -3667,10 +3668,6 @@ WINDOW w AS ( ); -- N FOLLOWING + SKIP TO NEXT ROW: overlapping matches bounded by frame --- Row 1: frame [1,4], A(1-3) B(4) -> match --- Row 2: frame [2,5], A(2-3) B(4) -> match --- Row 3: frame [3,6], A(3) B(4) -> match --- Row 5: frame [5,6], A(5) B(6) -> match WITH test_n_skip_next AS ( SELECT * FROM (VALUES (1, ARRAY['A']), @@ -4584,7 +4581,6 @@ WINDOW w AS ( -- ((A|B){1,2}) greedy: lexicographic > length -- Standard example: preferment AA, AB, A, BA, BB, B --- Single A preferred over B-starting longer match WITH test_quant_lex_greedy AS ( SELECT * FROM (VALUES (1, ARRAY['A','B']), @@ -4833,8 +4829,6 @@ WINDOW w AS ( -- (A?){0,3}: min=0, nullable inner. -- A never matches but A? matches empty, satisfying min=0 immediately. --- NFA reports 3 length-0 matches (one per row); first_value / last_value --- are NULL because the window frame for an empty match has no rows. WITH test_728_min0 AS ( SELECT * FROM (VALUES (1, ARRAY['B']), @@ -4857,7 +4851,6 @@ WINDOW w AS ( -- (A?){1,3}: min=1, nullable inner. -- A never matches; one empty iteration satisfies min=1. --- NFA reports 3 length-0 matches; first/last_value NULL over empty frame. WITH test_728_min1 AS ( SELECT * FROM (VALUES (1, ARRAY['B']), @@ -4880,7 +4873,6 @@ WINDOW w AS ( -- (A?){2,3}: min=2, nullable inner. Per ISO/IEC 19075-5 7.2.8 STR06 = (STRE STRE) -- is valid: two empty iterations satisfy min=2. --- NFA reports 3 length-0 matches; first/last_value NULL over empty frame. WITH test_728_min2 AS ( SELECT * FROM (VALUES (1, ARRAY['B']), @@ -4902,9 +4894,6 @@ WINDOW w AS ( ); -- (A?){2,3} mixed: some rows match A, some don't --- Rows 1-2: A matches, greedy takes 2 -> min satisfied (real match) --- Row 3: A doesn't match, two empty iterations satisfy min=2 (length-0 match) --- Row 4: A matches 1 real iter + 1 ff empty exit -> match 4-4 WITH test_728_min2_mixed AS ( SELECT * FROM (VALUES (1, ARRAY['A']), @@ -4984,8 +4973,6 @@ WINDOW w AS ( -- fires as a parallel exit path (EXIT ONLY, no greedy/reluctant loop). -- Data: alternating A, B rows (6 rows) -- Greedy: each row gets the longest match from its starting position. --- Row 1: 3 iters (A@1,B@2)(A@3,B@4)(A@5,B@6) -> 1-6 --- Row 5: 1 real iter + 1 ff empty exit -> 5-6 WITH test_728_multi_body AS ( SELECT * FROM (VALUES (1, ARRAY['A']), @@ -5011,7 +4998,6 @@ WINDOW w AS ( ); -- (A? B?){2,3}: pure empty body (nothing matches A or B). --- NFA reports 3 length-0 matches; first/last_value NULL over empty frame. WITH test_728_multi_empty AS ( SELECT * FROM (VALUES (1, ARRAY['C']), @@ -5034,9 +5020,6 @@ WINDOW w AS ( ); -- (A? B?){2,3}: mixed real and empty iterations --- Row 1: iter1 real (A@1,B@2), iter2 at row 3 empty -> ff exit, match 1-2 --- Row 3: C doesn't match A or B -> NULL --- Row 4: iter1 real (A@4,B@5), iter2 at end empty -> ff exit, match 4-5 WITH test_728_multi_mixed AS ( SELECT * FROM (VALUES (1, ARRAY['A']),