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