MIME-Version: 1.0
References: <20260209.190209.470246357440525054.ishii@postgresql.org>
 <CAAAe_zDX24SnaQFwTNVUx_vK5887mpD+rXTaedjzdfFrGROFGA@mail.gmail.com>
 <20260211.125032.1207789314761104094.ishii@postgresql.org> <20260212.101701.1434868071304923886.ishii@postgresql.org>
In-Reply-To: <20260212.101701.1434868071304923886.ishii@postgresql.org>
Reply-To: assam258@gmail.com
From: Henson Choi <assam258@gmail.com>
Date: Thu, 12 Feb 2026 21:04:52 +0900
Message-ID: <CAAAe_zDPaPiRg2gn=kRtrh+VTis5EDUeCftBK46vKsZjUHAEtA@mail.gmail.com>
Subject: Re: Row pattern recognition
To: Tatsuo Ishii <ishii@postgresql.org>
Cc: ssam258@gmail.com, vik@postgresfriends.org, er@xs4all.nl, 
	jacob.champion@enterprisedb.com, david.g.johnston@gmail.com, 
	peter@eisentraut.org, pgsql-hackers@postgresql.org
Content-Type: multipart/alternative; boundary="00000000000085404e064a9f4c07"
Archived-At: <https://www.postgresql.org/message-id/CAAAe_zDPaPiRg2gn%3DkRtrh%2BVTis5EDUeCftBK46vKsZjUHAEtA%40mail.gmail.com>
Precedence: bulk

--00000000000085404e064a9f4c07
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Hi Tatsuo,

Thank you for the detailed analysis and references to the standard.

However in my uderstanding the SQL standard allows A{0}.
>
> PATTERN (A{0} B)
> is equivalant to:
> PATTERN (B)
>

I agree. We should support A{0} to comply with the standard.


> BTW, after studied this more, I found that A{0,0} is not allowed. In
> this form the right hand side number shall be greater than 0. From
> ISO/IEC 9075-2 7.9 <row pattern syntax> "Syntax Rules 20)
>
> "If <left brace> <unsigned integer> <comma> <unsigned integer> <right
> brace> is specified, then let VUI1 and VUI2 be the values of the first
> and second <unsigned integer>'s, respectively. VUI1 shall be less than
> or equal to VUI2, and VUI2 shall be greater than 0 (zero)."
>


Good catch. I agree we should follow the standard strictly. Since I
don't have direct access to the ISO/IEC 9075-2 document, I trust your
interpretation that A{0,0} should be rejected per SR 20.

However, this raises interesting questions: should we optimize patterns
by removing {0} quantifiers or simplifying them? And if so, how should
we handle patterns that become empty after such optimization?

For example:
- PATTERN (A{0})          =E2=86=92 empty pattern
- PATTERN (A{0} B{0})     =E2=86=92 empty pattern
- PATTERN (A{0} B)        =E2=86=92 PATTERN (B) after optimization

Empty patterns would result in zero-length matches, which our current
implementation explicitly treats as invalid (see initialAdvance flag
logic in nodeWindowAgg.c).

More importantly, I recall that zero-length matches caused serious
issues during development, which is why we added logic to explicitly
avoid them.

The reason I cannot immediately provide a concrete plan for A{0}
support is that I need to deeply understand the semantic meaning of
zero-length matches in the SQL standard first. Without this
understanding, any implementation approach could be fundamentally
flawed.

Specifically, I need to investigate:
- What zero-length matches mean semantically in RPR
- How to handle empty patterns according to the standard
- The correct behavior when a pattern optimizes to nothing

After the current code review phase is complete, I'm also considering
setting up an Oracle test environment to observe how it handles these
edge cases. This could provide valuable insights into the expected
behavior, especially for zero-length matches and empty patterns.

Do you have insights on how the standard handles empty patterns or
zero-length matches?

Best regards,
Henson

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

<div dir=3D"ltr"><div dir=3D"ltr"><div dir=3D"ltr">Hi Tatsuo,<br><br>Thank =
you for the detailed analysis and references to the standard.</div><div dir=
=3D"ltr"><br></div><div class=3D"gmail_quote"><blockquote class=3D"gmail_qu=
ote" style=3D"margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-st=
yle:solid;border-left-color:rgb(204,204,204);padding-left:1ex">
However in my uderstanding the SQL standard allows A{0}.<br>
<br>
PATTERN (A{0} B)<br>
is equivalant to:<br>
PATTERN (B)<br></blockquote><div><br></div><div>I agree. We should support =
A{0} to comply with the standard.<br></div><div>=C2=A0</div><blockquote cla=
ss=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left-width:1px;=
border-left-style:solid;border-left-color:rgb(204,204,204);padding-left:1ex=
">
BTW, after studied this more, I found that A{0,0} is not allowed. In<br>
this form the right hand side number shall be greater than 0. From<br>
ISO/IEC 9075-2 7.9 &lt;row pattern syntax&gt; &quot;Syntax Rules 20)<br>
<br>
&quot;If &lt;left brace&gt; &lt;unsigned integer&gt; &lt;comma&gt; &lt;unsi=
gned integer&gt; &lt;right<br>
brace&gt; is specified, then let VUI1 and VUI2 be the values of the first<b=
r>
and second &lt;unsigned integer&gt;&#39;s, respectively. VUI1 shall be less=
 than<br>
or equal to VUI2, and VUI2 shall be greater than 0 (zero).&quot;<br></block=
quote><div><br></div><div><br></div><div>Good catch. I agree we should foll=
ow the standard strictly. Since I<br>don&#39;t have direct access to the IS=
O/IEC 9075-2 document, I trust your<br>interpretation that A{0,0} should be=
 rejected per SR 20.<br><br>However, this raises interesting questions: sho=
uld we optimize patterns<br>by removing {0} quantifiers or simplifying them=
? And if so, how should<br>we handle patterns that become empty after such =
optimization?<br><br>For example:<br>- PATTERN (A{0}) =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0=E2=86=92 empty pattern<br>- PATTERN (A{0} B{0}) =C2=A0 =C2=A0=
 =E2=86=92 empty pattern<br>- PATTERN (A{0} B) =C2=A0 =C2=A0 =C2=A0 =C2=A0=
=E2=86=92 PATTERN (B) after optimization<br><br>Empty patterns would result=
 in zero-length matches, which our current<br>implementation explicitly tre=
ats as invalid (see initialAdvance flag<br>logic in nodeWindowAgg.c).<br><b=
r>More importantly, I recall that zero-length matches caused serious<br>iss=
ues during development, which is why we added logic to explicitly<br>avoid =
them.<br><br>The reason I cannot immediately provide a concrete plan for A{=
0}<br>support is that I need to deeply understand the semantic meaning of<b=
r>zero-length matches in the SQL standard first. Without this<br>understand=
ing, any implementation approach could be fundamentally<br>flawed.<br><br>S=
pecifically, I need to investigate:<br>- What zero-length matches mean sema=
ntically in RPR<br>- How to handle empty patterns according to the standard=
<br>- The correct behavior when a pattern optimizes to nothing<br><br>After=
 the current code review phase is complete, I&#39;m also considering<br>set=
ting up an Oracle test environment to observe how it handles these<br>edge =
cases. This could provide valuable insights into the expected<br>behavior, =
especially for zero-length matches and empty patterns.<br><br></div><div>Do=
 you have insights on how the standard handles empty patterns or<br>zero-le=
ngth matches?<br><br>Best regards,<br>Henson</div><div><br></div></div></di=
v>
</div>

--00000000000085404e064a9f4c07--