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>
 <CACJufxG6rKfd0WtsVZgzcoh8vOEwo_8UDWkuOd888ATavNv_uw@mail.gmail.com>
In-Reply-To: 
 <CACJufxG6rKfd0WtsVZgzcoh8vOEwo_8UDWkuOd888ATavNv_uw@mail.gmail.com>
Reply-To: assam258@gmail.com
From: Henson Choi <assam258@gmail.com>
Date: Fri, 19 Jun 2026 21:08:19 +0900
Message-ID: 
 <CAAAe_zAsqKocS-Mfj2aSnJBhVyHgHUzp_TUOzJRrZR8kYsn_Rg@mail.gmail.com>
Subject: Re: Row pattern recognition
To: jian he <jian.universality@gmail.com>
Cc: Tatsuo Ishii <ishii@postgresql.org>, 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="000000000000b86e4606549a263d"
Archived-At: 
 <https://www.postgresql.org/message-id/CAAAe_zAsqKocS-Mfj2aSnJBhVyHgHUzp_TUOzJRrZR8kYsn_Rg%40mail.gmail.com>
Precedence: bulk

--000000000000b86e4606549a263d
Content-Type: text/plain; charset="UTF-8"

Hi Tatsuo,

> From ISO/IEC 9075-2:2016 7.15 <window clause> General Rules:
> ...
> a) If SL does not simply contain a <window function>, then the <window
> clause> is disregarded, and the result of TE is the result of the last
> <from clause>, <where clause>, <group by clause> or <having clause> of TE.

Thank you -- this is exactly the text I was missing.  I only have ISO/IEC
19075-5 (the guidance / technical report), which describes what a window
definition does but says nothing about a window clause being disregarded
when no window function references it.  The General Rule you quote settles
it: disregarding an unused window clause is required by the standard, and
since DEFINE is part of the window clause, it is disregarded along with the
rest.  So evaluating its DEFINE -- option (b) -- would mean running a clause
the standard tells us to ignore.

> But we already pass faulty window clauses. Example:
> ... ERROR:  frame ending offset must not be negative ...
> I think if we detect faulty DEFINE in the last case, it's not only
> against the standard but against our existing behavior.

Agreed, and the parallel is convincing.  An unused window already escapes
the negative-frame-offset check, so singling out DEFINE as the one clause
that must fire regardless would be inconsistent with how the rest of an
unused window already behaves.

This also matches the planner: select_active_windows() drops any window with
no referencing WindowFunc, and its comment already cites the same <window
clause> General Rules (General Rule 4) as its basis.

Either way, the disregard rule governs only execution.  A failure raised in
the parser, transform, rewrite, or planner is a separate, static layer the
rule does not touch.  And at execution there is nothing to evaluate: an
unused window is never turned into a WindowAgg node, so its DEFINE is never
reached.

So I'm convinced -- let's keep (a), the current behavior, and I'll treat
this open question as closed, with no patch change.  For the record, the two
halves stay cleanly separated: the RPR DEFINE volatility check still visits
every window clause at preprocessing (independent of select_active_windows),
while run-time DEFINE evaluation happens only for windows that survive it.

Thanks for digging up the standard text.

Best regards,
Henson

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

<div dir=3D"ltr">Hi Tatsuo,<br><br>&gt; From ISO/IEC 9075-2:2016 7.15 &lt;w=
indow clause&gt; General Rules:<br>&gt; ...<br>&gt; a) If SL does not simpl=
y contain a &lt;window function&gt;, then the &lt;window<br>&gt; clause&gt;=
 is disregarded, and the result of TE is the result of the last<br>&gt; &lt=
;from clause&gt;, &lt;where clause&gt;, &lt;group by clause&gt; or &lt;havi=
ng clause&gt; of TE.<br><br>Thank you -- this is exactly the text I was mis=
sing.=C2=A0 I only have ISO/IEC<br>19075-5 (the guidance / technical report=
), which describes what a window<br>definition does but says nothing about =
a window clause being disregarded<br>when no window function references it.=
=C2=A0 The General Rule you quote settles<br>it: disregarding an unused win=
dow clause is required by the standard, and<br>since DEFINE is part of the =
window clause, it is disregarded along with the<br>rest.=C2=A0 So evaluatin=
g its DEFINE -- option (b) -- would mean running a clause<br>the standard t=
ells us to ignore.<br><br>&gt; But we already pass faulty window clauses. E=
xample:<br>&gt; ... ERROR: =C2=A0frame ending offset must not be negative .=
..<br>&gt; I think if we detect faulty DEFINE in the last case, it&#39;s no=
t only<br>&gt; against the standard but against our existing behavior.<br><=
br>Agreed, and the parallel is convincing.=C2=A0 An unused window already e=
scapes<br>the negative-frame-offset check, so singling out DEFINE as the on=
e clause<br>that must fire regardless would be inconsistent with how the re=
st of an<br>unused window already behaves.<br><br>This also matches the pla=
nner: select_active_windows() drops any window with<br>no referencing Windo=
wFunc, and its comment already cites the same &lt;window<br>clause&gt; Gene=
ral Rules (General Rule 4) as its basis.<br><br>Either way, the disregard r=
ule governs only execution.=C2=A0 A failure raised in<br>the parser, transf=
orm, rewrite, or planner is a separate, static layer the<br>rule does not t=
ouch.=C2=A0 And at execution there is nothing to evaluate: an<br>unused win=
dow is never turned into a WindowAgg node, so its DEFINE is never<br>reache=
d.<br><br>So I&#39;m convinced -- let&#39;s keep (a), the current behavior,=
 and I&#39;ll treat<br>this open question as closed, with no patch change.=
=C2=A0 For the record, the two<br>halves stay cleanly separated: the RPR DE=
FINE volatility check still visits<br>every window clause at preprocessing =
(independent of select_active_windows),<br>while run-time DEFINE evaluation=
 happens only for windows that survive it.<br><br>Thanks for digging up the=
 standard text.<br><br>Best regards,<br>Henson<br></div>

--000000000000b86e4606549a263d--