Implicit path correlation produces correlation predicate in ON clause rather than WHERE clause in some cases

[lukaseder]

The new implicit path correlation feature that was introduced with jOOQ 3.19 has a problem when the path correlation is composed of multiple path joins:
https://www.jooq.org/doc/dev/manual/sql-building/sql-statements/select-statement/implicit-path-correlation/

Consider this integration test query:

Result<Record2<Integer, Integer>> r3 =
create().select(
            a.ID, field(select(field(
                select(countDistinct(a.fkTBookAuthorId().tBookStore().NAME))
                .from(a.fkTBookAuthorId())
                .leftJoin(a.fkTBookAuthorId().tBookStore())
            ))))
        .from(a)
        .orderBy(a.ID)
        .fetch();

It produces a weird LEFT JOIN correlation predicate in the LEFT JOIN's ON clause rather than the WHERE clause:

select
  a.ID,
  (
    select (
      select count(distinct alias_122359416.NAME)
      from TEST.T_BOOK as alias_52818137
        left outer join TEST.T_BOOK_TO_BOOK_STORE as alias_73255154
          on (
            alias_52818137.AUTHOR_ID = a.ID -- Correlation here
            and alias_73255154.BOOK_ID = alias_52818137.ID
          )
        left outer join TEST.T_BOOK_STORE as alias_122359416
          on alias_73255154.BOOK_STORE_NAME = alias_122359416.NAME
    )
    from SYSIBM.SYSDUMMY1
  )
from TEST.T_AUTHOR as a
order by a.ID

This isn't supported in numerous dialects, including:

• Derby
• H2: Column not found in correlated subquery, when referencing outer column from LEFT JOIN .. ON clause h2database/h2database#3945

These dialects seem to return wrong results (sometimes):

• Oracle: https://forums.oracle.com/ords/apexds/post/wrong-query-result-produced-by-fetch-clause-when-correlatin-0496

While the dialect's lack of support for this query semantics is debatable (I can see how they reject the query. Things get hairy when outer joins are nested even further), it's probably better for jOOQ to generate this query, instead:

select
  a.ID,
  (
    select (
      select count(distinct alias_122359416.NAME)
      from TEST.T_BOOK as alias_52818137
        left outer join TEST.T_BOOK_TO_BOOK_STORE as alias_73255154
          on alias_73255154.BOOK_ID = alias_52818137.ID
        left outer join TEST.T_BOOK_STORE as alias_122359416
          on alias_73255154.BOOK_STORE_NAME = alias_122359416.NAME
    )
    from SYSIBM.SYSDUMMY1
    where alias_52818137.AUTHOR_ID = a.ID -- Correlation here
  )
from TEST.T_AUTHOR as a
order by a.ID