Support for Trino DB

[lukaseder]

Formerly known as Presto (#5414), now Trino:
https://trino.io/blog/2020/12/27/announcing-trino.html

Database features / missing features

• No constraints
• No indexes
• No NOT NULL constraints (??): Enforcement of NOT NULL column constraints trinodb/trino#4070
• No DEFAULT
• No identities
• No batches: Implement alternative batch execution mode to emulate batch #14784

Some bugs found:

• [FR] Document rules/limitations of correlated subqueries in Trino trinodb/trino#14199 (found by @GavinRay97)
• JDBC DatabaseMetaData::getDatabaseMajorVersion etc. should cache their result trinodb/trino#16464
• JDBC bind variables aren't supported in JSON_OBJECT and JSON_ARRAY trinodb/trino#16489
• Cannot prepare statement when it contains position function  trinodb/trino#16516
• Can't embed JSON values in JSON_OBJECT or JSON_ARRAY trinodb/trino#16522
• ClassCastException when nesting JSON_OBJECT(.. CAST(ARRAY_AGG(JSON_OBJECT(..)) AS JSON)) trinodb/trino#16523
• Cannot nest aggregate function in JSON_OBJECT() trinodb/trino#16525
• Parser mistakes grouping set specification for ROW in ROLLUP and CUBE trinodb/trino#16981
• Improve error message for LISTAGG window function trinodb/trino#16982
• Wrong error message reported when trying to use ARRAY_AGG with ORDER BY clause as a window function trinodb/trino#16984
• Syntax error when referencing a column name from a table by identical name in ORDER BY trinodb/trino#18726
• Correlated subquery can't have nested subqueries trinodb/trino#18727
• JDBC driver incorrectly binds java.sql.Date values for old dates trinodb/trino#18731

Missing features to work around:

• Support java.time date/time classes in PreparedStatement.setObject trinodb/trino#6299
• Implement JDBC's PreparedStatement.setArray() and setObject() for array types trinodb/trino#16470
• Let io.trino.jdbc.Row implement java.sql.Struct trinodb/trino#16479
• Add support for casting CHAR to JSON and vice versa trinodb/trino#16540
• Add support for casting UUID to JSON and vice versa trinodb/trino#16579
• BlobBinding and ClobBinding should fall back to byte[] and String binding if unavailable #14803
• Add support for casting TIME and TIMESTAMP WITH TIME ZONE to JSON and vice versa trinodb/trino#18733

Also, existing PrestoDB limitations:

• Broaden the join types that can be used with UNNEST. prestodb/presto#8200

---

See also:

• Support for new dialects #14059
• https://hub.docker.com/r/trinodb/trino
• https://trino.io/blog/2020/12/27/announcing-trino.html

[bannmann]

Hint: Depending on the SQL queries one uses, people may have success using jOOQ's PostgreSQL dialects to connect to Trino. We have been using this workaround for more than 4 years now without any trouble.

[HasanAmmori]

@bannmann
I wonder how you connect PostgreSQL dialect to Trino since two dialects are vastly different. I assume, you could write a SQL query, that would work on both engines, but I don't think this is a jOOQ's core idea

[bannmann]

@HasanAmmori I don't know what exactly is affected by the choice of dialects, so I can only guess: maybe the reason is that the product in question does not use jOOQ's query builder or code generation. It merely uses jOOQ to execute user-defined SQL queries and to read result sets. I imagine that is a rather unusual scenario, so if that is the only reason it works, then my hint above is useless to most people :-/

[lukaseder]

A dialect consists of:

• A set of existing feature toggles scattered throughout the API (e.g. is AS a supported keyword for aliasing columns, tables)
• A set of not yet discovered feature toggles (e.g. there might be an alternative aliasing syntax that should be preferred)
• A set of ad-hoc dialect switches and if-elses (this is the biggest part, because a lot of switches are very dialect specific. Just look at the mess that is SQL date time arithmetic)

By now, it takes me about 2 person weeks to fully support a new dialect initially, discounting all the efforts that appear after going live, maintaining the dialect for years. For example, I've already spent almost 2 days getting the Vertica dialect up to date with more recent jOOQ developments, given that a customer had a feature request, and the investment was justified: #11650

Other ORMs which do not offer nearly as much SQL functionality as jOOQ, may find tweaking their internals for new dialects simpler. Their generated SQL is trivial SQL-92 - sometimes not even supporting unions, which are hard enough to get right on their own with all the possible cases of required/optional/forbidden parentheses!

So, the rule of thumb is this: You can get jOOQ to work with an existing dialect on a new database product to some extent. It will never be really as good as you're used to from supported dialects, and you will constantly run into issues, in case of which patching jOOQ or running plain SQL are the only options. We generally do not recommend using jOOQ with an unsupported dialect, but it may still work, and may still be better than any alternative.

Having said so, I'm very open to a sponsored integration project. The financial effort of paying me for a Trino integration is likely less than the effort of patching jOOQ all the time when it doesn't work with Trino, and it will pay off in the long run, after years of Trino support.

[lukaseder]

Looking into this for possible inclusion in jOOQ 3.19

First "fun" roadblock:

SQL Error [13]: Query failed (#20230308_161807_00031_btv7d): line 1:17: Catalog 'memory' does not support non-null column for column name 'i'

Not sure what I should think of this 😅 No support for NOT NULL constraints?

[lukaseder]

Also:

This connector does not support modifying table rows

Gee. Which connector should I use for integration testing, to avoid most quirks / get access to most features? Obviously, I don't want to test them all.

[ThoSap]

Also:

This connector does not support modifying table rows

Gee. Which connector should I use for integration testing, to avoid most quirks / get access to most features? Obviously, I don't want to test them all.

@lukaseder which connector does not support this, I did not find it in the docs:
https://github.com/search?q=repo%3Atrinodb%2Fdocs.trino.io+%22This+connector+does+not+support+modifying+table+rows%22&type=code

Nevermind, found it:
https://github.com/search?q=org%3Atrinodb%20%22This%20connector%20does%20not%20support%20modifying%20table%20rows%22&type=code

I can only chime in by saying that at our company we never used the memory connector once, we use the following connectors (of many supported by Trino):

• Delta Lake
• Elasticsearch
• Kafka
• MongoDB
• Oracle
• PostgreSQL
• Redis

Just the fact that it is possible for (crazy) example to query Elasticsearch, Kafka, MongoDB, Redis and Oracle using standard SQL, even joining tables/documents from different DBs (that are not RDBMS with RDBMS) in the same query, makes Trino soooo powerful!

Also many Fortune 100 companies now use Delta Lake in combination with Trino to store, fetch and analyze large-scale data.
Recently we also started to insert data into Delta Lake using Trino (in addition to Spark streaming).

jOOQ supporting Trino starting with 3.19.0 would make a huge difference and would also be a major selling point compared to other ORMs.

[richardfearn]

See https://trino.io/docs/current/connector/memory.html - when using the Memory connector the tables are effectively append-only. You can create a table, you can insert rows into it, and read them back; but you can't modify existing rows or delete them.

[findepi]

Modifying existing rows, deletes, NOT NULL constraints are supported by Iceberg and Delta Lake connectors.
Adding fully fledged features to the memory connector was not a priority so far, but I understand how it would fit well the jOOQ testing.

[lukaseder]

Thanks for looking that up, @ThoSap, @richardfearn

Just the fact that it is possible for (crazy) example to query Elasticsearch, Kafka, MongoDB, Redis and Oracle using standard SQL, even joining tables/documents from different DBs (that are not RDBMS with RDBMS) in the same query, makes Trino soooo powerful!

No doubt, it is compelling for users. For me, I just need the path of least resistance to access the most features that are possibly available. I would have expected the memory (or file based) connector to be complete, because it does not "suffer" from any third party limitations, but obviously, that would mean that all transactional and other constraints would have to be implemented by trino itself.

I guess trino is more about "distributing compute" rather than implementing stuff itself? Makes sense for end users, of course.

jOOQ supporting Trino starting with 3.19.0 would make a huge difference and would also be a major selling point compared to other ORMs.

Yeah 😅 I guess once you use one of those more rare RDBMS from the bottom of the list here https://db-engines.com/en/ranking, then support for that RDBMS does become a "selling point." I don't think it will make a key difference for the jOOQ community, but anyway, the number of upvotes on the issue has helped the issue bubble up in this list:
https://github.com/jOOQ/jOOQ/issues?page=1&q=is%3Aissue+sort%3Areactions-%2B1-desc+is%3Aopen

A contender is Clickhouse, but there, I think the vendor cheated with the upvotes.

Anyway. I was planning on adding support for 2-3 new dialects in 3.19, and this has been requested a few times by existing paying customers as well, so that justifies the effort without trying to find separate budget.

@findepi Modifying existing rows, deletes, NOT NULL constraints are supported by Iceberg and Delta Lake connectors.
Adding fully fledged features to the memory connector was not a priority so far, but I understand how it would fit well the jOOQ testing.

I don't even know what those things are 😅 (Iceberg and Delta Lake). I don't really want to spend time setting them up in a test environment.

How do you test these things? I mean, you have to have a complete test suite for all of the SQL features somewhere. Do you just test them indirectly via the connector that makes them available?

Is there a support matrix somewhere that shows which SQL features are supported by which connector? (I'm aware that each connector lists things in prose, but a matrix would be cool)

[lukaseder]

Anyway, the lack of DELETE support is just a problem for the setup of integration tests, which have a reset.sql script that deletes data again prior to resetting. It's probably possible to work around this limitation by simply resetting the entire schema instead of just the test delta, to get this started.

[lukaseder]

Integrated for jOOQ 3.19.

The implementation is far from complete. The fact that the in-memory backend lacks a ton of functionality (yet it is the simplest to test without excessive test setup) means that jOOQ probably doesn't cover some stuff users might be using, e.g. DELETE or UPDATE syntax.

This can be addressed in future releases, still.

[lukaseder]

To anyone interested in this dialect, please note that early feedback is very appreciated! You can build the jOOQ Open Source Edition off github, or work with a 3.19.0-SNAPSHOT build, if you're licensed:
https://www.jooq.org/download/versions