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 <[email protected]>님이 작성: > 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
