You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by bupt_ljy <bu...@163.com> on 2018/12/14 08:37:20 UTC
Problems about subsets clause order for MATCH_RECOGNIZE
Hi all,
It’s my first time to send emails to Calcite developers. It’s a really good project and many projects benefit from it.
Now I’ve encountered a problem about the subsets for MATCH_RECOGNIZE in thetestMatchRecognizeSubset1() testing. From the results, I can tell that"subset stdn = (strt, down)"will be explained to "SUBSET \"STDN\" = (\"DOWN\", \"STRT\”)”, which confuses me. IMO, It’ll affect the result of functions like“FIRST” and“LAST”, which may not be what I want, although it works fine with“AVG” function.
I’m not sure if this is a bug, or anyone can tell me how we arrive here?
Best,
Jiayi Liao
Re: Problems about subsets clause order for MATCH_RECOGNIZE
Posted by Dawid Wysakowicz <dw...@apache.org>.
Hi Jiayi,
I don't think it should any difference what is the order in the SUBSET
clause. The PATTERN clause specifies the definitive order of pattern
variables. SUBSET clause just groups them as a single entity you can
reference. Therefore LAST/FIRST/PREV/NEXT/AFTER MATCH etc. will take
into account only order in the PATTERN clause.
Best,
Dawid
On 16/12/2018 08:07, Julian Hyde wrote:
> I don’t understand MATCH_RECOGNIZE well enough to give an opinion. Is there a query that gives different results on Oracle if you change the order of items in SUBSET?
>
> It seems that the parser preserves the order of items in the subset, but the SqlToRelConverter does not, hence the line "subsets=[[[DOWN, STRT]]” in SqlToRelConverterTest.xml. I would be concerned if the parser re-ordered things, but I am not too concerned about SqlToRelConverter unless the semantics are wrong.
>
>> On Dec 14, 2018, at 12:37 AM, bupt_ljy <bu...@163.com> wrote:
>>
>> Hi all,
>> It’s my first time to send emails to Calcite developers. It’s a really good project and many projects benefit from it.
>> Now I’ve encountered a problem about the subsets for MATCH_RECOGNIZE in thetestMatchRecognizeSubset1() testing. From the results, I can tell that"subset stdn = (strt, down)"will be explained to "SUBSET \"STDN\" = (\"DOWN\", \"STRT\”)”, which confuses me. IMO, It’ll affect the result of functions like“FIRST” and“LAST”, which may not be what I want, although it works fine with“AVG” function.
>> I’m not sure if this is a bug, or anyone can tell me how we arrive here?
>>
>>
>>
>>
>> Best,
>> Jiayi Liao
Re: Problems about subsets clause order for MATCH_RECOGNIZE
Posted by Julian Hyde <jh...@apache.org>.
I don’t understand MATCH_RECOGNIZE well enough to give an opinion. Is there a query that gives different results on Oracle if you change the order of items in SUBSET?
It seems that the parser preserves the order of items in the subset, but the SqlToRelConverter does not, hence the line "subsets=[[[DOWN, STRT]]” in SqlToRelConverterTest.xml. I would be concerned if the parser re-ordered things, but I am not too concerned about SqlToRelConverter unless the semantics are wrong.
> On Dec 14, 2018, at 12:37 AM, bupt_ljy <bu...@163.com> wrote:
>
> Hi all,
> It’s my first time to send emails to Calcite developers. It’s a really good project and many projects benefit from it.
> Now I’ve encountered a problem about the subsets for MATCH_RECOGNIZE in thetestMatchRecognizeSubset1() testing. From the results, I can tell that"subset stdn = (strt, down)"will be explained to "SUBSET \"STDN\" = (\"DOWN\", \"STRT\”)”, which confuses me. IMO, It’ll affect the result of functions like“FIRST” and“LAST”, which may not be what I want, although it works fine with“AVG” function.
> I’m not sure if this is a bug, or anyone can tell me how we arrive here?
>
>
>
>
> Best,
> Jiayi Liao