ParsingConnection adding unnecessary casts on columns in selects, joins and wheres

[Ahmed Ghanmi]

We are using jOOQ's ParsingConnection to translate queries from ORACLE to POSTGRESQL. We've noticed that jOOQ was automatically adding casts in places where the typing is evident. Suppressing these casts is important to us as we do not want to add any more points of divergence between oracle and postgresql.

1. Is there a way to suppress automatic casting completely when using the parser. If not, are there any plans to implement this option?
2. It seems that activating meta lookups allows jOOQ to type expressions from the schema, which should inhibit much of the unnecessary casts (we have not tested this feature yet). When using meta lookups, in which cases will jOOQ add its own casts ?

[Lukas Eder]

Hi Ahmed,

Casting is necessary a lot of times in PostgreSQL (much more than in Oracle), so these automatic casts won't go away, nor is there a flag to deactivate them.

1. There is not.

2. Meta lookups always helps, yes

Do you have any specific concerns related to casts? you said you wanted to avoid "points of divergence," but that doesn't sound like you ran into any actual issue.

Best Regards,

Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/5baa2ecb-f4bf-48ad-805d-e6c79e95f832n%40googlegroups.com.

[Ahmed Ghanmi]

Hello Lukas,

Forgive me I forgot to greet in both my posts :((

> Do you have any specific concerns related to casts?

To explain the situation, we intend to use the same codebase on both Oracle environments and PostgreSQL environments. 

While most of our SQL primarily targets Oracle, it is expected to also work on PostgreSQL. We use the parser to translate the queries dynamically as they are requested.

We are concerned that we'd end up with recurring performance issues on PG due to the casts. As a starting point will we have to rewrite indexes to account for the casts? Will any index created in the future have to account fo them? Since on Oracle's end this won't be needed, different scripts for each dbms would beat our goal of becoming as DB agnostic as possible.

The casts are hidden to the developer, how predictable are they ?  But say they weren't added, if a query is badly written and its castless translation is rendered pg-incompatible, the dev would from the start be aware of a casting issue and proceed to at least rewrite their oracle query to use an explicit cast. The casting will be visible and predictable.

[Lukas Eder]

On Wed, May 1, 2024 at 12:54 PM Ahmed Ghanmi <ahmed....@gmail.com> wrote:

We are concerned that we'd end up with recurring performance issues on PG due to the casts. As a starting point will we have to rewrite indexes to account for the casts?

I'm not sure what you mean by this. Bind values are cast by jOOQ, yes. But that doesn't affect query performance to my best knowledge. A bind value is a constant expression, and a cast on a constant expression is still a constant expression. Why do you think this is performance relevant?

 

Will any index created in the future have to account fo them? Since on Oracle's end this won't be needed, different scripts for each dbms would beat our goal of becoming as DB agnostic as possible.

Eventually, you will have to think about the specifics of each of your supported RDBMS, there's no way around that. There are definitely a few differences that jOOQ can't or cannot easily abstract over, in terms of behaviour (e.g. transactions), data types, etc.

But again, rather than thinking about generic and hypothetical cases, do you have *specific* issues that you've encountered?

 

The casts are hidden to the developer, how predictable are they ?  But say they weren't added, if a query is badly written and its castless translation is rendered pg-incompatible, the dev would from the start be aware of a casting issue and proceed to at least rewrite their oracle query to use an explicit cast. The casting will be visible and predictable.

jOOQ will always try to "make the SQL work." This may include casts, and many other elements of emulation of features. The alternative to certain bind value casts is a query that simply won't work on PostgreSQL.

But again, I doubt that this is an actual problem (performance or otherwise).

Best Regards,

Lukas