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>
In-Reply-To: <20260304.171203.445020956605509539.ishii@postgresql.org>
From: =?UTF-8?B?7J6l7ISx7KSA?= <sjjang112233@gmail.com>
Date: Thu, 5 Mar 2026 13:16:21 +0900
Message-ID: 
 <CAE+cgNiUbKeH1A0PoxV2QjpsoxJLe+pJcGz_gdxwOwu_9zqchw@mail.gmail.com>
Subject: Re: Row pattern recognition
To: Tatsuo Ishii <ishii@postgresql.org>, assam258@gmail.com
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="000000000000ae2ba1064c3f337e"
Archived-At: 
 <https://www.postgresql.org/message-id/CAE%2BcgNiUbKeH1A0PoxV2QjpsoxJLe%2BpJcGz_gdxwOwu_9zqchw%40mail.gmail.com>
Precedence: bulk

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

Hi hackers

I converted PostgreSQL RPR regression test queries to Oracle
MATCH_RECOGNIZE syntax and executed them on both systems to perform
cross-validation.

The tests were based on the following PostgreSQL regression test files:

  rpr_base.sql
  rpr_nfa.sql


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 behavior was consistently observed across the converted tests.


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


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.


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.


These results come from running the converted PostgreSQL RPR regression
tests on Oracle for comparison.

Best regards,
SugJun

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

<div dir=3D"ltr">Hi hackers<br><br>I converted PostgreSQL RPR regression te=
st queries to Oracle<br>MATCH_RECOGNIZE syntax and executed them on both sy=
stems to perform<br>cross-validation.<br><br>The tests were based on the fo=
llowing PostgreSQL regression test files:<br><br>=C2=A0 rpr_base.sql<br>=C2=
=A0 rpr_nfa.sql<br><br><br>PostgreSQL bug: zero-min reluctant quantifier<br=
><br>During cross-validation a PostgreSQL bug was discovered involving<br>r=
eluctant 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><br>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 quantifiers wi=
th min=3D0 (such as *? and ??), PostgreSQL<br>always consumes at least one =
row, while Oracle allows a zero-length<br>match. When min&gt;=3D1 (e.g., A+=
?), both systems behave the same.<br><br>This behavior was consistently obs=
erved across the converted tests.<br><br><br>Design difference: unused DEFI=
NE 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>PostgreSQL executes the query successfu=
lly and ignores the unused<br>variable B.<br><br>Oracle raises:<br><br>=C2=
=A0 ORA-62503: illegal variable definition<br><br><br>Oracle limitations ob=
served<br><br><br>Bounded 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 bo=
und of bounded quantifiers to 200,<br>while PostgreSQL does not impose this=
 restriction.<br><br><br>Nested nullable quantifiers<br><br>Examples:<br><b=
r>=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>when a nullable subpatte=
rn is wrapped by an outer quantifier, while<br>PostgreSQL executes these pa=
tterns successfully.<br><br><br>These results come from running the convert=
ed PostgreSQL RPR regression<br>tests on Oracle for comparison.<br><br>Best=
 regards,<br>SugJun</div>

--000000000000ae2ba1064c3f337e--