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?
