Re: Row pattern recognition

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

>