Re: Row pattern recognition

Hi Ishii-san,

I agree - this is expected behavior.

DEFINE expressions must be in the target list - this is a
prerequisite for NFA pattern matching. They go through the
standard expression pipeline:

2026년 1월 19일 (월) PM 3:45, Tatsuo Ishii <ishii(at)postgresql(dot)org>님이 작성:

> While looking into EXPLAIN VERBOSE ANALYZE output of a RPR defined
> query, I noticed that the "Output" row of the explain command includes
> columns from DEFINE clause (price). This is because columns referenced
> in the DEFINE clause must appear on the target list. It is the same
> situation as a target list which does not include a column used by
> ORDER BY clause like "SELECT price FROM stock ORDER by company". See
> the discussion:
> https://www.postgresql.org/message-id/13494.1250901451%40sss.pgh.pa.us
>
> So I think it's ok for now. Opinions?
>
> explain analyze verbose
> SELECT company, tdate, price, count(*) OVER w
> FROM stock
> WINDOW w AS (
> PARTITION BY company
> ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
> INITIAL
> PATTERN (A{,2} )
> DEFINE
> A AS price = 200 OR price = 140,
> B AS price = 150
> );
> QUERY PLAN
>
>
> ---------------------------------------------------------------------------------------------------------------------------
> WindowAgg (cost=83.46..113.37 rows=1200 width=50) (actual
> time=0.017..0.031 rows=10.00 loops=1)
> Output: company, tdate, price, count(*) OVER w, ((price = 200) OR
> (price = 140)), (price = 150)
> Window: w AS (PARTITION BY stock.company ROWS BETWEEN CURRENT ROW AND
> UNBOUNDED FOLLOWING)
> Pattern: a{0,2}
> Storage: Memory Maximum Storage: 17kB
> NFA States: 3 peak, 13 total, 0 merged
> NFA Contexts: 3 peak, 11 total, 8 pruned
> NFA: 2 matched (len 1/1/1), 0 mismatched
> Buffers: shared hit=1
> -> Sort (cost=83.37..86.37 rows=1200 width=40) (actual
> time=0.008..0.009 rows=10.00 loops=1)
> Output: company, tdate, price
> Sort Key: stock.company
> Sort Method: quicksort Memory: 25kB
> Buffers: shared hit=1
> -> Seq Scan on public.stock (cost=0.00..22.00 rows=1200
> width=40) (actual time=0.003..0.003 rows=10.00 loops=1)
> Output: company, tdate, price
> Buffers: shared hit=1
> Planning Time: 0.023 ms
> Execution Time: 0.050 ms
> (19 rows)
>

SQL: DEFINE A AS price = 200

[PARSE]
ResTarget { name: "A", val: "price = 200" }
↓ findTargetlistEntrySQL99()
Added to query targetlist
↓
[PLAN]
WindowAgg.defineClause = List<TargetEntry>
↓
[RUNTIME]
ExecEvalExpr() called for EACH ROW
↓
varMatched[A] = true/false → used by NFA pattern matching

This is the same as ORDER BY columns not in SELECT - they must
be in the internal target list for execution, as Tom Lane
explained in the thread you referenced.

Best regards,
Henson