Re: Row pattern recognition

Hi Henson,

>> ## Proposal for consistency
>> >
>> > /* Initialize NFA free lists for row pattern matching */
>> > winstate->nfaContext = NULL;
>> > winstate->nfaContextTail = NULL;
>> > winstate->nfaContextFree = NULL;
>> > winstate->nfaStateFree = NULL;
>> > winstate->nfaLastProcessedRow = -1;
>> > winstate->nfaStatesActive = 0; // Add this
>> > winstate->nfaContextsActive = 0; // Add this
>> >
>> > Would you like me to include this change in the next patch?
>>
>> Yes, please.
>>
>
>
> Done. Please see the attached patch.

Looks good to me.

BTW, I noticed that following test now succeeds with v42 patch.
In rpr_explain.sql test 11.3:

-- Test 11.3: Consecutive increasing values (using PREV)
-- FIXME: The original pattern was:
-- DEFINE A AS v > PREV(v) OR PREV(v) IS NULL
-- This causes "ERROR: unrecognized node type: 15" (T_FuncExpr) because
-- NullTest(FuncExpr(PREV)) is not properly handled somewhere in the planner.
-- The expression v > PREV(v) works fine, but PREV(v) IS NULL fails.
-- Using COALESCE(PREV(v), 0) as a workaround until the bug is fixed.
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) OVER w
FROM generate_series(1, 50) AS s(v)
WINDOW w AS (
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
AFTER MATCH SKIP PAST LAST ROW
PATTERN (A{3,})
DEFINE A AS v > COALESCE(PREV(v), 0)
);

I changed
DEFINE A AS v > COALESCE(PREV(v), 0)
to
DEFINE A AS v > PREV(v) OR PREV(v) IS NULL
and get following result.

EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) OVER w
FROM generate_series(1, 50) AS s(v)
WINDOW w AS (
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
AFTER MATCH SKIP PAST LAST ROW
PATTERN (A{3,})
DEFINE A AS v > PREV(v) OR PREV(v) IS NULL
);
QUERY PLAN
----------------------------------------------------------------------
WindowAgg (actual rows=50.00 loops=1)
Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
Pattern: a{3,}"
Storage: Memory Maximum Storage: 18kB
NFA States: 3 peak, 99 total, 0 merged
NFA Contexts: 2 peak, 51 total, 0 pruned
NFA: 1 matched (len 50/50/50.0), 0 mismatched
NFA: 49 absorbed (len 1/1/1.0), 0 skipped
-> Function Scan on generate_series s (actual rows=50.00 loops=1)
(9 rows)

Probably we can restore 11.3 test in v43?

Best regards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp