MIME-Version: 1.0
References: <20260302.141823.129040280886273031.ishii@postgresql.org>
 <20260304.162450.1463961082114305353.ishii@postgresql.org>
 <CAAAe_zAk3zRy4e=St-_7jqUB1cbw08B6-4Giwr+1Ei570J0byg@mail.gmail.com>
 <20260304.171203.445020956605509539.ishii@postgresql.org>
 <CAE+cgNiUbKeH1A0PoxV2QjpsoxJLe+pJcGz_gdxwOwu_9zqchw@mail.gmail.com>
In-Reply-To: 
 <CAE+cgNiUbKeH1A0PoxV2QjpsoxJLe+pJcGz_gdxwOwu_9zqchw@mail.gmail.com>
Reply-To: assam258@gmail.com
From: Henson Choi <assam258@gmail.com>
Date: Thu, 5 Mar 2026 13:59:41 +0900
Message-ID: 
 <CAAAe_zBFKp7bn9YUamzNiy7s2LQ3C9VXsFLRTyVTbk+ETLfZUQ@mail.gmail.com>
Subject: Re: Row pattern recognition
To: =?UTF-8?B?7J6l7ISx7KSA?= <sjjang112233@gmail.com>,
	Tatsuo Ishii <ishii@postgresql.org>
Cc: 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="000000000000928530064c3fce38"
Archived-At: 
 <https://www.postgresql.org/message-id/CAAAe_zBFKp7bn9YUamzNiy7s2LQ3C9VXsFLRTyVTbk%2BETLfZUQ%40mail.gmail.com>
Precedence: bulk

--000000000000928530064c3fce38
Content-Type: text/plain; charset="UTF-8"

Hi Tatsuo,

PostgreSQL bug: zero-min reluctant quantifier
>
> During cross-validation a PostgreSQL bug was discovered involving
> reluctant quantifiers whose minimum repetition is 0.
>
> Example pattern:
>
>   PATTERN (A*?)
>   DEFINE A AS val > 0
>
> Result comparison:
>
> pattern   PostgreSQL (cnt)   Oracle (cnt)
> A*?       1,1,1              0,0,0
> A??       1,1,1              0,0,0
> A+?       1,1,1              1,1,1
>
> For reluctant quantifiers with min=0 (such as *? and ??), PostgreSQL
> always consumes at least one row, while Oracle allows a zero-length
> match. When min>=1 (e.g., A+?), both systems behave the same.
>

This is indeed a bug. Thanks SugJun for finding it. I'll fix this in
the next patch.

Design difference: unused DEFINE variables
>
> Example:
>
>   PATTERN (A+)
>   DEFINE A AS id > 0, B AS id > 5
>
> PostgreSQL executes the query successfully and ignores the unused
> variable B.
>
> Oracle raises:
>
>   ORA-62503: illegal variable definition
>

Currently PostgreSQL silently removes unused DEFINE variables during
optimization. Do you think we should raise an error instead, as Oracle
does?


> Oracle limitations observed
>
>
> Bounded quantifier limit
>
> A{200}  -> works
> A{201}  -> ORA-62518
>
> Oracle appears to limit the upper bound of bounded quantifiers to 200,
> while PostgreSQL does not impose this restriction.
>

I don't think we need to impose an artificial limit like Oracle's 200.
What do you think?


> Nested nullable quantifiers
>
> Examples:
>
>   (A*)*
>   (A*)+
>   (((A)*)*)*
>
>   (A?|B){1,2}
>   ((A?){2,3}){2,3}
>   (A?){n,m}
>   (A? B?){2,3}
>
> Oracle raises:
>
>   ORA-62513
>
> when a nullable subpattern is wrapped by an outer quantifier, while
> PostgreSQL executes these patterns successfully.
>

This seems like an Oracle limitation rather than a standard requirement.

Best regards,
Henson

>

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

<div dir=3D"ltr"><div dir=3D"ltr">Hi Tatsuo,<br></div><div class=3D"gmail_q=
uote gmail_quote_container"><div dir=3D"ltr" class=3D"gmail_attr"><br></div=
><blockquote class=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"><div dir=3D"ltr">PostgreSQL bug: zero-min reluctant quant=
ifier<br><br>During cross-validation a PostgreSQL bug was discovered involv=
ing<br>reluctant quantifiers whose minimum repetition is 0.<br><br>Example =
pattern:<br><br>=C2=A0 PATTERN (A*?)<br>=C2=A0 DEFINE A AS val &gt; 0<br><b=
r>Result comparison:<br><br>pattern =C2=A0 PostgreSQL (cnt) =C2=A0 Oracle (=
cnt)<br>A*? =C2=A0 =C2=A0 =C2=A0 1,1,1 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A00,0,0<br>A?? =C2=A0 =C2=A0 =C2=A0 1,1,1 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A00,0,0<br>A+? =C2=A0 =C2=A0 =C2=A0 1,1,1 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A01,1,1<br><br>For reluctant quantif=
iers with min=3D0 (such as *? and ??), PostgreSQL<br>always consumes at lea=
st one row, while Oracle allows a zero-length<br>match. When min&gt;=3D1 (e=
.g., A+?), both systems behave the same.<br></div></blockquote><div><br></d=
iv><div>This is indeed a bug. Thanks SugJun for finding it. I&#39;ll fix th=
is in<br>the next patch.</div><div><br></div><blockquote class=3D"gmail_quo=
te" style=3D"margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-sty=
le:solid;border-left-color:rgb(204,204,204);padding-left:1ex"><div dir=3D"l=
tr">Design difference: unused DEFINE variables<br><br>Example:<br><br>=C2=
=A0 PATTERN (A+)<br>=C2=A0 DEFINE A AS id &gt; 0, B AS id &gt; 5<br><br>Pos=
tgreSQL executes the query successfully and ignores the unused<br>variable =
B.<br><br>Oracle raises:<br><br>=C2=A0 ORA-62503: illegal variable definiti=
on<br></div></blockquote><div><br></div><div>Currently PostgreSQL silently =
removes unused DEFINE variables during<br>optimization. Do you think we sho=
uld raise an error instead, as Oracle<br>does?<br></div><div>=C2=A0</div><b=
lockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-le=
ft-width:1px;border-left-style:solid;border-left-color:rgb(204,204,204);pad=
ding-left:1ex"><div dir=3D"ltr">Oracle limitations observed<br><br><br>Boun=
ded quantifier limit<br><br>A{200} =C2=A0-&gt; works<br>A{201} =C2=A0-&gt; =
ORA-62518<br><br>Oracle appears to limit the upper bound of bounded quantif=
iers to 200,<br>while PostgreSQL does not impose this restriction.<br></div=
></blockquote><div><br></div><div>I don&#39;t think we need to impose an ar=
tificial limit like Oracle&#39;s 200.<br>What do you think?<br></div><div>=
=C2=A0</div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0=
.8ex;border-left-width:1px;border-left-style:solid;border-left-color:rgb(20=
4,204,204);padding-left:1ex"><div dir=3D"ltr">Nested nullable quantifiers<b=
r><br>Examples:<br><br>=C2=A0 (A*)*<br>=C2=A0 (A*)+<br>=C2=A0 (((A)*)*)*<br=
><br>=C2=A0 (A?|B){1,2}<br>=C2=A0 ((A?){2,3}){2,3}<br>=C2=A0 (A?){n,m}<br>=
=C2=A0 (A? B?){2,3}<br><br>Oracle raises:<br><br>=C2=A0 ORA-62513<br><br>wh=
en a nullable subpattern is wrapped by an outer quantifier, while<br>Postgr=
eSQL executes these patterns successfully.<br></div></blockquote><div><br><=
/div><div>This seems like an Oracle limitation rather than a standard requi=
rement.<br><br>Best regards,<br>Henson<br></div><blockquote class=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">
</blockquote></div></div>

--000000000000928530064c3fce38--