public inbox for [email protected]  

help / color / mirror / Atom feed

From: Henson Choi <[email protected]>
To: 장성준 <[email protected]>
To: Tatsuo Ishii <[email protected]>
Cc: [email protected]
Cc: [email protected]
Cc: [email protected]
Cc: [email protected]
Cc: [email protected]
Cc: [email protected]
Subject: Re: Row pattern recognition
Date: Thu, 5 Mar 2026 13:59:41 +0900
Message-ID: <CAAAe_zBFKp7bn9YUamzNiy7s2LQ3C9VXsFLRTyVTbk+ETLfZUQ@mail.gmail.com> (raw)
In-Reply-To: <CAE+cgNiUbKeH1A0PoxV2QjpsoxJLe+pJcGz_gdxwOwu_9zqchw@mail.gmail.com>
References: <[email protected]>
	<[email protected]>
	<CAAAe_zAk3zRy4e=St-_7jqUB1cbw08B6-4Giwr+1Ei570J0byg@mail.gmail.com>
	<[email protected]>
	<CAE+cgNiUbKeH1A0PoxV2QjpsoxJLe+pJcGz_gdxwOwu_9zqchw@mail.gmail.com>

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

>

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
  Subject: Re: Row pattern recognition
  In-Reply-To: <CAAAe_zBFKp7bn9YUamzNiy7s2LQ3C9VXsFLRTyVTbk+ETLfZUQ@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox