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