Implicit path joins could now skip pointless tables within the be part of tree

One in every of jOOQ’s key options to date has all the time been to render just about precisely the SQL that customers anticipate, with none surprises – until some emulation is required to make a question work, after all. Which means that whereas be part of elimination is a robust function of many RDBMS, it isn’t a part of jOOQ’s function set, to date.

This modifications, to some extent, with jOOQ 3.19, and #14992, for implicit path joins solely. Up to now, if you write:

ctx.choose(ACTOR, ACTOR.movie().class().NAME)
   .from(ACTOR)
   .fetch();

The ensuing be part of tree of this question could look just like this:

FROM
  actor
    LEFT JOIN film_actor ON actor.actor_id = film_actor.actor_id
    LEFT JOIN movie ON film_actor.film_id = movie.film_id
    LEFT JOIN film_category ON movie.film_id = film_category.film_id
    LEFT JOIN class ON film_category.category_id = class.category_id

However, the FILM desk isn’t actually wanted on this specific question, as a result of no columns from it are being projected, and the presence of main / international keys ensures equivalence if we simply skip the desk within the be part of tree:

FROM
  actor
    LEFT JOIN film_actor ON actor.actor_id = film_actor.actor_id
    LEFT JOIN film_category ON film_actor.film_id = film_category.film_id
    LEFT JOIN class ON film_category.category_id = class.category_id

As quickly as any column from the FILM desk is projected (or referenced, on the whole), then the desk re-appears within the be part of tree. E.g. for this question:

ctx.choose(ACTOR, ACTOR.movie().class().NAME)
   .from(ACTOR)
   // This implies we have now so as to add the FILM desk once more to the be part of tree:
   .the place(ACTOR.movie().TITLE.like("A%"))
   .fetch();

In lots of RDBMS, this doesn’t actually matter, as a result of the RDBMS could do the identical optimisation, however in some, there’s a giant distinction. It is a nice optimisation particularly as a result of with implicit path joins, jOOQ customers can’t actually hand-write these optimisations as they’re not authoring the be part of tree within the FROM clause themselves.

Why implement this solely in jOOQ 3.19

Earlier than jOOQ 3.19, there was no help for to-many path joins, and significantly, not for many-to-many path joins, which skip the connection desk. However now, customers can write:

// This
ACTOR.movie().class().NAME

// Is brief (and equal) for this:
ACTOR.filmActor().movie().filmCategory().class().NAME

Notice that the above examples assume that the brand new Settings.renderImplicitJoinToManyType flag is ready to LEFT_JOIN. By default, implicit to-many joins aren’t supported due to their bizarre semantics by way of question cardinalities as defined on this weblog put up. By default, such paths should be declared explicitly within the FROM clause:

ctx.choose(ACTOR, ACTOR.movie().class().NAME)
   .from(
       ACTOR,
       ACTOR.movie(),
       ACTOR.movie().class())
   .fetch();

Or, simply:

ctx.choose(ACTOR, ACTOR.movie().class().NAME)
   .from(
       ACTOR,
       ACTOR.movie().class())
   .fetch();