[bafe]

All great features, but unfortunately most SQL DBs still miss the implementation of features from SQL:2016 like MATCH_RECOGNIZE. I wonder what's the purpose of an ever growing standard when most implementations only support a small subset of it, and often with nonstandard syntax and semantics

[hashhar]

https://trino.io/docs/current/sql/match-recognize.html

[bafe]

Great thanks, if I understand it right, instead of having a new database engine, Trino compiles the statement into the query languages of the different backends and runs these queries in a distributed way?

[hashhar]

Exactly. It provides an API using which you can build connectors to whatever systems you want. e.g. Here's a connector for GitHub API https://github.com/nineinchnick/trino-rest/tree/master/trino....

It doesn't have to be SQL based systems on the other end - the most used connector with Trino is to query files on object storage (S3/GCS/Azure Blob).

Disclaimer: I'm one of the maintainers of the project.

[bafe]

That's great! How about performance? Say I want to use MATCH_RECOGNIZE on a postgresql backend, is the compiled query going to be significantly less performant as compared as the same query on databases that support (oracle) that statement natively?

[hashhar]

For complex things like MATCH_RECOGNIZE (and CASTs) whose syntax and semantics differ across underlying systems unfortunately the result will be that some data is going to be pulled and be processed in Trino - so it'll be slower than native. If you are only dealing with a single data source (unless it's not an RDBMS, say files on S3 or an API) I'd say Trino is not needed and would slow you down.

The rule of thumb is that Trino aims to provide a uniform layer over whatever sits underneath. So operations which when "pushded down" to the source result in same results as when executed within Trino do get "pushed down" - i.e. executed on the source. But in cases where the results might differ or it's complex to push-down the operation the operation runs within Trino - i.e. pull data from source (minimum needed data) and then perform operation within Trino.

Note that it's not an all-or-nothing case, e.g. a query like:

    SELECT n.name, r.name
    FROM postgresql.tpch.nation n
    LEFT JOIN postgresql.tpch.region r ON r.regionkey = n.regionkey
    WHERE n.name > 'A'
        AND r.name = 'ASIA'

will result in the following query to Postgres:

    SELECT n.name, r.name FROM tpch.nation n LEFT JOIN tpch.region r ON r.regionkey = n.regionkey WHERE r.name = 'ASIA'

The rest of the query (n.name > 'A') would be applied in Trino to results fetched from Postgres because the collation in Postgres will affect results if we push complete query down to Postgres and may not match results when entire query would be processed by Trino. With single data source this is not easily appreciated but e.g. if the second table in the query came from SQL Server then you'd want to have a consistent comparision logic regardless of source of table.

This is a very simplified example though.

[bafe]

Thanks for the clarification! I'll read more about it, I'm quite curious to see how these clauses are implemented in Trino