Sqlmesh cannot use match_recognize (trino)

[laugeroenberg]

Hi

We are a team working on a data warehouse for analytics, and are sad to see that currently, sqlmesh with trino does not support match_recognize with all its functionality (as it looks like).

I have a query which works fine in Trino 472 outside of sqlmesh.
In sqlmesh i get:
TrinoUserError: TrinoUserError(type=USER_ERROR, name=SYNTAX_ERROR, message="line 1:3802: mismatched input 'MATCH_RECOGNIZE'. Expecting: ',', 'CROSS', 'EXCEPT', 'FETCH', 'FULL', 'GROUP', 'HAVING', 'INNER', 'INTERSECT', 'JOIN', 'LEFT', 'LIMIT', 'NATURAL', 'OFFSET', 'ORDER', 'RIGHT', 'UNION', 'WHERE', 'WINDOW', 'WITH', <EOF>"

This sqlmesh points to the exact same trino engine as the working one, but the error happens before the trino engine is asked to run the query. I think it is about the parsing of the sql language into sqlmesh.

Is it true that match_recognize is not part i sqlmesh, even though the engine can use it easily? Or is it something you could plan adding into it.
Btw i must say apart from this, sqlmesh is really an amazing tool.

BR, Lauge

[georgesittas]

Hi @laugeroenberg, can you please share the exact query that is being executed? Attaching the log file corresponding to the command that failed would also help greatly. Thanks!

[laugeroenberg]

Hi @laugeroenberg, can you please share the exact query that is being executed? Attaching the log file corresponding to the command that failed would also help greatly. Thanks!

Hi @georgesittas . Yes, Thank you.
I might have found a very unpleasant workaround - i will try to describe it here:

1. After making the query even leaner, the error code changed. It seems before the code was being send to trino and the error happens when sqlmesh sent the code to trino. Before, i combined match_recognize with a left join, and the syntax was reordered so the left join came before the match_recognize clause, causing the message above. That join can be done somewhere else, i just wanted to make match recognize work once.

2. Then i have trimmed the query to the following:

with X as (select timestamp_ts, date_trunc('day', timestamp_ts) as dayy, itemtype, itemid, price from dwh_test.items where timestamp_ts >= date '2025-01-01' ) select * from X MATCH_RECOGNIZE ( partition by itemtype, itemid, dayy order by timestamp_ts measures A.price as first_price, classifier() as cls ALL ROWS PER MATCH SHOW EMPTY MATCHES PATTERN ( A B* ) define B as (abs(price - prev(price)) < 10) ) ;

(The idea is to group rows together from the same day, if some criteria of sufficient similarity is met, here low difference in price).
It works fine in plain trino.

When sqlmesh wants to create the table it does:
CREATE TABLE IF NOT EXISTS "iceberg"."sqlmesh__dwh_test"."dwh_test__items_daily__2593069349__temp" AS WITH "x" AS (SELECT "timestamp_ts", DATE_TRUNC('DAY', "timestamp_ts") AS "dayy", "itemtype", "itemid", "price" FROM "iceberg"."dwh_test"."items" AS "items" WHERE "timestamp_ts" >= CAST('2025-01-01' AS DATE) AND FALSE) SELECT * FROM "x" AS "x" MATCH_RECOGNIZE (PARTITION BY "itemtype", "itemid", "dayy" ORDER BY "timestamp_ts" MEASURES "a"."price" AS "first_price", CLASSIFIER() AS "cls" ALL ROWS PER MATCH SHOW EMPTY MATCHES PATTERN (A B*) DEFINE "b" AS (ABS("price" - PREV("price")) < 10)) WHERE FALSE LIMIT 0

Result:

Error Type | USER_ERROR
Error Code | INVALID_LABEL (96)
Stack Trace | io.trino.spi.TrinoException: line 1:550: defined variable: "b" is not a primary pattern variable 	at io.trino.sql.analyzer.SemanticExceptions.semanticException(SemanticExceptions.java:58) 	at io.trino.sql.analyzer.SemanticExceptions.semanticException(SemanticExceptions.java:52) 	at io.trino.sql.analyzer.PatternRecognitionAnalyzer.analyze(PatternRecognitionAnalyzer.java:103) 	at io.trino.sql.analyzer.StatementAnalyzer$Visitor.visitPatternRecognitionRelation(StatementAnalyzer.java:2715) 	at io.trino.sql.analyzer.StatementAnalyzer$Visitor.visitPatternRecognitionRelation(StatementAnalyzer.java:522) 	at io.trino.sql.tree.PatternRecognitionRelation.accept(PatternRecognitionRelation.java:121) 	at 
...

3. Now if you look closely at the code sqlmesh executed in trino. It kept PATTERN (A B*) exactly as the characters were. But it changed define B to define "b" - and the error is especially that "b" was not found in the pattern.
   So now i changed my code to be PATTERN ("a" "b"*) - and now the define "b" matches the "b" in pattern and the code works.
   So apparently, trino in general is not case sensitive (the documentation in trino suggest capital letters here), but in sqlmesh when it translates references to strings "b", capitalization does make a difference, but the pattern clause here is not adapted the same way.

Hope this makes some sense - i guess it could be great if the pattern clause will match the definition of the cases.
If the first error type comes up again, i might come back.
BR