Re: Row pattern recognition

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