Re: Row pattern recognition

Hi Henson,

> 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.

Thanks in advance.

> 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?

No, I don't think so. I think the standard does not say anything if a
pattern variable defined in DEFINE clause is not used in PATTERN
clause. So the expected behavior would be implementation dependent. I
think just ignoring the variable is fine.

>> 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?

Agreed. We do not need to follow Oracle here.

>> 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.

Agreed. For example, the standard explicitly stats A(*)* is permitted
(ISO/IEC 19075-5, 4.1.4.1 Introduction to the PATTEREN syntax).

Best regards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp