Re: Row pattern recognition

>> What is PATH function? It's not in R010 or R020 as far as I know.
>>
> 
> A Match Path is essentially an array consisting of the CLASSIFIER() values
> of all rows
> within a matched sequence, arranged in chronological order.

So, the PATH function is Oracle specific? Or your own proposal?

>> > Longer-term, the goal is to get to MATCH_RECOGNIZE in the FROM clause for
>> > full SQL standard compliance.
>>
>> What about MEASURES clause? Without it, many things in the standard
>> cannot be implemented.
>>
> 
> I admit that my current understanding of the full SQL standard is still
> evolving. However,
> looking at the current codebase, I believed that the syntax I've
> implemented so far
> could be addressed even before mastering every detail of the standard.
> That is why I have taken a 'code-first' approach for this stage.
> 
> Of course, reaching 'FULL' compliance is undoubtedly the ultimate goal.
> Given
> the complexity, I believe the features should be released in several
> incremental phases.

I totally agree with the incremental approach.

> I trust that you will help define the strategic roadmap for these releases.
> As I continue to
> deepen my understanding of the standard, I will do my best to contribute to
> the
> technical roadmap as well.

Ok, here is my proposal for the strategic roadmap. This should be a
starting point of the discussion.

(1) The first release: this will a subset of R020 (RPR in Window
    clause), but will serve many of pratical uses of RPR.  The
    following features below will not be included.

    - MEASURES
    - SUBSET
    - SEEK
    - AFTER MATCH SKIP TO
    - AFTER MATCH SKIP TO FIRST
    - AFTER MATCH SKIP TO LAST
    - PERMUTE

Note 1: this list needs discussion and may be changed when the release
is made.

Note 2: Because MEASURES cannot be used in this release, all primary
row patter variables in the DEFINE clause must be unqualified ones
(i.e. "price", not "A.price") and they are implicitly qualified by the
universal row pattern variable. In this case the universal row pattern
variable is mapped to the current row, thus "price" in the DEFINE
clause is evaluated in the current row.

After (1) we can choose either (a) extend support for R020, i.e. add
more features to the first release, especially MEASURES or (b) support
a subset of R010 (MATCH_RECOGNIZE).

>> > Let me know if you have any concerns or suggestions about the approach.

> I will rebase my work on your latest v37 patch and move forward with that
> for the next version. I’ll submit the updated patch soon.

Thanks!

> I completely agree that those optimizations belong in the optimizer/planner
> phase.
> I intentionally placed them in the parse analysis phase for now to make
> debugging
> easier during this initial development stage. Once the logic is stabilized,
> I will move
> them to the planner as you suggested to ensure proper deparsing.

Understood. Looks like a good strategy.

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