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

>