[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: Problems about subsets clause order for MATCH_RECOGNIZE

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.



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 <bupt_ljy@xxxxxxx> 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

Attachment: signature.asc
Description: OpenPGP digital signature