search

jOOQ / jOOQ

jOOQ is the best way to write SQL in Java
https://www.jooq.org
Other
6.17k stars 1.21k forks source link

Rendered SQL should report unsupported SQL syntax #9319

Open knutwannheden opened 5 years ago

knutwannheden commented 5 years ago

Currently the translator always renders some SQL, given that the input can be parsed. It would be nice if the translator could report an error if the input cannot be translated to the given target dialect. It should be possible to leverage the @Support annotations for this purpose.

For example WITH ... MERGE INTO ... is only supported by H2 and SQL Server. Yet the translator renders such queries for all target dialects:

WITH x AS (SELECT 1 x)
MERGE INTO t
USING x
ON 1=1
WHEN NOT MATCHED THEN
INSERT (x) VALUES (2)
;
lukaseder commented 4 years ago

This requires some infrastructure first: #9666. Once we have the infrastructure, this issue here merely consists of enabling the new settings introduced in #9666

lukaseder commented 1 week ago

Here's a case where a user ran into some unsupported syntax being rendered in ordinary jOOQ usage, i.e. without translator. jOOQ generated table.ROWID expressions in YugabyteDB, which doesn't support this:

It could be useful to render a /* comment */ with the expression hinting at this syntax not being supported by jOOQ to help with the debugging.

lukaseder commented 1 week ago

To ensure this is being done, we could add some additional checks with our instrumentation where we skip tests that run into @Support annotations missing dialect support. We should re-render the SQL with the dialect, and assert that the relevant /* comment */ is present in the rendered SQL.