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

Considered one of jOOQ’s key options to this point has all the time been to render just about precisely the SQL that customers count on, with none surprises – until some emulation is required to make a question work, in fact. Because of this whereas be part of elimination is a robust characteristic of many RDBMS, it isn’t a part of jOOQ’s characteristic set, to this point.

This modifications, to some extent, with jOOQ 3.19, and #14992, for implicit path joins solely. Thus far, once you write:

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

The ensuing be part of tree of this question might look much 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 major / 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, generally), 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've got 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 might do the identical optimisation, however in some, there’s a giant distinction. It is a nice optimisation specifically 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 assist 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

Word that the above examples assume that the brand new Settings.renderImplicitJoinToManyType flag is about to LEFT_JOIN. By default, implicit to-many joins aren’t supported due to their bizarre semantics when it comes to question cardinalities as defined on this weblog put up. By default, such paths must 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();