Wrong Oracle style to ANSI JOIN transformation result, depending on predicate order

[lukaseder]

As reported here:
https://blog.jooq.org/2020/11/17/automatically-transform-oracle-style-implicit-joins-to-ansi-join-using-jooq/#comment-404345

Consider these input queries, which are equivalent, and differ only in the lexical order of predicates:

select *
from A, B
where (A.ID = B.ID(+) AND B.ID = 1);

select *
from A, B
where (B.ID = 1 AND A.ID = B.ID(+));

Transforming the Oracle style to ANSI JOIN yields different results:

select *
from A
  join B
    on A.ID = B.ID
where B.ID = 1;

select *
from A
  left outer join B
    on A.ID = B.ID
where B.ID = 1;

Both are correct. The one that produces an inner join applies an optimisation that seems not to be applied otherwise. Perhaps, the optimisation isn't worth it, given that:

• it seems to be tricky to apply independently of predicate ordering
• it doesn't matter in cases like these, as the RDBMS will perform the optimisation anyway
• without the optimisation, the user "intent" is reflected more clearly
• there may be an edge case where the optimisation produces wrong SQL?

[lukaseder]

Fixed in jOOQ 3.15.0 and 3.14.4 (#10995)