MIME-Version: 1.0
References: 
 <CAAAe_zAZDuHSiVGvz9c6h=Pe=aN+FKZOrdNPfbTOk3XV+WFKYQ@mail.gmail.com>
 <CAAAe_zDz3z2Paidk3jHOm9S3eMVLoXRxK0Lyo=5i_9-EfSH7fA@mail.gmail.com>
 <20260604.132108.405136284364833955.ishii@postgresql.org>
 <20260609.171307.1883356507067957349.ishii@postgresql.org>
 <CACJufxFnwdQSApt2vWwYCd0gtf+JjFDxT2hbxHi=+dhFJc+-1g@mail.gmail.com>
 <CAAAe_zATnkqsbLYDj8MJV1TriX9Wi0wShDg3nK3qYpiupKwhFA@mail.gmail.com>
 <CAAAe_zBL+J0AYmvmcJQT7Q-gp5aRH0deJ7SE7-N21g4hWExyJw@mail.gmail.com>
 <CACJufxHJFNBJ0vHCCLestWV5b7DF5e4VzfmovqGKBGgqg+rcGA@mail.gmail.com>
 <CAAAe_zBY0rrgf+tKXMUc-Y3nDDD69hddRBKopEKAZobhY=Cy-Q@mail.gmail.com>
 <CAAAe_zDYxq0d3exCDwvKncD0kaL2uehDir6HXo4r5DXMitKrSg@mail.gmail.com>
 <CACJufxG57=ddtbN=5RZCzhxWDYXvocKmB7NtZy+DoqZuhxb_DA@mail.gmail.com>
 <CAAAe_zB4TMHnpaOrwYp7dKs553q2474ZXRytGfYOfYp4DdrgiQ@mail.gmail.com>
 <CACJufxFAQhbOD9EVCTAy-VwDbG4446N10GsxCcgdpFnjHO1Efw@mail.gmail.com>
In-Reply-To: 
 <CACJufxFAQhbOD9EVCTAy-VwDbG4446N10GsxCcgdpFnjHO1Efw@mail.gmail.com>
Reply-To: assam258@gmail.com
From: Henson Choi <assam258@gmail.com>
Date: Tue, 16 Jun 2026 14:32:58 +0900
Message-ID: 
 <CAAAe_zCQaV_im5WNbU1D1HAQDUk3yYK3nSduZZ8Qu_aK-aRA9A@mail.gmail.com>
Subject: Re: Row pattern recognition
To: jian he <jian.universality@gmail.com>, Tatsuo Ishii <ishii@postgresql.org>
Cc: zsolt.parragi@percona.com, sjjang112233@gmail.com,
 vik@postgresfriends.org,
	er@xs4all.nl, jacob.champion@enterprisedb.com, david.g.johnston@gmail.com,
	peter@eisentraut.org, li.evan.chao@gmail.com, pgsql-hackers@postgresql.org
Content-Type: multipart/alternative; boundary="0000000000004f5c6706545847b6"
Archived-At: 
 <https://www.postgresql.org/message-id/CAAAe_zCQaV_im5WNbU1D1HAQDUk3yYK3nSduZZ8Qu_aK-aRA9A%40mail.gmail.com>
Precedence: bulk

--0000000000004f5c6706545847b6
Content-Type: text/plain; charset="UTF-8"

Hi Tatsuo, Jian,

I think there's a correctness problem in the RPR patch: a window function's
result can change depending on which other, unrelated window functions are
in the same query.

Pattern matching only advances when a window function reads the frame.
nth_value(x, n) returns NULL without reading the frame when n is NULL
(correct per the standard), so if it is the only window function in an RPR
window, the match never advances over those rows and the reduced frame no
longer matches a full scan.

Example -- one partition, 60 rows, price = id * 10:

  CREATE TABLE rpr_dormant (id int, price int);
  INSERT INTO rpr_dormant SELECT g, g*10 FROM generate_series(1,60) g;

  SELECT id, nth_value(price, CASE WHEN id < 50 THEN NULL ELSE 1 END) OVER w
  FROM rpr_dormant
  WINDOW w AS (
    ORDER BY id
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    AFTER MATCH SKIP PAST LAST ROW
    PATTERN (A+)
    DEFINE A AS price > PREV(FIRST(price), 50)
  );

Run alone, the nth_value column does not follow the actual match structure;
adding an unrelated first_value(id) OVER w, which reads the frame every row,
changes it.  And while the match is dormant the mark position keeps
advancing, running ahead and trimming rows that the backward navigation
later needs -- so the same query can instead fail with "cannot fetch row N
before WindowObject's mark position".

I think an RPR window should perform the match for each row up front,
building its reduced frame during the row scan before the window functions
are evaluated, regardless of whether any function reads the frame.  The fix
belongs in the executor, not in nth_value -- the early return is standard,
and the same gap is reachable from any user-defined function that skips the
frame.

Does this direction seem right, or is the lazy, frame-driven matching
intentional in a way I'm missing?  Happy to prepare a patch.

Best regards,
Henson

--0000000000004f5c6706545847b6
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Hi Tatsuo, Jian,<br><br>I think there&#39;s a correctness =
problem in the RPR patch: a window function&#39;s<br>result can change depe=
nding on which other, unrelated window functions are<br>in the same query.<=
br><br>Pattern matching only advances when a window function reads the fram=
e.<br>nth_value(x, n) returns NULL without reading the frame when n is NULL=
<br>(correct per the standard), so if it is the only window function in an =
RPR<br>window, the match never advances over those rows and the reduced fra=
me no<br>longer matches a full scan.<br><br><font face=3D"monospace">Exampl=
e -- one partition, 60 rows, price =3D id * 10:<br><br>=C2=A0 CREATE TABLE =
rpr_dormant (id int, price int);<br>=C2=A0 INSERT INTO rpr_dormant SELECT g=
, g*10 FROM generate_series(1,60) g;<br><br>=C2=A0 SELECT id, nth_value(pri=
ce, CASE WHEN id &lt; 50 THEN NULL ELSE 1 END) OVER w<br>=C2=A0 FROM rpr_do=
rmant<br>=C2=A0 WINDOW w AS (<br>=C2=A0 =C2=A0 ORDER BY id<br>=C2=A0 =C2=A0=
 ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING<br>=C2=A0 =C2=A0 AFTER MA=
TCH SKIP PAST LAST ROW<br>=C2=A0 =C2=A0 PATTERN (A+)<br>=C2=A0 =C2=A0 DEFIN=
E A AS price &gt; PREV(FIRST(price), 50)<br>=C2=A0 );</font><br><br>Run alo=
ne, the nth_value column does not follow the actual match structure;<br>add=
ing an unrelated first_value(id) OVER w, which reads the frame every row,<b=
r>changes it.=C2=A0 And while the match is dormant the mark position keeps<=
br>advancing, running ahead and trimming rows that the backward navigation<=
br>later needs -- so the same query can instead fail with &quot;cannot fetc=
h row N<br>before WindowObject&#39;s mark position&quot;.<br><br>I think an=
 RPR window should perform the match for each row up front,<br>building its=
 reduced frame during the row scan before the window functions<br>are evalu=
ated, regardless of whether any function reads the frame.=C2=A0 The fix<br>=
belongs in the executor, not in nth_value -- the early return is standard,<=
br>and the same gap is reachable from any user-defined function that skips =
the<br>frame.<br><br>Does this direction seem right, or is the lazy, frame-=
driven matching<br>intentional in a way I&#39;m missing?=C2=A0 Happy to pre=
pare a patch.<br><br>Best regards,<br>Henson<br></div>

--0000000000004f5c6706545847b6--