Match_Recognize in Snowflake is not returning what I think it should

I'm sure I'm not understanding this, but this is defined as

PATTERN: Specifying the Pattern to Match PATTERN ( ) The pattern defines a valid sequence of rows that represents a match. The pattern is defined like a regular expression (regex) and is built from symbols, operators, and quantifiers.

An example was:

For example, suppose that symbol S1 is defined as stock_price < 55, and symbol S2 is defined as stock price > 55. The following pattern specifies a sequence of rows in which the stock price increased from less than 55 to greater than 55:

PATTERN (S1 S2)

So if I do

create or replace table names (id int, name varchar (500), groupid int);     insert into names             select 1, 'andrew', 1             union             select 2, 'andrew2', 1             union             select 3, '3andrew', 1  

And then I do

select * from names  match_recognize(  measures    classifier() as "classifier"    all rows per match    pattern (test test2)    define test as startswith(name, 'and'),    test2 as endswith(name, 'rew')    ) t  ;          

Why do I not get 'andrew' as a record return? If I put either test in the pattern, it does show it. When I put both in, it does not. Instead it is showing 3andrew and andrew2 as the record result which is unexpected for me because the example lead me to believe it works like an AND. Any help is appreciated.

https://stackoverflow.com/questions/67260607/match-recognize-in-snowflake-is-not-returning-what-i-think-it-should April 26, 2021 at 11:46AM