Re: Row pattern recognition

Hi Tatsuo,

Thank you for the detailed analysis and references to the standard.

However in my uderstanding the SQL standard allows A{0}.
>
> PATTERN (A{0} B)
> is equivalant to:
> PATTERN (B)
>

I agree. We should support A{0} to comply with the standard.


> BTW, after studied this more, I found that A{0,0} is not allowed. In
> this form the right hand side number shall be greater than 0. From
> ISO/IEC 9075-2 7.9 <row pattern syntax> "Syntax Rules 20)
>
> "If <left brace> <unsigned integer> <comma> <unsigned integer> <right
> brace> is specified, then let VUI1 and VUI2 be the values of the first
> and second <unsigned integer>'s, respectively. VUI1 shall be less than
> or equal to VUI2, and VUI2 shall be greater than 0 (zero)."
>


Good catch. I agree we should follow the standard strictly. Since I
don't have direct access to the ISO/IEC 9075-2 document, I trust your
interpretation that A{0,0} should be rejected per SR 20.

However, this raises interesting questions: should we optimize patterns
by removing {0} quantifiers or simplifying them? And if so, how should
we handle patterns that become empty after such optimization?

For example:
- PATTERN (A{0})          → empty pattern
- PATTERN (A{0} B{0})     → empty pattern
- PATTERN (A{0} B)        → PATTERN (B) after optimization

Empty patterns would result in zero-length matches, which our current
implementation explicitly treats as invalid (see initialAdvance flag
logic in nodeWindowAgg.c).

More importantly, I recall that zero-length matches caused serious
issues during development, which is why we added logic to explicitly
avoid them.

The reason I cannot immediately provide a concrete plan for A{0}
support is that I need to deeply understand the semantic meaning of
zero-length matches in the SQL standard first. Without this
understanding, any implementation approach could be fundamentally
flawed.

Specifically, I need to investigate:
- What zero-length matches mean semantically in RPR
- How to handle empty patterns according to the standard
- The correct behavior when a pattern optimizes to nothing

After the current code review phase is complete, I'm also considering
setting up an Oracle test environment to observe how it handles these
edge cases. This could provide valuable insights into the expected
behavior, especially for zero-length matches and empty patterns.

Do you have insights on how the standard handles empty patterns or
zero-length matches?

Best regards,
Henson