Nested match_recognize query not supported in SQL ?

[Tejas]

Hi,
I am using flink 1.11 and trying nested query where match_recognize is
inside, as shown below :
/select * from events where id = (SELECT * FROM events MATCH_RECOGNIZE
(PARTITION BY org_id ORDER BY proctime MEASURES A.id AS startId ONE ROW PER
MATCH PATTERN (A C* B) DEFINE A AS A.tag = 'tag1', C AS C.tag <> 'tag2', B
AS B.tag = 'tag2'));/

And I am getting an error as :
/org.apache.calcite.sql.validate.SqlValidatorException: Table 'A' not found/

Is this not supported ? If not what's the alternative ?



--
Sent from: http://apache-flink-user-mailing-list-archive.2336050.n4.nabble.com/

[David Anderson-4]

I was able to get something like this working, but only by introducing a view:

CREATE TEMPORARY VIEW mmm AS SELECT id FROM events MATCH_RECOGNIZE (...);

SELECT * FROM event WHERE id IN (SELECT id FROM mmm);

Regards,

David

On Tue, May 11, 2021 at 9:22 PM Tejas <[hidden email]> wrote:

Hi,
I am using flink 1.11 and trying nested query where match_recognize is
inside, as shown below :
/select * from events where id = (SELECT * FROM events MATCH_RECOGNIZE
(PARTITION BY org_id ORDER BY proctime MEASURES A.id AS startId ONE ROW PER
MATCH PATTERN (A C* B) DEFINE A AS A.tag = 'tag1', C AS C.tag <> 'tag2', B
AS B.tag = 'tag2'));/

And I am getting an error as :
/org.apache.calcite.sql.validate.SqlValidatorException: Table 'A' not found/

Is this not supported ? If not what's the alternative ?



--
Sent from: http://apache-flink-user-mailing-list-archive.2336050.n4.nabble.com/

[David Anderson-4]

By the way, views that use MATCH_RECOGNIZE don't work in Flink 1.11. [1]

[1] https://issues.apache.org/jira/browse/FLINK-20077

On Thu, May 13, 2021 at 11:06 AM David Anderson <[hidden email]> wrote:

I was able to get something like this working, but only by introducing a view:

CREATE TEMPORARY VIEW mmm AS SELECT id FROM events MATCH_RECOGNIZE (...);

SELECT * FROM event WHERE id IN (SELECT id FROM mmm);

Regards,

David

On Tue, May 11, 2021 at 9:22 PM Tejas <[hidden email]> wrote:

Hi,
I am using flink 1.11 and trying nested query where match_recognize is
inside, as shown below :
/select * from events where id = (SELECT * FROM events MATCH_RECOGNIZE
(PARTITION BY org_id ORDER BY proctime MEASURES A.id AS startId ONE ROW PER
MATCH PATTERN (A C* B) DEFINE A AS A.tag = 'tag1', C AS C.tag <> 'tag2', B
AS B.tag = 'tag2'));/

And I am getting an error as :
/org.apache.calcite.sql.validate.SqlValidatorException: Table 'A' not found/

Is this not supported ? If not what's the alternative ?



--
Sent from: http://apache-flink-user-mailing-list-archive.2336050.n4.nabble.com/