Date: Wed, 11 Feb 2026 12:50:32 +0900 (JST)
Message-Id: <20260211.125032.1207789314761104094.ishii@postgresql.org>
To: assam258@gmail.com, vik@postgresfriends.org, er@xs4all.nl,
 jacob.champion@enterprisedb.com
Cc: david.g.johnston@gmail.com, vik@postgresfriends.org, er@xs4all.nl,
 peter@eisentraut.org, pgsql-hackers@postgresql.org
Subject: Re: Row pattern recognition
From: Tatsuo Ishii <ishii@postgresql.org>
In-Reply-To: <CAAAe_zDX24SnaQFwTNVUx_vK5887mpD+rXTaedjzdfFrGROFGA@mail.gmail.com>
References: <CAAAe_zALWNDOcTMCOk71d3FD5MHQLiCsL-US5ok=7ZoX=xtHFg@mail.gmail.com>
	<20260209.190209.470246357440525054.ishii@postgresql.org>
	<CAAAe_zDX24SnaQFwTNVUx_vK5887mpD+rXTaedjzdfFrGROFGA@mail.gmail.com>
Mime-Version: 1.0
Content-Type: Text/Plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/20260211.125032.1207789314761104094.ishii%40postgresql.org>
Precedence: bulk

Hi Henson,

In my understanding RANGE/GROUPS are not allowed when RPR is defined.
(See ISO/IEC 19075-5 section 6.10.2 "ROWS BETWEEN CURRENT ROW AND").
So proper fix would be to error out at the parse/analyze phase if
RANGE/GROUPS are used when RPR is defined.

Vik, Jaconb,
Thoughts?

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

> Hi Ishii-san,
> 
> During code review of nodeWindowAgg.c, I discovered that
> update_reduced_frame()
> can receive the same pos value repeatedly for RANGE/GROUPS frames.
> 
> 
> ----------------------------------------------------------------------
> PATTERN DISCOVERED
> ----------------------------------------------------------------------
> 
> For RANGE/GROUPS frames with peer rows (rows having the same ORDER BY
> value):
> 
>   Data: id=1,2,3 all have val=10 (peer rows)
>         id=4,5 both have val=20 (peer rows)
> 
>   RANGE frame processing:
>   - id=1 processing: pos=0 (frameheadpos points to first peer)
>   - id=2 processing: pos=0 (same frameheadpos)
>   - id=3 processing: pos=0 (same frameheadpos)
>   - id=4 processing: pos=3
>   - id=5 processing: pos=3
> 
> ROWS frames always have sequential pos values (0,1,2,3,4...), but
> RANGE/GROUPS
> can have repeated pos values (0,0,0,3,3...).
> 
> 
> ----------------------------------------------------------------------
> CURRENT IMPLEMENTATION ISSUE
> ----------------------------------------------------------------------
> 
> The current implementation appears to assume sequential processing only,
> which
> is valid for ROWS frames. Two specific issues found:
> 
> 1. In update_reduced_frame():
>    - The pos <= nfaLastProcessedRow check assumes "already processed pos
> doesn't
>      need reprocessing", but RANGE/GROUPS require processing the same pos
>      multiple times.
> 
> 2. In nfa_process_row():
>    - Frame boundary calculation: ctxFrameEnd = matchStartRow + frameOffset
> + 1
>    - This adds frameOffset directly to row position, which works for ROWS
> but
>      may be incorrect for RANGE (value-based) and GROUPS (group-based)
> frames.
> 
> 
> ----------------------------------------------------------------------
> TEST COVERAGE LIMITATION
> ----------------------------------------------------------------------
> 
> The existing RANGE/GROUPS test cases in rpr_base.sql only cover simple
> scenarios
> that don't reach the frame end, which is why this issue wasn't detected.
> 
> Most tests target ROWS frames, with only a small number testing RANGE/GROUPS
> frames.
> 
> 
> ----------------------------------------------------------------------
> PROPOSAL
> ----------------------------------------------------------------------
> 
> Rather than directly modifying these functions, we should consider an
> alternative approach for non-UNBOUNDED frames:
> - Strictly respect the frame head position
> - Minimize lower-level optimizations (absorption/pruning)
> - Avoid creating subsequent contexts for limited reuse benefit
> - For bounded frames, the benefit of context reuse across rows may be
> limited
>   compared to UNBOUNDED frames where contexts can be reused extensively
> - This would simplify RANGE/GROUPS handling at the cost of some optimization
> 
> To implement this properly, we need to broaden our understanding of the
> execution structure through code review of Window clauses and Aggregate
> functions.
> 
> Therefore, for this round of nodeWindowAgg.c review:
> 1. Perform code simplification/refactoring for ROWS frames only
> 2. Add FIXME comments to RANGE/GROUPS test cases
> 3. Defer RANGE/GROUPS handling to a future phase
> 
> There may be other issues beyond RANGE/GROUPS.
> After this code review round is complete, I would like to regroup and
> discuss
> the direction forward.
> 
> Best regards,
> Henson